##3.11 Hive SerDe

Hive SerDe - Serializer and Deserializer

SerDe 用于做序列化和反序列化。

构建在数据存储和执行引擎之间，对两者实现解耦。

Hive通过ROW FORMAT DELIMITED以及SERDE进行内容的读写。

xxxxxxxxxx
row_format
  : delimited [fields terminated by char [escaped by char]] [collection items terminated by char]
        [map keys terminated by char] [lines terminated by char]
        [null defined as char]  
  | serde serde_name [with serdeproperties (property_name=property_value, property_name=property_value, ...)]

Hive正则匹配

xxxxxxxxxx
CREATE TABLE logtbl (
  `host` string,
  `identity` string,
  `t_user` string,
  `time` string,
  `request` string,
  `referer` string,
  `agent` string)
 ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
 WITH SERDEPROPERTIES (
  "input.regex" = "([^ ]*) ([^ ]*) ([^ ]*) \\[(.*)\\] \"(.*)\" (-|[0-9]*) (-|[0-9]*)"
 )
 STORED AS TEXTFILE;

使用“软件/data/localhost_access_log.txt”导入。

192.168.57.4 - - [29/Feb/2016:18:14:35 +0800] "GET /bg-upper.png HTTP/1.1" 304 -

将该文件上传到node4的/root/data目录

xxxxxxxxxx
[root@node4 ~]# cd data/
[root@node4 data]# pwd
/root/data
[root@node4 data]# rz -E
rz waiting to receive.
[root@node4 data]# ll
总用量 12
-rw-r--r-- 1 root root 1759 12月 11 2019 localhost_access_log.txt
-rw-r--r-- 1 root root   57 11月 11 15:40 person3.txt
-rw-r--r-- 1 root root  496 11月 11 14:36 person.txt
[root@node4 data]# mv localhost_access_log.txt logtbl.txt

在hive中新建表，并load数据到表中，然后在查询数据：

xxxxxxxxxx
hive> CREATE TABLE logtbl (
    >   `host` string,
    >   `identity` string,
    >   `t_user` string,
    >   `time` string,
    >   `request` string,
    >   `referer` string,
    >   `agent` string)
    >  ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
    >  WITH SERDEPROPERTIES (
    >   "input.regex" = "([^ ]*) ([^ ]*) ([^ ]*) \\[(.*)\\] \"(.*)\" (-|[0-9]*) (-|[0-9]*)"
    >  )
    >  STORED AS TEXTFILE;
OK
Time taken: 0.366 seconds
hive> load data local inpath '/root/data/logtbl.txt' into table logtbl;
Loading data to table default.logtbl
OK
Time taken: 0.735 seconds
hive> select * from logtbl;
OK
192.168.57.4    -   -   29/Feb/2016:18:14:35 +0800  GET /bg-upper.png HTTP/1.1  304 -
192.168.57.4    -   -   29/Feb/2016:18:14:35 +0800  GET /bg-nav.png HTTP/1.1    304 -
192.168.57.4    -   -   29/Feb/2016:18:14:35 +0800  GET /asf-logo.png HTTP/1.1  304 -
192.168.57.4    -   -   29/Feb/2016:18:14:35 +0800  GET /bg-button.png HTTP/1.1 304 -
192.168.57.4    -   -   29/Feb/2016:18:14:35 +0800  GET /bg-middle.png HTTP/1.1 304 -