{Keyspaces}Amazon Keyspaces (Apache Cassandra 向け)

-- 1. EX2(Amazon Linux2)へのcqlsh のインストール

python --version

wget https://dlcdn.apache.org/cassandra/3.11.11/apache-cassandra-3.11.11-bin.tar.gz
tar xvzf apache-cassandra-3.11.11-bin.tar.gz
cd apache-cassandra-3.11.11

./bin/cqlsh --version

curl https://certs.secureserver.net/repository/sf-class2-root.crt -O

mkdir /home/ec2-user/.cassandra
vim /home/ec2-user/.cassandra/cqlshrc

-------------------------------------------------

[connection]
port = 9142
factory = cqlshlib.ssl.ssl_transport_factory

[ssl]
validate = true
certfile = /home/ec2-user/apache-cassandra-3.11.11/sf-class2-root.crt
version = TLSv1_2

[copy]
NUMPROCESSES=16
MAXATTEMPTS=25

[copy-from]
CHUNKSIZE=50
INGESTRATE=1500
MAXINSERTERRORS=-1
MAXPARSEERRORS=-1
MINBATCHSIZE=1
MAXBATCHSIZE=10

[csv]
field_size_limit=999999

-------------------------------------------------

-- 2. サービス固有の認証情報を生成する

aws iam create-service-specific-credential \
--user-name iamuser \
--service-name cassandra.amazonaws.com

-- 3. DB接続

cd /home/ec2-user/apache-cassandra-3.11.11
./bin/cqlsh cassandra.ap-northeast-1.amazonaws.com 9142 -u "iamuser-at-999999999999" -p "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX" --ssl

-- 4. Keyspace の作成

CREATE KEYSPACE IF NOT EXISTS "ks01"
WITH REPLICATION = {'class': 'SingleRegionStrategy'};

SELECT * from system_schema.keyspaces ;

-- 5. テーブルの作成

USE "ks01" ;

CREATE TABLE IF NOT EXISTS "ks01".tab1 (
col1 text,
col2 text,
col3 int,
col4 text,
PRIMARY KEY (col1,col2))
WITH CLUSTERING ORDER BY (col2 ASC) ;

SELECT * from system_schema.tables WHERE keyspace_name='ks01' ;

SELECT * FROM system_schema.columns WHERE keyspace_name = 'ks01' AND table_name = 'tab1' ;

-- 6. データの作成、読み込み、更新、削除

USE "ks01" ;

CONSISTENCY LOCAL_QUORUM;

INSERT INTO "ks01".tab1 (col1, col2, col3, col4)
VALUES ('000-00','Engineering',10,'200-00') ;

SELECT * FROM "ks01".tab1 ;

vim /home/ec2-user/tab1.csv

col1,col2,col3,col4
100-00,Engineering,11,200-00
200-00,Engineering,12,700-00
300-00,Engineering,13,200-00
400-00,Engineering,14,200-00
500-00,Marketing,15,600-00
600-00,Marketing,16,700-00
700-00,Executive,17,None

※最後の行に改行があるとエラーとなる

COPY tab1 (col1,col2,col3,col4)
FROM '/home/ec2-user/tab1.csv' WITH delimiter=',' AND header=TRUE ;

SELECT * FROM tab1 ;

SELECT * FROM "ks01".tab1 ;
SELECT col1, col2, col4 FROM "ks01".tab1 ;
SELECT * FROM "ks01".tab1 WHERE col1='200-00' ;

SELECT * FROM "ks01".tab1 WHERE col1='500-00' ;
UPDATE "ks01".tab1 SET col3=5 WHERE col1='500-00' AND col2='Marketing' ;
SELECT * FROM "ks01".tab1 WHERE col1='500-00' ;

SELECT * FROM "ks01".tab1 WHERE col1='700-00' AND col2='Executive';
DELETE col4 FROM "ks01".tab1 WHERE col1='700-00' AND col2='Executive';
SELECT * FROM "ks01".tab1 WHERE col1='700-00' AND col2='Executive';

SELECT * FROM "ks01".tab1 WHERE col1='400-00' AND col2='Engineering';
DELETE FROM "ks01".tab1 WHERE col1='400-00' AND col2='Engineering';
SELECT * FROM "ks01".tab1 WHERE col1='400-00' AND col2='Engineering';

-- 7. テーブルの削除
DROP TABLE IF EXISTS "ks01".tab1 ;
SELECT * FROM system_schema.tables WHERE keyspace_name = 'ks01' ;

-- 8. Keyspaceの削除

DROP KEYSPACE IF EXISTS "ks01" ;
SELECT * from system_schema.keyspaces ;

-- 9. クリーンアップ

-- サービス固有の認証情報一覧
aws iam list-service-specific-credentials

-- サービス固有の認証情報の削除

aws iam delete-service-specific-credential \
--service-specific-credential-id XXXXXXXXXXXXXXXXXXXXX