MySQL
The mysql connector reads MySQL and MariaDB databases with read-only SELECT queries and table reads, and
writes tables from DuckDB and Spark job outputs. It works like the postgres
connector; this page lists what differs.
Install
sw connectors -i mysql
Connect
Use the connection keys or one DSN secret, not both.
kind: source
name: mysql_demo
spec:
config:
mysql_host: { type: string, default: localhost }
start_date: { type: date, default: "-7d" }
secrets:
mysql_password: { type: string }
auth:
provider: mysql
host: "{{ config.mysql_host }}"
database: app_db
user: reader
password: "{{ secrets.mysql_password }}"
statement_timeout: 5min
| Key | What it is | Required / default |
|---|---|---|
host | A host name or an IP address | Required (with keys) |
port | The port | 3306 |
database | The database. Also the schema of a table named without one (a source also accepts dbname) | Required (with keys) |
user | The user. Use one that can only read | Required (with keys) |
password | The user's password. Secret | Optional |
sslmode | The TLS mode (see TLS) | preferred |
options | More connection parameters: socket and the ssl_* files (ssl_ca, ssl_cert, ssl_key, ...) | Optional |
statement_timeout | On streamwright run, stops a statement that runs longer: seconds, or 500ms, 30s, 5min | Optional |
dsn | mysql://user:password@host:port/database, or key=value pairs, instead of the keys above. Secret | Optional |
TLS
sslmode | What it does |
|---|---|
disabled | No TLS |
preferred | The default. Uses TLS when the server offers it; ssl_* files in options make it required |
required | Encrypts, without checking the server certificate |
verify_ca | Also checks the server certificate against ssl_ca |
verify_identity | Also checks the server's host name |
The same sslmode and ssl_* files apply to every connection the connector makes, including job state stores and
Spark merges and deletes.
Read data
A request uses service: database:
| Method | Arguments | What it does |
|---|---|---|
query | query, params | Runs one read-only SELECT. $name placeholders take their values from params |
table | table, schema (default: the connection's database), columns, where | Reads a table: some or all columns, and the rows that match every where condition |
incremental:
cursor_field: created_at
start: "{{ config.start_date }}"
window: 1d
requests:
- name: raw_users
operation:
service: database
method: query
arguments:
query: |
SELECT id, username, email, created_at
FROM users
WHERE created_at >= $since
params: { since: "{{ window.start }}" }
transform:
mode: page
steps:
- name: users
select: |
SELECT
(record->>'id')::BIGINT AS user_id,
record->>'username' AS username,
record->>'email' AS email,
(record->>'created_at')::TIMESTAMPTZ AS created_at
FROM raw_users
export:
users:
step: users
primary_key: [user_id]
$since is a bound parameter: its value comes from params and is never pasted into the query text.
In jobs, a DuckDB job uses a read step (read: query with query, or read: table with table and
schema), and a Spark job the same operation: as a source stream. Engines take the same arguments as on
PostgreSQL. On Spark, $name values become escaped literals (backslashes
doubled, as MySQL reads them as escapes).
Write data
DuckDB and Spark job outputs write a step's rows into a table:
outputs:
- name: save_users # DuckDB job
connection: app_db
step: users
table: users_copy
mode: overwrite
| Mode | What it does | Engines |
|---|---|---|
append | Adds the rows | DuckDB, Spark |
overwrite | Replaces the table | DuckDB |
merge | Updates the rows that match on primary_key and inserts the rest (INSERT ... ON DUPLICATE KEY UPDATE) | Spark |
delete | Deletes the rows that match on primary_key | Spark |
DuckDB's MySQL extension cannot merge, so use a Spark job to merge or delete. On Spark, merge and delete load a
staging table (CREATE TABLE ... LIKE the target, or the output's staging_table) and run one statement.
Good to know
- Read-only queries. A query is checked before it runs: one statement, no writes, and no system schemas
(
mysql,performance_schema,sys,information_schema) or files. The database is attached read-only. - Connection options. On
streamwright runand in DuckDB jobs,optionstakes onlysocketand thessl_*files. Any other parameter is refused by name, never silently dropped. - Job state and Spark. A DuckDB job can keep its state in MySQL; install
pip install "streamwright-mysql[state]". For Spark jobs, installpip install "streamwright-mysql[spark]". - Vendor arguments (Spark). A connection key or
database.*argument the connector does not declare becomes a JDBC driver property, unchanged (for examplefetchsize,connectTimeout,sessionInitStatement).
See also
- PostgreSQL: the same model, with merges in DuckDB jobs.
- Starter template:
examples/sources/readers/mysql_demo.