JSON 파일을 PostgreSQL로 Import하려면 어떻게 해야 하나요?

JSON 파일을 PostgreSQL로 Import하려면 어떻게 해야 하나요?

를 들어, 저는 파일이 .customers.json오브젝트 배열(형성된 오브젝트 없음)로, 다음과 같이 매우 간단합니다(중요한 것은 이미 ID가 포함되어 있다는 것입니다).

[
  {
    "id": 23635,
    "name": "Jerry Green",
    "comment": "Imported from facebook."
  },
  {
    "id": 23636,
    "name": "John Wayne",
    "comment": "Imported from facebook."
  }
]

.customers.

json-type column과 같은 column으로 할 때 .imported_json 컬럼은 "" " " " 입니다.datasql을 사용하여 값을 가져와 실제 테이블에 삽입합니다.

하지만 sql을 건드리지 않고 json을 postgres로 Import하는 간단한 방법이 있을까요?

정보를 추출하여 테이블에 삽입하는 SQL 문에 JSON을 입력할 수 있습니다.JSON Atribut이 테이블컬럼과 동일한 이름을 가진 경우 다음과 같은 작업을 수행할 수 있습니다.

with customer_json (doc) as (
   values 
    ('[
      {
        "id": 23635,
        "name": "Jerry Green",
        "comment": "Imported from facebook."
      },
      {
        "id": 23636,
        "name": "John Wayne",
        "comment": "Imported from facebook."
      }
    ]'::json)
)
insert into customer (id, name, comment)
select p.*
from customer_json l
  cross join lateral json_populate_recordset(null::customer, doc) as p
on conflict (id) do update 
  set name = excluded.name, 
      comment = excluded.comment;

신규 고객을 삽입하고 기존 고객을 업데이트합니다.은 '마법의' 입니다.json_populate_recordset(null::customer, doc)JSON 객체의 관계 표현을 생성합니다.

---

상기의 테이블 정의는 다음과 같습니다.

create table customer 
(
  id        integer primary key,
  name      text not null,
  comment   text
);

---

데이터가 파일로 제공되는 경우 먼저 해당 파일을 데이터베이스의 일부 테이블에 저장해야 합니다.다음과 같은 경우:

create unlogged table customer_import (doc json);

다음 파일을 해당 테이블의합니다. 를 들어,을 업로드하려면 다음과 같이 입력합니다.\copypsql가 제공하는 것이 ('SQL')

\copy customer_import from 'customers.json' ....

그런 다음 위의 문을 사용할 수 있습니다.CTE를 삭제하고 스테이징 테이블을 사용합니다.

insert into customer (id, name, comment)
select p.*
from customer_import l
  cross join lateral json_populate_recordset(null::customer, doc) as p
on conflict (id) do update 
  set name = excluded.name, 
      comment = excluded.comment;

명령줄 psql 도구를 사용하여 여러 줄의 JSON 개체를 postgres 데이터베이스의 JSON 열로 쉽게 Import할 수 있습니다.SQL 문에 JSON을 명시적으로 삽입할 필요는 없습니다.이 기술은 postgresql 문서에 기재되어 있습니다만, 조금 숨겨져 있습니다.

백틱을 사용하여 JSON을 psql 변수에 로드하는 방법이 있습니다.예를 들어 /tmp/test.json에 다음과 같은 여러 줄의 JSON 파일이 있습니다.

{
  "dog": "cat",
  "frog": "frat"
}

다음 SQL을 사용하여 임시 테이블에 로드할 수 있습니다.

sql> \set content `cat /tmp/test.json`
sql> create temp table t ( j jsonb );
sql> insert into t values (:'content');
sql> select * from t;

결과는 다음과 같습니다.

               j                
────────────────────────────────
 {"dog": "cat", "frog": "frat"}
(1 row)

데이터에 대한 작업을 직접 수행할 수도 있습니다.

sql> select :'content'::jsonb -> 'dog';
 ?column? 
──────────
 "cat"
(1 row)

여기에서는 JSON을 SQL에 내장하고 있을 뿐이지만 psql이 보간 자체를 실행할 수 있도록 하는 것이 매우 편리합니다.

빅데이터에 가까운 경우 외부 도구를 사용하지 않고 파일에서 json을 가져오는 가장 효율적인 방법은 파일에서 단일 json을 가져오지 않고 단일 열 csv: 한 줄 json 목록:

data.json.csv:

{"id": 23635,"name": "Jerry Green","comment": "Imported from facebook."}
{"id": 23636,"name": "John Wayne","comment": "Imported from facebook."}

psql에서 다음을 수행합니다.

create table t ( j jsonb )
\copy t from 'd:\path\data.json.csv' csv quote e'\x01' delimiter e'\x02'

