بيانات متغير الاستعلام

هام

هذه الميزة في المعاينة العامة.

توضح هذه المقالة عوامل تشغيل Databricks SQL التي يمكنك استخدامها للاستعلام عن البيانات شبه المنظمة المخزنة وتحويلها ك VARIANT. VARIANT يتوفر نوع البيانات في Databricks Runtime 15.3 وما فوق.

توصي Databricks باستخدام VARIANT سلاسل JSON. بالنسبة للمستخدمين الذين يستخدمون حاليا سلاسل JSON التي تبحث عن الترحيل، راجع كيف يختلف المتغير عن سلاسل JSON؟.

إذا كنت تريد الاطلاع على أمثلة للاستعلام عن البيانات شبه المنظمة المخزنة باستخدام سلاسل JSON، فشاهد الاستعلام عن سلاسل JSON.

إنشاء جدول بعمود متغير

قم بتشغيل الاستعلام التالي لإنشاء جدول مع بيانات متداخلة للغاية مخزنة ك VARIANT. تشير الأمثلة الواردة في هذه المقالة إلى هذا الجدول.

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

حقول الاستعلام في عمود متغير

ينطبق بناء الجملة للاستعلام عن سلاسل JSON وأنواع البيانات المعقدة الأخرى على Azure Databricks على VARIANT البيانات، بما في ذلك ما يلي:

• استخدم : لتحديد حقول المستوى الأعلى.
• استخدم . أو [<key>] لتحديد الحقول المتداخلة باستخدام مفاتيح مسماة.
• استخدم [<index>] لتحديد القيم من الصفائف.

إشعار

إذا كان اسم الحقل يحتوي على نقطة (.)، فيجب عليك تخطيها بأقواس مربعة ([ ]). على سبيل المثال، يحدد الاستعلام التالي حقلا باسم zip.code:

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

استخراج حقل متغير من المستوى الأعلى

لاستخراج حقل، حدد اسم حقل JSON في مسار الاستخراج. أسماء الحقول دائما حساسة لحالة الأحرف.

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

إذا تعذر العثور على مسار، تكون NULL النتيجة من النوع VARIANT.

استخراج الحقول المتداخلة المتغيرة

يمكنك تحديد الحقول المتداخلة من خلال تدوين النقطة أو استخدام الأقواس. أسماء الحقول دائما حساسة لحالة الأحرف.

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

إذا تعذر العثور على مسار، تكون NULL النتيجة من النوع VARIANT.

استخراج القيم من صفائف متغيرة

يمكنك فهرسة العناصر في صفائف ذات أقواس. تستند المؤشرات إلى 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     |
| }                 | }                |
+-------------------+------------------+

إذا تعذر العثور على المسار، أو إذا كان فهرس الصفيف خارج الحدود، فإن النتيجة هي NULL.

تسوية الكائنات والصفائف المتغيرة

variant_explode يمكن استخدام دالة المولد ذات القيمة الجدولية لتسطيح VARIANT الصفائف والكائنات.

لأن variant_explode هي دالة مولد، يمكنك استخدامها كجزء من FROM عبارة بدلا من القائمة SELECT ، كما في الأمثلة التالية:

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

قواعد تحويل نوع المتغير

يمكنك تخزين الصفائف والأحجام باستخدام VARIANT النوع. عند محاولة تحويل أنواع المتغيرات إلى أنواع أخرى، تنطبق قواعد التحويل العادية على القيم والحقول الفردية، مع القواعد الإضافية التالية.

إشعار

variant_get وأخذ try_variant_get وسيطات النوع واتبع قواعد التحويل هذه.

نوع المصدر	سلوك
VOID	والنتيجة هي NULL من نوع VARIANT.
ARRAY<elementType>	elementType يجب أن يكون النوع الذي يمكن تحويله إلى VARIANT.

عند الاستدلال على النوع مع schema_of_variant أو schema_of_variant_agg، تعود الدالات إلى VARIANT الكتابة بدلا من STRING الكتابة عند وجود أنواع متعارضة لا يمكن حلها.

يمكنك استخدام :: أو cast لإرسال القيم إلى أنواع البيانات المدعومة.

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

قواعد فارغة متغيرة

يمكن أن تحتوي المتغيرات على نوعين من القيم الخالية:

• SQL NULL: تشير SQL NULLs إلى أن القيمة مفقودة. هذه هي نفسها NULLعند التعامل مع البيانات المنظمة.
• المتغير NULL: يشير المتغير NULLs إلى أن المتغير يحتوي بشكل صريح على NULL قيمة. هذه ليست نفس SQL NULLs، لأن القيمة مخزنة NULL في البيانات.

استخدم الدالة is_variant_null لتحديد ما إذا كانت قيمة المتغير متغيرا 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|
+--------+------------+------------------+----------------------+