탐색 쿼리 중첩 해제 객체 피벗 해제 동적 형식 지정 Lax 의미 체계 순서 기준

반정형 데이터 쿼리

Amazon Redshift를 사용하면 정형화된 데이터와 함께 JSON, Avro 또는 Ion 등의 반정형 데이터를 쿼리 및 분석할 수 있습니다. 반정형 데이터는 유연한 스키마가 있는 데이터를 나타내며 계층 구조 또는 중첩 구조를 허용합니다. 다음 섹션에서는 Amazon Redshift의 개방형 데이터 형식 지원을 통해 반정형 데이터를 쿼리하여 복잡한 데이터 구조에서 중요한 정보를 활용하는 방법을 설명합니다.

Amazon Redshift는 PartiQL 언어를 사용하여 관계형, 반정형 및 중첩 데이터에 대한 SQL 호환 액세스를 제공합니다.

PartiQL은 동적 형식으로 작업합니다. 이를 통해 정형, 반정형 및 중첩 데이터세트의 조합에 대한 직관적 필터링, 조인 및 집계가 가능합니다. PartiQL 구문은 중첩 데이터에 액세스할 때 경로 탐색을 위해 점 표기법과 배열 첨자를 사용합니다. 또한 FROM 절 항목이 배열을 반복하고 중첩 해제 작업에 사용할 수 있습니다. 다음에서 SUPER 데이터 유형 사용을 경로 및 배열 탐색, 중첩 해제, 피벗 해제 및 조인과 결합하는 다양한 쿼리 패턴에 대한 설명을 찾을 수 있습니다. PartiQL에 대한 자세한 내용은 PartiQL - Amazon Redshift용 SQL 호환 쿼리 언어 섹션을 참조하세요.

Amazon Redshift는 PartiQL을 사용하여 각각 [...] 대괄호와 점 표기법을 사용하여 배열과 구조를 탐색할 수 있도록 합니다. 또한 점 표기법을 사용하는 구조와 대괄호 표기법을 사용하는 배열을 혼합하여 탐색할 수 있습니다. 예를 들어 다음 문은 SUPER 객체의 한 수준 깊이에 중첩된 배열에서 세 번째 요소만 선택합니다.


SELECT super_object.array[2];
         
 array
---------------
 third_element

필터링, 조인, 집계와 같은 데이터 작업을 수행할 때 점과 대괄호 표기법을 사용할 수 있습니다. 일반적으로 열 참조가 있는 쿼리의 어디서나 이러한 표기법을 사용할 수 있습니다. 예를 들어 다음 문은 유형이 UPDATED인 이벤트의 수를 선택합니다.


SELECT COUNT(*)
FROM test_json
WHERE all_data.data.pnr.events[0]."eventType" = 'UPDATED';
         
 eventType | count
-----------+-------
 "UPDATED" | 1

PartiQL 탐색 사용에 대한 자세한 예는 Amazon Redshift에서 반정형 데이터를 사용하는 예 섹션을 참조하세요.

쿼리 중첩 해제

쿼리 중첩을 해제하기 위해 Amazon Redshift는 FROM 절의 PartiQL 구문과 UNNEST 작업이라는 두 가지 방법으로 SUPER 배열을 반복합니다. 두 중첩 해제 방법 모두 동일한 출력을 생성합니다. UNNEST 작업에 대한 정보는 FROM 절 섹션을 참조하세요. UNNEST 작업 사용에 대한 예는 UNNEST 예제 섹션을 참조하세요.

Amazon Redshift는 PartiQL 구문을 사용하여 쿼리의 FROM 절로 SUPER 배열을 탐색합니다. 다음 예에서는 이전 예를 사용하여 c_orders에 대한 속성 값을 반복합니다.


SELECT orders.*, o FROM customer_orders orders, orders.c_orders o;

FROM 절 항목 x (AS) y를 사용하여 중첩 해제하는 PartiQL 구문은 y가 (SUPER) 배열 표현식 x의 각 (SUPER) 값을 반복함을 의미합니다. 이 경우 x는 SUPER 표현식이고, y는 x에 대한 별칭입니다.

