Connectors → MySQL

About MySQL

MySQL is one of the most popular open-source relational database management systems. MySQL has a proven track record of performance, reliability, and ease-of-use. A relational database organizes data into tables. These tables contain data types that can have relations. These relationships help structure the data. Programmers use SQL to create, extract, modify, and control access to data in a relational database.

MySQL Connector Updates

This section is to explore the updates in the newer versions of the MySQL connector available on the Incorta connectors marketplace.

In order to get the newer version of the connector, please update the connector using the marketplace.

MySQL Connector

Incorta loads data from a MySQL database using the MySQL connector. The MySQL Connector uses the 5.1.48 MySQL driver.

The MySQL connector supports the following Incorta specific functionality:

Steps to connect MySQL and Incorta

To connect MySQL and Incorta, here are the high-level steps, tools, and procedures:

• Create an external data source
• Create a schema with the Schema Wizard
• or, Create a schema with the Schema Designer
• Load the schema
• Explore the schema

Create an external data source

Here are the steps to create an external data source with the MySQL connector:

• Sign in to the Incorta Direct Data Platform.
• In the Navigation bar, select Data.
• In the Action bar, select + New → Add Data Source.
• In the Choose a Data Source dialog, in Database, select MySQL.
• In the New Data Source dialog, specify the applicable connector properties.
• To test, select Test Connection.
• Select Ok to save your changes.

MySQL connector properties

Here are the properties for the MySQL connector:

Connection Properties

The connection properties allow for customized connection to your MySQL database. The properties are accepted in a key=value format. The type of connector properties can range from username and password to SSL settings. The 5.1.48 MySQL driver determines the available connection properties. For a list of connection properties available with Incorta and a MySQL database refer to Configuration Properties for Connector.

Create a schema with the Schema Wizard

Here are the steps to create an MySQL schema with the Schema Wizard:

• Sign in to the Incorta Direct Data Platform.
• In the Navigation bar, select Schema.
• In the Action bar, select + New → Schema Wizard.
• In (1) Choose a Source, specify the following:
  • For Enter a name, enter the schema name.
  • For Select a Datasource, select the MySQL external data source.
  • Optionally create a description.
• In the Schema Wizard footer, select Next.
• In (2) Manage Tables, in the Data Panel, first select the name of the Data Source, and then check the Select All checkbox.
• In the Schema Wizard footer, select Next.
• In (3) Finalize, in the Schema Wizard footer, select Create Schema.

Create a schema with the Schema Designer

Here are the steps to create an MySQL schema using the Schema Designer:

• Sign in to the Incorta Direct Data Platform.
• In the Navigation bar, select Schema.
• In the Action bar, select + New → Create Schema.
• In Name, specify the schema name, and select Save.
• In Start adding tables to your schema, select SQL Database.
• In the Data Source dialog, specify the MySQL table data source properties.
• Select Add.
• In the Table Editor, in the Table Summary section, enter the table name.
• To save your changes, select Done in the Action bar.

MySQL table data source properties

For a schema table in Incorta, you can define the following MySQL specific data source properties as follows:

View the schema diagram with the Schema Diagram Viewer

Here are the steps to view the schema diagram using the Schema Diagram Viewer:

• Sign in to the Incorta Direct Data Platform.
• In the Navigation bar, select Schema.
• In the list of schemas, select the MySQL schema.
• In the Schema Designer, in the Action bar, select Diagram.

Load the schema

Here are the steps to perform a Full Load of the MySQL schema using the Schema Designer:

• Sign in to the Incorta Direct Data Platform.
• In the Navigation bar, select Schema.
• In the list of schemas, select the MySQL schema.
• In the Schema Designer, in the Action bar, select Load → Full Load.
• To review the load status, in Last Load Status, select the date.

Explore the schema

With the full load of the MySQL schema complete, you can use the Analyzer to explore the schema, create your first insight, and save the insight to a new dashboard.

To open the Analyzer from the schema, follow these steps:

• In the Navigation bar, select Schema.
• In the Schema Manager, in the List view, select the MySQL schema.
• In the Schema Designer, in the Action bar, select Explore Data.

Additional Considerations

Types of Incremental Load

You can enable Incremental Load for a MySQL table data source. There are two types of incremental extracts:

Last Successful Extract Time

Fetch updates since the last time the tables were loaded. Determined by the difference between the current time and the database timestamp.

Maximum Value of a Column

The column-based strategy depends on an extra column called "Incremental Column" in each table. The MySQL connector supports both timestamp and numeric columns. A timestamp column is of the type date or timestamp. A numeric column is of the type int or long.

Log-based incremental load

Starting the connector version 2.0.2.0, Incorta supports the log-based incremental load using the change data capture (CDC).

CDC is the process of identifying and capturing changes made to data in a database using logs and then delivering those changes in real time to a downstream process or system.

Prerequisites

To be able to use the log-based incremental load, you need to be aware of and apply the following:

1. Install and configure Apache Kafka and Kafka Connect.
2. Configure Debezium connector, knowing that Incorta recommends using Debezium version 2.4.1.
3. Disable snapshot while configuring Debezium.
4. Make sure Debezium connector is configured to send data types to Incorta by adding the propagate property.
5. Log-based incremental load only supports database physical tables.
6. Tables must have primary keys.

Known limitations

• For the time being, Incorta does not track deletion updates through the log-based incremental load (CDC).
• Minimal mismatch in column INTERVAL data types.
• This feature supports Kafka topics that use a single partition only.

Incremental Load Example

In this example, the invoices table must contain a column of the type Date or Timestamp in order to load the table incrementally with a last successful extract time strategy. In this case, the name of the date column is ModifiedDate and the format of the column is Timestamp.

Here are the data source property values for this example:

• Incremental is enabled
• Query contains SELECT * FROM `invoices`
• Update Query contains SELECT * FROM `invoices` WHERE `ModifiedDate` > ?

• Incremental Field Type = Timestamp

Valid Query Types

When creating a query for your MySQL Connector, please note that only SELECT statements are valid.

Pre-SQL support

This feature allows executing SQL statements before executing the original extraction query or incremental query during a load job. This helps in different scenarios, for example, when you need to do one of the following:

• Run stored procedures
• Update the data source before extraction
• Create a temporary table before executing the extraction query
• Delete old records in the source so that the source includes only the latest records