2021年の技術部新卒研修で社内CTFの運営・作問をしました


こんにちは!2021年技術部新卒研修を担当しました、秦です。社内ではがはくちゃん(5さい)と名乗っています。

2020年に引き続きオンラインベースで行われた技術部新卒研修ですが、今年は後半の部のキックオフとして社内CTFを実施しました。 昨年入社の身で大変僭越ながら、その作問・運営をさせていただいたのでその振り返りを書かせていただきます。

これからCTFの作問をするぞ〜‼でもなにをすればいいかわからないよ〜‼という2ヶ月前のわたしのような元気なエンジニアの方に届けば良いなと思っています(もしくは、エッ……カヤックって新卒研修でCTFなんかやるんだ……と思ってもらえたら良いですね)。



経緯


「今年の新卒研修担当には画伯を拉致します」

わたし「えっ?」

「画伯にはCTFを作ってもらおうとおもいます!」

わたし「えっ??」

「社内にCTF経験者特にいないから画伯だけが頼りです!」

わたし「えっ???」


今まで弊社で社内CTFを開催したという事例はなく、さらに社内のエンジニアのほとんどはCTF未経験者(名前は聞いたことある程度)ということだったのでワオ……という感じではありましたが、たのしそうだったので快諾させていただきました。ありがとうございます



問題の構想を練る

今回は社内CTFという形ではありますが、Webエンジニアの新卒研修なので問題はWebに絞りつつ全問Webだと疲れちゃうよな〜ということで、味変程度にMiscを混ぜて以下の10問(Welcomeを除いて)を作りました。

f:id:mwc922_hsm:20210426171319p:plain


スコアサーバにはCTFdを使わせていただいています。本当はfbctfを使う予定だったのですがインストーラがaptにしか対応していなくて、うっかり「ディストリビューションは選べるならArchがいいです‼」と元気よく注文してしまったので変更しました。 CTFd最高。CTFdしか勝たん。いつもありがとうございます。

github.com


作問時に気をつけた点としては

  • エスパー問を作らない(それはそう)
  • 新卒研修の一環であることを意識する
    • 基礎的な知識で解けるようにする
    • ある程度まんべんなく要素を入れ込む
    • 解いている間、解けたときの爽快感
  • 問題同士が似ないようにする

あたりでしょうか。


特に、「新卒研修である」というところに重点を置いたので、CTFを知っている人向けに話すとだいたい「SECCON Beginners CTFの2ステップぐらい前」ぐらいの温度感だと思ってもらえばいいんじゃないかな、というぐらいの難易度で考えています。

大会時間は3時間目安ということでお話をいただいていたので、SECCON Beginners CTF ぐらいの難易度の大会でWebの100点問題がギリギリ解けるか解けないかぐらいの(よわい……)わたしが3時間掛けてギリギリ全完できるかな〜どうだろうな〜ぐらいの10問を揃えたつもりです。


具体的な作問のフローとしては、まず始めに最低限揃えるべきWeb周りの知識や攻撃手法をリスティングするところからはじめました。画像はその様子です。

f:id:mwc922_hsm:20210426172105p:plain

実際はこのうち初歩的すぎるな〜という問題を抜いたりしてますが、まあ大まかにはこの通りに出しました。50点、100点の問題はここから作っています。

200点問題に関しては少し頭を捻ったり与えられた情報を大事にして複合的なアプローチで解く問題を増やしています。

全体的に簡単なものを多めに、配点の高い問題はそこまでの問題を解けていれば知識量的には十分解けるように、というのを意識しています。


テーマを決めたらまず架空のWrite-up(CTFの大会において、解いた問題を解説したブログ記事などをWrite-upと呼びます)を書いて想定解の流れを定め、そこからどんな見た目(メモアプリだったり、ファイルアップローダだったり)にするかね〜という肉付けをして実装、というのが大まかな流れでした。



作問記

例として2問ほど作問について詳しく触れておきます。


[Web] Very useful uploader (100)

この問題は上で紹介した作問ネタのうち

アップロードしたファイルを実行させちゃうぞ〜っ

ここから膨らませたネタです。そのまま〜!


