The EDB Blog
April 7, 2016

오늘날의 커넥티드 엔터프라이즈에는 정형 데이터와 비정형 데이터를 모두 처리할 수 있고 빠른 변화와 함께 새롭게 나타나는 데이터 유형에 역동적으로 적응할 수 있는 단일 데이터베이스가 필요합니다. 많은 조직에 있어 그러한 데이터베이스가 바로 Postgres입니다. Postgres는 JSON으로 문서 데이터베이스와 관계형 테이블스페이스를 함께 지원할 수 있으며 정형 데이터와 비정형 데이터를 결합할 수도 있습니다. 이로써 조직은 원자성, 일관성, 고립성, 지속성(ACID) 특성을 충족할 수 있으며 데이터 무결성을 확보하는 데 필요한 공통 비즈니스 로직도 제공됩니다.

Postgres 데이터베이스 관리자는 Postgres의 NoSQL 기능을 활용함으로써 전문 기술을 확대할 수 있으며 이러한 스킬에 중요한 것이 바로 JSON을 사용하여 문서 데이터베이스를 구축하는 것입니다. 이 글은 DBA가 Postgres의 새로운 이점을 구현할 수 있는 능력을 강화하고 기존 데이터 센터를 혁신 플랫폼으로 변환하는 데 도움을 주기 위한 시리즈의 두 번째 게시물입니다. 첫 번째 게시물, 관계 테이블스페이스에서 JSON 문서 만들기(게시 날짜: 2016년 3월 3일)는 47,000개 공항의 데이터 세트를 사용하여 PostgreSQL 9.5로 관계형 테이블스페이스에서 중첩된 JSON 문서를 만드는 방법을 설명했습니다.

Enterprise-ready Postgres tools for high availability, monitoring, and disaster recovery. Download Now.

 This post explores the Create, Read, Update, and Delete operations in PostgreSQL, known as CRUD. It is the second in a series of posts exploring PostgreSQL's unique JSON capabilities within the RDBMS framework. The (many) examples use the airports.json data set created in the previous post and can be downloaded here.  (* READ includes CREATE, UPDATE and DELETE. READ and indexing examples will be explored in a later post in this series.)

airports.json 데이터 세트에는 전 세계 47,000여 공항에 대한 문서가 포함되어 있습니다. 이 문서는 http://ourairports.com 데이터 세트에 있는 6개의 csv 파일 / RDBMS 테이블스페이스를 기반으로 합니다. 자세한 내용은 이전 게시물을 참조하십시오.

PostgreSQL의 JSON CRUD 기능은 PostgreSQL로 만들 수 있는 JSON 어플리케이션의 범위를 확장시켜 줍니다. 이러한 기능으로 인해 개발자들이 이전의 제한적인 워크플로우 요구에 유연하게 대처할 수 있기 때문입니다.

  1. 어플리케이션에서 JSON 생성 / 외부 소스에서 JSON 가져오기
  2. 데이터베이스에 JSON 문서 저장
  3. 필요할 때 문서 검색
  4. 다음 방법으로 JSON 수정

a. 어플리케이션 계층으로 문서 검색

b. 어플리케이션 코드를 사용하여 변경

c. 변경 완료와 함께 데이터베이스에 문서 다시 저장

이제 개발자는 PostgreSQL의 JSON CRUD 기능으로 다음과 같은 작업을 수행할 수 있습니다.

  1. PostgreSQL 테이블스페이스, 어플리케이션 코드 또는 외부 소스에서 JSON(및 GeoJSON!) 생성
  2. PostgreSQL에 JSON 문서 저장
  3. SQL을 사용하여 태그, 값, 어레이, 하위 문서 및 문서 검색
  4. CREATE를 사용하여 PostgreSQL 내에서 JSON

수정

*.json 파일에서 로드하여 문서를 생성하고 문서 내에서 새 요소를 만듭니다.

*.json 문서 파일을 PostgreSQL에 로드

airports.json 파일은 여기서 다운로드하거나 블로그 게시물, 관계형 테이블스페이스에서 JSON 문서 만들기의 SQL을 사용하여 만들 수 있습니다.

참고: *.json 파일 로드는 다른 데이터 유형을 로드하는 것과 같이 생각하면 됩니다.  본인은 일반적으로 외부 데이터 래퍼(FDW)가 간단하고 빠르며 문제를 쉽게 해결할 수 있기 때문에 FDW를 사용합니다.  COPY를 사용하는 구문은 다음 참조 예와 같이 훨씬 더 간단합니다.

 

CREATE EXTENSION IF NOT EXISTS file_fdw;

CREATE SERVER ext_table FOREIGN DATA WRAPPER file_fdw;

CREATE FOREIGN TABLE airports_fdw (

  airport  JSONB) SERVER ext_table

  OPTIONS (filename '/path/to/file/airports.json', format 'csv',
  header 'false', delimiter '|', quote '$');

-- delimiter and quote should not appear in the file

 

CREATE TABLE airports (

  id       BIGSERIAL PRIMARY KEY,

  airport JSONB);

 

