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. And it is required to send the data of infostore table into that application. This application expects a file which should have data of infostore table and delimited by 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 a output directory

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

Step 2: go to hive prompt and execute the following code.

 

 
 
  1. INSERT OVERWRITE LOCAL DIRECTORY '/root/local_bdp/posts/export-hive-data-into-file/output'
  2. ROW FORMAT DELIMITED
  3. FIELDS TERMINATED BY ':'
  4. SELECT * FROM bdp.infostore;
Explanation of the code
  1. Insert overwrite is written so that if the file is already loaded then it will update the file if you run it again.
  2. You have to define a local directory which was created in the first step. The output file will be available after executing this query.
  3. Look at the line where fields terminated is written, there we have mentioned colon (: ) as we want columns to be separated by a colon. You can change it to comma (‘,’) or pipe (‘|’) as per your requirement.
  4. We are taking all the data of infostore table so *, you can 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’

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.

Load CSV file into hive AVRO table

Requirement You have comma separated(CSV) file and you want to create Avro table in hive on top of it, then ...
Read More

Load CSV file into hive PARQUET table

Requirement You have comma separated(CSV) file and you want to create Parquet table in hive on top of it, then ...
Read More

Hive Most Asked Interview Questions With Answers – Part II

What is bucketing and what is the use of it? Answer: Bucket is an optimisation technique which is used to ...
Read More
/ hive, hive interview, interview-qa

Spark Interview Questions Part-1

Suppose you have a spark dataframe which contains millions of records. You need to perform multiple actions on it. How ...
Read More

2 Comments

    1. @Sandeep It will not have any extension. it will be just a file,if you want to get particular file type ,use below command to get the required extension
      >mv oldfilename newfilename.txt

Leave a Reply