以下が実装前にメモしてあった架空の Write-up です。

ページを開くとはじめから生意気な画像ファイルが数点と flag_encrypted.txt が用意されている。生意気な画像ファイルの中に「秘密鍵は環境変数に隠しておいた」とあるので、どうにかして環境変数を読み出すことを考える。アップローダは拡張子指定とかがされていないので試しに printenv する PHP ファイルとかを置いて踏んでみると秘密鍵が出てくるのであとはソイヤとして終わり

この問題はこの段階からいくらか変わっていて、ちょっと単純すぎるかな〜っというので多少捻りました。

具体的には、以下のようなコードを含む index.php のソースコードを配布しました(まあまあ長いので必要な部分以外は省略しています)。

include './get_user_dir.php';
$dir = get_user_dir();
include $dir . '/encrypt.php';
$flag = "XXXXXXXXXXXXXXXXXXXXXXXXXXXX";
$flag = my_encrypter($flag);

この my_encrypter という関数を[与えられた文字列をそのまま出力する]ように書き換えた encrypt.php を上書きアップロードしてください、という問題です。コードがヒントとして与えられる問題は他になかったので、そういう要素を入れられたのは良かったポイントですね。


この問題、テストプレイの時点で「 index.php を読んできてダンプするコードを書けばフラグが取ってこれてしまう」みたいな抜け穴がありまして、指摘していただいてから急遽カレントディレクトリに置いたダミーのPHPファイルをApacheのrewrite_modで上書きするように修正したのですが、如何せんApacheを扱うのがはじめてで見事にルーティングのバグを生み、終了間際まで実は解けない状態だった!という驚きの状況が発生しちゃったりしていました。

お詫びにアディショナル競技時間を20分追加しました。


そもそもrewriteなんてことができるのか〜っていうところからわたしは初耳だったので(インフラ周りの知識はギリギリNginxでリバースプロキシが作れる程度にしかもっていない貧相な知識量をしたわたしだ)勉強になる〜〜〜〜〜〜〜〜むずかし〜〜〜〜〜〜〜〜という感じでした。参加者のみなさん本当にすみません。情けな。

しかも修正後も encrypt.php をダンプすると暗号化部分が抜けちゃうという穴があったのでわあ……という感じです。任意コード実行系の問題を作るときはコードをダンプされないように気をつけましょう。情けな。



[Web] 5:03 PM (200)

この問題はタイトルから考えたものです。他にもいくつかタイトルから考えた問題があるのですが(Five years oldとかもそうです)、「200点作らなきゃいけないけど作問ネタ降ってこね〜〜〜〜〜〜〜〜!!!!期限だけが迫ってくるぜ!!!!!!!!!!!」と焦燥しながらApple Musicを眺めていたら同名の曲が目についたので「あ、ここから問題作れそうだな」と練ったらできた。

ちなみに作問のネタになった曲はわたしが一番好きなとあるアーティストの楽曲なのですが、作問のきっかけが好きなものだと自分のテンションも上がるし、タイトルに恥ずかしくない問題にしようと思えるのでとても良いです。この問題はかなり自分の中でもお気に入りの問題になりましたね。

解いてくれた人からの評判も結構良くてうれしかったです。


架空のWrite-upがこちら。

  1. 表示されたサーバログを見ると 6:00 PM 以降のものしかないが、ソースを見てみるとタイムスタンプをパラメータで取れそうな痕跡が残っているので 5:03 PM を含むように指定しリクエストを送り直す
  2. 5:03 PM のログを見ると WebShell からコマンド操作を行われた痕跡が残っているのでこれを利用することを考える
  3. とりあえずログの通りに何個か試してみると、 flag というファイルがあるっぽい
  4. file flag とかするとELFファイルっぽいが、実行権限は与えられていなそう
  5. 適当に権限がありそうなディレクトリを探してその中にコピーして chmod +x flag./flag するとフラグが取れるみたい

この問題はほとんどこのままですねー。違いは実行ファイルが隠しファイルになっていることぐらいか。


実際に出題したログがこんな感じです。

f:id:mwc922_hsm:20210426173842p:plain


