Importing a Large .ndjson file
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
Sankar P <sankar.curiosity@gmail.com> writes:
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"] }
What is the best way to do this on a postgresql database, deployed in
kubernetes, with a 1 GB RAM allocated ?
It looks like plain old COPY would do this just fine, along the lines
of (in psql)
\copy myTable(content) from 'myfile.ndjson'
If the newlines actually are \n\r rather than the more usual \r\n,
you might have to clean that up to stop COPY from thinking they
represent two line endings not one.
I'd advise extracting the first hundred or so lines of the file and doing
a test import into a temporary table, just to verify the process.
regards, tom lane
It looks like plain old COPY would do this just fine, along the lines
of (in psql)\copy myTable(content) from 'myfile.ndjson'
Indeed. Thanks.
--
Sankar P
http://psankar.blogspot.com
It looks like plain old COPY would do this just fine, along the lines
of (in psql)\copy myTable(content) from 'myfile.ndjson'
I spoke too soon. While this worked fine when there were no indexes
and finished within 10 minutes, with GIN index on the jsonb column, it
is taking hours and still not completing.
--
Sankar P
http://psankar.blogspot.com
I spoke too soon. While this worked fine when there were no indexes
and finished within 10 minutes, with GIN index on the jsonb column, it
is taking hours and still not completing.
It is always recommended to create indexes AFTER loading data. Sometimes it
can be faster to drop all indexes on the table, load huge data, and
re-create the indexes but there's no hard & fast rule. If you are adding
100k records to an empty or near empty table, I would remove all indexes
and create them after. Be sure you have sufficient maintenance_work_mem
also.
Sankar P <sankar.curiosity@gmail.com> writes:
I spoke too soon. While this worked fine when there were no indexes
and finished within 10 minutes, with GIN index on the jsonb column, it
is taking hours and still not completing.
There's the generic advice that building an index after-the-fact
is often cheaper than updating it incrementally. For GIN indexes
specifically, see also
https://www.postgresql.org/docs/current/gin-tips.html
regards, tom lane
Sankar P <sankar.curiosity@gmail.com> writes:
I spoke too soon. While this worked fine when there were no indexes
and finished within 10 minutes, with GIN index on the jsonb column, it
is taking hours and still not completing.There's the generic advice that building an index after-the-fact
is often cheaper than updating it incrementally. For GIN indexes
specifically, see also
Thanks guys.
--
Sankar P
http://psankar.blogspot.com