MongDB同步数据到Hive

1.问题

如何将mongodb中的数据导入hive中？

2.解决方案

步骤：

1. 使用mongoexport导出CSV数据文件
2. 在Hive中创建表（注意指定表serde格式）
3. 使用load data 命令导入csv数据文件

2.1 导出csv文件

mongoexport 常用命令参数如下：

mongoexport <options>

  -h, --host=<hostname>                             mongodb ip地址
      --port=<port>                                 端口

  -u, --username=<username>  用户名
  -p, --password=<password>  密码                 

  -d, --db=<database-name>                        数据库
  -c, --collection=<collection-name>              表名

  -f, --fields=<field>[,<field>]*                 导出字段,用逗号分隔(导出为csv文件才需要指定) e.g. -f "name,age"
      --fieldFile=<filename>                      指定字段文件路径，将字段写到txt文件中，每个字段占一行
      --type=<type>                               指定导出文件的格式（json or csv）,默认为json
  -o, --out=<filename>                            导出文件路径 

  -q, --query=<json>                              查询过滤条件,JSON字符串格式 e.g.'{x:{$gt:1}}'
      --queryFile=<filename>                      包含查询过滤条件(JSON格式)的文件路径

      --skip=<count>                              导出跳过的行数
      --limit=<count>                             导出行数限制条件

从以上参数可以看出mongoexport可以导出两种文件格式：CSV 和 JSON

什么是csv文件

CSV(Comma-Separated Values)–逗号分隔值文件格式
其文件以纯文本形式存储表格数据（数字和文本）。记录间以某种换行符分隔；每条记录由字段组成，字段间的分隔符是其它字符或字符串，最常见的是逗号或制表符。

例子

• 导出字段较少时采用 –fields

mongoexport --host 192.168.1.231 --port 27100 --db mzk_spiders --collection 'tencent.song.stable' --username readonly --password xxxx --type=csv --fields _id,ctime,publishCompany --out /home/hadoop/BigData/data/tencent_song_2017_03_29.csv

• 导出字段较多时采用 –fieldFile 文件中每个字段独占一行，参见附录

mongoexport --host 192.168.1.231 --port 27100 --db mzk_spiders --collection 'tencent.song.stable' --username readonly --password xxx  --type=csv --fieldFile /home/hadoop/BigData/data/tencent_song_stable_fields_2017_03_29.txt --out /home/hadoop/BigData/data/tencent_song_2017_03_29.csv  

• 导出行数限制 –limit

mongoexport --host 192.168.1.231 --port 27100 --db mzk_spiders --collection 'tencent.song.stable' --username readonly --password xxxx --type=csv --fieldFile /home/hadoop/BigData/data/tencent_song_stable_fields_2017_03_29.txt --out /home/hadoop/BigData/data/tencent_song_2017_03_29.csv  --limit 1000

导出文件样例

_id,ctime,publishCompany,trackNumber,name,artists.0.name,language,originalName,genre,utime,publishTime,sizeogg,size_flac,size_ape,size320,size128,offline,bpm,playNum,translationName.0,extInfo.resultJson.data.0.status,extInfo.resultJson.data.0.interval,extInfo.resultJson.data.0.fnote,extInfo.resultJson.data.0.language,extInfo.resultJson.data.0.genre,extInfo.resultJson.data.0.type,extInfo.resultJson.data.0.index_cd,extInfo.resultJson.data.0.index_album,extInfo.resultJson.data.0.subtitle,extInfo.resultJson.data.0.isonly,extInfo.resultJson.data.0.label,albums.0.name,albums.0._id
109153388,1478960157298,,11,Great Balls of Fire / Whole Lotta Shakin',Cliff Richard,,Great Balls of Fire / Whole Lotta Shakin',,1490688779017,2016-11-11,,0,0,,,0,162,2112,热情火焰 / 众人摇摆,0,243,4009,5,36,0,0,11,,1,4611686018427387984,Just... Fabulous Rock 'n' Roll,1682124
109145868,1478960156536,,5,エレベーターガール (电梯小姐),BURNOUT SYNDROMES (バーンアウトシンドローム),,エレベーターガール,,1490688779082,2016-11-09,,0,0,,,0,130,40,电梯小姐,0,252,8013,3,55,0,0,5,,0,4611686018427387908,檸檬,1681219
109145873,1478960156550,,10,人工衛星,BURNOUT SYNDROMES (バーンアウトシンドローム),,人工衛星,,1490688779116,2016-11-09,,0,0,,,0,176,44,,0,273,8013,3,55,0,0,10,,0,4611686018427387908,檸檬,1681219
109135793,1478960158338,华纳唱片,6,My Gospel,Charlie Puth,英语,My Gospel,Pop,1490688779120,2016-11-11,,0,0,,,0,56,164965,我的福音,0,210,4009,5,1,0,0,6,,1,4611686018435776528,Nine Track Mind Deluxe,1680115

