{Keyspaces}Amazon Keyspaces (Apache Cassandra 向け) ポイントインタイムリカバリ

-- 前提: cqlshインストール済

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

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

-- 2. 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

-- 3. Keyspace の作成

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

SELECT * from system_schema.keyspaces ;

-- 4. テーブルの作成

USE "ks01" ;

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

CREATE TABLE IF NOT EXISTS "ks01".tab02 (
col1 text,
col2 text,
col3 int,
col4 text,
PRIMARY KEY (col1,col2))
WITH CUSTOM_PROPERTIES = {
'capacity_mode':{'throughput_mode':'PAY_PER_REQUEST'},
'point_in_time_recovery':{'status':'enabled'}
} AND 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 = 'tab01' ;
SELECT * FROM system_schema.columns WHERE keyspace_name = 'ks01' AND table_name = 'tab02' ;

-- 5. 既存テーブルのPITR有効化

ALTER TABLE "ks01".tab01
WITH custom_properties = {'point_in_time_recovery': {'status': 'enabled'}} ;

-- 6. テストデータ追加

USE "ks01" ;

CONSISTENCY LOCAL_QUORUM;

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

SELECT * FROM "ks01".tab01 ;
SELECT * FROM "ks01".tab02 ;

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

DROP TABLE IF EXISTS "ks01".tab02 ;
SELECT * from system_schema.tables WHERE keyspace_name='ks01' ;

-- 7. 特定時点へのテーブルの復元

SELECT custom_properties
FROM system_schema_mcs.tables
WHERE keyspace_name = 'ks01' AND table_name = 'tab01';

RESTORE TABLE ks01.tab11
FROM TABLE ks01.tab01
WITH restore_timestamp = '2021-08-30T09:04:04Z';

SELECT * FROM "ks01".tab11 ;

-- 8. 削除済みテーブルの復元

SELECT custom_properties
FROM system_schema_mcs.tables_history
WHERE keyspace_name = 'ks01' AND table_name = 'tab02';

RESTORE TABLE ks01.tab12
FROM TABLE ks01.tab02;

SELECT * FROM "ks01".tab12 ;

-- 9. クリーンアップ

-- テーブルの削除
DROP TABLE IF EXISTS "ks01".tab01 ;

DROP TABLE IF EXISTS "ks01".tab11 ;
DROP TABLE IF EXISTS "ks01".tab12 ;

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

-- Keyspaceの削除

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

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

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

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