How to load String format timestamp value to hive table?

Hi KnimeTeam,

I have a String format time in a column as follow.

2015-05-01 00:28:39.487

I would like to load this column into hive table as TIMESTAMP type.

For this, I have given this String format date and time to "String to Date/Time" Node and configured the date format as "yyyy-MM-dd' 'HH:mm:ss.S".

After execute the String to Date/Time node, got the following as output in the column.

01.May.2015 00:28:39.487

Then, I have given this to hive loader and I am getting the null value in the column output.

In the hive loader and hive table, I have given the data type as TIMESTAMP for the timedetail column.

But, I have given only date(2015-05-01). And tried the above the same with YYYY-MM-DD configuration, it is working fine.
I have successfully loaded into hive table and the output is 01.May.2015.

Could you suggest me, Is there anything I am missing when I include date with time?

 

Best Regards,

velu

 

 

Hi Velu,

the Hive Loader does not use the right date format for timestamp columns which results in the missing values. We will fix this problem with the KNIME 3.1 release in December.

As a possible workaround you can convert the column to a string column with the Time To String node and "yyy-MM-dd HH:mm:ss.SSS" as format. In addition you have to manually set the sql type of the new created string column to TIMESTAMP in the SQL Types tab of the Hive Loader. Please see the attached workflow for an example.

Sorry for the inconveniences.

Bye,

Tobias

Hi Tobias,

Thanks for your reply.

Now I am able to load the date and time details into hive table.

 

Best Regards,

Velu