MySQL metadata store

To use this Apache Druid extension, include mysql-metadata-storage in the extensions load list.

info

The MySQL extension requires the MySQL Connector/J library, which is not included in the Druid distribution. Refer to the following section for instructions on how to install this library.

The extension requires a connector library that's not included with Druid. See the Prerequisites for installation instructions.

Prerequisites

To use the MySQL extension, you need to install one of the following libraries:

• MySQL Connector/J

Install MySQL Connector/J

The MySQL extension uses Oracle's MySQL JDBC driver. The current version of Druid uses version 8.2.0. Other versions may not work with this extension.

Set up MySQL

To avoid issues with upgrades that require schema changes to a large metadata table, consider a MySQL version that supports instant ADD COLUMN semantics. For example, MySQL 8.

1. Install MySQL

   Use your favorite package manager to install mysql, e.g.:

• on Ubuntu/Debian using apt apt-get install mysql-server

• on OS X, using Homebrew brew install mysql

  Alternatively, download and follow installation instructions for MySQL Community Server here: http://dev.mysql.com/downloads/mysql/.

2. Create a druid database and user

   Connect to MySQL from the machine where it is installed.

   mysql -u root

   Paste the following snippet into the mysql prompt:

   -- create a druid database, make sure to use utf8mb4 as encoding
   CREATE DATABASE druid DEFAULT CHARACTER SET utf8mb4;
   
   -- create a druid user
   CREATE USER 'druid'@'localhost' IDENTIFIED BY 'password';
   
   -- grant the user all the permissions on the database we just created
   GRANT ALL PRIVILEGES ON druid.* TO 'druid'@'localhost';

3. Configure your Druid metadata storage extension:

   Add the following parameters to your Druid configuration, replacing <host> with the location (host name and port) of the database.

   druid.extensions.loadList=["mysql-metadata-storage"]
   druid.metadata.storage.type=mysql
   druid.metadata.storage.connector.connectURI=jdbc:mysql://<host>/druid
   druid.metadata.storage.connector.user=druid
   druid.metadata.storage.connector.password=diurd

Encrypt MySQL connections

This extension provides support for encrypting MySQL connections. To get more information about encrypting MySQL connections using TLS/SSL in general, please refer to this guide.

This extension provides support for encrypting MySQL connections. To get more information about encrypting MySQL connections using TLS/SSL in general, please refer to this guide.

Configuration properties

Property	Description	Default	Required
druid.metadata.mysql.ssl.useSSL	Enable SSL	false	no
druid.metadata.mysql.ssl.clientCertificateKeyStoreUrl	The file path URL to the client certificate key store.	none	no
druid.metadata.mysql.ssl.clientCertificateKeyStoreType	The type of the key store where the client certificate is stored.	none	no
druid.metadata.mysql.ssl.clientCertificateKeyStorePassword	The Password Provider or String password for the client key store.	none	no
druid.metadata.mysql.ssl.verifyServerCertificate	Enables server certificate verification.	false	no
druid.metadata.mysql.ssl.trustCertificateKeyStoreUrl	The file path to the trusted root certificate key store.	Default trust store provided by MySQL	yes if verifyServerCertificate is set to true and a custom trust store is used
druid.metadata.mysql.ssl.trustCertificateKeyStoreType	The type of the key store where trusted root certificates are stored.	JKS	yes if verifyServerCertificate is set to true and keystore type is not JKS
druid.metadata.mysql.ssl.trustCertificateKeyStorePassword	The Password Provider or String password for the trust store.	none	yes if verifyServerCertificate is set to true and password is not null
druid.metadata.mysql.ssl.enabledSSLCipherSuites	Overrides the existing cipher suites with these cipher suites.	none	no
druid.metadata.mysql.ssl.enabledTLSProtocols	Overrides the TLS protocols with these protocols.	none	no

MySQL input source

The MySQL extension provides an implementation of an SQL input source to ingest data into Druid from a MySQL database. For more information on the input source parameters, see SQL input source.

{
  "type": "index_parallel",
  "spec": {
    "dataSchema": {
      "dataSource": "some_datasource",
      "dimensionsSpec": {
        "dimensionExclusions": [],
        "dimensions": [
          "dim1",
          "dim2",
          "dim3"
        ]
      },
      "timestampSpec": {
        "format": "auto",
        "column": "ts"
      },
      "metricsSpec": [],
      "granularitySpec": {
        "type": "uniform",
        "segmentGranularity": "DAY",
        "queryGranularity": {
          "type": "none"
        },
        "rollup": false,
        "intervals": null
      },
      "transformSpec": {
        "filter": null,
        "transforms": []
      }
    },
    "ioConfig": {
      "type": "index_parallel",
      "inputSource": {
        "type": "sql",
        "database": {
          "type": "mysql",
          "connectorConfig": {
            "connectURI": "jdbc:mysql://some-rds-host.us-west-1.rds.amazonaws.com:3306/druid",
            "user": "admin",
            "password": "secret"
          }
        },
        "sqls": [
          "SELECT * FROM some_table"
        ]
      },
      "inputFormat": {
        "type": "json"
      }
    },
    "tuningConfig": {
      "type": "index_parallel"
    }
  }
}