CQLクイック・リファレンス

ALTER MATERIALIZED VIEW cycling.cyclist_by_age 
WITH compression = { 
  'sstable_compression' : 'DeflateCompressor', 
  'chunk_length_kb' : 64 }
AND compaction = {
  'class': 'SizeTieredCompactionStrategy', 
  'max_threshold': 64};

ALTER MATERIALIZED VIEW cycling.cyclist_by_age 
WITH caching = { 
  'keys' : 'NONE', 
  'rows_per_partition' : '15' };

DESCRIBE MATERIALIZED VIEW cycling.cyclist_by_age

CREATE MATERIALIZED VIEW cycling.cyclist_by_age AS
    SELECT age, cid, birthday, country, name
    FROM cycling.cyclist_base
    WHERE age IS NOT NULL AND cid IS NOT NULL
    PRIMARY KEY (age, cid)
    WITH CLUSTERING ORDER BY (cid ASC)
    AND bloom_filter_fp_chance = 0.02
    AND caching = {'keys': 'NONE', 'rows_per_partition': '15'}
    AND comment = 'A most excellent and useful view'
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '64', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.DeflateCompressor'}
    AND crc_check_chance = 1.0
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND speculative_retry = '99PERCENTILE';

1. 構成スキーマを変更します。

   ALTER SEARCH INDEX CONFIG ON cycling.comments 
   SET directoryFactory = 'encrypted';

2. 保留中のスキーマでこの変更が適切であることを確認します。

   DESC PENDING SEARCH INDEX CONFIG ON cycling.comments

3. 構成をアクティブにします。

   <?xml version="1.0" encoding="UTF-8" standalone="no"?>
   <config>
     <luceneMatchVersion>LUCENE_6_0_1</luceneMatchVersion>
     <dseTypeMappingVersion>2</dseTypeMappingVersion>
     <directoryFactory class="solr.EncryptedFSDirectoryFactory" name="DirectoryFactory"/>
     <indexConfig>
       <rt>false</rt>
     </indexConfig>
     <jmx/>
     <updateHandler>
       <autoSoftCommit>
         <maxTime>10000</maxTime>
       </autoSoftCommit>
     </updateHandler>
     <query>
       <filterCache class="solr.SolrFilterCache" highWaterMarkMB="2048" lowWaterMarkMB="1024"/>
       <enableLazyFieldLoading>true</enableLazyFieldLoading>
       <useColdSearcher>true</useColdSearcher>
       <maxWarmingSearchers>16</maxWarmingSearchers>
     </query>
     <requestDispatcher>
       <requestParsers enableRemoteStreaming="true" multipartUploadLimitInKB="2048000"/>
       <httpCaching never304="true"/>
     </requestDispatcher>
     <requestHandler class="solr.SearchHandler" default="true" name="search"/>
     <requestHandler class="com.datastax.bdp.search.solr.handler.component.CqlSearchHandler" name="solr_query"/>
     <requestHandler class="solr.UpdateRequestHandler" name="/update"/>
     <requestHandler class="solr.UpdateRequestHandler" name="/update/csv" startup="lazy"/>
     <requestHandler class="solr.UpdateRequestHandler" name="/update/json" startup="lazy"/>
     <requestHandler class="solr.FieldAnalysisRequestHandler" name="/analysis/field" startup="lazy"/>
     <requestHandler class="solr.DocumentAnalysisRequestHandler" name="/analysis/document" startup="lazy"/>
     <requestHandler class="solr.admin.AdminHandlers" name="/admin/"/>
     <requestHandler class="solr.PingRequestHandler" name="/admin/ping">
       <lst name="invariants">
         <str name="qt">search</str>
         <str name="q">solrpingquery</str>
       </lst>
       <lst name="defaults">
         <str name="echoParams">all</str>
       </lst>
     </requestHandler>
     <requestHandler class="solr.DumpRequestHandler" name="/debug/dump">
       <lst name="defaults">
         <str name="echoParams">explicit</str>
         <str name="echoHandler">true</str>
       </lst>
     </requestHandler>
   </config>

