쿼리 변형 데이터

이 문서에서는 반구조화된 데이터를 VARIANT쿼리하고 변환하는 데 사용할 수 있는 Databricks SQL 연산자를 설명합니다. 데이터 형식은 VARIANT Databricks Runtime 15.3 이상에서 사용할 수 있습니다.

Databricks는 JSON 문자열을 VARIANT 사용하는 것이 좋습니다. 현재 마이그레이션하려는 JSON 문자열을 사용하는 사용자의 경우 변형이 JSON 문자열과 어떻게 다른가요?를 참조하세요.

JSON 문자열로 저장된 반구조화된 데이터를 쿼리하는 예제를 보려면 JSON 문자열 쿼리를 참조 하세요.

variant 열이 있는 테이블 만들기

다음 쿼리를 실행하여 중첩된 데이터가 다음과 같이 VARIANT저장된 테이블을 만듭니다. 이 아티클의 예제는 모두 이 테이블을 참조합니다.

CREATE TABLE store_data AS
SELECT parse_json(
  '{
    "store":{
        "fruit": [
          {"weight":8,"type":"apple"},
          {"weight":9,"type":"pear"}
        ],
        "basket":[
          [1,2,{"b":"y","a":"x"}],
          [3,4],
          [5,6]
        ],
        "book":[
          {
            "author":"Nigel Rees",
            "title":"Sayings of the Century",
            "category":"reference",
            "price":8.95
          },
          {
            "author":"Herman Melville",
            "title":"Moby Dick",
            "category":"fiction",
            "price":8.99,
            "isbn":"0-553-21311-3"
          },
          {
            "author":"J. R. R. Tolkien",
            "title":"The Lord of the Rings",
            "category":"fiction",
            "reader":[
              {"age":25,"name":"bob"},
              {"age":26,"name":"jack"}
            ],
            "price":22.99,
            "isbn":"0-395-19395-8"
          }
        ],
        "bicycle":{
          "price":19.95,
          "color":"red"
        }
      },
      "owner":"amy",
      "zip code":"94025",
      "fb:testid":"1234"
  }'
) as raw

변형 열의 쿼리 필드

Azure Databricks에서 JSON 문자열 및 기타 복잡한 데이터 형식을 쿼리하는 구문은 다음을 VARIANT 포함하여 데이터에 적용됩니다.

• 최상위 필드를 선택하는 데 사용합니다 : .
• 명명된 키가 있는 중첩 필드를 사용 . 하거나 [<key>] 선택합니다.
• 배열에서 값을 선택하는 데 사용합니다 [<index>] .

SELECT raw:['zip.code'] FROM store_data

최상위 변형 필드 추출

필드를 추출하려면 추출 경로에서 JSON 필드의 이름을 지정합니다. 필드 이름은 항상 대/소문자를 구분합니다.

SELECT raw:owner FROM store_data

+-------+
| owner |
+-------+
| "amy" |
+-------+

-- Use backticks to escape special characters.
SELECT raw:`zip code`, raw:`fb:testid` FROM store_data

+----------+-----------+
| zip code | fb:testid |
+----------+-----------+
| "94025"  | "1234"    |
+----------+-----------+

경로를 찾을 수 없는 경우 결과는 형식NULL입니다VARIANT.

변형 중첩 필드 추출

점 표기법이나 대괄호를 사용하여 중첩 필드를 지정합니다. 필드 이름은 항상 대/소문자를 구분합니다.

-- Use dot notation
SELECT raw:store.bicycle FROM store_data

+------------------+
| bicycle          |
+------------------+
| {                |
|   "color":"red", |
|   "price":19.95  |
| }                |
+------------------+

-- Use brackets
SELECT raw:store['bicycle'] FROM store_data

+------------------+
| bicycle          |
+------------------+
| {                |
|   "color":"red", |
|   "price":19.95  |
| }                |
+------------------+

경로를 찾을 수 없는 경우 결과는 형식NULL입니다VARIANT.

변형 배열에서 값 추출

