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

Вычисляемые столбцы

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

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

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

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

В настоящее время режим shared-data в 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)

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

Для выполнения частичных обновлений в таблице Primary Key вы должны указать все обычные столбцы, на которые ссылаются вычисляемые столбцы, в параметре 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 допускает null значения, Stream Load возвращает ошибку, поскольку столбец data_json используется вычисляемым столбцом newcol2.