导出为直线配置单元中的csv [英] Export as csv in beeline hive

问题描述

我试图将我的配置单元表作为直线配置单元中的csv导出。当我运行命令！sql select * from database1> /user/bob/output.csv 它给了我语法错误。

我已经使用以下命令成功连接到数据库。

  beeline -u'jdbc：hive2：// [databaseaddress]'--outputformat = csv 
  

另外，文件结尾不是很清楚。它应该是hdfs中的文件路径是否正确？

解决方案

当hive版本至少为0.11.0时，您可以执行：

  INSERT OVERWRITE LOCAL DIRECTORY'/ tmp / directoryWhereToStoreData'
 ROW FORMAT DELIMITED 
 FIELDS TERMINATED BY'，'
线路终止于\\\

 SELECT * FROM yourTable; 
  

来自hive / beeline将表格存储到本地目录文件系统。

---

或者，使用直线，将您的SELECT查询保存到您的SQLFile.sql中并运行：

  beeline -u'jdbc：hive2：// [databaseaddress]'--outputformat = csv2 -f yourSQlFile.sql> theFileWhereToStoreTheData.csv 
  

另外，这会将结果存储到文件中本地文件系统。

---

从配置单元，将数据存储到HDFS中：

  CREATE EXTERNAL TABLE输出
 LIKE yourTable 
 ROW FORMAT DELIMITED 
 FIELDS TERMINATED BY'，'
 LINES TERMINATED BY' \\\
'
 LOCATION'hfds：// WhereDoYou / Like'; 
 
 INSERT OVERWRITE TABLE输出SELECT * from yourTable; 
  

然后您可以使用以下方式将数据收集到本地文件：

  hdfs dfs -getmerge / WhereDoYou / Like 
  

这是另一种使用直线获取数据的选项：

  env HADOOP_CLIENT_OPTS = -  Ddisable.quoting.for.sv = falsebeeline -ujdbc：hive2：//your.hive.server.address：10000 /--incremental = true --outputformat = csv2 -eselect * from youdatabase.yourtable
  





---

处理：

 连接到：Apache Hive（版本1.1.0-cdh5.10.1）
驱动程序：Hive JDBC（版本1.1.0-cdh5.10.1）
事务隔离：TRANSACTION_REPEATABLE_READ 
 Apache Hive的直线版本1.1.0-cdh5.10.1 
  

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

Also, not very clear where the file ends up. It should be the file path in hdfs correct?

解决方案

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 "\n"
SELECT * FROM yourTable;

from hive/beeline to store the table into a directory on the local filesystem.

---

Alternatively, with beeline, save your SELECT query in yourSQLFile.sql and run:

beeline -u 'jdbc:hive2://[databaseaddress]' --outputformat=csv2 -f yourSQlFile.sql > theFileWhereToStoreTheData.csv 

Also this will store the result into a file in the local file system.

---

From hive, to store the data somewhere into HDFS:

CREATE EXTERNAL TABLE output 
LIKE yourTable 
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
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" 

---

Working on:

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屋！