Запрос данных варианта

Статья
09/05/2024

Внимание

Эта функция предоставляется в режиме общедоступной предварительной версии.

В этой статье описаны операторы SQL Databricks, которые можно использовать для запроса и преобразования полуструктурированных данных, хранящихся в виде 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

Поля запроса в столбце variant

Синтаксис для запроса строк 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"  |
| }                |
+------------------+

Вариантные правила NULL

Варианты могут содержать два типа null:

SQL : SQL NULLNULLуказывает, что значение отсутствует. Это то же NULLсамое, что и при работе с структурированными данными.
Variant: Variant NULLNULLs указывает, что вариант явно содержит NULL значение. Это не то же самое, что и SQL NULL, так как 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|
+--------+------------+------------------+----------------------+

Поделиться через