如何将HiveQL查询结果输出为CSV格式？

Question

我们想将Hive查询结果放入CSV文件中。 我认为命令应该像这样：

我们希望将Hive查询结果导出为CSV文件。我认为命令应该是这样的:

INSERT OVERWRITE LOCAL DIRECTORY '/path/to/directory' ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' SELECT * FROM table_name;

insert overwrite directory '/home/output.csv' select books from table;

当我运行它时，它显示已经成功完成，但我却无法找到该文件。我该如何找到这个文件或者说我应该以不同的方式提取数据？

- AAA

Answer 1

虽然可以使用 INSERT OVERWRITE 从 Hive 中提取数据，但这可能不是适合你的情况的最佳方法。首先，让我解释一下 INSERT OVERWRITE 的作用，然后我将描述我从 Hive 表中获取 tsv 文件的方法。

根据手册，你的查询将把数据存储在 HDFS 的一个目录中。格式将不是 csv。

写入到文件系统的数据会序列化为文本，其中以^A分隔列，并以换行符分隔行。如果任何列不是原始类型，则这些列将序列化为 JSON 格式。

稍作修改（加上 LOCAL 关键字）将数据存储在本地目录中。

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

当我运行类似的查询时，输出结果如下。

[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

就我个人而言，我通常会在命令行上直接通过Hive运行查询，并将结果导入本地文件中：

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

这为我提供了一个可以使用的制表符分隔文件。希望对您也有用。

根据此补丁-3682，我怀疑在使用Hive 0.11时有更好的解决方案，但我无法自行测试。新语法应该允许以下操作。

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

- Lukas Vermeer

---

你知道在insert overwrite local和piping之间有什么性能差异吗？在哪个大概的数据量级别会出现问题？此外，piping可以保证你得到一个文件，而另一种方法会给我们一个目录，可能需要合并。 - fd8s0

能否将HDFS中的数据导出为Sequence文件格式？ - Nageswaran

我尝试了解决方案(patch-3682)，对我来说效果很好，但由于某种原因，输出文件没有包括标题。请注意，我已经在我的.hiverc中设置了hive.cli.print.header=true;。值得一提的是，标题被打印到终端上(这显然不是我想要的)。 - Peter Cogan

@lukas-vermeer，当您使用“INSERT OVERWRITE”方法创建表时，标题信息会丢失。有没有办法获取标题信息？ - ML_Passion

嗨Lukas，你是如何让你的shell在Hadoop文件系统中运行的？ - notilas

Answer 2

如果您想要一个CSV文件，那么您可以按照以下方式修改Lukas的解决方案（假设您在Linux系统上）：

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

- David Kjerrumgaard

---

谢谢。我正在使用一个变体，它非常有效。请注意，这将输出逗号分隔的内容，并不一定是某些人认为的 CSV 格式。CSV 通常有一些格式处理逗号分隔的数据（例如，用双引号包装数据，并在数据中使用双倍双引号）。值得一提的是，添加“--hiveconf hive.cli.print.header=True”参数也将在输出中获取标题。 - jatal

这是最干净的解决方案。 - Dutta

这对我来说失败了，例如，一个日期时间字符串在日期和时间之间有一个空格。 - williaster

@williaster sed 's/\t+/,/g' 这应该有助于解决这个问题。 - Sudhakar Chavan

如果 TSV 中包含逗号的文本，这种方法就行不通了。（因为未被引用的逗号会被视为分隔符） - yahiaelgamal

Answer 3

我发现这是输出HiveQL结果最友好的csv方式。
你不需要使用任何grep或sed命令来格式化数据，而是可以使用hive支持的方法，只需要添加额外的outputformat标签即可。

hive --outputformat=csv2 -e 'select * from <table_name> limit 20' > /path/toStore/data/results.csv

- Rishabh Sachdeva

---

需要使用特定版本吗？对我一点用都没有。 hive --outputformat=csv2 -e 'select * from bla' > test.csv 的输出为 -> 在Hive 2.1.1-cdh6.3.3上出现无法识别选项: --outputformat=csv2。 - PandaWood

@PandaWood - 是的，它需要3.1+。或者，可以尝试使用相同参数的beeline。 - d-_-b

Answer 4

您可以使用INSERT … DIRECTORY …，例如：

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

< p > OVERWRITE 和 LOCAL 的解释与以前相同，路径根据通常的规则进行解释。根据调用的 reducer 数量，将写入一个或多个文件到 /tmp/ca_employees。

- bigmakers

Answer 5

你应该使用CREATE TABLE AS SELECT（CTAS）语句，在HDFS中创建一个目录，其中包含查询结果的文件。之后，您将不得不将这些文件从HDFS导出到您的常规磁盘并将它们合并为单个文件。

您还可能需要进行一些技巧来将文件从'\001'分隔符转换为CSV。您可以使用自定义CSV SerDe或对提取的文件进行后处理。

- Olaf

---

如果想在后续的Oozie管道步骤中使用输出，这种方法是最好的。 - cerd

Answer 6

我在寻找类似的解决方案，但这里提到的解决方案都不起作用。我的数据包含各种空格（空格、换行、制表符）字符和逗号。

为了使列数据安全地保存为tsv文件，我将列数据中的所有\t字符替换为一个空格，并在命令行上执行Python代码以生成csv文件，如下所示：

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)")'