サーバログに残っている3つのコマンドの実行結果から

  • 自分が書き込み・実行可能なディレクトリが存在する
  • PATHに怪しげなディレクトリがある

という情報をしっかり汲み取れなければ問題が解けないので、今回の難易度感の中では良い感じにパズルゲーム感があったかなと思います。


実行ファイルはうっかり cat flag / strings flag とかでフラグを取られてしまうことがないようにフラグの文字列をバラした上で最適化なしのコンパイルを掛けて簡単に取られてしまうことを防ぎます。

また、Mac上でELFファイルを動かせる人であれば「wget とかしちゃお😃」みたいな発想になってしまうことがテストプレイ時にわかったのでこれもhostnameを参照して問題サーバのものでない場合エラーメッセージを吐くようにしています。

簡易的な対策だったので、できる人は実行ファイルを簡単に逆解析してhostnameを参照していることを特定しDockerコンテナ上で動かしたり分岐している部分のバイナリコードをNOPに書き換えてフラグを吐かせたり(Reversingっぽい、たのしい)できちゃったのでCTFのWeb問としてはあまり適切でないかな〜という感じもありましたが、正攻法でやったほうがどう考えても早くて簡単だということもあり、そこまでしたい人はご自由に、ぐらいのつもりで残したままにしました。

わたしは元々CTFはWebよりReversingの方が好きなひとだったので別解の話が弾んで非常にたのしかったです。


実はこの問題はわたしの知識不足で「WebShell上で操作をした際のカレントディレクトリにも実行ファイルをコピーできてしまい、特定の方法を使うと他の人がコピーした実行ファイルが見えてしまう」という穴があったので(大半の人は想定解通りに解いてくれましたが)、本来ならアクセスごとにコンテナを立てて隔離するだとかユーザ切り替え、chrootを活用する、みたいな上手な処理をすべきでしたね、という反省がありました。

今回は全体的にそういう穴の塞ぎ方みたいなところまで気が回らなかったり、そもそも割と孤独な作問期間だったのもあり(上手に質問をすれば上手に知見が返ってきていたのかもしれないが、作るのにいっぱいいっぱいで整理された質問文を考える余裕もなかった……)、もしも次にまた作問する機会があれば上手な構築をがんばりたいですね。



社内CTFを開催してみての感想

例年(2019年まで)は新卒研修の最終日に社内ISUCONを行うのが恒例だったようですが、たまたまちょっとだけ経験のあるわたしが新卒研修担当になって研修の題材として社内CTFという企画をさせていただいて、今まで社内でCTFに言及している人はほぼいなかったところを想像以上にたくさんのエンジニアの先輩に参加してもらい好意的な感想もいただけて非常に楽しく、わたしにとっても会社にとっても実りある試みになったかと思います!

来年からの新卒研修はまたどうなるのかわからないですが、「この年はこういうこともしたんだよね〜」という前向きな実績として残せた(よね?)ことを非常にうれしく思います。


当日は大会時間中スコアボードを眺めるのもすごくたのしかったです。これは試合終了後のスコアグラフですが、エンジニアのみなさんがMTGの合間を縫って熱戦を繰り広げる様子が見て取れます。たのしかったです

f:id:mwc922_hsm:20210507173953p:plain
スコアボード


……という良い振り返りと同時に、もっと早くテストプレイを始められる状態にしておくべきでしたね、という反省が非常に強いです。

想定外の抜け穴を塞ぐためというのは当然そうなのですが、「これは問題として想定解に辿り着くまでの道筋が不親切すぎない?」とか「これがこの配点なのは簡単すぎるんじゃないかなあ」とか、フィードバックを受けてテコ入れをした問題がまるっきり歯応えの違う問題に生まれ変わったりするので。大会中にスコアボードを眺めながら意見聞いておいてよかった〜〜〜と本当に思いました。

その上、テコ入れをしていると「問題の終盤のアプローチが似通ってしまう(特に配点の高い問題)」みたいなことが起こり得るので(引き出しの少なさに問題がありますね)、そういう事態が起きても大丈夫なように問題修正用のマージンはしっかり取っておくべきですね〜という感じです。実装にどれだけ手数が掛かるかにもよりますが、作問:実装:修正=1:1:1ぐらいにバランス良く時間を割けるのが理想だと思います。


