Support Questions
Find answers, ask questions, and share your expertise

BulkLoading date in Phoenix

New Contributor

I try to import some CSV using the Phoenix Bulkload Utility.

The first column of my table (and of the file) is a date (with time) and is part of the primary key.

The file is loaded in Phoenix table but I can't select individual rows using the date.

Let's say that the date in the file is 2016-10-06 08:41:21, I can retrieve the record if I'm executing :

SELECT * FROM TABLE WHERE DATE >= TO_DATE( '2016-10-06 08:41:21' )

However, I won't get my record if I execute

SELECT * FROM TABLE WHERE DATE = TO_DATE( '2016-10-06 08:41:21' )

or

SELECT * FROM TABLE WHERE DATE > TO_DATE( '2016-10-06 08:41:21' )

The same occurs if I change my column type to TIMESTAMP.

If I use psql.py tool to load the same CSV file, I will be able to get my record using the = operator...

Could someone give me any indication on what's going on ?

When loading dates from CSV file, is it better to have these dates in Local or UTC format ?

Regards,

3 REPLIES 3

Re: BulkLoading date in Phoenix

Can you share your table schema, a record from the table and a corresponding record from the CSV file?

Re: BulkLoading date in Phoenix

New Contributor

I attached tablecreate.zip that contains table script creation and the csv file.

To do the tests, I'm using SQuirreL (but I get the same result with sqlline.py).

I execute the following queries :

select * from SysLogEvent WHERE LogDate >= TO_TIMESTAMP( '2016-10-07 08:42:29', 'yyyy-MM-dd HH:mm:ss' )

This will return all the records (100)

But the following queries do not return any record

select * from SysLogEvent WHERE LogDate = TO_TIMESTAMP( '2016-10-07 08:42:29', 'yyyy-MM-dd HH:mm:ss' );

select * from SysLogEvent WHERE LogDate > TO_TIMESTAMP( '2016-10-07 08:42:29', 'yyyy-MM-dd HH:mm:ss' );

My environment is HDP 2.5 on CentOS 7.

Re: BulkLoading date in Phoenix

New Contributor

Any clue to understand what's going on in this case ?