Delen via

Query's uitvoeren op variantgegevens

  • Artikel

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.

Notitie

VARIANT columns kan niet worden gebruikt voor clustersleutels, partities of Z-ordersleutels. Het VARIANT gegevenstype kan niet worden gebruikt voor vergelijkingen, groepering, volgorde en set bewerkingen. Voor een volledige list van beperkingen, zie Beperkingen.

Een table maken met een variant column

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

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 variant column

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 velden op het hoogste niveau van : tot select.
  • Gebruik . of [<key>] om geneste velden met benoemde sleutels te manipuleren select.
  • Gebruik [<index>] om selectvalues uit matrices te selectvalues.

Notitie

Als een veldnaam een punt. () bevat, moet u deze escapen met vierkante haken ([ ]). Met de volgende query selecteert u bijvoorbeeld een veld met de naam zip.code:

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

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.

values extraheren uit variantenmatrices

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 functie variant_explodetable-valued generator kan worden gebruikt om VARIANT matrices en objecten plat te maken.

Omdat variant_explode een generatorfunctie is, gebruikt u deze als onderdeel van de FROM component in plaats van in de SELECTlist, 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 values 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 :: of cast gebruiken om values te 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|
+--------+------------+------------------+----------------------+