MySQL Data Connector

MySQL is an open-source relational database management system that uses structured query language (SQL) for managing and manipulating databases.

The MySQL Data Connector enables federated/accelerated SQL queries on data stored in MySQL databases.

datasets:
  - from: mysql:mytable
    name: my_dataset
    params:
      mysql_host: localhost
      mysql_tcp_port: 3306
      mysql_db: my_database
      mysql_user: my_user
      mysql_pass: ${secrets:mysql_pass}
      mysql_pool_min: 10
      mysql_pool_max: 100

Configuration

from

The from field takes the form mysql:database_name.table_name where database_name is the fully-qualified table name in the SQL server.

If the database_name is omitted in the from field, the connector will use the database specified in the mysql_db parameter. If the mysql_db parameter is not provided, it will default to the user's default database.

These two examples are identical:

datasets:
  - from: mysql:mytable
    name: my_dataset
    params:
      mysql_db: my_database
      ...

datasets:
  - from: mysql:my_database.mytable
    name: my_dataset
    params: ...

name

The dataset name. This will be used as the table name within Spice.

Example:

datasets:
  - from: mysql:path.to.my_dataset
    name: cool_dataset
    params: ...

SELECT COUNT(*) FROM cool_dataset;

+----------+
| count(*) |
+----------+
| 6001215  |
+----------+

params

The MySQL data connector can be configured by providing the following params. Use the secret replacement syntax to load the secret from a secret store, e.g. ${secrets:my_mysql_conn_string}.

Parameter Name	Description
mysql_connection_string	The connection string to use to connect to the MySQL server. This can be used instead of providing individual connection parameters.
mysql_host	The hostname of the MySQL server.
mysql_tcp_port	The port of the MySQL server.
mysql_db	The name of the database to connect to.
mysql_user	The MySQL username.
mysql_pass	The password to connect with.
mysql_sslmode	Optional. Specifies the SSL/TLS behavior for the connection, supported values: required: (default) This mode requires an SSL connection. If a secure connection cannot be established, server will not connect. preferred: This mode will try to establish a secure SSL connection if possible, but will connect insecurely if the server does not support SSL. disabled: This mode will not attempt to use an SSL connection, even if the server supports it.
mysql_sslrootcert	Optional parameter specifying the path to a custom PEM certificate that the connector will trust.
mysql_pool_min	The minimum number of connections to keep open in the pool, lazily created when requested. Default: 10
mysql_pool_max	The maximum number of connections to allow in the pool. Default: 100

metrics

The MySQL data connector supports the following optional component metrics:

Metric Name	Type	Description
connection_count	Gauge	Gauge of active connections to the database server
connections_in_pool	Gauge	Gauge of active connections that are idling in the pool
active_wait_requests	Gauge	Gauge of requests that are waiting for a connection to be returned to the pool
create_failed	Counter	Counter of connections that failed to be created
discarded_superfluous_connection	Counter	Counter of connections that were closed because there were already enough idle connections in the pool
discarded_unestablished_connection	Counter	Counter of connections that were closed because they could not be established
dirty_connection_return	Counter	Counter of connections that were returned to the pool but were dirty (ie. open transactions, pending queries, etc)
discarded_expired_connection	Counter	Counter of connections that were discarded because they were expired by the pool constraints (i.e. TTL expired)
resetting_connection	Counter	Counter of connections that were reset
discarded_error_during_cleanup	Counter	Counter of connections that were discarded because they returned an error during cleanup
connection_returned_to_pool	Counter	Counter of connections that were returned to the pool

These metrics are not enabled by default, enable them by setting the metrics parameter:

datasets:
  - from: mysql:mytable
    name: my_dataset
    metrics:
      - name: connection_count
      - name: connections_in_pool
      - name: active_wait_requests
      - name: create_failed
      - name: discarded_superfluous_connection
      - name: discarded_unestablished_connection
      - name: dirty_connection_return
      - name: discarded_expired_connection
      - name: resetting_connection
      - name: discarded_error_during_cleanup
      - name: connection_returned_to_pool
    params: &params
      mysql_host: localhost
      mysql_tcp_port: 3306
      mysql_user: my_user
      mysql_pass: ${secrets:mysql_pass}

Types

The table below shows the MySQL data types supported, along with the type mapping to Apache Arrow types in Spice.

MySQL Type	Arrow Type
TINYINT	Int8
SMALLINT	Int16
INT	Int32
MEDIUMINT	Int32
BIGINT	Int64
DECIMAL	Decimal128 / Decimal256
FLOAT	Float32
DOUBLE	Float64
DATETIME	Timestamp(Microsecond, None)
TIMESTAMP	Timestamp(Microsecond, None)
YEAR	Int16
TIME	Time64(Nanosecond)
DATE	Date32
CHAR	Utf8
BINARY	Binary
VARCHAR	Utf8
VARBINARY	Binary
TINYBLOB	Binary
TINYTEXT	Utf8
BLOB	Binary
TEXT	Utf8
MEDIUMBLOB	Binary
MEDIUMTEXT	Utf8
LONGBLOB	LargeBinary
LONGTEXT	LargeUtf8
SET	Utf8
ENUM	Dictionary(UInt16, Utf8)
BIT	UInt64

note

• The MySQL TIMESTAMP value is retrieved as a UTC time value.

Examples

Connecting using username and password

datasets:
  - from: mysql:path.to.my_dataset
    name: my_dataset
    params:
      mysql_host: localhost
      mysql_tcp_port: 3306
      mysql_db: my_database
      mysql_user: my_user
      mysql_pass: ${secrets:mysql_pass}

Connecting using SSL

datasets:
  - from: mysql:path.to.my_dataset
    name: my_dataset
    params:
      mysql_host: localhost
      mysql_tcp_port: 3306
      mysql_db: my_database
      mysql_user: my_user
      mysql_pass: ${secrets:mysql_pass}
      mysql_sslmode: preferred
      mysql_sslrootcert: ./custom_cert.pem

Connecting using a Connection String

datasets:
  - from: mysql:path.to.my_dataset
    name: my_dataset
    params:
      mysql_connection_string: mysql://${secrets:my_user}:${secrets:my_password}@localhost:3306/my_db

Connecting to the default database

datasets:
  - from: mysql:mytable
    name: my_dataset
    params:
      mysql_host: localhost
      mysql_tcp_port: 3306
      mysql_user: my_user
      mysql_pass: ${secrets:mysql_pass}

With custom connection pool settings

datasets:
  - from: mysql:path.to.my_dataset
    name: my_dataset
    params:
      mysql_host: localhost
      mysql_tcp_port: 3306
      mysql_db: my_database
      mysql_user: my_user
      mysql_pass: ${secrets:mysql_pass}
      mysql_pool_min: 5
      mysql_pool_max: 10

Secrets

Spice integrates with multiple secret stores to help manage sensitive data securely. For detailed information on supported secret stores, refer to the secret stores documentation. Additionally, learn how to use referenced secrets in component parameters by visiting the using referenced secrets guide.

Cookbook

• A cookbook recipe to configure MySQL as a data connector in Spice. MySQL Data Connector
• A cookbook recipe to configure AWS RDS Aurora (MySQL Compatible) as a data connector in Spice. AWS RDS Aurora (MySQL Data Connector)
• A cookbook recipe to configure Planetscale as a data connector in Spice. Planetscale (MySQL Data Connector)