Transforming date from Epoch During Routine Load

Working with Data
1

Hi All,Im creating a routine load that reads from Kaftka - the statement date field is in epoch format and im trying to convert it to a normal date within the routine load but its failing, can anyone tell me why, or how this should be done?

CREATE ROUTINE LOAD DEMO.quickstart2 ON statement2
COLUMNS(id,imported,branch_id,statement_date=FROM_UNIXTIME(statement_date*86400),amount)
PROPERTIES
(
"desired_concurrent_number"="1",
"max_error_number"="1000",
"format" ="json",
"jsonpaths" ="[\"$.id\",\"$.imported\",\"$.branch_id\",\"$.statement_date\",\"$.amount\"]"
)
FROM KAFKA
(
"kafka_broker_list" ="Hidden",
"kafka_topic" = "source.public.statement",
"property.kafka_default_offsets" = "OFFSET_BEGINNING"
);

ERROR:

ErrorReason{errCode = 2, msg='failed to create task: unknown reference column, column=statement_date, reference=statement_date'}

Thanks in advance

2

per form… what is the version number and logs?

3

also by the looks of it, you can’t call the column with the same name. I would call it the new column some other name.

4

Sorted thanks Albert - will post logs and version number in the future:

Solution

CREATE ROUTINE LOAD DEMO.quickstart2 ON statement2
COLUMNS(id,imported,branch_id,statement_date,statement_date=from_unixtime(statement_date*86400),amount)
PROPERTIES
(
"desired_concurrent_number"="1",
"max_error_number"="1000",
"format" ="json",
"jsonpaths" ="[\"$.id\",\"$.imported\",\"$.branch_id\",\"$.statement_date\",\"$.amount\"]"
)
FROM KAFKA
(
"kafka_broker_list" ="10.5.0.5:9092",
"kafka_topic" = "source.public.statement",
"property.kafka_default_offsets" = "OFFSET_BEGINNING"
);
2 Likes
5

statement_date=FROM_UNIXTIME(statement_date*86400)
@Gerrit_van_Zyl The referenced column statement_date in the exprssion FROM_UNIXTIME(statement_date*86400) is not defined yet.

1 Like