SSTable Attached Secondary Index（SASI）の使用

CQLを使用して、テーブルを定義した後にカラムのSSTable Attached Secondary Index（SASI）を作成します。

セカンダリ・インデックスであるSSTable Attached Secondary Index（SASI）はセカンダリ・インデックスのパフォーマンスを向上したものですが、使用する際は注意が必要です。

注: DSEにおけるSASIインデックスの使用は試験段階です。DataStaxでは、実稼働環境ではSASIインデックスをサポートしていません。

CQLを使用すると、テーブルで定義されている非コレクション・カラムに対してSSTable Attached Secondary Index（SASI）を作成できます。セカンダリ・インデックスは、非プライマリ・キー・カラムなど、通常はクエリーできないカラムを使用しているテーブルをクエリーするときに使用します。SASIは、PREFIX、CONTAINS、およびSPARSEの3種類のインデックスを実装します。

手順

PREFIXインデックス

テーブルcyclist_nameのカラムfirstnameに対してインデックスfn_prefixを作成します。PREFIXはデフォルト・モードであるため、指定する必要はありません。
```
CREATE TABLE cycling.cyclist_name ( 
  id UUID PRIMARY KEY, 
  lastname text, 
  firstname text
);

CREATE CUSTOM INDEX  fn_prefix ON cyclist_name (firstname) USING 'org.apache.cassandra.index.sasi.SASIIndex';
```
図 1. SELECT * FROM cycling.cyclist_name;
クエリーで、firstnameの値の完全一致を検出できます。プライマリ・キーidが指定されていないため、このクエリーにはインデックスが使用されます。
```
SELECT * FROM cyclist_name WHERE firstname = 'Marianne';
```
クエリーで、部分一致に基づいてfirstnameの値の一致を検出できます。LIKEを使用して、文字「M」で始まる単語を検索するように指定します。文字「M」の後に%を指定すると、任意の複数の文字に一致し、一致した値を返します。プライマリ・キーidが指定されていないため、このクエリーにはインデックスが使用されます。
```
SELECT * FROM cyclist_name WHERE firstname LIKE 'M%';
```
部分文字列に基づく一致を検出できないクエリーはたくさんあります。次のクエリーはすべて失敗します。
```
SELECT * FROM cyclist_name WHERE firstname = 'MARIANNE';
SELECT * FROM cyclist_name WHERE firstname LIKE 'm%';
SELECT * FROM cyclist_name WHERE firstname LIKE '%m%';
SELECT * FROM cyclist_name WHERE firstname LIKE '%m%' ALLOW FILTERING;
SELECT * FROM cyclist_name WHERE firstname LIKE '%M%';
SELECT * FROM cyclist_name WHERE firstname = 'M%';
SELECT * FROM cyclist_name WHERE firstname = '%M';
SELECT * FROM cyclist_name WHERE firstname = '%M%';
SELECT * FROM cyclist_name WHERE firstname = 'm%';
```
最初の4つのクエリーは、大文字と小文字の違いにより失敗します。「MARIANNE」はすべて大文字ですが、格納されている値は違います。次の3つのクエリーは小文字の「m」を使用しています。%の位置は重要です。インデックスはPREFIXモードを指定しているため、LIKEと組み合わせるときは後ろに%を付けた場合にのみ一致を検出できます。等価を使ったクエリーは、完全一致を指定しないと失敗します。

CONTAINSインデックス

