パーティションを持つ

PartiQL AWS Dynamodbに導入され、AWSを作るannouncement 2020年には、開発者の生活を容易にし、SQLに似たコマンドを実行することができます.

どのようなパーティションですか？

A SQL-compatible query language — in addition to already-available DynamoDB operations—to query, insert, update, and delete table data in Amazon DynamoDB. PartiQL makes it easier to interact with DynamoDB and run queries in the AWS Management Console. Because PartiQL is supported for all DynamoDB data-plane operations, it can help improve the productivity of developers by enabling them to use a familiar, structured query language to perform these operations.

Dynamic用のPartiqlにアクセスする方法

All CLI

ウェブコンソール

AWS SDKによるダイナモ

NoSQLワークベンチ

Partiqlのウォークスルーでは、テーブルを使用します.

シングルテーブルデザインCopa America 表.

文のCLI実行のためのAWS曇りシェル.

声明の実行のためのnodejsスニペット.

AWS Webコンソール上のパーティクルエディタ.

DynamoDBテーブルの作成

ダイナモテーブルcopa-america のスキーマpk パーティションキーとsk ソートキーとして.また、オンデマンドのDynamoDBを提供しますbilling-mode PAY_PER_REQUEST .

aws dynamodb create-table --attribute-definitions \
  AttributeName=pk,AttributeType=S \
  AttributeName=sk,AttributeType=S \
 --key-schema \
  AttributeName=pk,KeyType=HASH \
  AttributeName=sk,KeyType=RANGE \
 --billing-mode PAY_PER_REQUEST\
 --table-name copa-america

作成されたテーブルの詳細はコンソールで確認できます.

チーム詳細の挿入

パーティションを挿入する最も簡単な方法はINSERT SQL文と同様のステートメント.

INSERT INTO "copa-america" VALUE {'pk':'TEAM','sk':'Argentina#Group A#1','display_name':'Argentina','team_group':'Group A','ranking':1,'matches_played':4,'matches_won':3,'matches_drew':1,'matches_lost':0,'goals_for':7,'goals_against':2,'goals_difference':5,'team_points':10}

nodejsと同じことができるexecuteStatement API.

const insert_teams = async(event) => {
    let team = event.team
    let teamParams = {
        pk: "TEAM",
        sk: team.name + "#" + team.group + "#" + team.ranking,
        display_name: team.name,
        team_group: team.group,
        ranking: team.ranking,
        matches_played: team.matches_played,
        matches_won: team.matches_won,
        matches_drew: team.matches_drew,
        matches_lost: team.matches_lost,
        goals_for: team.goals_for,
        goals_against: team.goals_against,
        goals_difference: team.goals_difference,
        team_points: team.team_points
    }
    let partiqlStmt = {
        Statement: `INSERT INTO "testing-partiql" VALUE "{'pk':'${teamParams.pk}','sk':'${teamParams.sk}','display_name':'${teamParams.display_name}','team_group':'${teamParams.team_group}','ranking':${teamParams.ranking},'matches_played':${teamParams.matches_played},'matches_won':${teamParams.matches_won},'matches_drew':${teamParams.matches_drew},'matches_lost':${teamParams.matches_lost},'goals_for':${teamParams.goals_for},'goals_against':${teamParams.goals_against},'goals_difference':${teamParams.goals_difference},'team_points':${teamParams.team_points}}"`,
    }
    let response = await dynamodb.executeStatement(partiqlStmt).promise()
    return response
}

同様の特徴でbatchWriteItem , Partiqlを使用すると、ステートメントのバッチ実行を行うことができますbatchExecuteStatement .

