I have the following 5 lines of data in a file on hdfs. I want to load this to a table. I have regex that will do it, but it is loading an extra row of nulls for each line of data. Does anyone know why this is happening?
19/Mar/2018 3:00:06 INFO activity Submitted to Splunk
19/Mar/2018 3:00:20 INFO activity response received statuscode=200 bytesreceived=11548264
19/Mar/2018 3:00:21 INFO activity done writing K:\Data\031818\activity_031818.csv lineswritten=296110
19/Mar/2018 3:00:21 INFO hardware Submitted to Splunk
I use this to create the table
create table Splunk_BCO_MSR
(
ts string,
status string,
area string,
text string
)
partitioned by (partition_dt date)
ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.RegexSerDe'
WITH SERDEPROPERTIES ("input.regex" = "([^ ]+[ ][^ ]*) ([^ ]*) ([^ ]*) (.*)?");
This almost works, however when I run a select * from the table, I get 8 rows instead of 4. It looks like there is an additional row of NULLS being added.
| 19/Mar/2018 3:00:06 | INFO | activity | Submitted to Splunk | 2018-03-18 |
| NULL | NULL | NULL | NULL | 2018-03-18 |
| 19/Mar/2018 3:00:20 | INFO | activity | response received statuscode=200 bytesreceived=11548264 | 2018-03-18 |
| NULL | NULL | NULL | NULL | 2018-03-18 |
| 19/Mar/2018 3:00:21 | INFO | activity | done writing K:\Data\031818\activity_031818.csv lineswritten=296110 | 2018-03-18 |
| NULL | NULL | NULL | NULL | 2018-03-18 |
| 19/Mar/2018 3:00:21 | INFO | hardware | Submitted to Splunk | 2018-03-18 |
| NULL | NULL | NULL | NULL | 2018-03-18