INSERT INTO airports (airport)

  (SELECT airport FROM airports_fdw);

 

COPY를 사용하여 로드

더 간단하지만 제한적인 대안은 COPY(http://www.postgresql.org/docs/9.5/static/sql-copy.html)를 사용하는 것입니다.

 

COPY airports (airport)

FROM '/path/to/file/airports.json'

WITH delimiter '|' quote '$' CSV;

 

레코드 예 참조

Baltimore/Washington International Thurgood Marshall Airport(KBWI)의 예를 참조하십시오.

 

SELECT JSONB_PRETTY(airport)

FROM airports

WHERE airport ->> 'ident' = 'KBWI';
 

참고: ->> operator returns JSONB.  -> returns TEXT/ INT.  http://www.postgresql.org/docs/9.5/static/functions-json.html 참조

 

외부 수준 태그 및 값 추가

공항 JSON 문서에 해당 공항을 취항하는 항공사를 저장하는 태그가 없습니다.  JSONB 연결 연산자, || jsonb_set. 를 사용하여 Southwest Airlines 허브에 carrier_hub 태그를 추가합니다.

 

UPDATE airports

SET airport = airport || '{"carrier_hub": ["Southwest"]}'
WHERE airport ->> 'ident' IN (
  'KBWI',   -- Baltimore-Washington International

  'KMDW',   -- Chicago Midway

  'KLAS',   -- Las Vegas

  'KDAL');  -- Love Field, Dallas;

 

공항 JSON

jsonb 세트에 id 태그를 추가합니다.http://www.postgresql.org/docs/9.5/static/functions-json.html은 PostgreSQL JSON CRUD 주 연산자입니다.  선택적 create missing 플래그에 유의하십시오. 이 플래그가 false(기본값)면 jsonb_set가 INSERT와 같은 기능을 수행하고 true면 UPSERT와 같은 기능을 수행합니다.

jsonb_set는 PostgreSQL 내 다른 데이터(예: 열, 변수 등)에 액세스할 수 있습니다.

 

UPDATE airports_json

SET airport = jsonb_set(

  airport,

  '{id}',

  id::TEXT::JSONB,

  true); -- creates id column in JSON using airports_json.id

 

결과 확인:

 

SELECT

  airport ->> 'name',

  airport ->> 'ident',

  airport ->> 'carrier_hub'

FROM airports

WHERE airport ->> 'ident' IN ('KBWI', 'KMDW', 'KLAS', 'KDAL');

 

어레이에 요소 추가

airport_keyword 태그는 공항과 연관된 도시의 어레이입니다.  예를 들어, Baltimore/Washington International Thurgood Marshall Airport(KBWI), Ronald Reagan Washington National Airport(KDCA) 및 Washington Dulles International Airport(KIAD)는 모두 WAS 요소가 있습니다.

아래 SQL은 서브쿼리에 연결 연산자, ||JSONB_SET를 사용하여 BWI(Baltimore-Washington Airport)의 airport_keyword 어레이에 'BAL'을 추가합니다.

 

UPDATE airports

SET airport = JSONB_SET(

  airport,

  '{airport_keywords}',

  (SELECT (airport -> 'airport_keywords') || TO_JSONB('BWI'::TEXT)
  FROM airports WHERE airport ->> 'ident' = 'KBWI'),
  false)

WHERE airport ->> 'ident' = 'KBWI';

 

결과 확인

 

SELECT airport ->> 'airport_keywords'

FROM airports

WHERE airport ->> 'ident' = 'KBWI';

 

READ

문서, 하위 문서, 어레이, 태그 및 값 읽기는 인덱싱과 함께 다음 블로그 게시물에서 다룹니다.

 

UPDATE

첫 번째 수준 태그의 값 변경

Salisbury Ocean City Wicomico Regional Airport, KSBY를 medium_airport에서 large_airport로 변경합니다.

 

Check type first

 

SELECT airport ->> 'type'

FROM airports

WHERE airport ->> 'ident' = 'KSBY';

 

업데이트 type

 

UPDATE airports

SET airport = JSONB_SET(

  airport,

  '{type}',

  TO_JSONB('large airport'::TEXT),

  false)

WHERE airport ->> 'ident' = 'KSBY';

 

재확인 type

 

중첩된 문서에서 태그 값 변경

Howard County General Hospital Heliport, MD25, 활주로 표면을 'GRASS / SOD'에서 'ASPH'로 변경합니다.

여기서는 중첩된 JSONB_SET를 사용했습니다. 내부 수준은 활주로 중첩 문서의 값을 변경하고 외부 수준은 상위 문서를 업데이트합니다.

Check the surface first:

 

SELECT JSONB_ARRAY_ELEMENTS(airport -> 'runways') ->> 'surface'

FROM airports

WHERE airport ->> 'ident' = 'MD25';

 

JSONB_ARRAY_ELEMENTS는 각 JSONB 하위 문서의 열을 반환합니다.

Linux처럼 JSONB를 반환하는 -> 연산자를 일련의 파이프에서 고려하는 것은 유용한 방법입니다.  이 예에서는 하위 문서의 runways 어레이에서 첫 번째(또한 유일한) 요소가 ->> 연산자에 '파이핑'되어 TEXT / INT/를 반환합니다.

값을 업데이트합니다.

 

UPDATE airports

SET airport = JSONB_SET(

  airport,

  '{runways}',

  (WITH heli_runs AS (

    SELECT airport -> 'runways' AS heli_run

    FROM airports

    WHERE airport ->> 'ident' = 'MD25')

  SELECT JSONB_SET(

    JSONB_ARRAY_ELEMENTS(heli_run),

    '{surface}',

    TO_JSONB('asph'::text),

    false)

  FROM heli_runs),

  false)

WHERE airport ->> 'ident' = 'MD25';

재확인 surface

 

DELETE

외부 수준 태그 제거

마이너스 연산자, '-'를 사용하여 airport_wikipediaregion_wikipedia 태그를 각 문서에서 제거합니다.

 

UPDATE airports

SET airport = (airport - 'airport_wikipedia' - 'region_wikipedia');

어레이에서 요소 제거

John F. Kennedy International Airport, KJFK의 airport_keywords 어레이에서 'Idlewild' 요소를 제거합니다.

 

UPDATE airports
SET airport =

JSONB_SET(

  airport,

  '{airport_keywords}',
  (WITH kjfk_keys AS (

    SELECT JSONB_ARRAY_ELEMENTS(airport -> 'airport_keywords') keywords

    FROM airports

    WHERE airport ->> 'ident' = 'KJFK')
  SELECT JSONB_AGG(keywords)

  FROM kjfk_keys

  WHERE keywords::TEXT != '"Idlewild"'))
WHERE airport ->> 'ident' = 'KJFK';

 

하위 문서에서 태그 제거

BWI 공항에는 다음과 같은 4개 활주로가 있습니다.

●22 / 04                           6,000 ft

●28 / 10                         10,520 ft

●33R / 15L                       5,000 ft

●33L / 15R                       9,501 ft

 

참고: 활주로의 이름은 10 *비행기가 중앙선에 있을 때 향하는 자기 방향으로 지정됩니다. 예를 들어, 활주로 28은 정서쪽 약 280°를 가리킵니다.  두 번째 이름은 다른 방향의 같은 활주로입니다(180° 차이 또는 18을 뺌).  활주로 22 - 18 = 활주로 04.  평행 활주로는 같은 규칙을 따르며 L(왼쪽) 또는 R(오른쪽)을 추가합니다.

이 SQL은 BWI 활주로 '28'에서 le_displaced_threshold_ft를 제거합니다.

참고: 활주로의 대체 임계값은 비행기가 착륙해야 하는 활주로 노면의 시작 지점을 지난 거리입니다.

 

UPDATE airports

SET airport =

JSONB_SET(

  airport,

  '{runways}',

  (SELECT

    (WITH kbwi_runways AS (

      SELECT JSONB_ARRAY_ELEMENTS (airport -> 'runways') runway

      FROM airports

      WHERE airport ->> 'ident' = 'KBWI')

    SELECT runway - 'le_displaced_threshold_ft'

    FROM kbwi_runways

    WHERE runway ->> 'he_ident' = '28')

    ||

    (WITH kbwi_runways AS (

      SELECT JSONB_ARRAY_ELEMENTS(airport -> 'runways') AS runway

      FROM airports

      WHERE airport ->> 'ident' = 'KBWI')

    SELECT JSONB_AGG(runway)

    FROM kbwi_runways

    WHERE runway ->> 'he_ident' != '28'))

  )

WHERE airport ->> 'ident' = 'KBWI';

 

이 SQL의 로직은 다음과 같습니다.

  1. JSONB 어레이의 각 요소를 결과 세트 행으로 반환하는 JSONB ARRAY ELEMENTS로 각 활주로의 row/JSONB_document를 만듭니다.
  2. 활주로 '28'의 행을 찾아 대체된 le displaced threshold ft '태그를 제거합니다.
  3. 수정된 활주로 '28' 하위 문서를 다른 활주로 하위 문서와 연결합니다.
  4. 업데이트된 어레이 또는 활주로로 BWI 공항 JSONB 문서를 업데이트합니다.

활주로가 하나만 있거나 활주로 '28'의 어레이 위치를 알고 있으면 더 간단합니다.

 

요약

PostgreSQL 9.5에는 새로운 사용 사례, 데이터 모델은 물론 가능한 경우 새로운 시장까지 지원할 수 있는 강력하고 특별한 JSON CRUD 기능이 있습니다. 

Jamey Hanson은 EnterpriseDB 파트너인 Freedom Consulting Group의 데이터 아키텍트입니다.  

  

Jamey.Hanson 사진

Jamey Hanson is a PostgreSQL data architect who specializes in integrating PostgreSQL with NoSQL and other non-traditional relational data structures.  He is also exploring large-scale analytics with PL/R.  Jamey has presented at multiple PostgreSQL conferences and is a reformed Oracle developer...