dbt 集成presto试用

时间:2022-12-22 00:55:15

dbt 团队提供了presto 的adapter同时也是一个不错的的参考实现,可以学习

当前dbt presto 对于版本的要求是0.13.1 对于当前最新版本的还不支持,同时需要使用源码安装pip 包

dbt presto pip 包安装

  • clone 代码包
git clone https://github.com/fishtown-analytics/dbt-presto.git
  • pip 开发模式安装
cd dbt-presto
pip install -e .

配置presto profile

  • profiles.yml 文件
my-presto-db:
  target: dev
  outputs:
    dev:
      type: presto
      method: none
      host: <your server ip >
      port: 8080
      database: memory
      schema: default
      threads: 8

demo 项目

  • dbt project 配置

    dbt_project.yml 文件

# Name your package! Package names should contain only lowercase characters
# and underscores. A good package name should reflect your organization's
# name or the intended use of these models
name: 'my_new_package'
version: '1.0'
# This setting configures which "profile" dbt uses for this project. Profiles contain
# database connection information, and should be configured in the ~/.dbt/profiles.yml file
profile: 'my-presto-db'
# These configurations specify where dbt should look for different types of files.
# The `source-paths` config, for example, states that source models can be found
# in the "models/" directory. You probably won't need to change these!
source-paths: ["models"]
analysis-paths: ["analysis"] 
test-paths: ["tests"]
data-paths: ["data"]
macro-paths: ["macros"]
target-path: "target" # directory which will store compiled SQL files
clean-targets: # directories to be removed by `dbt clean`
    - "target"
    - "dbt_modules"
# You can define configurations for models in the `source-paths` directory here.
# Using these configurations, you can enable or disable models, change how they
# are materialized, and more!
# In this example config, we tell dbt to build all models in the example/ directory
# as views (the default). These settings can be overridden in the individual model files
# using the `{{ config(...) }}` macro.
models:
  my_new_package:
      # Applies to all files under models/example/
      example:
          materialized: view
  • 简单model

    models/example/my_first_dbt_model.sql

-- Welcome to your first dbt model!
-- Did you know that you can also configure models directly within
-- the SQL file? This will override configurations stated in dbt_project.yml
-- Try changing 'view' to 'table', then re-running dbt
{{ config(materialized='table') }}
SELECT * from tpch.tiny.nation

编译&&运行

  • 编译
dbt compile
  • 执行
dbt run
 

效果:

dbt run
Running with dbt=0.13.0-rc1
Found 96 macros, 0 analyses, 0 archives, 0 sources, 0 seed files, 0 tests, 0 operations, 1 models
No handlers could be found for logger "prestodb.transaction"
16:26:09 | Concurrency: 8 threads (target='dev')
16:26:09 | 
16:26:09 | 1 of 1 START table model default.my_first_dbt_model.................. [RUN]
16:26:12 | 1 of 1 OK created table model default.my_first_dbt_model............. [OK in 1.95s]
16:26:12 | 
16:26:12 | Finished running 1 table models in 6.57s.
Completed successfully
Done. PASS=1 ERROR=0 SKIP=0 TOTAL=1

persto server 查询

  • 环境使用的docker 运行
    以下为参考运行脚本
docker run -d -p 8080:8080 --name demo starburstdata/presto
  • 进入容器查询
docker exec -it demo sh
presto-cli
查询table 数据:
use memory.default;
show tables;
       Table
--------------------
 my_first_dbt_model
 nation
(2 rows)
Query 20190711_083342_00134_ucty8, FINISHED, 1 node
Splits: 19 total, 19 done (100.00%)
0:00 [2 rows, 58B] [11 rows/s, 343B/s]
 select * from my_first_dbt_model;
 nationkey | name | regionkey |
-----------+----------------+-----------+---------------------------------------
         0 | ALGERIA | 0 | haggle. carefully final deposits dete
         1 | ARGENTINA | 1 | al foxes promise slyly according to th
         2 | BRAZIL | 1 | y alongside of the pending deposits. c
         3 | CANADA | 1 | eas hang ironic, silent packages. slyl
         4 | EGYPT | 4 | y above the carefully unusual theodoli
         5 | ETHIOPIA | 0 | ven packages wake quickly. regu
         6 | FRANCE | 3 | refully final requests. regular, ironi
         7 | GERMANY | 3 | l platelets. regular accounts x-ray: u
         8 | INDIA | 2 | ss excuses cajole slyly across the pac
         9 | INDONESIA | 2 | slyly express asymptotes. regular dep
        10 | IRAN | 4 | efully alongside of the slyly final de
        11 | IRAQ | 4 | nic deposits boost atop the quickly fi
        12 | JAPAN | 2 | ously. final, express gifts cajole a
        13 | JORDAN | 4 | ic deposits are blithely about the car
        14 | KENYA |

说明

以上是一个简单的集成测试,后边可以学习下源码,看看dbt apdater 的编写

参考资料

https://github.com/fishtown-analytics/dbt-presto
https://docs.getdbt.com/docs/building-a-new-adapter
https://github.com/rongfengliang/dbt-0.14.0-learning
https://www.cnblogs.com/rongfengliang/p/11164355.html
https://github.com/fishtown-analytics/dbt-presto/issues/4
https://github.com/fishtown-analytics/dbt-presto/issues/6