MySQL Data Connector

MySQL Data Connector Documentation

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 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 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) - requires an SSL connection, `preferred` - tries SSL but connects insecurely if not supported, `disabled` - does not use SSL.
`mysql_sslrootcert`	Optional parameter specifying the path to a custom PEM certificate that the connector will trust.
`mysql_time_zone`	Optional. 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` (MySQL server host's OS time zone), or `local_system` (local runtime OS time zone).
`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:
      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`

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