Dela via

Frågevariantdata

  • Artikel

Viktigt!

Den här funktionen finns som allmänt tillgänglig förhandsversion.

I den här artikeln beskrivs de Databricks SQL-operatorer som du kan använda för att fråga efter och transformera halvstrukturerade data som lagras som VARIANT. Datatypen VARIANT är tillgänglig i Databricks Runtime 15.3 och senare.

Databricks rekommenderar att du använder VARIANT över JSON-strängar. För användare som för närvarande använder JSON-strängar som vill migrera, se Hur skiljer sig varianten från JSON-strängar?.

Om du vill se exempel för att köra frågor mot halvstrukturerade data som lagras med JSON-strängar läser du Fråga JSON-strängar.

Kommentar

VARIANT kolumner kan inte användas för klustring av nycklar, partitioner eller Z-ordernycklar. Den VARIANT datatypen kan inte användas för jämförelser, gruppering, ordning och uppsättningsåtgärder. En fullständig lista över begränsningar finns i Begränsningar.

Skapa en tabell med en variantkolumn

Kör följande fråga för att skapa en tabell med mycket kapslade data som lagras som VARIANT. Exemplen i den här artikeln refererar alla till den här tabellen.

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

Frågefält i en variantkolumn

Syntaxen för att fråga efter JSON-strängar och andra komplexa datatyper i Azure Databricks gäller för VARIANT data, inklusive följande:

  • Använd : för att välja fält på den översta nivån.
  • Använd . eller [<key>] för att välja kapslade fält med namngivna nycklar.
  • Använd [<index>] för att välja värden från matriser.

Kommentar

Om ett fältnamn innehåller en punkt (.) måste du undvika det med hakparenteser ([ ]). Följande fråga väljer till exempel ett fält med namnet zip.code:

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

Extrahera ett variantfält på den översta nivån

Om du vill extrahera ett fält anger du namnet på JSON-fältet i din extraheringssökväg. Fältnamn är alltid skiftlägeskänsliga.

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

Om det inte går att hitta en sökväg är NULL resultatet av typen VARIANT.

Extrahera kapslade variantfält

Du anger kapslade fält via punkt notation eller med hakparenteser. Fältnamn är alltid skiftlägeskänsliga.

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

Om det inte går att hitta en sökväg är NULL resultatet av typen VARIANT.

Extrahera värden från variantmatriser

Du indexeras element i matriser med hakparenteser. Index är 0-baserade.

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

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

Om sökvägen inte kan hittas, eller om matrisindexet ligger utanför gränserna, blir NULLresultatet .

Platta ut variantobjekt och matriser

Den tabellvärda generatorfunktionen variant_explode kan användas för att platta ut VARIANT arrayer och objekt.

Eftersom variant_explode är en generatorfunktion använder du den som en del av FROM-satsen i stället för i listan SELECT, som i följande exempel:

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

Regler för gjutning av varianttyp

Du kan lagra matriser och skalärer med hjälp av VARIANT typen . När du försöker omvandla varianttyper till andra typer gäller normala gjutningsregler för enskilda värden och fält, med följande ytterligare regler.

Kommentar

variant_get och try_variant_get ta typargument och följa dessa regler för gjutning.

Source type Funktionssätt
VOID Resultatet är av NULL typen VARIANT.
ARRAY<elementType> elementType Måste vara en typ som kan gjutas till VARIANT.

När du härleder typen med schema_of_variant eller schema_of_variant_aggåtergår funktionerna till typen i stället VARIANT för STRING typ när det finns motstridiga typer som inte kan lösas.

Du kan använda :: eller cast för att omvandla värden till datatyper som stöds.

-- 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-regler

Varianter kan innehålla två typer av null:er:

  • SQL NULL: SQL NULLs anger att värdet saknas. Dessa är samma NULLsom vid hantering av strukturerade data.
  • Variant NULL: Variant NULLs anger att varianten uttryckligen innehåller ett NULL värde. Dessa är inte samma som SQL NULLeftersom NULL värdet lagras i data.

is_variant_null Använd funktionen för att avgöra om variantvärdet är en 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|
+--------+------------+------------------+----------------------+