CSV or comma separated flat files are most common file system used to transfer data using electronic media. You may get requirement to export data for ad-hoc query or just unload data for subset of columns available in table, in this case export Hive table into CSV format using Beeline client comes into handy.
Beeline Separated Value Output Format
The values of a row are separated by different delimiters. Starting with Hive 0.14 there are improved output formats available, dsv (custom delimiter), csv2 (comma separated value) and tsv2 (tab separated value).
Read:
- Hadoop – Export Hive Data with Quoted Values into Flat File and Example
- Apache Hive Different File Formats:TextFile,SequenceFile,RCFile,AVRO,ORC,Parquet
- Apache Hive Load Quoted Values CSV File and Examples
- Hive External table and Examples
Export Hive Table into CSV Format using Beeline Client
You can use options available in Beeline client to export Hive table into CSV format. You can either use csv2 or dsv output format to export data into CSV format.
Export Hive Table into CSV Format using CSV2 Output Format
Apache Hive support built in functions that you can use to export Hive tables in required format. One of the function is CSV2 output format. You simply have to use –outputformat=csv2 option to export Hive table to CSV format.
Below example demonstrate the use of CSV2 output format:
https://gist.github.com/22cc2dc8c3b0602642aea1483ca2d327
As shown in the above example, you can simply redirect query output to file if you want to save result.
Export Hive Table into CSV Format using DSV Output Format
Apache Hive support built in functions that you can use to export Hive tables in required format. One of the function is DSV output format. You simply have to use –outputformat=dsv and –delimiterForDSV=’,’ options to export Hive table to CSV format.
Below example demonstrate the use of DSV output format and comma as delimiter:
https://gist.github.com/e6c767c696886cc2d20fdead8e008298