Query dati variant

Questo articolo descrive gli operatori SQL di Databricks che è possibile usare per eseguire query e trasformare dati semistrutturati archiviati come VARIANT. Il VARIANT tipo di dati è disponibile in Databricks Runtime 15.3 e versioni successive.

Databricks consiglia di usare VARIANT le stringhe JSON. Per gli utenti che usano attualmente stringhe JSON che cercano di eseguire la migrazione, vedere Differenze tra varianti rispetto alle stringhe JSON.

Per visualizzare esempi per l'esecuzione di query su dati semistrutturati archiviati con stringhe JSON, vedere Eseguire query su stringhe JSON.

Creare una tabella con una colonna variante

Eseguire la query seguente per creare una tabella con dati altamente annidati archiviati come VARIANT. Gli esempi in questo articolo fanno riferimento a questa tabella.

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

Campi di query in una colonna variante

La sintassi per l'esecuzione di query su stringhe JSON e altri tipi di dati complessi in Azure Databricks si applica ai VARIANT dati, tra cui:

• Usare : per selezionare i campi di primo livello.
• Usare . o [<key>] per selezionare campi annidati con chiavi denominate.
• Usare [<index>] per selezionare i valori dalle matrici.

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

Estrarre un campo variant di primo livello

Per estrarre un campo, specificare il nome del campo JSON nel percorso di estrazione. I nomi dei campi fanno sempre distinzione tra maiuscole e minuscole.

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"    |
+----------+-----------+

Se non è possibile trovare un percorso, il risultato è NULL di tipo VARIANT.

Estrarre campi annidati varianti

È possibile specificare campi annidati tramite notazione con punto o parentesi. I nomi dei campi fanno sempre distinzione tra maiuscole e minuscole.

-- 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  |
| }                |
+------------------+

Se non è possibile trovare un percorso, il risultato è NULL di tipo VARIANT.

Estrarre valori da matrici varianti

Gli elementi vengono indicizzati in matrici con parentesi. Gli indici sono basati su 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     |
| }                 | }                |
+-------------------+------------------+

Se non è possibile trovare il percorso o se l'indice di matrice non è compreso nei limiti, il risultato è NULL.

Appiattire oggetti varianti e matrici

La variant_explode funzione generatore con valori di tabella può essere usata per rendere flat VARIANT matrici e oggetti.

Poiché variant_explode è una funzione generatore, viene usata come parte della FROM clausola anziché nell'elenco SELECT , come negli esempi seguenti:

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"}|
+---+-----------------+

Regole di cast dei tipi varianti

È possibile archiviare matrici e scalari usando VARIANT il tipo . Quando si tenta di eseguire il cast di tipi varianti ad altri tipi, le regole di cast normali si applicano a singoli valori e campi, con le regole aggiuntive seguenti.

Quando si deduce il tipo con schema_of_variant o schema_of_variant_agg, le funzioni eseguono il fallback al tipo anziché VARIANT al STRING tipo quando sono presenti tipi in conflitto che non possono essere risolti.

È possibile usare :: o cast per eseguire il cast dei valori ai tipi di dati supportati.

-- 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"  |
| }                |
+------------------+

Regole null varianti

Le varianti possono contenere due tipi di valori Null:

• SQL NULL: SQL NULLindica che il valore è mancante. Questi sono gli stessi NULLdi quando si gestiscono dati strutturati.
• Variant NULL: variant NULLs indicano che la variante contiene in modo esplicito un NULL valore. Non sono uguali a SQL NULL, perché il NULL valore viene archiviato nei dati.

Usare la is_variant_null funzione per determinare se il valore variant è una variante 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|
+--------+------------+------------------+----------------------+