Hive笔记之导出查询结果

一、导出到本地

导出查询结果到本地：

INSERT OVERWRITE LOCAL DIRECTORY "/tmp/hive-result/t_visit_video"
SELECT * FROM t_visit_video ;

导出到的本地路径不必已经存在，会自动创建父目录，导出的查询结果会是一个文件夹，文件夹下存放着本次查询的结果，如果结果集比较大的话会分块存放。

 

每个数据块还会有一个CRC校验文件，此文件为隐藏文件，用于校验此块的数据有效性。

但是当查看导出的数据文件时发现悲剧了，列与列之间好像是没有东西分隔啊：

其实是有东西分隔的，这个字符就是^A，只不过这是一个不可见字符，这个字符在vim中可见，使用vim编辑一下它（或者使用cat -A）：

看到了，列与列之间确实是有一个^A分隔符的。

如果不希望使用^A作为分隔符，可以在导出数据的时候使用ROW FORMAT DELIMITED FIELDS TERMINITED BY ","来指定列与列之间的分隔符，来重新导出一下：

INSERT OVERWRITE LOCAL DIRECTORY "/tmp/hive-result/t_visit_video_002"
ROW FORMAT DELIMITED FIELDS TERMINATED BY ","
SELECT * FROM t_visit_video ;

再查看一下导出的本地文件，发现列与列之间的分隔符是逗号了：

也许你会认为自己已经得到了CSV格式，如果这样的话就踩坑了，因为它并不符合CSV的RFC4180。简单地来证明一下，在上面的表插入一行巨多逗号然后重新导出：

INSERT INTO t_visit_video PARTITION (day="20180604") SELECT "foo,foo,foo", "bar,bar,bar";
INSERT OVERWRITE LOCAL DIRECTORY "/tmp/hive-result/t_visit_video_003"
ROW FORMAT DELIMITED FIELDS TERMINATED BY ","
SELECT * FROM t_visit_video ;

查看导出结果：

 

注意看最后一行，如果按照CSV的格式去解析的话最后得到的结果一定是错的，一定要确保所指定的列分隔符不包含在列数据中出现，这是在导出数据指定分隔时需要注意的一个坑。

Hive导出数据时指定分隔符的语法长的令人发指，说实话我是记不住的，这里可以耍个小聪明，可以先将数据按照默认的分隔符^A导出，然后使用tr将^A替换为想要的分隔符：

注意这个^V是先按Ctrl+V，告诉终端我下一个字符要输入一个特殊字符，然后按CTRL+A打出。

注：使用默认分隔符导出的Hive查询结果在程序中处理的时候使用split("x01")或者split("01")来切分列。

二、导出到HDFS

导出到HDFS跟导出到本地差不多，只是没有LOCAL，加LOCAL后面的是本地路径，否则的话就是HDFS路径：

INSERT OVERWRITE DIRECTORY "/test/hive-export/t_visit_video" 
SELECT * FROM t_visit_video; 

查看HDFS上导出的目录：

[root@foobar ~]# hadoop fs -ls /test/hive-export/t_visit_video
Found 1 items
-rwxr-xr-x   1 root supergroup        283 2018-06-08 00:04 /test/hive-export/t_visit_video/000000_0

和导出到本地一样，只不过是位置换到了HDFS而已。

同样的，导出到HDFS也可以指定列分隔符：

INSERT OVERWRITE DIRECTORY "/test/hive-export/t_visit_video_002" 
ROW FORMAT DELIMITED FIELDS TERMINATED BY ","
SELECT * FROM t_visit_video;

查看列分隔符是否设置正确：

[root@foobar ~]# hadoop fs -ls /test/hive-export/t_visit_video_002
Found 1 items
-rwxr-xr-x   1 root supergroup        283 2018-06-08 00:12 /test/hive-export/t_visit_video_002/000000_0
[root@foobar ~]# hadoop fs -cat /test/hive-export/t_visit_video_002/000000_0
张三,大唐双龙传,20180516
李四,天下无贼,20180516
张三,神探狄仁杰,20180516
李四,霸王别姬,20180516
李四,霸王别姬,20180516
王五,机器人总动员,20180516
王五,放牛班的春天,20180516
王五,盗梦空间,20180516
foo,foo,foo,bar,bar,bar,20180604

 

三、导出到其它Hive表

导出到其它表的语法和导出到目录类似，只是目的地变成了表名，如果目标表是个分区表的话还要指定所要插入的分区。

下面是一个简单的例子，先复制一份表结构，然后将其中一个分区的数据拷贝一份：

CREATE TABLE t_visit_video_20180516 LIKE t_visit_video;
INSERT OVERWRITE TABLE t_visit_video_copy PARTITION (day="20180516") 
SELECT * FROM t_visit_video WHERE day="20180516";

同样的，这里可以使用INTO表示追加到指定的分区，也可以使用OVERWRITE表示覆盖指定分区。

上面的方式适用于表已经存在的情况，如果想使用一个新表来保存查询结果但是又不想手动创建的话，可以让其自动创建表结构:

CREATE TABLE t_visit_video_20180516 AS SELECT * FROM t_visit_video WHERE day='20180516';

这种方法常用于将查询结果集导出为临时表时使用。

四、定时查询并备份结果集

hive -e可以用来指定一些命令，hive -f可以用来指定一个脚本文件，可以将导出脚本的逻辑写个小脚本，借助于crontab定时执行此脚本，即可实现对Hive表查询并备份。

下面是一个小小的例子，定时将hive表前一天的张三看过的电影导出到某个目录下，同时记录操作日志：

#! /bin/bash

day=`date '+%Y%m%d' -d 'day ago'`
if [ $1 ]; then 
        day=$1
fi
hive="/opt/hive/apache-hive-2.3.3-bin/bin/hive"
dest_dir="/tmp/hive-result/t_visit_video_$day"
log_file="`dirname $0`/backup.log"

echo "[`date '+%F %T'`] $day begin bakcup" >> $log_file
hive -e "INSERT OVERWRITE LOCAL DIRECTORY '$dest_dir' SELECT * FROM test_003.t_visit_video WHERE day='$day' AND username='张三'"
echo "[`date '+%F %T'`] $day begin end" >> $log_file

将上面的脚本加入到crontab即可实现定时导出查询结果：

0 1 * * * /root/hive/backup/backup.sh

.