aws dynamodb batch-execute-statement --statements \
> '[{"Statement": "INSERT INTO \"copa-america\" VALUE \"{'pk':'TEAM','sk':'Uruguay#Group A#2','display_name':'Uruguay','team_group':'Group A','ranking':2,'matches_played':4,'matches_won':2,'matches_drew':1,'matches_lost':1,'goals_for':4,'goals_against':2,'goals_difference':2,'team_points':7}\"" },  { "Statement": "INSERT INTO \"copa-america\" VALUE \"{'pk':'TEAM','sk':'Paraguay#Group A#3','display_name':'Paraguay','team_group':'Group A','ranking':3,'matches_played':4,'matches_won':2,'matches_drew':0,'matches_lost':2,'goals_for':5,'goals_against':3,'goals_difference':2,'team_points':6}\""}, { "Statement": "INSERT INTO \"copa-america\" VALUE \"{'pk':'TEAM','sk':'Chile#Group A#4','display_name':'Chile','team_group':'Group A','ranking':4,'matches_played':4,'matches_won':1,'matches_drew':2,'matches_lost':1,'goals_for':3,'goals_against':4,'goals_difference':-1,'team_points':5}\"" }, { "Statement": "INSERT INTO \"copa-america\" VALUE \"{'pk':'TEAM','sk':'Bolivia#Group A#5','display_name':'Bolivia','team_group':'Group A','ranking':5,'matches_played':4,'matches_won':0,'matches_drew':0,'matches_lost':4,'goals_for':2,'goals_against':10,'goals_difference':-8,'team_points':0}\"" }, {"Statement": "INSERT INTO \"copa-america\" VALUE \"{'pk':'TEAM','sk':'Brazil#Group b#1','display_name':'Brazil','team_group':'Group b','ranking':1,'matches_played':4,'matches_won':3,'matches_drew':1,'matches_lost':0,'goals_for':10,'goals_against':2,'goals_difference':8,'team_points':10}\"" }, { "Statement": "INSERT INTO \"copa-america\" VALUE \"{'pk':'TEAM','sk':'Peru#Group b#2','display_name':'Peru','team_group':'Group b','ranking':2,'matches_played':4,'matches_won':2,'matches_drew':1,'matches_lost':1,'goals_for':5,'goals_against':7,'goals_difference':-2,'team_points':7}\""}, { "Statement": "INSERT INTO \"copa-america\" VALUE \"{'pk':'TEAM','sk':'Colombia#Group B#3','display_name':'Colombia','team_group':'Group B','ranking':3,'matches_played':4,'matches_won':1,'matches_drew':1,'matches_lost':2,'goals_for':3,'goals_against':4,'goals_difference':-1,'team_points':4}\""},{"Statement": "INSERT INTO \"copa-america\" VALUE \"{'pk':'TEAM','sk':'Ecuador#Group B#5','display_name':'Ecuador','team_group':'Group B','ranking':5,'matches_played':4,'matches_won':0,'matches_drew':3,'matches_lost':1,'goals_for':6,'goals_against':6,'goals_difference':-1,'team_points':3}\"" },{ "Statement": "INSERT INTO \"copa-america\" VALUE \"{'pk':'TEAM','sk':'Venezuela#Group B#5','display_name':'Venezuela','team_group':'Group B','ranking':5,'matches_played':4,'matches_won':0,'matches_drew':2,'matches_lost':2,'goals_for':2,'goals_against':6,'goals_difference':-4,'team_points':2}\""}]'

同様にnodejs実行については、

const insert_teams_bulk = async(event) => {
    let partiqlInsertParams = {
        Statements: []
    }
    for (let team of event.teams) {
        let teamParams = {
            pk: "TEAM",
            sk: team.name + "#" + team.group + "#" + team.ranking,
            display_name: team.name,
            team_group: team.group,
            ranking: team.ranking,
            matches_played: team.matches_played,
            matches_won: team.matches_won,
            matches_drew: team.matches_drew,
            matches_lost: team.matches_lost,
            goals_for: team.goals_for,
            goals_against: team.goals_against,
            goals_difference: team.goals_difference,
            team_points: team.team_points
        }
        let partiqlStmt = {
            Statement: `INSERT INTO "testing-partiql" VALUE "{'pk':'${teamParams.pk}','sk':'${teamParams.sk}','display_name':'${teamParams.display_name}','team_group':'${teamParams.team_group}','ranking':${teamParams.ranking},'matches_played':${teamParams.matches_played},'matches_won':${teamParams.matches_won},'matches_drew':${teamParams.matches_drew},'matches_lost':${teamParams.matches_lost},'goals_for':${teamParams.goals_for},'goals_against':${teamParams.goals_against},'goals_difference':${teamParams.goals_difference},'team_points':${teamParams.team_points}}"`,
        }
        partiqlInsertParams.Statements.push(partiqlStmt)
    }
    let response = await dynamodb.batchExecuteStatement(partiqlInsertParams).promise()
    return response
}