4. 構成を適用し、インデックスをリビルドします。

   RELOAD SEARCH INDEX ON cycling.comments;

ALTER SEARCH INDEX CONFIG ON cycling.comments 
SET autoCommitTime = 10000;

RELOAD SEARCH INDEX ON cycling.comments;

ALTER SEARCH INDEX CONFIG ON cycling.comments 
ADD requestHandler[@name='/elevate',@class='solr.SearchHandler', @startup='lazy'] 
WITH  $$ {"defaults":[{"echoParams":"explicit"}],"last-components":["elevator"]} $$;

RELOAD SEARCH INDEX ON cycling.comments;

<requestHandler name="/elevate" class="solr.SearchHandler"> 
   <lst name="defaults">
      <str name="echoParams">explicit</int>
   </lst>
   <arr name="last-components">
    <str>elevator</str>
   </arr>
</requestHandler>

RELOAD SEARCH INDEX ON cycling.comments;

ALTER SEARCH INDEX SCHEMA ON cycling.comments 
ADD fields.field[@name='fieldname', @type='StrField', @multiValued = 'false', @indexed='true'];
ALTER SEARCH INDEX SCHEMA
ON cycling.comments
SET types.fieldType[@name='TextField']@class='org.apache.solr.schema.TextField';

ALTER SEARCH INDEX SCHEMA
ON cycling.comments
SET fields.field[@name='comment']@type='TextField';

RELOAD SEARCH INDEX ON cycling.comments;

ALTER SEARCH INDEX SCHEMA ON cycling.comments 
ADD FIELD record_id;

RELOAD SEARCH INDEX ON cycling.comments;

REBUILD SEARCH INDEX ON cycling.comments;

ALTER SEARCH INDEX SCHEMA ON cycling.comments 
SET field[@name='fieldname']@name = 'anotherFieldName';

RELOAD SEARCH INDEX ON cycling.comments;

REBUILD SEARCH INDEX ON cycling.comments;

ALTER SEARCH INDEX SCHEMA ON cycling.comments 
SET field[@name='fieldname']@type = 'UUIDField';

RELOAD SEARCH INDEX ON cycling.comments;

REBUILD SEARCH INDEX ON cycling.comments;

ALTER SEARCH INDEX SCHEMA ON cycling.comments 
DROP field anotherFieldName;

RELOAD SEARCH INDEX ON cycling.comments;

REBUILD SEARCH INDEX ON cycling.comments;

ALTER TABLE cycling.cyclist_races 
  ADD manager UUID;

ALTER TABLE cycling.cyclist_races 
  ADD completed list<text>;

ALTER TABLE cycling.cyclist_races 
  DROP manager;

ALTER TABLE cycling.race_times 
  RENAME race_date TO date;

• 単一のプロパティ名および値。
• 名前および値を設定するためのプロパティ・マップ。「圧縮とコンパクションに関する次のセクション」を参照してください。

ALTER TABLE cycling.cyclist_base
WITH comment = 'basic cyclist information';

ALTER TABLE cycling.comments 
WITH compression = { 
   'sstable_compression' : 'DeflateCompressor', 
   'chunk_length_kb' : 64 };

ALTER TABLE cycling.comments 
   WITH caching = {
    'keys': 'NONE', 
    'rows_per_partition': 10 };

ALTER TABLE cycling.cyclist_base
WITH speculative_retry = '95percentile';

ALTER TABLE cycling.cyclist_base
WITH speculative_retry = '10ms';

ALTER TABLE cycling.comments 
WITH COMPACTION = {
   'class': 'SizeTieredCompactionStrategy', 
   'enabled': 'false' };

DESC cycling.comments

CREATE TABLE cycling.comments (
    id uuid,
    created_at timestamp,
    comment text,
    commenter text,
    record_id timeuuid,
    solr_query text,
    PRIMARY KEY (id, created_at)
) WITH CLUSTERING ORDER BY (created_at DESC)
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'NONE', 'rows_per_partition': '10'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.DeflateCompressor'}
    AND crc_check_chance = 1.0
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND speculative_retry = '99PERCENTILE';

