いままでのスクリプト風の問い合わせよりも多くの人に馴染みやすいと思うので、ちょっと触ってみます。
CQLコンソール
CQLのインターフェースはいままでのcassandra-cliとは別に、binディレクトリにあるcqlshコマンドを使用します。
まずhelpを見てみます。
# /usr/local/cassandra/bin/cqlsh --help Usage: cqlsh [options] [host [port]] CQL Shell for Apache Cassandra Options: --version show program's version number and exit -h, --help show this help message and exit -C, --color Always use color output --no-color Never use color output -u USERNAME, --username=USERNAME Authenticate as user. -p PASSWORD, --password=PASSWORD Authenticate using password. -k KEYSPACE, --keyspace=KEYSPACE Authenticate to the given keyspace. -f FILE, --file=FILE Execute commands from FILE, then exit -t TRANSPORT_FACTORY, --transport-factory=TRANSPORT_FACTORY Use the provided Thrift transport factory function. --debug Show additional debugging information --cqlversion=CQLVERSION Specify a particular CQL version (default: 3). Examples: "2", "3.0.0-beta1" -2, --cql2 Shortcut notation for --cqlversion=2 -3, --cql3 Shortcut notation for --cqlversion=3 Connects to localhost:9160 by default. These defaults can be changed by setting $CQLSH_HOST and/or $CQLSH_PORT. When a host (and optional port number) are given on the command line, they take precedence over any defaults.
-2や-3とオプションを渡すと指定されたバージョンのCQLを使用します。
ここではバージョン2を指定してみます。
# /usr/local/cassandra/bin/cqlsh -2 Connected to Memorycraft Cluster at localhost:9160. [cqlsh 2.3.0 | Cassandra 0.0.0 | CQL spec 2.0.0 | Thrift protocol 19.35.0] Use HELP for help. cqlsh>
このようにSQLクライアント風のコンソールが開始されます。
コンソール上でもう一度helpを打ってみます。
cqlsh:Hogebook> help Documented shell commands: =========================== ASSUME CONSISTENCY DESC EXIT SHOW TRACING CAPTURE COPY DESCRIBE HELP SOURCE CQL help topics: ================ ALTER CREATE_KEYSPACE SELECT_EXPR ALTER_ADD CREATE_TABLE SELECT_LIMIT ALTER_ALTER CREATE_TABLE_OPTIONS SELECT_TABLE ALTER_DROP CREATE_TABLE_TYPES SELECT_WHERE ALTER_WITH DELETE TEXT_OUTPUT APPLY DELETE_COLUMNS TIMESTAMP_INPUT ASCII_OUTPUT DELETE_USING TIMESTAMP_OUTPUT BEGIN DELETE_WHERE TRUNCATE BLOB_INPUT DROP TYPES BOOLEAN_INPUT DROP_COLUMNFAMILY UPDATE CONSISTENCYLEVEL DROP_INDEX UPDATE_COUNTERS CREATE DROP_KEYSPACE UPDATE_SET CREATE_COLUMNFAMILY DROP_TABLE UPDATE_USING CREATE_COLUMNFAMILY_OPTIONS INSERT UPDATE_WHERE CREATE_COLUMNFAMILY_TYPES SELECT USE CREATE_INDEX SELECT_COLUMNFAMILY UUID_INPUT
さらに各コマンドでhelpを打つと詳細が分かります。
cqlsh:Hogebook> help SELECT SELECT [FIRST n] [REVERSED] <selectExpr> FROM [<keyspace>.]<table> [USING CONSISTENCY <consistencylevel>] [WHERE <clause>] [ORDER BY <colname> [DESC]] [LIMIT m]; SELECT is used to read one or more records from a CQL table. It returns a set of rows matching the selection criteria specified. Note that FIRST and REVERSED are only supported in CQL 2, and ORDER BY is only supported in CQL 3 and higher. For more information, see one of the following: HELP SELECT_EXPR HELP SELECT_TABLE HELP SELECT_WHERE HELP SELECT_LIMIT HELP CONSISTENCYLEVEL
かなりSQLっぽく使えるようです。
いくつかコマンドを打ってみたいと思います。
DESC TABLE
cqlsh:Hogebook> desc table User; CREATE TABLE User ( KEY text PRIMARY KEY, email text, gender text ) WITH comment='' AND comparator=text AND read_repair_chance=0.100000 AND gc_grace_seconds=864000 AND default_validation=text AND min_compaction_threshold=4 AND max_compaction_threshold=32 AND replicate_on_write='true' AND compaction_strategy_class='SizeTieredCompactionStrategy' AND compression_parameters:sstable_compression='SnappyCompressor'; CREATE INDEX User_gender_idx ON User (gender);
SELECT
cqlsh:Hogebook> select * from User; KEY | email | gender -----------------+----------------------------+-------- memorycraft | memorycraft@gmail.com | male memorycraftgirl | memorycraft+girl@gmail.com | female
INSERT
cqlsh:Hogebook> insert into User(KEY, email, gender) VALUES('hellocql', 'memorycraft+cql@gmail.com', 'male');
cqlsh:Hogebook>
cqlsh:Hogebook> select * from User;
KEY | email | gender
-----------------+----------------------------+--------
memorycraft | memorycraft@gmail.com | male
hellocql | memorycraft+cql@gmail.com | male
memorycraftgirl | memorycraft+girl@gmail.com | female
これなら違和感なくデータの操作や参照ができそうです。
APIアクセス
これらのCQLコマンドはコンソール上でも使用しますが、アプリケーションコードからも発行できます。
以前の記事で使用したphpcasaからも使うことができます。
例えば以下はテーブルの全データを取得して出力するスクリプトになります。
このスクリプトを実行してみます。
$ php ./cql.php
cassandra\CqlResult Object
(
[type] => 1
[rows] => Array
(
[0] => cassandra\CqlRow Object
(
[key] => memorycraft
[columns] => Array
(
[0] => cassandra\Column Object
(
[name] => KEY
[value] => memorycraft
[timestamp] => -1
[ttl] =>
)
[1] => cassandra\Column Object
(
[name] => email
[value] => memorycraft@gmail.com
[timestamp] => 1589299048
[ttl] =>
)
[2] => cassandra\Column Object
(
[name] => gender
[value] => male
[timestamp] => 1596347048
[ttl] =>
)
)
)
[1] => cassandra\CqlRow Object
(
[key] => hellocql
[columns] => Array
(
[0] => cassandra\Column Object
(
[name] => KEY
[value] => hellocql
[timestamp] => -1
[ttl] =>
)
[1] => cassandra\Column Object
(
[name] => email
[value] => memorycraft+cql@gmail.com
[timestamp] => 1562593385
[ttl] =>
)
[2] => cassandra\Column Object
(
[name] => gender
[value] => male
[timestamp] => 1562593384
[ttl] =>
)
)
)
[2] => cassandra\CqlRow Object
(
[key] => memorycraftgirl
[columns] => Array
(
[0] => cassandra\Column Object
(
[name] => KEY
[value] => memorycraftgirl
[timestamp] => -1
[ttl] =>
)
[1] => cassandra\Column Object
(
[name] => email
[value] => memorycraft+girl@gmail.com
[timestamp] => 1611666048
[ttl] =>
)
[2] => cassandra\Column Object
(
[name] => gender
[value] => female
[timestamp] => 1602906048
[ttl] =>
)
)
)
)
[num] =>
[schema] => cassandra\CqlMetadata Object
(
[name_types] => Array
(
[KEY] => AsciiType
)
[value_types] => Array
(
[KEY] => UTF8Type
[email] => UTF8Type
[gender] => UTF8Type
)
[default_name_type] => UTF8Type
[default_value_type] => UTF8Type
)
)
出力をみると、オブジェクト形式で結果が帰ってくるようです。 これなら普段のSQLと近い感覚でコードがかけるので、移行もしやすいと思います。
以上です。
