Skip to main content

The MySQL adapter plugin for dbt

Project description

dbt-mysql

Tests and Code Checks Integration Tests Badge

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

This plugin can be installed via pip:

$ python -m pip install dbt-mysql

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

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 tests/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.

Project details


Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Source Distribution

dbt-mysql-1.7.0.tar.gz (34.4 kB view details)

Uploaded Source

Built Distribution

dbt_mysql-1.7.0-py3-none-any.whl (54.4 kB view details)

Uploaded Python 3

File details

Details for the file dbt-mysql-1.7.0.tar.gz.

File metadata

  • Download URL: dbt-mysql-1.7.0.tar.gz
  • Upload date:
  • Size: 34.4 kB
  • Tags: Source
  • Uploaded using Trusted Publishing? No
  • Uploaded via: twine/4.0.2 CPython/3.11.8

File hashes

Hashes for dbt-mysql-1.7.0.tar.gz
Algorithm Hash digest
SHA256 8f82ee562b081d3d887a844f00fc2666328567f4c8be3f0be13a3aebefa646d2
MD5 d3ee2b72ead5702078c7e11bd78d5be9
BLAKE2b-256 baa04b6f88937eb894c8747750e160fcbfb59f6e25a1563fdbddd25c6a345ca6

See more details on using hashes here.

File details

Details for the file dbt_mysql-1.7.0-py3-none-any.whl.

File metadata

  • Download URL: dbt_mysql-1.7.0-py3-none-any.whl
  • Upload date:
  • Size: 54.4 kB
  • Tags: Python 3
  • Uploaded using Trusted Publishing? No
  • Uploaded via: twine/4.0.2 CPython/3.11.8

File hashes

Hashes for dbt_mysql-1.7.0-py3-none-any.whl
Algorithm Hash digest
SHA256 f63d7ed7dc6eafdd775609076d2e6021882a42cacf0304db3db843c4ca8a47a4
MD5 78b120fed94e032788687fe7126bbe50
BLAKE2b-256 7288bf350432441870bcadd5dad38a81feb210794055f756c733706c63c477ee

See more details on using hashes here.

Supported by

AWS Cloud computing and Security Sponsor Datadog Monitoring Fastly CDN Google Download Analytics Pingdom Monitoring Sentry Error logging StatusPage Status page