テーブルcyclist_nameのカラムfirstnameに対してインデックスfn_suffixを作成します。プレフィックスだけでなく、指定された部分パターンに対するパターン照合を行うには、CONTAINSモードを指定します。
```
CREATE CUSTOM INDEX fn_contains ON cyclist_name (firstname) USING 'org.apache.cassandra.index.sasi.SASIIndex'
WITH OPTIONS = { 'mode': 'CONTAINS' };
```
クエリーで、firstnameの値の完全一致を検出できます。プライマリ・キーidが指定されていないため、このクエリーにはインデックスが使用されます。CONTAINSインデックスのクエリーでは、ALLOW FILTERING句を含める必要がありますが、データベースでは実際にはフィルター処理は行われません。
```
SELECT * FROM cyclist_name WHERE firstname = 'Marianne' ALLOW FILTERING;
```
このクエリーは、PREFIXインデックスを使用したクエリーと同じ結果を返し、クエリーを多少変更することで完全一致を検出します。
クエリーで、部分一致に基づいてfirstnameの値の一致を検出できます。LIKEを使用して、文字「M」を含む単語を検索するように指定します。文字「M」の前後に%を指定すると、任意の複数の文字に一致し、一致した値を返します。プライマリ・キーidが指定されていないため、このクエリーにはインデックスが使用されます。
```
SELECT * FROM cyclist_name WHERE firstname LIKE '%M%';
```
ここでも、クエリーを多少変更してPREFIXインデックスの場合と同じ結果が得られます。
CONTAINSインデックスの照合アルゴリズムは、PREFIXよりも汎用性に優れています。以下の例で、前の検索のバリエーションから得られる結果を見てみましょう。
```
SELECT * FROM cyclist_name WHERE firstname LIKE '%arianne';
SELECT * FROM cyclist_name WHERE firstname LIKE '%arian%';
```
各クエリーは、%arianneのようにカラム値の最後の文字、または%arian%のように%で囲まれた文字のパターンを照合します。
CONTAINSインデックスでは、不等価パターン照合も可能です。ここでもALLOW FILTERING句を使用する必要がありますが、クエリーの応答にレイテンシーは発生しません。
```
SELECT * FROM cyclist_name WHERE firstname > 'Mar' ALLOW FILTERING;
```
条件に一致する唯一の行が前のクエリーと同じ値を返します。
PREFIXインデックスと同様に、部分文字列に基づく一致を検出できないクエリーはたくさんあります。次のクエリーはすべて失敗します。
```
SELECT * FROM cyclist_name WHERE firstname = 'Marianne';
SELECT * FROM cyclist_name WHERE firstname = 'MariAnne' ALLOW FILTERING;
SELECT * FROM cyclist_name WHERE firstname LIKE '%m%';
SELECT * FROM cyclist_name WHERE firstname LIKE 'M%';
SELECT * FROM cyclist_name WHERE firstname LIKE '%M';
SELECT * FROM cyclist_name WHERE firstname LIKE 'm%';
```
最初のクエリーは、ALLOW FILTERING句がないために失敗します。次の2つのクエリーは、大文字と小文字の違いにより失敗します。「MariAnne」には大文字が1文字含まれていますが、格納されている値は違います。最後の3つは%の位置が原因で失敗します。
PREFIXインデックスとCONTAINSインデックスは、アナライザー・クラスとcase_sensitiveオプションを追加することで、大文字と小文字を区別して作成できます。
```
CREATE CUSTOM INDEX fn_suffix_allcase ON cyclist_name (firstname) USING 'org.apache.cassandra.index.sasi.SASIIndex'
WITH OPTIONS = { 
'mode': 'CONTAINS',
'analyzer_class': 'org.apache.cassandra.index.sasi.analyzer.NonTokenizingAnalyzer',
'case_sensitive': 'false'
};
```
ここで使用されているanalyzer_classは非トークン化アナライザーであり、指定したカラム内のテキストに対して分析を実行しません。オプションcase_sensitiveは、インデックスの大文字と小文字を区別しないようにするために、falseに設定されています。
アナライザー・クラスとオプションを追加すると、小文字「m」を使用した次のクエリーも機能するようになります。
```
SELECT * FROM cyclist_name WHERE firstname LIKE '%m%';
```
インデックス付きカラム値を使用してクエリー対象を狭めると、インデックスのないカラムを指定できます。複数のインデックス付きカラムを使用して複合クエリーを作成することもできます。次の例では、クエリーの実行前にインデックスが作成されないカラムageを追加するようにテーブルを変更します。
```
ALTER TABLE cyclist_name ADD age int;
UPDATE cyclist_name SET age=23 WHERE id=5b6962dd-3f90-4c93-8f61-eabfa4a803e2;
INSERT INTO cyclist_name (id,age,firstname,lastname) VALUES (8566eb59-07df-43b1-a21b-666a3c08c08a,18,'Marianne','DAAE');
SELECT * FROM cyclist_name WHERE firstname='Marianne' and age > 20 allow filtering;
```