왼쪽 피연산자는 일반 탐색을 위해 점 및 대괄호 표기법을 사용할 수도 있습니다. 다음 예에서 customer_orders_lineitem c는 customer_order_lineitem 기본 테이블에 대한 반복이고 c.c_orders o는 c.c_orders 배열에 대한 반복입니다. 배열 내의 배열인 o_lineitems 속성을 반복하려는 경우 다음과 같이 여러 절을 추가할 수 있습니다.


SELECT c.*, o, l FROM customer_orders_lineitem c, c.c_orders o, o.o_lineitems l;

Amazon Redshift는 AT 키워드를 사용하여 배열을 반복할 때 배열 인덱스도 지원합니다. 절 x AS y AT z는 배열 x를 반복하고 배열 인덱스인 필드 z,를 생성합니다. 다음 예에서는 배열 인덱스의 작동 방식을 보여줍니다.


SELECT c_name,
       orders.o_orderkey AS orderkey,
       index AS orderkey_index
FROM customer_orders_lineitem c, c.c_orders AS orders AT index 
ORDER BY orderkey_index;

c_name             | orderkey | orderkey_index
-------------------+----------+----------------
Customer#000008251 | 3020007  |        0
Customer#000009452 | 4043971  |        0
  (2 rows)

다음은 스칼라 배열을 반복하는 예입니다.


CREATE TABLE bar AS SELECT json_parse('{"scalar_array": [1, 2.3, 45000000]}') AS data;

SELECT element, index FROM bar AS b, b.data.scalar_array AS element AT index;

 index | element
-------+----------
     0 | 1
     1 | 2.3
     2 | 45000000
(3 rows)

다음 예에서는 여러 수준의 배열을 반복합니다. 이 예제에서는 여러 unnest 절을 사용하여 가장 안쪽 배열로 반복합니다. f.multi_level_array AS 배열은 multi_level_array를 반복합니다. 배열 AS 요소는 multi_level_array 내의 배열에 대한 반복입니다.


CREATE TABLE foo AS SELECT json_parse('[[1.1, 1.2], [2.1, 2.2], [3.1, 3.2]]') AS multi_level_array;

SELECT array, element FROM foo AS f, f.multi_level_array AS array, array AS element;

 element | array
---------+---------
 1.1     | [1.1,1.2]
 1.2     | [1.1,1.2]
 2.1     | [2.1,2.2] 
 2.2     | [2.1,2.2]
 3.1     | [3.1,3.2] 
 3.2     | [3.1,3.2] 
(6 rows)

FROM 절에 대한 자세한 내용은 FROM 절 섹션을 참조하세요. SUPER 쿼리를 중첩 해제하는 더 많은 예는 Amazon Redshift에서 반정형 데이터를 사용하는 예 섹션을 참조하세요.

객체 피벗 해제

객체 피벗 해제를 수행하기 위해 Amazon Redshift는 PartiQL 구문을 사용하여 SUPER 객체를 반복합니다. UNPIVOT 키워드와 함께 쿼리의 FROM 절을 사용하여 이 작업을 수행합니다. 다음 예제에서 표현식은 c.c_orders[0] 객체입니다. 예제 쿼리는 객체가 반환한 각 속성을 반복합니다.


SELECT attr as attribute_name, json_typeof(val) as value_type 
FROM customer_orders_lineitem c, UNPIVOT c.c_orders[0] AS val AT attr 
WHERE c_custkey = 9451;

 attribute_name  | value_type
-----------------+------------
 o_orderstatus   | string
 o_clerk         | string
 o_lineitems     | array
 o_orderdate     | string
 o_shippriority  | number
 o_totalprice    | number
 o_orderkey      | number
 o_comment       | string
 o_orderpriority | string
(9 rows)

중첩 해제와 마찬가지로 피벗 해제 구문도 FROM 절의 확장입니다. 차이점은 피벗 해제 구문이 UNPIVOT 키워드를 사용하여 배열 대신 객체를 반복하고 있음을 나타냅니다. 객체 내부의 모든 값에 대한 반복에는 AS value_alias를 사용하고 모든 속성에 대한 반복에는 AT attribute_alias를 사용합니다. 다음 구문을 고려합니다.


