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

问题描述

我试图在存储我的增量日志数据的配置单元中创建一个内部（托管）表。表格是这样的：

pre $ CREATE TABLE logs（foo INT，bar STRING，created_date TIMESTAMP）
ROW FORMAT DELIMITED
终止的字段'< =>'
存储为TEXTFILE;


我需要定期将数据载入此表。

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

但是数据没有正确插入到表格中。

示例日志行：

<$ p $可能存在分隔符的问题，无法找到原因。 p> 120 <=> abcdefg< => 2016-01-01 12:14:11


关于 select * from logs; 我得到，

  120 => abcdefg NULL 
  

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

任何人都可以请求如何成功提供自定义分隔符和加载数据。 / p>

解决方案

默认情况下，配置单元只允许用户使用单个字符作为字段分隔符。虽然有RegexSerDe来指定多字符分隔符，但使用起来可能令人望而生畏，特别是对于业余爱好者来说。

补丁（ HIVE-5871 ）添加了一个新的 SerDe ，名为 MultiDelimitSerDe 。使用 MultiDelimitSerDe 时， 用户可以在创建表格时指定多字符字段分隔符 ，其方式与典型表格创作。

  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=<=>）
>保存为TEXTFILE; 
 
 hive> dfs -put /home/user1/multi_char.txt / user / hive / warehouse / logs /。 ; 
 
 hive>从日志中选择*; 
确定
 120 abcdefg 2016-01-01 12:14:11 
所用时间：1.657秒，提取：1行
 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.

Example log line:

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

On select * from logs; I get,

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.

解决方案

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.

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屋！