Write data to AnalyticDB for MySQL 2.0 using Logstash

Background information

Logstash is an open source, server-side data processing pipeline. It was originally used to write log data to Elasticsearch (ES). Logstash can now ingest data from multiple data sources, transform the data, and then send the data to a desired storage destination.

You can use the open source logstash-output-jdbc plugin to import log data into AnalyticDB for MySQL for further analysis because AnalyticDB for MySQL supports native access through JDBC. However, tests show that when the log volume is large, writing data to AnalyticDB for MySQL using JDBC has low performance and consumes a large amount of CPU resources. This is because JDBC writes data one record at a time. To solve this issue, the logstash-output-analyticdb plugin provides an optimized solution. This plugin is based on JDBC and is designed to write log data to AnalyticDB for MySQL using an aggregation method.

When you use logstash-output-analyticdb to write data to AnalyticDB for MySQL, the performance is five times higher than when you use logstash-output-jdbc. The CPU consumption is also significantly lower. This topic describes the procedure in detail.

Installation

For information about how to install Logstash, see Installing Logstash. This section describes how to install the logstash-output-analyticdb plugin.

1. Navigate to the Logstash root directory: cd logstash.

2. Install the logstash-output-analyticdb plugin: bin/logstash-plugin install logstash-output-analyticdb.

3. In the Logstash directory, create the vendor/jar/jdbc directory: mkdir -p vendor/jar/jdbc.

4. Copy the JDBC JAR file to the vendor/jar/jdbc directory:

   cd vendor/jar/jdbc

   wget https://repo1.maven.org/maven2/mysql/mysql-connector-java/5.1.16/mysql-connector-java-5.1.16.jar

This completes the installation of the logstash-output-analyticdb plugin.

Usage

Create a configuration file named logstash-analyticdb.conf in the config directory. You can use a different name for the file. The content of the .conf file is as follows:

input
{
    stdin { }
}
output {
    analyticdb {
        driver_class => "com.mysql.jdbc.Driver"
        connection_string => "jdbc:mysql://HOSTNAME/DATABASE?user=USER&password=PASSWORD"
        statement => [ "INSERT INTO log (host, timestamp, message) VALUES(?, ?, ?)", "host", "@timestamp", "message" ]
    }
}

• connection_string: The JDBC URL used to connect to AnalyticDB for MySQL.

• statement: An array that contains the INSERT SQL statement.

The preceding configuration is an example. You can configure the file as needed. For more information about other configuration items related to AnalyticDB for MySQL, see the README file. For more information about the original Logstash configuration items and rules, see the Logstash documentation.

This completes the configuration. The next step is to start the task.

Start the task

In the Logstash installation directory, run bin/logstash -f config/logstash-analyticdb.conf to start the task.

If you encounter any issues, you can create an issue on GitHub.