Перейти к основному содержимому
Версия: 2.0.x

Генерируемые столбцы

Начиная с версии 3.1, Selena поддерживает генерируемые столбцы. Генерируемые столбцы можно использовать для ускорения запросов со сложными выражениями. Эта функция поддерживает предварительное вычисление и сохранение результатов выражений и перезапись запросов, что значительно ускоряет запросы с теми же сложными выражениями.

Вы можете определить один или несколько генерируемых столбцов для хранения результатов выражений при создании таблицы. Таким образом, при выполнении запросов, содержащих выражение, результаты которого хранятся в определённом вами генерируемом столбце, CBO перезаписывает запрос для чтения данных непосредственно из генерируемого столбца. Также вы можете напрямую запрашивать данные из генерируемого столбца.

Также рекомендуется оценить влияние генерируемых столбцов на производительность загрузки, поскольку вычисление выражений занимает некоторое время. Кроме того, рекомендуется создавать генерируемые столбцы при создании таблицы вместо добавления или изменения их после создания таблицы. Это связано с тем, что добавление или изменение генерируемых столбцов после создания таблицы требует много времени и ресурсов.

Однако обратите внимание, что при загрузке данных в таблицы с генерируемым столбцом время и накладные расходы могут увеличиться, поскольку Selena необходимо выполнить дополнительные вычисления для расчёта результатов на основе выражения и записать результаты в генерируемый столбец.

Начиная с версии 3.5.0, cluster Selena с разделяемыми данными поддерживают генерируемые столбцы.

Базовые операции​

Создание генерируемых столбцов​

Синтаксис​

<col_name> <data_type> [NULL] AS <expr> [COMMENT 'string']

Создание генерируемых столбцов при создании таблицы​

Создайте таблицу с именем test_tbl1 с пятью столбцами, из которых столбцы newcol1 и newcol2 являются генерируемыми столбцами, значения которых вычисляются с использованием указанных выражений и ссылаются на значения обычных столбцов data_array и data_json соответственно.

CREATE TABLE test_tbl1
(
    id INT NOT NULL,
    data_array ARRAY<int> NOT NULL,
    data_json JSON NOT NULL,
    newcol1 DOUBLE AS array_avg(data_array),
    newcol2 String AS json_string(json_query(data_json, "$.a"))
)
PRIMARY KEY (id)
DISTRIBUTED BY HASH(id);

ПРИМЕЧАНИЕ:

  • Генерируемые столбцы должны быть определены после обычных столбцов.
  • Агрегатные функции не могут использоваться в выражениях для генерируемых столбцов.
  • Выражения для генерируемых столбцов не могут ссылаться на другие генерируемые столбцы или столбцы с автоинкрементом, но выражения могут ссылаться на несколько обычных столбцов.
  • Тип данных генерируемого столбца должен соответствовать типу данных результата, генерируемого выражением для генерируемого столбца.
  • Генерируемые столбцы не могут быть созданы для таблиц Aggregate.

Добавление генерируемых столбцов после создания таблицы​

подсказка