アイテムの取得

検索はSELECT Partiqlを使用した文.

スキャン操作

aws dynamodb execute-statement --statement "select * from \"copa-america\""

結果

新しいコンソールコンソールで利用可能なWebコンソールのパーティションエディタ.

スキャンは、COPAアメリカ　　のすべてのチームを取得する

SELECT * FROM "copa-america" WHERE "pk" = 'TEAM'

Dynamicのために、SQL SQL文がどのようにサポートされるかについてSELECT .

aws dynamodb execute-statement --statement "SELECT display_name FROM \"copa-america\" WHERE \"pk\" = 'TEAM'"
{
    "Items": [
        {
            "display_name": {
                "S": "Argentina"
            }
        },
        {
            "display_name": {
                "S": "Bolivia"
            }
        },
        {
            "display_name": {
                "S": "Brazil"
            }
        },
        {
            "display_name": {
                "S": "Chile"
            }
        },
        {
            "display_name": {
                "S": "Colombia"
            }
        },
        {
            "display_name": {
                "S": "Ecuador"
            }
        },
        {
            "display_name": {
                "S": "Paraguay"
            }
        },
        {
            "display_name": {
                "S": "Peru"
            }
        },
        {
            "display_name": {
                "S": "Uruguay"
            }
        },
        {
            "display_name": {
                "S": "Venezuela"
            }
        }
    ]
}

上記のPartiqlステートメントはpk にWHERE 原因としてSCAN 実行されるが、内部的にはsk レスポンス項目をソートするには

パーティクルクエリ
DynamoDBの問い合わせは、パーティションのキースキーマを利用して、DynamODBテーブルでキーをソートすることができます.

SELECT * FROM "copa-america" WHERE "pk" = 'MATCH' and contains("sk",'ARG')

 aws dynamodb execute-statement --statement "SELECT display_name,match_type,final_score FROM \"copa-america\" WHERE \"pk\" = 'MATCH' and contains(\"sk\",'ARG')"

DynamoDBを問い合わせるとき、インデックスは特定のビューでデータを取得するために重要な役割を果たします.これはまた、SELECT からの声明"table-name"."index-name" .

aws dynamodb execute-statement --statement "SELECT * FROM \"copa-america\".\"team_group-index\" where \"team_group\"='Group A'"

DynamoDBの値の更新

テーブルスキーマで定義されているパーティションとソートキーを持つ項目の更新を行うDynamODbUPDATE-SET Partiql上のステートメント.

UPDATE "copa-america" SET "match_date" = '2021-07-11' WHERE "pk" = 'MATCH' AND "sk" = 'F#ARG#BRA'

レコードの削除

DynamOdbの削除操作はDROP Partiql上のステートメント.

DELETE FROM "copa-america" WHERE "pk" = 'TEAM' AND "sk" = 'Bolivia#Group A#5'

パーティクルエディタ

同様の操作は、パーティションエディタのセクションのWebコンソールで可能です.

PartiSQLエディタでは、いくつかのオプションを選択し、選択した場合には、Partiqlステートメントの構文を簡単にし、開発者フレンドリーになります.

結論

Partiqlを持つDynamoDBは、すべてのDynamODBサポートされている操作のためのシンタックススニペットだけでなく、より多くのdevloperになりますPUT , SCAN , QUERY , UPDATE , DELETE SQL同様のステートメントでINSERT , SELECT , UPDATE , DROP 構造化されたクエリを簡単にする.また、すぐに開始するSQLの背景を持つ新しいdevlopersに役立ちます.