ALTER TYPE cycling.fullname 
   ADD middlename text;

ALTER TYPE cycling.fullname 
  RENAME middlename TO middle 
  AND lastname TO last 
  AND firstname TO first;

DESC TYPE cycling.fullname

CREATE TYPE cycling.fullname (
    first text,
    last text,
    middle text
);

BEGIN BATCH USING TIMESTAMP 1481124356754405

  INSERT INTO cycling.cyclist_expenses (
    cyclist_name, expense_id, amount, description, paid
  ) VALUES (
    'Vera ADRIAN', 2, 13.44, 'Lunch', true
  );

  INSERT INTO cycling.cyclist_expenses (
    cyclist_name, expense_id, amount, description, paid
  ) VALUES (
    'Vera ADRIAN', 3, 25.00, 'Dinner', true
  );

APPLY BATCH;

EXPAND ON

SELECT
  cyclist_name, expense_id,
  amount, WRITETIME(amount),
  description, WRITETIME(description),
  paid, WRITETIME(paid)
FROM
  cycling.cyclist_expenses
WHERE
  cyclist_name = 'Vera ADRIAN';

@ Row 1
------------------------+------------------
 cyclist_name           | Vera ADRIAN
 expense_id             | 2
 amount                 | 13.44
 writetime(amount)      | 1481124356754405
 description            | Lunch
 writetime(description) | 1481124356754405
 paid                   | True
 writetime(paid)        | 1481124356754405

@ Row 2
------------------------+------------------
 cyclist_name           | Vera ADRIAN
 expense_id             | 3
 amount                 | 25
 writetime(amount)      | 1481124356754405
 description            | Dinner
 writetime(description) | 1481124356754405
 paid                   | True
 writetime(paid)        | 1481124356754405

(2 rows)

BEGIN BATCH

  INSERT INTO cycling.cyclist_expenses (
    cyclist_name, expense_id
  ) VALUES (
    'Joe WALLS', 1
  ) IF NOT EXISTS;

  INSERT INTO cycling.cyclist_expenses (
    cyclist_name, expense_id, amount, description, paid
  ) VALUES (
    'Joe WALLS', 1, 8, 'burrito', false
  );

APPLY BATCH;

BEGIN COUNTER BATCH

  UPDATE cycling.popular_count
  SET popularity = popularity + 1
  WHERE id = 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47;

  UPDATE cycling.popular_count
  SET popularity = popularity + 125
  WHERE id = 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47;

  UPDATE cycling.popular_count
  SET popularity = popularity - 64
  WHERE id = 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47;

APPLY BATCH;

CREATE CUSTOM INDEX fn_prefix 
   ON cycling.comments (commenter) 
   USING 'org.apache.cassandra.index.sasi.SASIIndex';

CREATE CUSTOM INDEX fn_sparse 
   ON cycling.comments (record_id) 
   USING 'org.apache.cassandra.index.sasi.SASIIndex'
   WITH OPTIONS = { 'mode': 'SPARSE' };

CREATE CUSTOM INDEX  fn_notcasesensitive 
ON cycling.comments (comment) 
USING 'org.apache.cassandra.index.sasi.SASIIndex'
WITH OPTIONS = { 
   'analyzer_class': 'org.apache.cassandra.index.sasi.analyzer.NonTokenizingAnalyzer',
   'case_sensitive': 'false'};

CREATE CUSTOM INDEX stdanalyzer_idx 
   ON cycling.comments (comment) 
   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' }
;

CREATE OR REPLACE FUNCTION cycling.left (
  column text, num int
) 
  RETURNS NULL ON NULL INPUT 
  RETURNS text 
  LANGUAGE javascript AS 
    $$
      column.substring(0, num)
    $$
;

SELECT left(firstname, 1), lastname
FROM cycling.cyclist_name;

 cycling.left(firstname, 1) | lastname
----------------------------+-----------------
                          A |           FRAME
                       null |        MATTHEWS
                       null |             VOS
                          P |       TIRALONGO
                          M |             VOS
                          S |      KRUIKSWIJK
                          A | VAN DER BREGGEN

