Export hive table to excel

Requirement

You have one hive table named as infostore which is present in bdp schema. It is needed to get the data into Excel file.

Solution.

Let’s say the location where output file should present is

/root/local_bdp/posts/export-hive-data-into-file

Step 1: Create Output directory

 mkdir /root/local_bdp/posts/export-hive-data-into-file/output

Step 2: Go to hive CLI and execute the following code.

set hive.cli.print.header=true;

INSERT OVERWRITE LOCAL DIRECTORY '/root/local_bdp/posts/export-hive-data-into-file/output'
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t' 
SELECT * FROM bdp.infostore;

Explanation of the query:

Insert overwrite is written so that if the file is already loaded then it will update the file in the second time run.
You have to define a local directory which was created in the first step. The output file will be available under the created directory.
Look at the line where fields terminated is written, there we have mentioned Tab (\t) as we want columns to be separated by a tab delimiter.
We are taking all the data of infostore table so ‘*’ is mentioned in the select query. You can also add filter condition if you wish to transfer certain records.

Step 3: Output

Verify the file which is generated in ‘/root/local_bdp/posts/export-hive-data-into-file/output’ directory.

Go to the directory via WinSCP and see the content, it should be delimited by a tab \t .

If there are multiple part files you can combine all files using below command

cd /root/local_bdp/posts/export-hive-data-into-file/output

cat * > op.txt

and then copy content of op.txt and paste it into Excel. and before pasting into Excel make sure you are doing below step:

Select complete sheet and go to format cell and change the cell category to Text.

Don’t miss any updates from us, subscribe us. Keep learning.

Don’t miss the tutorial on Top Big data courses on Udemy you should Buy