blocks|key|262371|text|如果你想要一个CSV文件，你可以修改Lukas的解决方案，如下所示(假设你在linux机器上)：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|262372|hive+-e+'select+books+from+table'+%7C+sed+'s/[[:space:]]\%2B/,/g'+>+/home/lvermeer/temp.csv|code-block|syntax|javascript|262373|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

If you want a CSV file then you can modify Lukas' solutions as follows (assuming you are on a linux box):

<pre><code>hive -e 'select books from table' | sed 's/[[:space:]]\+/,/g' &gt; /home/lvermeer/temp.csv
</code></pre>

blocks|key|262817|text|这是我发现的输出HiveQL结果的最友好的csv方法。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|262818|您不需要任何grep或sed命令来格式化数据，相反，hive支持它，只需要添加额外的outputformat标记。|262819|hive+--outputformat=csv2+-e+'select+*+from+<table_name>+limit+20'+>+/path/toStore/data/results.csv|code-block|syntax|javascript|262820|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

This is most csv friendly way I found to output the results of HiveQL. 
You don't need any grep or sed commands to format the data, instead hive supports it, just need to add extra tag of outputformat.

<pre><code>hive --outputformat=csv2 -e 'select * from &lt;table_name&gt; limit 20' &gt; /path/toStore/data/results.csv
</code></pre>

blocks|key|262270|text|您应该使用CREATE+TABLE+AS+SELECT+(CTAS)语句在HDFS中创建一个目录，其中包含包含查询结果的文件。在此之后，您必须将这些文件从HDFS导出到您的常规磁盘，并将它们合并为一个文件。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|262271|您可能还需要使用一些技巧将文件从'\001‘分隔转换为CSV。您可以使用自定义CSV+SerDe或对提取的文件进行后处理。|262272|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

You should use CREATE TABLE AS SELECT (CTAS) statement to create a directory in HDFS with the files containing the results of the query. After that you will have to export those files from HDFS to your regular disk and merge them into a single file.

You also might have to do some trickery to convert the files from '\001' - delimited to CSV. You could use a custom CSV SerDe or postprocess the extracted file.

blocks|key|45886|text|您可以使用INSERT…DIRECTORY…，如下例所示：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|45887|INSERT+OVERWRITE+LOCAL+DIRECTORY+'/tmp/ca_employees'
SELECT+name,+salary,+address
FROM+employees
WHERE+se.state+=+'CA';|code-block|syntax|javascript|45888|OVERWRITE和LOCAL的解释与以前相同，路径的解释遵循通常的规则。一个或多个文件将被写入/tmp/ca_employees，这取决于调用的缩减程序的数量。|45889|entityMap^0|5|6|C|9|0|0|0|9|A|5|1C|H|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]|$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|U|8|@$9|V|A|W|B|C]|$9|X|A|Y|B|C]|$9|Z|A|10|B|C]]|D|@]|E|$]]|$1|M|3|-4|5|6|7|11|8|@]|D|@]|E|$]]]|N|$]]

You can use <code>INSERT</code> … <code>DIRECTORY</code> …, as in this example:

<pre><code>INSERT OVERWRITE LOCAL DIRECTORY '/tmp/ca_employees'
SELECT name, salary, address
FROM employees
WHERE se.state = 'CA';
</code></pre>

<code>OVERWRITE</code> and <code>LOCAL</code> have the same interpretations as before and paths are interpreted following the usual rules. One or more files will be written to <code>/tmp/ca_employees</code>, depending on the number of reducers invoked.

blocks|key|45935|text|如果你使用色调，这也是相当简单的。只需转到HUE中的配置单元编辑器，执行配置单元查询，然后在本地将结果文件保存为XLS或CSV，或者您也可以将结果文件保存到HDFS。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|45936|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

If you are using HUE this is fairly simple as well. Simply go to the Hive editor in HUE, execute your hive query, then save the result file locally as XLS or CSV, or you can save the result file to HDFS.

blocks|key|262467|text|您可以使用配置单元字符串函数CONCAT_WS(+string+delimiter,+string+str1,+string+str2...strn+)|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|262468|对于ex：|262469|hive+-e+'select+CONCAT_WS(',',cola,colb,colc...,coln)+from+Mytable'+>+/home/user/Mycsv.csv|code-block|syntax|javascript|262470|entityMap^0|E|1Q|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|R|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|S|8|@]|D|@]|E|$K|L]]|$1|M|3|-4|5|6|7|T|8|@]|D|@]|E|$]]]|N|$]]

