Azure Databricks에서 반구조화된 데이터 쿼리
이 문서에서는 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 레코드에 대/소문자를 구분하지 않는 일치로 인해 추출 경로와 일치할 수 있는 여러 열이 포함된 경우 대괄호를 사용하라는 오류가 표시됩니다. 행 간에 일치하는 열이 있는 경우 오류가 발생하지 않습니다. 다음은 오류가 throw({"foo":"bar", "Foo":"bar"})되며, 다음은 오류가 throw되지 않습니다.
{"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 연산자를 사용할 수 없는 경우 더 높은 차수의 함수를 사용하면 많은 추가 옵션을 제공됩니다.