Query's uitvoeren op variantgegevens

Artikel
07/31/2024

Belangrijk

Deze functie is beschikbaar als openbare preview.

In dit artikel worden de Databricks SQL-operators beschreven die u kunt gebruiken om semi-gestructureerde gegevens op te vragen en te transformeren die zijn opgeslagen als VARIANT. Het VARIANT gegevenstype is beschikbaar in Databricks Runtime 15.3 en hoger.

Databricks raadt aan om JSON-tekenreeksen te gebruiken VARIANT . Zie Hoe verschilt de variant van JSON-tekenreeksen die momenteel worden gemigreerd voor gebruikers die JSON-tekenreeksen willen migreren.

Als u voorbeelden wilt zien voor het uitvoeren van query's op semi-gestructureerde gegevens die zijn opgeslagen met JSON-tekenreeksen, raadpleegt u Query-JSON-tekenreeksen.

Een tabel met een variantkolom maken

Voer de volgende query uit om een tabel te maken met zeer geneste gegevens die zijn opgeslagen als VARIANT. De voorbeelden in dit artikel verwijzen allemaal naar deze tabel.

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

Queryvelden in een variantkolom

De syntaxis voor het opvragen van JSON-tekenreeksen en andere complexe gegevenstypen in Azure Databricks is van toepassing op VARIANT gegevens, waaronder de volgende:

Gebruik : deze optie om velden op het hoogste niveau te selecteren.
Gebruik . of [<key>] selecteer geneste velden met benoemde sleutels.
Gebruik [<index>] deze optie om waarden te selecteren uit matrices.

Een variantveld op het hoogste niveau extraheren

Als u een veld wilt extraheren, geeft u de naam op van het JSON-veld in het extractiepad. Veldnamen zijn altijd hoofdlettergevoelig.

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

Als een pad niet kan worden gevonden, is NULL het resultaat van het type VARIANT.

Geneste variantvelden extraheren

U geeft geneste velden op via punt notatie of met vierkante haken. Veldnamen zijn altijd hoofdlettergevoelig.

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

Als een pad niet kan worden gevonden, is NULL het resultaat van het type VARIANT.

Waarden extraheren uit variantmatrices

U indexeert elementen in matrices met vierkante haken. Indexen zijn gebaseerd op 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     |
| }                 | }                |
+-------------------+------------------+

Als het pad niet kan worden gevonden of als de matrixindex buiten de grenzen valt, is NULLhet resultaat.

Afgevlakte variantobjecten en matrices

De variant_explode generatorfunctie met tabelwaarden kan worden gebruikt om matrices en objecten plat te maken VARIANT .

Omdat variant_explode dit een generatorfunctie is, gebruikt u deze als onderdeel van de FROM component in plaats van in de SELECT lijst, zoals in de volgende voorbeelden:

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

Cast-regels voor varianttypen

U kunt matrices en scalaire waarden opslaan met behulp van VARIANT het type. Wanneer u varianttypen probeert te casten naar andere typen, zijn normale cast-regels van toepassing op afzonderlijke waarden en velden, met de volgende aanvullende regels.

Notitie

variant_get en try_variant_get neem de typeargumenten en volg deze cast-regels.

Brontype	Gedrag
`VOID`	Het resultaat is een van het `NULL` type `VARIANT`.
`ARRAY<elementType>`	Het `elementType` moet een type zijn dat kan worden gecast naar `VARIANT`.

Wanneer u het type met schema_of_variant of schema_of_variant_aggals uitstel opgeeft, vallen functies terug op VARIANT type in plaats STRING van type wanneer er conflicterende typen aanwezig zijn die niet kunnen worden opgelost.

U kunt waarden gebruiken :: of cast casten naar ondersteunde gegevenstypen.

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

Null-regels voor varianten

Varianten kunnen twee soorten null-waarden bevatten:

SQL NULL: SQL NULLs geven aan dat de waarde ontbreekt. Dit zijn dezelfde NULLals bij het omgaan met gestructureerde gegevens.
Variant: Variant NULLNULLs geven aan dat de variant expliciet een NULL waarde bevat. Deze zijn niet hetzelfde als SQL NULLs, omdat de NULL waarde wordt opgeslagen in de gegevens.

Gebruik de is_variant_null functie om te bepalen of de variantwaarde een variant NULLis.

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

Delen via