Saya ingin mengimpor data json ke postgres. Data yang saya miliki adalah pesanan satu juta baris, ukuran minimal 700 MB dan membentang hingga 3 GB.

Berikut contoh data yang saya buat berdasarkan struktur data yang saya miliki. Saya mencoba mengimpor ini ke postgres tetapi saya mendapatkan kesalahan.

Contoh(1) data

{"offers":{"offer":[
{"url": "https://some1-value.com", "nested": {"id":4,"value":"some1 text value"}, "quotes": "5\"  side"},
{"url": "https://some2-value.com", "nested": {"id":5,"value":"some2 text value"}, "quotes": "6\"  side"},
{"url": "https://some3-value.com", "nested": {"id":6,"value":"some3 text value"}, "quotes": "7\"  side"}]}}

Perintah yang saya gunakan dan kesalahan yang saya dapatkan

# copy contrial from '/home/ubuntu/sample-data.json';
ERROR:  invalid input syntax for type json
DETAIL:  The input string ended unexpectedly.
CONTEXT:  JSON data, line 1: {"offers":{"offer":[
COPY contrial, line 1, column info: "{"offers":{"offer":["

Saya memodifikasi file untuk menghapus dua kunci pertama dan hanya memiliki daftar jsons seperti di bawah ini, tetapi saya masih mendapatkan kesalahan.

Contoh (2) data

[
{"url": "https://some1-value.com", "nested": {"id":4,"value":"some1 text value"}, "quotes": "5\"  side"},
{"url": "https://some2-value.com", "nested": {"id":5,"value":"some2 text value"}, "quotes": "6\"  side"},
{"url": "https://some3-value.com", "nested": {"id":6,"value":"some3 text value"}, "quotes": "7\"  side"}]

Kesalahan

# copy contrial from '/home/ubuntu/sample2-data.json';
ERROR:  invalid input syntax for type json
DETAIL:  The input string ended unexpectedly.
CONTEXT:  JSON data, line 1: [
COPY contrial, line 1, column info: "["

Contoh (3) data yang saya modifikasi lebih lanjut

[{"url": "https://some1-value.com", "nested": {"id":4,"value":"some1 text value"}, "quotes": "5\"  side"},
{"url": "https://some2-value.com", "nested": {"id":5,"value":"some2 text value"}, "quotes": "6\"  side"},
{"url": "https://some3-value.com", "nested": {"id":6,"value":"some3 text value"}, "quotes": "7\"  side"}]

Kesalahan yang berbeda

# copy contrial from '/home/ubuntu/sample2-data.json';
ERROR:  invalid input syntax for type json
DETAIL:  Token "side" is invalid.
CONTEXT:  JSON data, line 1: ...,"value":"some1 text value"}, "quotes": "5"  side...
COPY contrial, line 1, column info: "[{"url": "https://some1-value.com", "nested": {"id":4,"value":"some1 text value"}, "quotes": "5"  si..."

Buat pernyataan tabel

CREATE TABLE public.contrial (
    info json NOT NULL
);

Tujuan akhirnya adalah membuat tabel dengan kunci sebagai kolom dan nilai sebagai catatan. Kunci bersarang perlu diratakan.

+-------------------------+-----------+------------------+----------+
| url                     | nested_id | nested_value     | quotes   |
+-------------------------+-----------+------------------+----------+
| https://some1-value.com | 4         | some1 text value | 5\" side |
+-------------------------+-----------+------------------+----------+
| https://some2-value.com | 5         | some2 text value | 6\" side |
+-------------------------+-----------+------------------+----------+
| https://some3-value.com | 6         | some3 text value | 7\" side |
+-------------------------+-----------+------------------+----------+
0
scientific_explorer 3 April 2021, 15:04

1 menjawab

Jawaban Terbaik

Saya akhirnya menggunakan blog Andre Dunstan dan jawaban SO ini yang mengatakan untuk memformat json dengan cara tertentu menggunakan perintah salin.

Karena struktur saya cukup ditentukan untuk file yang saya parsing, saya berakhir dengan skrip berikut.

def file_len(fname):
    # to find the number of lines in the file.
    # Has been pretty efficient even for millions of records
    with open(fname) as f:
        for i, l in enumerate(f):
            pass
    return i + 1

LEN = file_len('/path/to/input.json')
with open('/path/to/output.json.csv', 'w') as fo:
    with open('23819_part003.json', 'r') as fi:
        for i, l in enumerate(fi):
            # I skip the first line
            if i == 0: continue 
            
            # To remove the ']}}' from the end
            elif i+1 == LEN: _ = fo.write(l[:-3])
            
            # To remove the ',' from the end 
            # and add \n since write does not add newline on its own
            else: _ = fo.write(l[:-2]+'\n') 

# load statement

import sqlalchemy
POSTGRESQL = f'postgresql+psycopg2://{USERNAME}:{PASSWORD}@{HOSTNAME}/{DB}'
engine = sqlalchemy.create_engine(POSTGRESQL, echo=True)
            
con = engine.connect()
trans = con.begin()
LOAD_SQL = "COPY tablename from '/path/to/output.json.csv' with csv delimiter E'\x01' quote E'\x02' null as '';"
try:
    con.execute(LOAD_SQL)
    trans.commit()
except Exception as e:
    trans.rollback()
finally:
    con.close()
0
scientific_explorer 3 April 2021, 19:06