FME can read and write both attribute and geometric data stored in a MariaDB database.
There are two versions of the reader and writer:
- MariaDB Spatial, which includes the spatial extension and supports geometry features, and
- MariaDB Non-Spatial, which is an attribute-only version that ignores geometry.
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.
MariaDB/SkySQL (MySQL-compatible) Product and System Requirements
|
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 |
MariaDB Spatial Overview
Currently the table types that can be read are MyISAM, InnoDB and MEMORY. Note that only MyISAM tables currently support geometry columns.
The MariaDB Spatial Reader and Writer are specifically designed to handle the geometric portions of the data when present and are optimized for non-geometry or attribute processing when no spatial columns are present. The MariaDB reader and writer communicate directly with the MariaDB C API interface for maximum throughput.
This chapter assumes familiarity with MariaDB, the table types, column types, OGC geometry types, available server daemons, indexing mechanisms and connection parameters.
Please note that MariaDB functionality that only exists in the MariaDB road map and not in practice (such as integration of the Spatial Reference Systems (SRS), spatial metadata tables, spatial operators, server side cursors and views) were considered but are not currently integrated into the MariaDB reader for FME.
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. In the case of geometry collections, a single row may become more than one FME feature.
Tables schemas must be defined in the FME workspace before they can be read and can consist of both geometric and primary column types, however, only one geometry column is read per table while others are discarded.
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.
If null geometries are read, they are treated as non-geometry features and the attributes are preserved.
Writer Overview
The MariaDB writer module stores both geometry and attributes into an MariaDB database. The MariaDB writer provides 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, a spatial R-Tree index is created on the primary geometry column of a table and not on any other columns. Additional 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.
Each MariaDB table must be defined before it can be written.
The table definition allows control of the table that will be created. If the table already exists, the majority of the mysql_ parameters will be ignored (and are therefore not required). If the fields and types are listed, they must match those in the database.
If the table does not exist, then the field names and types are used to first create the table. If a field type is given, it may be any field type supported by the target database.