Connectors → MariaDB

About MariaDB

MariaDB is one of the popular open-source relational databases. It is also the default database in most Linux distributions. 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.

MariaDB Connector

Incorta loads data from a MariaDB database using the MariaDB connector.

The MariaDB connector supports the following Incorta-specific functionality:

Steps to connect MariaDB and Incorta

To connect MariaDB 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 MariaDB 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 MariaDB.
• In the New Data Source dialog, specify the applicable connector properties.
• To test, select Test Connection.
• Select OK to save your changes.

MariaDB connector properties

Here are the properties for the MariaDB connector:

Connection Properties

The connection properties allow for customized connection to your MariaDB database. The properties are accepted in a key=value format. The type of connector properties can range from username and password to SSL settings.

Create a schema with the Schema Wizard

Here are the steps to create an MariaDB 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 MariaDB 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 MariaDB 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 MariaDB 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.

MariaDB table data source properties

For a schema table in Incorta, you can define the following MariaDB-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 MariaDB 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 MariaDB 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 MariaDB 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 MariaDB 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 MariaDB 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 MariaDB 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 MariaDB 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.

Incremental Load Example

In this example, the invoices table must contain a column of the type Date or Timestamp 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 MariaDB 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