Create a hive table upon a fixed-width log file but the last column width is not fixed

Question

Basically, I am trying to analyze SQL server log files using Hive.

The layout is the log file is

char(23) - for timestamp
char(1) - space
char(12) - source
the rest of the row, and the length varies.

The row delimiter is CR+LF.

Below are some entries in the log.

2019-05-28 07:29:55.03 Server UTC adjustment: -7:00 2019-05-28 07:29:55.03 Server (c) Microsoft Corporation. 2019-05-28 07:29:55.03 Server All rights reserved. 2019-05-28 07:29:55.03 Server Server process ID is 3368.

There are several posts here regarding fixed-width column layout. But in my case, the last column is identified not by the width but by the row delimiter.

Shu_ashu · Accepted Answer

@Haijin LiUse hive Regex serde and your matching regex will be(.{22})(.{1})(.{12})(.*)(.{22}) -> 1st capture group for 22 characters(.{1}) -> 2nd for 1 character(.{12}) -> 3rd for 12 characters(.*) -> 4th capture group matches for rest of the row.

Sign up

Please use your Cloudera account.

Login to the community

Please use your Cloudera account.