SPARSEインデックス

SPARSEインデックスは、ミリ秒ごとにデータが挿入されるタイムスタンプなど、大きく密度の高い数値範囲に対するクエリーのパフォーマンスを改善することを目的としています。データが数値で、少数のパーティション・キーを持つ数百万のカラム値があり、インデックスに対して範囲クエリーを実行する場合、SPARSEの使用が適しています。この条件を満たさない数値データに対しては、PREFIXが適しています。
```
CREATE CUSTOM INDEX fn_contains ON cyclist_name (age) USING 'org.apache.cassandra.index.sasi.SASIIndex'
WITH OPTIONS = { 'mode': 'SPARSE' };
```
各用語/カラム値の照合キーが5つ未満である疎のデータに対しては、SPARSEインデックスを使用します。通常、created_atタイムスタンプごとにいくつかの一致する行/イベントが存在するなど、時系列データのcreated_atフィールドのインデックスの作成は、ユース・ケースとして適しています。SPARSEインデックスは主に範囲クエリーを最適化し、特に長期にわたる大きな範囲に適しています。

SPARSEインデックスの使用法を示すために、テーブルを作成し、時系列データを挿入します。

CREATE TABLE cycling.comments (commenter text, created_at timestamp, comment text, PRIMARY KEY (commenter));
INSERT INTO cycling.comments (commenter, comment, created_at) VALUES ('John', 'Fantastic race!', '2013-01-01 00:05:01.500');
INSERT INTO cycling.comments (commenter, comment, created_at) VALUES ('Jane', 'What a finish', '2013-01-01 00:05:01.400');
INSERT INTO cycling.comments (commenter, comment, created_at) VALUES ('Mary', 'Hated to see the race end.', '2013-01-01 00:05:01.300');
INSERT INTO cycling.comments (commenter, comment, created_at) VALUES ('Jonnie', 'Thankfully, it is over.', '2013-01-01 00:05:01.600');

タイムスタンプ2013-01-01 00:05:01.500より前に作成されたすべてのコメントを検出します。
```
SELECT * FROM cycling.comments WHERE created_at < '2013-01-01 00:05:01.500';
```
このクエリーは、created_atが指定したタイムスタンプより前の日時である結果をすべて返します。不等価>=、>、および<=はいずれも演算子として有効です。

SPARSEインデックスは数値データに対してのみ使用されるので、LIKEクエリーは使用しません。

アナライザーの使用

アナライザーは、指定したカラム内のテキストを分析するために指定します。NonTokenizingAnalyzerは、テキストを分析するのではなく、大文字と小文字の正規化または区別が必要な場合に使用します。StandardAnalyzerは、ステミング、大文字と小文字の正規化または区別、「and」や「the」などの一般的な単語のスキップ、および分析用言語のローカライズに関する分析に使用します。再びテーブルを変更してさらに長いテキスト・カラムを追加して、分析を確認します。

ALTER TABLE cyclist_name ADD comments text;
UPDATE cyclist_name SET comments ='Rides hard, gets along with others, a real winner' WHERE id = fb372533-eb95-4bb4-8685-6ef61e994caa;
UPDATE cyclist_name SET comments ='Rides fast, does not get along with others, a real dude' WHERE id = 5b6962dd-3f90-4c93-8f61-eabfa4a803e2;

CREATE CUSTOM INDEX stdanalyzer_idx ON cyclist_name (comments) USING 'org.apache.cassandra.index.sasi.SASIIndex'
WITH OPTIONS = {
'mode': 'CONTAINS',
'analyzer_class': 'org.apache.cassandra.index.sasi.analyzer.StandardAnalyzer',
'analyzed': 'true',
'tokenization_skip_stop_words': 'and, the, or',
'tokenization_enable_stemming': 'true',
'tokenization_normalize_lowercase': 'true',
'tokenization_locale': 'en'
};

このクエリーは、結果を返すために分析されたテキストを使用して、指定した文字列が存在するかどうかを検索します。
```
SELECT * FROM cyclist_name WHERE comments LIKE 'ride';
```
このクエリーは、rideが完全一致の単語、または別の単語（この場合はrides）の語幹として検出される結果をすべて返します。