Skip to main content
Kiranq
Kiranq
Silver ⭐️⭐️⭐️
August 29, 2023
Solved

Read the flow file of ExecuteStreamCommand

I am executing python script in ExecuteStreamCommand and my code last line dumps the json key values. 
My goal is to read those key values into PutDatabaseRecord processor and insert the values in my database. However, I am unable to read content of the flow file of ExecuteStream. I have used ConvertAvrotoJson but it is showing output as "not a avro data file". Is there any workarounf of it? Thanks.

SAMSAL
SAMSALAnswer
Platinum ⭐️⭐️⭐️
August 30, 2023

If you are able to store the display name and the mail values into flowfile attributes, then the easiest way is to use the PutSQL processor to create your insert statement of those values and other values you want to ingest into the target table. In the PutSQL you can specify the insert statement in the SQL Statement property as follows:

 

insert into myTable(displayNameCol, mailCol, otherCol...) values
('${displayNameAttribute}','${mailAttribute}', 'otherValue',...)

 

The AttributeToJson should work as well if you specify which attributes you want to insert into the DB to be converted as Json flowfile and then use the PutDatabaseRecord with JsonTreeReader where  you specify that the Statement Type property as INSERT.  The only caveat here is in the PutDatabaseRecord the attribute name should match the table column names.

If that helps please accept solution.

Thanks

 

 

 

View in thread

6 replies

SAMSAL
SAMSAL
Platinum ⭐️⭐️⭐️
August 29, 2023

Hi @Kiranq ,

 

If you are getting the output form the ExecuteStreamCommand as Json  I dont understand why are you using the ConvertAvroJson Processor. In the PutDatabaseRecrod you can specify the Record Reader property as JsonTreeReader   and consume it as such. I guess if you can shed more light on how the executestream output looks like maybe we can help you better.

 

If that helps please accept solution.

Thanks

Translate
Kiranq
KiranqAuthor
Silver ⭐️⭐️⭐️
August 29, 2023

Thanks @SAMSAL for responding. I will explain what I am trying to accomplish.
The ExecuteStreamCommand gives me below results:

Kiranq_0-1693340857298.png


and I want to use displayName and mail key's values from this result and store it into the database. My current workflow is based on the flow that I will store these two key's value as a attribute and use it in the subsequent processors. However, using AttributestoJson aint also working for me.

Can you suggest any other better appraoach to ingest these two values in my database? I have other columns' value to ingest that as well so may be ExecuteSQLCommand processor would be a better choice to use.  

Translate
SAMSAL
SAMSALAnswer
Platinum ⭐️⭐️⭐️
August 30, 2023

If you are able to store the display name and the mail values into flowfile attributes, then the easiest way is to use the PutSQL processor to create your insert statement of those values and other values you want to ingest into the target table. In the PutSQL you can specify the insert statement in the SQL Statement property as follows:

 

insert into myTable(displayNameCol, mailCol, otherCol...) values
('${displayNameAttribute}','${mailAttribute}', 'otherValue',...)

 

The AttributeToJson should work as well if you specify which attributes you want to insert into the DB to be converted as Json flowfile and then use the PutDatabaseRecord with JsonTreeReader where  you specify that the Statement Type property as INSERT.  The only caveat here is in the PutDatabaseRecord the attribute name should match the table column names.

If that helps please accept solution.

Thanks

 

 

 

Translate
Kiranq
KiranqAuthor
Silver ⭐️⭐️⭐️
August 31, 2023

@SAMSAL, hey I know I accepted the solution and it did work for when I had 1 user details to fetch and store it's values to the processor's attributes.

I wanted to ask if it is possible to store multiple values to a single attribute of a processor since I now have many users' details to store. Is this possible otherwise I would have to change the logic? Thanks.

Translate
SAMSAL
SAMSAL
Platinum ⭐️⭐️⭐️
August 31, 2023

If you are getting multiple records in one Json array , then you probably need to use SplitJosn Processor to get each record individually , then extract the values you need using EvaluateJsonPath from each record then do the PutSQL.

Translate
Terms & ConditionsAccessibility statement