CODEWITHSUNDEEP
sqloracle-databaseapache-nifivertica
Bishal
Bishal

Reputation: 63

Nifi extract content from ExecuteSQL output

I am trying to fetch the DDL from Oracle, and then convert the DDL into syntax appropriate for Vertica database. My ExecuteSQL processor SQL query is:

select dbms_metadata.get_ddl('TABLE','${table_name}','${owner}') as t_ddl from dual

I am getting the following output in AVRO format:

Objavro.schemaú{"type":"record","name":"NiFi_ExecuteSQL_Record","namespace":"any.data","fields":[{"name":"T_DDL","type":["null","string"]}]}avro.codecnull­¶½``¼�÷KGÙ‡LÞoCäÞ
  CREATE TABLE "VDBA"."CUSTOMERS" 
   (    "CUSTOMER_ID" NUMBER(10,0) NOT NULL ENABLE, 
    "CUSTOMER_NAME" VARCHAR2(50) NOT NULL ENABLE, 
    "CITY" VARCHAR2(50), 
    "ITEMS_CNT" NUMBER(5,0), 
    "TSTAMP" TIMESTAMP (6) DEFAULT CURRENT_TIMESTAMP NOT NULL ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ­¶½``¼�÷KGÙ‡LÞoC

From this output, I only need the following text:

CREATE TABLE VDBA.CUSTOMERS 
   (CUSTOMER_ID NUMBER(10,0) NOT NULL, 
    CUSTOMER_NAME VARCHAR2(50) NOT NULL, 
    CITY VARCHAR2(50), 
    ITEMS_CNT NUMBER(5,0), 
    TSTAMP TIMESTAMP (6) DEFAULT CURRENT_TIMESTAMP NOT NULL
   )

I am unable to understand how to proceed next. Will ReplaceText processor work here? Please give me some pointers to move ahead.

Thanks & Regards, Bishal

Upvotes: 2

Views: 2675

Answers (2)

mattyb
mattyb

Reputation: 12083

As of NiFi 1.8.0, you can use ExecuteSQLRecord instead of ExecuteSQL, then you don't need a conversion processor afterwards. Then you can use either CSVRecordSetWriter (configured to not output the header or quote strings) or a FreeFormTextRecordSetWriter with the text ${T_DDL} to output the contents of the single field containing the DDL statement.

Upvotes: 3

jdex
jdex

Reputation: 1363

Try using a ConvertAvroToJSON processor and then manipulating or extracting the JSON from there.

Upvotes: 1

Related Questions