How to use HStream connectors with MySQL

    TIP

    Up to the present, we only provide sink connectors writing to MySQL or ClickHouse. The recommended verion of MySQL is 5.7 and MySQL versions after 8.0 are not supported yet.

    Make sure you have started an HStreamDB server and a client connected to the server. You also need MySQL or ClickHouse service available in order to dump data to the databases. In this tutorial, I will use HStream CLI client and MySQL database as the example. To better manipulate data in MySQL, I will use MyCLI as the interface but any alternatives should work similarly. Please replace the host address and port number with your own configuration.

    I will be using a database called in MySQL and a table called hstreamtbl with two columns of integers in the database.

    1. > USE hstreamdb;
    2. > CREATE TABLE IF NOT EXISTS hstreamtbl (temperature INT, humidity INT);

    Create Built-In Connectors

    We first create a source stream called hstreamsrc in our server via an HStream client.

    1. CREATE STREAM hstreamsrc;

    Connector options include:

    Options with a * symbol are required and others are optional. The option has to be either mysql or clickhouse for now and the default value listed above are specific to the MySQL option. If the name of table is omitted, the connector will write the data to a table with the same name as the source stream.

    Back to our example, we will use the following command to create a sink connector called mysql_conn that subsribes to the hstreamsrc stream.

    1. CREATE SINK CONNECTOR mysql_conn WITH (type = mysql, stream = hstreamsrc, username = "root", password = "", host = "127.0.0.1", port = 3306, database = "hstreamdb", table = "hstreamtbl");

    You can use the following command to check the status of a connector.

    1. SHOW CONNECTORS;

    One of the following states is assigned to the connectors:

    statedescription
    CreatingThe server has started to process the request
    CreatedThe connection has been established but it has not started to process the data
    CreationAbortThe process of creating the connection failed and it is frozon
    RunningThe connector is ready to process requests
    ExecutionAbortThe connector failed to execute a SQL statement and it is frozen
    TerminateThe connector is frozen by a user request

    Once the connector has been set up, the data inserted into the source stream thereafter will be written into the connected external system in a very short time gap.

    For example, we can insert some data to the source stream by

    1. INSERT INTO hstreamsrc (temperature, humidity) VALUES (13, 83);
    2. INSERT INTO hstreamsrc (temperature, humidity) VALUES (14, 82);

    Please make sure that the data inserted into the source stream follow the schema of the table in the database. Otherwise, a MySQL error will happen and the connection is broken subsequently.

    After inserting the data into the source stream, you should be able to view the data on MySQL end,

    1. > SELECT * FROM hstreamtbl;
    • What happened if the status of the connector is CreationAbort?

    This is caused by an error occured when the server tried to connect to the MySQL service. Please double check that you have passed the correct configuration options, especially the port number, and that the database has been created. Please drop the connector before you try again.

    • What happened if the status of the connector is ?

    It means a client has requested that the connector be terminated. You could restart the connection (in the future) or drop it in this circumstance.