Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Facing issues with Date and Timestamp Columns

Facing issues with Date and Timestamp Columns

New Contributor

Am working on one requirement , it is simple loading the data from Hive to Postgresql using apache nifi.

My processors are SelectHiveQL--->SplitAvro--->PutDatabaseRecord.

SelectHiveQL get the data from Hive, in hive we have many columns but facing issues with Date Column and Timestamp Coloumns. In putDatabaseRecord am getting error as

":"PutDatabaseRecord[id=cecd3d18-f1d2-1d39-9e48-d4be96170480] Failed to process StandardFlowFileRecord[uuid=1bb1f4cf-3b06-4c0a-aaea-4156a3953ce9,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1510654697269-11, container=default, section=11], offset=186544, length=4932],offset=0,name=64863335688318.avro,size=4932] due to java.sql.BatchUpdateException: Batch entry 0 INSERT INTO some XXXX Columns was aborted: ERROR: column \"XXXdate\" is of type date but expression is of type character varying\nHint: You will need to rewrite or cast the expression.\nPosition: 932Call getNextException to see other errors in the batch.: ","timestamp":"06:14:09 EST"}}],"generated":"06:16:37 EST"}}

Would you please guide me to resolve the issue, do i need add any processor in the flow, how to come out of this issue

Thanks in advance.

5 REPLIES 5

Re: Facing issues with Date and Timestamp Columns

New Contributor

@Abdelkrim Hadjidj any inputs on this question..Thanks in advance.

Re: Facing issues with Date and Timestamp Columns

New Contributor

Can anybody please respond on my question, am new to Nifi

the issue i find here is am using SelectHiveQl Processor to connect with Hive and get the data while getting the date it is trying to read the Date Columns as String (

{"name":"join_date","type":["null","string"]}) this the main issue i found.

I have gone through few blogs regarding this they told me to use Update Atrribute processor to convert the string to Date as like below

but no result.

${field.value:toDate('yyyy-mm-dd HH:mm:ss.SSS'):toNumber()}

I have tried the same with QueryDatabaseTable Processor where we have an option of Boolean Property Use Avro Logical Types set to True but it is not worked it raised another issue.

QueryDatabaseTable[id=590aae62-0160-1000-ffff-ffffdb58c844] Unable to execute SQL select query SELECT * FROM test.DatesTable due to org.apache.nifi.processor.exception.ProcessException: Error during database query or conversion of records to Avro.: org.apache.nifi.processor.exception.ProcessException: Error during database query or conversion of records to Avro.

Am not sure how to resolve this issue. i found in one blog if the Avro format is like this (

{"name":"join_date","type":["null",{"type":"int","logicalType":"date"}]} ) it works but am not sure how to get this avro format with select HiveQL or QueryDatabaseTable processor.

Help is highly appreciated.


Re: Facing issues with Date and Timestamp Columns

New Contributor

loadingdatafrom-hive-to-postgres.xml

I have attached the XML for the reference , please check and let me know what am doing wrong here.

please help me to resolve the issue

Highlighted

Re: Facing issues with Date and Timestamp Columns

New Contributor

change <Use Avro Logical Types==>true>

Re: Facing issues with Date and Timestamp Columns

New Contributor

Hi, you need to add one param to your JDBC connection pooling service. Something like this:

jdbc:postgresql://localhost:5432/databaseName?stringtype=unspecified

Don't have an account?
Coming from Hortonworks? Activate your account here