Ingesting Timestamp field from Oracle to HDFS using NIFI

Question

I am trying to insert a table from Oracle to HDFS using Nifi. The source table in Oracle has a timestamp(6) datatype field called sd_timestamp. Nifi is having the following processor:

QueryDatabase: This queries the OracleDatabase.
CovertAVROSchema: This one has input and output schemas. Both input and output schemas have sd_timestamp datatype as String.
ConvertAvroToOrc
PutHDFS: The table that is created in Hive also has the datatype as string for sd_timestamp. When the ingestion is done and I do a select * from the destination hive table, I am getting oracle.sql.timestamp@23aff4 as the value instead of the timestamp.

Please help.

Okay.. btw, do you want it to be saved as a String in the Hive external table as well or you're okay with it being a String? — Sivaprasanna Sethuraman

Jagrut Sharma Jagrut Sharma · Accepted Answer · 2018-05-10T02:47:25

Here are details of what I did to get it working. Did not require the ConvertAvroSchema step.

Oracle table

CREATE TABLE my_table
(
  entry_name varchar(10),
  sd_timestamp timestamp(6)
);

Populate some data

insert into my_table values('e-1',CURRENT_TIMESTAMP);
insert into my_table values('e-2',CURRENT_TIMESTAMP);
insert into my_table values('e-3',CURRENT_TIMESTAMP);

Verify data

SELECT * FROM my_table;
ENTRY_NAME   SD_TIMESTAMP
e-1          09-MAY-18 06.45.24.963327000 PM
e-2          09-MAY-18 06.45.39.291241000 PM
e-3          09-MAY-18 06.45.44.748736000 PM

NiFi Flow

Flow Design

QueryDatabaseTable configuration

ConvertAvroToOrc configuration

PutHDFS configuration

LogAttribute to see the hive.ddl attribute value

Verify results on HDFS

$ hadoop fs -ls /oracle-ingest
/oracle-ingest/50201861895275.orc

Create Hive table to query data using the hive.ddl value and adding location to it

hive> CREATE EXTERNAL TABLE IF NOT EXISTS my_oracle_table 
(
    ENTRY_NAME STRING, 
    SD_TIMESTAMP STRING
) 
STORED AS ORC 
LOCATION '/oracle-ingest';

Query Hive table

hive> select * from my_oracle_table;
e-1 2018-05-09 18:45:24.963327
e-2 2018-05-09 18:45:39.291241
e-3 2018-05-09 18:45:44.748736

Ingesting Timestamp field from Oracle to HDFS using NIFI

2 Answers