有没有卖设计的网站,婚纱摄影网站模板之家,seo公司网站推广,飞虹网架建设官方网站DBT 是一个有趣的工具#xff0c;它通过一种结构化的方式定义了数仓中各种表、视图的构建和填充方式。
dbt 面相的对象是数据开发团队#xff0c;提供了如下几个最有价值的能力#xff1a;
支持多种数据库通过 select 来定义数据#xff0c;无需编写 DML构建数据时#…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(admintest)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 dbt1.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: tidb1.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 tablewhen table_type SYSTEM TABLE then tablewhen table_type SYSTEM VIEW then viewwhen table_type VIEW then viewelse table_typeend as table_typefrom information_schema.tableswhere table_schema oceanbase22: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 (targetdev)
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_tmpas (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_backup22: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_customers22: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_tmpas (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_backup22: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_orders22: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_tmpas (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_backup22: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_payments22: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 existsoceanbase.customers__dbt_tmp(customer_idint(11)not null,first_nametextnot null,last_nametextnot null,first_orderdatenot null,most_recent_orderdatenot null,number_of_ordersbigint(20)not null,customer_lifetime_valuedecimal(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_backup22: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.customers22: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 existsoceanbase.orders__dbt_tmp(order_idint(11)not null,customer_idint(11)not null,order_datedatenot null,statustextnot null,credit_card_amountdecimal(37,4)not null,coupon_amountdecimal(37,4)not null,bank_transfer_amountdecimal(37,4)not null,gift_card_amountdecimal(37,4)not null,amountdecimal(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_backup22: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.orders22: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. PASS5 WARN0 ERROR0 SKIP0 TOTAL5
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_customersdbt 的处理过程如下
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 ordersdbt 的处理过程如下
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 能启动一个服务用于浏览这些文档。真的很贴心。
