Перейти к содержимому
Последнее обновление

VARIANT

В этом документе описана загрузка данных CSV или JSON в столбец VARIANT в Doris: от создания таблицы до загрузки данных и проверки результата.

Для кого предназначен документ и что прочитать заранее​

Прежде чем читать этот документ, выберите материал, который лучше всего соответствует вашей задаче:

Ваша задачаРекомендуемый материал
Быстро загрузить данные CSV / JSONПродолжайте читать этот документ
Выбрать между режимом по умолчанию, Sparse, режимом DOC и Schema TemplateРуководство по использованию и настройке VARIANT
Найти сведения о синтаксисе запросов к VARIANT, индексах, ограничениях или параметрах настройкиVARIANT

Ограничения​

  • В приведённых ниже примерах загружаются файлы CSV и JSON. В задании загрузки любое строковое поле, загружаемое в столбец VARIANT, разбирается как JSON, поэтому другие форматы работают так же, если исходный столбец содержит строки, например столбец STRING в Parquet. Формат Arrow не поддерживает загрузку столбцов VARIANT.

Как обрабатываются загружаемые значения​

  • Столбцы NOT NULL: строка, в которой значение VARIANT равно SQL NULL, отфильтровывается, в том числе если SQL NULL получен из-за ошибки разбора. Отфильтрованные строки учитываются в max_filter_ratio (по умолчанию 0), поэтому при настройках по умолчанию задание загрузки завершается ошибкой.
  • Особенность INSERT: INSERT не выполняет разбор строк. INSERT INTO t VALUES (1, '{"a": 1}') сохраняет в VARIANT строку {"a": 1}; так же работает INSERT INTO ... SELECT из s3(), hdfs() или local(). Чтобы сохранить объект, используйте PARSE_TO_VARIANT.

Сохраняемые значения нормализуются: вне массивов из объектов удаляются поля со значением null, {} или []. То же значение, вычисленное в запросе, сохраняет все поля:

CREATE TABLE variant_norm (k INT, v VARIANT)
DUPLICATE KEY(k)
DISTRIBUTED BY HASH(k) BUCKETS 1
PROPERTIES ("replication_num" = "1");

INSERT INTO variant_norm VALUES (1, PARSE_TO_VARIANT('{"a": null, "b": {}, "c": [], "d": 1}'));

SELECT PARSE_TO_VARIANT('{"a": null, "b": {}, "c": [], "d": 1}') AS computed;
SELECT v AS stored, v['a'] IS NULL AS a_is_null FROM variant_norm;
+--------------------------------+
| computed                       |
+--------------------------------+
| {"a":null,"b":{},"c":[],"d":1} |
+--------------------------------+

+---------+-----------+
| stored  | a_is_null |
+---------+-----------+
| {"d":1} |         1 |
+---------+-----------+

Об ошибках разбора и других правилах нормализации см. разделы Ошибки разбора и Что сохраняется при хранении.

Рекомендуемый формат хранения (V3)​

Для новых таблиц с VARIANT, особенно при работе с широкими JSON-объектами, сразу используйте Storage Format V3, если у вас нет веских причин выбрать другой формат. Обоснование этого решения приведено в разделе Storage Format V3.

Явно включите этот формат через PROPERTIES при создании таблицы:

CREATE TABLE table_v3 (
    id BIGINT,
    data VARIANT
)
DISTRIBUTED BY HASH(id) BUCKETS 32
PROPERTIES (
    "storage_format" = "V3"
);

Загрузка данных в формате CSV​

Шаг 1: Подготовьте данные​

Создайте CSV-файл с именем test_variant.csv и следующим содержимым:

14186154924|PushEvent|{"avatar_url":"https://avatars.githubusercontent.com/u/282080?","display_login":"brianchandotcom","gravatar_id":"","id":282080,"login":"brianchandotcom","url":"https://api.github.com/users/brianchandotcom"}|{"id":1920851,"name":"brianchandotcom/liferay-portal","url":"https://api.github.com/repos/brianchandotcom/liferay-portal"}|{"before":"abb58cc0db673a0bd5190000d2ff9c53bb51d04d","commits":[""],"distinct_size":4,"head":"91edd3c8c98c214155191feb852831ec535580ba","push_id":6027092734,"ref":"refs/heads/master","size":4}|1|2020-11-14 02:00:00

