Tools to migrate data from Json files to PostgreSQL DB.

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Tools to migrate data from Json files to PostgreSQL DB.

github kran
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.


Thanks
Kran,
Reply | Threaded
Open this post in threaded view
|

Re: Tools to migrate data from Json files to PostgreSQL DB.

Adrian Klaver-4
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
[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Tools to migrate data from Json files to PostgreSQL DB.

Pavel Stehule
In reply to this post by github kran
Hi

čt 7. 3. 2019 v 22:21 odesílatel github kran <[hidden email]> 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.


Thanks
Kran,
Reply | Threaded
Open this post in threaded view
|

Re: Tools to migrate data from Json files to PostgreSQL DB.

Tony Shelver
In reply to this post by github kran
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 <[hidden email]> 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.


Thanks
Kran,
Reply | Threaded
Open this post in threaded view
|

Re: Tools to migrate data from Json files to PostgreSQL DB.

github kran
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 <[hidden email]> 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 <[hidden email]> 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.


Thanks
Kran,