В большинстве случаев часто используемые при запросах выражения определяются после создания таблицы, поэтому генерируемые столбцы часто добавляются после создания таблицы. Для повышения производительности внутренняя логика Selena для добавления генерируемых столбцов после создания таблицы оптимизирована. Таким образом, при добавлении генерируемого столбца Selena не нужно перезаписывать все данные. Вместо этого Selena нужно только записать данные нового генерируемого столбца и связать эти данные с существующими физическими файлами данных, что значительно повышает эффективность добавления генерируемых столбцов после создания таблицы.

  1. Создайте таблицу с именем test_tbl2 с тремя обычными столбцами id, data_array и data_json. Вставьте строку данных в таблицу.

    -- Создание таблицы.
    CREATE TABLE test_tbl2
    (
        id INT NOT NULL,
        data_array ARRAY<int> NOT NULL,
        data_json JSON NOT NULL
    )
    PRIMARY KEY (id)
    DISTRIBUTED BY HASH(id);

    -- Вставка строки данных.
    INSERT INTO test_tbl2 VALUES (1, [1,2], parse_json('{"a" : 1, "b" : 2}'));

    -- Запрос к таблице.
    MySQL [example_db]> select * from test_tbl2;
    +------+------------+------------------+
    | id   | data_array | data_json        |
    +------+------------+------------------+
    |    1 | [1,2]      | {"a": 1, "b": 2} |
    +------+------------+------------------+
    1 row in set (0.04 sec)

  2. Выполните ALTER TABLE ... ADD COLUMN ... для добавления генерируемых столбцов newcol1 и newcol2, которые создаются путём вычисления выражений на основе значений обычных столбцов data_array и data_json.

    ALTER TABLE test_tbl2
    ADD COLUMN newcol1 DOUBLE AS array_avg(data_array);

    ALTER TABLE test_tbl2
    ADD COLUMN newcol2 String AS json_string(json_query(data_json, "$.a"));

    ПРИМЕЧАНИЕ:

    • Добавление генерируемых столбцов в таблицы Aggregate не поддерживается.
    • Обычные столбцы должны быть определены перед генерируемыми столбцами. При использовании оператора ALTER TABLE ... ADD COLUMN ... для добавления обычного столбца без указания его позиции система автоматически размещает его перед генерируемыми столбцами. Кроме того, нельзя использовать AFTER для явного размещения обычного столбца после генерируемого столбца.

  3. Запрос данных таблицы.

    MySQL [example_db]> SELECT * FROM test_tbl2;
    +------+------------+------------------+---------+---------+
    | id   | data_array | data_json        | newcol1 | newcol2 |
    +------+------------+------------------+---------+---------+
    |    1 | [1,2]      | {"a": 1, "b": 2} |     1.5 | 1       |
    +------+------------+------------------+---------+---------+
    1 row in set (0.04 sec)

    Результат показывает, что генерируемые столбцы newcol1 и newcol2 добавлены в таблицу, и Selena автоматически вычислила их значения на основе выражения.

Загрузка данных в генерируемые столбцы​

Во время загрузки данных Selena автоматически вычисляет значения для генерируемых столбцов на основе выражений. Вы не можете указать значения генерируемых столбцов. В следующем примере используется оператор INSERT INTO для загрузки данных:

  1. Используйте INSERT INTO для вставки записи в таблицу test_tbl1. Обратите внимание, что нельзя указывать значения для генерируемых столбцов в предложении VALUES ().

    INSERT INTO test_tbl1 (id, data_array, data_json)
    VALUES (1, [1,2], parse_json('{"a" : 1, "b" : 2}'));

  2. Запрос данных таблицы.

    MySQL [example_db]> SELECT * FROM test_tbl1;
    +------+------------+------------------+---------+---------+
    | id   | data_array | data_json        | newcol1 | newcol2 |
    +------+------------+------------------+---------+---------+
    |    1 | [1,2]      | {"a": 1, "b": 2} |     1.5 | 1       |
    +------+------------+------------------+---------+---------+
    1 row in set (0.01 sec)

    Результаты показывают, что Selena автоматически вычислила значения для генерируемых столбцов newcol1 и newcol2 на основе выражений.

    ПРИМЕЧАНИЕ:

    Следующая ошибка возвращается, если вы указываете значения для генерируемых столбцов во время загрузки данных:

    MySQL [example_db]> INSERT INTO test_tbl1 (id, data_array, data_json, newcol1, newcol2)
    VALUES (2, [3,4], parse_json('{"a" : 3, "b" : 4}'), 3.5, "3");
    ERROR 1064 (HY000): Getting analyzing error. Detail message: materialized column 'newcol1' can not be specified.

    MySQL [example_db]> INSERT INTO test_tbl1 VALUES (2, [3,4], parse_json('{"a" : 3, "b" : 4}'), 3.5, "3");
    ERROR 1064 (HY000): Getting analyzing error. Detail message: Column count doesn't match value count.

Изменение генерируемых столбцов​

подсказка

При изменении генерируемого столбца Selena необходимо перезаписать все данные, что требует много времени и ресурсов. Если использование ALTER TABLE для изменения генерируемых столбцов неизбежно, рекомендуется заранее оценить затраты и время.

