This plugin ports dbt functionality to MySQL and MariaDB.
This is an experimental plugin:
Please read these docs carefully and use at your own risk. Issues and PRs welcome!
This plugin can be installed via pip:
$ python -m pip install dbt-mysql
MariaDB 10.5 | MySQL 5.7 | MySQL 8.0 | Feature |
---|---|---|---|
✅ | ✅ | ✅ | Table materialization |
✅ | ✅ | ✅ | View materialization |
✅ | ✅ | ✅ | Incremental materialization |
✅ | ❌ | ✅ | Ephemeral materialization |
✅ | ✅ | ✅ | Seeds |
✅ | ✅ | ✅ | Sources |
✅ | ✅ | ✅ | Custom data tests |
✅ | ✅ | ✅ | Docs generate |
🤷 | 🤷 | ✅ | Snapshots |
Notes:
dbt snapshots might not work properly due to automatic initialization and updating for TIMESTAMP
if:
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.
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
Option | Description | Required? | Example |
---|---|---|---|
type | The specific adapter to use | Required | mysql , mysql5 or mariadb |
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 |
ssl_disabled | Set to enable or disable TLS connectivity to mysql5.x | Optional | True or False |
charset | Specify charset to be used by a connection | Optional | utf8mb4 |
collation | Set to enable or disable TLS connectivity to mysql5.x | Optional | utf8mb4_0900_ai_ci |
Conflicting terminology is used between:
information_schema
The conflicts include both:
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 |
See tests/README.md for details on running the integration tests.
dbt-mysql borrows from dbt-spark and dbt-sqlite since Spark and SQLite also use two-part relation names.