CrateDB MariaDB FDW example

cratedb_mariadb_fdw_example.md

Setup MariaDB

Run MariaDB inside docker

% docker run --name mariadb-10.3 -e MYSQL_ROOT_PASSWORD=mypass -p 3306:3306 -d mariadb:10.3

Create database and table

Connect inside docker using mysql CLI:

% docker exec -it mariadb-10.3 bash
# mysql -u root -p

Create database and table:

create database d1;
use d1;
create table t1 (id int, s text);
insert into t1 values (1, 'foo'), (2, 'bar');

Setup CrateDB

Build CrateDB tarball

Inside the crate/crate repository, run:

./mvnw package -Dcheckstyle.skip -DskipTests=true

Alternatively, use the nightly tarball.

Extract the tarball to a given location

cd ../tmp
tar xzf ../crate/app/target/crate-5.7.0-2024-03-20-10-58-73ab2cd.tar.gz

Install MariaDB JDBC connector

Download and install the MariaDB JAR connector to the lib/ folder (all JAR files in this location will be added the class path):

% wget https://dlm.mariadb.com/3752081/Connectors/java/connector-java-3.3.3/mariadb-java-client-3.3.3.jar
% mv mariadb-java-client-3.3.3.jar crate-5.7.0-2024-03-20-10-58-73ab2cd/lib/

Run CrateDB and create a foreign table to MariaDB

Start CrateDB

% cd crate-5.7.0-2024-03-20-10-58-73ab2cd
% ./bin/crate

Connect via crash CLI:

crash -vv

Create remote table via FDW:

CREATE SERVER mariadb FOREIGN DATA WRAPPER jdbc OPTIONS (url 'jdbc:mariadb://localhost:3306/d1');
CREATE USER MAPPING FOR USER SERVER mariadb OPTIONS ("user" 'root', password  'mypass');
CREATE FOREIGN TABLE maria.t1 (id int, s text) SERVER mariadb OPTIONS (schema_name 'd1');

Create local table (to join with):

create table t1 (id int, s text);
insert into t1 values (1, 'crate foo'), (2, 'crate bar'), (3, 'crate');

Run some queries

select * from maria.t1;
select * from t1 left join maria.t1 mt1 on t1.id = mt1.id;
select * from t1 join maria.t1 mt1 on t1.id = mt1.id where mt1.s = 'bar';