Connector Details

NameValue
PlatformMySQL
Auth TypeAPI Keys
DirectionBidirectional
Tap Repohttps://github.com/transferwise/pipelinewise-tap-mysql
Target Repohttps://github.com/hotgluexyz/target-mysql-v2
Tap Metrics

Usage:

Target Metrics

Usage:

Target MySQL

Config

target-mysql requires the standard 5 connection parameters to be specified in the config:

{
    "host": "https://...",
    "port": "3306",
    "user": "...",
    "password": "...",
    "database": "...",
}

Example ETL Script

import gluestick as gs
import os

# Define standard Hotglue directories
ROOT_DIR = os.environ.get("ROOT_DIR", ".")
INPUT_DIR = f"{ROOT_DIR}/sync-output"
OUTPUT_DIR = f"{ROOT_DIR}/etl-output"

# Read sync output
input = gs.Reader()

# Get tenant id
tenant_id = os.environ.get('USER_ID', os.environ.get('TENANT', 'default'))

# Iterate through the different streams in the sync output
for key in eval(str(input)):
    input_df = input.get(key)

    """
    Here we get the metadata for extracting the key properties, such as primary and replication keys.
    The database export targets will utilize these primary keys when upserting data.
    If you wish to hardcode your choice of primary keys, you can do so here.
    """
    metadata = input.get_metadata(key)
    if metadata.get("key_properties"):
        # Use the key_properties (e.g. primary and replication keys) from the catalog
        key_properties = eval(metadata["key_properties"])
    else:
        key_properties = []

    # Include tenant_id as a field if desired
    input_df["tenant"] = tenant_id

    # Write this stream to the OUTPUT directory with the specified key_properties
    gs.to_singer(input_df, key, OUTPUT_DIR, keys=key_properties)

Optional config flags

PropertyDescriptionDefault
table_name_patternMySQL table name pattern to use when creating tables”${TABLE_NAME}“
lower_case_table_namesUse lowercase for table names or nottrue
allow_column_alterAllow column alterations or notfalse
replace_nullReplace null values with others or notfalse
table_configDictionary with specifications on insert/upsert/truncate

The replace_null Option (Experimental)

By enabling the replace_null option, null values are replaced with ‘empty’ equivalents based on their data type. Use with caution as it may alter data semantics.

When replace_null is true, null values are replaced as follows:

JSON Schema Data TypeNull Value Replacement
stringEmpty string("")
number0
objectEmpty object({})
arrayEmpty array([])
booleanfalse
nullnull

The table_config option

By default, target-mysql will upsert your payload into each table. This means it will insert all new data into your table, but perform an update on primary key collision.

The table_config allows you to override this behavior with one of two insertion methods:

  • truncate: Delete the existing table and replace it with the new data
  • insert: Attempt to insert all records as new rows, throwing an error on primary key collision

If you want to use the truncate or insert methods, you can specify which tables should use the method using the table_config flag:

{
    "host": "..."
    ....
    "table_config": {
        "accounts": "truncate",
        "vendors": "insert",
    }
}

Tap Changelog