介绍
做数据分析的时候,经常会用到hive -e "sql" > xxx.txt
或者最原始的hive命令行来获得查询结果,然后再将查询结果放到Excel
等工具中,但是如果查询的字段太多,这时候将查询结果放到Excel
会经常会碰到错位问题,很是头疼.
解决方案一:借助linux管道替换输出分隔符
样例如下:
# 方法一:sed hive -e "select * from db.table_name" | sed 's/\t/,/g' > ./abc.txt # 方法二:tr hive -e "select * from db.table_name" | tr "\t" ","
结果查看如下:
$ cat abc.txt
解决方案二:借助Hive的insert
语法
代码如下:
insert overwrite local directory 'path' row format delimited fields terminated by ',' select xxxx from xxxx;
上面的sql
将会把查询结果写到指定
目录中,字段之间以‘,’
分隔
结果如下:
$ ls path
000000_0
官方介绍:
Standard syntax:
INSERT OVERWRITE [LOCAL] DIRECTORY directory1
[ROW FORMAT row_format] [STORED AS file_format] (Note: Only available starting with Hive
0.11.
0)
SELECT ... FROM ...
Hive extension (multiple inserts):
FROM from_statement
INSERT OVERWRITE [LOCAL] DIRECTORY directory1 select_statement1
[INSERT OVERWRITE [LOCAL] DIRECTORY directory2 select_statement2] ...
row_format
: DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
[NULL DEFINED AS char] (Note: Only available starting with Hive
0.13)
发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/119632.html原文链接:https://javaforall.cn
【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛
【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...