dbt adapter expand_target_column_types 简单说明
adapter expand_target_column_types 核心作用是进行关系的展开对比,确定我们的模型是否需要进行提升(实际上就是数据类型变动)
此功能目前在dbt 的快照以及增量物化场景中使用到
参考使用
- 简单示例
{% set tmp_relation = adapter.get_relation(
{% set target_relation = adapter.get_relation(
{% do adapter.expand_target_column_types(tmp_relation, target_relation) %}
- 增量物化处理
{% if existing_relation is none %}
{% set build_sql = get_create_table_as_sql(False, target_relation, sql) %}
{% elif full_refresh_mode %}
{% set build_sql = get_create_table_as_sql(False, intermediate_relation, sql) %}
{% set need_swap = true %}
{% else %}
{% do run_query(get_create_table_as_sql(True, temp_relation, sql)) %}
{% do adapter.expand_target_column_types(
from_relation=temp_relation,
to_relation=target_relation) %}
内部处理
def expand_column_types(self, goal, current):
reference_columns = {c.name: c for c in self.get_columns_in_relation(goal)}
target_columns = {c.name: c for c in self.get_columns_in_relation(current)}
for column_name, reference_column in reference_columns.items():
target_column = target_columns.get(column_name)
if target_column is not None and target_column.can_expand_to(reference_column):
col_string_size = reference_column.string_size()
new_type = self.Column.string_type(col_string_size)
fire_event(
ColTypeChange(
orig_type=target_column.data_type,
new_type=new_type,
table=_make_ref_key_dict(current),
)
)
# 判断需要进行提升的会进行ALTER_COLUMN_TYPE_MACRO_NAME macro 的调用进行类型修改
self.alter_column_type(current, column_name, new_type)
def alter_column_type(self, relation, column_name, new_column_type) -> None:
"""
1. Create a new column (w/ temp name and correct type)
2. Copy data over to it
3. Drop the existing column (cascade!)
4. Rename the new column to existing column
"""
kwargs = {
"relation": relation,
"column_name": column_name,
"new_column_type": new_column_type,
}
self.execute_macro(ALTER_COLUMN_TYPE_MACRO_NAME, kwargs=kwargs)
具体ALTER_COLUMN_TYPE_MACRO_NAME macro 的处理如下
可以看到为了确保变更的安全,使用了多步处理
{% macro default__alter_column_type(relation, column_name, new_column_type) -%}
{#
1. Create a new column (w/ temp name and correct type)
2. Copy data over to it
3. Drop the existing column (cascade!)
4. Rename the new column to existing column
#}
{%- set tmp_column = column_name + "__dbt_alter" -%}
{% call statement('alter_column_type') %}
alter table {{ relation }} add column {{ adapter.quote(tmp_column) }} {{ new_column_type }};
update {{ relation }} set {{ adapter.quote(tmp_column) }} = {{ adapter.quote(column_name) }};
alter table {{ relation }} drop column {{ adapter.quote(column_name) }} cascade;
alter table {{ relation }} rename column {{ adapter.quote(tmp_column) }} to {{ adapter.quote(column_name) }}
{% endcall %}
说明
此功能还是比较重要的,尤其我们可能会进行数据类型的调整场景,就可以帮助我们自动处理一些ddl了
参考资料
dbt/include/global_project/macros/adapters/columns.sql
https://docs.getdbt.com/reference/dbt-jinja-functions/adapter#expand_target_column_types