Hive导出复杂数据到csv文件

工作中经常遇到使用Hive导出数据到文本文件供数据分析时使用。Hive导出复杂数据到csv等文本文件时，有时会遇到以下几个问题：

导出的数据只有数据没有列名。
导出的数据比较复杂时，如字符串内包含一些制表符、换行符等。直接导出后，其它程序无法对数据进行正常的分割。若直接使用管道符号和sed指令的话，会导致分列出错。
数据分析师使用数据时使用R语言，加载数据时如果一个字段只有单引号或双引号时，会导致后续数据读为一行。
导出数据时空值在文本显示为N,不是NULL。
hive导出的数据生成若干个000000_0，000001_0这样的文件，可读性比较差，需要手工去合并

针对上述几个问题采取以下解决方案：

导出的数据不包含列名，使用hive -e 'use mydb;SET hive.cli.print.header=true; SELECT * FROM t_test LIMIT 0'方式获取列名。若抓取的为多张表的字段，需要对查询语句进行相应的调整。
根据具体的情况设置合适的分隔符，我这里使用一般极少用到的符号 '|' 作为分隔符。
对于出现的单引号和双引号，在经过分析师沟通后，对这些无实际意义的一个字段只有单引号或双引号的字段置空。若有其它需求也可以根据具体情况进行替换。
使用sed指令对N字段进行替换。根据具体情况，注意确保不修改到字符串中的N。可以把'|N|'替换为'|NULL|'，行首行尾的数据也要考虑到。我这里由于其它字段不包含'N'，就简化处理了。
000000_0文件聚合使用脚本进行解决。

下面为一个具体的示例的shell脚本:

 1 #!/bin/bash
 2 mkdir -p  /tmp/project_1010/project  #初始化结果存放的目录
 3 hive -e "use mydb;
 4 insert overwrite local directory '/tmp/project_1010/t_test/'   #指定该表初步查询结果存放的目录
 5 ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'    #指定字段分隔符
 6 select t.* from t_test t JOIN　　#查询语句
 7 t_test1 t1
 8 on t1.test_id = t.test_id;
 9 " 
10 # 生成表头，替换表头前的't_test.'字段，并写入最终的csv文件中
11 hive  -e 'use mydb;SET hive.cli.print.header=true; SELECT * FROM t_test LIMIT 0' | sed -e 's/	/|/g;s/t_test.//g'  > /tmp/project_1010/project/t_test.csv 
12 cat /tmp/project_1010/t_test/* >> /tmp/project_1010/project/t_test.csv #把000000_0，000001_0这样的文件通过追加的方式，写入最终的csv文件中
13 sed -i 's/\N/NULL/g' /tmp/project_1010/project/t_test.csv    #使用sed处理最终的csv文件，根据需求进行替换
14 sed -i "s/|'|/|NULL|/g" /tmp/project_1010/project/t_test.csv
15 sed -i 's/|"|/|NULL|/g' /tmp/project_1010/project/t_test.csv

本脚本假设抓取项目project中t_test表的数据。考虑到需求的变动，每次生产的项目目录加一个日期后缀，如“project_1010”。一般一个项目会抓取对个表，针对每个表都会创建一个子目录存储生产的000000_0，000001_0等数据。示意图如下：

 1 project_1010/
 2 ├── project  #项目名
 3 │   ├── t_test.csv
 4 │   ├── t_test1.csv
 5 ├── t_test   #抓取的t_test表数据
 6 │   ├── 000000_0
 7 │   └── 000001_0
 8 ├── t_test1  #抓取的t_test1数据
 9 │   ├── 000000_0
10 │   └── 000001_0

先抓取每张需要的表的数据，最后统一汇总在project的目录下，最终的数据就存储在project目录下。

在实际的使用环境中，可以再写shell脚本和一个配置文件（包括项目名、抓取的表的列表、需要执行的查询语句）直接生成上述的脚本，不需要一个个的手动生成。