الاستعلام عن البيانات شبه المنظمة في Azure Databricks

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

إشعار

تتيح لك هذه الميزة قراءة البيانات شبه المنظمة دون تسوية الملفات. ومع ذلك، للحصول على أداء استعلام القراءة الأمثل، توصي Databricks باستخراج الأعمدة المتداخلة بأنواع البيانات الصحيحة.

يمكنك استخراج عمود من حقول تحتوي على سلاسل JSON باستخدام بناء الجملة <column-name>:<extraction-path>، حيث <column-name> هو اسم عمود السلسلة وهو <extraction-path> المسار إلى الحقل لاستخراجه. النتائج التي تم إرجاعها هي سلاسل.

إنشاء جدول ببيانات متداخلة للغاية

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

CREATE TABLE store_data AS SELECT
'{
   "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 في مسار الاستخراج.

يمكنك توفير أسماء الأعمدة ضمن أقواس. تتم مطابقة الأعمدة المشار إليها داخل الأقواس مع حالة الأحرف بشكل حساس. كما تتم الإشارة إلى اسم العمود بشكل غير حساس لحالة الأحرف.

SELECT raw:owner, RAW:owner FROM store_data

+-------+-------+
| owner | owner |
+-------+-------+
| amy   | amy   |
+-------+-------+

-- References are case sensitive when you use brackets
SELECT raw:OWNER case_insensitive, raw:['OWNER'] case_sensitive FROM store_data

+------------------+----------------+
| case_insensitive | case_sensitive |
+------------------+----------------+
| amy              | null           |
+------------------+----------------+

استخدم backticks للهروب من المسافات والأحرف الخاصة. يتم مطابقة أسماء الحقول بحالة غير حساسة.

-- Use backticks to escape special characters. References are case insensitive when you use backticks.
-- Use brackets to make them case sensitive.
SELECT raw:`zip code`, raw:`Zip Code`, raw:['fb:testid'] FROM store_data

+----------+----------+-----------+
| zip code | Zip Code | fb:testid |
+----------+----------+-----------+
| 94025    | 94025    | 1234      |
+----------+----------+-----------+

إشعار

إذا كان سجل JSON يحتوي على أعمدة متعددة يمكن أن تتطابق مع مسار الاستخراج بسبب المطابقة غير الحساسة لحالة الأحرف، فستتلقى خطأ يطلب منك استخدام الأقواس. إذا كانت لديك تطابقات للأعمدة عبر الصفوف، فلن تتلقى أي أخطاء. سيؤدي ما يلي إلى ظهور خطأ: {"foo":"bar", "Foo":"bar"}، ولن يطرح ما يلي خطأ:

{"foo":"bar"}
{"Foo":"bar"}

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

يمكنك تحديد الحقول المتداخلة من خلال تدوين النقطة أو استخدام الأقواس. عند استخدام الأقواس، تتم مطابقة الأعمدة لحالة الأحرف بشكل حساس.

-- Use dot notation
SELECT raw:store.bicycle FROM store_data
-- the column returned is a string

+------------------+
| bicycle          |
+------------------+
| {                |
|   "price":19.95, |
|   "color":"red"  |
| }                |
+------------------+

-- Use brackets
SELECT raw:store['bicycle'], raw:store['BICYCLE'] FROM store_data

+------------------+---------+
| bicycle          | BICYCLE |
+------------------+---------+
| {                | null    |
|   "price":19.95, |         |
|   "color":"red"  |         |
| }                |         |
+------------------+---------+

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

يمكنك فهرسة العناصر في صفائف ذات أقواس. تستند المؤشرات إلى 0. يمكنك استخدام علامة نجمية (*) متبوعة بعلامة نقطة أو قوس لاستخراج الحقول الفرعية من جميع العناصر في صفيف.

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

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

-- Extract subfields from arrays
SELECT raw:store.book[*].isbn FROM store_data

+--------------------+
| isbn               |
+--------------------+
| [                  |
|   null,            |
|   "0-553-21311-3", |
|   "0-395-19395-8"  |
| ]                  |
+--------------------+

-- Access arrays within arrays or structs within arrays
SELECT
    raw:store.basket[*],
    raw:store.basket[*][0] first_of_baskets,
    raw:store.basket[0][*] first_basket,
    raw:store.basket[*][*] all_elements_flattened,
    raw:store.basket[0][2].b subfield
FROM store_data

+----------------------------+------------------+---------------------+---------------------------------+----------+
| basket                     | first_of_baskets | first_basket        | all_elements_flattened          | subfield |
+----------------------------+------------------+---------------------+---------------------------------+----------+
| [                          | [                | [                   | [1,2,{"b":"y","a":"x"},3,4,5,6] | y        |
|   [1,2,{"b":"y","a":"x"}], |   1,             |   1,                |                                 |          |
|   [3,4],                   |   3,             |   2,                |                                 |          |
|   [5,6]                    |   5              |   {"b":"y","a":"x"} |                                 |          |
| ]                          | ]                | ]                   |                                 |          |
+----------------------------+------------------+---------------------+---------------------------------+----------+

قيم التحويل

يمكنك استخدام :: لإرسال القيم إلى أنواع البيانات الأساسية. استخدم أسلوب from_json لتحويل النتائج المتداخلة إلى أنواع بيانات أكثر تعقيدا، مثل الصفائف أو البنيات.

-- price is returned as a double, not a string
SELECT raw:store.bicycle.price::double FROM store_data

+------------------+
| price            |
+------------------+
| 19.95            |
+------------------+

-- use from_json to cast into more complex types
SELECT from_json(raw:store.bicycle, 'price double, color string') bicycle FROM store_data
-- the column returned is a struct containing the columns price and color

+------------------+
| bicycle          |
+------------------+
| {                |
|   "price":19.95, |
|   "color":"red"  |
| }                |
+------------------+

SELECT from_json(raw:store.basket[*], 'array<array<string>>') baskets FROM store_data
-- the column returned is an array of string arrays

+------------------------------------------+
| basket                                   |
+------------------------------------------+
| [                                        |
|   ["1","2","{\"b\":\"y\",\"a\":\"x\"}]", |
|   ["3","4"],                             |
|   ["5","6"]                              |
| ]                                        |
+------------------------------------------+

سلوك NULL

عند وجود حقل JSON بقيمة null ، ستتلقى قيمة SQL null لهذا العمود، وليس قيمة نصية null .

select '{"key":null}':key is null sql_null, '{"key":null}':key == 'null' text_null

+-------------+-----------+
| sql_null    | text_null |
+-------------+-----------+
| true        | null      |
+-------------+-----------+

تحويل البيانات المتداخلة باستخدام عوامل تشغيل Spark SQL

يحتوي Apache Spark على عدد من الوظائف المضمنة للعمل مع البيانات المعقدة والمتداخلة. يحتوي دفتر الملاحظات التالي على أمثلة.

بالإضافة إلى ذلك، توفر وظائف الترتيب الأعلى العديد من الخيارات الإضافية عندما لا تتوفر عوامل تشغيل Spark المضمنة لتحويل البيانات بالطريقة التي تريدها.

دفتر ملاحظات البيانات المتداخلة المعقد

الحصول على دفتر الملاحظات