이 문서에서는 JSON 스트링으로 저장된 반구조적 데이터를 쿼리하고 변환하는 데 사용할 수 있는 Databricks SQL 연산자에 대해 설명합니다.
참고
이 기능을 사용하면 파일을 병합하지 않고 반구조화된 데이터를 읽을 수 있습니다. 그러나 최적의 읽기 쿼리 성능을 위해 Databricks는 올바른 데이터 형식으로 중첩된 열을 추출하는 것이 좋습니다.
구문 <column-name>:<extraction-path>사용하여 JSON 문자열이 포함된 필드에서 열을 추출합니다. 여기서 <column-name> 문자열 열 이름이고 <extraction-path> 추출할 필드의 경로입니다. 반환된 결과는 문자열입니다.
고도로 중첩된 데이터를 사용하여 테이블 만들기
다음 쿼리를 실행하여 중첩된 데이터가 있는 테이블을 만듭니다. 이 문서의 예제는 모두 이 테이블을 참조합니다.
CREATE TABLE store_data AS SELECT
'{
"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
최상위 열을 추출하세요
열을 추출하려면 추출 경로에서 JSON 필드의 이름을 지정합니다.
대괄호 안에 열 이름을 제공할 수 있습니다. 대괄호 내에서 참조된 열은 대소문자를 구분하여 로 매칭됩니다. 열 이름은 참조 시 대/소문자를 구분하지 않습니다.
SELECT raw:owner, RAW:owner FROM store_data
+-------+-------+
| owner | owner |
+-------+-------+
| amy | amy |
+-------+-------+
-- References are case sensitive when you use brackets
SELECT raw:OWNER case_insensitive, raw:['OWNER'] case_sensitive FROM store_data
+------------------+----------------+
| case_insensitive | case_sensitive |
+------------------+----------------+
| amy | null |
+------------------+----------------+
백틱을 사용하여 공백과 특수 문자를 회피합니다. 필드 이름은 대/소문자를 구분하지 않고 일치합니다.
-- Use backticks to escape special characters. References are case insensitive when you use backticks.
-- Use brackets to make them case sensitive.
SELECT raw:`zip code`, raw:`Zip Code`, raw:['fb:testid'] FROM store_data
+----------+----------+-----------+
| zip code | Zip Code | fb:testid |
+----------+----------+-----------+
| 94025 | 94025 | 1234 |
+----------+----------+-----------+
참고
JSON 레코드에 대/소문자를 구분하지 않는 일치로 인해 추출 경로와 일치할 수 있는 여러 열이 포함된 경우 대괄호를 사용하도록 요청하는 오류가 표시됩니다. 행의 열들이 일치할 경우, 오류가 발생하지 않습니다. 다음은 오류를 발생시킵니다: {"foo":"bar", "Foo":"bar"}, 그리고 다음은 오류를 발생시키지 않습니다.
{"foo":"bar"}
{"Foo":"bar"}
중첩 필드 추출
점 표기법이나 대괄호를 사용하여 중첩 필드를 지정합니다. 대괄호를 사용할 경우, 열 이름은 대소문자를 구별하여 매칭됩니다.
-- Use dot notation
SELECT raw:store.bicycle FROM store_data
-- the column returned is a string
+------------------+
| bicycle |
+------------------+
| { |
| "price":19.95, |
| "color":"red" |
| } |
+------------------+
-- Use brackets
SELECT raw:store['bicycle'], raw:store['BICYCLE'] FROM store_data
+------------------+---------+
| bicycle | BICYCLE |
+------------------+---------+
| { | null |
| "price":19.95, | |
| "color":"red" | |
| } | |
+------------------+---------+
배열에서 값 추출
대괄호를 사용하여 배열의 요소를 인덱싱합니다. 인덱스는 0부터 시작합니다. 별표(*) 다음에 점 또는 대괄호 표기법을 사용하여 배열의 모든 요소에서 하위 필드를 추출할 수 있습니다.
-- Index elements
SELECT raw:store.fruit[0], raw:store.fruit[1] FROM store_data
+------------------+-----------------+
| fruit | fruit |
+------------------+-----------------+
| { | { |
| "weight":8, | "weight":9, |
| "type":"apple" | "type":"pear" |
| } | } |
+------------------+-----------------+
-- Extract subfields from arrays
SELECT raw:store.book[*].isbn FROM store_data
+--------------------+
| isbn |
+--------------------+
| [ |
| null, |
| "0-553-21311-3", |
| "0-395-19395-8" |
| ] |
+--------------------+
-- Access arrays within arrays or structs within arrays
SELECT
raw:store.basket[*],
raw:store.basket[*][0] first_of_baskets,
raw:store.basket[0][*] first_basket,
raw:store.basket[*][*] all_elements_flattened,
raw:store.basket[0][2].b subfield
FROM store_data
+----------------------------+------------------+---------------------+---------------------------------+----------+
| basket | first_of_baskets | first_basket | all_elements_flattened | subfield |
+----------------------------+------------------+---------------------+---------------------------------+----------+
| [ | [ | [ | [1,2,{"b":"y","a":"x"},3,4,5,6] | y |
| [1,2,{"b":"y","a":"x"}], | 1, | 1, | | |
| [3,4], | 3, | 2, | | |
| [5,6] | 5 | {"b":"y","a":"x"} | | |
| ] | ] | ] | | |
+----------------------------+------------------+---------------------+---------------------------------+----------+
캐스트 값
:: 사용하여 값을 기본 데이터 형식으로 캐스팅할 수 있습니다.
from_json 메서드를 사용하여 중첩된 결과를 배열이나 구조체와 같은 더 복잡한 데이터 형식으로 변환합니다.
-- price is returned as a double, not a string
SELECT raw:store.bicycle.price::double FROM store_data
+------------------+
| price |
+------------------+
| 19.95 |
+------------------+
-- use from_json to cast into more complex types
SELECT from_json(raw:store.bicycle, 'price double, color string') bicycle FROM store_data
-- the column returned is a struct containing the columns price and color
+------------------+
| bicycle |
+------------------+
| { |
| "price":19.95, |
| "color":"red" |
| } |
+------------------+
SELECT from_json(raw:store.basket[*], 'array<array<string>>') baskets FROM store_data
-- the column returned is an array of string arrays
+------------------------------------------+
| basket |
+------------------------------------------+
| [ |
| ["1","2","{\"b\":\"y\",\"a\":\"x\"}]", |
| ["3","4"], |
| ["5","6"] |
| ] |
+------------------------------------------+
NULL 동작
null 값이 있는 JSON 필드가 있는 경우 null 텍스트 값이 아니라 해당 열에 대한 SQL null 값을 받게 됩니다.
select '{"key":null}':key is null sql_null, '{"key":null}':key == 'null' text_null
+-------------+-----------+
| sql_null | text_null |
+-------------+-----------+
| true | null |
+-------------+-----------+
Spark SQL 연산자를 사용하여 중첩된 데이터 변환
Apache Spark에는 복잡하고 중첩된 데이터를 사용하기 위한 여러 가지 기본 제공 함수가 있습니다. 다음 Notebook에는 예제가 포함되어 있습니다.
또한 기본 제공 Spark 연산자가 원하는 방식으로 데이터를 변환하는 데 사용할 수 없는 경우 더 높은 순서의 함수 는 많은 추가 옵션을 제공합니다.