如何定位/导出Hive查询结果？新手导出本地文件遇问题求助

Hey there! Let's work through each of your problems step by step to get your Hive query results onto your local machine for Excel:

1. Why hive -e fails inside Hive CLI #

The hive -e "<SQL>" command is meant to run directly in your shell (like Bash), not inside the Hive interactive CLI. When you're already at the hive> prompt, you just execute plain SQL queries. For example:

select * from TABLE limit 10;

If you want to export results from within the Hive CLI, skip the hive -e part and use the local directory export method we cover below.

2. Shell command exporting to HDFS instead of local? #

Wait, the command:

hive -S -e "USE DATABASE; select * from TABLE limit 10" > /tmp/test/test.csv;

The > redirection is handled by your shell, so this should save the file to your local machine's /tmp/test/ directory (not HDFS). Here’s what might be tripping you up:

• You might be mixing up HDFS paths and local paths. Check local storage with ls /tmp/test/test.csv and HDFS with hdfs dfs -ls /tmp/test/test.csv.
• The local directory /tmp/test/ might not exist. Create it first with mkdir -p /tmp/test before running the export command.
• If you’re running this on a remote server (most Hive setups are), the file will save to the server’s local filesystem—not your personal computer. You’ll need to download it using scp or a file transfer tool, like:

  scp your_username@server_ip:/tmp/test/test.csv /your/local/computer/path/
  

3. insert overwrite local directory saving to HDFS? #

The local keyword tells Hive to write to the local filesystem of the machine running Hive (again, if it’s a remote server, that’s the server’s storage, not yours). If you’re seeing the file in HDFS, double-check these:

• Did you actually include the local keyword? It’s easy to miss! The correct, Excel-friendly syntax is:

  insert overwrite local directory '/tmp/hello'
  row format delimited fields terminated by ','
  select * from TABLE limit 10;
  

  Adding row format delimited fields terminated by ',' ensures the output is CSV-ready for Excel.
• If you’re using Beeline instead of the classic Hive CLI, confirm your connection—Beeline’s path handling can be finicky, but local directory should still target the server’s local storage.
• Just like before, if Hive is on a remote server, you’ll need to transfer the file from the server’s /tmp/hello folder to your own computer.

Quick Tip for Excel-Friendly Exports #

To make the output directly usable in Excel, always specify a delimiter and include headers if needed. For the shell method, try this:

hive -S -e "SET hive.cli.print.header=true; USE DATABASE; select * from TABLE limit 10" | sed 's/\t/,/g' > /tmp/test/test.csv

This enables column headers, replaces tabs with commas, and saves as a clean CSV.

内容的提问来源于stack exchange，提问作者Sam