json(라인)당 1개의 레코드가 t 테이블에 추가됩니다.

"\copy from" Import가 csv용으로 이루어졌기 때문에 데이터를 한 줄씩 로드합니다.그 결과 나중에 분할되는 단일 json 배열이 아닌 한 줄에 하나의 json을 읽으면 중간 테이블을 사용하지 않고 높은 throughput을 달성할 수 있습니다.

입력 json 파일이 너무 클 경우 발생하는 최대 입력 라인 크기 제한에 도달할 가능성이 낮아집니다.

따라서 먼저 입력 내용을 단일 열 csv로 변환하고 copy 명령을 사용하여 Import합니다.

spyql을 사용할 수 있습니다.다음 명령을 실행하면 psql에 파이프로 연결할 수 있는 INSERT 문이 생성됩니다.

$ jq -c .[] customers.json | spyql -Otable=customer "SELECT json->id, json->name, json->comment FROM json TO sql"
INSERT INTO "customer"("id","name","comment") VALUES (23635,'Jerry Green','Imported from facebook.'),(23636,'John Wayne','Imported from facebook.');

jq를 사용하여 json 어레이를 json 행(행당 1 json 객체)으로 변환한 다음 spyql을 사용하여 json 행을 INSERT 문으로 변환합니다.

데이터를 Postgre로 Import하려면SQL:

$ jq -c .[] customers.json | spyql -Otable=customer "SELECT json->id, json->name, json->comment FROM json TO sql" | psql  -U your_user_name -h your_host your_database

면책사항:저는 spyql의 저자입니다.

명령줄에서 실행할 경우...

메모: JSON을 SQL로 변환해야 하므로 질문에 대한 직접적인 답변은 아닙니다.어쨌든 변환 시에는 JSON 'null'을 처리해야 합니다.단, 뷰 또는 구체화된 뷰를 사용하여 문제를 보이지 않게 만들 수 있습니다.

다음은 JSON을 PostgreSQL(WSL Ubuntu)로 Import하기 위해 사용한 스크립트입니다.기본적으로 psql meta 명령어와 SQL을 같은 명령줄로 혼재시켜야 합니다.의사 tty를 할당하는 다소 불분명한 스크립트명령어의 사용에 주의해 주세요.

$ more update.sh
#!/bin/bash
wget <filename>.json
echo '\set content `cat $(ls -t <redacted>.json.* | head -1)` \\ delete from <table>; insert into <table> values(:'"'content'); refresh materialized view <view>; " | PGPASSWORD=<passwd> psql -h <host> -U <user> -d <database>
$

(파일 내의 pgsql 명령어를 실행하기 위해 Shell 스크립트의 답변에서 복사)

또 다른 옵션은 슬링을 사용하는 것입니다.JSON 파일을 PG에 로드하는 방법에 대해서는, 이 블로그의 투고를 참조해 주세요.json 파일을 다음과 같이 파이핑할 수 있습니다.

$ export POSTGRES='postgresql://...'

$ sling conns list
+------------+------------------+-----------------+
| CONN NAME  | CONN TYPE        | SOURCE          |
+------------+------------------+-----------------+
| POSTGRES   | DB - PostgreSQL  | env variable    |
+------------+------------------+-----------------+

$ cat /tmp/records.json | sling run --tgt-conn POSTGRES --tgt-object public.records --mode full-refresh
11:09AM INF connecting to target database (postgres)
11:09AM INF reading from stream (stdin)
11:09AM INF writing to target database [mode: full-refresh]
11:09AM INF streaming data
11:09AM INF dropped table public.records
11:09AM INF created table public.records
11:09AM INF inserted 500 rows in 0 secs [1,556 r/s]
11:09AM INF execution succeeded

사용.debug모드에서는 DDL이 표시됩니다.create table if not exists public.records ("data" jsonb)JSON을 평평하게 하고 싶다면 슬링도 마찬가지로 JSON을 평평하게 합니다.--src-options 'flatten: true'옵션:

$ cat /tmp/records.json | sling run --src-options 'flatten: true' --tgt-conn POSTGRES --tgt-object public.records --mode full-refresh

이 경우 DDL은 다음과 같습니다.

create table if not exists public.records ("_id" varchar(255),
"age" integer,
"balance" varchar(255),
"company__about" text,
"company__address" varchar(255),
"company__email" varchar(255),
"company__latitude" numeric,
"company__longitude" numeric,
"company__name" varchar(255),
"company__phone" varchar(255),
"company__registered" varchar(255),
"isactive" bool,
"name" varchar(255),
"picture" varchar(255),
"tags" jsonb)

참고로 저는 슬링의 저자입니다.

언급URL : https://stackoverflow.com/questions/39224382/how-can-i-import-a-json-file-into-postgresql