SQLクエリで検索インデックスの仮想カラムを使用する方法 - Tablestore

検索インデックスのスキーマを変更する場合、または検索インデックスを作成する場合、仮想カラムを設定して新しいフィールドまたは新しいデータ型を作成できます。このようにして、Tablestore に格納されている元のテーブルのスキーマまたはデータを変更することなく、これらの新しいフィールドまたは新しいデータ型のフィールドをクエリできます。

説明

検索インデックスの仮想カラムの詳細については、仮想カラムを参照してください。

使用上の注意

SQLステートメントを実行して、検索インデックス内の GEOPOINT フィールドのデータをクエリすることはできません。
SQLステートメントを実行して、ARRAY型の仮想カラムをクエリすることはできません。

データ型マッピング

テーブル内のデータ型	検索インデックス内の仮想カラムのデータ型	SQLでのデータ型
検索インデックスの仮想カラムは、実際にはテーブル内に存在しません。仮想カラムには、テーブルに対応するソースカラムがあります。検索インデックスとデータテーブルのデータ型間のマッピングの詳細については、基本データ型のマッピングを参照してください。	KEYWORD	MEDIUMTEXT
	TEXT	MEDIUMTEXT
	LONG	BIGINT
	DOUBLE	DOUBLE

検索インデックスのマッピングテーブルを作成する

SQLステートメントを実行して検索インデックスの仮想カラムをクエリする場合は、検索インデックスのマッピングテーブルを作成する必要があります。詳細については、検索インデックスのマッピングテーブルを作成するを参照してください。

CREATE TABLE ステートメントでは、仮想カラムの有効な名前とデータ型を指定する必要があります。

SQLステートメントの例:

CREATE TABLE `test_table__test_table_index`(
    `id` MEDIUMTEXT,
    `col_keyword` MEDIUMTEXT,
    `col_keyword_virtual_long` BIGINT
)
ENGINE='searchindex',
ENGINE_ATTRIBUTE='{"index_name":"test_table_index","table_name":"test_table"}';

SQLステートメントを実行してデータをクエリする

SQLステートメントを実行してデータをクエリする場合、次の方法で仮想カラムを使用できます。

SELECTステートメントでWHERE句とともに仮想カラムを使用して、指定された条件を満たすデータをクエリできます。
クエリ条件で仮想カラムを使用する場合、仮想カラムはクエリ条件のパラメータと同じデータ型である必要があります。
集計関数と仮想カラムを一緒に使用して、データを分析できます。仮想カラムのソースカラムのデータ型は、検索インデックスでの集計操作の要件を満たしている必要があります。たとえば、検索インデックスの列の値の合計を取得する場合、列は LONG 型または DOUBLE 型である必要があります。検索インデックスの仮想カラムのソースカラムが KEYWORD 型の場合、仮想カラムの値の合計を取得することはできません。
仮想カラムに基づいて結果をグループ化できます。仮想カラムのソースカラムのデータ型は、検索インデックスでのグループ化操作の要件を満たしている必要があります。たとえば、検索インデックスの TEXT 型のデータをグループ化することはできません。したがって、TEXT 型の仮想カラムの値はグループ化できません。
SQLステートメントを実行して、仮想カラムに基づいて上位n件のクエリ結果を返すことができます。
仮想カラムに基づいてデータをソートできます。ただし、仮想カラムに基づいてデータをソートする場合、LIMIT句を使用する必要があります。LIMIT句なしで仮想カラムに基づいてデータをソートすることはできません。

SQLステートメントを実行して仮想カラムに基づいてデータをクエリする場合、次の制限事項に注意してください。

