Support Questions

ManishaJain · ‎11-03-2017

Have used QueryDatabase processor to read data from sql server DB.

It has read a avro files ,need to add static fields to the avro record

for ex..

=============================

Avro Record = id,name

Value= 1,ABC

=============================

need to add type to the record

========================================

New Avro Record = id,name,type

Value= 1,ABC,IN

========================================

Is there a way to manipulate avro files .

Shu_ashu · ‎11-03-2017

@manisha jain

Yeah you can manipulate results coming out from your Query database processor itself.

if you want to add static fields to the each avro record then in your Querydatabase processor.

change Columns to return property as

*,'in' type //return all columns from table and add type column with value in for each row.

Processor Config:-

as you can view above screenshot how we are configuring Columns to Return property above.

(or)

Another way doing this is

Once you get results from Query database processor then

1.Use convertavrotojson processor //we are converting avro data into json.
2.Use ReplaceText processor //to search for literal value } and do replacement value as your desired value in jsonformat
ex:- ,"type":"in"}
in this processor using replace text we are having json array of records and we are looking for } that means at end of each record and we are going to replace with ,"type":"in"} for each record so the json record structure will wont change.
3.Use convertjsontoavro processor //we are converting json data into avro data again and this data having new field that we have added to the json message.

View solution in original post

abhinav_joshi · ‎04-27-2018

Thanks @Shu for trying this out . I used the double quotes but it isnt working with Oracle. For now , I will use your JSON approach as a stop gap arrangement till I find a way out to fix this. In case you have any ideas then do let me know. Thansk again

abhinav_joshi · ‎04-27-2018

Ok got this fixed . In oracle , this works well if we add the table name and then a .*. So this is what I put in and my problem got fixed

GIM.GIDB_GC_SKILL.*,'${now():toNumber():format('yyyy-MM-dd HH:mm:ss')}' AS LOAD_TMS

Thanks

abhinav_joshi · ‎04-27-2018

@Ok , I managed to get this fixed. In oracle I can see it works a little differently. I used the following and this worked.I passed the table name followed by a "." and that seemed to do the trick. @Shu

GIM.GIDB_GC_SKILL.*,'${now():toNumber():format('yyyy-MM-dd HH:mm:ss')}' AS LOAD_TMS

Cloudera Community

Support Questions

Nifi : QueryDatabaseProcessor - Add new fields to Avro Files