ddbcli - 対話型のDynamoDBクライアント

https://bitbucket.org/winebarrel/ddbcli
DynamoDBのmysqlコマンド的なクライアントを作りました。ほんとはAWS Tools Hackathonでこのネタをやろうとしていたのですが、DynamoDBのAPIにさわり始めたらあれよあれよと実装が進んでしまいまして。本番どうするかな…

§導入

最初にgemでインストールしてから、環境変数を設定します。

shell> gem install ddbcli
shell> export AWS_ACCESS_KEY_ID='...'
shell> export AWS_SECRET_ACCESS_KEY='...'
shell> export DDB_REGION=ap-northeast-1
shell> ddbcli # プロンプト表示

ddbcliコマンドを実行すると、以下のようなプロンプトが表示されます。


ap-northeast-1> show tables;
[
"employees"
]
// 1 row in set (0.33 sec)

ap-northeast-1>

§テーブルの作成

JSONをそのまま書くのは手間なので、各アクションはSQLっぽい文法で実行できるようにしています。
テーブルの作成は以下の通り。


ap-northeast-1> create table foo (
-> hoge string hash,
-> fuga number range,
-> index my_idx (piyo string) all)
-> read = 1, write = 1;

create table foo (
  hoge string hash,
  fuga number range,
  index my_idx (piyo string) all
) read = 1, write = 1;


ap-northeast-1> desc foo;
{
"AttributeDefinitions": [
{
"AttributeName": "fuga",
"AttributeType": "N"
},
{
"AttributeName": "hoge",
"AttributeType": "S"
},
{
"AttributeName": "piyo",
"AttributeType": "S"
}
],
"CreationDateTime": 1367815317.165,
"ItemCount": 0,
"KeySchema": [
{
"AttributeName": "hoge",
"KeyType": "HASH"
},
{
"AttributeName": "fuga",
"KeyType": "RANGE"
}
],
"LocalSecondaryIndexes": [
{
"IndexName": "my_idx",
"IndexSizeBytes": 0,
"ItemCount": 0,
"KeySchema": [
{
"AttributeName": "hoge",
"KeyType": "HASH"
},
{
"AttributeName": "piyo",
"KeyType": "RANGE"
}
],
"Projection": {
"ProjectionType": "ALL"
}
}
],
"ProvisionedThroughput": {
"NumberOfDecreasesToday": 0,
"ReadCapacityUnits": 1,
"WriteCapacityUnits": 1
},
"TableName": "foo",
"TableSizeBytes": 0,
"TableStatus": "CREATING"
}

ap-northeast-1>

※TableStatusがACTIVEになるまで若干時間がかかります

「SHOW CREATE TABLE」で、CREATE文の表示も出来ます。


ap-northeast-1> show create table foo;
CREATE TABLE `foo` (
`hoge` STRING HASH,
`fuga` NUMBER RANGE,
INDEX `my_idx` (`piyo` STRING) ALL
) read=1, write=1

ap-northeast-1>

§データを入れる

INSERTでデータを入れます。


ap-northeast-1> insert into foo (hoge, fuga, piyo) values ('AAA', 100, 'BBB');
// 1 row changed (0.39 sec)
ap-northeast-1> select all * from foo;
[
{"fuga":100,"hoge":"AAA","piyo":"BBB"}
]
// 1 row in set (0.08 sec)

ap-northeast-1>

BULK INSERTも使えます。


ap-northeast-1> insert into foo (hoge, fuga, piyo) values ('CCC', 101, 'DDD'), ('EEE', 201, 'FFF') ;
// 2 rows changed (0.44 sec)
ap-northeast-1>

§データを取り出す

SELECT(Query)/SELECT ALL(Scan)でデータを取り出します。
Queryアクションは高速ですがキー属性を指定する必要があり、ちょっとデータを見るだけの時には不便です。
Scanアクションは全件走査になりますが、Queryより使えるオペレーターが多く、条件なし(フィルタなし)でもデータを取得できます。
http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/QueryAndScan.html

SELECT


ap-northeast-1> select * from foo where hoge = 'AAA' and fuga >= 100;
[
{"fuga":100,"hoge":"AAA","piyo":"BBB"}
]
// 1 row in set (0.05 sec)

ap-northeast-1>