UNPIVOT expression AS value_alias [ AT attribute_alias ]

Amazon Redshift는 다음과 같이 단일 FROM 절에서 객체 unpivoting 및 array unnesting 사용을 지원합니다.


SELECT attr as attribute_name, val as object_value
FROM customer_orders_lineitem c, c.c_orders AS o, UNPIVOT o AS val AT attr 
WHERE c_custkey = 9451;

객체 피벗 해제를 사용하는 경우 Amazon Redshift는 상관 관계가 있는 피벗 해제를 지원하지 않습니다. 특히, 서로 다른 쿼리 수준에서 피벗 해제 예가 여러 개 있고 내부 피벗 해제가 외부 쿼리 수준을 참조하는 경우가 있다고 가정합니다. Amazon Redshift는 이러한 유형의 다중 피벗 해제를 지원하지 않습니다.

FROM 절에 대한 자세한 내용은 FROM 절 섹션을 참조하세요. SUPER 유형에서 피벗을 사용하는 예는 Amazon Redshift에서 반정형 데이터를 사용하는 예 섹션을 참조하세요.

동적 형식 지정

동적 형식 지정에는 점 및 대괄호 경로에서 추출된 데이터의 명시적 캐스팅이 필요하지 않습니다. Amazon Redshift는 동적 형식 지정을 사용하여 쿼리에 사용하기 전에 데이터 형식을 선언할 필요 없이 스키마 없는 SUPER 데이터를 처리합니다. 동적 형식 지정은 Amazon Redshift 형식으로 명시적으로 캐스팅하지 않고도 SUPER 데이터 열로 이동한 결과를 사용합니다. 동적 형식 지정은 조인과 GROUP BY 절에서 가장 유용합니다. 다음 예에서는 일반적인 Amazon Redshift 형식으로 점 및 대괄호 표현식을 명시적으로 캐스팅할 필요가 없는 SELECT 문을 사용합니다. 형식 호환성 및 변환에 대한 자세한 내용은 형식 호환성 및 변환 섹션을 참조하세요.

주문 상태가 shipped인 행을 찾는 다음 예를 검토해보세요.


SELECT c_orders[0].o_orderkey
FROM customer_orders_lineitem
WHERE c_orders[0].o_orderstatus = 'shipped';

이 샘플 쿼리의 등호는 c_orders[0].o_orderstatus의 값이 문자열 'shipped'일 때 true로 평가됩니다. 등식의 인수가 다른 형식인 경우를 포함한 다른 모든 경우에는 등호가 false로 평가됩니다. 예를 들어 주문 상태가 정수인 경우 해당 행은 선택되지 않습니다.

동적 및 정적 형식 지정

동적 형식 지정을 사용하지 않으면 c_orders[0].o_orderstatus가 문자열, 정수 또는 구조인지 확인할 수 없습니다. c_orders[0].o_orderstatus가 Amazon Redshift 스칼라, 배열 또는 구조가 될 수 있는 SUPER 데이터 형식인지만 결정할 수 있습니다. c_orders[0].o_orderstatus의 정적 형식은 SUPER 데이터 형식입니다. 일반적으로 형식은 SQL에서 암시적으로 정적 형식입니다.

Amazon Redshift는 스키마 없는 데이터 처리에 동적 형식 지정을 사용합니다. 쿼리가 데이터를 평가할 때 c_orders[0].o_orderstatus가 특정 형식으로 판명됩니다. 예를 들어 customer_orders_lineitem의 첫 번째 레코드에서 c_orders[0].o_orderstatus를 평가하면 정수가 될 수 있습니다. 두 번째 레코드를 평가하면 문자열이 될 수 있습니다. 다음은 표현식의 동적 형식입니다.