検索インデックスで仮想カラムを使用するには、検索インデックスのマッピングテーブルを作成する必要があります。
仮想カラムはSQLクエリステートメントでのみ使用できます。ただし、SELECTステートメントは仮想カラムの値を直接返すことはできません。仮想カラムの値を返す場合は、SELECTステートメントで仮想カラムのソースカラムを指定します。たとえば、SELECT * ステートメントを実行して、column_virtual 仮想カラムを含む検索インデックスのマッピングテーブルに基づいてデータをクエリできます。ただし、仮想カラムはクエリ結果で自動的に無視されます。マッピングテーブルで SELECT column_virtual ステートメントを実行することはできません。
仮想カラム間で比較、計算、または結合操作を実行することはできません。
仮想カラムで型キャストを含む計算を実行することはできません。また、検索インデックスでサポートされていない関数で仮想カラムを使用することはできません。検索インデックスは、SQLクエリで集計関数のみをサポートしています。

例

この例では、データテーブルの名前は test_table です。テーブルには、LONG型の id カラムとSTRING型の col_keyword カラムが含まれています。

SQLステートメントを実行して仮想カラムに基づいてデータテーブルのデータをクエリする場合は、データテーブルの検索インデックスを作成し、次に検索インデックスのマッピングテーブルを作成する必要があります。仮想カラムを使用するには、次の手順を実行します。

テーブルの検索インデックスを作成し、検索インデックスを作成するときに仮想カラムを指定します。詳細については、メソッドまたは仮想カラムを参照してください。
説明
使用する検索インデックスで仮想カラムが指定されていない場合は、検索インデックスのスキーマを変更して仮想カラムを追加できます。詳細については、検索インデックスのスキーマを動的に変更するを参照してください。
この例では、検索インデックスの名前は test_table_index です。検索インデックスには、String型の id カラム、KEYWORD型の col_keyword カラム、およびLONG型の col_keyword_virtual_long 仮想カラムが含まれています。仮想カラムのソースカラムは col_keyword カラムです。
次の図は、Tablestoreコンソールで検索インデックスを設定する方法を示しています。
次の図は、検索インデックスのスキーマを示しています。
検索インデックスのマッピングテーブルを作成します。詳細については、TablestoreコンソールでSQLクエリ機能を使用するまたは Tablestore SDKを使用してSQLクエリ機能を使用するを参照してください。
この例では、検索インデックスのマッピングテーブルの名前は test_table__test_table_index です。マッピングテーブルのLONG型の col_keyword_virtual_long 仮想カラムのSQLデータ型はBIGINTです。詳細については、検索インデックスのマッピングテーブルを作成するを参照してください。
SQLステートメントの例:
```
CREATE TABLE `test_table__test_table_index`(
    `id` MEDIUMTEXT,
    `col_keyword` MEDIUMTEXT,
    `col_keyword_virtual_long` BIGINT
)
ENGINE='searchindex',
ENGINE_ATTRIBUTE='{"index_name":"test_table_index","table_name":"test_table"}';
```
検索インデックスのマッピングテーブルを作成した後、次のステートメントを実行してマッピングテーブルからデータをクエリします。
```
SELECT * FROM `test_table__test_table_index`;
```
次の図は、クエリ結果を示しています。この例では、test_table__test_table_index マッピングテーブルには10個のデータレコードが含まれています。
SELECTステートメントを実行してデータをクエリします。詳細については、データのクエリを参照してください。
- 次の例は、col_keyword_virtual_long 仮想カラムで50未満のすべての値の平均値を取得する方法を示しています。
```
SELECT AVG(col_keyword_virtual_long) FROM `test_table__test_table_index` WHERE col_keyword_virtual_long < 50; 
```
  col_keyword_virtual_long 仮想カラムには、値が50未満の4つのデータレコード（10、20、30、40）があります。計算された平均値は 25.0000 です。次の図は、クエリ結果を示しています。
- 次の例は、col_keyword_virtual_long 仮想カラムに基づいて他のカラムを降順にソートし、上位5つのデータレコードを返す方法を示しています。
```
SELECT * FROM `test_table__test_table_index` ORDER BY col_keyword_virtual_long DESC LIMIT 5;
```
  次の図は、クエリ結果を示しています。