Вы можете изменить тип данных и выражение генерируемого столбца.

  1. Создайте таблицу test_tbl3 с пятью столбцами, из которых столбцы newcol1 и newcol2 являются генерируемыми столбцами, значения которых вычисляются с использованием указанных выражений и ссылаются на значения обычных столбцов data_array и data_json соответственно. Вставьте строку данных в таблицу.

    -- Создание таблицы.
    MySQL [example_db]> CREATE TABLE test_tbl3
    (
        id INT NOT NULL,
        data_array ARRAY<int> NOT NULL,
        data_json JSON NOT NULL,
        -- Типы данных и выражения генерируемых столбцов указаны следующим образом:
        newcol1 DOUBLE AS array_avg(data_array),
        newcol2 String AS json_string(json_query(data_json, "$.a"))
    )
    PRIMARY KEY (id)
    DISTRIBUTED BY HASH(id);

    -- Вставка строки данных.
    INSERT INTO test_tbl3 (id, data_array, data_json)
    VALUES (1, [1,2], parse_json('{"a" : 1, "b" : 2}'));

    -- Запрос к таблице.
    MySQL [example_db]> select * from test_tbl3;
    +------+------------+------------------+---------+---------+
    | id   | data_array | data_json        | newcol1 | newcol2 |
    +------+------------+------------------+---------+---------+
    |    1 | [1,2]      | {"a": 1, "b": 2} |     1.5 | 1       |
    +------+------------+------------------+---------+---------+
    1 row in set (0.01 sec)

  2. Изменение генерируемых столбцов newcol1 и newcol2:

    • Изменение типа данных генерируемого столбца newcol1 на ARRAY<INT> и изменение его выражения на data_array.

      ALTER TABLE test_tbl3
      MODIFY COLUMN newcol1 ARRAY<INT> AS data_array;

    • Изменение выражения генерируемого столбца newcol2 для извлечения значений поля b из обычного столбца data_json.

      ALTER TABLE test_tbl3
      MODIFY COLUMN newcol2 String AS json_string(json_query(data_json, "$.b"));

  3. Просмотр изменённой схемы и данных в таблице.

    • Просмотр изменённой схемы.

      MySQL [example_db]> show create table test_tbl3\G
      **** 1. row ****
          Table: test_tbl3
      Create Table: CREATE TABLE test_tbl3 (
      id int(11) NOT NULL COMMENT "",
      data_array array<int(11)> NOT NULL COMMENT "",
      data_json json NOT NULL COMMENT "",
      -- После изменения типы данных и выражения генерируемых столбцов следующие:
      newcol1 array<int(11)> NULL AS example_db.test_tbl3.data_array COMMENT "",
      newcol2 varchar(65533) NULL AS json_string(json_query(example_db.test_tbl3.data_json, '$.b')) COMMENT ""
      ) ENGINE=OLAP
      PRIMARY KEY(id)
      DISTRIBUTED BY HASH(id)
      PROPERTIES (...);
      1 row in set (0.00 sec)

    • Запрос данных таблицы после изменения. Результат показывает, что Selena пересчитала значения генерируемых столбцов newcol1 и newcol2 на основе изменённых выражений.

      MySQL [example_db]> select * from test_tbl3;
      +------+------------+------------------+---------+---------+
      | id   | data_array | data_json        | newcol1 | newcol2 |
      +------+------------+------------------+---------+---------+
      |    1 | [1,2]      | {"a": 1, "b": 2} | [1,2]   | 2       |
      +------+------------+------------------+---------+---------+
      1 row in set (0.01 sec)

Удаление генерируемого столбца​

Удаление столбца newcol1 из таблицы test_tbl3

ALTER TABLE test_tbl3 DROP COLUMN newcol1;

ПРИМЕЧАНИЕ:

Если генерируемый столбец ссылается на обычный столбец в выражении, вы не можете напрямую удалить или изменить этот обычный столбец. Вместо этого вам нужно сначала удалить генерируемый столбец, а затем удалить или изменить обычный столбец.

Перезапись запросов​

Если выражение в запросе совпадает с выражением генерируемого столбца, оптимизатор автоматически перезаписывает запрос для прямого чтения значений генерируемого столбца.

  1. Предположим, что вы создаёте таблицу test_tbl4 со следующей схемой:

    CREATE TABLE test_tbl4
    (
        id INT NOT NULL,
        data_array ARRAY<int> NOT NULL,
        data_json JSON NOT NULL,
        newcol1 DOUBLE AS array_avg(data_array),
        newcol2 String AS json_string(json_query(data_json, "$.a"))
    )
    PRIMARY KEY (id) DISTRIBUTED BY HASH(id);

  2. Если вы запрашиваете данные в таблице test_tbl4 с помощью оператора SELECT array_avg(data_array), json_string(json_query(data_json, "$.a")) FROM test_tbl4;, запрос включает только обычные столбцы data_array и data_json. Однако выражения в запросе совпадают с выражениями генерируемых столбцов newcol1 и newcol2. В этом случае план выполнения показывает, что CBO автоматически перезаписывает запрос для чтения значений генерируемых столбцов newcol1 и newcol2.

    MySQL [example_db]> EXPLAIN SELECT array_avg(data_array), json_string(json_query(data_json, "$.a")) FROM test_tbl4;
    +---------------------------------------+
    | Explain String                        |
    +---------------------------------------+
    | PLAN FRAGMENT 0                       |
    |  OUTPUT EXPRS:4: newcol1 | 5: newcol2 | -- Запрос перезаписан для чтения данных из генерируемых столбцов newcol1 и newcol2.
    |   PARTITION: RANDOM                   |
    |                                       |
    |   RESULT SINK                         |
    |                                       |
    |   0:OlapScanNode                      |
    |      TABLE: test_tbl4                 |
    |      PREAGGREGATION: ON               |
    |      partitions=0/1                   |
    |      rollup: test_tbl4                |
    |      tabletRatio=0/0                  |
    |      tabletList=                      |
    |      cardinality=1                    |
    |      avgRowSize=2.0                   |
    +---------------------------------------+
    15 rows in set (0.00 sec)