동적 형식이 있는 점 및 대괄호 표현식과 함께 SQL 연산자 또는 함수를 사용하는 경우 Amazon Redshift는 각 정적 형식과 함께 표준 SQL 연산자 또는 함수를 사용하는 것과 유사한 결과를 생성합니다. 이 예에서 경로 표현식의 동적 형식이 문자열인 경우 문자열 'P'와의 비교는 의미가 있습니다. c_orders[0].o_orderstatus의 동적 형식이 문자열이 아닌 다른 데이터 형식일 때마다 등식은 false를 반환합니다. 잘못된 형식의 인수가 사용되면 다른 함수는 null을 반환합니다.

다음 예에서는 정적 형식 지정을 사용하여 이전 쿼리를 작성합니다.


SELECT c_custkey
FROM customer_orders_lineitem
WHERE CASE WHEN JSON_TYPEOF(c_orders[0].o_orderstatus) = 'string'
           THEN c_orders[0].o_orderstatus::VARCHAR = 'P'
           ELSE FALSE END;

동등 술어와 비교 술어의 다음과 같은 차이에 유의합니다. 이전 예에서 동등 술어를 작거나 같음 술어로 바꾸면 의미 체계가 false 대신 null을 생성합니다.


SELECT c_orders[0]. o_orderkey
FROM customer_orders_lineitem
WHERE c_orders[0].o_orderstatus <= 'P';

이 예에서 c_orders[0].o_orderstatus가 문자열이고 알파벳순으로 'P'보다 작거나 같으면 Amazon Redshift는 true를 반환합니다. 알파벳순으로 'P'보다 크면 Amazon Redshift는 false를 반환합니다. 그러나 c_orders[0].o_orderstatus가 문자열이 아닌 경우 Amazon Redshift는 다음 쿼리와 같이 다른 형식의 값을 비교할 수 없으므로 null을 반환합니다.


SELECT c_custkey
FROM customer_orders_lineitem
WHERE CASE WHEN JSON_TYPEOF(c_orders[0].o_orderstatus) = 'string'
           THEN c_orders[0].o_orderstatus::VARCHAR <= 'P'
           ELSE NULL END;

동적 형식 지정은 최소한으로 비교 가능한 형식의 비교에서 제외되지 않습니다. 예를 들어 CHAR 및 VARCHAR Amazon Redshift 스칼라 형식을 모두 SUPER로 변환할 수 있습니다. 이들은 Amazon Redshift CHAR 및 VARCHAR 형식과 유사한 후행 공백 문자를 무시하는 것을 포함하여 문자열과 유사합니다. 마찬가지로 정수, 소수 및 부동 소수점 값은 SUPER 값으로 비교 가능합니다. 특히 소수 열의 경우 값마다 소수 자릿수가 다를 수도 있습니다. Amazon Redshift는 여전히 이들을 동적 형식으로 간주합니다.

Amazon Redshift는 또한 객체 또는 배열을 심층적으로 평가하고 모든 속성을 비교하는 것과 같이 깊은 동등(deep equal)으로 평가되는 객체 및 배열에 대한 동등성을 지원합니다. 깊은 동등(deep equal)은 수행하는 데 시간이 많이 걸릴 수 있으므로 주의해서 사용합니다.

조인에 동적 형식 지정 사용

조인의 경우 동적 형식 지정은 나타날 수 있는 데이터 형식을 찾기 위해 긴 CASE WHEN 분석을 수행하지 않고 값을 다른 동적 형식과 일치시킵니다. 예를 들어 시간이 지남에 따라 조직에서 부분 키에 사용하던 형식을 변경했다고 가정합니다.

발행된 초기 정수 부분 키는 'A55'와 같은 문자열 부분 키로 대체되고 나중에 문자열과 숫자를 결합하는 ['X', 10]과 같은 배열 부분 키로 대체됩니다. Amazon Redshift는 부분 키에 대해 긴 사례 분석을 수행할 필요가 없으며 다음 예와 같이 조인을 사용할 수 있습니다.


SELECT c.c_name
    ,l.l_extendedprice
    ,l.l_discount
FROM customer_orders_lineitem c
    ,c.c_orders o
    ,o.o_lineitems l
    ,supplier_partsupp s
    ,s.s_partsupps ps
WHERE l.l_partkey = ps.ps_partkey
AND c.c_nationkey = s.s_nationkey
ORDER BY c.c_name;