{'nation':'CANADA' }

CREATE INDEX blist_idx
ON cycling.birthday_list ( ENTRIES(blist) );

CREATE INDEX rnumbers_idx
ON cycling.race_starts ( FULL(rnumbers) );

CREATE KEYSPACE cycling
  WITH REPLICATION = { 
   'class' : 'SimpleStrategy', 
   'replication_factor' : 1 
  };

CREATE KEYSPACE cycling 
  WITH REPLICATION = { 
   'class' : 'NetworkTopologyStrategy', 
   'datacenter1' : 1 } 
;

dsetool status

DC: Cassandra       Workload: Cassandra       Graph: no
======================================================
Status=Up/Down
|/ State=Normal/Leaving/Joining/Moving
--   Address          Load             Owns                 Token                                        Rack         Health [0,1]
                                                            0
UN   10.0.0.15   67.44 MiB        ?                    -9223372036854775808                         rack1        0.90
UN   10.0.0.110   766.4 KiB        ?                    0                                            rack2        0.90

Note: you must specify a keyspace to get ownership information.

CREATE KEYSPACE cycling
  WITH REPLICATION = { 
   'class' : 'NetworkTopologyStrategy',
   'datacenter1' : 3 
  } 
AND DURABLE_WRITES = false ;

CREATE TABLE cycling.race_winners (
  cyclist_name FROZEN<fullname>, 
  race_name text,
  race_position int,
  PRIMARY KEY (race_name, race_position)
);

CREATE TABLE cycling.cyclist_name (
  id UUID PRIMARY KEY,
  lastname text,
  firstname text
);

CREATE TABLE cycling.cyclist_category (
  category text,
  points int,
  id UUID,
  lastname text,
  PRIMARY KEY (category, points)
)
WITH CLUSTERING ORDER BY (points DESC);

CREATE TABLE cycling.rank_by_year_and_name (
  race_year int,
  race_name text,
  cyclist_name text,
  rank int,
  PRIMARY KEY ((race_year, race_name), rank)
);

CREATE TABLE cycling.cyclist_id (
  lastname text,
  firstname text,
  age int,
  id UUID,
  PRIMARY KEY ((lastname, firstname), age)
);

CREATE TABLE cycling.cyclist_category (
  category text,
  points int,
  id UUID,
  lastname text,
  PRIMARY KEY (category, points)
)
WITH CLUSTERING ORDER BY (points DESC);

CREATE TABLE cycling.cyclist_emails (
  userid text PRIMARY KEY,
  id UUID,
  emails set<text>
) WITH ID='1bb7516e-b140-11e8-96f8-529269fb1459';

CREATE TABLE cycling.cyclist_name (
  id UUID PRIMARY KEY,
  lastname text,
  firstname text
);

 id                                   | firstname | lastname
--------------------------------------+-----------+-----------------
 e7ae5cf3-d358-4d99-b900-85902fda9bb0 |      Alex |           FRAME
 fb372533-eb95-4bb4-8685-6ef61e994caa |   Michael |        MATTHEWS
 5b6962dd-3f90-4c93-8f61-eabfa4a803e2 |  Marianne |             VOS
 220844bf-4860-49d6-9a4b-6b5d3a79cbfb |     Paolo |       TIRALONGO
 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47 |    Steven |      KRUIKSWIJK
 e7cd5752-bc0d-4157-a80f-7523add8dbcd |      Anna | VAN DER BREGGEN

(6 rows)

 id                                   | firstname | lastname
--------------------------------------+-----------+-----------------
 e7ae5cf3-d358-4d99-b900-85902fda9bb0 |      null |            null
 fb372533-eb95-4bb4-8685-6ef61e994caa |   Michael |        MATTHEWS
 5b6962dd-3f90-4c93-8f61-eabfa4a803e2 |  Marianne |             VOS
 220844bf-4860-49d6-9a4b-6b5d3a79cbfb |     Paolo |       TIRALONGO
 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47 |    Steven |      KRUIKSWIJK
 e7cd5752-bc0d-4157-a80f-7523add8dbcd |      Anna | VAN DER BREGGEN

