MariaDB/SkySQL (MySQL-compatible) Non-Spatial Reader/Writer

FME can read and write attribute data stored in a MariaDB database. There are two versions of the reader and writer:

  • MariaDB Non-Spatial (the version in this chapter) is an attribute-only version that ignores geometry. This reader and writer communicates directly with the MySQL C API interface for maximum throughput.
  • MariaDB Spatial includes the spatial extension and supports geometry features.

MariaDB ®1 is a well-known DataBase Management System (DBMS) that provides various types of tables for different database applications. This chapter assumes familiarity with MariaDB, the table types, column types, available server daemons, indexing mechanisms and connection parameters.

Note  This format uses terminology specific to MySQL, since MariaDB is known to work well with MySQL Servers.

MariaDB/SkySQL (MySQL-compatible) Non-Spatial Product and System Requirements

Compatibility  This writer is compatible with MariaDB versions up to 10.4, and MySQL versions up to 8.0.

Format

FME Platform

Operating System

Reader/Writer

FME Form

FME Flow

FME Flow Hosted

Windows 64-bit

Linux

Mac

Reader

Yes

Yes

Yes

Yes

Yes

Yes

Writer

Yes

Yes

Yes

Yes

Yes

Yes

Reader Overview

FME considers a MariaDB dataset to be a database containing a collection of relational tables, and a table to be an FME feature type with each row corresponding to at least one FME feature.

Tables schemas must be defined in the FME workspace before they can be read.

Arbitrary WHERE clauses and joins are fully supported, as well as an entire arbitrary SQL SELECT statement. The basic reading process involves opening a connection to the database, querying metadata, and querying data. The data is read by submitting SQL queries and parsing the returned result sets.

Please note that MariaDB functionality that only exists in the MariaDB road map and not in practice (such as server side cursors and views) are not currently integrated into this reader.

Writer Overview

The writer module stores both geometry and attributes into a MariaDB database, and has the following capabilities:

  • Transaction Support: The MariaDB writer provides transaction support that eases the data loading process. Occasionally, a data load operation terminates prematurely due to data difficulties. The transaction support provides a mechanism for reloading corrected data without data loss or duplication. Performance can also be improved by reducing transactional overhead for multiple small queries such as inserts.
  • Index Creation: The MariaDB writer can set up and populate indexes as part of the loading process. By default, no indexes are created. Columns can be individually indexed. Composite column indexes are not supported at this time.
  • Insert Binding: By default, the MariaDB writer uses prepared statements and query parameter binding ensure speedy data loading.