MySQL setup
Community plugin
Some core functionality may be limited. If you're interested in contributing, check out the source code for each repository listed below.
Overview of dbt-mysql
- Maintained by: Community
- Authors: Doug Beatty (https://github.com/dbeatty10)
- GitHub repo: dbeatty10/dbt-mysql
- PyPI package:
dbt-mysql
- Slack channel: #db-mysql-family
- Supported dbt Core version: v0.18.0 and newer
- dbt Cloud support: Not Supported
- Minimum data platform version: MySQL 5.7 and 8.0
Installing dbt-mysql
pip is the easiest way to install the adapter:
pip install dbt-mysql
Installing dbt-mysql
will also install dbt-core
and any other dependencies.
Configuring dbt-mysql
For MySQL-specifc configuration please refer to MySQL Configuration
For further info, refer to the GitHub repository: dbeatty10/dbt-mysql
This is an experimental plugin:
- It has not been tested extensively.
- Storage engines other than the default of InnoDB are untested.
- Only tested with dbt-adapter-tests with the following versions:
- MySQL 5.7
- MySQL 8.0
- MariaDB 10.5
- Compatibility with other dbt packages (like dbt_utils) are also untested.
Please read these docs carefully and use at your own risk. Issues and PRs welcome!
Connecting to MySQL with dbt-mysql
MySQL targets should be set up using the following configuration in your profiles.yml
file.
Example:
~/.dbt/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
Description of MySQL Profile Fields
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 |
Supported features
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
- Ephemeral materializations rely upon Common Table Expressions (CTEs), which are not supported until MySQL 8.0.
- MySQL 5.7 has some configuration gotchas that might affect dbt snapshots to not work properly due to automatic initialization and updating for
TIMESTAMP
.- If the output of
SHOW VARIABLES LIKE 'sql_mode'
includesNO_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 theSHOW VARIABLES LIKE 'sql_mode'
output.
- If the output of
0