Частичные обновления и генерируемые столбцы​

Для выполнения частичных обновлений в таблице с первичным ключом необходимо указать все обычные столбцы, на которые ссылаются генерируемые столбцы, в параметре columns. В следующем примере используется Stream Load для выполнения частичных обновлений.

  1. Создайте таблицу test_tbl5 с пятью столбцами, из которых столбцы newcol1 и newcol2 являются генерируемыми столбцами, значения которых вычисляются с использованием указанных выражений и ссылаются на значения обычных столбцов data_array и data_json соответственно. Вставьте строку данных в таблицу.

    -- Создание таблицы.
    CREATE TABLE test_tbl5
    (
        id INT NOT NULL,
        data_array ARRAY<int> NOT NULL,
        data_json JSON NULL,
        newcol1 DOUBLE AS array_avg(data_array),
        newcol2 String AS json_string(json_query(data_json, "$.a"))
    )
    PRIMARY KEY (id)
    DISTRIBUTED BY HASH(id);

    -- Вставка строки данных.
    INSERT INTO test_tbl5 (id, data_array, data_json)
    VALUES (1, [1,2], parse_json('{"a" : 1, "b" : 2}'));

    -- Запрос к таблице.
    MySQL [example_db]> select * from test_tbl5;
    +------+------------+------------------+---------+---------+
    | id   | data_array | data_json        | newcol1 | newcol2 |
    +------+------------+------------------+---------+---------+
    |    1 | [1,2]      | {"a": 1, "b": 2} |     1.5 | 1       |
    +------+------------+------------------+---------+---------+
    1 row in set (0.01 sec)

  2. Подготовьте CSV-файл my_data1.csv для обновления некоторых столбцов в таблице test_tbl5.

    1|[3,4]|{"a": 3, "b": 4}
    2|[3,4]|{"a": 3, "b": 4}

  3. Используйте Stream Load с файлом my_data1.csv для обновления некоторых столбцов таблицы test_tbl5. Необходимо установить partial_update:true и указать все обычные столбцы, на которые ссылаются генерируемые столбцы, в параметре columns.

    curl --location-trusted -u <username>:<password> -H "label:1" \
        -H "column_separator:|" \
        -H "partial_update:true" \
        -H "columns:id,data_array,data_json" \
        -T my_data1.csv -XPUT \
        http://<fe_host>:<fe_http_port>/api/example_db/test_tbl5/_stream_load

  4. Запрос данных таблицы.

    [example_db]> select * from test_tbl5;
    +------+------------+------------------+---------+---------+
    | id   | data_array | data_json        | newcol1 | newcol2 |
    +------+------------+------------------+---------+---------+
    |    1 | [3,4]      | {"a": 3, "b": 4} |     3.5 | 3       |
    |    2 | [3,4]      | {"a": 3, "b": 4} |     3.5 | 3       |
    +------+------------+------------------+---------+---------+
    2 rows in set (0.01 sec)

Ошибка возвращается Stream Load, если вы выполняете частичные обновления без указания всех обычных столбцов, на которые ссылаются генерируемые столбцы.

  1. Подготовьте CSV-файл my_data2.csv.

    1|[3,4]
    2|[3,4]

  2. При выполнении частичных обновлений столбцов с помощью Stream Load с файлом my_data2.csv, если значения для столбца data_json не предоставлены в my_data2.csv и параметр columns в задаче Stream Load не включает столбец data_json, даже если столбец data_json допускает нулевые значения, Stream Load возвращает ошибку, поскольку на столбец data_json ссылается генерируемый столбец newcol2.