Skip to content

Latest commit

 

History

History
154 lines (116 loc) · 7.87 KB

README.md

File metadata and controls

154 lines (116 loc) · 7.87 KB
Raw

dbt-doris

This plugin ports dbt functionality to Apache Doris, based on dbt-mysql.

Still under development.

This is an experimental plugin:

  • We have not tested it extensively
  • Storage engines other than the default of InnoDB are untested
  • MariaDB compatibility is untested
  • Only tested with dbt-adapter-tests with MySQL 5.6, 5.7, and 8.0
  • Compatiblity with other dbt packages (like dbt_utils) is also untested

Please read these docs carefully and use at your own risk. Issues and PRs welcome!

Table of Contents

Installation

This plugin can be installed via pip:

$ pip install dbt-mysql

Supported features

5.6 / 5.7 8.0 Feature
✅ ✅ Table materialization
✅ ✅ View materialization
✅ ✅ Incremental materialization
❌ ✅ Ephemeral materialization
✅ ✅ Seeds
✅ ✅ Sources
✅ ✅ Custom data tests
✅ ✅ Docs generate
🤷 ✅ Snapshots

Notes:

  • Ephemeral materializations rely upon Common Table Expressions (CTEs), which are not supported until MySQL 8.0
  • MySQL 5.6 and 5.7 have some configuration gotchas that affect snapshots (see below).
MySQL 5.6 configuration gotchas

dbt snapshots might not work properly due to automatic initialization and updating for TIMESTAMP if:

  • the output of SHOW VARIABLES LIKE 'sql_mode' includes NO_ZERO_DATE
  • the output of SHOW GLOBAL VARIABLES LIKE 'explicit_defaults_for_timestamp' has a value of OFF

A solution is to include the following in a *.cnf file: Configuration to include in a *.cnf file:

[mysqld]
explicit_defaults_for_timestamp = true
MySQL 5.7 configuration gotchas

dbt snapshots might not work properly due to automatic initialization and updating for TIMESTAMP if: dbt snapshots might not work properly if:

  • the output of SHOW VARIABLES LIKE 'sql_mode' includes NO_ZERO_DATE

A solution is to include the following in a *.cnf file: Configuration to include in a *.cnf file:

[mysqld]
explicit_defaults_for_timestamp = true
sql_mode = "ALLOW_INVALID_DATES,{other_sql_modes}"

where {other_sql_modes} is the rest of the modes from the SHOW VARIABLES LIKE 'sql_mode' output.

Configuring your profile

A dbt profile can be configured to run against MySQL using the following configuration example:

Use type: mysql for MySQL 8.x and type: mysql5 for MySQL 5.x

Example entry for profiles.yml:

your_profile_name:
  target: dev
  outputs:
    dev:
      type: mysql
      server: localhost
      port: 3306
      schema: analytics
      username: your_mysql_username
      password: your_mysql_password
Option Description Required? Example
type The specific adapter to use Required mysql or mysql5
server The server (hostname) to connect to Required yourorg.mysqlhost.com
port The port to use Optional 3306
schema Specify the schema (database) to build models into Required analytics
username The username to use to connect to the server Required dbt_admin
password The password to use for authenticating to the server Required correct-horse-battery-staple

Notes

Conflicting terminology is used between:

  • dbt
  • Database management systems (DBMS) like MySQL, Postgres, and Snowflake
  • metadata in the ANSI-standard information_schema

The conflicts include both:

  • the same word meaning different things
  • different words meaning the same thing

For example, a "database" in MySQL is not the same as a "database" in dbt, but it is equivalent to a "schema" in Postgres 🤯.

dbt-mysql uses the dbt terms. The native MySQL verbiage is restricted to SQL statements.

This cross-walk aligns the terminology:

information_schema dbt (and Postgres) MySQL
catalog database undefined / not implemented
schema schema database
relation (table/view) relation (table/view) relation (table/view)
column column column

Additionally, many DBMS have relation names with three parts whereas MySQL has only two. E.g., a fully-qualified table name in Postgres is database.schema.table versus database.table in MySQL. The missing part in MySQL is the information_schema "catalog".

DBMS Fully-qualified relation name Parts
Postgres database.schema.table 3
MySQL database.table 2

Running Tests

See test/README.md for details on running the integration tests.

Reporting bugs and contributing code

Credits

dbt-mysql borrows from dbt-spark and dbt-sqlite since Spark and SQLite also use two-part relation names.