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
16:26:12 | 1 of 1 OK created table model default.my_first_dbt_model
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