Socket
Socket
Sign inDemoInstall

dbt-mysql

Package Overview
Dependencies
2
Maintainers
2
Alerts
File Explorer

Install Socket

Protect your apps from supply chain attacks

Install

dbt-mysql

The MySQL adapter plugin for dbt

    1.1.0

Maintainers
2

Readme

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

This plugin can be installed via pip:

$ python -m pip install dbt-mysql

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

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_schemadbt (and Postgres)MySQL
catalogdatabaseundefined / not implemented
schemaschemadatabase
relation (table/view)relation (table/view)relation (table/view)
columncolumncolumn

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

DBMSFully-qualified relation nameParts
Postgresdatabase.schema.table3
MySQLdatabase.table2

Running Tests

See test/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.

FAQs


Did you know?

Socket installs a GitHub app to automatically flag issues on every pull request and report the health of your dependencies. Find out what is inside your node modules and prevent malicious activity before you update the dependencies.

Install

Related posts

SocketSocket SOC 2 Logo

Product

  • Package Alerts
  • Integrations
  • Docs
  • Pricing
  • FAQ
  • Roadmap

Stay in touch

Get open source security insights delivered straight into your inbox.


  • Terms
  • Privacy
  • Security

Made with ⚡️ by Socket Inc