question Re: Read Database tables and convert into CSV format in Archives of Support Questions (Read Only)

Read Database tables and convert into CSV format

ramesh_ganginen — Sun, 18 Aug 2019 09:56:05 GMT

Hi Team,

I'm trying to read the data from MySQL database and trying to place the data in S3. I was able to read the data from MySQL and able to place the data in S3 bucket as well. However whatever the data I'm placing in S3 bucket is not a CSV data. It's JSON data. Below is the flow I have right now. I have total 6 tables that I need to read from database and each table have different schema.

I want to place the CSV data in S3 bucket. For that I got suggestions from experts and I was bit puzzled whatever I'm doing is correct or not. Any other possible solutions/suggestions to place the CSV data in S3 bucket will be helpful for me.

Suggestions I got:

https://stackoverflow.com/questions/49145832/convert-json-to-csv-in-nifi

https://community.hortonworks.com/questions/176254/convert-json-to-csv-in-nifi.html?childToView=177079#answer-177079

Re: Read Database tables and convert into CSV format

Shu_ashu — Thu, 08 Mar 2018 20:05:07 GMT

@User 805

As you are using NiFi 1.5 you can use Avroreader as RecordReader and CsvRecordSetWriter as Record Writer in ConvertRecord processor.

ConvertRecord processor 
RecordReader-->AvroReader//reads the incoming avro format flowfile contents
RecordWriter-->CsvRecordSetWriter//write the output results in csv format

Flow:-

ListDatabaseTables
GenerateTableFetch
UpdateAttribute(are you changing the filename to UUID?)
ConvertRecord
PutS3Object

Please refer to below link to configure AvroReader
https://community.hortonworks.com/questions/175208/how-to-store-the-output-of-a-query-to-one-text-fil.html?childToView=174434#comment-174434

If you are still facing issues then share us sample of 10 records in csv (or) json format to recreate your scenario on our side.

Re: Read Database tables and convert into CSV format

ramesh_ganginen — Sun, 18 Aug 2019 09:55:35 GMT

Hi @Shu,

I made the changes as you suggested and it worked. I added one extra processor(Execute SQL). I'm posting the flow here so that it might help someone like me in future who are having same requirement. Really appreciate your help. Thanks a lot.

Re: Read Database tables and convert into CSV format

Shu_ashu — Fri, 09 Mar 2018 05:28:24 GMT

@User 805

Awesome,thankyou very much for sharing the flow with us 🙂

Re: Read Database tables and convert into CSV format

sajidkhan — Sun, 24 Mar 2024 20:31:50 GMT

Hi @Shu_ashu, @ramesh_ganginen I am encountering the same issue. I attempted the method you suggested, but it didn't work. I'm trying to copy a table from my MySQL database to my local machine using Apache NiFi. I've configured the 'ConvertRecord' processor and added the 'CSVRecordSetWriter', but I'm still receiving files in JSON format. Can you please guide me if I'm making any mistakes?