dbt-mysql

This plugin ports dbt functionality to MySQL and MariaDB.

This is an experimental plugin:

• We have not tested it extensively
• Storage engines other than the default of InnoDB are untested
• Only tested with dbt-tests-adapter with the following:
  • MySQL 5.7
  • MySQL 8.0
  • MariaDB 10.5
• Compatiblity with other dbt packages (like dbt_utils) is also untested
• Supported Python Versions are: 3.6, 3.7, 3.8 & 3.9

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

Table of Contents

• Installation
• Supported features
  • MySQL 5.7 configuration gotchas
• Configuring your profile
• Notes
• Running Tests
• Reporting bugs and contributing code

Installation

This plugin can be installed via pip:

$ python -m pip install dbt-mysql

Supported features

Notes:

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

MySQL 5.7 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

A solution is to include the following 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 configuration example below.

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

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
      ssl_disabled: True
      charset: utf8mb4
      collation: utf8mb4_0900_ai_ci

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:

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".

Running Tests

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

Reporting bugs and contributing code

• Want to report a bug or request a feature? See the contributing guidelines, or open an issue.

Credits

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