반정형 데이터 쿼리하기¶
이 항목에서는 특수 연산자와 함수를 사용하여 VARIANT에 저장된 복잡한 계층적 데이터를 쿼리하는 방법에 대해 설명합니다.
(ARRAY와 OBJECT에서 값을 추출하는 방법을 보여주는 간단한 예는 인덱스 또는 슬라이스를 기준으로 ARRAY 요소에 액세스하기 및 키를 기준으로 OBJECT의 요소에 액세스하기 를 참조하십시오.)
일반적으로, 계층적 데이터는 지원되는 다음 데이터 형식 중 하나에서 VARIANT로 가져왔습니다.
JSON
Avro
ORC
Parquet
(XML 데이터(예: XML 데이터 형식에서 시작되고 PARSE_XML 을 호출하여 OBJECT로 변환된 데이터) 쿼리에 대한 자세한 내용은 XMLGET 섹션을 참조하십시오.)
팁
검색 최적화 서비스를 사용하여 쿼리 성능을 개선할 수 있습니다. 자세한 내용은 검색 최적화 서비스 섹션을 참조하십시오.
이 항목의 내용:
예에서 사용된 샘플 데이터¶
명시된 경우를 제외하고, 이 항목의 예는 이름이 src 인 단일 VARIANT 열이 포함된 car_sales 테이블을 참조합니다. 이 VARIANT에는 중첩된 ARRAYs 및 OBJECTs 가 포함됩니다.
테이블을 만들어 로딩합니다.
CREATE OR REPLACE TABLE car_sales
(
src variant
)
AS
SELECT PARSE_JSON(column1) AS src
FROM VALUES
('{
"date" : "2017-04-28",
"dealership" : "Valley View Auto Sales",
"salesperson" : {
"id": "55",
"name": "Frank Beasley"
},
"customer" : [
{"name": "Joyce Ridgely", "phone": "16504378889", "address": "San Francisco, CA"}
],
"vehicle" : [
{"make": "Honda", "model": "Civic", "year": "2017", "price": "20275", "extras":["ext warranty", "paint protection"]}
]
}'),
('{
"date" : "2017-04-28",
"dealership" : "Tindel Toyota",
"salesperson" : {
"id": "274",
"name": "Greg Northrup"
},
"customer" : [
{"name": "Bradley Greenbloom", "phone": "12127593751", "address": "New York, NY"}
],
"vehicle" : [
{"make": "Toyota", "model": "Camry", "year": "2017", "price": "23500", "extras":["ext warranty", "rust proofing", "fabric protection"]}
]
}') v;
데이터를 선택합니다.
SELECT * FROM car_sales;
+-------------------------------------------+
| SRC |
|-------------------------------------------|
| { |
| "customer": [ |
| { |
| "address": "San Francisco, CA", |
| "name": "Joyce Ridgely", |
| "phone": "16504378889" |
| } |
| ], |
| "date": "2017-04-28", |
| "dealership": "Valley View Auto Sales", |
| "salesperson": { |
| "id": "55", |
| "name": "Frank Beasley" |
| }, |
| "vehicle": [ |
| { |
| "extras": [ |
| "ext warranty", |
| "paint protection" |
| ], |
| "make": "Honda", |
| "model": "Civic", |
| "price": "20275", |
| "year": "2017" |
| } |
| ] |
| } |
| { |
| "customer": [ |
| { |
| "address": "New York, NY", |
| "name": "Bradley Greenbloom", |
| "phone": "12127593751" |
| } |
| ], |
| "date": "2017-04-28", |
| "dealership": "Tindel Toyota", |
| "salesperson": { |
| "id": "274", |
| "name": "Greg Northrup" |
| }, |
| "vehicle": [ |
| { |
| "extras": [ |
| "ext warranty", |
| "rust proofing", |
| "fabric protection" |
| ], |
| "make": "Toyota", |
| "model": "Camry", |
| "price": "23500", |
| "year": "2017" |
| } |
| ] |
| } |
+-------------------------------------------+
반정형 데이터 탐색하기¶
VARIANT 열 이름과 첫 번째 레벨 요소인 <열>:<레벨1_요소> 사이에 콜론 : 을 삽입합니다.
참고
다음 예에서는 쿼리의 출력이 VARCHAR가 아닌 VARIANT이므로 쿼리의 출력을 큰따옴표로 묶습니다. (VARIANT 값은 문자열이 아니며, VARIANT 값에 문자열이 포함됩니다.) : 연산자 및 후행 . 및 [] 는 항상 문자열이 포함된 VARIANT 값을 반환합니다.
예를 들어, 모든 대리점 이름의 목록을 가져옵니다.
SELECT src:dealership
FROM car_sales
ORDER BY 1;
+--------------------------+
| SRC:DEALERSHIP |
|--------------------------|
| "Tindel Toyota" |
| "Valley View Auto Sales" |
+--------------------------+
JSON 오브젝트의 요소에 액세스하는 방법은 다음의 2가지입니다.
중요
사용할 표기법에 관계없이, 열 이름에서는 대/소문자를 구분하지 않지만 요소 이름에서는 대/소문자를 구분합니다. 예를 들어, 다음 목록에서 첫 번째 두 경로는 동일하지만 세 번째 경로는 그렇지 않습니다.
src:salesperson.name
SRC:salesperson.name
SRC:Salesperson.Name
점 표기법¶
점 표기법은 JSON 오브젝트에서 <열>:<레벨1_요소>.<레벨2_요소>.<레벨3_요소> 경로를 탐색합니다. 선택 사항으로 요소의 이름 <열>:"<레벨1_요소>"."<레벨2_요소>"."<레벨3_요소>" 을 큰따옴표로 묶습니다.
참고
JSON 키(요소 이름)에 대한 규칙은 Snowflake SQL 식별자에 대한 규칙과 다릅니다.
Snowflake SQL 식별자 규칙에 대한 자세한 내용은 식별자 요구 사항 을 참조하십시오.
JSON 키에 대한 자세한 내용은 http://json.org에서 특히 “문자열” 설명을 참조하십시오.
요소 이름이 Snowflake SQL 식별자 규칙을 따르지 않는 경우(예: 공백이 포함된 경우) 반드시 이름을 큰따옴표로 묶어야 합니다. 큰따옴표로 묶는 경우를 제외하고 유효한 Snowflake 식별자 이름이 아닌 유효한 JSON 요소 이름의 몇 가지 예는 아래와 같습니다(위의 car_sales 예에 해당하지 않은 항목도 있음).
-- This contains a blank.
SELECT src:"company name" FROM partners;
-- This does not start with a letter or underscore.
SELECT zipcode_info:"94987" FROM addresses;
-- This contains characters that are not letters, digits, or underscores, and
-- it does not start with a letter or underscore.
SELECT measurements:"#sPerSquareInch" FROM english_metrics;
자동차를 판매하는 모든 영업 인력의 이름을 가져옵니다.
SELECT src:salesperson.name
FROM car_sales
ORDER BY 1;
+----------------------+
| SRC:SALESPERSON.NAME |
|----------------------|
| "Frank Beasley" |
| "Greg Northrup" |
+----------------------+
대괄호 표기법¶
아니면, 대괄호 표기법을 사용하여 오브젝트에서 <열>['<레벨1_요소>']['<레벨2_요소>'] 경로를 탐색합니다. 요소 이름을 작은따옴표로 묶습니다. 값은 문자열로 검색됩니다.
자동차를 판매하는 모든 영업 인력의 이름을 가져옵니다.
SELECT src['salesperson']['name']
FROM car_sales
ORDER BY 1;
+----------------------------+
| SRC['SALESPERSON']['NAME'] |
|----------------------------|
| "Frank Beasley" |
| "Greg Northrup" |
+----------------------------+
반복 요소의 단일 인스턴스 검색하기¶
번호가 지정된 조건자(0부터 시작)를 배열 참조에 추가하여 반복 배열에서 하위 요소의 특정 번호 인스턴스를 검색합니다.
반복 배열에서 하위 요소의 모든 인스턴스를 검색하려면 배열을 평면화합니다. 이 항목의 FLATTEN 함수를 사용하여 배열 구문 분석하기 에서 제공되는 예를 참조하십시오.
각 판매에 대한 차량 세부 정보를 가져옵니다.
SELECT src:customer[0].name, src:vehicle[0]
FROM car_sales
ORDER BY 1;
+----------------------+-------------------------+
| SRC:CUSTOMER[0].NAME | SRC:VEHICLE[0] |
|----------------------+-------------------------|
| "Bradley Greenbloom" | { |
| | "extras": [ |
| | "ext warranty", |
| | "rust proofing", |
| | "fabric protection" |
| | ], |
| | "make": "Toyota", |
| | "model": "Camry", |
| | "price": "23500", |
| | "year": "2017" |
| | } |
| "Joyce Ridgely" | { |
| | "extras": [ |
| | "ext warranty", |
| | "paint protection" |
| | ], |
| | "make": "Honda", |
| | "model": "Civic", |
| | "price": "20275", |
| | "year": "2017" |
| | } |
+----------------------+-------------------------+
판매된 각 차량의 가격을 가져옵니다.
SELECT src:customer[0].name, src:vehicle[0].price
FROM car_sales
ORDER BY 1;
+----------------------+----------------------+
| SRC:CUSTOMER[0].NAME | SRC:VEHICLE[0].PRICE |
|----------------------+----------------------|
| "Bradley Greenbloom" | "23500" |
| "Joyce Ridgely" | "20275" |
+----------------------+----------------------+
명시적 값 캐스팅¶
VARIANT에서 값을 추출할 때 값을 원하는 데이터 타입으로 명시적으로 캐스팅할 수 있습니다. 예를 들어, 가격을 숫자 값으로 추출하고 이들 값에 대한 계산을 수행할 수 있습니다.
SELECT src:vehicle[0].price::NUMBER * 0.10 AS tax
FROM car_sales
ORDER BY tax;
+--------+
| TAX |
|--------|
| 2027.5 |
| 2350.0 |
+--------+
기본적으로 VARCHAR, DATE, TIME, TIMESTAMP가 VARIANT 열에서 검색될 때 값은 큰따옴표로 묶입니다. 값을 명시적으로 캐스팅하여 큰따옴표를 제거할 수 있습니다. 예:
SELECT src:dealership, src:dealership::VARCHAR
FROM car_sales
ORDER BY 2;
+--------------------------+-------------------------+
| SRC:DEALERSHIP | SRC:DEALERSHIP::VARCHAR |
|--------------------------+-------------------------|
| "Tindel Toyota" | Tindel Toyota |
| "Valley View Auto Sales" | Valley View Auto Sales |
+--------------------------+-------------------------+
VARIANT 값의 캐스팅에 대한 자세한 내용은 VARIANT에서 변수 사용하기 섹션을 참조하십시오.
일반적으로 캐스팅에 대한 자세한 내용은 데이터 타입 변환 섹션을 참조하십시오.
Using FLATTEN to Filter the Results in a WHERE Clause¶
The FLATTEN function explodes nested values into separate columns. You can use the function to filter query results in a WHERE clause.
The following example returns key-value pairs that match a WHERE clause and displays them in separate columns:
CREATE TABLE pets (v variant);
INSERT INTO pets SELECT PARSE_JSON ('{"species":"dog", "name":"Fido", "is_dog":"true"} ');
INSERT INTO pets SELECT PARSE_JSON ('{"species":"cat", "name":"Bubby", "is_dog":"false"}');
INSERT INTO pets SELECT PARSE_JSON ('{"species":"cat", "name":"dog terror", "is_dog":"false"}');
SELECT a.v, b.key, b.value FROM pets a,LATERAL FLATTEN(input => a.v) b
WHERE b.value LIKE '%dog%';
+-------------------------+---------+--------------+
| V | KEY | VALUE |
|-------------------------+---------+--------------|
| { | species | "dog" |
| "is_dog": "true", | | |
| "name": "Fido", | | |
| "species": "dog" | | |
| } | | |
| { | name | "dog terror" |
| "is_dog": "false", | | |
| "name": "dog terror", | | |
| "species": "cat" | | |
| } | | |
+-------------------------+---------+--------------+
Using FLATTEN to List Distinct Key Names¶
When working with unfamiliar semi-structured data, you might not know the key names in an OBJECT. You can use the FLATTEN function with the RECURSIVE argument to return the list of distinct key names in all nested elements in an OBJECT:
SELECT REGEXP_REPLACE(f.path, '\\[[0-9]+\\]', '[]') AS "Path",
TYPEOF(f.value) AS "Type",
COUNT(*) AS "Count"
FROM <table>,
LATERAL FLATTEN(<variant_column>, RECURSIVE=>true) f
GROUP BY 1, 2 ORDER BY 1, 2;
The REGEXP_REPLACE function removes the array index values (e.g. [0]) and replaces them with brackets ([]) to group array elements.
For example:
{"a": 1, "b": 2, "special" : "data"} <--- row 1 of VARIANT column
{"c": 3, "d": 4, "normal" : "data"} <----row 2 of VARIANT column
Output from query:
+---------+---------+-------+
| Path | Type | Count |
|---------+---------+-------|
| a | INTEGER | 1 |
| b | INTEGER | 1 |
| c | INTEGER | 1 |
| d | INTEGER | 1 |
| normal | VARCHAR | 1 |
| special | VARCHAR | 1 |
+---------+---------+-------+
Using FLATTEN to List Paths in an OBJECT¶
Related to Using FLATTEN to List Distinct Key Names, you can use the FLATTEN function with the RECURSIVE argument to retrieve all keys and paths in an OBJECT.
The following query returns keys, paths, and values (including VARIANT “null” values) for all data types stored in a VARIANT column. The code assumes that the VARIANT column contains an OBJECT in each row.
SELECT
t.<variant_column>,
f.seq,
f.key,
f.path,
REGEXP_COUNT(f.path,'\\.|\\[') +1 AS Level,
TYPEOF(f.value) AS "Type",
f.index,
f.value AS "Current Level Value",
f.this AS "Above Level Value"
FROM <table> t,
LATERAL FLATTEN(t.<variant_column>, recursive=>true) f;
The following query is similar to the first query, but excludes nested OBJECTs and ARRAYs:
SELECT
t.<variant_column>,
f.seq,
f.key,
f.path,
REGEXP_COUNT(f.path,'\\.|\\[') +1 AS Level,
TYPEOF(f.value) AS "Type",
f.value AS "Current Level Value",
f.this AS "Above Level Value"
FROM <table> t,
LATERAL FLATTEN(t.<variant_column>, recursive=>true) f
WHERE "Type" NOT IN ('OBJECT','ARRAY');
The queries return the following values:
- <variant_column>
OBJECT stored as a row in the VARIANT column.
- Seq
Unique sequence number associated with the data in the row.
- Key
String associated with a value in the data structure.
- Path
Path to the element within the data structure.
- Level
Level of the key-value pair within the data structure.
- Type
Data type for the value.
- Index
Index of the element in the data structure. Applies to ARRAY values only; otherwise NULL.
- Current Level Value
Value at the current level in the data structure.
- Above Level Value
Value one level higher in the data structure.
FLATTEN 함수를 사용하여 배열 구문 분석하기¶
FLATTEN 함수를 사용하여 배열의 구문을 분석합니다. FLATTEN 은 VARIANT, OBJECT 또는 ARRAY 열의 래터럴 뷰를 생성하는 테이블 함수입니다. 이 함수는 각 오브젝트에 대한 행을 반환하고 LATERAL 수정자는 데이터를 오브젝트 외부의 모든 정보와 결합합니다.
모든 고객의 이름 및 주소를 가져옵니다. VARIANT 출력을 문자열 값으로 캐스팅합니다.
SELECT
value:name::string as "Customer Name",
value:address::string as "Address"
FROM
car_sales
, LATERAL FLATTEN(INPUT => SRC:customer);
+--------------------+-------------------+
| Customer Name | Address |
|--------------------+-------------------|
| Joyce Ridgely | San Francisco, CA |
| Bradley Greenbloom | New York, NY |
+--------------------+-------------------+
FLATTEN 함수를 사용하여 중첩 배열의 구문 분석하기¶
샘플 데이터에서 extras 배열은 vehicle 배열에 중첩되어 있습니다.
"vehicle" : [
{"make": "Honda", "model": "Civic", "year": "2017", "price": "20275", "extras":["ext warranty", "paint protection"]}
]
두 번째 FLATTEN 절을 평면화된 vehicle 배열 내의 extras 배열을 평면화하고 판매된 각 차량에서 구입된 “추가 부품”을 검색합니다.
SELECT
vm.value:make::string as make,
vm.value:model::string as model,
ve.value::string as "Extras Purchased"
FROM
car_sales
, LATERAL FLATTEN(INPUT => SRC:vehicle) vm
, LATERAL FLATTEN(INPUT => vm.value:extras) ve
ORDER BY make, model, "Extras Purchased";
+--------+-------+-------------------+
| MAKE | MODEL | Extras Purchased |
|--------+-------+-------------------|
| Honda | Civic | ext warranty |
| Honda | Civic | paint protection |
| Toyota | Camry | ext warranty |
| Toyota | Camry | fabric protection |
| Toyota | Camry | rust proofing |
+--------+-------+-------------------+
PARSE_JSON 함수를 사용하여 텍스트를 VARIANT 변수로 구문 분석하기¶
PARSE_JSON 함수를 사용하여 텍스트를 JSON 문서로 구문 분석합니다.
입력이 NULL이면 출력도 NULL입니다. 그러나 입력 문자열이 null 이면 VARIANT null 값으로 해석되며, 즉, 결과는 SQL NULL이 아닌 반정형 형식으로 null 값을 표현하기 위해 사용되는 실제 값입니다.
예를 들어, 이 항목의 예에서 사용된 샘플 데이터 를 참조하십시오.
GET 함수를 사용하여 값 추출하기¶
GET 에서는 VARIANT, OBJECT 또는 ARRAY 값을 첫 번째 인자로 허용하며 두 번째 인자로 제공되는 경로 요소의 VARIANT 값을 추출합니다.
GET 및 ARRAY_SIZE 함수를 사용하여 VARIANT 열에 있는 각 배열의 마지막 요소를 계산한 후 추출합니다. ARRAY_SIZE는 입력 배열의 크기를 반환합니다.
참고
이 예는 이 항목의 다른 위치에서 사용된 car_sales 테이블과 다릅니다.
CREATE OR replace TABLE colors (v variant);
INSERT INTO
colors
SELECT
parse_json(column1) AS v
FROM
VALUES
('[{r:255,g:12,b:0},{r:0,g:255,b:0},{r:0,g:0,b:255}]'),
('[{c:0,m:1,y:1,k:0},{c:1,m:0,y:1,k:0},{c:1,m:1,y:0,k:0}]')
v;
SELECT *, GET(v, ARRAY_SIZE(v)-1) FROM colors;
+---------------+-------------------------+
| V | GET(V, ARRAY_SIZE(V)-1) |
|---------------+-------------------------|
| [ | { |
| { | "b": 255, |
| "b": 0, | "g": 0, |
| "g": 12, | "r": 0 |
| "r": 255 | } |
| }, | |
| { | |
| "b": 0, | |
| "g": 255, | |
| "r": 0 | |
| }, | |
| { | |
| "b": 255, | |
| "g": 0, | |
| "r": 0 | |
| } | |
| ] | |
| [ | { |
| { | "c": 1, |
| "c": 0, | "k": 0, |
| "k": 0, | "m": 1, |
| "m": 1, | "y": 0 |
| "y": 1 | } |
| }, | |
| { | |
| "c": 1, | |
| "k": 0, | |
| "m": 0, | |
| "y": 1 | |
| }, | |
| { | |
| "c": 1, | |
| "k": 0, | |
| "m": 1, | |
| "y": 0 | |
| } | |
| ] | |
+---------------+-------------------------+
GET_PATH를 사용하여 경로를 기준으로 값 추출하기¶
GET_PATH , : 함수를 사용하여 VARIANT 열에서 값을 추출합니다. 이 함수는 경로 이름을 사용하여 값을 추출하기 위해 사용되는 GET 의 변형입니다. GET_PATH는 GET 함수 체인과 동등합니다.
각 고객이 구매한 자동차의 차량 제조사를 가져옵니다.
SELECT GET_PATH(src, 'vehicle[0]:make') FROM car_sales;
+----------------------------------+
| GET_PATH(SRC, 'VEHICLE[0]:MAKE') |
|----------------------------------|
| "Honda" |
| "Toyota" |
+----------------------------------+
반정형 데이터 탐색하기 에서는 VARIANT 열에서 요소를 검색하기 위해 사용되는 경로 구문을 설명합니다. 구문은 GET 또는 GET_PATH , : 함수의 줄임 속성입니다. 경로 구문과 다르게, 이러한 함수는 불규칙한 경로 또는 경로 요소를 처리할 수 있습니다.
다음 쿼리에서는 동일한 결과가 제공됩니다.
SELECT GET_PATH(src, 'vehicle[0].make') FROM car_sales;
SELECT src:vehicle[0].make FROM car_sales;
스테이징된 데이터 파일에서 직접 배열 구문 분석하기¶
이름이 contacts.json.gz 인 스테이징 파일에 다음 데이터가 포함되어 있다고 가정해 보겠습니다.
{
"root": [
{
"employees": [
{
"firstName": "Anna",
"lastName": "Smith"
},
{
"firstName": "Peter",
"lastName": "Jones"
}
]
}
]
}
또한, my_json_format 파일 형식의 정의에 TYPE=JSON 이 포함되어 있다고 가정해 보겠습니다.
스테이징된 파일에서 첫 번째 직원의 이름을 쿼리합니다. 이 예에서 파일은 customers 테이블 스테이지에 위치하지만, 내부(즉, Snowflake) 또는 외부 스테이지에도 위치할 수 있습니다.
SELECT 'The First Employee Record is '||
S.$1:root[0].employees[0].firstName||
' '||S.$1:root[0].employees[0].lastName
FROM @%customers/contacts.json.gz (file_format => 'my_json_format') as S;
+----------------------------------------------+
| 'THE FIRST EMPLOYEE RECORD IS '|| |
| S.$1:ROOT[0].EMPLOYEES[0].FIRSTNAME|| |
| ' '||S.$1:ROOT[0].EMPLOYEES[0].LASTNAME |
|----------------------------------------------|
| The First Employee Record is Anna Smith |
+----------------------------------------------+