This is an automated email from the ASF dual-hosted git repository.
yiguolei pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new 4217b9c1d3 [feature] (dbt) add incremental and init interactive
command line (#11870)
4217b9c1d3 is described below
commit 4217b9c1d3c20963c6866724d16c59bf0998fabe
Author: catpineapple <[email protected]>
AuthorDate: Thu Aug 25 15:03:28 2022 +0800
[feature] (dbt) add incremental and init interactive command line (#11870)
add dbt-doris incremental model and init interactive command line
---
.../dbt-doris/dbt/adapters/doris/__version__.py | 25 +++++++
.../dbt/include/doris/macros/adapters/relation.sql | 26 +++++++
.../macros/materializations/incremental/help.sql | 62 ++++++++++++++++
.../materializations/incremental/incremental.sql | 83 ++++++++++++++++++++++
.../incremental/is_incremental.sql | 28 --------
.../materializations/table/create_table_as.sql | 18 ++++-
.../dbt/include/doris/profile_template.yml | 38 ++++++++++
7 files changed, 251 insertions(+), 29 deletions(-)
diff --git a/extension/dbt-doris/dbt/adapters/doris/__version__.py
b/extension/dbt-doris/dbt/adapters/doris/__version__.py
new file mode 100644
index 0000000000..ac314a2aec
--- /dev/null
+++ b/extension/dbt-doris/dbt/adapters/doris/__version__.py
@@ -0,0 +1,25 @@
+#!/usr/bin/env python
+# encoding: utf-8
+
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements. See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership. The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License. You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied. See the License for the
+# specific language governing permissions and limitations
+# under the License.
+
+
+# this 'version' must be set !!!
+# otherwise the adapters will not be found after the 'dbt init xxx' command
+
+version = "0.1.0"
\ No newline at end of file
diff --git a/extension/dbt-doris/dbt/include/doris/macros/adapters/relation.sql
b/extension/dbt-doris/dbt/include/doris/macros/adapters/relation.sql
index 290b9a72d3..a4f45a1324 100644
--- a/extension/dbt-doris/dbt/include/doris/macros/adapters/relation.sql
+++ b/extension/dbt-doris/dbt/include/doris/macros/adapters/relation.sql
@@ -52,6 +52,18 @@
{% endif %}
{%- endmacro %}
+{% macro doris__unique_key() -%}
+ {% set cols = config.get('unique_key', validator=validation.any[list]) %}
+ {% if cols is not none %}
+ UNIQUE KEY (
+ {% for item in cols %}
+ {{ item }}
+ {% if not loop.last %},{% endif %}
+ {% endfor %}
+ )
+ {% endif %}
+{%- endmacro %}
+
{% macro doris__distributed_by(column_names) -%}
{% set label = 'DISTRIBUTED BY HASH' %}
{% set engine = config.get('engine', validator=validation.any[basestring]) %}
@@ -105,3 +117,17 @@
{% endif %}
{% endcall %}
{%- endmacro %}
+
+
+{% macro doris__timestimp_id() -%}
+ {{ return( (modules.datetime.datetime.now() ~
"").replace('-','').replace(':','').replace('.','').replace(' ','') ) }}
+{%- endmacro %}
+
+{% macro doris__with_label() -%}
+ {% set lable_suffix_id = config.get('label_id',
validator=validation.any[basestring]) %}
+ {% if lable_suffix_id in [none,'DEFAULT'] %}
+ WITH LABEL dbt_doris_label_{{doris__timestimp_id()}}
+ {% else %}
+ WITH LABEL dbt_doris_label_{{ lable_suffix_id }}
+ {% endif %}
+{%- endmacro %}
\ No newline at end of file
diff --git
a/extension/dbt-doris/dbt/include/doris/macros/materializations/incremental/help.sql
b/extension/dbt-doris/dbt/include/doris/macros/materializations/incremental/help.sql
new file mode 100644
index 0000000000..01dd9e5ffb
--- /dev/null
+++
b/extension/dbt-doris/dbt/include/doris/macros/materializations/incremental/help.sql
@@ -0,0 +1,62 @@
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing,
+-- software distributed under the License is distributed on an
+-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+-- KIND, either express or implied. See the License for the
+-- specific language governing permissions and limitations
+-- under the License.
+
+{% macro is_incremental() %}
+ {% if not execute %}
+ {{ return(False) }}
+ {% else %}
+ {% set relation = adapter.get_relation(this.database, this.schema,
this.table) %}
+ {{ return(relation is not none
+ and relation.type == 'table'
+ and model.config.materialized in ['incremental','partition']
+ and not should_full_refresh()) }}
+ {% endif %}
+{% endmacro %}
+
+
+{% macro tmp_delete(tmp_relation, target_relation, unique_key=none,
statement_name="pre_main") %}
+ {% if unique_key is not none %}
+ {% set unique_key_str %}
+ {% for item in unique_key %}
+ {{ item }},
+ {% endfor %}
+ {% endset %}
+ insert into {{ target_relation }} ( {{unique_key_str
~'`__DORIS_DELETE_SIGN__`'}})
+ select {{ unique_key_str }}
+ 1 as `__DORIS_DELETE_SIGN__`
+ from {{ tmp_relation }}
+ {% endif %}
+{%- endmacro %}
+
+
+{% macro tmp_insert(tmp_relation, target_relation, unique_key=none,
statement_name="main") %}
+ {%- set dest_cols_csv = adapter.get_columns_in_relation(target_relation) |
map(attribute='quoted') | join(', ') -%}
+ insert into {{ target_relation }} ({{ dest_cols_csv }})
+ (
+ select {{ dest_cols_csv }}
+ from {{ tmp_relation }}
+ )
+{%- endmacro %}
+
+{% macro show_create( target_relation, statement_name="table_model") %}
+ show create table {{ target_relation }}
+{%- endmacro %}
+
+{% macro is_unique_model( table_create_obj ) %}
+ {% set create_table = table_create_obj['data'][0][1]%}
+ {{ return('\nUNIQUE KEY(' in create_table and '\nDUPLICATE KEY(' not in
create_table and '\nAGGREGATE KEY(' not in create_table) }}
+{%- endmacro %}
diff --git
a/extension/dbt-doris/dbt/include/doris/macros/materializations/incremental/incremental.sql
b/extension/dbt-doris/dbt/include/doris/macros/materializations/incremental/incremental.sql
new file mode 100644
index 0000000000..d0bb652137
--- /dev/null
+++
b/extension/dbt-doris/dbt/include/doris/macros/materializations/incremental/incremental.sql
@@ -0,0 +1,83 @@
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing,
+-- software distributed under the License is distributed on an
+-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+-- KIND, either express or implied. See the License for the
+-- specific language governing permissions and limitations
+-- under the License.
+
+{% materialization incremental, adapter='doris' %}
+ {% set unique_key = config.get('unique_key', validator=validation.any[list])
%}
+ {% if unique_key is not %}
+ {% do exceptions.raise_compiler_error("dbt-doris incremental 'unique_key'
cannot be empty" ) %}
+ {% endif %}
+
+ {% set target_relation = this.incorporate(type='table') %}
+
+
+ {% set existing_relation = load_relation(this) %}
+ {% set tmp_relation = make_temp_relation(this) %}
+
+ {{ run_hooks(pre_hooks, inside_transaction=False) }}
+
+ {{ run_hooks(pre_hooks, inside_transaction=True) }}
+
+ {% set to_drop = [] %}
+ {% if existing_relation is none %}
+ {% set build_sql = doris__create_unique_table_as(False, target_relation,
sql) %}
+ {% elif existing_relation.is_view or should_full_refresh() %}
+ {#-- Make sure the backup doesn't exist so we don't encounter issues
with the rename below #}
+ {% set backup_identifier = existing_relation.identifier ~ "__dbt_backup"
%}
+ {% set backup_relation =
existing_relation.incorporate(path={"identifier": backup_identifier}) %}
+ {% do adapter.drop_relation(backup_relation) %}
+ {% do adapter.rename_relation(target_relation, backup_relation) %}
+ {% set build_sql = doris__create_unique_table_as(False, target_relation,
sql) %}
+ {% do to_drop.append(backup_relation) %}
+ {% else %}
+ {% set build_show_create = show_create( target_relation,
statement_name="table_model") %}
+ {% call statement('table_model' , fetch_result=True) %}
+ {{ build_show_create }}
+ {% endcall %}
+ {%- set table_create_obj = load_result('table_model') -%}
+ {% if not is_unique_model(table_create_obj) %}
+ {% do exceptions.raise_compiler_error("doris table:"~
target_relation ~ ", model must be 'UNIQUE'" ) %}
+ {% endif %}
+ {% set tmp_relation = make_temp_relation(target_relation) %}
+ {% do run_query(create_table_as(True, tmp_relation, sql)) %}
+ {% do adapter.expand_target_column_types(
+ from_relation=tmp_relation,
+ to_relation=target_relation) %}
+ {% set build_sql_del = tmp_delete(tmp_relation, target_relation,
unique_key=unique_key, statement_name="pre_main") %}
+ {% call statement("pre_main") %}
+ {{ build_sql_del }}
+ {% endcall %}
+ {% set build_sql = tmp_insert(tmp_relation, target_relation,
unique_key=unique_key) %}
+ {% endif %}
+
+ {% call statement("main") %}
+ {{ build_sql }}
+ {% endcall %}
+
+
+ {% do persist_docs(target_relation, model) %}
+ {{ run_hooks(post_hooks, inside_transaction=True) }}
+ {% do adapter.commit() %}
+ {% for rel in to_drop %}
+ {% do adapter.drop_relation(rel) %}
+ {% endfor %}
+ {{ run_hooks(post_hooks, inside_transaction=False) }}
+ {{ return({'relations': [target_relation]}) }}
+
+{%- endmaterialization %}
+
+
+
diff --git
a/extension/dbt-doris/dbt/include/doris/macros/materializations/incremental/is_incremental.sql
b/extension/dbt-doris/dbt/include/doris/macros/materializations/incremental/is_incremental.sql
deleted file mode 100644
index abc732d0db..0000000000
---
a/extension/dbt-doris/dbt/include/doris/macros/materializations/incremental/is_incremental.sql
+++ /dev/null
@@ -1,28 +0,0 @@
--- Licensed to the Apache Software Foundation (ASF) under one
--- or more contributor license agreements. See the NOTICE file
--- distributed with this work for additional information
--- regarding copyright ownership. The ASF licenses this file
--- to you under the Apache License, Version 2.0 (the
--- "License"); you may not use this file except in compliance
--- with the License. You may obtain a copy of the License at
---
--- http://www.apache.org/licenses/LICENSE-2.0
---
--- Unless required by applicable law or agreed to in writing,
--- software distributed under the License is distributed on an
--- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
--- KIND, either express or implied. See the License for the
--- specific language governing permissions and limitations
--- under the License.
-
-{% macro is_incremental() %}
- {% if not execute %}
- {{ return(False) }}
- {% else %}
- {% set relation = adapter.get_relation(this.database, this.schema,
this.table) %}
- {{ return(relation is not none
- and relation.type == 'table'
- and model.config.materialized in ['incremental','partition']
- and not should_full_refresh()) }}
- {% endif %}
-{% endmacro %}
diff --git
a/extension/dbt-doris/dbt/include/doris/macros/materializations/table/create_table_as.sql
b/extension/dbt-doris/dbt/include/doris/macros/materializations/table/create_table_as.sql
index 2442571427..bceb5f9703 100644
---
a/extension/dbt-doris/dbt/include/doris/macros/materializations/table/create_table_as.sql
+++
b/extension/dbt-doris/dbt/include/doris/macros/materializations/table/create_table_as.sql
@@ -19,9 +19,25 @@
{% set sql_header = config.get('sql_header', none) %}
{% set table = relation.include(database=False) %}
{{ sql_header if sql_header is not none }}
+ {%if temporary %}
+ {{doris__drop_relation(relation)}};
+ {% endif %}
create table {{ table }}
+ {{ doris__duplicate_key() }}
{{ doris__partition_by() }}
{{ doris__distributed_by() }}
{{ doris__properties() }} as {{ sql }};
- insert into {{ table }} {{ sql }};
+
{%- endmacro %}
+
+{% macro doris__create_unique_table_as(temporary, relation, sql) -%}
+ {% set sql_header = config.get('sql_header', none) %}
+ {% set table = relation.include(database=False) %}
+ {{ sql_header if sql_header is not none }}
+ create table {{ table }}
+ {{ doris__unique_key() }}
+ {{ doris__partition_by() }}
+ {{ doris__distributed_by() }}
+ {{ doris__properties() }} as {{ sql }};
+
+{%- endmacro %}
\ No newline at end of file
diff --git a/extension/dbt-doris/dbt/include/doris/profile_template.yml
b/extension/dbt-doris/dbt/include/doris/profile_template.yml
new file mode 100644
index 0000000000..9f1abae45c
--- /dev/null
+++ b/extension/dbt-doris/dbt/include/doris/profile_template.yml
@@ -0,0 +1,38 @@
+#!/usr/bin/env python
+# encoding: utf-8
+
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements. See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership. The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License. You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied. See the License for the
+# specific language governing permissions and limitations
+# under the License.
+
+fixed:
+ type: doris
+prompts:
+ host:
+ hint: 'hostname for your instance(your doris fe host)'
+ port:
+ default: 9030
+ type: 'int'
+ hint: 'port for your instance(your doris fe query_port)'
+ schema:
+ default: 'dbt'
+ hint: 'the schema name as stored in the database'
+ username:
+ hint: 'your doris username'
+ password:
+ hint: 'your doris password, if no password, just Enter'
+ hide_input: true
+ default: ''
\ No newline at end of file
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