这创建了一个完全有效的csv。希望这能帮助那些寻求此解决方案的人。

- sisanared

---

现在已经是2016年了，我们仍然需要费尽周折才能做到这一点吗？我发现shravster的解决方案是迄今为止最好、最优雅的解决方案。 - Josh

你是如何替换列数据中的所有 \t 字符的？你是在查询中处理它还是创建了一个单独的视图来解决它？ - Naresh S

@NareshS，抱歉回复晚了。是的，在Hive中处理了列，用空格替换制表符，或者如果它们是必需的，您可以用<：tab>之类的替代品进行替换。 - sisanared

@sisanared，谢谢您的回复。我明白我们需要对所有字符串列使用正则表达式替换，如果我们有大量 >100个列的表格，这将是繁琐的。针对这种情况，是否有快速的解决方案？ - Naresh S

@NareshS，不幸的是，另一个解决方案就是在将数据放入分区之前清理数据。否则，您将不得不在执行可能包含制表符的所有字符串列的选择时进行清理。 - sisanared

@sisanared，感谢您的回复。 - Naresh S

Answer 7

如果您正在使用HUE，这也非常简单。只需进入HUE中的Hive编辑器，执行您的Hive查询，然后将结果文件保存在本地为XLS或CSV格式，或者您可以将结果文件保存到HDFS。

- Ray

Answer 8

我曾遇到类似的问题，以下是我的解决方法。

第一步 - 将Hive表中的数据加载到另一个表中，方法如下：

DROP TABLE IF EXISTS TestHiveTableCSV;
CREATE TABLE TestHiveTableCSV 
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n' AS
SELECT Column List FROM TestHiveTable;

步骤2 - 使用适当的扩展名将 blob 从 Hive 仓库复制到新位置

Start-AzureStorageBlobCopy
-DestContext $destContext 
-SrcContainer "Source Container"
-SrcBlob "hive/warehouse/TestHiveTableCSV/000000_0"
-DestContainer "Destination Container"
-DestBlob "CSV/TestHiveTable.csv"

- Dattatrey Sindol

Answer 9

hive  --outputformat=csv2 -e "select * from yourtable" > my_file.csv

或者

hive  --outputformat=csv2 -e "select * from yourtable" > [your_path]/file_name.csv

对于tsv格式，只需在上述查询中将csv更改为tsv并运行您的查询。 最初的回答。

- Terminator17

Answer 10

您可以使用Hive字符串函数CONCAT_WS(字符串分隔符，字符串str1，字符串str2... strn) 例如：

hive -e 'select CONCAT_WS(',',cola,colb,colc...,coln) from Mytable' > /home/user/Mycsv.csv

- Ram Ghadiyaram