実際に300点問題としてひとつ大きいのを作っておくか〜と作問まで済ませていた Zip Slip Bad-trip という問題があったのですが(問題文の通りZip Slipという名前付きの脆弱性を利用した任意コード実行の問題になる予定でした)、上記でも紹介した Very useful uploader に作問修正・テコ入れを繰り返した結果解法アプローチが似てしまい、作問修正からするには時間が足りなかったという理由でお蔵入りになりました。問題名すごい気に入ってたのにな。声に出して読みたい問題名ですね。


あと新卒研修という意味では本当は実用性のあるテクニックなんかをもっと盛り込みたくて、Gitコマンドを駆使してあれこれする、みたいな問題もMisc枠で考えていた(具体的には、"環境変数をリポジトリにコミットしてしまったのでこいつを歴史から消し去りたいです‼"みたいな問題とか)のですが、実際業務コードでそんな巨大歴史改変されちゃ堪ったもんじゃないので不適切だろうなと思いお蔵入りになったりしています。

社内ではSlackが必須ツールなので、Slackのサーチ機能を駆使してわたしのtimesの奥深くからフラグになる文字列を拾ってきてください!みたいな問題も考えていましたがお蔵入りになったりしています。結構いろんな案がお蔵入りになりました。

よくあるCTFの大会ではMiscがそういう枠になりがちな気がしますが、CTF(というかJeopardy)という大会形式はセキュリティ文脈だけでなく開発小ネタクイズみたいなものにも使えるので新卒研修結構向いているんじゃないかなっていう気もしています。「新卒研修としてのCTF」はなかなか作問のしがいがありました。


余談ですが、走り出した初期は「custom emojiをflagとして置いて、reactionでsubmitするスコアサーバbotをつくったら楽しそう」みたいな話も出ていました。結局スコアボードとか問題一覧とかでフロントはいるし既存の使ったほうが良いねってことでお蔵入りになりましたが面白そうなアイデアではあったなあ。残念。


おわり

というわけで拙筆ではありましたが本年度の新卒研修について書かせていただきました。

わたしの作った問題を解いた後の研修プログラムとして新卒のみなさんにも問題を作ってもらい、みんなでわいわいと解くという大会も行いましたが、非常に歯応えのある問題が多くて解いていて楽しかったです!凝ったものが多くてうわ〜凝ってるな〜と思ったので、これについても機会があれば是非ブログに書いていただきたいところですね(昨年はアドベントカレンダーに新卒研修についてを書いている新卒エンジニア多かったけど今年はどうだろう)。

はじめての試みなのもありなかなか反省点も多かったですが、またどこかで活かせる機会が来たらいいねと思っています。


👇弊社に興味をお持ちの方はこちらもどうぞ👇

www.kayac.com

DynamoDB から Redshift へデータ移送する話 (RedshiftのSUPER型の利用事例)

こんにちは。技術部のSRE所属の池田です。

この記事では、昨年の2020年12月にPreviewが発表されたRedshiftの汎用データ型 SUPER の利用事例として、DynamoDBからRedshiftへデータ移送する話をします。

汎用データ型SUPERは、2020/12/9に 『Amazon Redshift が、ネイティブ JSON と半構造化データ処理のサポートを発表 (プレビュー) 』という記事が公開され、2021年04月時点でもPreview状態です。 このSUPER型は、ログやDynamoDBストリームに見られるJSONおよび半構造化データと非常に相性がよく、使い始めてみると【素晴らしい!】と言う感想を得るので、皆様も是非使ってみてください。

背景

2021年4月現在では、Tonamel という大会プラットフォームサービスのデータ基盤 構築業務・整備を行っております。
Tonamelのトーナメント表構築に関しては、昨年のアドベントカレンダーで弊社 谷脇 が熱く語ってくれました。

techblog.kayac.com techblog.kayac.com