2.2 创建Hive表

CREATE TABLE tencent_song_tmp (
    id BIGINT,
    ctime BIGINT,
    publishCompany string,
    trackNumber INT,
    NAME string,
    artists string,
    LANGUAGE string,
    originalName string,
    genre string,
    utime BIGINT,
    publishTime string,
    sizeogg INT,
    size_flac INT,
    size_ape INT,
    size320 INT,
    size128 INT,
    offline INT,
    bpm INT,
    playNum INT,
    translationName string,
    in_status INT,
    in_interval INT,
    in_fnote INT,
    in_language INT,
    in_genre INT,
    in_type INT,
    in_index_cd INT,
    in_index_album INT,
    in_subtitle string,
    in_isonly INT,
    in_label string,
    in_name string,
    in_id string
) ROW format serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde' WITH SERDEPROPERTIES (
    "separatorChar" = ",",
    "quotechar" = "\""
) STORED AS TEXTFILE;

2.3 load data 导入数据

load data (local) inpath 'filepath' (overwrite) into table tablename (partition xxxx)

• load data local inpath xxx

使用带有local的命令方式导入数据,filepath路径为本地文件路径

• load data inpath xxx

使用该命令导入数据,filepath路径为HDFS文件系统路径

例子

load data local inpath '/home/hadoop/BigData/data/tencent_song_2017_03_29.csv' into table DEFAULT.tencent_song_tmp;

3. 查询Hive表

在Hive查询导入表，检查数据是否正确

select * from tencent_song_tmp;

4. 踩坑事项

4.1 mongodb 表中字段含有特殊字符

• 字段中含有逗号

因为CSV文件采用逗号分隔，导致数据导入hive表后，查询结果时Hive解析文件时出现错误。

• 字段中含有换行符

字段中含有换行符”\n”,从mongodb中导出csv文件后,文件能够load到Hive中,但是在查询该表时,因为建表时指定了解析方式为org.apache.hadoop.hive.serde2.OpenCSVSerde,是按照标准的csv文件格式解析(按行解析，逗号分隔字段),如果字段中有\n会导致换行，从而导致Hive解析内容出错。

4.2 mongoexport 导出csv文件含有表头

在导出csv文件时不能指定取消导出表头，故在load data之前或者之后需要处理表头

附录 fieldFile文件格式样例

字段文件 tencent_song_stable_fields_2017_03_29.txt

_id
ctime
publishCompany
trackNumber
name
artists.0.name
language
originalName
genre
utime
publishTime
sizeogg
size_flac
size_ape
size320
size128
offline
bpm
playNum
translationName.0
extInfo.resultJson.data.0.status
extInfo.resultJson.data.0.interval
extInfo.resultJson.data.0.fnote
extInfo.resultJson.data.0.language
extInfo.resultJson.data.0.genre
extInfo.resultJson.data.0.type
extInfo.resultJson.data.0.index_cd
extInfo.resultJson.data.0.index_album
extInfo.resultJson.data.0.subtitle
extInfo.resultJson.data.0.isonly
extInfo.resultJson.data.0.label
albums.0.name
albums.0._id