Skip to main content

MySQL Profile

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
Author: Doug Beatty
Source: GitHub
Core version: v1.0.8
dbt Cloud: Not Supported

dbt-mysql stars latest version on PyPI

The package can be installed from PyPI with:

pip install 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​

OptionDescriptionRequired?Example
typeThe specific adapter to useRequiredmysql, mysql5 or mariadb
serverThe server (hostname) to connect toRequiredyourorg.mysqlhost.com
portThe port to useOptional3306
schemaSpecify the schema (database) to build models intoRequiredanalytics
usernameThe username to use to connect to the serverRequireddbt_admin
passwordThe password to use for authenticating to the serverRequiredcorrect-horse-battery-staple
ssl_disabledSet to enable or disable TLS connectivity to mysql5.xOptionalTrue or False

Supported features​

MariaDB 10.5MySQL 5.7MySQL 8.0Feature
βœ…βœ…βœ…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' 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.