当前位置:首页 » Linux技术知识

Hive导出复杂数据到csv文件

2017-10-10 19:01 本站整理 浏览(0)

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

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

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

  1. 导出的数据不包含列名,使用hive -e 'use mydb;SET hive.cli.print.header=true; SELECT * FROM t_test LIMIT 0'方式获取列名。若抓取的为多张表的字段,需要对查询语句进行相应的调整。
  2. 根据具体的情况设置合适的分隔符,我这里使用一般极少用到的符号 '|' 作为分隔符。
  3. 对于出现的单引号和双引号,在经过分析师沟通后,对这些无实际意义的一个字段只有单引号或双引号的字段置空。若有其它需求也可以根据具体情况进行替换。
  4. 使用sed指令对\N字段进行替换。根据具体情况,注意确保不修改到字符串中的\N。可以把'|\N|'替换为'|NULL|',行首行尾的数据也要考虑到。我这里由于其它字段不包含'\N',就简化处理了。
  5. 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/\t/|/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脚本和一个配置文件(包括项目名、抓取的表的列表、需要执行的查询语句)直接生成上述的脚本,不需要一个个的手动生成。