Amazon Redshift는 패치 198부터 새 Python UDF 생성을 더 이상 지원하지 않습니다. 기존 Python UDF는 2026년 6월 30일까지 계속 작동합니다. 자세한 내용은 [블로그 게시물](https://aws.amazon.com/blogs/big-data/amazon-redshift-python-user-defined-functions-will-reach-end-of-support-after-june-30-2026/)을 참조하세요.
# Amazon Redshift에서 반정형 데이터를 사용하는 예
다음 예에서는 PartiQL 구문을 사용하여 Amazon Redshift에서 반정형 데이터로 작업하는 방법을 보여줍니다. 샘플 테이블을 생성하여 반정형 데이터 샘플 세트를 로드한 다음 다양한 사용 사례에서 반정형 데이터 객체를 쿼리합니다.
**참고**
SUPER 데이터 형식으로 작업하기 전에 `enable_case_sensitive_identifier` 및 `enable_case_sensitive_super_attribute` 구성 옵션을 설정하는 것이 좋습니다. 자세한 내용은 [enable\$1case\$1sensitive\$1identifier](r_enable_case_sensitive_identifier.md) 및 [enable\$1case\$1sensitive\$1super\$1attribute](r_enable_case_sensitive_super_attribute.md)(을)를 참조하세요.
## 반정형 데이터 로드
다음 문은 샘플 테이블을 생성하고 샘플 JSON 객체를 `all_data` SUPER 열에 로드합니다.
```
DROP TABLE IF EXISTS test_json;
SET enable_case_sensitive_super_attribute TO true;
SET enable_case_sensitive_identifier TO true;
CREATE TABLE test_json (all_data SUPER);
INSERT INTO test_json VALUES (JSON_PARSE('
{
"data":{
"pnr":{
"type":"pnr",
"pnrid":"123PQRS-2024-09-20",
"bookingIdentifier":"123PQRS",
"version":"5",
"triggerType":"",
"events":[
{
"eventType":"UPDATED",
"type":"PART",
"id":"123PQRS-2024-09-20-HO-1"
},
{
"eventType":"CREATED",
"type":"ABC",
"id":"123PQRS-2024-09-20-OT-38"
}
],
"create":{
"pnrCreateDate":"2024-09-20T16:56:00Z",
"officeID":"OFFCID1234",
"officeIDCategory":"Email"
},
"lastModification":{
"dateTime":"2024-09-20T17:09:00Z"
},
"PARTDetails":[
{
"path":"1",
"TrainPARTs":[
{
"PARTID":"123PQRS-2024-09-20-HO-1",
"departure":{
"departureStation":"XYZ",
"departureTimeLocal":"2024-10-03T06:30:00",
"departureTimeGMT":"2024-10-03T10:30:00Z"
},
"arrival":{
"arrivalStation":"ABC",
"arrivalTimeLocal":"2024-10-03T08:20:00",
"arrivalTimeGMT":"2024-10-03T15:20:00Z"
},
"marketing":{
"carrierCode":"XX",
"TrainNumber":"100"
},
"operating":{
"carrierCode":"YY",
"TrainNumber":"100-A"
},
"status":"ON",
"aircraft":{
"code":"222"
},
"class":"WC",
"first":"Y",
"seating":[
]
}
]
}
],
"commuterInformation":[
{
"commuterID":"2",
"commuterPNR":"123PQRS-2024-09-20-RO-2",
"commuterTypeCode":"DOM",
"firstName":"JOHN",
"lastName":"MILLER"
}
],
"contactDetail":[
{
"emailContacts":[
{
"id":"123PQRS-2024-09-20-OT-4",
"contact":"JOHNMILLER@EXAMPLE.COM",
"purpose":[
"BUSINESS"
],
"commuter":[
"123PQRS-2024-09-20-RO-2"
],
"language":"EN"
},
{
"id":"123PQRS-2024-09-20-OT-5",
"contact":"HARVEYCORMIER@EXAMPLE.COM",
"purpose":[
"NOTIFICATION"
],
"commuter":[
"123PQRS-2024-09-20-RO-2"
],
"language":"EN"
}
]
},
{
"phoneContacts":[
{
"id":"123PQRS-2024-09-20-OT-3",
"contact":"1234567890",
"purpose":[
"NOTIFICATION"
],
"commuter":[
"123PQRS-2024-09-20-RO-2"
],
"language":""
}
]
},
{
"addressInfo":[
{
"id":"123PQRS-2024-09-20-OT-6",
"addressline":[
"112 PORT STREET"
],
"provinceState":"CA",
"cityName":"SAN JOSE",
"postalCode":"12345",
"countryCode":"USA",
"purpose":[
"MAILING"
],
"commuter":[
"123PQRS-2024-09-20-RO-2"
]
}
]
}
],
"PendingService":[
{
"id":"123PQRS-2024-09-20-OT-26",
"code":"MONO",
"status":"",
"text":"Broken Seat at Coach-No XYZ123 Seat-No 567",
"trainCode":"WC-1",
"TrainsArray":[
"123PQRS-2024-09-20-HO-1"
],
"commuter":[
"123PQRS-2024-09-20-RO-2"
]
},
{
"id":"123PQRS-2024-09-20-OT-27",
"code":"OTHS",
"status":"",
"text":"Broken Seat at Coach-No XYZ567 Seat-No 111",
"trainCode":"WC-1",
"TrainsArray":[
"123PQRS-2024-09-20-HO-1"
],
"commuter":[
"123PQRS-2024-09-20-RO-2"
]
},
{
"id":"123PQRS-2024-09-20-OT-28",
"code":"OTHS",
"status":"",
"text":"Broken Seat at Coach-No XYZ890 Seat-No 123",
"trainCode":"WC-1",
"TrainsArray":[
"123PQRS-2024-09-20-HO-1"
],
"commuter":[
"123PQRS-2024-09-20-RO-2"
]
},
{
"id":"123PQRS-2024-09-20-OT-29",
"code":"OTHS",
"status":"",
"text":"Broken Seat at Coach-No XYZ111 Seat-No 333",
"trainCode":"WC-1",
"TrainsArray":[
"123PQRS-2024-09-20-HO-1"
],
"commuter":[
"123PQRS-2024-09-20-RO-2"
]
}
],
"parts": [
{
"partname": "prop",
"manufacturer": "local parts co",
"quality": 2,
"price": 10.00
},
{
"partname": "prop",
"manufacturer": "big parts co",
"quality": null,
"price": 9.00
},
{
"partname": "prop",
"manufacturer": "small parts co",
"quality": 1,
"price": 12.00
},
{
"partname": "rudder",
"manufacturer": "local parts co",
"quality": 1,
"price": 2.50
},
{
"partname": "rudder",
"manufacturer": "big parts co",
"quality": 2,
"price": 3.75
},
{
"partname": "rudder",
"manufacturer": "small parts co",
"quality": null,
"price": 1.90
},
{
"partname": "wing",
"manufacturer": "local parts co",
"quality": null,
"price": 7.50
},
{
"partname": "wing",
"manufacturer": "big parts co",
"quality": 1,
"price": 15.20
},
{
"partname": "wing",
"manufacturer": "small parts co",
"quality": null,
"price": 11.80
}
],
"count_by_color": [
{
"quality": "high",
"red": 15,
"green": 20,
"blue": 7
},
{
"quality": "normal",
"red": 35,
"green": null,
"blue": 40
},
{
"quality": "low",
"red": 10,
"green": 23,
"blue": null
}
]
}
},
"id":"abcdefgh-ijklmnop-qrstuvwxyz123",
"mainIds":[
{
"ID":"pqrstuvwxyz-aabbcc123",
"Source":"NYC"
}
]
}
'));
```
## 중첩된 반정형 데이터 쿼리
다음 문은 PartiQL의 점 표기법을 사용하여 최상위 `all_data` 객체 내에 3수준 깊이로 중첩된 `pnrid` 필드를 추출합니다.
```
select all_data.data.pnr.pnrid::varchar from test_json;
pnrid
--------------------
123PQRS-2024-09-20
```
다음 문은 PartiQL의 대괄호 표기법을 사용하여 최상위 객체 내에 중첩된 `events` 배열에서 첫 번째 요소만 지정하고 추출합니다.
```
SELECT
all_data.data.pnr.events[0]
FROM test_json;
events
---------------------------------
{
"eventType":"UPDATED",
"type":"PART",
"id":"123PQRS-2024-09-20-HO-1"
}
```
다음 문은 `events` 배열에서 지정된 요소의 `eventType` 속성만 추출합니다.
```
SELECT
all_data.data.pnr.events[0].eventType
FROM test_json;
eventtype
-----------
"UPDATED"
```
다음 문
## 반정형 데이터에 `enable_case_sensitive_super_attribute` 및 `enable_case_sensitive_identifier` 사용
다음 예는 구성 옵션 [enable\$1case\$1sensitive\$1identifier](r_enable_case_sensitive_identifier.md)와 [enable\$1case\$1sensitive\$1super\$1attribute](r_enable_case_sensitive_super_attribute.md)가 반정형 데이터 쿼리에 사용될 때 어떻게 다른지 보여줍니다. 이러한 구성 옵션에 대한 자세한 내용은 [대문자 및 대소문자 혼합 필드 이름 또는 속성이 포함된 JSON 필드에 액세스](super-configurations.md#upper-mixed-case) 섹션을 참조하세요.
다음 문에서 두 구성 옵션을 모두 기본값 false로 재설정하면 쿼리가 NULL을 반환합니다.
```
RESET enable_case_sensitive_identifier;
RESET enable_case_sensitive_super_attribute;
SELECT
all_data.data.pnr.events[0].eventType
FROM test_json;
eventtype
-----------
NULL
```
다음 예에서 샘플 쿼리는 대소문자를 구분하는 속성을 큰따옴표로 묶고 `enable_case_sensitive_identifier`를 true로 설정한 후 원하는 결과를 반환합니다.
```
RESET enable_case_sensitive_identifier;
RESET enable_case_sensitive_super_attribute;
SELECT
all_data.data.pnr.events[0]."eventType"
FROM test_json;
eventtype
-----------
NULL
SET enable_case_sensitive_identifier TO true;
SELECT
all_data.data.pnr.events[0]."eventType"
FROM test_json;
eventtype
-----------
"UPDATED"
```
다음 예에서 샘플 쿼리는 대소문자를 구분하는 속성을 큰따옴표로 묶지 않고 `enable_case_sensitive_super_attribute`를 true로 설정한 후 원하는 결과를 반환합니다.
```
RESET enable_case_sensitive_identifier;
RESET enable_case_sensitive_super_attribute;
SELECT
all_data.data.pnr.events[0].eventType
FROM test_json;
eventtype
-----------
NULL
SET enable_case_sensitive_super_attribute TO true;
SELECT
all_data.data.pnr.events[0].eventType
FROM test_json;
eventtype
-----------
"UPDATED"
```
## 반정형 데이터 필터링
다음 문은 `UPDATED` 유형의 이벤트 수를 계산하는 명령문의 WHERE 절에서 PartiQL 구문을 사용하여 배열 내부에서 특정 속성의 데이터를 검색합니다. 일반적으로 열을 참조하는 쿼리의 모든 부분에서 이 구문을 사용할 수 있습니다.
```
SELECT COUNT(*)
FROM test_json
WHERE all_data.data.pnr.events[0].eventType = 'UPDATED';
count
------
1
```
다음 예에서는 GROUP BY 및 ORDER BY 절에 PartiQL의 대괄호와 점 구문을 사용합니다.
```
SELECT all_data.data.pnr.events[0].eventType::varchar,
COUNT(*)
FROM test_json
WHERE all_data.data.pnr.events[0].eventType IS NOT NULL
GROUP BY all_data.data.pnr.events[0].eventType
ORDER BY all_data.data.pnr.events[0].eventType;
eventtype | count
-----------+-------
"UPDATED" | 1
```
## 반정형 데이터 중첩 해제
다음 문은 PartiQL 조인을 사용하여 `events` 배열의 중첩을 해제합니다. 이 조인은 배열의 인덱스 수가 정적이지 않은 경우에도 작동합니다.
FROM 절에서 UNNEST를 사용하여 반정형 데이터를 중첩 해제하는 예는 [UNNEST 예제](r_FROM_clause-unnest-examples.md) 섹션을 참조하세요.
```
SELECT
a.all_data.data.pnr.type::varchar type_info,
a.all_data.data.pnr.pnrid::varchar pnr_id ,
a.all_data.data.pnr.bookingIdentifier::varchar booking_id,
a.all_data.data.pnr.version::varchar version_info,
b.eventType::varchar event_type,
b.id::varchar event_id
FROM test_json a,
a.all_data.data.pnr.events b;
type_info | pnr_id | booking_id | version_info | event_type | event_id
-----------+---------------------+------------+--------------+------------+-------------------------
pnr | 123PQRS-2024-09-20 | 123PQRS | 5 | UPDATED | 123PQRS-2024-09-20-HO-1
pnr | 123PQRS-2024-09-20 | 123PQRS | 5 | CREATED | 123PQRS-2024-09-20-OT-38
```
## 중첩된 배열 중첩 해제
다음 문은 PartiQL 조인을 사용하여 다른 배열 내에 중첩된 배열을 중첩 해제합니다.
FROM 절에서 UNNEST를 사용하여 반정형 데이터를 중첩 해제하는 예는 [UNNEST 예제](r_FROM_clause-unnest-examples.md) 섹션을 참조하세요.
```
SELECT
a.all_data.data.pnr.type::varchar type_info,
a.all_data.data.pnr.pnrid::varchar pnr_id ,
a.all_data.data.pnr.bookingIdentifier::varchar booking_id,
a.all_data.data.pnr.version::varchar version_info,
d.id::varchar email_record_id,
d.contact::varchar email_contact,
e::varchar email_purpose,
f::varchar email_commuter
FROM test_json a,
a.all_data.data.pnr.contactDetail c,
c."emailContacts" d,
d.purpose e,
d.commuter f;
type_info | pnr_id | booking_id | version_info | email_record_id | email_contact | email_purpose | email_commuter
-----------+---------------------+------------+--------------+-------------------------+---------------------------+---------------+-------------------------
pnr | 123PQRS-2024-09-20 | 123PQRS | 5 | 123PQRS-2024-09-20-OT-4 | JOHNMILLER@EXAMPLE.COM | BUSINESS | 123PQRS-2024-09-20-RO-2
pnr | 123PQRS-2024-09-20 | 123PQRS | 5 | 123PQRS-2024-09-20-OT-5 | HARVEYCORMIER@EXAMPLE.COM | NOTIFICATION | 123PQRS-2024-09-20-RO-2
```
## 하위 쿼리에서 반정형 데이터 사용
다음 문은 WHERE 절의 하위 쿼리를 사용하여 이전 예의 결과 중 일부만 반환합니다.
```
SELECT
a.all_data.data.pnr.type::varchar type_info,
a.all_data.data.pnr.pnrid::varchar pnr_id ,
a.all_data.data.pnr.bookingIdentifier::varchar booking_id,
a.all_data.data.pnr.version::varchar version_info,
d.id::varchar email_record_id,
d.contact::varchar email_contact
FROM test_json a,
a.all_data.data.pnr.contactDetail c,
c."emailContacts" d
WHERE (SELECT COUNT(*) FROM d.purpose e WHERE e = 'BUSINESS') > 0;
type_info | pnr_id | booking_id | version_info | email_record_id | email_contact | email_purpose | email_commuter
-----------+---------------------+------------+--------------+-------------------------+---------------------------+---------------+-------------------------
pnr | 123PQRS-2024-09-20 | 123PQRS | 5 | 123PQRS-2024-09-20-OT-4 | JOHNMILLER@EXAMPLE.COM | BUSINESS | 123PQRS-2024-09-20-RO-2
```
## 반정형 데이터를 사용하여 쿼리 집계
다음 문은 COUNT 함수를 사용하여 `PendingService` 배열의 요소 수를 집계합니다.
```
SELECT
a.all_data.data.pnr.type::varchar type_info,
a.all_data.data.pnr.pnrid::varchar pnr_id ,
a.all_data.data.pnr.bookingIdentifier::varchar booking_id,
a.all_data.data.pnr.version::varchar version_info,
COUNT(*) AS total_pending_service
FROM test_json a,
a.all_data.data.pnr.PendingService c
GROUP BY 1,2,3,4;
type_info | pnr_id | booking_id | version_info | total_pending_service
-----------+--------------------+------------+--------------+-----------------------
pnr | 123PQRS-2024-09-20 | 123PQRS | 5 | 4
```
## 구체화된 뷰에서 반정형 데이터 사용
다음 문은 이전 예의 문을 사용하여 구체화된 뷰를 생성합니다. 구체화된 뷰는 기본 테이블에 새 데이터가 들어오면 보류 중인 서비스 수를 자동으로 새로 고칩니다.
```
CREATE MATERIALIZED VIEW mv_total_pending_service
AUTO REFRESH YES
AS
SELECT
a.all_data.data.pnr.type::varchar type_info,
a.all_data.data.pnr.pnrid::varchar pnr_id ,
a.all_data.data.pnr.bookingIdentifier::varchar booking_id,
a.all_data.data.pnr.version::varchar version_info,
COUNT(*) AS total_pending_service
FROM test_json a,
a.all_data.data.pnr.PendingService c
GROUP BY 1,2,3,4;
```
## 반정형 데이터에 PIVOT 및 UNPIVOT 사용
다음 문은 `partname` 열에서 PIVOT을 사용하여 각 부품의 평균 가격을 반환합니다.
```
SELECT *
FROM
(
SELECT
c.partname::varchar, c.price
FROM test_json a,
a.all_data.data.pnr.parts c)
PIVOT (AVG(price) for partname IN ('prop', 'rudder', 'wing'));
prop | rudder | wing
------------+--------------------+--------
10.33 | 2.71 | 11.50
```
이전 예에서 결과는 열로 변환됩니다. 다음 예는 평균 가격을 열이 아닌 행으로 반환하는 GROUP BY 쿼리를 보여줍니다.
```
SELECT partname, avg(price)
FROM (
SELECT
c.partname::varchar, c.price
FROM test_json a,
a.all_data.data.pnr.parts c)
WHERE partname IN ('prop', 'rudder', 'wing')
GROUP BY partname;
partname | avg
----------+-------
prop | 10.33
rudder | 2.71
wing | 11.50
```
다음은 `manufacturer`를 암시적 열로 사용하는 PIVOT 예입니다.
```
SELECT *
FROM (
SELECT
c.quality, c.manufacturer::varchar
FROM test_json a,
a.all_data.data.pnr.parts c) PIVOT (
count(*) FOR quality IN (1, 2, NULL)
);
manufacturer | 1 | 2 | null
-------------------+----+----+------
local parts co | 1 | 1 | 1
big parts co | 1 | 1 | 1
small parts co | 1 | 0 | 2
```
다음은 `quality` 열의 UNPIVOT 예입니다.
```
SELECT *
FROM
(
SELECT
c.quality as quality
FROM test_json a,
a.all_data.data.pnr.parts c)
UNPIVOT (cnt FOR column_header IN (quality));
column_header | cnt
-----------------+----
quality | 2
quality | 1
quality | 1
quality | 2
quality | 1
```