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;
※TableStatusがACTIVEになるまで若干時間がかかります
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>
「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=1ap-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 moreap-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 moreap-northeast-1>
Rubyとの連携
末尾に「|」をつけると、Rubyコードにデータを渡せます。
ap-northeast-1> select all * from employees limit 3 | size;
3ap-northeast-1> select all * from employees limit 3 | emp_no.avg;
38572ap-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 listSHOW REGIONS
displays a region listSHOW CREATE TABLE table_name
displays a CREATE TABLE statementCREATE 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 tableDROP TABLE table_name
deletes a tableALTER TABLE table_name READ = num, WRITE = num
updates the provisioned throughputGET {*|attrs} FROM table_name WHERE key1 = '...' AND ...
gets itemsINSERT INTO table_name (attr1, attr2, ...) VALUES ('val1', 'val2', ...), ('val3', 'val4', ...), ...
creates itemsUPDATE table_name {SET|ADD} attr1 = 'val1', ... WHERE key1 = '...' AND ...
UPDATE ALL table_name {SET|ADD} attr1 = 'val1', ... [WHERE attr1 = '...' AND ...] [LIMIT limit]
updates itemsDELETE FROM table_name WHERE key1 = '...' AND ..
DELETE ALL FROM table_name WHERE [WHERE attr1 = '...' AND ...] [ORDER {ASC|DESC}] [LIMIT limit]
deletes itemsSELECT {*|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.htmlDESC[RIBE] table_name
displays information about the tableUSE region_or_endpoint
changes an endpointNEXT
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-KeyConditionsScan (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_scriptex) 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_commandex) 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 versionap-northeast-1>