Шаг 2: Создайте таблицу в базе данных​

Выполните следующий SQL-оператор, чтобы создать таблицу:

CREATE TABLE IF NOT EXISTS testdb.test_variant (
    id BIGINT NOT NULL,
    type VARCHAR(30) NULL,
    actor VARIANT NULL,
    repo VARIANT NULL,
    payload VARIANT NULL,
    public BOOLEAN NULL,
    created_at DATETIME NULL,
    INDEX idx_payload (`payload`) USING INVERTED PROPERTIES("parser" = "english") COMMENT 'inverted index for payload'
)
DUPLICATE KEY(`id`)
DISTRIBUTED BY HASH(id) BUCKETS 10
properties("replication_num" = "1");

Шаг 3: Загрузите данные​

На примере Stream Load загрузите данные следующей командой:

curl --location-trusted -u root:  -T test_variant.csv -H "column_separator:|" http://127.0.0.1:8030/api/testdb/test_variant/_stream_load

Пример ответа при успешной загрузке:

{
    "TxnId": 12,
    "Label": "96cd6250-9c78-4a9f-b8b3-2b7cef0dd606",
    "Comment": "",
    "TwoPhaseCommit": "false",
    "Status": "Success",
    "Message": "OK",
    "NumberTotalRows": 1,
    "NumberLoadedRows": 1,
    "NumberFilteredRows": 0,
    "NumberUnselectedRows": 0,
    "LoadBytes": 660,
    "LoadTimeMs": 213,
    "BeginTxnTimeMs": 0,
    "StreamLoadPutTimeMs": 6,
    "ReadDataTimeMs": 0,
    "WriteDataTimeMs": 183,
    "ReceiveDataTimeMs": 14,
    "CommitAndPublishTimeMs": 20
}

Шаг 4: Проверьте загруженные данные​

Выполните следующий SQL-запрос, чтобы убедиться, что данные записаны:

mysql> select * from testdb.test_variant\G
*************************** 1. row ***************************
        id: 14186154924
      type: PushEvent
     actor: {"avatar_url":"https://avatars.githubusercontent.com/u/282080?","display_login":"brianchandotcom","gravatar_id":"","id":282080,"login":"brianchandotcom","url":"https://api.github.com/users/brianchandotcom"}
      repo: {"id":1920851,"name":"brianchandotcom/liferay-portal","url":"https://api.github.com/repos/brianchandotcom/liferay-portal"}
   payload: {"before":"abb58cc0db673a0bd5190000d2ff9c53bb51d04d","commits":[""],"distinct_size":4,"head":"91edd3c8c98c214155191feb852831ec535580ba","push_id":6027092734,"ref":"refs/heads/master","size":4}
    public: 1
created_at: 2020-11-14 02:00:00

Загрузка данных в формате JSON​

Шаг 1: Подготовьте данные​

Создайте JSON-файл с именем test_variant.json и следующим содержимым:

{"id": "14186154924","type": "PushEvent","actor": {"id": 282080,"login":"brianchandotcom","display_login": "brianchandotcom","gravatar_id": "","url": "https://api.github.com/users/brianchandotcom","avatar_url": "https://avatars.githubusercontent.com/u/282080?"},"repo": {"id": 1920851,"name": "brianchandotcom/liferay-portal","url": "https://api.github.com/repos/brianchandotcom/liferay-portal"},"payload": {"push_id": 6027092734,"size": 4,"distinct_size": 4,"ref": "refs/heads/master","head": "91edd3c8c98c214155191feb852831ec535580ba","before": "abb58cc0db673a0bd5190000d2ff9c53bb51d04d","commits": [""]},"public": true,"created_at": "2020-11-13T18:00:00Z"}

Шаг 2: Создайте таблицу в базе данных​

Выполните следующий SQL-оператор, чтобы создать таблицу:

CREATE TABLE IF NOT EXISTS testdb.test_variant (
    id BIGINT NOT NULL,
    type VARCHAR(30) NULL,
    actor VARIANT NULL,
    repo VARIANT NULL,
    payload VARIANT NULL,
    public BOOLEAN NULL,
    created_at DATETIME NULL,
    INDEX idx_payload (`payload`) USING INVERTED PROPERTIES("parser" = "english") COMMENT 'inverted index for payload'
)
DUPLICATE KEY(`id`)
DISTRIBUTED BY HASH(id) BUCKETS 10;

Шаг 3: Загрузите данные​

На примере Stream Load загрузите данные следующей командой:

curl --location-trusted -u root:  -T test_variant.json -H "format:json"  http://127.0.0.1:8030/api/testdb/test_variant/_stream_load

Пример ответа при успешной загрузке:

{
    "TxnId": 12,
    "Label": "96cd6250-9c78-4a9f-b8b3-2b7cef0dd606",
    "Comment": "",
    "TwoPhaseCommit": "false",
    "Status": "Success",
    "Message": "OK",
    "NumberTotalRows": 1,
    "NumberLoadedRows": 1,
    "NumberFilteredRows": 0,
    "NumberUnselectedRows": 0,
    "LoadBytes": 660,
    "LoadTimeMs": 213,
    "BeginTxnTimeMs": 0,
    "StreamLoadPutTimeMs": 6,
    "ReadDataTimeMs": 0,
    "WriteDataTimeMs": 183,
    "ReceiveDataTimeMs": 14,
    "CommitAndPublishTimeMs": 20
}

Шаг 4: Проверьте загруженные данные​

Выполните следующий SQL-запрос, чтобы убедиться, что данные записаны:

mysql> select * from testdb.test_variant\G
*************************** 1. row ***************************
        id: 14186154924
      type: PushEvent
     actor: {"avatar_url":"https://avatars.githubusercontent.com/u/282080?","display_login":"brianchandotcom","gravatar_id":"","id":282080,"login":"brianchandotcom","url":"https://api.github.com/users/brianchandotcom"}
      repo: {"id":1920851,"name":"brianchandotcom/liferay-portal","url":"https://api.github.com/repos/brianchandotcom/liferay-portal"}
   payload: {"before":"abb58cc0db673a0bd5190000d2ff9c53bb51d04d","commits":[""],"distinct_size":4,"head":"91edd3c8c98c214155191feb852831ec535580ba","push_id":6027092734,"ref":"refs/heads/master","size":4}
    public: 1
created_at: 2020-11-14 02:00:00

Шаг 5: Проверьте вывод типов​

По умолчанию DESC показывает только столбец VARIANT верхнего уровня и не раскрывает вложенные столбцы:

mysql> desc test_variant;
+------------+---------------------------+------+-------+---------+-------+
| Field      | Type                      | Null | Key   | Default | Extra |
+------------+---------------------------+------+-------+---------+-------+
| id         | bigint                    | No   | true  | NULL    |       |
| type       | varchar(30)               | Yes  | false | NULL    | NONE  |
| actor      | variant<PROPERTIES (...)> | Yes  | false | NULL    | NONE  |
| repo       | variant<PROPERTIES (...)> | Yes  | false | NULL    | NONE  |
| payload    | variant<PROPERTIES (...)> | Yes  | false | NULL    | NONE  |
| public     | boolean                   | Yes  | false | NULL    | NONE  |
| created_at | datetime                  | Yes  | false | NULL    | NONE  |
+------------+---------------------------+------+-------+---------+-------+
7 rows in set

Строка типа VARIANT, в которой перечислены свойства столбца, здесь сокращена до variant<PROPERTIES (...)>.

После включения describe_extend_variant_column можно просмотреть типы вложенных столбцов, выведенные из столбца VARIANT:

mysql> set describe_extend_variant_column = true;
Query OK, 0 rows affected (0.00 sec)