다음 예에서는 동적 형식 지정을 사용하지 않으면 동일한 쿼리가 얼마나 복잡하고 비효율적일 수 있는지 보여줍니다.


SELECT c.c_name
    ,l.l_extendedprice
    ,l.l_discount
FROM customer_orders_lineitem c
    ,c.c_orders o
    ,o.o_lineitems l
    ,supplier_partsupp s
    ,s.s_partsupps ps
WHERE CASE WHEN IS_INTEGER(l.l_partkey) AND IS_INTEGER(ps.ps_partkey)
           THEN l.l_partkey::integer = ps.ps_partkey::integer
           WHEN IS_VARCHAR(l.l_partkey) AND IS_VARCHAR(ps.ps_partkey)
           THEN l.l_partkey::varchar = ps.ps_partkey::varchar
           WHEN IS_ARRAY(l.l_partkey) AND IS_ARRAY(ps.ps_partkey)
                AND IS_VARCHAR(l.l_partkey[0]) AND IS_VARCHAR(ps.ps_partkey[0])
                AND IS_INTEGER(l.l_partkey[1]) AND IS_INTEGER(ps.ps_partkey[1])
           THEN l.l_partkey[0]::varchar = ps.ps_partkey[0]::varchar
                AND l.l_partkey[1]::integer = ps.ps_partkey[1]::integer
           ELSE FALSE END
AND c.c_nationkey = s.s_nationkey
ORDER BY c.c_name;

Lax 의미 체계

기본적으로 SUPER 값에 대한 탐색 작업은 탐색이 유효하지 않을 때 오류를 반환하는 대신 null을 반환합니다. SUPER 값이 객체가 아니거나 SUPER 값이 객체이지만 쿼리에 사용된 속성 이름을 포함하지 않는 경우 객체 탐색이 유효하지 않습니다. 예를 들어 다음 쿼리는 SUPER 데이터 열 cdata에서 잘못된 속성 이름에 액세스합니다.


SELECT c.c_orders.something FROM customer_orders_lineitem c;

SUPER 값이 배열이 아니거나 배열 인덱스가 범위를 벗어난 경우 배열 탐색은 null을 반환합니다. 다음 쿼리는 c_orders[1][1]이 범위를 벗어났기 때문에 null을 반환합니다.


SELECT c.c_orders[1][1] FROM customer_orders_lineitem c;

Lax 의미 체계는 동적 형식 지정을 사용하여 SUPER 값을 캐스팅할 때 특히 유용합니다. SUPER 값을 잘못된 형식으로 캐스팅하면 캐스트가 유효하지 않은 경우 오류 대신 null이 반환됩니다. 예를 들어 다음 쿼리는 객체 속성 o_orderstatus의 문자열 값 'Good'을 INTEGER로 캐스팅할 수 없기 때문에 null을 반환합니다. Amazon Redshift는 VARCHAR에서 INTEGER로 캐스트에 대해서는 오류를 반환하지만 SUPER 캐스트에 대해서는 오류를 반환하지 않습니다.


SELECT c.c_orders.o_orderstatus::integer FROM customer_orders_lineitem c;

순서 기준

Amazon Redshift는 동적 형식이 다른 값 간의 SUPER 비교를 정의하지 않습니다. 문자열인 SUPER 값은 숫자인 SUPER 값보다 작지도 크지도 않습니다. SUPER 열과 함께 ORDER BY 절을 사용하기 위해 Amazon Redshift는 ORDER BY 절을 사용하여 SUPER 값의 순위를 지정할 때 관찰할 다양한 형식 간의 총 순서를 정의합니다. 동적 형식 중 순서는 부울, 숫자, 문자열, 배열, 객체입니다.

SUPER 쿼리에서 GROUP BY 및 ORDER BY를 사용하는 예는 반정형 데이터 필터링 섹션을 참조하세요.

javascript가 브라우저에서 비활성화되거나 사용이 불가합니다.

AWS 설명서를 사용하려면 Javascript가 활성화되어야 합니다. 지침을 보려면 브라우저의 도움말 페이지를 참조하십시오.

문서 규칙

반정형 데이터 언로드

연산자 및 함수