Skip to main content
data_eng_87
data_eng_87
Silver ⭐️⭐️
March 29, 2023
Question

Ingest modifiable data from SQL to HDFS using NiFi

I trying to ingest a SQL Table to Hive using NiFi. This is easily done by using a QueryRecordTable. However, the records in the SQL table can be modified by hand, so they are not static.

Is there any way to update the data in HDFS according to the changes made in the SQL table?

4 replies

ckumar
Platinum ⭐️
ckumar
Platinum ⭐️
March 29, 2023

 CaptureChangeMySQL processor would be fit for your requirement.

 

If you found this response assisted with your issue, please take a moment and click on "Accept as Solution" below this post.

Thank you

 

 

 

Translate
data_eng_87
data_eng_87Author
Silver ⭐️⭐️
March 29, 2023

I'm sorry, i forgot to specify that I'm working with MS SQL Server, not with MySQL. Is the CaptureChangeMySQL processor valid for MS SQL Server?

Translate
ckumar
Platinum ⭐️
ckumar
Platinum ⭐️
March 29, 2023

No, So you can evaluate  GenerateTableFetch -->ExecuteSQL 

Where GenerateTableFetch "Maximum-value Columns" setting can help.  refr

https://nifi.apache.org/docs/nifi-docs/components/org.apache.nifi/nifi-standard-nar/1.20.0/org.apache.nifi.processors.standard.GenerateTableFetch/index.html

 

If you found this response assisted with your issue, please take a moment and click on "Accept as Solution" below this post.

Thank you

Translate
data_eng_87
data_eng_87Author
Silver ⭐️⭐️
March 29, 2023

I'm afraid that the GenerateTableFetch does not help me either because it uses the highest value on a column to determine whether to read new added records in the SQL server.

 

In my case, I'm trying to extract the already existing records that have recently been modified. If there is no processor than can directly deal with this, i may add a column to the table view which displays the time when the record was last modified (i.e. LastModificationDateTime) and use this column to retain the "Maximum-value Columns" value.

 

Back to the second part of my doubt, regarding the updating of this record in HDFS, how can I deal with this? I have the following approach in mind:

1 - Fetch and Read the parquet file where the "ChangedID" was stored.

2 - Modify the record in that file.

3 - Save up or replace that file in HDFS

 

Is this approach correct? Is there any better solution?

Translate
Terms & ConditionsAccessibility statement