如何将Hive仓库中的查询结果导出到本地文件

ゝ一纸荒年。 2022-05-17 03:46 229阅读 0赞

我在hive仓库中有个表，已经使用HQL跑出来结果，存到了hive中。表的schema如下

show create table test_xiaoxiannv.dw_clickadvert_daily;
    CREATE EXTERNAL TABLE test_xiaoxiannv.dw_clickadvert_daily(
      id int, 
      ip string, 
      time string, 
      action string, 
      dm string, 
      pnum int, 
      a0 string, 
      a1 string, 
      a2 string, 
      a3 string, 
      a4 string, 
      a5 string, 
      a6 string, 
      a7 string, 
      a8 string, 
      a9 string, 
      a10 string, 
      a11 string, 
      a12 string, 
      a13 string, 
      a14 string, 
      a15 string)
    PARTITIONED BY ( 
      dt date)
    ROW FORMAT DELIMITED 
      FIELDS TERMINATED BY ',' 
    STORED AS INPUTFORMAT 
      'org.apache.hadoop.mapred.TextInputFormat' 
    OUTPUTFORMAT 
      'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
    LOCATION
      'hdfs://kingsoft-ha/apps/hive/warehouse/test_xiaoxiannv.db/dw_clickadvert_daily'
    TBLPROPERTIES (
      'transient_lastDdlTime'='1533263442')

可以看到，分隔符是逗号,  
那么导出成CSV文件就很方便了，操作步骤如下：

使用 \{HIVE\_HOME\}/bin/hive -e “HQL” >> target\_file\_path.csv | 或者使用 \{HIVE\_HOME\}/bin/hive -f sql.q >> target\_file\_path.csv

[hive@tony-client ~]$ hive -e "select * from test_xiaoxiannv.dw_clickadvert_daily" >> ~/test_xiaoxiannv.dw_clickadvert_daily.csv
    
    Logging initialized using configuration in file:/etc/hive/2.5.0.0-1245/0/hive-log4j.properties
    OK
    Time taken: 3.09 seconds, Fetched: 322914 row(s)

\[hive@tony-client ~\]$ wc -l ~/test\_xiaoxiannv.dw\_clickadvert\_daily.csv  
322914 /home/hive/test\_xiaoxiannv.dw\_clickadvert\_daily.csv

最后在xshell中使用sz下载到本地(Windows)  
\[hive@tony-client ~\]$ sz ~/test\_xiaoxiannv.dw\_clickadvert\_daily.csv

另外，关于分隔符的问题，可参考这哥们的博文：[https://blog.csdn.net/azhao\_dn/article/details/6921423][https_blog.csdn.net_azhao_dn_article_details_6921423]

[https_blog.csdn.net_azhao_dn_article_details_6921423]: https://blog.csdn.net/azhao_dn/article/details/6921423