How do I output the results of a HiveQL query to CSV?

by

Although it is possible to use INSERT OVERWRITE to get data out of Hive, it might not be the best method for your particular case. First let me explain what INSERT OVERWRITE does, then I’ll describe the method I use to get tsv files from Hive tables.

According to the manual, your query will store the data in a directory in HDFS. The format will not be csv.

Data written to the filesystem is serialized as text with columns separated by ^A and rows separated by newlines. If any of the columns are not of primitive type, then those columns are serialized to JSON format.

A slight modification (adding the LOCAL keyword) will store the data in a local directory.

INSERT OVERWRITE LOCAL DIRECTORY '/home/lvermeer/temp' select books from table;

When I run a similar query, here’s what the output looks like.

[lvermeer@hadoop temp]$ ll
total 4
-rwxr-xr-x 1 lvermeer users 811 Aug  9 09:21 000000_0
[lvermeer@hadoop temp]$ head 000000_0 
"row1""col1"1234"col3"1234FALSE
"row2""col1"5678"col3"5678TRUE

Personally, I usually run my query directly through Hive on the command line for this kind of thing, and pipe it into the local file like so:

hive -e 'select books from table' > /home/lvermeer/temp.tsv

That gives me a tab-separated file that I can use. Hope that is useful for you as well.

Based on this patch-3682, I suspect a better solution is available when using Hive 0.11, but I am unable to test this myself. The new syntax should allow the following.

INSERT OVERWRITE LOCAL DIRECTORY '/home/lvermeer/temp' 
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ',' 
select books from table;

Hope that helps.

More Related Contents:

  1. Hive: Best way to do incremetal updates on a main table
  2. Primary keys with Apache Spark
  3. Execute Hive Query with IN clause parameters in parallel
  4. Difference between Hive internal tables and external tables?
  5. data block size in HDFS, why 64MB?
  6. How to update table in Hive 0.13?
  7. Should each and every table have a primary key?
  8. Database, Table and Column Naming Conventions? [closed]
  9. What are the performance improvement of Sequential Guid over standard Guid? [closed]
  10. When and why are database joins expensive?
  11. Normalization: What does “repeating groups” mean?
  12. How to set variables in HIVE scripts
  13. Copying data from one SQLite database to another
  14. Difference Between Firestore Set with {merge: true} and Update
  15. HiveQL: Using query results as variables
  16. What is a database transaction?
  17. PostgreSQL: Drop PostgreSQL database through command line [closed]
  18. Are foreign keys really necessary in a database design?
  19. GUID vs INT IDENTITY [duplicate]
  20. What is a sequence (Database)? When would we need it?
  21. Storing Images : DB or File System –
  22. What is database pooling?
  23. In what way does denormalization improve database performance?
  24. Hive Explode / Lateral View multiple arrays
  25. Storing a small number of images: blob or fs?
  26. How to unit test an object with database queries
  27. Populate Android Database From CSV file?
  28. Creating test data in a database [closed]
  29. When we need to use 1-to-1 relationship in database design?
  30. CAP theorem – Availability and Partition Tolerance

Leave a Comment