(6 rows)

 [applied]
-----------
      True

 id                                   | firstname | lastname
--------------------------------------+-----------+-----------------
 fb372533-eb95-4bb4-8685-6ef61e994caa |   Michael |        MATTHEWS
 5b6962dd-3f90-4c93-8f61-eabfa4a803e2 |  Marianne |             VOS
 220844bf-4860-49d6-9a4b-6b5d3a79cbfb |     Paolo |       TIRALONGO
 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47 |    Steven |      KRUIKSWIJK
 e7cd5752-bc0d-4157-a80f-7523add8dbcd |      Anna | VAN DER BREGGEN

(5 rows)

 [applied] | firstname | lastname
-----------+-----------+----------
     False |   Michael | MATTHEWS

DELETE id FROM cycling.cyclist_id 
WHERE lastname = 'JONES' and firstname = 'Bram' 
IF EXISTS;

 [applied]
-----------
     False

DELETE id FROM cycling.cyclist_id 
WHERE lastname = 'WELTEN' AND firstname = 'Bram' 
IF age = 20;

 [applied] | age
-----------+-----
     False |  18

• cyclingキースペース内のすべてのオブジェクトについて、AUTHORIZEを含むすべてのパーミッション・タイプをロールが付与できるようにします。

  GRANT AUTHORIZE ON KEYSPACE cycling TO cycling_admin;

  警告: これによって、ロールは、そのロールのパーミッションと、継承するロールを変更できるようになるため、cyclingキースペースにおけるスーパーユーザーになります。
• samロールは、クエリーを実行し、cyclingキースペース内のデータを変更するパーミッションを割り当てることができます。

  GRANT AUTHORIZE FOR SELECT, MODIFY ON KEYSPACE cycling TO sam;

  ヒント: samロールは、AUTHORIZE, AUTHORIZE FOR ...、ALTER、CREATE、DESCRIBE、およびDROPなどの他のパーミッションを別のロールに付与することはできません。

GRANT ALL PERMISSIONS ON KEYSPACE cycling TO cycling_admin;

GRANT SELECT, MODIFY ON KEYSPACE cycling TO coach;

GRANT ALTER ON KEYSPACE cycling TO coach;

GRANT SELECT ON 'Sprint' ROWS IN cycling.cyclist_category TO martin;

INSERT INTO cycling.cyclist_career_teams (
  id,lastname,teams
 ) VALUES (
  5b6962dd-3f90-4c93-8f61-eabfa4a803e2, 
  'VOS', 
  { 'Rabobank-Liv Woman Cycling Team','Rabobank-Liv Giant','Rabobank Women Team','Nederland bloeit' }
 );

INSERT INTO cycling.cyclist_teams (
  id,firstname,lastname,teams
) VALUES (
  5b6962dd-3f90-4c93-8f61-eabfa4a803e2, 
  'Marianne',
  'VOS', 
  { 2015 : 'Rabobank-Liv Woman Cycling Team', 
    2014 : 'Rabobank-Liv Woman Cycling Team' }
);

 [applied]
-----------
      True

 [applied] | id                                   | firstname | lastname
-----------+--------------------------------------+-----------+----------
     False | c4b65263-fe58-4846-83e8-f0e1c13d518f |  Rissella |    RATTO

LIST ALL PERMISSIONS;

LIST ALL PERMISSIONS OF sam;

 role | username | resource           | permission | granted | restricted | grantable
------+----------+--------------------+------------+---------+------------+-----------
  sam |      sam | <keyspace cycling> |     SELECT |   False |      False |      True
  sam |      sam | <keyspace cycling> |     MODIFY |   False |      False |      True

(2 rows)

LIST ALL PERMISSIONS ON cycling.cyclist_name;

 role            | username        | resource           | permission | granted | restricted | grantable
