CQL

cqlsh

SHOW VERSION;
SHOW HOST;

SHOW SESSION $id;

SOURCE '/data/commands.cql'

CAPTURE;
CAPTURE OFF;
CAPTURE '/data/file';

TRACING ON;
TRACING OFF;

PAGING ON;
PAGING OFF;
PAGING $page_size_in_rows;

EXPAND ON;
EXPAND OFF;

-- LOGIN <username> [<password>]

CLEAR;
CLS;

-- DESCRIBE <>
DESCRIBE CLUSTER;
DESCRIBE SCHEMA;
DESCRIBE KEYSPACES;
DESCRIBE KEYSPACE keyspace_name;
DESCRIBE TABLES;
DESCRIBE TABLE table_name;
DESCRIBE MATERIALIZED VIEW view_name;
DESCRIBE TYPES;
DESCRIBE TYPE type_name;
DESCRIBE FUNCTIONS;
DESCRIBE FUNCTION function_name;
DESCRIBE AGGREGATES;
DESCRIBE AGGREGATE aggregate_function_name;

-- COPY <table name> [(<column>, ...)] TO <file name> WITH <copy option> [AND <copy option> ...]
-- MAXREQUESTS=6 PAGESIZE=1000 PAGETIMEOUT=10
-- BEGINTOKEN, ENDTOKEN
-- MAXOUTPUTSIZE=-1
-- ENCODING=utf8

-- COPY <table name> [(<column>, ...)] FROM <file name> WITH <copy option> [AND <copy option> ...]
-- INGESTRATE=100000 MAXROWS=-1 SKIPROWS=0 SKIPCOLS=0 MAXPARSEERRORS=-1 MAXINSERTERRORS=1000
-- ERRFILE=import_<ks>_<table>.err
-- MAXBATCHSIZE=20 MINBATCHSIZE=2
-- CHUNKSIZE=1000
COPY test FROM 'file.csv' WITH HEADER=true;

-- COPY
-- NULLVAL=null HEADER=false DECIMALSEP=.
-- THOUSANDSSEP
-- BOOLSTYlE True, False
-- NUMPROCESSES
-- MAXATTEMPTS=5
-- REPORTFREQUENCY=0.25
-- RATEFILE

CQL

KEYSPACE - CREATE, DROP, DESCRIBE, ALTER
- 数据库概念
- durable_writes
- replication
  - class
    - NetworkTopologyStrategy
      - <datacenter> - 给定 dc 的副本数
      - replication_factor - dc 默认副本数
    - SimpleStrategy
      - replication_factor
        
        全局副本数，不考虑 datacenter
USE keyspace
TABLE
- column
  - STATIC - 相同分片值相同
- PRIMARY KEY
  - 不指定则每行为一个 partition
  - 值不能为 null
  - 第一个列作为 partition key
  - PRIMARY KEY ((a, b), c) - 多个 partation key
    - c 为 clustering column
- table_options
  - ~~COMPACT STORAGE~~
  - CLUSTERING ORDER
    - 默认基于 clustering column 排序
    - 可修改排序
  - comment
  - read_repair_chance=0
  - dclocal_read_repair_chance=0
  - speculative_retry=99PERCENTILE
  - gc_grace_seconds=864000
  - tombstone_gc
  - bloom_filter_fp_chance=0.01
  - default_time_to_live=0
  - cdc
    - enabled=false
    - preimage=false
    - ttl
  - compaction - 合并 SSTables
    - tombstone_threshold=0.2
    - tombstone_compaction_interval=86400s - 1day
    - SizeTieredCompactionStrategy - STCS - 默认
      - bucket_high=1.5, bucket_low=0.5
      - min_sstable_size=50
      - min_threshold=4, max_threshold=32
    - LeveledCompactionStrategy - LCS
      - sstable_size_in_mb=160
    - IncrementalCompactionStrategy - ICS - 企业版
    - DateTieredCompactionStrategy - TWCS
      - 1 sstable/time window
      - compaction_window_unit=DAYS, compaction_window_size=1
      - expired_sstable_check_frequency_seconds=600
      - min_threshold=4, max_threshold=32
  - compression
    - sstable_compression=LZ4Compressor
      - SnappyCompressor
      - DeflateCompressor
    - chunk_length_in_kb=4KB
  - scylla_encryption_options
  - caching = {'enabled': 'true'}
  - speculative_retry
  - cache
    - enabled=true
INDEX - 二级索引
- 本地 - 单分片
- 全局
TTL
- 设置为列维度
- 可以行维度
- 表可以设置默认 default_time_to_live
JSON
- SELECT JSON
- INSERT JSON
- fromJson
- toJson
CREATE MATERIALIZED VIEW
- SELECT 限制
  - 不可以 ALLOW FILTERING
  - 不允许 order
  - 不包含 limit
ScyllaDB 扩展
- SELECT BYPASS CACHE
- CREATE TABLE WITH paxos_grace_seconds
- SELECT/INSERT USING TIMEOUT
- KEYSPACE options
- PRUNE MATERIALIZED VIEW
- CREATE MATERIALIZED VIEW WITH synchronous_updates
  - 同步更新
- MV 二级索引 synchronous_updates
- REDUCEFUNC for UDA
- per_partition_rate_limit

CREATE KEYSPACE catalog WITH REPLICATION = { 'class' : 'NetworkTopologyStrategy','DC1' : 3};
use catalog;
CREATE TABLE mutant_data (
   first_name text,
   last_name text,
   address text,
   picture_location text,
   PRIMARY KEY((first_name, last_name)));

cqlsh​

CQL​

cqlsh

CQL