Değişken verileri sorgulama

Bu makalede, olarak VARIANTdepolanan yarı yapılandırılmış verileri sorgulamak ve dönüştürmek için kullanabileceğiniz Databricks SQL işleçleri açıklanmaktadır. VARIANT Veri türü Databricks Runtime 15.3 ve üzerinde kullanılabilir.

Databricks, JSON dizeleri üzerinden kullanılmasını VARIANT önerir. Şu anda geçiş yapmak isteyen JSON dizelerini kullanan kullanıcılar için bkz . Değişken JSON dizelerinden nasıl farklıdır?.

JSON dizeleriyle depolanan yarı yapılandırılmış verileri sorgulamaya yönelik örnekler görmek istiyorsanız bkz . JSON dizelerini sorgulama.

Değişken table ile column oluşturma

tableolarak depolanan yüksek düzeyde iç içe geçmiş verilerle bir VARIANT oluşturmak için aşağıdaki sorguyu çalıştırın. Bu makaledeki örneklerin tümü bu table'a atıfta bulunmaktadır.

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

Değişken column alanları sorgulama

Azure Databricks'te JSON dizelerini ve diğer karmaşık veri türlerini sorgulama söz dizimi, aşağıdakiler de dahil olmak üzere veriler için VARIANT geçerlidir:

• :'dan select'e kadar olan üst düzey alanları kullanın.
• . olarak adlandırılmış anahtarlarla iç içe alanlar için [<key>] veya select kullanın.
• Dizilerden [<index>]select için values kullanın.

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

En üst düzey değişken alanını ayıklama

Bir alanı ayıklamak için ayıklama yolunuzda JSON alanının adını belirtin. Alan adları her zaman büyük/küçük harfe duyarlıdır.

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

Yol bulunamazsa, sonuç türünde NULLolurVARIANT.

Değişken iç içe alanları ayıklama

noktalı gösterimi veya köşeli ayraç kullanarak iç içe alanları belirtirsiniz. Alan adları her zaman büyük/küçük harfe duyarlıdır.

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

Yol bulunamazsa, sonuç türünde NULLolurVARIANT.

Değişken dizilerden values'yi ayıkla.

Dizilerdeki öğeleri köşeli ayraçlarla dizinlersiniz. Dizinler 0 tabanlıdır.

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

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

Yol bulunamazsa veya dizi dizini sınırların dışındaysa sonuç olur NULL.

Değişken nesneleri ve dizileri düzleştirme

variant_explode tabledeğerli generator fonksiyonu, VARIANT dizileri ve nesneleri düzleştirmek için kullanılabilir.

variant_explode bir oluşturucu işlevi olduğundan, aşağıdaki örneklerde olduğu gibi FROMSELECTyerine list yan tümcesinin bir parçası olarak kullanırsınız:

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

Değişken türü atama kuralları

Tür kullanarak VARIANT dizileri ve skalerleri depolayabilirsiniz. Varyant türlerini diğer türlere atamaya çalışırken, bireysel values ve alanlar için normal atama kuralları geçerlidir ve aşağıdaki ek kurallar da uygulanır.

veya schema_of_variantile schema_of_variant_agg tür çıkarılırken, çözümlenebilen çakışan türler mevcut olduğunda işlevler tür yerine VARIANT türe geri dönerSTRING.

:: veya cast kullanarak values desteklenen veri türlerine dönüştürebilirsiniz.

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

Değişken null kuralları

Değişkenler iki tür null içerebilir:

• SQL NULL: SQL NULLs değerin eksik olduğunu gösterir. Bunlar, yapılandırılmış verilerle ilgilenirken kullanılanlarla aynıdır NULL.
• Değişken NULL: Değişken NULLs, değişkenin açıkça bir NULL değer içerdiğini gösterir. Değer verilerde depolandığındanNULL, bunlar SQL NULLile aynı değildir.

Değişken değerinin is_variant_null bir değişken NULLolup olmadığını belirlemek için işlevini kullanın.

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