ローカルセカンダリインデックスを使う場合はUSE INDEXでインデックスを指定します。


ap-northeast-1> select * from foo use index (my_idx) where hoge = 'AAA' and piyo = 'BBB';
[
{"fuga":100,"hoge":"AAA","piyo":"BBB"}
]
// 1 row in set (0.37 sec)

ap-northeast-1>

デバッグモードにすると、実際に投げているHashオブジェクトが表示されます。


ap-northeast-1> .d t
ap-northeast-1> select * from foo use index (my_idx) where hoge = 'AAA' and piyo = 'BBB';
---request begin---
Action: Query
{"TableName"=>"foo",
"IndexName"=>"my_idx",
"KeyConditions"=>
{"hoge"=>{"ComparisonOperator"=>"EQ", "AttributeValueList"=>[{"S"=>"AAA"}]},
"piyo"=>{"ComparisonOperator"=>"EQ", "AttributeValueList"=>[{"S"=>"BBB"}]}}}

---request end---
---response begin---
{"Count"=>1,
"Items"=>[{"hoge"=>{"S"=>"AAA"}, "piyo"=>{"S"=>"BBB"}, "fuga"=>{"N"=>"100"}}]}

---response end---
[
{"fuga":100,"hoge":"AAA","piyo":"BBB"}
]
// 1 row in set (0.05 sec)

ap-northeast-1>

SELECT ALL

Scanの場合、特に条件を指定しなくてもデータを取得できます。


ap-northeast-1> select all * from foo;
[
{"fuga":100,"hoge":"AAA","piyo":"BBB"},
{"fuga":201,"hoge":"EEE","piyo":"FFF"},
{"fuga":101,"hoge":"CCC","piyo":"DDD"}
]
// 3 rows in set (0.68 sec)

ap-northeast-1>

また、インデックスを指定しなくてもフィルタリングは可能です。


ap-northeast-1> select all * from foo where piyo = 'FFF';
[
{"fuga":201,"hoge":"EEE","piyo":"FFF"}
]
// 1 row in set (0.35 sec)

ap-northeast-1>

その他

MySQLと同じように\Gで表示を変更できます。


ap-northeast-1> select all * from foo where piyo = 'FFF' \G
[
{
"fuga": 201,
"hoge": "EEE",
"piyo": "FFF"
}
]
// 1 row in set (0.13 sec)

ap-northeast-1>

§データを更新する

UPDATE/UPDATE ALLでデータを更新します。
UPDATEは通常のUpdateItemアクションです。キーを指定しての更新しか出来ません。
UPDATE ALLは裏でScanを行っているので、Scanでつかえるオペレータでまとめて更新できます(遅いですが)。

UPDATE


ap-northeast-1> update foo set xxx = 'XXX' where hoge = 'AAA' and fuga = 100;
// 1 row changed (0.07 sec)
ap-northeast-1> select all * from foo;
[
{"fuga":100,"hoge":"AAA","piyo":"BBB","xxx":"XXX"},
{"fuga":201,"hoge":"EEE","piyo":"FFF"},
{"fuga":101,"hoge":"CCC","piyo":"DDD"}
]
// 3 rows in set (0.06 sec)

ap-northeast-1>

RANGEキーがある場合、HASHキーだけでは更新できないようです。SELECTはHASHキーだけでいいのに何でだろう、、、

UPDATE ALL


ap-northeast-1> update all foo set zzz = 'ZZZ';
// 3 rows changed (0.49 sec)
ap-northeast-1> select all * from foo;
[
{"fuga":100,"hoge":"AAA","piyo":"BBB","xxx":"XXX","zzz":"ZZZ"},
{"fuga":201,"hoge":"EEE","piyo":"FFF","zzz":"ZZZ"},
{"fuga":101,"hoge":"CCC","piyo":"DDD","zzz":"ZZZ"}
]
// 3 rows in set (0.07 sec)

ap-northeast-1>

属性の削除

NULLをセットすると属性を削除できます。


ap-northeast-1> update all foo set zzz = null;
// 3 rows changed (0.57 sec)
ap-northeast-1> select all * from foo;
[
{"fuga":100,"hoge":"AAA","piyo":"BBB","xxx":"XXX"},
{"fuga":201,"hoge":"EEE","piyo":"FFF"},
{"fuga":101,"hoge":"CCC","piyo":"DDD"}
]
// 3 rows in set (0.07 sec)

