비정형 데이터 쿼리
Amazon Redshift는 PartiQL 언어를 사용하여 관계형, 비정형 및 중첩 데이터에 대한 SQL 호환 액세스를 제공합니다.
PartiQL은 동적 형식으로 작업합니다. 이를 통해 정형, 반정형 및 중첩 데이터 집합의 조합에 대한 직관적 필터링, 조인 및 집계가 가능합니다. PartiQL 구문은 중첩 데이터에 액세스할 때 경로 탐색을 위해 점 표기법과 배열 첨자를 사용합니다. 또한 FROM 절 항목이 배열을 반복하고 중첩 해제 작업에 사용할 수 있습니다. 다음에서 SUPER 데이터 유형 사용을 경로 및 배열 탐색, 중첩 해제, 피벗 해제 및 조인과 결합하는 다양한 쿼리 패턴에 대한 설명을 찾을 수 있습니다.
다음 예에 사용된 테이블에 대한 자세한 내용은 SUPER 샘플 데이터 집합 섹션을 참조하세요.
탐색
Amazon Redshift는 PartiQL을 사용하여 각각 [...] 대괄호와 점 표기법을 사용하여 배열과 구조를 탐색할 수 있도록 합니다. 또한 점 표기법을 사용하는 구조와 대괄호 표기법을 사용하는 배열을 혼합하여 탐색할 수 있습니다. 예를 들어 다음 예에서는 c_orders SUPER 데이터 열이 구조가 있는 배열이고 속성 이름이 o_orderkey라고 가정합니다.
customer_orders_lineitem 테이블의 데이터를 수집하려면 다음 명령을 실행합니다. IAM 역할을 사용자의 자격 증명으로 바꿉니다.
COPY customer_orders_lineitem FROM 's3://redshift-downloads/semistructured/tpch-nested/data/json/customer_orders_lineitem' REGION 'us-east-1' IAM_ROLE 'arn:aws:iam::xxxxxxxxxxxx:role/Redshift-S3' FORMAT JSON 'auto'; SELECT c_orders[0].o_orderkey FROM customer_orders_lineitem;
Amazon Redshift는 또한 테이블 별칭을 표기법에 대한 접두사로 사용합니다. 다음 예는 이전 예와 동일한 쿼리입니다.
SELECT cust.c_orders[0].o_orderkey FROM customer_orders_lineitem AS cust;
필터링, 조인 및 집계와 같은 모든 형식의 쿼리에 점 및 대괄호 표기법을 사용할 수 있습니다. 일반적으로 열 참조가 있는 쿼리에서 이러한 표기법을 사용할 수 있습니다. 다음 예에서는 결과를 필터링하는 SELECT 문을 사용합니다.
SELECT count(*) FROM customer_orders_lineitem WHERE c_orders[0]. o_orderkey IS NOT NULL;
다음 예에서는 GROUP BY 및 ORDER BY 절에 괄호와 점 탐색을 사용합니다.
SELECT c_orders[0].o_orderdate, c_orders[0].o_orderstatus, count(*) FROM customer_orders_lineitem WHERE c_orders[0].o_orderkey IS NOT NULL GROUP BY c_orders[0].o_orderstatus, c_orders[0].o_orderdate ORDER BY c_orders[0].o_orderdate;
쿼리 중첩 해제
쿼리 중첩을 해제하기 위해 Amazon Redshift는 PartiQL 구문을 사용하여 SUPER 배열을 반복합니다. 쿼리의 FROM 절로 배열을 탐색하여 이를 수행합니다. 다음 예에서는 이전 예를 사용하여 c_orders에 대한 속성 값을 반복합니다.
SELECT c.*, o FROM customer_orders_lineitem c, c.c_orders o;
중첩 해제 구문은 FROM 절의 확장입니다. 표준 SQL에서 FROM 절 x (AS) y는 y가 x 관계에 있는 각 튜플을 반복함을 의미합니다. 이 경우 x는 관계를 나타내고, y는 관계 x에 대한 별칭을 나타냅니다. 마찬가지로, 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 index, element 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; array | element -----------+--------- [1.1,1.2] | 1.1 [1.1,1.2] | 1.2 [2.1,2.2] | 2.1 [2.1,2.2] | 2.2 [3.1,3.2] | 3.1 [3.1,3.2] | 3.2 (6 rows)
FROM 절에 대한 자세한 내용은 FROM 절 섹션을 참조하세요.
객체 피벗 해제
객체 피벗 해제를 수행하기 위해 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 절 섹션을 참조하세요. PIVOT 및 UNPIVOT을 사용하여 구조화된 데이터를 쿼리하는 방법을 보여주는 예는 PIVOT 및 UNPIVOT 예 섹션을 참조하세요.
동적 형식 지정
동적 형식 지정에는 점 및 대괄호 경로에서 추출된 데이터의 명시적 캐스팅이 필요하지 않습니다. Amazon Redshift는 동적 형식 지정을 사용하여 쿼리에 사용하기 전에 데이터 형식을 선언할 필요 없이 스키마 없는 SUPER 데이터를 처리합니다. 동적 형식 지정은 Amazon Redshift 형식으로 명시적으로 캐스팅하지 않고도 SUPER 데이터 열로 이동한 결과를 사용합니다. 동적 형식 지정은 조인과 GROUP BY 절에서 가장 유용합니다. 다음 예에서는 일반적인 Amazon Redshift 형식으로 점 및 대괄호 표현식을 명시적으로 캐스팅할 필요가 없는 SELECT 문을 사용합니다. 형식 호환성 및 변환에 대한 자세한 내용은 형식 호환성 및 변환 섹션을 참조하세요.
SELECT c_orders[0].o_orderkey FROM customer_orders_lineitem WHERE c_orders[0].o_orderstatus = 'P';
이 쿼리의 등호는 c_orders[0].o_orderstatus가 문자열 'P'일 때 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;
내부 검사 유형
SUPER 데이터 열은 SUPER 값에 대한 동적 형식과 기타 형식 정보를 반환하는 내부 검사 함수를 지원합니다. 가장 일반적인 예는 SUPER 값의 동적 형식에 따라 부울, 숫자, 문자열, 객체, 배열 또는 null 값이 있는 VARCHAR를 반환하는 JSON_TYPEOF 스칼라 함수입니다. Amazon Redshift는 SUPER 데이터 열에 대해 다음과 같은 부울 함수를 지원합니다.
DECIMAL_PRECISION
DECIMAL_SCALE
IS_ARRAY
IS_BIGINT
IS_CHAR
IS_DECIMAL
IS_FLOAT
IS_INTEGER
IS_OBJECT
IS_SCALAR
IS_SMALLINT
IS_VARCHAR
JSON_TYPEOF
입력 값이 null인 경우 이러한 함수는 모두 false를 반환합니다. IS_SCALAR, IS_OBJECT 및 IS_ARRAY는 상호 배타적이며 null을 제외한 모든 가능한 값을 포함합니다.
데이터에 해당하는 형식을 추론하기 위해 Amazon Redshift는 다음 예와 같이 SUPER 값의 형식(최상위 수준)을 반환하는 JSON_TYPEOF 함수를 사용합니다.
SELECT JSON_TYPEOF(r_nations) FROM region_nations; json_typeof ------------- array (1 row)
SELECT JSON_TYPEOF(r_nations[0].n_nationkey) FROM region_nations; json_typeof ------------- number
Amazon Redshift는 이를 SUPER 대신 VARCHAR 열에 삽입하는 것과 유사한 단일 긴 문자열로 간주합니다. 열이 SUPER이므로 단일 문자열은 여전히 유효한 SUPER 값이고 차이점은 JSON_TYPEOF에 표시됩니다.
SELECT IS_VARCHAR(r_nations[0].n_name) FROM region_nations; is_varchar ------------- true (1 row)
SELECT r_nations[4].n_name FROM region_nations WHERE CASE WHEN IS_INTEGER(r_nations[4].n_nationkey) THEN r_nations[4].n_nationkey::INTEGER = 15 ELSE false END;
순서 기준
Amazon Redshift는 동적 형식이 다른 값 간의 SUPER 비교를 정의하지 않습니다. 문자열인 SUPER 값은 숫자인 SUPER 값보다 작지도 크지도 않습니다. SUPER 열과 함께 ORDER BY 절을 사용하기 위해 Amazon Redshift는 ORDER BY 절을 사용하여 SUPER 값의 순위를 지정할 때 관찰할 다양한 형식 간의 총 순서를 정의합니다. 동적 형식 중 순서는 부울, 숫자, 문자열, 배열, 객체입니다. 다음 예에서는 서로 다른 형식의 순서를 보여줍니다.
INSERT INTO region_nations VALUES (100,'name1','comment1','AWS'), (200,'name2','comment2',1), (300,'name3','comment3',ARRAY(1, 'abc', null)), (400,'name4','comment4',-2.5), (500,'name5','comment5','Amazon'); SELECT r_nations FROM region_nations order by r_nations; r_nations ---------------- -2.5 1 "Amazon" "AWS" [1,"abc",null] (5 rows)
ORDER BY 절에 대한 자세한 내용은 ORDER BY 절 섹션을 참조하세요.