mysql> desc test_variant;
+-----------------------+---------------------------+------+-------+---------+-------+
| Field                 | Type                      | Null | Key   | Default | Extra |
+-----------------------+---------------------------+------+-------+---------+-------+
| id                    | bigint                    | No   | true  | NULL    |       |
| type                  | varchar(30)               | Yes  | false | NULL    | NONE  |
| actor                 | variant<PROPERTIES (...)> | Yes  | false | NULL    | NONE  |
| repo                  | variant<PROPERTIES (...)> | Yes  | false | NULL    | NONE  |
| payload               | variant<PROPERTIES (...)> | Yes  | false | NULL    | NONE  |
| public                | boolean                   | Yes  | false | NULL    | NONE  |
| created_at            | datetime                  | Yes  | false | NULL    | NONE  |
| actor.avatar_url      | text                      | Yes  | false | NULL    | NONE  |
| actor.display_login   | text                      | Yes  | false | NULL    | NONE  |
| actor.gravatar_id     | text                      | Yes  | false | NULL    | NONE  |
| actor.id              | bigint                    | Yes  | false | NULL    | NONE  |
| actor.login           | text                      | Yes  | false | NULL    | NONE  |
| actor.url             | text                      | Yes  | false | NULL    | NONE  |
| payload.before        | text                      | Yes  | false | NULL    | NONE  |
| payload.commits       | array<text>               | Yes  | false | NULL    | NONE  |
| payload.distinct_size | bigint                    | Yes  | false | NULL    | NONE  |
| payload.head          | text                      | Yes  | false | NULL    | NONE  |
| payload.push_id       | bigint                    | Yes  | false | NULL    | NONE  |
| payload.ref           | text                      | Yes  | false | NULL    | NONE  |
| payload.size          | bigint                    | Yes  | false | NULL    | NONE  |
| repo.id               | bigint                    | Yes  | false | NULL    | NONE  |
| repo.name             | text                      | Yes  | false | NULL    | NONE  |
| repo.url              | text                      | Yes  | false | NULL    | NONE  |
+-----------------------+---------------------------+------+-------+---------+-------+
23 rows in set

Результаты вывода типов также можно просмотреть для отдельной партиции:

DESCRIBE ${table_name} PARTITION ($partition_name);

Часто задаваемые вопросы​

Q1: Какие форматы данных поддерживаются при загрузке в VARIANT?​

Задания загрузки разбирают любое строковое поле, загружаемое в столбец VARIANT, как JSON независимо от формата файла; в примерах этого документа используются CSV и JSON. При использовании INSERT, в том числе INSERT INTO ... SELECT из табличных функций, оборачивайте строковые значения в PARSE_TO_VARIANT.

Q2: Когда требуется Storage Format V3?​

Для новых таблиц с VARIANT, особенно при работе с широкими JSON-объектами с большим количеством полей, сразу используйте формат хранения V3. Более ранние форматы хранения не рекомендуются, если у вас нет веских причин их использовать.

Q3: Почему DESC не показывает вложенные столбцы, выведенные из VARIANT?​

По умолчанию DESC показывает только столбец VARIANT верхнего уровня. Сначала выполните:

SET describe_extend_variant_column = true;

После этого повторный вызов DESC покажет все выведенные вложенные столбцы и их типы. Их также можно просмотреть для отдельной партиции с помощью DESCRIBE ${table_name} PARTITION ($partition_name).

Q4: Чем отличаются операторы создания таблицы при загрузке CSV и JSON?​

Операторы создания таблицы практически одинаковы. Единственное отличие состоит в том, что в примере CSV явно указано "replication_num" = "1". Различие при загрузке заключается в команде Stream Load:

ФорматОсновной заголовок
CSV-H "column_separator:|"
JSON-H "format:json"

Q5: Как проверить, успешно ли завершилась загрузка Stream Load?​

Проверьте поле Status в возвращённом JSON:

  • Значение Status, равное Success, указывает на успешную загрузку.
  • Значение NumberLoadedRows должно быть равно NumberTotalRows, а NumberFilteredRows должно быть равно 0.

Q6: Почему INSERT сохраняет мой JSON-текст как строку?​

INSERT преобразует строку в VARIANT с помощью CAST(string AS VARIANT), сохраняя её как строку без разбора. Задания загрузки, такие как Stream Load, выполняют разбор JSON-текста; при использовании INSERT оборачивайте текст в PARSE_TO_VARIANT:

INSERT INTO testdb.test_variant (id, actor)
VALUES (1, PARSE_TO_VARIANT('{"id": 282080, "login": "brianchandotcom"}'));
Report issue
На этой странице