Skip to main content
Version: v1.10

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

The dataset name cannot be a [reserved keyword(../../reference/spicepod/keywords.md) or any of the following keywords that are reserved by MySQL:

  • PARTITION

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 NameDescription
mysql_connection_stringThe connection string to use to connect to the MySQL server. This can be used instead of providing individual connection parameters.
mysql_hostThe hostname of the MySQL server.
mysql_tcp_portThe port of the MySQL server.
mysql_dbThe name of the database to connect to.
mysql_userThe MySQL username.
mysql_passThe password to connect with.
mysql_sslmodeOptional. 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_sslrootcertOptional parameter specifying the path to a custom PEM certificate that the connector will trust.
mysql_time_zoneOptional. Specifies connection time zone. Default is UTC. Accepts:
  • Fixed offsets (e.g., +02:00).
  • IANA time zone names (e.g., America/Los_Angeles), if supported by the MySQL server.
  • system: The MySQL server host’s OS time zone.
  • local_system: The local runtime OS time zone.
mysql_pool_minThe minimum number of connections to keep open in the pool, lazily created when requested. Default: 10
mysql_pool_maxThe maximum number of connections to allow in the pool. Default: 100

metrics​

The MySQL data connector supports the following optional [component metrics(../../features/observability/component_metrics):

Metric NameTypeDescription
connection_countGaugeGauge of active connections to the database server
connections_in_poolGaugeGauge of active connections that are idling in the pool
active_wait_requestsGaugeGauge of requests that are waiting for a connection to be returned to the pool
create_failedCounterCounter of connections that failed to be created
discarded_superfluous_connectionCounterCounter of connections that were closed because there were already enough idle connections in the pool
discarded_unestablished_connectionCounterCounter of connections that were closed because they could not be established
dirty_connection_returnCounterCounter of connections that were returned to the pool but were dirty (ie. open transactions, pending queries, etc)
discarded_expired_connectionCounterCounter of connections that were discarded because they were expired by the pool constraints (i.e. TTL expired)
resetting_connectionCounterCounter of connections that were reset
discarded_error_during_cleanupCounterCounter of connections that were discarded because they returned an error during cleanup
connection_returned_to_poolCounterCounter 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 TypeArrow Type
TINYINTInt8
SMALLINTInt16
INTInt32
MEDIUMINTInt32
BIGINTInt64
DECIMALDecimal128 / Decimal256
FLOATFloat32
DOUBLEFloat64
DATETIMETimestamp(Microsecond, None)
TIMESTAMPTimestamp(Microsecond, None)
YEARInt16
TIMETime64(Nanosecond)
DATEDate32
CHARUtf8
BINARYBinary
VARCHARUtf8
VARBINARYBinary
TINYBLOBBinary
TINYTEXTUtf8
BLOBBinary
TEXTUtf8
MEDIUMBLOBBinary
MEDIUMTEXTUtf8
LONGBLOBLargeBinary
LONGTEXTLargeUtf8
SETUtf8
ENUMDictionary(UInt16, Utf8)
BITUInt64
note
  • The MySQL TIMESTAMP value is retrieved as a UTC time value by default. Use the mysql_time_zone configuration parameter to specify the desired time zone for interpreting TIMESTAMP values during data retrieval.

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(../secret-stores). Additionally, learn how to use referenced secrets in component parameters by visiting the [using referenced secrets guide(../secret-stores#using-secrets).

Cookbook​