-----------------+-----------------+--------------------+------------+---------+------------+-----------
           coach |           coach | <keyspace cycling> |      ALTER |    True |      False |     False
           coach |           coach | <keyspace cycling> |     SELECT |    True |      False |     False
           coach |           coach | <keyspace cycling> |     MODIFY |    True |      False |     False
   cycling_admin |   cycling_admin | <keyspace cycling> |     CREATE |    True |      False |     False
   cycling_admin |   cycling_admin | <keyspace cycling> |      ALTER |    True |      False |     False
   cycling_admin |   cycling_admin | <keyspace cycling> |       DROP |    True |      False |     False
   cycling_admin |   cycling_admin | <keyspace cycling> |     SELECT |    True |      False |     False
   cycling_admin |   cycling_admin | <keyspace cycling> |     MODIFY |    True |      False |     False
   cycling_admin |   cycling_admin | <keyspace cycling> |  AUTHORIZE |    True |      False |     False
   cycling_admin |   cycling_admin | <keyspace cycling> |   DESCRIBE |    True |      False |     False
 cycling_analyst | cycling_analyst | <keyspace cycling> |     SELECT |    True |      False |     False
        dantest1 |        dantest1 | <keyspace cycling> |  AUTHORIZE |    True |      False |     False
        db_admin |        db_admin | <keyspace cycling> |     SELECT |   False |       True |     False
        db_admin |        db_admin | <keyspace cycling> |     MODIFY |   False |       True |     False
          martin |          martin |    <all keyspaces> |     CREATE |   False |       True |     False
          martin |          martin | <keyspace cycling> |     CREATE |    True |      False |     False
      role_admin |      role_admin | <keyspace cycling> |     SELECT |   False |       True |     False
      role_admin |      role_admin | <keyspace cycling> |     MODIFY |   False |       True |     False
             sam |             sam | <keyspace cycling> |     SELECT |   False |      False |      True
             sam |             sam | <keyspace cycling> |     MODIFY |   False |      False |      True
    team_manager |    team_manager | <keyspace cycling> |     MODIFY |    True |      False |     False

(21 rows)

LIST ROLES;

LIST ROLES OF coach;

 role          | super | login | options
---------------+-------+-------+---------
         coach | False | False |        {}
 cycling_admin | False |  True |        {}

(2 rows)

• テーブルのパーティション・キーの1つまたは複数のメンバー。
• クラスター化カラム（関係の前にパーティション・キー内のすべてのカラムを指定する他の関係がある場合）。
• CREATE INDEXを使用してインデックスが作成されたカラム。

CREATE TABLE cycling.cyclist_career_teams (
  id UUID PRIMARY KEY,
  lastname text,
  teams set<text>
);

SELECT id, lastname, teams 
FROM cycling.cyclist_career_teams 
WHERE id = 5b6962dd-3f90-4c93-8f61-eabfa4a803e2;

CREATE TABLE cycling.cyclist_id (
  lastname text,
  firstname text,
  age int,
  id UUID,
  PRIMARY KEY ((lastname, firstname), age)
);

SELECT * 
FROM cycling.cyclist_id 
WHERE lastname = 'EENKHOORN'
  AND firstname = 'Pascal'
  AND age IN (17, 18); 

CREATE TABLE cycling.events (
  year int,
  start_month int,
  start_day int,
  end_month int,
  end_day int,
  race text,
  discipline text,
  location text,
  uci_code text,
  PRIMARY KEY (
    (year, discipline), start_month, start_day, race
  )
);

SELECT *
FROM cycling.events
WHERE race = 'Superprestige - Hoogstraten -2017'
  AND start_month IN (1, 2)
ALLOW FILTERING;

 id                                   | lastname | teams
--------------------------------------+----------+----------------------------------------------------------------------------------------------------
 1c9ebc13-1eab-4ad5-be87-dce433216d40 |    BRAND | {'AA Drink - Leontien.nl', 'Leontien.nl', 'Rabobank-Liv Giant', 'Rabobank-Liv Woman Cycling Team'}

(1 rows)

CREATE INDEX team_year_idx
ON cycling.cyclist_teams ( KEYS (teams) );