こちらのアドベントカレンダーの記事で、一言触れてますがTonamelのトーナメント表構築システムではDynamoDBを採用しています。 データ基盤では、既存のPerl Appが参照するAurora MySQLや、トーナメント表構築システムが参照するDynamoDBなど、様々な場所に蓄積されているデータを統合分析できるようにする必要がありました。 Tonamelのデータ基盤では、その要求を満たすために、Redshiftを利用する選択をしました。
Aurora MySQLに関しては、SUPER型と同じくPreview中の MySQLに対する Amazon Redshift Federated Query を利用することで、困難なくRedshiftからデータにアクセスできました。(そちらについては機会があれば別途記事にしようと思います。)

DynamoDBに関しては、DynamoDB Streamの情報をKinesis Data Streamに流し、Kinesis Data Firehose経由でRedshiftに取り込むことで解決しました。 その際に、SUPER型が非常に役に立ちましたので、今回記事にします。

利用事例

アーキテクチャーとしては、非常にシンプルです。

f:id:ikeda-masashi:20210426120244p:plain
アーキテクチャ
Kinesis Data FirehoseからRedshiftに取り込む部分はクラスメソッド株式会社様の記事を参照していただければ幸いです。 dev.classmethod.jp

このようなアーキテクチャーでDynamoDB StreamのKinesisアダプターを使うことで、DynamoDB 上で変更があった場合のデータの差分をKinesis Data Streamに投入することができます。 Kinesis Data Streamの中には、以下のようなデータが投入されます。

{
  "awsRegion": "ap-northeast-1",
  "dynamodb": {
    "ApproximateCreationDateTime": 1614246592241,
    "Keys": {
      "HashKey": {
        "S": "HASHKEY-96b8-6822cfdd73ba"
      },
      "SortKey": {
        "S": "SORTKEY-96b8-6822cfdd73ba"
      }
    },
    "NewImage": {
      "NumberValue": {
        "N": "1234567890"
      },
      "StringValue": {
        "S": "super string"
      },
      "ListMapValue": {
        "L": [
          {
            "M": {
              "StringValue": {
                "S": "b478e725-69c0-4b3a-8674-e229544e3f4b"
              },
              "NumberValue": {
                "N": "012345678"
              }
            }
          }
        ]
      },
      "BoolValue": {
        "BOOL": false
      },
      "HashKey": {
        "S": "HASHKEY-96b8-6822cfdd73ba"
      },
      "SortKey": {
        "S": "SORTKEY-96b8-6822cfdd73ba"
      }
    },
    "OldImage": {
      "NumberValue": {
        "N": "1234567890"
      },
      "StringValue": {
        "S": "super string"
      },
      "ListMapValue": {
        "L": [
          {
            "M": {
              "StringValue": {
                "S": "b478e725-69c0-4b3a-8674-e229544e3f4b"
              },
              "NumberValue": {
                "N": "012345678"
              }
            }
          }
        ]
      },
      "BoolValue": {
        "BOOL": true
      },
      "HashKey": {
        "S": "HASHKEY-96b8-6822cfdd73ba"
      },
      "SortKey": {
        "S": "SORTKEY-96b8-6822cfdd73ba"
      }
    },
    "SizeBytes": 800
  },
  "eventID": "00000000-0000-0000-0000-000000000000",
  "eventName": "MODIFY",
  "userIdentity": null,
  "recordFormat": "application/json",
  "tableName": "dummy_develop",
  "eventSource": "aws:dynamodb"
}

データ加工・変換について

このアーキテクチャーの場合、Kinesis Data Firehoseにデータ加工・変換用のLambda関数を設定することで、Streamに流れる情報をRedshiftに取り込むことが可能です。
従来では、Redshiftは半構造化データをそのまま取り扱うことができませんでしたので、上記のデータをRedshiftに取り込むためにはFlattenする必要がありました。 具体的には以下のようなデータへの変換が必要でした。

