在直线蜂巢中导出为 csv [英] Export as csv in beeline hive
问题描述
我正在尝试将我的配置单元表导出为直线配置单元中的 csv.当我运行命令 !sql select * from database1 >/user/bob/output.csv
它给了我语法错误.
I am trying to export my hive table as a csv in beeline hive. When I run the command !sql select * from database1 > /user/bob/output.csv
it gives me syntax error.
此时我已使用以下命令成功连接到数据库.查询在控制台输出正确的结果.
I have successfully connected to the database at this point using the below command. The query outputs the correct results on console.
beeline -u 'jdbc:hive2://[databaseaddress]' --outputformat=csv
另外,不太清楚文件的最终位置.应该是hdfs中的文件路径正确吗?
Also, not very clear where the file ends up. It should be the file path in hdfs correct?
推荐答案
当 hive 版本至少为 0.11.0 时,您可以执行:
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 "
"
SELECT * FROM yourTable;
从 hive/beeline 将表存储到本地文件系统上的目录.
from hive/beeline to store the table into a directory on the local filesystem.
或者,使用直线,将 SELECT 查询保存在 yourSQLFile.sql 中并运行:
Alternatively, with beeline, save your SELECT query in yourSQLFile.sql and run:
beeline -u 'jdbc:hive2://[databaseaddress]' --outputformat=csv2 -f yourSQlFile.sql > theFileWhereToStoreTheData.csv
此外,这会将结果存储到本地文件系统中的文件中.
从 hive,将数据存储到 HDFS:
From hive, to store the data somewhere into HDFS:
CREATE EXTERNAL TABLE output
LIKE yourTable
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LINES TERMINATED BY '
'
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"
<小时>
工作:
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
这篇关于在直线蜂巢中导出为 csv的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!