SQLのためのテンプレートエンジン再考

0post.md

テンプレートエンジンののコードをコードとして管理したくない

• テンプレート化されたコードを読み解くコストは高い

  • コードは書くよりも読まれる時間の方が長い

• テンプレートのコードは lint/formatter/コード補完などの静的解析ツールの相性が悪い

1_poc_workflow.sql

declare generated_sql string;
call `sandbox.template_engine`(
  (null, "sandbox", "zmock_sample") -- sandbox.zmock_sampleを基準に生成する
  , ("__mock__data", "(select 2 as a)")  -- __mock__dataの呼び出しを (select 2 as a) に置換 
  , generated_sql
);

-------------------------------
-- 生成されるSQL
-------------------------------
-- call `sandbox.template_engine`((null, "sandbox", "zmock_sample"), ("__mock__data", "(select 2 as a)") , generated_sql);
-- ... DO NOT EDIT
with __mock__data as (
  select 1 as a
)

select a from (select 2 as a)

zmock_sample.sql

-- sandbox.zmock_sample
with __mock__data as (
  select 1 as a
)

select a from `__mock_data`
;

ztemplate_engine.sql

create or replace procedure `sandbox.templating_view` (
  target struct<table_catalog string, table_schema string, table_name string>
  , placeholders array<struct<identifier string, new_identifier string>>
  , out generated_sql string
)
options (description="""Generate New SQL based on `target`'s view_definition.
Arguments
===

- target: target view
- placeholders: 
- generated_sql:

Examples
===
  declare target default struct(string(null) as table_catalog, "sandbox" as table_schema, "zmock_sample" as table_name);
  declare placeholders default [struct(
          "__mock__data" as identifier
          , "(select 2)" as new_identifier
      )];
  declare generated_sql string;
  call `sandbox.template_engine`(target, placeholders, generated_sql);
"""
)
begin 
  execute immediate format("""
        with recursive template_apply as (
        select 0 as stage, view_definition as sql, placeholders from get_mockable_sql
        union all
        select stage + 1 as stage
            , regexp_replace(
            sql
            , format(r'`%%s`', json_value(p, '$.identifier'))
            , json_value(p, '$.new_identifier')
            )
            , placeholders
        from template_apply
        left join unnest([struct(placeholders[safe_offset(stage)] as p)])
        where stage < array_length(placeholders)
        )
        ,  input_view_information_schema as (
            select  
                view_definition
                , @placeholders as placeholders
            from
                `%s.INFORMATION_SCHEMA.VIEWS`
            where
                table_name = "%s"
        )
        , get_mockable_sql as (
        select * replace(
            array(select to_json_string(struct(v.identifier, v.new_identifier)) from data.placeholders as v) as placeholders
        ) from `input_view_information_schema` data
        )
        select as struct
          trim(sql) as sql
        from template_apply
        where stage = array_length(placeholders)
  """
    , target.table_schema
    , target.table_name
  )
    into generated_sql
    using placeholders as placeholders 
  ;
end