Amazon Redshift Serverless データレイクテーブルのマテリアライズドビューが増分更新に対応したので試します

こんにちは、データ事業本部のキタガワです。
Redshiftがデータレイクテーブルのマテリアライズドビュー (以下MV) の増分更新のサポートを開始しました。

以前までデータレイクテーブルでのMVは増分更新に対応していませんでした。
そのため更新する際は常にフルリフレッシュが必要となり、無駄な料金が発生する状態となっていました。
増分更新ができるようになるとMVの柔軟な使い方ができると期待しています。

今回は Redshift Serverless でも同様に増分更新ができるのか、実際にMVを作り試してみたいと思います。

ところでRedshiftにおける「データレイクテーブル」の具体的な定義って公式のドキュメントに記述されているのでしょうか？
探してみましたが、明確な定義は確認できませんでした。
僕は漠然と

• データソースが外部にあり
• 外部のデータカタログに登録されたメタデータを元に
• 外部スキーマに外部テーブルとして登録

されたテーブルのことを言っているものだと理解していますが、この辺りのことでより詳しい方がいればぜひ教えて欲しいです。

環境構築

以下ブログのCloudFormationテンプレートを参考にRedshift Serverlessとその他もろもろのリソースを作成します。
https://dev.classmethod.jp/articles/redshift-serverless-and-vpc-cloudformaion-template/

検証やっていき

外部スキーマと外部テーブルの作成

まずはMVの元となる外部スキーマと外部テーブルを作成していきます。
Redshiftのクエリエディタv2を開き、環境構築のセクションで作成したRedshift Serverlessに接続します。

外部スキーマは以下のSQLで作成可能です。

CREATE EXTERNAL SCHEMA external_schema
FROM DATA CATALOG
DATABASE 'YOUR-DB'
IAM_ROLE 'arn:aws:iam::XXXXXXXXXXXX:role/YOUR-REDSHIFT-SERVERLESS-ROLE'
CREATE EXTERNAL DATABASE IF NOT EXISTS;

次に外部テーブルを先ほど作成した外部スキーマの中^[1]に作成します。
テーブルの元となるデータには、福岡県のオートバイ盗に関するオープンデータを使用します。

ダウンロードは以下のサイトからできます。
福岡県　令和元年　犯罪オープンデータ - オートバイ盗（ＣＳＶファイル） - 自治体オープンデータのCKAN

こちらのCSVファイルはSJISでエンコーディングされているため、Redshiftで扱うには一度UTF8に変換する必要があります。
UTF8に変換する方法は様々あります。
例として、macOSではターミナルから次のように変換できます。
このファイルでは一部UTF8に変換できない文字があったため、-c オプションをつけています。

cd ~/Downlods
iconv -c -f sjis -t utf8 fukuoka_2019ootobaitou.csv > fukuoka_2019ootobaitou_utf8.csv

次にUTF8に変換したファイルをS3の任意の場所に配置します。
あとはそのS3 URIをLocationに指定してCREATE文を実行すると、外部テーブルを作成することができます。

CREATE EXTERNAL TABLE external_schema.fukuoka_ootobaitou (
    "罪名" VARCHAR(MAX), 
    "手口" VARCHAR(MAX), 
    "管轄警察署（発生地）" VARCHAR(MAX), 
    "管轄交番・駐在所（発生地）" VARCHAR(MAX), 
    "市区町村コード（発生地）" INT, 
    "都道府県（発生地）" VARCHAR(MAX), 
    "市区町村（発生地）" VARCHAR(MAX), 
    "町丁目（発生地）" VARCHAR(MAX), 
    "発生年月日（始期）" VARCHAR(MAX), 
    "発生時（始期）" INT, 
    "発生場所の属性" VARCHAR(MAX), 
    "施錠関係" VARCHAR(MAX), 
    "盗難防止装置の有無" VARCHAR(MAX), 
    "現金以外の主な被害品" VARCHAR(MAX))
ROW FORMAT DELIMITED 
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\n'
STORED AS TEXTFILE
LOCATION 's3://YOUR-BUCKET/fukuoka_ootobaitou/'
TABLE PROPERTIES ('skip.header.line.count'='1');

外部テーブルを元にしたMVの作成

これでようやくデータレイクテーブルの準備ができたので、次はMVを作成します。

CREATE MATERIALIZED VIEW fukuoka_city_ootobaitou AS
SELECT *
FROM external_schema.fukuoka_ootobaitou
WHERE "市区町村（発生地）" LIKE '福岡市%';

作成したMVをリフレッシュしてみる

ようやく事前の準備ができました。
ここからが本題です。
まずは差分を発生させたいので、先ほどS3にCSVファイルを配置した場所に追加のCSVを用意します。
UTF8に変換したファイルの1,2行目だけを新しいCSVファイルとして保存します。

awk -F, 'NR==1 || $7 ~ /^福岡市/ {print; if($7 ~ /^福岡市/) exit}' fukuoka_2019ootobaitou_utf8.csv > fukuoka_2019ootobaitou_utf8_add.csv

新しく作成した fukuoka_2019ootobaitou_utf8_add.csv を先ほどと同じ要領でS3に配置し、MVを更新してみます。
また更新前後での件数も比較します。
想定ではIncremental Refresh等の文言が表示^[2]され、また更新前後で件数が1件増えているはずです。

SELECT COUNT(*) FROM fukuoka_city_ootobaitou;

REFRESH MATERIALIZED VIEW fukuoka_city_ootobaitou;

SELECT COUNT(*) FROM fukuoka_city_ootobaitou;

更新前の件数

RFRESHコマンドで表示される文言

更新後の件数

無事1件増えていることが確認できましたね！
また更新についても増分更新がされている旨が通知されています。
このことはRedshiftのSYS モニタリングビューからも確認できます。

SYS_MV_REFRESH_HISTORY - Amazon Redshift

次のSQLを実行します。

SELECT
    database_name
    schema_name, 
    mv_name,
    refresh_type,
    status
FROM
    SYS_MV_REFRESH_HISTORY;

結果

status の値に Refresh successfully updated MV incrementally が入っています。
先ほどリフレッシュコマンドを実行した時に表示されていた文言と同様に、増分更新が行われたことがここからも分かりますね。

終わりに

無事Redshift ServerlessでもデータレイクテーブルベースのMVが増分更新に対応したことを確認できました。
ところで今回テストしたデータにどうして福岡県の令和元年オートバイ盗データを選んだか、不思議に思った方はいませんか？

そうです。
この中の一件の被害者は僕です。

それではまた次の記事でお会いしましょう。