CREATE INDEX blist_idx
ON cycling.birthday_list ( ENTRIES(blist) );

CREATE INDEX rnumbers_idx
ON cycling.race_starts ( FULL(rnumbers) );

SELECT *
FROM cycling.race_starts
WHERE rnumbers = [39, 7, 14];

SELECT * 
FROM cycling.calendar 
WHERE race_id = 101
  AND race_start_date >= '2014-05-27' 
  AND race_start_date < '2017-06-16';

1. ORDER BY句にはクラスター化カラムのみを含めます。
2. WHERE句では、ORDER BY句のカラムの前にあるすべてのパーティション・キー値とクラスター化カラム値を指定します。
3. 複数のカラムをソートする場合、カラムは、テーブル定義のPRIMARY KEY句のカラムのリストと同じ順序でORDER BY句にリストする必要があります。
4. ソートの順序指定は制限されます。たとえば、テーブル定義でCLUSTERING ORDER BY (start_month ASC, start_day ASC)を使用すると、クエリーでORDER BY start_day, raceを使用できます（デフォルトはASC）。すべてのカラムに適用する場合は、ソートを逆順にすることもできます（例：ORDER BY start_day DESC, race DESC）。
5. 別名ではなく実際の名前を使用してカラムを参照してください。

 race_date  | race_time
------------+--------------------
 2019-03-21 | 10:01:18.000000000
 2018-07-26 | 10:01:18.000000000
 2017-04-14 | 10:01:18.000000000

(3 rows)

SELECT
  race_date, FLOOR(race_time, 1h), COUNT(*)
FROM
  cycling.race_times_summary
GROUP BY
  race_date, FLOOR(race_time, 1h);

 race_date  | system.floor(race_time, 1h) | count
------------+-----------------------------+-------
 2019-03-21 |          10:00:00.000000000 |     2
 2019-03-21 |          11:00:00.000000000 |     1
 2019-03-21 |          12:00:00.000000000 |     1
 2018-07-26 |          10:00:00.000000000 |     2
 2018-07-26 |          11:00:00.000000000 |     1
 2018-07-26 |          12:00:00.000000000 |     1
 2017-04-14 |          10:00:00.000000000 |     2
 2017-04-14 |          11:00:00.000000000 |     1
 2017-04-14 |          12:00:00.000000000 |     1

(9 rows)

SELECT MAX(race_points) 
FROM cycling.cyclist_points
WHERE id = e3b19ec4-774a-4d1c-9e5a-decec1e30aac;

 system.max(race_points)
-------------------------
                     120

(1 rows)

 system.min(race_points)
-------------------------
                       6

(1 rows)

SELECT AVG(race_points) 
FROM cycling.cyclist_points
WHERE id = e3b19ec4-774a-4d1c-9e5a-decec1e30aac;

 system.avg(race_points)
-------------------------
                      67

(1 rows)

 system.sum(race_points)
-------------------------
                     195

(1 rows)

INSERT INTO cycling.calendar (
  race_id, race_name, race_start_date, race_end_date
) VALUES (
  200, 'placeholder', '2015-05-27', '2015-05-27'
)
USING TTL 200;

UPDATE cycling.calendar 
USING TTL 300 
SET race_name = 'dummy' 
WHERE race_id = 200 
  AND race_start_date = '2015-05-27' 
  AND race_end_date = '2015-05-27';

SELECT TTL(race_name) 
FROM cycling.calendar 
WHERE race_id = 200;

 ttl(race_name)
----------------
            276

(1 rows)

UPDATE cycling.cyclist_name
SET firstname = NULL
WHERE id IN (
  5b6962dd-3f90-4c93-8f61-eabfa4a803e2, fb372533-eb95-4bb4-8685-6ef61e994caa
);

UPDATE cycling.cyclist_sponsors_expire 
  USING TIMESTAMP 200 AND TTL 20000 
  SET sponsorship += {'Tag Heuer'} 
  WHERE cyclist_name='PRIETO, Marcela';

UPDATE cycling.popular_count 
SET popularity = popularity + 2 
WHERE id = 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47;