Argthtjtr

If you want a CSV file then you can modify Lukas' solutions as follows (assuming you are on a linux box):

hive -e 'select books from table' | sed 's/[[:space:]]+/,/g' > /home/lvermeer/temp.csv

You should use CREATE TABLE AS SELECT (CTAS) statement to create a directory in HDFS with the files containing the results of the query. After that you will have to export those files from HDFS to your regular disk and merge them into a single file.

You also might have to do some trickery to convert the files from '01' - delimited to CSV. You could use a custom CSV SerDe or postprocess the extracted file.

If you are using HUE this is fairly simple as well. Simply go to the Hive editor in HUE, execute your hive query, then save the result file locally as XLS or CSV, or you can save the result file to HDFS.

I was looking for a similar solution, but the ones mentioned here would not work. My data had all variations of whitespace (space, newline, tab) chars and commas.

To make the column data tsv safe, I replaced all t chars in the column data with a space, and executed python code on the commandline to generate a csv file, as shown below:

hive -e 'tab_replaced_hql_query' |  python -c 'exec("import sys;import csv;reader = csv.reader(sys.stdin, dialect=csv.excel_tab);writer = csv.writer(sys.stdout, dialect=csv.excel)nfor row in reader: writer.writerow(row)")'

This created a perfectly valid csv. Hope this helps those who come looking for this solution.

You can use hive string function CONCAT_WS( string delimiter, string str1, string str2...strn )

for ex:

hive -e 'select CONCAT_WS(',',cola,colb,colc...,coln) from Mytable' > /home/user/Mycsv.csv

You can use INSERT … DIRECTORY …, as in this example:

INSERT OVERWRITE LOCAL DIRECTORY '/tmp/ca_employees'

SELECT name, salary, address

FROM employees

WHERE se.state = 'CA';

OVERWRITE and LOCAL have the same interpretations as before and paths are interpreted following the usual rules. One or more files will be written to /tmp/ca_employees, depending on the number of reducers invoked.

I had a similar issue and this is how I was able to address it.

Step 1 - Loaded the data from Hive table into another table as follows

DROP TABLE IF EXISTS TestHiveTableCSV;

CREATE TABLE TestHiveTableCSV 

ROW FORMAT DELIMITED 

FIELDS TERMINATED BY ','

LINES TERMINATED BY 'n' AS

SELECT Column List FROM TestHiveTable;

Step 2 - Copied the blob from Hive warehouse to the new location with appropriate extension

Start-AzureStorageBlobCopy

-DestContext $destContext 

-SrcContainer "Source Container"

-SrcBlob "hive/warehouse/TestHiveTableCSV/000000_0"

-DestContainer "Destination Container"

-DestBlob "CSV/TestHiveTable.csv"

The default separator is "^A". In python language, it is "x01".

When I want to change the delimiter, I use SQL like:

SELECT col1, delimiter, col2, delimiter, col3, ..., FROM table

Then, regard delimiter+"^A" as a new delimiter.

Similar to Ray's answer above, Hive View 2.0 in Hortonworks Data Platform also allows you to run a Hive query and then save the output as csv.

In case you are doing it from Windows you can use Python script hivehoney to extract table data to local CSV file.

It will:

1. Login to bastion host.


2. pbrun.


3. kinit.


4. beeline (with your query).


5. Save echo from beeline to a file on Windows.

Execute it like this:

set PROXY_HOST=your_bastion_host



set SERVICE_USER=you_func_user



set LINUX_USER=your_SOID



set LINUX_PWD=your_pwd



python hh.py --query_file=query.sql

I tried various options, but this would be one of the simplest solution for Python Pandas:

hive -e 'select books from table' | grep "|" ' > temp.csv



df=pd.read_csv("temp.csv",sep='|')

You can also use tr "|" "," to convert "|" to ","