使用自定义分隔符将数据加载到 Hive [英] Load data into Hive with custom delimiter

问题描述

我正在尝试在 hive 中创建一个内部(托管)表来存储我的增量日志数据.表格是这样的:

I'm trying to create an internal (managed) table in hive that can store my incremental log data. The table goes like this:

CREATE TABLE logs (foo INT, bar STRING, created_date TIMESTAMP)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '<=>'
STORED AS TEXTFILE;

我需要定期将数据加载到这个表中.

I need to load data into this table periodically.

LOAD DATA INPATH '/user/foo/data/logs' INTO TABLE logs;

但是数据没有正确插入表中.分隔符可能有问题.找不到原因.

But the data is not getting inserted into the table properly. There might be some problem with the delimiter.Can't find why.

示例日志行:

120<=>abcdefg<=>2016-01-01 12:14:11

在select * from logs;我明白了，

120  =>abcdefg  NULL

第一个属性很好，第二个包含分隔符的一部分，但因为它是被插入的字符串，第三个将为空，因为它需要日期时间.

first attribute is fine, the second contains a part of delimiter but since it's string that is getting inserted and third will be null since it expects date time.

任何人都可以帮忙提供自定义分隔符并成功加载数据.

Can anyone please help on how to provide custom delimiters and load data successfully.

推荐答案

默认情况下，hive 只允许用户使用单个字符作为字段分隔符.尽管有 RegexSerDe 来指定多字符分隔符，但使用起来可能会令人生畏，尤其是对于业余爱好者.

By default, hive only allows user to use single character as field delimiter. Although there's RegexSerDe to specify multiple-character delimiter, it can be daunting to use, especially for amateurs.

补丁 (HIVE-5871) 添加了一个新的 SerDe 名为 MultiDelimitSerDe.使用 MultiDelimitSerDe，用户可以在创建表时指定多字符字段分隔符，其方式与典型的表创建最相似.

The patch (HIVE-5871) adds a new SerDe named MultiDelimitSerDe. With MultiDelimitSerDe, users can specify a multiple-character field delimiter when creating tables, in a way most similar to typical table creations.

hive> CREATE TABLE logs (foo INT, bar STRING, created_date TIMESTAMP)
    > ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.MultiDelimitSerDe' 
    > WITH SERDEPROPERTIES ("field.delim"="<=>")
    > STORED AS TEXTFILE;

hive> dfs -put /home/user1/multi_char.txt /user/hive/warehouse/logs/. ;

hive> select * from logs;
OK
120 abcdefg 2016-01-01 12:14:11
Time taken: 1.657 seconds, Fetched: 1 row(s)
hive> 

这篇关于使用自定义分隔符将数据加载到 Hive的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！