Thread: Tools to migrate data from Json files to PostgreSQL DB.
Hello PostgreSQL Team,
We have data in flat files about 2 billion records across multiple files.
1) What is the easiest way I can transfer this data to relational database ?.
2) Any tools I can use ?. and any better ways do it ?
Appreciate your help.
Thanks
Kran,
On 3/7/19 1:21 PM, github kran wrote: > Hello PostgreSQL Team, > > Are there are any tools to migrate data present in the json files ? to > the postgreSQL database. > We have data in flat files about 2 billion records across multiple files. > > 1) What is the easiest way I can transfer this data to relational > database ?. > 2) Any tools I can use ?. and any better ways do it ? An older blog: https://www.citusdata.com/blog/2013/05/30/run-sql-on-json-files-without-any-data-loads/ Not sure if all the parts still work or not. Otherwise I could see using the JSON types and JSON functions built into Postgres: https://www.postgresql.org/docs/10/datatype-json.html https://www.postgresql.org/docs/10/functions-json.html > > Appreciate your help. > > > Thanks > Kran, -- Adrian Klaver adrian.klaver@aklaver.com
Hi
čt 7. 3. 2019 v 22:21 odesílatel github kran <githubkran@gmail.com> napsal:
Hello PostgreSQL Team,Are there are any tools to migrate data present in the json files ? to the postgreSQL database.We have data in flat files about 2 billion records across multiple files.1) What is the easiest way I can transfer this data to relational database ?.2) Any tools I can use ?. and any better ways do it ?
I wrote pgimportdoc
Regards
Pavel
Appreciate your help.ThanksKran,
You may want to assess how you want to store and access the data in Postgres before deciding on an import strategy.
I have a system with a mix of relational and JSON data. The data was originally sourced in flat file format.
I wrote a few Python programs to take the data, then format to JSON, which I then submitted to pg functions.
To make life easier, I submitted it as 1 JSON field to be decomposed by Postgres into relational data, and another to be loaded straight into pg JSON columns.
The functions then either strip out the data from JSON using the PG JSON functions and store it relationally, or plug it straight into a JSON data element.
Not terribly difficult to do, especially if you are not navigating complex JSON structures in pl/pgsql to strip out the data.
Plenty of python JSON examples out there. Less so for PG :)
On Thu, 7 Mar 2019 at 23:21, github kran <githubkran@gmail.com> wrote:
Hello PostgreSQL Team,Are there are any tools to migrate data present in the json files ? to the postgreSQL database.We have data in flat files about 2 billion records across multiple files.1) What is the easiest way I can transfer this data to relational database ?.2) Any tools I can use ?. and any better ways do it ?Appreciate your help.ThanksKran,
Thank you all for your tips. I will try to see if I can make use of these useful tips using my java application.
On Fri, Mar 8, 2019 at 1:33 AM Tony Shelver <tshelver@gmail.com> wrote:
You may want to assess how you want to store and access the data in Postgres before deciding on an import strategy.I have a system with a mix of relational and JSON data. The data was originally sourced in flat file format.I wrote a few Python programs to take the data, then format to JSON, which I then submitted to pg functions.To make life easier, I submitted it as 1 JSON field to be decomposed by Postgres into relational data, and another to be loaded straight into pg JSON columns.The functions then either strip out the data from JSON using the PG JSON functions and store it relationally, or plug it straight into a JSON data element.Not terribly difficult to do, especially if you are not navigating complex JSON structures in pl/pgsql to strip out the data.Plenty of python JSON examples out there. Less so for PG :)On Thu, 7 Mar 2019 at 23:21, github kran <githubkran@gmail.com> wrote:Hello PostgreSQL Team,Are there are any tools to migrate data present in the json files ? to the postgreSQL database.We have data in flat files about 2 billion records across multiple files.1) What is the easiest way I can transfer this data to relational database ?.2) Any tools I can use ?. and any better ways do it ?Appreciate your help.ThanksKran,