대괄호를 사용하여 배열의 요소를 인덱싱합니다. 인덱스는 0부터 시작합니다.

-- Index elements
SELECT raw:store.fruit[0], raw:store.fruit[1] FROM store_data

+-------------------+------------------+
| fruit             | fruit            |
+-------------------+------------------+
| {                 | {                |
|   "type":"apple", |   "type":"pear", |
|   "weight":8      |   "weight":9     |
| }                 | }                |
+-------------------+------------------+

경로를 찾을 수 없거나 배열 인덱스가 범위를 벗어난 경우 결과는 다음과 같습니다 NULL.

변형 개체 및 배열 평면화

variant_explode 테이블 반환 생성기 함수를 사용하여 배열과 개체를 평면화 VARIANT 할 수 있습니다.

variant_explode 생성기 함수이므로 다음 예제와 같이 목록이 아닌 FROM 절의 SELECT 일부로 사용합니다.

SELECT key, value
  FROM store_data,
  LATERAL variant_explode(store_data.raw:store);

+-------+--------------------+
|    key|               value|
+-------+--------------------+
| basket|[[1,2,{"a":"x","b...|
|bicycle|{"color":"red","p...|
|   book|[{"author":"Nigel...|
|  fruit|[{"type":"apple",...|
+-------+--------------------+

SELECT pos, value
  FROM store_data,
  LATERAL variant_explode(store_data.raw:store.basket[0]);

+---+-----------------+
|pos|            value|
+---+-----------------+
|  0|                1|
|  1|                2|
|  2|{"a":"x","b":"y"}|
+---+-----------------+

변형 형식 캐스팅 규칙

형식을 사용하여 VARIANT 배열 및 스칼라를 저장할 수 있습니다. 변형 형식을 다른 형식으로 캐스팅하려고 하면 다음과 같은 추가 규칙과 함께 개별 값 및 필드에 일반 캐스팅 규칙이 적용됩니다.

형식 schema_of_variant 을 유추할 schema_of_variant_agg때 함수는 해결할 수 없는 충돌하는 형식이 있을 때 형식이 아닌 VARIANT 형식으로 대체 STRING 됩니다.

값을 사용 :: 하거나 cast 지원되는 데이터 형식으로 캐스팅할 수 있습니다.

-- price is returned as a double, not a string
SELECT raw:store.bicycle.price::double FROM store_data

+------------------+
| price            |
+------------------+
| 19.95            |
+------------------+

-- cast into more complex types
SELECT cast(raw:store.bicycle AS STRUCT<price DOUBLE, color STRING>) bicycle FROM store_data;
-- `::` also supported
SELECT raw:store.bicycle::STRUCT<price DOUBLE, color STRING> bicycle FROM store_data;

+------------------+
| bicycle          |
+------------------+
| {                |
|   "price":19.95, |
|   "color":"red"  |
| }                |
+------------------+

Variant null 규칙

변형에는 다음 두 종류의 null이 포함될 수 있습니다.

• SQL NULL: SQL NULL은 값이 누락되었음을 나타냅니다. 이는 구조화된 데이터를 처리할 때와 동일합니다 NULL.
• Variant NULL: Variant s는 변형 NULL에 명시적으로 값이 NULL 포함되어 있음을 나타냅니다. 값이 데이터에 저장되기 때문에 NULL 이러한 값은 SQL NULLs와 동일하지 않습니다.

함수를 is_variant_null 사용하여 변형 값이 variant NULL인지 확인합니다.

SELECT
  is_variant_null(parse_json(NULL)) AS sql_null,
  is_variant_null(parse_json('null')) AS variant_null,
  is_variant_null(parse_json('{ "field_a": null }'):field_a) AS variant_null_value,
  is_variant_null(parse_json('{ "field_a": null }'):missing) AS missing_sql_value_null

+--------+------------+------------------+----------------------+
|sql_null|variant_null|variant_null_value|missing_sql_value_null|
+--------+------------+------------------+----------------------+
|   false|        true|              true|                 false|
+--------+------------+------------------+----------------------+