Wykonywanie zapytań dotyczących danych wariantów
Ważne
Ta funkcja jest dostępna w publicznej wersji zapoznawczej.
W tym artykule opisano operatory SQL usługi Databricks, których można użyć do wykonywania zapytań i przekształcania częściowo ustrukturyzowanych danych przechowywanych jako VARIANT. Typ VARIANT danych jest dostępny w środowisku Databricks Runtime 15.3 lub nowszym.
Usługa Databricks zaleca używanie VARIANT ciągów JSON. W przypadku użytkowników korzystających obecnie z ciągów JSON, którzy chcą przeprowadzić migrację, zobacz How is variant different than JSON strings? (Jak różni się wariant niż ciągi JSON?).
Jeśli chcesz zobaczyć przykłady wykonywania zapytań dotyczących częściowo ustrukturyzowanych danych przechowywanych za pomocą ciągów JSON, zobacz Query JSON strings (Wykonywanie zapytań w ciągach JSON).
Uwaga
Kolumny VARIANT nie mogą być używane jako klucze klastrowania, partycje ani klucze Z-order. Nie można używać VARIANT typu danych na potrzeby porównań, grupowania, porządkowania i ustawiania operacji. Aby uzyskać pełną listę ograniczeń, zobacz Ograniczenia.
Tworzenie tabeli z kolumną wariantu
Uruchom następujące zapytanie, aby utworzyć tabelę z wysoce zagnieżdżonymi danymi przechowywanymi jako VARIANT. Przykłady w tym artykule odwołują się do tej tabeli.
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
Wykonywanie zapytań względem pól w kolumnie wariantu
Składnia wykonywania zapytań dotyczących ciągów JSON i innych złożonych typów danych w usłudze Azure Databricks ma zastosowanie do VARIANT danych, w tym następujących:
- Użyj
:polecenia , aby wybrać pola najwyższego poziomu. - Użyj
.lub[<key>]wybierz pola zagnieżdżone z nazwanymi kluczami. - Służy
[<index>]do wybierania wartości z tablic.
Uwaga
Jeśli nazwa pola zawiera kropkę (.), należy ją usunąć z nawiasami kwadratowymi ([ ]). Na przykład następujące zapytanie wybiera pole o nazwie zip.code:
SELECT raw:['zip.code'] FROM store_data
Wyodrębnianie pola wariantu najwyższego poziomu
Aby wyodrębnić pole, określ nazwę pola JSON w ścieżce wyodrębniania. W nazwach pól zawsze jest rozróżniana wielkość liter.
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" |
+----------+-----------+
Jeśli nie można odnaleźć ścieżki, wynik jest NULL typu VARIANT.
Wyodrębnianie zagnieżdżonych pól wariantu
Pola zagnieżdżone można określić zagnieżdżonym notacją kropkową lub nawiasami kwadratowymi. W nazwach pól zawsze jest rozróżniana wielkość liter.
-- 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 |
| } |
+------------------+
Jeśli nie można odnaleźć ścieżki, wynik jest NULL typu VARIANT.
Wyodrębnianie wartości z tablic wariantów
Elementy indeksowania w tablicach z nawiasami kwadratowymi. Indeksy są oparte na 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 |
| } | } |
+-------------------+------------------+
Jeśli nie można odnaleźć ścieżki lub jeśli indeks tablicy jest poza granicami, wynikiem jest NULL.
Spłaszczać obiekty i tablice wariantów
Funkcja variant_explode generatora wartości tabeli może służyć do spłaszczania VARIANT tablic i obiektów.
Ponieważ variant_explode jest funkcją generatora, należy użyć jej jako części FROM klauzuli, a nie na SELECT liście, jak w następujących przykładach:
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"}|
+---+-----------------+
Reguły rzutywania typów wariantów
Tablice i skalarne można przechowywać przy użyciu VARIANT typu. Podczas próby rzutowania typów wariantów do innych typów reguły rzutowania normalnego dotyczą poszczególnych wartości i pól z następującymi dodatkowymi regułami.
Uwaga
variant_get i weź argumenty typu i try_variant_get postępuj zgodnie z tymi regułami rzutu.
| Source type | Zachowanie |
|---|---|
VOID |
Wynik jest typem NULLVARIANT. |
ARRAY<elementType> |
Musi elementType być typem, który można rzutować na VARIANT. |
Podczas wnioskowania typu z schema_of_variant lub schema_of_variant_aggfunkcje wracają do VARIANT typu, a nie STRING typu, gdy występują typy powodujące konflikty, których nie można rozwiązać.
Możesz użyć :: funkcji lub cast rzutować wartości do obsługiwanych typów danych.
-- 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" |
| } |
+------------------+
Reguły wartości null wariantu
Warianty mogą zawierać dwa rodzaje wartości null:
-
NULLs wskazuje, że brakuje wartości. Są one takie sameNULLjak w przypadku obsługi danych ustrukturyzowanych. -
Wariant : wariant
NULLNULLwskazuje, że wariant jawnie zawieraNULLwartość. Nie są one takie same jak sqlNULLs, ponieważNULLwartość jest przechowywana w danych.
is_variant_null Użyj funkcji , aby określić, czy wartość wariantu jest wariantem 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|
+--------+------------+------------------+----------------------+