Hive Insert statement in NiFi - String to DateTime

[#1]

Hello,

I'm trying to create Hive insert statements for NiFi's PutHiveQL processor; I've a timestamp column in the Hive table and the incoming value for this column is in string format, like 20161011075959 (no milliseconds).

I tried using toDate, Format, toNumber in various combinations to convert the string to DateTime format in the Hive insert statement, but the data in Hive table shows NULL value for this column regardless; can anyone tell me what's the correct way to convert string to datetime for Hive.

None of the below seem to work:

${Abc.DateTimeOfMessage:toDate("yyyyMMddHHmmss")}

${Abc.DateTimeOfMessage:format("yyyy/MM/dd HH:mm:ss")}

${Abc.DateTimeOfMessage:toDate("yyyyMMddHHmmss"):toNumber()}

Thanks in advance for your time.

[#2 · Accepted solution]

Try this

${dateNum:toDate("yyyyMMddHHmmss"):format("yyyy-MM-dd HH:mm:ss")}

Where dateNum is 20161011075959 (yyyyMMddHHmmss) which on formatting changes to 2016-10-11 07:59:59 (yyyy-MM-dd HH:mm:ss)

[#3]

Thanks @Arun A K

That worked, also I found ${dateNum:format("yyyy-MM-dd HH:mm:ss")} (without toDate) working as well in my case, may be because I already have the string in the yyyyMMddHHmmss format.

[#4]

What do the resulting HiveQL statement (and attributes) look like? Are you using parameters (with attributes like hiveql.args.N.value and such)? If so, then it appears from looking at the code that it expects a long integer (probably days or seconds from Epoch depending on the data type) for the value, and the appropriate JDBC type value for DATE, TIME, or TIMESTAMP.

If parameterized statements don't work, perhaps a ReplaceText to build an explicit HiveQL statement will (such as to remove quotes from attributes which are strings, or to cast a literal to the appropriate date type, etc.)

[#5]

The HiveQL statements for the PutHiveQL processor are like this:

INSERT INTO TABLE hive_obx_etl_test( abc_datetimeofmessage , .... ) VALUES ( '${Abc.DateTimeOfMessage:format("yyyy-MM-dd HH:mm:ss")}', ....)

I'm not using any parameters, what I'm using is a combination of UpdateAttributes and ReplaceText processors (among others) to build the exact Hive INSERT statements.

[#6]

@Raj B

The toDate NiFi expression Language function expected the input to this function to define the current format of the value it is being passed. The result is the number of milliseconds since Jan. 1st 1970. The Format function will take a standard date of format number milliseconds since Jan 1st 1970 and convert it into the desired output format as defined in teh function.

Assuming you have an attribute Abc.DateTimeOfMessage with a value of 20161011075959, teh following NiFi EL statement will produce the output '2016/10/11 07:59:59':

${Abc.DateTimeOfMessage:toDate('yyyyMMddHHmmss'):format('yyyy/MM/dd HH:mm:ss')}

The above EL statement firs convert the dat you have into the standard date format (milliseconds since 1/1/1970) using the toDate function and then pass that result to the format function which converts a standard dat format into the desired output string you are looking for.

*** An alternative EL that will yield the sam result is:

${Abc.DateTimeOfMessage:replaceAll('^([0-9]{4})([0-9]{2})([0-9]{2})([0-9]{2})([0-9]{2})([0-9]{2})','$1/$2/$3 $4:$5:$6')}

The above uses the EL replaceAll function uses java capture groups to break apart the incoming function and then uses the values of those 6 capture groups to reconstruct the output in the format you want.

There are even more ways, but I figured this is good enough.

Thanks,

Matt

[#7]

Thanks @Matt for the explanation