Hatena::ブログ(Diary)

lambda {|diary| lambda { diary.succ! } }.call(hatena) このページをアンテナに追加 RSSフィード

引っ越しました
 | 

2013-05-06

[][] 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>

スパム対策のためのダミーです。もし見えても何も入力しないでください
ゲスト


画像認証

 | 
Connection: close