ap-northeast-1>

ADD

「UPDATE テーブル名 SET」の代わりに「UPDATE テーブル名 ADD」を使うと、Itemへの処理にADDを使います。
http://docs.aws.amazon.com/amazondynamodb/latest/APIReference/API_UpdateItem.html#DDB-UpdateItem-request-AttributeUpdates


ap-northeast-1> update all foo set zzz = 100;
// 3 rows changed (0.30 sec)
ap-northeast-1> select all * from foo;
[
{"fuga":100,"hoge":"AAA","piyo":"BBB","xxx":"XXX","zzz":100},
{"fuga":201,"hoge":"EEE","piyo":"FFF","zzz":100},
{"fuga":101,"hoge":"CCC","piyo":"DDD","zzz":100}
]
// 3 rows in set (0.06 sec)

ap-northeast-1> update all foo add zzz = 1000; /* set -> add */
// 3 rows changed (2.52 sec)
ap-northeast-1> select all * from foo;
[
{"fuga":100,"hoge":"AAA","piyo":"BBB","xxx":"XXX","zzz":1100},
{"fuga":201,"hoge":"EEE","piyo":"FFF","zzz":1100},
{"fuga":101,"hoge":"CCC","piyo":"DDD","zzz":1100}
]
// 3 rows in set (0.05 sec)

ap-northeast-1>

数値に数値をADDすると加算、配列に配列をADDすると配列の結合等、パターンがあるようです。

§データを削除する

DELETE/DELETE ALLでデータを削除します。違いはUPDATEと同じです。


ap-northeast-1> delete from foo where hoge = 'AAA' and fuga = 100;
// 1 row changed (0.27 sec)
ap-northeast-1> select all * from foo;
[
{"fuga":201,"hoge":"EEE","piyo":"FFF","zzz":1100},
{"fuga":101,"hoge":"CCC","piyo":"DDD","zzz":1100}
]
// 2 rows in set (0.07 sec)

ap-northeast-1> delete all from foo;
// 2 rows changed (0.18 sec)
ap-northeast-1> select all * from foo;
[
]
// 0 row in set (0.05 sec)

ap-northeast-1>

データが大量にある場合、反映までには時間がかかるようです。

§その他

NEXT

データが一度に表示されない場合、NEXTで次のデータを表示できます。


ap-northeast-1> select all * from employees limit 3;
[
{"birth_date":"1954-12-16","emp_no":35176,"first_name":"Jiafu","gender":"M","hire_date":"1998-03-05","last_name":"Wilharm"},
{"birth_date":"1960-04-16","emp_no":15886,"first_name":"Kish","gender":"M","hire_date":"1986-12-09","last_name":"Zuberek"},
{"birth_date":"1964-05-05","emp_no":13335,"first_name":"Val","gender":"F","hire_date":"1994-05-25","last_name":"Akaboshi"}
]
// 3 rows in set (0.04 sec)
// has more

ap-northeast-1> next;
[
{"birth_date":"1955-06-20","emp_no":40627,"first_name":"Rance","gender":"M","hire_date":"1992-05-28","last_name":"Hemaspaandra"},
{"birth_date":"1953-10-05","emp_no":15337,"first_name":"Masaru","gender":"M","hire_date":"1988-08-07","last_name":"Radivojevic"},
{"birth_date":"1961-02-23","emp_no":17502,"first_name":"Gor","gender":"M","hire_date":"1990-01-03","last_name":"Moehrke"}
]
// 3 rows in set (0.05 sec)
// has more

ap-northeast-1>

Rubyとの連携

末尾に「|」をつけると、Rubyコードにデータを渡せます。


ap-northeast-1> select all * from employees limit 3 | size;
3

ap-northeast-1> select all * from employees limit 3 | emp_no.avg;
38572

ap-northeast-1> select all * from employees limit 3 | self.class;
"Array"

ap-northeast-1>

DynamoDBから取得したオブジェクト(たいていの場合はHashの配列)のコンテキストでコードが評価されます。

Arrayにgroup_by/avg/sumなどのメソッドを追加しています。


