export hive data into file

Requirement

You have one hive table named as infostore which is present in bdp schema. One more application is connected to your application, but it is not allowed to take the data from hive table due to security reasons. It is required to send the data of infostore table into that application. This application expects a file which should have infostore table’s data delimited by a colon (:).

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.

INSERT OVERWRITE LOCAL DIRECTORY '/root/local_bdp/posts/export-hive-data-into-file/output'
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ':' 
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 colon (: ) as we want columns to be separated by a colon delimiter. You can change it to comma (‘,’) or pipe (‘|’) as per your requirement.
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.

Please refer below screenshot for reference.

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 colon.

Please refer below screenshot for reference.

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

Export hive data into file