Reply
Highlighted
New Contributor
Posts: 4
Registered: ‎10-09-2017
Accepted Solution

Importing Timestamp values returning nulls

Hi

 

I'm attemtping to import a csv file into a table into Hadoop.

 

The file has imported but the fields with a timestamp field have returned as null for all rows (no nulls in original file).

 

I've currently formatted the columns in the csv file as custom format yyyy-mm-dd hh:dd:ss

and set the field in the Hadoop table as a timestamp. Presumably the excel format isn't being recognised.

 

How can I resolve this?

 

Thanks

Cloudera Employee
Posts: 4
Registered: ‎07-24-2017

Re: Importing Timestamp values returning nulls

[ Edited ]

The standard timestamp format is"yyyy-MM-dd HH:mm:ss[.SSSSSS]". Is there any warning printed when selecting from this table? Could you share a sample row in the text file?

New Contributor
Posts: 4
Registered: ‎10-09-2017

Re: Importing Timestamp values returning nulls

Example of the field is 

 

27/01/2016  11:25:00

 

None of the fields have any second values. They're all 00.

 

Within the file it's formatted as dd/mm/yyyy hh:mm.

 

I've tried changing it to yyyy-mm-dd hh:mm:ss but when I do that, save and re-open it's reverted to the original format.

 

I can upload into a text field (and it appears as 27/01/2016  11:25) but then if I cast the field as a timestamp it then returns null values.

 

 

Cloudera Employee
Posts: 4
Registered: ‎07-24-2017

Re: Importing Timestamp values returning nulls

[ Edited ]

You can create a table with a text column first, and then use to_timestamp to convert it into a table with a timestamp column.

create table timestamp_table as select column_1, column_2, to_timestamp(column_3, "dd/MM/yyyy HH:mm") from text_table;

For documentations on timestamp-related functions, please visit https://impala.apache.org/docs/build/html/topics/impala_datetime_functions.html

New Contributor
Posts: 4
Registered: ‎10-09-2017

Re: Importing Timestamp values returning nulls

Thanks, this function worked 

Announcements