dbtで外部テーブルを参照するモデルを作成する
2024.04.30はじめに
データアナリティクス事業本部のおざわ（じ）です。マテリアライズをviewにしたdbtモデルのソースとして外部テーブルを指定したところ、ちょっとしたエラーに遭遇したので解決方法を共有したいと思います。
dbt version
今回使用したdbtのバージョンです。
❯ dbt --version Core: - installed: 1.7.13 - latest: 1.7.13 - Up to date! Plugins: - redshift: 1.7.7 - Up to date!
DWHには別途テスト用に構築済みだったAmazon Redshift Serverlessを使っています。今回はここにCloudFormationで外部テーブルとGlueのデータベースを用意してから外部スキーマを作成してdbtのモデルを実行します。
外部テーブルについて
Amazon Redshiftでは、Redshift Spectrumの機能でS3バケットに格納されているデータに対して直接クエリすることができます。Glueデータカタログにファイル構造のメタデータを登録しておくことで、S3にあるファイルも他のテーブルと同じように参照したり、Redshiftの他のテーブルと結合するといったことが可能です（更新操作はサポートされていません※）。
詳しくは以下の記事やドキュメントをご参照ください。
1. 準備
以下のテンプレートを使用します。外部テーブルはStorageDescriptorのLocationにS3のデータファイル格納先パスがあり、その他ファイルを読み込むのに必要なメタデータを記載しています。このメタデータがGlueのデータカタログに登録され、RedshiftからS3に置いてあるファイルにアクセスできるようになります。
CFnテンプレート
AWSTemplateFormatVersino: "2010-09-09"
Resources:
MyGlueDatabase:
Type: AWS::Glue::Database
Properties:
CatalogId: !Ref AWS::AccountId
DatabaseInput:
Name: "my-testdb"
MyGlueMovieTable:
Type: AWS::Glue::Table
Properties:
CatalogId: !Ref AWS::AccountId
DatabaseName: !Ref MyTestGlueDatabase
TableInput:
Name: "movies"
Description: "My movie table description"
TableType: EXTERNAL_TABLE
Parameters:
classification: "csv"
separatorChar: ","
skip.header.line.count: "1"
StorageDescriptor:
Location: "s3://my-test-bucket/movies/"
InputFormat: "org.apache.hadoop.mapred.TextInputFormat"
OutputFormat: "org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat"
SerdeInfo:
SerializationLibrary: "org.apache.hadoop.hive.serde2.OpenCSVSerde"
Parameters:
separatorChar: ","
quoteChar: "\""
escapeChar: "\\"
Columns:
- Name: "movieId"
Type: "string"
- Name: "title"
Type: "string"
- Name: "genres"
Type: "string"
外部スキーマの作成
外部スキーマはRedshiftのクエリエディタでCREATE EXTERNAL SCHEMAを実行しました。
CREATE EXTERNAL SCHEMA movielens_sample FROM DATA CATALOG DATABASE 'my-testdb' IAM_ROLE 'arn:aws:iam::123456789012:role/my-lovely-redshift-server' CREATE EXTERNAL DATABASE IF NOT EXISTS;
dbtのモデルではmovielens_sample をソーステーブルのスキーマとして使用します。
ちなみに外部テーブルの話はこちらのブログでわかりやすく説明されていますので、ご興味ある方はご参照ください。
2. 外部テーブルを参照するdbtモデル
最初に作成したのは以下のようなモデルです。
{{
config(
materialized="view"
)
}}
select
*
from {{ source("movielens_sample", "movies") }}
このままdbt runを実行してもエラーになりました。
External tables are not supported in views
❯ dbt run --select my_movies 00:02:00 Running with dbt=1.7.13 00:02:00 Registered adapter: redshift=1.7.7 00:02:00 Found 1 model, 1 seed, 3 sources, 0 exposures, 0 metrics, 467 macros, 0 groups, 0 semantic models 00:02:00 00:02:10 Concurrency: 1 threads (target='dev') 00:02:10 00:02:10 1 of 1 START sql view model test_schema.my_movies .................. [RUN] 00:02:11 1 of 1 ERROR creating sql view model test_schema.my_movies ......... [ERROR in 1.92s] 00:02:13 00:02:13 Finished running 1 view model in 0 hours 0 minutes and 12.58 seconds (12.58s). 00:02:13 00:02:13 Completed with 1 error and 0 warnings: 00:02:13 00:02:13 Database Error in model my_movies (models/my_movies.sql) External tables are not supported in views compiled Code at target/run/my_sample_project/models/my_movies.sql 00:02:13 00:02:13 Done. PASS=0 WARN=0 ERROR=1 SKIP=0 TOTAL=1
dbtでビューのモデルを作成する際、特にオプション指定がない場合は参照先のオブジェクトに依存した標準のビューが作成されますが、Redshiftで外部テーブルを参照するビューを作成するにはwith no schema bindingオプションを付けて遅延バインドビューとする必要があります。
テーブルやユーザー定義関数など、基盤となるデータベースオブジェクトにバインドされていないことを示す句。その結果、ビューと参照先のオブジェクト間には依存関係がありません。参照先のオブジェクトが存在しない場合でも、ビューを作成できます。依存関係がないため、ビューに影響を与えることなく参照先のオブジェクトを削除または変更できます。Amazon Redshift は、ビューがクエリされるまで依存関係をチェックしません。
エラーになったdbt runのログを見てみると以下のようなSQLが実行されていました。
create view "devdb"."test_schema"."my_movies__dbt_tmp" as ( select * from "devdb"."movielens_sample"."movies" );
3. 遅延バインドビューの設定
解決方法としてはシンプルでモデルの設定に bind=False を追加します。
late binding views can be used with external tables via Redshift Spectrum.
{{
config(
materialized="view",
bind=False
)
}}
--以下同じ
bind=Falseを追加してdbt runを実行すると、今度は成功しました。
❯ dbt run --select my_movies 00:12:41 Running with dbt=1.7.13 00:12:41 Registered adapter: redshift=1.7.7 00:12:41 Found 1 seed, 1 model, 3 sources, 0 exposures, 0 metrics, 467 macros, 0 groups, 0 semantic models 00:12:41 00:12:45 Concurrency: 1 threads (target='dev') 00:12:45 00:12:45 1 of 1 START sql view model test_schema.my_movies .................. [RUN] 00:12:48 1 of 1 OK created sql view model test_schema.my_movies ............. [SUCCESS in 2.60s] 00:12:49 00:12:49 Finished running 1 view model in 0 hours 0 minutes and 8.13 seconds (8.13s). 00:12:49 00:12:49 Completed successfully 00:12:49 00:12:49 Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1
ログでwith no schema bindingが付与されていることを確認できました。
create view "devdb"."test_schema"."my_movies__dbt_tmp" as ( select * from "devdb"."movielens_sample"."movies" ) with no schema binding;
4. おわりに
以上、簡単ですがAmazon Redshiftの外部テーブルを参照するモデルを作成するときのTipsでした。