You can use hive string function <code>CONCAT_WS( string delimiter, string str1, string str2...strn )</code>

for ex:

<pre><code>hive -e 'select CONCAT_WS(',',cola,colb,colc...,coln) from Mytable' &gt; /home/user/Mycsv.csv
</code></pre>

blocks|key|262527|text|我正在寻找类似的解决方案，但这里提到的解决方案不起作用。我的数据包含各种不同的空格(空格、换行符、制表符)字符和逗号。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|262528|为了确保列数据tsv的安全，我将列数据中的所有\t字符都替换为空格，并在命令行上执行python代码以生成csv文件，如下所示：|262529|hive+-e+'tab_replaced_hql_query'+%7C++python+-c+'exec("import+sys;import+csv;reader+=+csv.reader(sys.stdin,+dialect=csv.excel_tab);writer+=+csv.writer(sys.stdout,+dialect=csv.excel)\nfor+row+in+reader:+writer.writerow(row)")'|code-block|syntax|javascript|262530|这创建了一个完全有效的csv。希望这对那些来寻找这个解决方案的人有所帮助。|262531|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|O|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

I was looking for a similar solution, but the ones mentioned here would not work. My data had all variations of whitespace (space, newline, tab) chars and commas. 

To make the column data tsv safe, I replaced all \t chars in the column data with a space, and executed python code on the commandline to generate a csv file, as shown below:

<pre><code>hive -e 'tab_replaced_hql_query' | python -c 'exec("import sys;import csv;reader = csv.reader(sys.stdin, dialect=csv.excel_tab);writer = csv.writer(sys.stdout, dialect=csv.excel)\nfor row in reader: writer.writerow(row)")'
</code></pre>

This created a perfectly valid csv. Hope this helps those who come looking for this solution.

blocks|key|46141|text|hive++--outputformat=csv2+-e+"select+*+from+yourtable"+>+my_file.csv|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|46142|或|unstyled|46143|hive++--outputformat=csv2+-e+"select+*+from+yourtable"+>+[your_path]/file_name.csv|46144|对于tsv，只需在上面的查询中将csv更改为tsv，然后运行您的查询|46145|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|N|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$B|C]]|$1|I|3|J|5|F|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|F|7|Q|8|@]|9|@]|A|$]]]|L|$]]

<pre><code>hive --outputformat=csv2 -e "select * from yourtable" &gt; my_file.csv
</code></pre>

or

<pre><code>hive --outputformat=csv2 -e "select * from yourtable" &gt; [your_path]/file_name.csv
</code></pre>

For tsv, just change csv to tsv in the above queries and run your queries

blocks|key|45822|text|默认分隔符是"%5EA“。在Python语言中，它是"\x01“。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|45823|当我想要更改分隔符时，我使用SQL，如下所示：|45824|SELECT+col1,+delimiter,+col2,+delimiter,+col3,+...,+FROM+table|code-block|syntax|javascript|45825|然后，将delimiter%2B"%5EA“视为新的分隔符。|45826|entityMap^0|7|2|P|4|0|0|0|F|2|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]|$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|V|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|W|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|X|8|@$9|Y|A|Z|B|C]]|D|@]|E|$]]|$1|O|3|-4|5|6|7|10|8|@]|D|@]|E|$]]]|P|$]]

The default separator is "<code>^A</code>". In python language, it is "<code>\x01</code>".

When I want to change the delimiter, I use SQL like:

<pre><code>SELECT col1, delimiter, col2, delimiter, col3, ..., FROM table
</code></pre>

Then, regard delimiter+"<code>^A</code>" as a new delimiter.

blocks|key|262624|text|我尝试了各种方法，但这将是Python+Pandas最简单的解决方案之一|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|262625|hive+-e+'select+books+from+table'+%7C+grep+"%7C"+'+>+temp.csv

df=pd.read_csv("temp.csv",sep='%7C')|code-block|syntax|javascript|262626|您还可以使用tr+"%7C"+","将"%7C“转换为"，”|262627|entityMap^0|D|6|K|6|0|0|6|A|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]|$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|U|8|@$9|V|A|W|B|C]]|D|@]|E|$]]|$1|M|3|-4|5|6|7|X|8|@]|D|@]|E|$]]]|N|$]]

I tried various options, but this would be one of the simplest solution for <code>Python</code> <code>Pandas</code>:

<pre><code>hive -e 'select books from table' | grep "|" ' &gt; temp.csv

df=pd.read_csv("temp.csv",sep='|')
</code></pre>

You can also use <code>tr "|" ","</code> to convert "|" to ","

blocks|key|46060|text|与上面Ray的答案类似，Hortonworks+Data+Platform中的Hive+View+2.0也允许您运行Hive查询，然后将输出保存为csv。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|46061|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Similar to Ray's answer above, Hive View 2.0 in Hortonworks Data Platform also allows you to run a Hive query and then save the output as csv.

blocks|key|46114|text|在开始查询之后，只需介绍更多以下步骤：INSERT+OVERWRITE+LOCAL+DIRECTORY+'/home/lvermeer/temp'++ROW+FORMAT+DELIMITED++FIELDS+TERMINATED+BY+','++select+books+from+table;|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|46115|在我的例子中，temp文件夹下生成的数据是deflate格式的，如下所示：|46116|$+ls
000000_0.deflate++
000001_0.deflate++
000002_0.deflate++
000003_0.deflate++
000004_0.deflate++
000005_0.deflate++
000006_0.deflate++
000007_0.deflate|code-block|syntax|javascript|46117|以下是解压deflate文件并将所有内容放入一个csv文件中的命令：|46118|hadoop+fs+-text+"file:///home/lvermeer/temp/*"+>+/home/lvermeer/result.csv|46119|entityMap^0|J|3K|0|L|7|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|V|8|@$9|W|A|X|B|C]]|D|@]|E|$]]|$1|H|3|I|5|J|7|Y|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|Z|8|@]|D|@]|E|$]]|$1|O|3|P|5|J|7|10|8|@]|D|@]|E|$K|L]]|$1|Q|3|-4|5|6|7|11|8|@]|D|@]|E|$]]]|R|$]]

Just to cover more following steps after kicking off the query:
<code>INSERT OVERWRITE LOCAL DIRECTORY '/home/lvermeer/temp' 
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ',' 
select books from table;</code>

In my case, the generated data under temp folder is in <code>deflate</code> format, 
and it looks like this:

<pre><code>$ ls
000000_0.deflate 
000001_0.deflate 
000002_0.deflate 
000003_0.deflate 
000004_0.deflate 
000005_0.deflate 
000006_0.deflate 
000007_0.deflate
</code></pre>

Here's the command to unzip the deflate files and put everything into one csv file:

<pre><code>hadoop fs -text "file:///home/lvermeer/temp/*" &gt; /home/lvermeer/result.csv
</code></pre>

blocks|key|46223|text|这个问题我可能来晚了，但我可以帮你回答：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|46224|echo+"COL_NAME1%7CCOL_NAME2%7CCOL_NAME3%7CCOL_NAME4“>+SAMPLE_Data.csv配置单元-e‘select+distinct+concat(COL_1，"%7C"，COL_2，"%7C"，COL_3，"%7C"，COL_4)+from+table_Name+where子句；’>>+SAMPLE_Data.csv|46225|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

blocks|key|262803|text|此外壳命令将csv格式的输出格式打印到output.txt，但不带列标题。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|262804|$+hive+--outputformat=csv2+-f+'hivedatascript.hql'+--hiveconf+hive.cli.print.header=false+>+output.txt|code-block|syntax|javascript|262805|entityMap^0|J|A|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

This shell command prints the output format in csv to <code>output.txt</code> without the column headers. 

<pre><code>$ hive --outputformat=csv2 -f 'hivedatascript.hql' --hiveconf hive.cli.print.header=false &gt; output.txt
</code></pre>

we would like to put the results of a Hive query to a CSV file. I thought the command should look like this:

<pre><code>insert overwrite directory '/home/output.csv' select books from table;
</code></pre>

When I run it, it says it completeld successfully but I can never find the file. How do I find this file or should I be extracting the data in a different way?

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

Python

Hive

HDFS

我们希望将配置单元查询的结果放到CSV文件中。我认为命令应该是这样的：insert overwrite directory '/home/output.csv' select books from table;当我运行它时，它说它成功完成了，但我永远找不到文件。我如何找到这个文件，或者我应该以不同的方式提取数据？

问如何将HiveQL查询的结果输出到CSV？
EN

回答 14

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何将HiveQL查询的结果输出到CSV？EN

回答 14

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何将HiveQL查询的结果输出到CSV？
EN