Importing a Large .ndjson file

From: Sankar P <sankar(dot)curiosity(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Importing a Large .ndjson file
Date: 2020-06-17 11:21:21
Message-ID: CAMSEaH5SfyfXN_rSah41dOOA_aAik4hZED0qp52=1wqzjz-pMA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi

I have a .ndjson file. It is a new-line-delimited JSON file. It is
about 10GB and has about 100,000 records.

Some sample records:
```
{ "key11": "value11", "key12": [ "value12.1", "value12.2"], "key13": {
"k111": "v111" } } \n\r
{ "key21": "value21", "key22": [ "value22.1", "value22.2"] }
```
Now I want to INSERT these json records into my postgres table of the
following schema:

```
CREATE TABLE myTable (id BIGSERIAL, content JSONB);
```

Where I want the records to be inserted to the `content` field of my
postgres table.

What is the best way to do this on a postgresql database, deployed in
kubernetes, with a 1 GB RAM allocated ?

I can probably write a that would read this file line-by-line and
INSERT into the database, in a transaction. But that I believe would
take a lot of network traffic and I want to know if there is a better
way to do this.

Thanks.

--
Sankar P
http://psankar.blogspot.com

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Toomas Kristin 2020-06-17 13:38:44 Re: Conflict with recovery on PG version 11.6
Previous Message Laurenz Albe 2020-06-17 09:42:40 Re: Conflict with recovery on PG version 11.6