I used this Hive query to export a table into a CSV file.
INSERT OVERWRITE DIRECTORY '/user/data/output/test' select column1, column2 from table1;
The file generated '000000_0' does not have comma separator
Is this the right way to generate CSV file? If no, please let me know how can I generate the CSV file?
解决方案
If you're using Hive 11 or better you can use the INSERT statement with the LOCAL keyword.
Example:
insert overwrite local directory '/home/carter/staging' row format delimited fields terminated by ',' select * from hugetable;
Note that this may create multiple files and you may want to concatenate them on the client side after it's done exporting.
Using this approach means you don't need to worry about the format of the source tables, can export based on arbitrary SQL query, and can select your own delimiters and output formats.