{
  "aws_region": "ap-northeast-1",
  "approximate_creation_date_time": 1614246592241,
  "hash_key": "HASHKEY-96b8-6822cfdd73ba",
  "sort_key": "SORTKEY-96b8-6822cfdd73ba",
  "new_image__number_value": 1234567890,
  "new_image__string_value": "super string",
  "new_image__list_map_value__string_value": "b478e725-69c0-4b3a-8674-e229544e3f4b",
  "new_image__list_map_value__number_value": 012345678,
  "new_image__list_map_value__bool_value": false,
  "new_image__hash_key": "HASHKEY-96b8-6822cfdd73ba",
  "new_image__sort_key": "SORTKEY-96b8-6822cfdd73ba",
  "old_image__number_value": 1234567890,
  "old_image__string_value": "super string",
  "old_image__list_map_value__string_value": "b478e725-69c0-4b3a-8674-e229544e3f4b",
  "old_image__list_map_value__number_value": 012345678,
  "old_image__list_map_value__bool_value": false,
  "old_image__hash_key": "HASHKEY-96b8-6822cfdd73ba",
  "old_image__sort_key": "SORTKEY-96b8-6822cfdd73ba",
  "size_bytes": 800,
  "event_iD": "00000000-0000-0000-0000-000000000000",
  "event_name": "MODIFY",
  "user_identity": null,
  "record_format": "application/json",
  "table_name": "dummy_develop",
  "event_source": "aws:dynamodb"
}
{
  //list_map_value の2要素目がある場合は追加が必要 
}

このデータ変換が特に困る理由として2点あります。

  • List要素がある場合1要素1レコードに変換する必要がある。
  • DynamoDBのテーブルの構造が変わる度に、このデータ変換をメンテナンスする必要がある。

1個目の困りごと故に、従来ではRedshift Spectrumでarrayやstructなどの型定義をして読み取ることもよくあったのではないでしょうか? その場合でも2個目の困りごとは残ったままです。

RedshiftのSUPER型を用いることで、半構造化データを取り扱えるようになるので、このデータ変換は必要なくなります。 しかし、そのまま取り込むとSQLでの利用時に以下のようなDDLとSELECTクエリになってしまい、取り扱いしづらい状態になります。

CREATE TABLE IF NOT EXISTS ddb_stream (
    awsregion      varchar ENCODE ZSTD,
    dynamodb       super   ENCODE ZSTD,
    eventid        varchar ENCODE ZSTD,
    eventname      varchar ENCODE ZSTD,
    recordformat   varchar ENCODE ZSTD,
    tablename      varchar ENCODE ZSTD,
    eventsource    varchar ENCODE ZSTD
);
SELECT 
    awsregion
    ,dynamodb.keys.hashkey.s::varchar as hash_key
    ,dynamodb.newimage.listmapvalue.l[0].m.stringvalue.s::varchar as new_image__list_map_value__0__string_value
FROM ddb_stream

SQLはcase insensitiveである都合とSUPER型の都合上、読みづらいクエリでアクセスすることになります。 SUPER型を使った場合以下のように、JSON Key をスネークケースにしておく、DynamoDB特有の型構造をFlatttenする程度の加工はしておいたほうが、Redshift上で取り扱いやすくなります。 すべてをFlattenする必要はないので加工の難易度は格段に下がると思います。 具体的には、このような形で変換すると使いやすくなるでしょう。

{
  "aws_region": "ap-northeast-1",
  "dynamodb": {
    "approximate_creation_date_time": 1614246592241,
    "keys": {
      "hash_key": "HASHKEY-96b8-6822cfdd73ba",
      "sort_key": "SORTKEY-96b8-6822cfdd73ba"
    },
    "new_image": {
      "number_value": 1234567890,
      "string_value": "super string",
      "list_map_value": [
        { 
          "string_value": "b478e725-69c0-4b3a-8674-e229544e3f4b",
          "number_value": 012345678,
        }
      ],
      "bool_value": false,
      "hash_key": "HASHKEY-96b8-6822cfdd73ba",
      "sort_key": "SORTKEY-96b8-6822cfdd73ba"
    },
    "old_image": {
      "number_value": 1234567890,
      "string_value": "super string",
      "list_map_value": [
        {
          "string_value": "b478e725-69c0-4b3a-8674-e229544e3f4b",
          "number_value": 012345678,
        }
      ],
      "bool_value": true,
      "hash_key": "HASHKEY-96b8-6822cfdd73ba",
      "sort_key": "SORTKEY-96b8-6822cfdd73ba"
    },
    "size_bytes": 800,
  },
  "event_id": "00000000-0000-0000-0000-000000000000",
  "event_name": "MODIFY",
  "user_identity": null,
  "record_format": "application/json",
  "table_name": "dummy_develop",
  "event_source": "aws:dynamodb"
}

