Hive 9、Hive 在表中添加正则匹配

在Hive中还有一项比较好用的功能，也是非常重要的功能：在建表的时候可以不指定表的行、字段、列的分隔方式，通过给表指定一段正则表达式，让Hive自动去匹配；

1、创建表

 CREATE TABLE apachelog (  dates STRING,  times STRING,  types STRING,  info STRING)ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'WITH SERDEPROPERTIES (  "input.regex" = "(^[0-9|-]{0,}) ([^ ]*) (INFO|ERROR) ([^*]*)$")STORED AS TEXTFILE;

2、日志文件内容

$ vi /opt/apachelog# 下面是一段hadoop的日志内容2016-01-23 14:22:04,407 INFO org.apache.hadoop.http.HttpServer2: Jetty bound to port 84802016-01-23 14:22:04,407 INFO org.mortbay.log: jetty-6.1.262016-01-23 14:22:05,065 INFO org.mortbay.log: Started HttpServer2$SelectChannelConnectorWithSafeStartup@0.0.0.0:84802016-01-23 14:22:05,357 INFO org.apache.hadoop.ipc.CallQueueManager: Using callQueue class java.util.concurrent.LinkedBlockingQueue2016-01-23 14:22:05,428 INFO org.apache.hadoop.ipc.Server: Starting Socket Reader #1 for port 84852016-01-23 14:22:05,470 INFO org.apache.hadoop.ipc.Server: IPC Server Responder: starting2016-01-23 14:22:05,472 INFO org.apache.hadoop.ipc.Server: IPC Server listener on 8485: starting2016-01-23 16:00:15,525 ERROR org.apache.hadoop.hdfs.qjournal.server.JournalNode: RECEIVED SIGNAL 15: SIGTERM2016-01-23 16:00:15,531 INFO org.apache.hadoop.hdfs.qjournal.server.JournalNode: SHUTDOWN_MSG: 

3、导入本地数据

hive> load data local inpath '/opt/apachelog' overwrite into table apachelog;

4、查看结果

hive> select * from apachelog;OK2016-01-23    14:22:04,407    INFO    org.apache.hadoop.http.HttpServer2: Jetty bound to port 84802016-01-23    14:22:04,407    INFO    org.mortbay.log: jetty-6.1.262016-01-23    14:22:05,065    INFO    org.mortbay.log: Started HttpServer2$SelectChannelConnectorWithSafeStartup@0.0.0.0:84802016-01-23    14:22:05,357    INFO    org.apache.hadoop.ipc.CallQueueManager: Using callQueue class java.util.concurrent.LinkedBlockingQueue2016-01-23    14:22:05,428    INFO    org.apache.hadoop.ipc.Server: Starting Socket Reader #1 for port 84852016-01-23    14:22:05,470    INFO    org.apache.hadoop.ipc.Server: IPC Server Responder: starting2016-01-23    14:22:05,472    INFO    org.apache.hadoop.ipc.Server: IPC Server listener on 8485: starting2016-01-23    16:00:15,525    ERROR    org.apache.hadoop.hdfs.qjournal.server.JournalNode: RECEIVED SIGNAL 15: SIGTERM2016-01-23    16:00:15,531    INFO    org.apache.hadoop.hdfs.qjournal.server.JournalNode: SHUTDOWN_MSG: Time taken: 0.518 seconds, Fetched: 9 row(s)