生成列
v3.1以降、StarRocksは生成列をサポートしています。生成列は、複雑な式を含むクエリを高速化するために使用できます。この機能は、式の結果を事前に計算して保存し、クエリの書き換えをサポートすることで、同じ複雑な式を含むクエリを大幅に高速化します。
テーブル作成時に、式の結果を保存するための1つ以上の生成列を定義できます。このようにして、定義した生成列に結果が保存されている式を含むクエリを実行する際、CBOはクエリを生成列から直接データを読み取るように書き換えます。あるいは、生成列のデータを直接クエリすることもできます。
また、生成列がロードパフォーマンスに与える影響を評価することをお勧めします。なぜなら、式の計算には時間がかかるからです。さらに、テーブル作成時に生成列を作成することをお勧めします。テーブル作成後に生成列を追加または変更するのは時間とコストがかかるため です。
ただし、生成列を持つテーブルにデータがロードされると、StarRocksが式に基づいて結果を計算し、生成列に結果を書き込むため、時間とオーバーヘッドが増加する可能性があることに注意してください。
現在、StarRocksの共有データモードは生成列をサポートしていません。
基本操作
生成列を作成する
構文
<col_name> <data_type> [NULL] AS <expr> [COMMENT 'string']
テーブル作成時に生成列を作成する
5つの列を持つテーブル 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);
注意:
- 生成列は通常の列の後に定義する必要があります。
- 集計関数は生成列の式で使用できません。
- 生成列の式は他の生成列や自動インクリメント列を参照できませんが、複数の通常の列を参照することができます。
- 生成列のデータ型は、生成列の式によって生成される結果のデータ型と一致している必要があります。
- 集計テーブルに生成列を作成することはできません。
テーブル作成後に生成列を追加する
ほとんどの場合、クエリ中に頻繁に使用される式はテーブル作成後に決定されるため、生成列はテーブル作成後に追加されることが多いです。パフォーマンスのために、StarRocksがテーブル作成後に生成列を追加するための基盤となるロジックは最適化されています。このため、生成列を追加する際に、StarRocksはすべてのデータを書き換える必要はありません。代わりに、新しく追加された生成列のデータを書き込み、そのデータを既存の物理データファイルと関連付けるだけで済みます。これにより、テーブル作成後に生成列を追加する効率が大幅に向上します。
-
通常の列
id、data_array、data_jsonを持つテーブルtest_tbl2を作成し、データ行をテーブルに挿入します。-- テーブルを作成します。
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) -
ALTER TABLE ... ADD COLUMN ...を実行して、通常の列data_arrayとdata_jsonの値に基づいて式を評価することで作成される生成列newcol1とnewcol2を追加します。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"));注意:
- 集計テーブルに生成列を追加することはサポートされていません。
- 通常の列は生成列の前に定義する必要があります。
ALTER TABLE ... ADD COLUMN ...ステートメン トを使用して通常の列を追加する際に、新しい通常の列の位置を指定しない場合、システムは自動的にそれを生成列の前に配置します。さらに、AFTER を使用して通常の列を生成列の後に明示的に配置することはできません。
-
テーブルデータをクエリします。
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がテーブルに追加され、StarRocksが式に基づいてその値を自動的に計算することを示しています。
生成列にデータをロードする
データロード中、StarRocksは式に基づいて生成列の値を自動的に計算します。生成列の値を指定することはできません。以下の例では、INSERT INTO ステートメントを使用してデータをロードします。
-
INSERT INTOを使用してtest_tbl1テーブルにレコードを挿入します。VALUES ()節内で生成列の値を指定することはできません。INSERT INTO test_tbl1 (id, data_array, data_json)
VALUES (1, [1,2], parse_json('{"a" : 1, "b" : 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)結果は、StarRocksが式に基づいて生成列
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.
生成列を修正する
生成列を修正する際、StarRocksはすべてのデータを書き換える必要があり、時間とリソースを消費します。ALTER TABLE を使用して生成列を修正することが避けられない場合は、事前にコストと時間を評価することをお勧めします。
生成列のデータ型と式を修正できます。
-
通常の列
data_arrayとdata_jsonの値を参照して指定された式を使用して計算される生成列newcol1とnewcol2を持つ5つの列を持つテーブルtest_tbl3を作成し、データ行をテーブルに挿入します。-- テーブルを作成します。
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) -
生成列
newcol1とnewcol2を修正します:-
生成列
newcol1のデータ型をARRAY<INT>に変更し、その式をdata_arrayに変更します。ALTER TABLE test_tbl3
MODIFY COLUMN newcol1 ARRAY<INT> AS data_array; -
生成列
newcol2の式を通常の列data_jsonのフィールドbの値を抽出するように修正します。ALTER TABLE test_tbl3
MODIFY COLUMN newcol2 String AS json_string(json_query(data_json, "$.b"));
-
-
修正後のスキーマとテーブル内のデータを確認します。
-
修正後のスキーマを確認します。
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) -
修正後のテーブルデータをクエリします。結果は、StarRocksが修正された式に基づいて生成列
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)
-
生成列を削除する
テーブル test_tbl3 から列 newcol1 を削除します
ALTER TABLE test_tbl3 DROP COLUMN newcol1;
注意:
生成列が式で通常の列を参照している場合、その通常の列を直接削除または修正することはできません。代わりに、まず生成列を削除し、その後に通常の列を削除または修正する必要があります。
クエリの書き換え
クエリ内の式が生成列の式と一致する場合、オプティマイザはクエリを自動的に書き換えて生成列の値を直接読み取ります。
-
次のスキーマを持つテーブル
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); -
SELECT array_avg(data_array), json_string(json_query(data_json, "$.a")) FROM test_tbl4;ステートメントを使用してテーブル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 を使用して部分更新を行います。
-
通常の列
data_arrayとdata_jsonの値を参照して指定された式を使用して計算される生成列newcol1とnewcol2を持つ5つの列を持つテーブルtest_tbl5を作成し、データ行をテーブルに挿入します。-- テーブルを作成します。
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) -
my_data1.csvというCSVファイルを準備し、test_tbl5テーブルのいくつかの列を更新します。1|[3,4]|{"a": 3, "b": 4}
2|[3,4]|{"a": 3, "b": 4} -
my_data1.csvファイルを使用して Stream Load を使用して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 -
テーブルデータをクエリします。
[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 によってエラーが返されます。
-
my_data2.csvというCSVファイルを準備します。1|[3,4]
2|[3,4] -
my_data2.csvファイルを使用して Stream Load で部分列更新を行う場合、my_data2.csvでdata_json列の値が提供されておらず、Stream Load ジョブのcolumnsパラメータにdata_json列が含まれていない場合、たとえdata_json列がNULL値を許可していても、生成列newcol2がdata_json列を参照しているため、Stream Load によってエラーが返されます。