MySQL Metadata Store

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

The MySQL extension requires the MySQL Connector/J library or MariaDB Connector/J library, neither of which are included in the Druid distribution. Refer to the following section for instructions on how to install this library.

Installing the MySQL connector library

This extension can use Oracle’s MySQL JDBC driver which is not included in the Druid distribution. You must install it separately. There are a few ways to obtain this library:

• It can be downloaded from the MySQL site at: https://dev.mysql.com/downloads/connector/j/
• It can be fetched from Maven Central at: https://repo1.maven.org/maven2/mysql/mysql-connector-java/5.1.48/mysql-connector-java-5.1.48.jar
• It may be available through your package manager, e.g. as libmysql-java on APT for a Debian-based OS

This fetches the MySQL connector JAR file with a name like mysql-connector-java-5.1.48.jar.

Copy or symlink this file inside the folder extensions/mysql-metadata-storage under the distribution root directory.

Alternative: Installing the MariaDB connector library

This extension also supports using the MariaDB connector jar, though it is also not included in the Druid distribution, so you must install it separately.

• Download from the MariaDB site: https://mariadb.com/downloads/connector
• Download from Maven Central: https://repo1.maven.org/maven2/org/mariadb/jdbc/mariadb-java-client/2.7.3/mariadb-java-client-2.7.3.jar

This fetches the MariaDB connector JAR file with a name like maria-java-client-2.7.3.jar.

Copy or symlink this file to extensions/mysql-metadata-storage under the distribution root directory.

To configure the mysql-metadata-storage extension to use the MariaDB connector library instead of MySQL, set druid.metadata.mysql.driver.driverClassName=org.mariadb.jdbc.Driver.

Depending on the MariaDB client library version, the connector supports both jdbc:mysql: and jdbc:mariadb: connection URIs. However, the parameters to configure the connection vary between implementations, so be sure to check the documentation for details.

Setting up MySQL

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/.

This extension also supports using MariaDB server, https://mariadb.org/download/, substituting for MariaDB in the following instructions where appropriate.

1. 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 'diurd';
-- grant the user all the permissions on the database we just created
GRANT ALL PRIVILEGES ON druid.* TO 'druid'@'localhost';

1. 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

If using the MariaDB connector library, set druid.metadata.mysql.driver.driverClassName=org.mariadb.jdbc.Driver.

Encrypting 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.

Configuration

MySQL InputSource and Firehose

The MySQL extension provides a connector implementation of an SqlInputSource and SqlFirehose which can be used to ingest data into Druid from a MySQL database. This works with either MySQL or MariaDB connector jars.

{
  "type": "index_parallel",
  "spec": {
    "dataSchema": {
      "dataSource": "some_datasource",
      "parser": {
        "parseSpec": {
          "format": "timeAndDims",
          "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",
      "firehose": {
        "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"
        ]
      }
    },
    "tuningConfig": {
      "type": "index_parallel"
    }
  }
}