Export as csv in beeline hive

by

When hive version is at least 0.11.0 you can execute:

INSERT OVERWRITE LOCAL DIRECTORY '/tmp/directoryWhereToStoreData' 
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ','  
LINES TERMINATED BY "\n"
SELECT * FROM yourTable;

from hive/beeline to store the table into a directory on the local filesystem.

Alternatively, with beeline, save your SELECT query in yourSQLFile.sql and run:

beeline -u 'jdbc:hive2://[databaseaddress]' --outputformat=csv2 -f yourSQlFile.sql > theFileWhereToStoreTheData.csv

Also this will store the result into a file in the local file system.

From hive, to store the data somewhere into HDFS:

CREATE EXTERNAL TABLE output 
LIKE yourTable 
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
LOCATION 'hfds://WhereDoYou/Like';

INSERT OVERWRITE TABLE output SELECT * from yourTable;

then you can collect the data to a local file using:

hdfs dfs -getmerge /WhereDoYou/Like

This is another option to get the data using beeline only:

env HADOOP_CLIENT_OPTS="-Ddisable.quoting.for.sv=false" beeline -u "jdbc:hive2://your.hive.server.address:10000/" --incremental=true --outputformat=csv2 -e "select * from youdatabase.yourtable"

Working on:

Connected to: Apache Hive (version 1.1.0-cdh5.10.1)
Driver: Hive JDBC (version 1.1.0-cdh5.10.1)
Transaction isolation: TRANSACTION_REPEATABLE_READ
Beeline version 1.1.0-cdh5.10.1 by Apache Hive

More Related Contents:

  1. How to export a Hive table into a CSV file?
  2. Csv empty when trying to read it
  3. What’s the most robust way to efficiently parse CSV using awk?
  4. Dealing with commas in a CSV file
  5. Properly escape a double quote in CSV
  6. Is there a way to include commas in CSV columns without breaking the formatting?
  7. How to load jar dependenices in IPython Notebook
  8. csv to array in d3.js
  9. How to properly escape a double quote in CSV?
  10. Can you encode CR/LF in into CSV files?
  11. What MIME type should I use for CSV?
  12. Encoding Error in Panda read_csv [duplicate]
  13. Spark dataframe save in single file on hdfs location [duplicate]
  14. Reading csv files with quoted fields containing embedded commas
  15. Parse a csv using awk and ignoring commas inside a field
  16. VBScript to loop through all files in a folder
  17. How to check encoding of a CSV file
  18. Sort CSV file by multiple columns using the “sort” command
  19. UnicodeDecodeError: ‘charmap’ codec can’t decode byte 0x8d in position 7240: character maps to
  20. CodedUI test does not read data from CSV input file
  21. Can a CSV file have a comment?
  22. CSV with comma or semicolon?
  23. Write Spark dataframe as CSV with partitions
  24. Import CSV into google cloud datastore
  25. What is the difference between partitioning and bucketing a table in Hive ?
  26. Import CSV to MySQL
  27. Delete “” from csv values and change column names when writing to a CSV
  28. Split CSV files into smaller files but keeping the headers?
  29. Forcing fputcsv to Use Enclosure For *all* Fields
  30. How to add a new column to the beginning of the rows of a CSV file?

Leave a Comment