※DynamoDBの型特有のFlattenが難しい場合は、JSON Keyをスネークケースにするだけでも取り扱いやすくなります。 さて、これを取り込む場合は以下のスキーマのテーブルを用意することで取り込めます。

CREATE TABLE IF NOT EXISTS ddb_stream (
    aws_region      varchar ENCODE ZSTD,
    dynamodb        super   ENCODE ZSTD,
    event_id        varchar ENCODE ZSTD,
    event_name      varchar ENCODE ZSTD,
    record_format   varchar ENCODE ZSTD,
    table_name      varchar ENCODE ZSTD,
    event_source    varchar ENCODE ZSTD
);

この ddb_stream テーブルからデータを読み取る場合は、以下のようなSELECTクエリが使えます。

SELECT 
    aws_region
    ,dynamodb.hash_key::varchar as hash_key
    ,dynamodb.new_image.list_map_value[0].string_value::varchar as new_image__list_map_value__0__string_value
FROM ddb_stream 

List要素をそのまま取り扱うことができ、DynamoDBのテーブルの構造の変化由来のメンテナンスからも開放されました。

最新データの取得方法に関して

ここまでで、DynamoDB上で変更があったデータの差分をRedshiftに蓄積していく状態になりました。 データの利用ケースとして、DyanamoDBの最新状態を知りたいというのはよくあるケースだと思います。  ddb_streamに流れるデータは変更の差分なので、最新状態を見るのに単純なクエリではできません。 ですので、以下のようなマテリアライズドビューを作成しておくと、非常に便利だと思います。

CREATE MATERIALIZED VIEW v_ddb  
BACKUP NO
AUTO REFRESH YES
AS 
WITH base as (
    SELECT
        dynamodb.keys.hash_key::varchar(512) as hash_key
        ,dynamodb.keys.sort_key::varchar(512) as sort_key
        ,case 
            when event_name in ('INSERT', 'MODIFY') then dynamodb.new_image
            when event_name = 'REMOVE' then dynamodb.old_image 
        end as item
        ,dynamodb.approximate_creation_date_time::bigint as updated_at
        ,table_name
    FROM ddb_stream
)
SELECT
    hash_key
    ,sort_key
    ,item
    ,updated_at
    ,table_name
FROM (
    SELECT
        *
        ,row_number() over (
                partition by hash_key, sort_key
                order by updated_at desc
        ) as rn
    FROM base
) 
WHERE rn = 1; 

このマテリアライズドビューを用いて、最新のstring_valueを知る場合は、以下のように利用することが可能です。

SELECT 
    hash_key
    ,sort_key
    ,item.string_value::varchar as string_value
FROM v_ddb

list_map_value内のstring_valueについては以下のように利用することが可能です。

SELECT
    d.hash_key
    ,d.sort_key
    ,l.string_value::varchar as string_value
FROM v_ddb as d, ddb.item.list_map_value as l

非常に直感的でわかりやすく利用できます。
その他の、SUPER型のnavigationについては『Querying semistructured data』 の参照をお願いします。具体的な型の検証や利用方法が解説されています。

もちろん、ワークフローエンジン等を用いて更にETLを加えてRedshift Spectrumで参照できるようにRedshift上でFlattenするのも良いです。 SUPER型を用いているので、DynamoDBのスキーマレスな特性をそのままに利用できるのがメリットです。

おわりに

今回はRedshiftのPreview中の機能 SUPER型を用いた、 DynamoDB から Redshift へデータ移送する話でした。 JSONや半構造化データがより取り扱いやすなるRedshiftは、AWS上でのデータ活用に関していろいろな場面で活躍することでしょう。

Redshift愛に満ちているエンジニアの皆様の一助になれば幸いです。

カヤックではデータ活用やSREに興味のあるエンジニアも募集しています