dbt 0.14.0 试用

dbt 0.14.0 在最近已经发布了，dbt server 的还是很不错的特性，以下安装试用下几个新功能

环境准备

安装

如果没有安装的：
pip install  dbt
已经安装的：
pip install -U dbt

pg 数据库环境准备

使用docker-compose 运行

  • docker-compose 文件
version: "3"
services:
   database:
    image: postgres
    environment:
      POSTGRES_USER: "postgres"
      POSTGRES_PASSWORD: "dalong"
    ports:
      - "5432:5432"
   database2:
    image: postgres
    environment:
      POSTGRES_USER: "postgres"
      POSTGRES_PASSWORD: "dalong"
    ports:
      - "5433:5432"
rpc

创建简单项目

  • dbt init
dbt init 14demo
  • 配置profile

    profiles.yml 文件

# For more information on how to configure this file, please see:
# https://github.com/fishtown-analytics/dbt/blob/master/sample.profiles.yml
default:
  target: dev
  outputs:
    dev:
      type: postgres
      host: 127.0.0.1
      user: postgres
      pass: password
      port: 5432
      dbname: postgres
      schema: dalong
      threads: 3
pg:
  target: dev
  outputs:
    dev:
      type: postgres
      host: 127.0.0.1
      user: postgres
      pass: dalong
      port: 5432
      dbname: postgres
      schema: public
      threads: 3
demo:
  target: dev
  outputs:
    dev:
      type: postgres
      host: 127.0.0.1
      user: postgres
      pass: dalong
      port: 5432
      dbname: postgres
      schema: demo
      threads: 3

基本使用

  • 启动pg
docker-compose up -d
  • 启动dbt server rpc 服务
dbt rpc

效果

dbt rpc 
Running with dbt=0.14.0
Found 116 macros, 0 analyses, 0 sources, 0 seed files, 0 snapshots, 0 tests, 1 models, 0 operations
​
14:39:08 | Concurrency: 3 threads (target='dev')
14:39:08 | 
14:39:08 | Done.
Serving RPC server at 0.0.0.0:8580
Supported methods: ['compile', 'run', 'ps', 'kill']
Send requests to http://localhost:8580/jsonrpc
 
  • rpc 服务请求格式
{
    "jsonrpc": "2.0",
    "method": "{ a valid rpc server command }",
    "id": "{ a unique identifier for this query }",
    "params": {
        "timeout": { timeout for the query in seconds },
        "sql": "{ base64 encoded dbt-sql query }",
        "name": "{ an identifying name for this query }"
    }
}
  • 一个rpc 服务参考例子
{
    "jsonrpc": "2.0",
    "method": "compile",
    "id": "2db9a2fe-9a39-41ef-828c-25e04dd6b07d",
    "params": {
        "timeout": 60,
        "sql": "c2VsZWN0IHt7IDEgKyAxIH19IGFzIGlk",
        "name": "my_first_query"
    }
}
 

curl 格式

curl -X GET \
  http://localhost:8580/jsonrpc \
  -H 'Accept: */*' \
  -H 'Cache-Control: no-cache' \
  -H 'Connection: keep-alive' \
  -H 'Content-Type: text/plain' \
  -H 'Host: localhost:8580' \
  -H 'accept-encoding: gzip, deflate' \
  -H 'cache-control: no-cache' \
  -H 'content-length: 229' \
  -H 'cookie: Cookie_6=value; platform=order' \
  -b 'Cookie_6=value; platform=order' \
  -d '{
    "jsonrpc": "2.0",
    "method": "compile",
    "id": "2db9a2fe-9a39-41ef-828c-25e04dd6b07d",
    "params": {
        "timeout": 60,
        "sql": "c2VsZWN0IHt7IDEgKyAxIH19IGFzIGlk",
        "name": "my_first_query"
    }
}'
 

返回数据

{
    "jsonrpc": "2.0",
    "result": {
        "timing": [
            {
                "completed_at": "2019-07-11T06:55:53.542902Z",
                "started_at": "2019-07-11T06:55:53.531315Z",
                "name": "compile"
            },
            {
                "completed_at": "2019-07-11T06:55:53.543440Z",
                "started_at": "2019-07-11T06:55:53.543151Z",
                "name": "execute"
            }
        ],
        "raw_sql": "select {{ 1 + 1 }} as id",
        "compiled_sql": "select 2 as id",
        "logs": [
            {
                "timestamp": "2019-07-11 14:55:53,428",
                "message": "Parsing rpc.my_new_package.my_first_query",
                "levelname": "DEBUG",
                "level": 10
            },
            {
                "timestamp": "2019-07-11 14:55:53,432",
                "message": "Acquiring new postgres connection \"my_first_query\".",
                "levelname": "DEBUG",
                "level": 10
            },
            {
                "timestamp": "2019-07-11 14:55:53,433",
                "message": "Opening a new connection, currently in state init",
                "levelname": "DEBUG",
                "level": 10
            },
            {
                "timestamp": "2019-07-11 14:55:53,522",
                "message": "Found 116 macros, 0 analyses, 0 sources, 1 None, 0 seed files, 0 snapshots, 0 tests, 1 models, 0 operations",
                "levelname": "NOTICE",
                "level": 15
            },
            {
                "timestamp": "2019-07-11 14:55:53,523",
                "message": "Acquiring new postgres connection \"my_first_query\".",
                "levelname": "DEBUG",
                "level": 10
            },
            {
                "timestamp": "2019-07-11 14:55:53,524",
                "message": "Opening a new connection, currently in state init",
                "levelname": "DEBUG",
                "level": 10
            },
            {
                "timestamp": "2019-07-11 14:55:53,531",
                "message": "Compiling rpc.my_new_package.my_first_query",
                "levelname": "DEBUG",
                "level": 10
            }
        ]
    },
    "id": "2db9a2fe-9a39-41ef-828c-25e04dd6b07d"
}
ls 命令

dbt ls

效果：

dbt ls
my_new_package.example.my_first_dbt_model
说明

dbt 0.14.0 的好多新特性还是很不错的

参考资料

https://docs.getdbt.com/docs/rpc
https://docs.getdbt.com/v0.14/docs/run-operation
https://docs.getdbt.com/docs/list
https://github.com/rongfengliang/dbt-0.14.0-learning