MariaDB Plugin#
The MariaDB plugin mainly provides a JDBC driver to access MariaDB databases via the JDBC relation
Activation#
The plugin can be easily activated by adding the following section to the default-namespace.yml
plugins:
- flowman-mariadb
Fixing SQL syntax errors during write operations#
Newer MariaDB versions expect a slightly different syntax for quoting column names as opposed to the way that Spark is doing it. This can be changing the SQL mode on the MariaDB server to allow ANSI Quotes:
SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ANSI_QUOTES';
You can also set the SQL mode as a MariaDB server startup parameter by adding the following option to the MariaDB daemon:
--sql-mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ANSI_QUOTES
Usage#
In order to connect to a MariaDB database, you need to specify a JDBC connection and use that one in a JDBC relation as follows:
# First specify a connection. This can be used by multiple JDBC relations
connections:
frontend:
kind: jdbc
driver: "org.mariadb.jdbc.Driver"
url: "jdbc:mariadb://my-mariadb-database.domain.com"
username: "my_username"
password: "secret!password"
relations:
frontend_users:
kind: jdbcTable
# Specify the name of the connection to use
connection: frontend
# Specify database
database: "frontend"
# Specify the table
table: "users"
Data Types#
Flowman will map its built in data types to the following data types in MariaDB
Flowman/Spark Datatype | MariaDB Datatype |
---|---|
string , text |
TEXT |
binary |
BLOB |
tinyint , byte |
BYTE |
smallint , short |
INTEGER |
int , integer |
INTEGER |
bigint , long |
BIGINT |
boolean , bool |
BIT(1) |
float |
FLOAT |
double |
DOUBLE PRECISION |
decimal(a,b) |
DECIMAL(a,b) |
varchar(n) |
VARCHAR(n) |
char(n) |
CHAR(n) |
date |
DATE |
timestamp |
TIMESTAMP |
duration |
unsupported |