dbt-external-tablesでlocationとかfile_formatを設定しようと思ったらハマった話
tl:dr
- s3をIFレイヤにして、かつ上流システムがある程度枯れていて、テーブル定義が変わらない場合にはdbt-external-tablesというパッケージが便利です
- 以前もこちらでハマりどころを紹介していましたが、その延長でlocationやfile_formatのちょっとしたハマりどころの紹介になります
- dbtのログで見ても、snowflakeのquery logを見ても意味がわからなかったのでちゃんとリポジトリの実装を詳細に追って確認してみました
経緯
- 上記の記事の流れで、Snowflakeの外部ステージやファイルフォーマットをそれぞれtables.exterbalの設定に追加していました
- location：外部ステージに対応するもの
- file_forkat：ファイルフォーマットに対応するもの
- また、当たり前ですが上記の設定はそれぞれSnowflakeだとスキーマオブジェクトになります
- sourcesにdatabaseやschemaの設定をしていたので、てっきりtables.externalのlocationやfile_formatもそのコンテキストで設定できるものと思っていました
- 以下は設定内容です
- location: "@sample_ext_stage
- file_format: sample_format
ただし、これだとprofiles.ymlに設定しているdatabaseやschemaを参照してしまう状態でした。
原因
早い話が、tablesのexternalの設定にあるこのlocationやfile_formatに関しては、sourcesで設定しているdatabseやschemaを参照していないからでした。
以下に設定を見せます。
version: 2
sources:
- name: sample_external_table
database: sample_db
schema: sample_schema
loader: S3
freshness:
error_after: {count: 7, period: day}
loaded_at_field: PARTITION_DATE
tables:
- name: sample_external_table
external:
location: "@sample_ext_stage
pattern: .*\.gz
file_format: sample_format
auto_refresh: true
partitions:
- name: PARTITION_DATE
data_type: DATE
expression: "TO_DATE(SPLIT_PART(SPLIT_PART(metadata$filename, '/', -1), '.', 1), 'YYYYMMDD')" #→これはOK
# expression: "xpression: "TO_DATE(REGEXP_SUBSTR(metadata$filename, '[0-9]{8}'), 'YYYYMMDD')" #→これはNG(REGEXP_SUBSTRに対応していない)
columns:
- name: sample_string
data_type: STRING
expression: value:c1
そして、これからは実際のdbt-external-tablesのパッケージのソースコードを見てみましょう。
dbt run-operation stage_external_sourcesコマンドの実装詳細
stage_external_sourcesに対応するマクロは以下です。
中身を見ると、以下のコードでDWH毎にクエリプランを取得し、その戻り値を実行しているようです。
{% set run_queue = dbt_external_tables.get_external_build_plan(node) %}
なので、次にSnowflakeのget_external_build_planの実装を見てみましょう
Snowflakeのget_external_build_planの実装詳細
対応するマクロは以下です。
中身を見ると、さらにsnowpipeを使わない場合は次のようにschemaやtableを作成するマクロを実行するようです。
dbt_external_tables.create_external_schema(source_node)
dbt_external_tables.create_external_table(source_node)
なので、次はこれらの実装を見てみましょう。
Snowflakeのcreate_external_schemaの実装詳細
対応するマクロは以下です。
これは割と単純で、source_nodeを引き続き引数に、source_nodeのdatabaseにschemaがあればOK、なければ作る、みたいな感じです。
なので、問題のcreate_external_tableの方を見てみます。
Snowflakeのcreate_external_tableの実装詳細
対応するマクロは以下です。
こちらのlocationとfile_formatの設定に問題があるようです。
現状だとソースコードを見てもらえると分かるのですが、それぞれlocationやfile_formatにexternal.xxxxというように設定されています。
- 11行目：
select * from table( infer_schema( location=>'{{external.location}}', file_format=>'{{external.file_format}}', ignore_case=> {{ ignore_case }}) )
結局ここでexternalで設定しているlocationやfile_formatをそのまま参照するようにし、databaseやschemaに関してはその時のコンテキストに依存する形になっているようです。
では、dbtのsourceに関するコンテキストの参照順序を今一度確認しましょう。
- sources.ymlのdatabase設定
a. ただし今回はsources.ymlに設定はしているものの、マクロの中身で参照していないので意味がないという話でした - profiles.ymlのdatabase設定
a. ここで設定されているdatabaseやschema情報を参照することになっているようでした
ということで、色々追っていくと、最終的にはdbt-external-tablesのパッケージにある、plugins内のsnowflakeの実装のなかでlocationやfile_formatに関してはなぜかdatabaseやschemaをsourcesに設定しているものを参照していないため、profiles.ymlの値を参照しにいっているようでした。
解決策
現状だと、externalにあるlocationやfile_formatにベタでdatabaseやschemaを記載するしかなさそうです。
- 誤
- location: "@sample_ext_stage
- file_format: sample_format
- 正
- location: "@sample_db.sample_schema.sample_ext_stage
- file_format: sample_db.sample_schema.sample_format
なので、全く直感的ではなく、最初に使う人にとってはハマりどころな気がしました。
オマケ：それくらいみんな知ってて改善しようとしてるんじゃないの？
というわけで、パッケージのリポジトリでIssueが上がっていないかも見てきました。
issue#89ではちょっと似たような議論がされていたようですが、結局ここでは何ら変更はなくcloseされていました。(本質的な課題が異なっていたことと、その課題へのアプローチとしてはソースコードの改修が必要ないと判断されていました。
ので、この際に修正案も載せてIssueを挙げてみます。
Issueあげる話は後日譚として書かせてください。
まとめ
今回一つのコマンドに関して実装の詳細まで見ることで色々理解が深まりました。
ただ、このdbt-external-tablesのパッケージはあまりメンテが芳しくないようなので、個人的には少しコミットしていきたいパッケージではあります。
Discussion