REG: Json file in s3 to Postgres table

Anonymous — Sat, 16 Nov 2024 04:51:58 GMT

I have json file coming to s3 bucket , every 10 mins to a folder. Each json have the same structure of columns , just with one different record in each. I need to migrate these to a table name common in postgres. How can i achieve them ? I have TAC as well, How to schedule & get it done.

Json File: For ex :

[{"DateTime":"2016-09-03 14:00:00","Total_Crude_Flow_Rate_MBperDay":171.9602,"Actual_Yield_LGO_MBperDay":39.2051,"Actual_Yield_MVGO_MBperDay":22.5081,"Actual_Yield_HVGO_MBperDay":26.0575,"Actual_Yield_VGO_MBperDay":48.5655,"Actual_Yield_VR_MBperDay":22.8243,"Crude_Ambient_Temperature_DEGC":43.5956,"Desalter_Inlet_Temperature_1_DEGC":141.2322,"Desalter_Inlet_Temperature_2_DEGC":133.4597,"Resultant_Desalter_Inlet_Temperature_DEGC":138.3071,"Desalter_Outlet_Temperature_DEGC":141.5549,"Coil_Inlet_Temperature_DEGC":262.1993,"RunDate":"2019-07-26 12:51:07","SavedDate":"2019-07-26 17:49:21"}]

Target table Structure : ( One to one mapping ) - Postgres database

"Id",
"Datetime",
"TotalCrudeFlowRate",
"ActualYieldLGO",
"ActualYieldMVGO",
"ActualYieldHVGO",
"ActualYieldVGO",
"ActualYieldVR",
"CoilInletTemperature",
"DesalterInletTemperature1",
"DesalterInletTemperature2",
"DesalterOutletTemperature"

Thanks,

Prabuj

Re: REG: Json file in s3 to Postgres table

iamabhishek — Tue, 27 Aug 2019 13:36:37 GMT

@prabuj27
I could think of couple of ways to achieve this -

download the file from S3 to your local(using tS3Connection,tS3Get etc...)
read the json file (using tfileinputjson,tExtractJsonFields etc...)
write the data to your database table (using tPostgresqlOutput etc...)
Once you are done you could remove the file from your local

Another way could be -
use sqoop -import to import data from S3 to Hive and then do an -export from hdfs to your postgres. The downside of this method is that you will have to create a Hive/HCatalog table before the import. You will have to clean it if it is not need in future. Also do need to check if Sqoop directly supports JSON file format import else have to utilize the hcatalog import function.

topic REG: Json file in s3 to Postgres table in Talend Studio

REG: Json file in s3 to Postgres table

Re: REG: Json file in s3 to Postgres table