ap-northeast-1> select all * from employees where first_name begins_with 'Al' | group_by(:gender) {|i| i.length };
{
"F": 76,
"M": 107
}

ap-northeast-1>

help

.h でヘルプが出ます。


ap-northeast-1> .h
##### Query #####

SHOW TABLES
displays a table list

SHOW REGIONS
displays a region list

SHOW CREATE TABLE table_name
displays a CREATE TABLE statement

CREATE TABLES table_name (
key_name {STRING|NUMBER|BINARY} HASH
[, key_name {STRING|NUMBER|BINARY} RANGE]
[, INDEX index1_name (attr1 {STRING|NUMBER|BINARY}) {ALL|KEYS_ONLY|INCLUDE (attr, ...)}
, INDEX index2_name (attr2 {STRING|NUMBER|BINARY}) {ALL|KEYS_ONLY|INCLUDE (attr, ...)}
, ...]
) READ = num, WRITE = num
creates a table

DROP TABLE table_name
deletes a table

ALTER TABLE table_name READ = num, WRITE = num
updates the provisioned throughput

GET {*|attrs} FROM table_name WHERE key1 = '...' AND ...
gets items

INSERT INTO table_name (attr1, attr2, ...) VALUES ('val1', 'val2', ...), ('val3', 'val4', ...), ...
creates items

UPDATE table_name {SET|ADD} attr1 = 'val1', ... WHERE key1 = '...' AND ...
UPDATE ALL table_name {SET|ADD} attr1 = 'val1', ... [WHERE attr1 = '...' AND ...] [LIMIT limit]
updates items

DELETE FROM table_name WHERE key1 = '...' AND ..
DELETE ALL FROM table_name WHERE [WHERE attr1 = '...' AND ...] [ORDER {ASC|DESC}] [LIMIT limit]
deletes items

SELECT {*|attrs|COUNT(*)} FROM table_name [USE INDEX (index_name)] [WHERE key1 = '...' AND ...] [ORDER {ASC|DESC}] [LIMIT limit]
SELECT ALL {*|attrs|COUNT(*)} FROM table_name [WHERE attr1 = '...' AND ...] [LIMIT limit]
queries using the Query/Scan action
see http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/QueryAndScan.html

DESC[RIBE] table_name
displays information about the table

USE region_or_endpoint
changes an endpoint

NEXT
displays a continuation of a result
(NEXT statement is published after SELECT statement)


##### Type #####

String
'London Bridge is...', "is broken down..." ...

Number
10, 100, 0.3 ...

Binary
x'123456789abcd...', x"123456789abcd..." ...

Identifier
`ABCD...` or Non-keywords


##### Operator #####

Query (SELECT)
= | <= | < | >= | > | BEGINS_WITH | BETWEEN
see http://docs.aws.amazon.com/amazondynamodb/latest/APIReference/API_Query.html#DDB-Query-request-KeyConditions

Scan (SELECT ALL)
= | <> | != | <= | < | >= | > | NOT NULL | NULL | CONTAINS | NOT CONTAINS | BEGINS_WITH | IN | BETWEEN
see http://docs.aws.amazon.com/amazondynamodb/latest/APIReference/API_Scan.html#DDB-Scan-request-ScanFilter


##### Pass to Ruby/Shell #####

Ryby
query | ruby_script

ex) SELECT ALL * FROM employees WHERE gender = 'M' | birth_date.map {|i| Time.parse(i) };
[
"1957-09-16 00:00:00 +0900",
"1954-12-16 00:00:00 +0900",
"1964-05-23 00:00:00 +0900",
...

Shell
query ! shell_command

ex) SELECT ALL * FROM employees LIMIT 10 ! sort;
{"birth_date"=>"1957-09-16", "emp_no"=>452020,...
{"birth_date"=>"1963-07-14", "emp_no"=>16998, ...
{"birth_date"=>"1964-04-30", "emp_no"=>225407,...
...


##### Command #####

.help displays this message
.quit | .exit exits sdbcli
.consistent (true|false)? displays ConsistentRead parameter or changes it
.debug (true|false)? displays a debug status or changes it
.retry NUM? displays number of times of a retry or changes it
.retry_interval SECOND? displays a retry interval second or changes it
.timeout SECOND? displays a timeout second or changes it
.version displays a version

ap-northeast-1>