学习数仓工具 dbt
DBT 是一个有趣的工具，它通过一种结构化的方式定义了数仓中各种表、视图的构建和填充方式。
dbt 面相的对象是数据开发团队，提供了如下几个最有价值的能力：
- 支持多种数据库
- 通过 select 来定义数据，无需编写 DML
- 构建数据时，自动分析数据依赖，按照依赖顺序生成模型
- 版本化数据定义，支持团队 review
- 支持数据质量测试
任何数据库都可以按照规范实现 DBT-XXX 适配器，使得你的数据库能够使用 DBT 来管理。
因为 OB 和 TiDB 都是兼容 MySQL 的，所以我先用 TiDB 的适配器来跑一下 OB，看看效果。因为 OB 对 information_schema.tables 表中的 table_type 字段内容做了一些扩展，除了 “VIEW” 和 “TABLE” 之外还引入了 “SYSTEM TABLE”, “SYSTEM VIEW”：
OceanBase(admin@test)>select distinct table_type from information_schema.tables;
+--------------+
| table_type |
+--------------+
| BASE TABLE |
| VIEW |
| SYSTEM TABLE |
| SYSTEM VIEW |
+--------------+
4 rows in set (0.008 sec)
这需要适配一下。改一下adapters.sql 即可。
/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/dbt/include/tidb/macros/adapters.sql
本文的 dbt 脚本见 https://github.com/dbt-labs/jaffle_shop
已经定义好 dbt 脚本后，数据开发工程师每次需要更新数仓中的数据时，只需要调用 dbt run 即可让所有数据更新，提升工作效率。可以反复执行 dbt run， dbt run背后会建立一些临时表，如：
customers__dbt_backup
stg_customers__dbt_tmp
运行(https://cn.pingcap.com/blog/when-tidb-meets-dbt/
) 里的例子，内部日志如下。仔细阅读，就能理解其工作原理了：
============================== 22:08:55.622663 | f43f4f98-1c20-4751-8101-18f6577098a1 ==============================
22:08:55.622663 [info ] [MainThread]: Running with dbt=1.6.9
22:08:55.622992 [debug] [MainThread]: running dbt with arguments {'printer_width': '80', 'indirect_selection': 'eager', 'log_cache_events': 'False', 'write_json': 'True', 'partial_parse': 'True', 'cache_selected_only': 'False', 'warn_error': 'None', 'fail_fast': 'False', 'debug': 'False', 'log_path': '/Users/raywill/code/raywill/jaffle_shop/logs', 'version_check': 'True', 'profiles_dir': '/Users/raywill/.dbt', 'use_colors': 'True', 'use_experimental_parser': 'False', 'no_print': 'None', 'quiet': 'False', 'warn_error_options': 'WarnErrorOptions(include=[], exclude=[])', 'introspect': 'True', 'static_parser': 'True', 'log_format': 'default', 'target_path': 'None', 'invocation_command': 'dbt run', 'send_anonymous_usage_stats': 'True'}
22:08:55.662363 [debug] [MainThread]: Sending event: {'category': 'dbt', 'action': 'project_id', 'label': 'f43f4f98-1c20-4751-8101-18f6577098a1', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x10afdad10>]}
22:08:55.669168 [debug] [MainThread]: Sending event: {'category': 'dbt', 'action': 'adapter_info', 'label': 'f43f4f98-1c20-4751-8101-18f6577098a1', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x10a9cd050>]}
22:08:55.669586 [info ] [MainThread]: Registered adapter: tidb=1.6.4
22:08:55.678545 [debug] [MainThread]: checksum: f795ff6322dabffafa5fc1f59b34557dea99c8bef60412d1b41cabcb4e97dcce, vars: {}, profile: , target: , version: 1.6.9
22:08:55.693348 [debug] [MainThread]: Partial parsing enabled: 0 files deleted, 0 files added, 0 files changed.
22:08:55.693590 [debug] [MainThread]: Partial parsing enabled, no changes found, skipping parsing
22:08:55.697272 [debug] [MainThread]: Sending event: {'category': 'dbt', 'action': 'load_project', 'label': 'f43f4f98-1c20-4751-8101-18f6577098a1', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x10767aa50>]}
22:08:55.703811 [debug] [MainThread]: Sending event: {'category': 'dbt', 'action': 'resource_counts', 'label': 'f43f4f98-1c20-4751-8101-18f6577098a1', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x10b01b950>]}
22:08:55.704055 [info ] [MainThread]: Found 5 models, 3 seeds, 20 tests, 0 sources, 0 exposures, 0 metrics, 349 macros, 0 groups, 0 semantic models
22:08:55.704224 [debug] [MainThread]: Sending event: {'category': 'dbt', 'action': 'runnable_timing', 'label': 'f43f4f98-1c20-4751-8101-18f6577098a1', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x10b008050>]}
22:08:55.705247 [info ] [MainThread]:
22:08:55.705615 [debug] [MainThread]: Acquiring new tidb connection 'master'
22:08:55.706190 [debug] [ThreadPool]: Acquiring new tidb connection 'list_schemas'
22:08:55.711040 [debug] [ThreadPool]: Using tidb connection "list_schemas"
22:08:55.711272 [debug] [ThreadPool]: On list_schemas: /* {"app": "dbt", "dbt_version": "1.6.9", "profile_name": "jaffle_shop", "target_name": "dev", "connection_name": "list_schemas"} */
select distinct schema_namefrom information_schema.schemata
22:08:55.711413 [debug] [ThreadPool]: Opening a new connection, currently in state init
22:08:55.791747 [debug] [ThreadPool]: SQL status: SUCCESS 7 in 0.0 seconds
22:08:55.793001 [debug] [ThreadPool]: On list_schemas: Close
22:08:55.794166 [debug] [ThreadPool]: Re-using an available connection from the pool (formerly list_schemas, now list_None_oceanbase)
22:08:55.797532 [debug] [ThreadPool]: Using tidb connection "list_None_oceanbase"
22:08:55.797766 [debug] [ThreadPool]: On list_None_oceanbase: BEGIN
22:08:55.797922 [debug] [ThreadPool]: Opening a new connection, currently in state closed
22:08:55.872273 [debug] [ThreadPool]: SQL status: SUCCESS 0 in 0.0 seconds
22:08:55.872627 [debug] [ThreadPool]: Using tidb connection "list_None_oceanbase"
22:08:55.872860 [debug] [ThreadPool]: On list_None_oceanbase: /* {"app": "dbt", "dbt_version": "1.6.9", "profile_name": "jaffle_shop", "target_name": "dev", "connection_name": "list_None_oceanbase"} */
selectnull as "database",table_name as name,table_schema as "schema",case when table_type = 'BASE TABLE' then 'table'when table_type = 'SYSTEM TABLE' then 'table'when table_type = 'SYSTEM VIEW' then 'view'when table_type = 'VIEW' then 'view'else table_typeend as table_typefrom information_schema.tableswhere table_schema = 'oceanbase'22:08:55.982141 [debug] [ThreadPool]: SQL status: SUCCESS 1067 in 0.0 seconds
22:08:55.991267 [debug] [ThreadPool]: On list_None_oceanbase: ROLLBACK
22:08:55.999188 [debug] [ThreadPool]: On list_None_oceanbase: Close
22:08:56.050320 [debug] [MainThread]: Sending event: {'category': 'dbt', 'action': 'runnable_timing', 'label': 'f43f4f98-1c20-4751-8101-18f6577098a1', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x10b26a3d0>]}
22:08:56.050653 [debug] [MainThread]: Using tidb connection "master"
22:08:56.050791 [debug] [MainThread]: On master: BEGIN
22:08:56.050913 [debug] [MainThread]: Opening a new connection, currently in state init
22:08:56.112223 [debug] [MainThread]: SQL status: SUCCESS 0 in 0.0 seconds
22:08:56.112550 [debug] [MainThread]: On master: COMMIT
22:08:56.112700 [debug] [MainThread]: Using tidb connection "master"
22:08:56.112843 [debug] [MainThread]: On master: COMMIT
22:08:56.127682 [debug] [MainThread]: SQL status: SUCCESS 0 in 0.0 seconds
22:08:56.127872 [debug] [MainThread]: On master: Close
22:08:56.128367 [info ] [MainThread]: Concurrency: 1 threads (target='dev')
22:08:56.128558 [info ] [MainThread]:
22:08:56.129995 [debug] [Thread-1 (]: Began running node model.jaffle_shop.stg_customers
22:08:56.130305 [info ] [Thread-1 (]: 1 of 5 START sql view model oceanbase.stg_customers ............................ [RUN]
22:08:56.130725 [debug] [Thread-1 (]: Re-using an available connection from the pool (formerly list_None_oceanbase, now model.jaffle_shop.stg_customers)
22:08:56.130937 [debug] [Thread-1 (]: Began compiling node model.jaffle_shop.stg_customers
22:08:56.132859 [debug] [Thread-1 (]: Writing injected SQL for node "model.jaffle_shop.stg_customers"
22:08:56.133337 [debug] [Thread-1 (]: Timing info for model.jaffle_shop.stg_customers (compile): 22:08:56.131077 => 22:08:56.133228
22:08:56.133526 [debug] [Thread-1 (]: Began executing node model.jaffle_shop.stg_customers
22:08:56.158597 [debug] [Thread-1 (]: Writing runtime sql for node "model.jaffle_shop.stg_customers"
22:08:56.159066 [debug] [Thread-1 (]: Using tidb connection "model.jaffle_shop.stg_customers"
22:08:56.159227 [debug] [Thread-1 (]: On model.jaffle_shop.stg_customers: BEGIN
22:08:56.159372 [debug] [Thread-1 (]: Opening a new connection, currently in state closed
22:08:56.217134 [debug] [Thread-1 (]: SQL status: SUCCESS 0 in 0.0 seconds
22:08:56.217351 [debug] [Thread-1 (]: Using tidb connection "model.jaffle_shop.stg_customers"
22:08:56.217555 [debug] [Thread-1 (]: On model.jaffle_shop.stg_customers: /* {"app": "dbt", "dbt_version": "1.6.9", "profile_name": "jaffle_shop", "target_name": "dev", "node_id": "model.jaffle_shop.stg_customers"} */create view `oceanbase`.`stg_customers__dbt_tmp`as (with source as (select * from `oceanbase`.`raw_customers`),renamed as (selectid as customer_id,first_name,last_namefrom source)select * from renamed);
22:08:56.329009 [debug] [Thread-1 (]: SQL status: SUCCESS 0 in 0.0 seconds
22:08:56.364055 [debug] [Thread-1 (]: Using tidb connection "model.jaffle_shop.stg_customers"
22:08:56.364679 [debug] [Thread-1 (]: On model.jaffle_shop.stg_customers: /* {"app": "dbt", "dbt_version": "1.6.9", "profile_name": "jaffle_shop", "target_name": "dev", "node_id": "model.jaffle_shop.stg_customers"} */drop view if exists `oceanbase`.`stg_customers__dbt_backup` cascade22:08:56.388533 [debug] [Thread-1 (]: SQL status: SUCCESS 0 in 0.0 seconds
22:08:56.390391 [debug] [Thread-1 (]: Using tidb connection "model.jaffle_shop.stg_customers"
22:08:56.390649 [debug] [Thread-1 (]: On model.jaffle_shop.stg_customers: /* {"app": "dbt", "dbt_version": "1.6.9", "profile_name": "jaffle_shop", "target_name": "dev", "node_id": "model.jaffle_shop.stg_customers"} */rename table `oceanbase`.`stg_customers` to `oceanbase`.`stg_customers__dbt_backup`22:08:56.482864 [debug] [Thread-1 (]: SQL status: SUCCESS 0 in 0.0 seconds
22:08:56.489215 [debug] [Thread-1 (]: Using tidb connection "model.jaffle_shop.stg_customers"
22:08:56.489815 [debug] [Thread-1 (]: On model.jaffle_shop.stg_customers: /* {"app": "dbt", "dbt_version": "1.6.9", "profile_name": "jaffle_shop", "target_name": "dev", "node_id": "model.jaffle_shop.stg_customers"} */drop view if exists `oceanbase`.`stg_customers` cascade22:08:56.510550 [debug] [Thread-1 (]: SQL status: SUCCESS 0 in 0.0 seconds
22:08:56.513581 [debug] [Thread-1 (]: Using tidb connection "model.jaffle_shop.stg_customers"
22:08:56.514166 [debug] [Thread-1 (]: On model.jaffle_shop.stg_customers: /* {"app": "dbt", "dbt_version": "1.6.9", "profile_name": "jaffle_shop", "target_name": "dev", "node_id": "model.jaffle_shop.stg_customers"} */rename table `oceanbase`.`stg_customers__dbt_tmp` to `oceanbase`.`stg_customers`22:08:56.600139 [debug] [Thread-1 (]: SQL status: SUCCESS 0 in 0.0 seconds
22:08:56.620564 [debug] [Thread-1 (]: On model.jaffle_shop.stg_customers: COMMIT
22:08:56.621050 [debug] [Thread-1 (]: Using tidb connection "model.jaffle_shop.stg_customers"
22:08:56.621314 [debug] [Thread-1 (]: On model.jaffle_shop.stg_customers: COMMIT
22:08:56.636866 [debug] [Thread-1 (]: SQL status: SUCCESS 0 in 0.0 seconds
22:08:56.642164 [debug] [Thread-1 (]: Using tidb connection "model.jaffle_shop.stg_customers"
22:08:56.642473 [debug] [Thread-1 (]: On model.jaffle_shop.stg_customers: /* {"app": "dbt", "dbt_version": "1.6.9", "profile_name": "jaffle_shop", "target_name": "dev", "node_id": "model.jaffle_shop.stg_customers"} */
drop view if exists `oceanbase`.`stg_customers__dbt_backup`
22:08:56.719959 [debug] [Thread-1 (]: SQL status: SUCCESS 0 in 0.0 seconds
22:08:56.724716 [debug] [Thread-1 (]: Timing info for model.jaffle_shop.stg_customers (execute): 22:08:56.133648 => 22:08:56.724313
22:08:56.725436 [debug] [Thread-1 (]: On model.jaffle_shop.stg_customers: Close
22:08:56.727114 [debug] [Thread-1 (]: Sending event: {'category': 'dbt', 'action': 'run_model', 'label': 'f43f4f98-1c20-4751-8101-18f6577098a1', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x10adad4d0>]}
22:08:56.728088 [info ] [Thread-1 (]: 1 of 5 OK created sql view model oceanbase.stg_customers ....................... [SUCCESS 0 in 0.60s]
22:08:56.728829 [debug] [Thread-1 (]: Finished running node model.jaffle_shop.stg_customers
22:08:56.729372 [debug] [Thread-1 (]: Began running node model.jaffle_shop.stg_orders
22:08:56.729887 [info ] [Thread-1 (]: 2 of 5 START sql view model oceanbase.stg_orders ............................... [RUN]
22:08:56.730682 [debug] [Thread-1 (]: Re-using an available connection from the pool (formerly model.jaffle_shop.stg_customers, now model.jaffle_shop.stg_orders)
22:08:56.731039 [debug] [Thread-1 (]: Began compiling node model.jaffle_shop.stg_orders
22:08:56.734512 [debug] [Thread-1 (]: Writing injected SQL for node "model.jaffle_shop.stg_orders"
22:08:56.735763 [debug] [Thread-1 (]: Timing info for model.jaffle_shop.stg_orders (compile): 22:08:56.731390 => 22:08:56.735555
22:08:56.736135 [debug] [Thread-1 (]: Began executing node model.jaffle_shop.stg_orders
22:08:56.752144 [debug] [Thread-1 (]: Writing runtime sql for node "model.jaffle_shop.stg_orders"
22:08:56.752639 [debug] [Thread-1 (]: Using tidb connection "model.jaffle_shop.stg_orders"
22:08:56.752858 [debug] [Thread-1 (]: On model.jaffle_shop.stg_orders: BEGIN
22:08:56.753063 [debug] [Thread-1 (]: Opening a new connection, currently in state closed
22:08:56.817242 [debug] [Thread-1 (]: SQL status: SUCCESS 0 in 0.0 seconds
22:08:56.817668 [debug] [Thread-1 (]: Using tidb connection "model.jaffle_shop.stg_orders"
22:08:56.818016 [debug] [Thread-1 (]: On model.jaffle_shop.stg_orders: /* {"app": "dbt", "dbt_version": "1.6.9", "profile_name": "jaffle_shop", "target_name": "dev", "node_id": "model.jaffle_shop.stg_orders"} */create view `oceanbase`.`stg_orders__dbt_tmp`as (with source as (select * from `oceanbase`.`raw_orders`),renamed as (selectid as order_id,user_id as customer_id,order_date,statusfrom source)select * from renamed);
22:08:56.896459 [debug] [Thread-1 (]: SQL status: SUCCESS 0 in 0.0 seconds
22:08:56.903640 [debug] [Thread-1 (]: Using tidb connection "model.jaffle_shop.stg_orders"
22:08:56.903963 [debug] [Thread-1 (]: On model.jaffle_shop.stg_orders: /* {"app": "dbt", "dbt_version": "1.6.9", "profile_name": "jaffle_shop", "target_name": "dev", "node_id": "model.jaffle_shop.stg_orders"} */drop view if exists `oceanbase`.`stg_orders__dbt_backup` cascade22:08:56.925139 [debug] [Thread-1 (]: SQL status: SUCCESS 0 in 0.0 seconds
22:08:56.926137 [debug] [Thread-1 (]: Using tidb connection "model.jaffle_shop.stg_orders"
22:08:56.926412 [debug] [Thread-1 (]: On model.jaffle_shop.stg_orders: /* {"app": "dbt", "dbt_version": "1.6.9", "profile_name": "jaffle_shop", "target_name": "dev", "node_id": "model.jaffle_shop.stg_orders"} */rename table `oceanbase`.`stg_orders` to `oceanbase`.`stg_orders__dbt_backup`22:08:56.994692 [debug] [Thread-1 (]: SQL status: SUCCESS 0 in 0.0 seconds
22:08:56.997962 [debug] [Thread-1 (]: Using tidb connection "model.jaffle_shop.stg_orders"
22:08:56.998295 [debug] [Thread-1 (]: On model.jaffle_shop.stg_orders: /* {"app": "dbt", "dbt_version": "1.6.9", "profile_name": "jaffle_shop", "target_name": "dev", "node_id": "model.jaffle_shop.stg_orders"} */drop view if exists `oceanbase`.`stg_orders` cascade22:08:57.016278 [debug] [Thread-1 (]: SQL status: SUCCESS 0 in 0.0 seconds
22:08:57.017341 [debug] [Thread-1 (]: Using tidb connection "model.jaffle_shop.stg_orders"
22:08:57.017666 [debug] [Thread-1 (]: On model.jaffle_shop.stg_orders: /* {"app": "dbt", "dbt_version": "1.6.9", "profile_name": "jaffle_shop", "target_name": "dev", "node_id": "model.jaffle_shop.stg_orders"} */rename table `oceanbase`.`stg_orders__dbt_tmp` to `oceanbase`.`stg_orders`22:08:57.091437 [debug] [Thread-1 (]: SQL status: SUCCESS 0 in 0.0 seconds
22:08:57.097185 [debug] [Thread-1 (]: On model.jaffle_shop.stg_orders: COMMIT
22:08:57.097653 [debug] [Thread-1 (]: Using tidb connection "model.jaffle_shop.stg_orders"
22:08:57.097973 [debug] [Thread-1 (]: On model.jaffle_shop.stg_orders: COMMIT
22:08:57.113205 [debug] [Thread-1 (]: SQL status: SUCCESS 0 in 0.0 seconds
22:08:57.119160 [debug] [Thread-1 (]: Using tidb connection "model.jaffle_shop.stg_orders"
22:08:57.119605 [debug] [Thread-1 (]: On model.jaffle_shop.stg_orders: /* {"app": "dbt", "dbt_version": "1.6.9", "profile_name": "jaffle_shop", "target_name": "dev", "node_id": "model.jaffle_shop.stg_orders"} */
drop view if exists `oceanbase`.`stg_orders__dbt_backup`
22:08:57.191539 [debug] [Thread-1 (]: SQL status: SUCCESS 0 in 0.0 seconds
22:08:57.196838 [debug] [Thread-1 (]: Timing info for model.jaffle_shop.stg_orders (execute): 22:08:56.736369 => 22:08:57.196496
22:08:57.197465 [debug] [Thread-1 (]: On model.jaffle_shop.stg_orders: Close
22:08:57.198946 [debug] [Thread-1 (]: Sending event: {'category': 'dbt', 'action': 'run_model', 'label': 'f43f4f98-1c20-4751-8101-18f6577098a1', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x10b00e090>]}
22:08:57.199806 [info ] [Thread-1 (]: 2 of 5 OK created sql view model oceanbase.stg_orders .......................... [SUCCESS 0 in 0.47s]
22:08:57.200556 [debug] [Thread-1 (]: Finished running node model.jaffle_shop.stg_orders
22:08:57.201094 [debug] [Thread-1 (]: Began running node model.jaffle_shop.stg_payments
22:08:57.201612 [info ] [Thread-1 (]: 3 of 5 START sql view model oceanbase.stg_payments ............................. [RUN]
22:08:57.202546 [debug] [Thread-1 (]: Re-using an available connection from the pool (formerly model.jaffle_shop.stg_orders, now model.jaffle_shop.stg_payments)
22:08:57.202993 [debug] [Thread-1 (]: Began compiling node model.jaffle_shop.stg_payments
22:08:57.206589 [debug] [Thread-1 (]: Writing injected SQL for node "model.jaffle_shop.stg_payments"
22:08:57.207501 [debug] [Thread-1 (]: Timing info for model.jaffle_shop.stg_payments (compile): 22:08:57.203264 => 22:08:57.207308
22:08:57.207855 [debug] [Thread-1 (]: Began executing node model.jaffle_shop.stg_payments
22:08:57.224227 [debug] [Thread-1 (]: Writing runtime sql for node "model.jaffle_shop.stg_payments"
22:08:57.224759 [debug] [Thread-1 (]: Using tidb connection "model.jaffle_shop.stg_payments"
22:08:57.224987 [debug] [Thread-1 (]: On model.jaffle_shop.stg_payments: BEGIN
22:08:57.225194 [debug] [Thread-1 (]: Opening a new connection, currently in state closed
22:08:57.286954 [debug] [Thread-1 (]: SQL status: SUCCESS 0 in 0.0 seconds
22:08:57.287364 [debug] [Thread-1 (]: Using tidb connection "model.jaffle_shop.stg_payments"
22:08:57.287725 [debug] [Thread-1 (]: On model.jaffle_shop.stg_payments: /* {"app": "dbt", "dbt_version": "1.6.9", "profile_name": "jaffle_shop", "target_name": "dev", "node_id": "model.jaffle_shop.stg_payments"} */create view `oceanbase`.`stg_payments__dbt_tmp`as (with source as (select * from `oceanbase`.`raw_payments`),renamed as (selectid as payment_id,order_id,payment_method,-- `amount` is currently stored in cents, so we convert it to dollarsamount / 100 as amountfrom source)select * from renamed);
22:08:57.369772 [debug] [Thread-1 (]: SQL status: SUCCESS 0 in 0.0 seconds
22:08:57.381916 [debug] [Thread-1 (]: Using tidb connection "model.jaffle_shop.stg_payments"
22:08:57.382347 [debug] [Thread-1 (]: On model.jaffle_shop.stg_payments: /* {"app": "dbt", "dbt_version": "1.6.9", "profile_name": "jaffle_shop", "target_name": "dev", "node_id": "model.jaffle_shop.stg_payments"} */drop view if exists `oceanbase`.`stg_payments__dbt_backup` cascade22:08:57.404650 [debug] [Thread-1 (]: SQL status: SUCCESS 0 in 0.0 seconds
22:08:57.406090 [debug] [Thread-1 (]: Using tidb connection "model.jaffle_shop.stg_payments"
22:08:57.406480 [debug] [Thread-1 (]: On model.jaffle_shop.stg_payments: /* {"app": "dbt", "dbt_version": "1.6.9", "profile_name": "jaffle_shop", "target_name": "dev", "node_id": "model.jaffle_shop.stg_payments"} */rename table `oceanbase`.`stg_payments` to `oceanbase`.`stg_payments__dbt_backup`22:08:57.476087 [debug] [Thread-1 (]: SQL status: SUCCESS 0 in 0.0 seconds
22:08:57.482015 [debug] [Thread-1 (]: Using tidb connection "model.jaffle_shop.stg_payments"
22:08:57.482508 [debug] [Thread-1 (]: On model.jaffle_shop.stg_payments: /* {"app": "dbt", "dbt_version": "1.6.9", "profile_name": "jaffle_shop", "target_name": "dev", "node_id": "model.jaffle_shop.stg_payments"} */drop view if exists `oceanbase`.`stg_payments` cascade22:08:57.504008 [debug] [Thread-1 (]: SQL status: SUCCESS 0 in 0.0 seconds
22:08:57.507457 [debug] [Thread-1 (]: Using tidb connection "model.jaffle_shop.stg_payments"
22:08:57.508634 [debug] [Thread-1 (]: On model.jaffle_shop.stg_payments: /* {"app": "dbt", "dbt_version": "1.6.9", "profile_name": "jaffle_shop", "target_name": "dev", "node_id": "model.jaffle_shop.stg_payments"} */rename table `oceanbase`.`stg_payments__dbt_tmp` to `oceanbase`.`stg_payments`22:08:57.584623 [debug] [Thread-1 (]: SQL status: SUCCESS 0 in 0.0 seconds
22:08:57.589099 [debug] [Thread-1 (]: On model.jaffle_shop.stg_payments: COMMIT
22:08:57.589588 [debug] [Thread-1 (]: Using tidb connection "model.jaffle_shop.stg_payments"
22:08:57.589984 [debug] [Thread-1 (]: On model.jaffle_shop.stg_payments: COMMIT
22:08:57.607837 [debug] [Thread-1 (]: SQL status: SUCCESS 0 in 0.0 seconds
22:08:57.614888 [debug] [Thread-1 (]: Using tidb connection "model.jaffle_shop.stg_payments"
22:08:57.615358 [debug] [Thread-1 (]: On model.jaffle_shop.stg_payments: /* {"app": "dbt", "dbt_version": "1.6.9", "profile_name": "jaffle_shop", "target_name": "dev", "node_id": "model.jaffle_shop.stg_payments"} */
drop view if exists `oceanbase`.`stg_payments__dbt_backup`
22:08:57.690624 [debug] [Thread-1 (]: SQL status: SUCCESS 0 in 0.0 seconds
22:08:57.695481 [debug] [Thread-1 (]: Timing info for model.jaffle_shop.stg_payments (execute): 22:08:57.208062 => 22:08:57.695113
22:08:57.696561 [debug] [Thread-1 (]: On model.jaffle_shop.stg_payments: Close
22:08:57.697780 [debug] [Thread-1 (]: Sending event: {'category': 'dbt', 'action': 'run_model', 'label': 'f43f4f98-1c20-4751-8101-18f6577098a1', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x10b02da10>]}
22:08:57.698487 [info ] [Thread-1 (]: 3 of 5 OK created sql view model oceanbase.stg_payments ........................ [SUCCESS 0 in 0.50s]
22:08:57.699066 [debug] [Thread-1 (]: Finished running node model.jaffle_shop.stg_payments
22:08:57.700156 [debug] [Thread-1 (]: Began running node model.jaffle_shop.customers
22:08:57.700680 [info ] [Thread-1 (]: 4 of 5 START sql table model oceanbase.customers ............................... [RUN]
22:08:57.701414 [debug] [Thread-1 (]: Re-using an available connection from the pool (formerly model.jaffle_shop.stg_payments, now model.jaffle_shop.customers)
22:08:57.701787 [debug] [Thread-1 (]: Began compiling node model.jaffle_shop.customers
22:08:57.707287 [debug] [Thread-1 (]: Writing injected SQL for node "model.jaffle_shop.customers"
22:08:57.708262 [debug] [Thread-1 (]: Timing info for model.jaffle_shop.customers (compile): 22:08:57.702033 => 22:08:57.708100
22:08:57.708561 [debug] [Thread-1 (]: Began executing node model.jaffle_shop.customers
22:08:57.737060 [debug] [Thread-1 (]: Using tidb connection "model.jaffle_shop.customers"
22:08:57.737355 [debug] [Thread-1 (]: On model.jaffle_shop.customers: BEGIN
22:08:57.737543 [debug] [Thread-1 (]: Opening a new connection, currently in state closed
22:08:57.794172 [debug] [Thread-1 (]: SQL status: SUCCESS 0 in 0.0 seconds
22:08:57.794493 [debug] [Thread-1 (]: Using tidb connection "model.jaffle_shop.customers"
22:08:57.794774 [debug] [Thread-1 (]: On model.jaffle_shop.customers: /* {"app": "dbt", "dbt_version": "1.6.9", "profile_name": "jaffle_shop", "target_name": "dev", "node_id": "model.jaffle_shop.customers"} */create viewoceanbase.temp_viewas (with customers as (select * from `oceanbase`.`stg_customers`),orders as (select * from `oceanbase`.`stg_orders`),payments as (select * from `oceanbase`.`stg_payments`),customer_orders as (selectcustomer_id,min(order_date) as first_order,max(order_date) as most_recent_order,count(order_id) as number_of_ordersfrom ordersgroup by customer_id),customer_payments as (selectorders.customer_id,sum(amount) as total_amountfrom paymentsleft join orders onpayments.order_id = orders.order_idgroup by orders.customer_id),final as (selectcustomers.customer_id,customers.first_name,customers.last_name,customer_orders.first_order,customer_orders.most_recent_order,customer_orders.number_of_orders,customer_payments.total_amount as customer_lifetime_valuefrom customersinner join customer_orderson customers.customer_id = customer_orders.customer_idinner join customer_paymentson customers.customer_id = customer_payments.customer_id)select * from final)22:08:57.895098 [debug] [Thread-1 (]: SQL status: SUCCESS 0 in 0.0 seconds
22:08:57.898817 [debug] [Thread-1 (]: Using tidb connection "model.jaffle_shop.customers"
22:08:57.899487 [debug] [Thread-1 (]: On model.jaffle_shop.customers: /* {"app": "dbt", "dbt_version": "1.6.9", "profile_name": "jaffle_shop", "target_name": "dev", "node_id": "model.jaffle_shop.customers"} */show columns from oceanbase.temp_view22:08:57.924479 [debug] [Thread-1 (]: SQL status: SUCCESS 7 in 0.0 seconds
22:08:57.926493 [debug] [Thread-1 (]: Using tidb connection "model.jaffle_shop.customers"
22:08:57.926786 [debug] [Thread-1 (]: On model.jaffle_shop.customers: /* {"app": "dbt", "dbt_version": "1.6.9", "profile_name": "jaffle_shop", "target_name": "dev", "node_id": "model.jaffle_shop.customers"} */create table if not exists`oceanbase`.`customers__dbt_tmp`(`customer_id`int(11)not null,`first_name`textnot null,`last_name`textnot null,`first_order`datenot null,`most_recent_order`datenot null,`number_of_orders`bigint(20)not null,`customer_lifetime_value`decimal(37,4)not null)22:08:58.097120 [debug] [Thread-1 (]: SQL status: SUCCESS 0 in 0.0 seconds
22:08:58.101002 [debug] [Thread-1 (]: Using tidb connection "model.jaffle_shop.customers"
22:08:58.101631 [debug] [Thread-1 (]: On model.jaffle_shop.customers: /* {"app": "dbt", "dbt_version": "1.6.9", "profile_name": "jaffle_shop", "target_name": "dev", "node_id": "model.jaffle_shop.customers"} */insert into `oceanbase`.`customers__dbt_tmp` select * from oceanbase.temp_view22:08:58.153432 [debug] [Thread-1 (]: SQL status: SUCCESS 62 in 0.0 seconds
22:08:58.156432 [debug] [Thread-1 (]: Using tidb connection "model.jaffle_shop.customers"
22:08:58.157239 [debug] [Thread-1 (]: On model.jaffle_shop.customers: /* {"app": "dbt", "dbt_version": "1.6.9", "profile_name": "jaffle_shop", "target_name": "dev", "node_id": "model.jaffle_shop.customers"} */drop view if exists oceanbase.temp_view22:08:58.227587 [debug] [Thread-1 (]: SQL status: SUCCESS 0 in 0.0 seconds
22:08:58.229403 [debug] [Thread-1 (]: Writing runtime sql for node "model.jaffle_shop.customers"
22:08:58.230580 [debug] [Thread-1 (]: Using tidb connection "model.jaffle_shop.customers"
22:08:58.231106 [debug] [Thread-1 (]: On model.jaffle_shop.customers: /* {"app": "dbt", "dbt_version": "1.6.9", "profile_name": "jaffle_shop", "target_name": "dev", "node_id": "model.jaffle_shop.customers"} */22:08:58.245210 [debug] [Thread-1 (]: SQL status: SUCCESS 0 in 0.0 seconds
22:08:58.252855 [debug] [Thread-1 (]: Using tidb connection "model.jaffle_shop.customers"
22:08:58.253059 [debug] [Thread-1 (]: On model.jaffle_shop.customers: /* {"app": "dbt", "dbt_version": "1.6.9", "profile_name": "jaffle_shop", "target_name": "dev", "node_id": "model.jaffle_shop.customers"} */drop table if exists `oceanbase`.`customers__dbt_backup` cascade22:08:58.274641 [debug] [Thread-1 (]: SQL status: SUCCESS 0 in 0.0 seconds
22:08:58.276792 [debug] [Thread-1 (]: Using tidb connection "model.jaffle_shop.customers"
22:08:58.277412 [debug] [Thread-1 (]: On model.jaffle_shop.customers: /* {"app": "dbt", "dbt_version": "1.6.9", "profile_name": "jaffle_shop", "target_name": "dev", "node_id": "model.jaffle_shop.customers"} */rename table `oceanbase`.`customers` to `oceanbase`.`customers__dbt_backup`22:08:58.348851 [debug] [Thread-1 (]: SQL status: SUCCESS 0 in 0.0 seconds
22:08:58.354702 [debug] [Thread-1 (]: Using tidb connection "model.jaffle_shop.customers"
22:08:58.355026 [debug] [Thread-1 (]: On model.jaffle_shop.customers: /* {"app": "dbt", "dbt_version": "1.6.9", "profile_name": "jaffle_shop", "target_name": "dev", "node_id": "model.jaffle_shop.customers"} */drop table if exists `oceanbase`.`customers` cascade22:08:58.375974 [debug] [Thread-1 (]: SQL status: SUCCESS 0 in 0.0 seconds
22:08:58.378890 [debug] [Thread-1 (]: Using tidb connection "model.jaffle_shop.customers"
22:08:58.379742 [debug] [Thread-1 (]: On model.jaffle_shop.customers: /* {"app": "dbt", "dbt_version": "1.6.9", "profile_name": "jaffle_shop", "target_name": "dev", "node_id": "model.jaffle_shop.customers"} */rename table `oceanbase`.`customers__dbt_tmp` to `oceanbase`.`customers`22:08:58.449860 [debug] [Thread-1 (]: SQL status: SUCCESS 0 in 0.0 seconds
22:08:58.461118 [debug] [Thread-1 (]: On model.jaffle_shop.customers: COMMIT
22:08:58.461648 [debug] [Thread-1 (]: Using tidb connection "model.jaffle_shop.customers"
22:08:58.461999 [debug] [Thread-1 (]: On model.jaffle_shop.customers: COMMIT
22:08:58.476014 [debug] [Thread-1 (]: SQL status: SUCCESS 0 in 0.0 seconds
22:08:58.479457 [debug] [Thread-1 (]: Using tidb connection "model.jaffle_shop.customers"
22:08:58.479875 [debug] [Thread-1 (]: On model.jaffle_shop.customers: /* {"app": "dbt", "dbt_version": "1.6.9", "profile_name": "jaffle_shop", "target_name": "dev", "node_id": "model.jaffle_shop.customers"} */
drop table if exists `oceanbase`.`customers__dbt_backup`
22:08:58.583995 [debug] [Thread-1 (]: SQL status: SUCCESS 0 in 0.0 seconds
22:08:58.585602 [debug] [Thread-1 (]: Timing info for model.jaffle_shop.customers (execute): 22:08:57.708757 => 22:08:58.585436
22:08:58.585951 [debug] [Thread-1 (]: On model.jaffle_shop.customers: Close
22:08:58.586683 [debug] [Thread-1 (]: Sending event: {'category': 'dbt', 'action': 'run_model', 'label': 'f43f4f98-1c20-4751-8101-18f6577098a1', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x10c884f10>]}
22:08:58.587183 [info ] [Thread-1 (]: 4 of 5 OK created sql table model oceanbase.customers .......................... [SUCCESS 0 in 0.89s]
22:08:58.587656 [debug] [Thread-1 (]: Finished running node model.jaffle_shop.customers
22:08:58.587999 [debug] [Thread-1 (]: Began running node model.jaffle_shop.orders
22:08:58.588335 [info ] [Thread-1 (]: 5 of 5 START sql table model oceanbase.orders .................................. [RUN]
22:08:58.588989 [debug] [Thread-1 (]: Re-using an available connection from the pool (formerly model.jaffle_shop.customers, now model.jaffle_shop.orders)
22:08:58.589298 [debug] [Thread-1 (]: Began compiling node model.jaffle_shop.orders
22:08:58.592665 [debug] [Thread-1 (]: Writing injected SQL for node "model.jaffle_shop.orders"
22:08:58.593351 [debug] [Thread-1 (]: Timing info for model.jaffle_shop.orders (compile): 22:08:58.589484 => 22:08:58.593212
22:08:58.593607 [debug] [Thread-1 (]: Began executing node model.jaffle_shop.orders
22:08:58.607013 [debug] [Thread-1 (]: Using tidb connection "model.jaffle_shop.orders"
22:08:58.607297 [debug] [Thread-1 (]: On model.jaffle_shop.orders: BEGIN
22:08:58.607492 [debug] [Thread-1 (]: Opening a new connection, currently in state closed
22:08:58.660110 [debug] [Thread-1 (]: SQL status: SUCCESS 0 in 0.0 seconds
22:08:58.660481 [debug] [Thread-1 (]: Using tidb connection "model.jaffle_shop.orders"
22:08:58.660790 [debug] [Thread-1 (]: On model.jaffle_shop.orders: /* {"app": "dbt", "dbt_version": "1.6.9", "profile_name": "jaffle_shop", "target_name": "dev", "node_id": "model.jaffle_shop.orders"} */create viewoceanbase.temp_viewas (with orders as (select * from `oceanbase`.`stg_orders`),payments as (select * from `oceanbase`.`stg_payments`),order_payments as (selectorder_id,sum(case when payment_method = 'credit_card' then amount else 0 end) as credit_card_amount,sum(case when payment_method = 'coupon' then amount else 0 end) as coupon_amount,sum(case when payment_method = 'bank_transfer' then amount else 0 end) as bank_transfer_amount,sum(case when payment_method = 'gift_card' then amount else 0 end) as gift_card_amount,sum(amount) as total_amountfrom paymentsgroup by order_id),final as (selectorders.order_id,orders.customer_id,orders.order_date,orders.status,order_payments.credit_card_amount,order_payments.coupon_amount,order_payments.bank_transfer_amount,order_payments.gift_card_amount,order_payments.total_amount as amountfrom ordersleft join order_paymentson orders.order_id = order_payments.order_id)select * from final)22:08:58.746833 [debug] [Thread-1 (]: SQL status: SUCCESS 0 in 0.0 seconds
22:08:58.749953 [debug] [Thread-1 (]: Using tidb connection "model.jaffle_shop.orders"
22:08:58.750953 [debug] [Thread-1 (]: On model.jaffle_shop.orders: /* {"app": "dbt", "dbt_version": "1.6.9", "profile_name": "jaffle_shop", "target_name": "dev", "node_id": "model.jaffle_shop.orders"} */show columns from oceanbase.temp_view22:08:58.774376 [debug] [Thread-1 (]: SQL status: SUCCESS 9 in 0.0 seconds
22:08:58.777285 [debug] [Thread-1 (]: Using tidb connection "model.jaffle_shop.orders"
22:08:58.778144 [debug] [Thread-1 (]: On model.jaffle_shop.orders: /* {"app": "dbt", "dbt_version": "1.6.9", "profile_name": "jaffle_shop", "target_name": "dev", "node_id": "model.jaffle_shop.orders"} */create table if not exists`oceanbase`.`orders__dbt_tmp`(`order_id`int(11)not null,`customer_id`int(11)not null,`order_date`datenot null,`status`textnot null,`credit_card_amount`decimal(37,4)not null,`coupon_amount`decimal(37,4)not null,`bank_transfer_amount`decimal(37,4)not null,`gift_card_amount`decimal(37,4)not null,`amount`decimal(37,4)not null)22:08:58.901266 [debug] [Thread-1 (]: SQL status: SUCCESS 0 in 0.0 seconds
22:08:58.904156 [debug] [Thread-1 (]: Using tidb connection "model.jaffle_shop.orders"
22:08:58.904774 [debug] [Thread-1 (]: On model.jaffle_shop.orders: /* {"app": "dbt", "dbt_version": "1.6.9", "profile_name": "jaffle_shop", "target_name": "dev", "node_id": "model.jaffle_shop.orders"} */insert into `oceanbase`.`orders__dbt_tmp` select * from oceanbase.temp_view22:08:58.942316 [debug] [Thread-1 (]: SQL status: SUCCESS 99 in 0.0 seconds
22:08:58.944322 [debug] [Thread-1 (]: Using tidb connection "model.jaffle_shop.orders"
22:08:58.944929 [debug] [Thread-1 (]: On model.jaffle_shop.orders: /* {"app": "dbt", "dbt_version": "1.6.9", "profile_name": "jaffle_shop", "target_name": "dev", "node_id": "model.jaffle_shop.orders"} */drop view if exists oceanbase.temp_view22:08:59.018315 [debug] [Thread-1 (]: SQL status: SUCCESS 0 in 0.0 seconds
22:08:59.021627 [debug] [Thread-1 (]: Writing runtime sql for node "model.jaffle_shop.orders"
22:08:59.023735 [debug] [Thread-1 (]: Using tidb connection "model.jaffle_shop.orders"
22:08:59.024275 [debug] [Thread-1 (]: On model.jaffle_shop.orders: /* {"app": "dbt", "dbt_version": "1.6.9", "profile_name": "jaffle_shop", "target_name": "dev", "node_id": "model.jaffle_shop.orders"} */22:08:59.039661 [debug] [Thread-1 (]: SQL status: SUCCESS 0 in 0.0 seconds
22:08:59.052185 [debug] [Thread-1 (]: Using tidb connection "model.jaffle_shop.orders"
22:08:59.052598 [debug] [Thread-1 (]: On model.jaffle_shop.orders: /* {"app": "dbt", "dbt_version": "1.6.9", "profile_name": "jaffle_shop", "target_name": "dev", "node_id": "model.jaffle_shop.orders"} */drop table if exists `oceanbase`.`orders__dbt_backup` cascade22:08:59.071808 [debug] [Thread-1 (]: SQL status: SUCCESS 0 in 0.0 seconds
22:08:59.073143 [debug] [Thread-1 (]: Using tidb connection "model.jaffle_shop.orders"
22:08:59.073565 [debug] [Thread-1 (]: On model.jaffle_shop.orders: /* {"app": "dbt", "dbt_version": "1.6.9", "profile_name": "jaffle_shop", "target_name": "dev", "node_id": "model.jaffle_shop.orders"} */rename table `oceanbase`.`orders` to `oceanbase`.`orders__dbt_backup`22:08:59.150592 [debug] [Thread-1 (]: SQL status: SUCCESS 0 in 0.0 seconds
22:08:59.156193 [debug] [Thread-1 (]: Using tidb connection "model.jaffle_shop.orders"
22:08:59.156664 [debug] [Thread-1 (]: On model.jaffle_shop.orders: /* {"app": "dbt", "dbt_version": "1.6.9", "profile_name": "jaffle_shop", "target_name": "dev", "node_id": "model.jaffle_shop.orders"} */drop table if exists `oceanbase`.`orders` cascade22:08:59.176531 [debug] [Thread-1 (]: SQL status: SUCCESS 0 in 0.0 seconds
22:08:59.179595 [debug] [Thread-1 (]: Using tidb connection "model.jaffle_shop.orders"
22:08:59.180285 [debug] [Thread-1 (]: On model.jaffle_shop.orders: /* {"app": "dbt", "dbt_version": "1.6.9", "profile_name": "jaffle_shop", "target_name": "dev", "node_id": "model.jaffle_shop.orders"} */rename table `oceanbase`.`orders__dbt_tmp` to `oceanbase`.`orders`22:08:59.248180 [debug] [Thread-1 (]: SQL status: SUCCESS 0 in 0.0 seconds
22:08:59.253036 [debug] [Thread-1 (]: On model.jaffle_shop.orders: COMMIT
22:08:59.253797 [debug] [Thread-1 (]: Using tidb connection "model.jaffle_shop.orders"
22:08:59.254205 [debug] [Thread-1 (]: On model.jaffle_shop.orders: COMMIT
22:08:59.268802 [debug] [Thread-1 (]: SQL status: SUCCESS 0 in 0.0 seconds
22:08:59.275387 [debug] [Thread-1 (]: Using tidb connection "model.jaffle_shop.orders"
22:08:59.275828 [debug] [Thread-1 (]: On model.jaffle_shop.orders: /* {"app": "dbt", "dbt_version": "1.6.9", "profile_name": "jaffle_shop", "target_name": "dev", "node_id": "model.jaffle_shop.orders"} */
drop table if exists `oceanbase`.`orders__dbt_backup`
22:08:59.460776 [debug] [Thread-1 (]: SQL status: SUCCESS 0 in 0.0 seconds
22:08:59.465491 [debug] [Thread-1 (]: Timing info for model.jaffle_shop.orders (execute): 22:08:58.593775 => 22:08:59.465044
22:08:59.466131 [debug] [Thread-1 (]: On model.jaffle_shop.orders: Close
22:08:59.467397 [debug] [Thread-1 (]: Sending event: {'category': 'dbt', 'action': 'run_model', 'label': 'f43f4f98-1c20-4751-8101-18f6577098a1', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x10c885950>]}
22:08:59.468191 [info ] [Thread-1 (]: 5 of 5 OK created sql table model oceanbase.orders ............................. [SUCCESS 0 in 0.88s]
22:08:59.468896 [debug] [Thread-1 (]: Finished running node model.jaffle_shop.orders
22:08:59.471036 [debug] [MainThread]: Using tidb connection "master"
22:08:59.471461 [debug] [MainThread]: On master: BEGIN
22:08:59.471741 [debug] [MainThread]: Opening a new connection, currently in state closed
22:08:59.556370 [debug] [MainThread]: SQL status: SUCCESS 0 in 0.0 seconds
22:08:59.557436 [debug] [MainThread]: On master: COMMIT
22:08:59.557989 [debug] [MainThread]: Using tidb connection "master"
22:08:59.558425 [debug] [MainThread]: On master: COMMIT
22:08:59.574008 [debug] [MainThread]: SQL status: SUCCESS 0 in 0.0 seconds
22:08:59.574684 [debug] [MainThread]: On master: Close
22:08:59.576154 [debug] [MainThread]: Connection 'master' was properly closed.
22:08:59.576798 [debug] [MainThread]: Connection 'model.jaffle_shop.orders' was properly closed.
22:08:59.577387 [info ] [MainThread]:
22:08:59.578056 [info ] [MainThread]: Finished running 3 view models, 2 table models in 0 hours 0 minutes and 3.87 seconds (3.87s).
22:08:59.579442 [debug] [MainThread]: Command end result
22:08:59.591150 [info ] [MainThread]:
22:08:59.591525 [info ] [MainThread]: Completed successfully
22:08:59.591792 [info ] [MainThread]:
22:08:59.592064 [info ] [MainThread]: Done. PASS=5 WARN=0 ERROR=0 SKIP=0 TOTAL=5
22:08:59.592567 [debug] [MainThread]: Command `dbt run` succeeded at 22:08:59.592489 after 4.01 seconds
22:08:59.592899 [debug] [MainThread]: Sending event: {'category': 'dbt', 'action': 'invocation', 'label': 'end', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x10a4eff10>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x1046a7f50>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x1046b4dd0>]}
22:08:59.593216 [debug] [MainThread]: Flushing usage events
对于 view stg_customers，dbt 的处理过程如下：
create view `oceanbase`.`stg_customers__dbt_tmp` as ...;
drop view if exists `oceanbase`.`stg_customers__dbt_backup` cascade;
rename table `oceanbase`.`stg_customers` to `oceanbase`.`stg_customers__dbt_backup`;
drop view if exists `oceanbase`.`stg_customers` cascade;
rename table `oceanbase`.`stg_customers__dbt_tmp` to `oceanbase`.`stg_customers`;
可见，它依靠创建临时 view，操作无误后通过 rename 的方式保证“原子性”。
对于 table orders，dbt 的处理过程如下：
create view oceanbase.temp_view as ( ... select * from final );
create table if not exists `oceanbase`.`orders__dbt_tmp` (...);
insert into `oceanbase`.`orders__dbt_tmp` select * from oceanbase.temp_view
drop view if exists oceanbase.temp_view;
drop table if exists `oceanbase`.`orders__dbt_backup` cascade;
rename table `oceanbase`.`orders` to `oceanbase`.`orders__dbt_backup`;
drop table if exists `oceanbase`.`orders` cascade;
rename table `oceanbase`.`orders__dbt_tmp` to `oceanbase`.`orders`;
这里面，它创建了一个临时 temp_view，然后把 temp_view 的内容插入到 orders 表里。为了保证原子性，也使用了类似 view 的手法，创建了一些临时表。
作为一个完整的产品，dbt 还提供了一些贴心功能给数据使用者：
dbt docs generate 能够给数仓表生成文档，dbt docs serve 能启动一个服务用于浏览这些文档。真的很贴心。
相关文章：
学习数仓工具 dbt
DBT 是一个有趣的工具，它通过一种结构化的方式定义了数仓中各种表、视图的构建和填充方式。 dbt 面相的对象是数据开发团队，提供了如下几个最有价值的能力： 支持多种数据库通过 select 来定义数据，无需编写 DML构建数据时&#…...
高录用快见刊【最快会后两个月左右见刊】第三届社会科学与人文艺术国际学术会议 (SSHA 2024)
第三届社会科学与人文艺术国际学术会议 (SSHA 2024) 2024 3rd International Conference on Social Sciences and Humanities and Arts *文章投稿均可免费参会 *高录用快见刊【最快会后两个月左右见刊】 重要信息 会议官网：icssha.com 大会时间：202…...
C语言-指针初学速成
1.指针是什么 C语言指针是一种特殊的变量，用于存储内存地址。它可以指向其他变量或者其他数据结构，通过指针可以直接访问或修改存储在指定地址的值。指针可以帮助我们在程序中动态地分配和释放内存，以及进行复杂的数据操作。在C语言中&#…...
MQL语言实现单元测试
文章目录 一、单元测试是什么二、单元测试的过程三、为什么需要单元测试四、MQL测试代码实现 一、单元测试是什么 单元测试是对软件中最小可测单元（如类或函数）进行独立验证和检查的过程。它是由开发工程师完成的，旨在确保每个单元的功能和逻…...
Redis信创平替之TongRDS(东方通),麒麟系统安装步骤
我的系统: 银河麒麟桌面系统V10(SP1)兆芯版 1.先进入东方通申请使用 2.客服会发送一个TongRDS包与center.lic给你(我这里只拿到.tar.gz文件,没有网上的什么安装版) 3.上传全部文件到目录中 4.服务节点安装,并启动 tar -zxvf TongRDS-2.2.1.2_P3.Node.tar.gz cd pmemdb/bin/…...
nginx服务
“欢唱吧，呼唤它，回来啊~” Web服务器简介 Web服务器，一般是指“网站服务器”，其本质就是驻留于互联网中，某一台机器(计算机)上的进程(程序)。Web服务器通常就是为用户提供信息浏览服务，更可以放置数据文件…...
多数pythoneer只知有列表list却不知道python也有array数组
数组和列表 Python中数组和列表是不同的，我敢断言大多数的pythoneer只知道有列表list，却不知道python也有array数组。列表是一个包含不同数据类型的元素集合，而数组是一个只能含相同数据类型的元素集合。 Python的array库是一个提供数组操作…...
【Rust】——控制流（if-else,循环）
🎃个人专栏： 🐬 算法设计与分析：算法设计与分析_IT闫的博客-CSDN博客 🐳Java基础：Java基础_IT闫的博客-CSDN博客 🐋c语言：c语言_IT闫的博客-CSDN博客 🐟MySQL：…...
通过platform总线驱动框架编写LED灯的驱动，编写应用程序测试
mydev.c #include <linux/init.h> #include <linux/module.h> #include <linux/of_gpio.h> #include <linux/gpio.h> #include <linux/platform_device.h> #include <linux/mod_devicetable.h>// 创建功能码 #define LED_ON _IO(l, 1) #d…...
费舍尔FISHER金属探测器探测仪维修F70
美国FISHER LABS费舍尔地下金属探测器，金属探测仪等维修（考古探金银铜探宝等仪器）。 费舍尔F70视听目标ID金属探测器，Fisher 金属探测器公司成立于1931年，在实验条件很艰苦的情况下，研发出了地下金属探测器…...
Airtest-Selenium实操小课③：下载可爱猫猫图片
1. 前言 那么这周我们看看如何实现使用Airtest-Selenium实现自动搜索下载可爱的猫猫图片吧~ 2. 需求分析和准备 整体的需求大致可以分为以下步骤： 打开chrome浏览器 打开百度网页 搜索“可爱猫猫图片” 定位图片元素 创建存储图片的文件夹 下载可爱猫猫图片…...
Druid无法登录监控页面
问题表现：在配置和依赖都正确的情况下，无法通过配置的用户名密码登录Druid的监控页面 检查配置发现 配置的用户名和密码和请求中参数是一致的🤔 Debug发现 ResourceServlet 是Druid的登录实现， 且调试发现usernameParam是null&am…...
【Linux系统化学习】深入理解匿名管道(pipe)和命名管道(fifo)
目录 进程间通信 进程间通信目的 进程间通信的方式 管道 System V IPC（本地通信） POSIX IPC（网络通信） 管道 什么是管道 匿名管道 匿名管道的创建 匿名管道的使用 匿名管道的四种情况 匿名管道的五种特性 命名管道 …...
信息学奥赛一本通1209：分数求和
1209：分数求和 时间限制: 1000 ms 内存限制: 65536 KB 提交数: 19111 通过数: 10647 【题目描述】 输入n个分数并对他们求和，并用最简形式表示。所谓最简形式是指：分子分母的最大公约数为11；若最终结果的分母为11&am…...
LabVIEW储氢材料循环寿命测试系统
LabVIEW储氢材料循环寿命测试系统 随着氢能技术的发展，固态储氢技术因其高密度和安全性成为研究热点。储氢材料的循环寿命是衡量其工程应用的关键。然而，传统的循环寿命测试设备存在成本高、测试效率低、数据处理复杂等问题。设计了一种基于LabVIEW软件…...
Unity3D 框架如何搭建基于纯Lua的U框架与开发模式详解
前言 Unity3D 是一款非常流行的游戏开发引擎，它支持C#、JavaScript和Boo等多种脚本语言。而Lua语言作为一种轻量级的脚本语言，也在游戏开发中得到了广泛应用。本文将介绍如何在Unity3D框架中搭建基于纯Lua的U框架，并详细讲解其开发模式。 对…...
Linux常见指令(2)
目录 1、tar指令 ！ 2、bc指令 3、uname 4、重要热键 5、关机 1、tar指令 ！ 功能：压缩/解压缩文件或目录,类似zip 我们先来看一下我们的文件即目录，接下来我们输入指令： tar -czf test.tgz test 压缩 -c …...
【C++】封装
1.封装的意义 封装是C面向对象三大特性之一 实例化（通过一个类 创建一个对象的过程） 类中的属性和行为 我们统一称为 成员 属性 成员属性 成员变量 行为 成员函数 成员方法 封装的意义： 1.将属性和行为作为一个整体，表现生活中的事…...
Maxwell安装部署
1 Maxwell输出格式 database：变更数据所属的数据库table：变更数据所属的表type：数据变更类型ts：数据变更发生的时间xid：事务idcommit：事务提交标志，可用于重新组装事务data：对于inse…...
说一下JVM类加载机制？
Java中的所有类，都需要由类加载器装载到JVM中才能运行。类加载器本身也是一个类，而它的工作就是把class文件从硬盘读取到内存中。 在写程序的时候，我们几乎不需要关心类的加载，因为这些都是隐式装载的，除非我们有特殊…...
解决SpringAMQP工作队列模型程序报错：WARN 48068:Failed to declare queue: simple.queue
这里写目录标题 1.运行环境2.报错信息3.解决方案4.查看解决之后的效果 1.运行环境 使用docker运行了RabbitMQ的服务器： 在idea中导入springAMQP的jar包，分别编写了子模块生产者publisher，消费者consumer： 1.在publisher中运行测试…...
mysql在服务器中的主从复制Linux下
mysql在服务器中的主从复制Linux下 为什么要进行主从复制主从复制的原理主从复制执行流程操作步骤主库创建从库创建 测试 为什么要进行主从复制 在业务中通常会有情况，在sql执行时，将表锁住，导致不能进行查询，这样就会影响业务的…...
QT-Day2
思维导图 作业 使用手动连接，将登录框中的取消按钮使用qt4版本的连接到自定义的槽函数中，在自定义的槽函数中调用关闭函数 将登录按钮使用qt5版本的连接到自定义的槽函数中，在槽函数中判断ui界面上输入的账号是否为"admin"&#x…...
流量分析——陇剑杯 2021【签到、jwt】
目录 签到1、攻击者正在进行的可能是什么协议的网络攻击 jwt1、该网站使用了______认证方式。前置知识：解： 2、黑客绕过验证使用的jwt中，id和username是3、黑客获取webshell之后，权限是什么4、黑客上传的恶意文件文件名是5、黑客在…...
Java并发基础：原子类之AtomicIntegerFieldUpdater全面解析
本文概要 AtomicIntegerFieldUpdater类提供了一种高效、简洁的方式来原子性地更新对象的volatile字段，无需使用重量级的锁机制，它通过基于反射的API实现了细粒度的并发控制，提升了多线程环境下的性能表现。 AtomicIntegerFieldUpdater核心概…...
普中51单片机学习（串口通信）
串口通信 原理 计算机通信是将计算机技术和通信技术的相结合，完成计算机与外部设备或计算机与计算机之间的信息交换 。可以分为两大类：并行通信与串行通信。并行通信通常是将数据字节的各位用多条数据线同时进行传送 。控制简单、传输速度快࿱…...
【ArcGIS】利用高程进行坡度分析
在ArcGIS中利用高程进行坡度分析 坡度ArcGIS实操参考 坡度 坡度是地表单元陡缓的程度，通常把坡面的垂直高度和水平距离的比值称为坡度。 坡度的表示方法有百分比法、度数法、密位法和分数法四种，其中以百分比法和度数法较为常用。 （1&#…...
递归读取文件夹下的所有文件
水一篇文章 🐶 代码 package file;import org.apache.commons.lang3.StringUtils; import org.junit.Test;import java.io.File; import java.util.Objects;/*** FlattenDirFiles** author allens* date 2024/2/19*/ public class FlattenDirFiles {// 文件数量pri…...
phpspreadsheet导出数据和图片到excel
仅作记录，废话不多说 前提是已经安装了phpspreadsheet （ composer require phpoffice/phpspreadsheet ） 一、 数据拼装，调用excel类 <?php /*** 电子台账* Date: 2023/4/20* Time: 17:28*/namespace app\store\controlle…...
Seata的 TCC 模式
目录 概述 使用 依赖与配置 代码 概述 TCC 模式是一种侵入式的分布式事务解决方案，它不依赖于数据库的事务，而是要求开发者自定义完成 预提交、提交、回滚的方法逻辑。因此，它是一个种偏 复杂、灵活、有侵入性 的分布式事务处理方案。 De…...
ssm做的直播网站/北京网络营销推广培训哪家好
1080P实战利器搭建最近《使命召唤：战区》热度还在持续攀升，前段时间硬核写过一个关于这款游戏6000元的配置推荐，才没过一个星期，官方Twitter再次宣布玩家人数翻倍突破3000万，想起评论区有玩家反映最近装机成本太高&…...
龙岗网站制作公司/获客渠道有哪些
插件名说明open in browser浏览器中打开live server实时预览prettier代码格式化 Auto Close Tag Chinese (Simplified) Auto Rename Tag Beautify BEM Helper Code Runner CSS Tree Easy LESS EditorConfig for VS Code Git Graph GitHub Copilot HTML CSS Support Live Server…...
做一元购网站 要多少钱/百度双十一活动
mysql 安装双击d:\websoft\mysql-essential-5.1.63-win32.msi 就开始安装了 看图操作出现上面的界面，不要点cancel 那是取消， 等等选择“custom”然后点“next” 出现下面的界面这个是选择mysql 安装的位置，默认在c盘，我们点“cha…...
做自己看视频的网站/免费域名服务器
2019独角兽企业重金招聘Python工程师标准>>> Fiori里的busy dialog有两种表现形式，一种是下图里的花朵形状，由5个不断旋转的花瓣组成。另一种是下图的3/4个圆环不断旋转的效果。 关于前者的效果，可以看我制作的这个视频。这个视频…...
电子书网站怎么做/接app推广的单子在哪接
前提：使用python3一：Django连接MySQL时默认使用MySQLdb驱动，但MySQLdb不支持Python3，因此这里将MySQL驱动设置为pymysql。具体步骤如下：(1)使用 pip install pymysql 进行安装(2)在项目目录下的__init__.py文件中添加以…...
网站搭建代理/网站模板下载
推荐阅读： 这套Github上40Kstar学习笔记，可以帮你搞定95%以上的Java面试 毫不夸张的说，这份SpringBoot学习指南能解决你遇到的98%的问题 最全面试题新鲜出炉：70算法题、近30种大厂面试笔试常考知识点 前言 最近在做的业务场景…...