CONNECT is a storage engine (SE) plugin used to access external, local or remote data. In this blog we are going to show how to install the CONNECT storage engine in a Docker container and how to share JSON data between containers.
Enabling CONNECT SE plugin in Docker
CONNECT SE needs to be installed within the container in order to use it. To see how to do that please check Installing plugins in the MariaDB Docker Library Container.
Create JSON data on remote server
The most important feature of CONNECT SE to MariaDB is the flexibility to create tables from various data sources, like the same database and other DMBS’s tables or files with different formats. You can read more at CONNECT Table types. Here we will use the CONNECT JSON Table type.
We are going to have 2 containers, mariadb-remote as a server where the source data will be, and mariadb-local which we will treat as a local server where we are going to create a table from the source server. So let’s first create JSON data on the source server.
- Create the network for the sake of having a remote connection
$ docker network create mynetwork- Start the source container
mariadb-remoteusing the image my_connect_img and by specifying grants for the user created during container start-up, which you will find in the previous blog.
$ docker run --rm -d --name mariadb-remote -v$PWD/local_init_dir1:/docker-entrypoint-initdb.d \
-e MARIADB_USER=anel -e MARIADB_PASSWORD=anel -e MARIADB_DATABASE=web_users \
--network mynetwork -e MARIADB_ALLOW_EMPTY_ROOT_PASSWORD=1 my_connect_img
- Validate grants for the user
$ docker exec -it mariadb-remote mariadb -uanel -panel web_users -e "show grants for current_user;"
+--------------------------------------------------------------------------------------------------------------+
| Grants for anel@% |
+--------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `anel`@`%` IDENTIFIED BY PASSWORD '*1F81CD5C3293B40FA2B98C69E8495874BE999E34' |
| GRANT ALL PRIVILEGES ON `web\_users`.* TO `anel`@`%` |
+--------------------------------------------------------------------------------------------------------------+
- Since we built from an image where CONNECT is installed, no need to check for existence of the SE.
- Create the JSON data.
We are going to use data from users1 and users2, store them in a file via SQL statements, and create the CONNECT table from that file using the table discovery feature. The steps are:
— Start the client
docker exec -it mariadb-remote mariadb -uanel -panel web_users— Execute the queries to create the file with set statement and select into outfile.
set @web_users='[{
"id": 1,
"name": "Leanne Graham",
"username": "Bret",
"email": "Sincere@april.biz",
"address": {
"street": "Kulas Light",
"suite": "Apt. 556",
"city": "Gwenborough",
"zipcode": "92998-3874",
"geo": {
"lat": "-37.3159",
"lng": "81.1496"
}
},
"phone": "1-770-736-8031 x56442",
"website": "hildegard.org",
"company": {
"name": "Romaguera-Crona",
"catchPhrase": "Multi-layered client-server neural-net",
"bs": "harness real-time e-markets"
}
},
{
"id": 2,
"name": "Ervin Howell",
"username": "Antonette",
"email": "Shanna@melissa.tv",
"address": {
"street": "Victor Plains",
"suite": "Suite 879",
"city": "Wisokyburgh",
"zipcode": "90566-7771",
"geo": {
"lat": "-43.9509",
"lng": "-34.4618"
}
},
"phone": "010-692-6593 x09125",
"website": "anastasia.net",
"company": {
"name": "Deckow-Crist",
"catchPhrase": "Proactive didactic contingency",
"bs": "synergize scalable supply-chains"
}
}]';
SET STATEMENT SQL_MODE= CONCAT(@@SQL_MODE, ',NO_BACKSLASH_ESCAPES') FOR SELECT @web_users into outfile 'web_users.json';
— Create the CONNECT table from a file and see results
CREATE TABLE webusers
ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='web_users.json';
MariaDB [web_users]> show create table webusers\G
*************************** 1. row ***************************
Table: webusers
Create Table: CREATE TABLE `webusers` (
`id` int(1) NOT NULL `JPATH`='$.id',
`name` char(13) NOT NULL `JPATH`='$.name',
`username` char(9) NOT NULL `JPATH`='$.username',
`email` char(17) NOT NULL `JPATH`='$.email',
`address_street` char(13) NOT NULL `JPATH`='$.address.street',
`address_suite` char(9) NOT NULL `JPATH`='$.address.suite',
`address_city` char(11) NOT NULL `JPATH`='$.address.city',
`address_zipcode` char(10) NOT NULL `JPATH`='$.address.zipcode',
`address_geo_lat` char(8) NOT NULL `JPATH`='$.address.geo.lat',
`address_geo_lng` char(8) NOT NULL `JPATH`='$.address.geo.lng',
`phone` char(21) NOT NULL `JPATH`='$.phone',
`website` char(13) NOT NULL `JPATH`='$.website',
`company_name` char(15) NOT NULL `JPATH`='$.company.name',
`company_catchPhrase` char(38) NOT NULL `JPATH`='$.company.catchPhrase',
`company_bs` char(32) NOT NULL `JPATH`='$.company.bs'
) ENGINE=CONNECT DEFAULT CHARSET=utf8mb4 `TABLE_TYPE`='JSON' `FILE_NAME`='web_users.json'
1 row in set (0.000 sec)
Create the local table from the remote server
Again, we need to create the local container/server called mariadb-local. Here are steps that need to be done.
Start the local container mariadb-local (again we are defining grants in a file and using a volume to apply them).
Please do the same steps as before to install the plugin, validate grants for your user, restart the server and check whether the plugin works.
$ docker run --rm -d --name mariadb-local \
-e MARIADB_USER=local_user -e MARIADB_PASSWORD=local_user_pw -e MARIADB_DATABASE=test \
--network mynetwork -v$PWD/local_init_dir:/docker-entrypoint-initdb.d \
-e MARIADB_ALLOW_EMPTY_ROOT_PASSWORD=1 my_connect_img- Create the table using remote connection to the source server
— Start the client
$ docker exec -it mariadb-local mariadb -ulocal_user -plocal_user_pw test— Create the table from remote server (note that the local user needs to have at least the FILE privilege to use for the outwards CONNECT table).
MariaDB [test]> show tables;
Empty set (0.000 sec)
MariaDB [test]> CREATE TABLE local_web_user
ENGINE = CONNECT
TABLE_TYPE = MYSQL
DBNAME = 'web_users'
TABNAME = 'webusers'
CONNECTION = 'mysql://anel:anel@mariadb-remote/';
Query OK, 0 rows affected (0.041 sec)
MariaDB [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| local_web_user |
+----------------+
1 row in set (0.000 sec)And voila, we now have our data from the remote server.
- Create the new tables
Now we can create the InnoDB tables if we wish.
MariaDB [test]> create table new_table as select name, email, address_street from local_web_user;
MariaDB [test]> show create table new_table;
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| new_table | CREATE TABLE `new_table` (
`name` char(13) NOT NULL,
`email` char(17) NOT NULL,
`address_street` char(13) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)
MariaDB [test]> select * from new_table;
+---------------+-------------------+----------------+
| name | email | address_street |
+---------------+-------------------+----------------+
| Leanne Graham | Sincere@april.biz | Kulas Light |
| Ervin Howell | Shanna@melissa.tv | Victor Plains |
+---------------+-------------------+----------------+
2 rows in set (0.000 sec)
— One can use a specific query in order to create the table from the remote:
MariaDB [test]> CREATE TABLE local_web_user1 ENGINE = CONNECT TABLE_TYPE = MYSQL SRCDEF = 'SELECT * FROM web_users.webusers WHERE id=1' CONNECTION = 'mysql://anel:anel@mariadb-remote/';The result:
MariaDB [test]> select * from local_web_user1;
+----+---------------+----------+-------------------+----------------+---------------+--------------+-----------------+-----------------+-----------------+-----------------------+---------------+-----------------+----------------------------------------+-----------------------------+
| id | name | username | email | address_street | address_suite | address_city | address_zipcode | address_geo_lat | address_geo_lng | phone | website | company_name | company_catchPhrase | company_bs |
+----+---------------+----------+-------------------+----------------+---------------+--------------+-----------------+-----------------+-----------------+-----------------------+---------------+-----------------+----------------------------------------+-----------------------------+
| 1 | Leanne Graham | Bret | Sincere@april.biz | Kulas Light | Apt. 556 | Gwenborough | 92998-3874 | -37.3159 | 81.1496 | 1-770-736-8031 x56442 | hildegard.org | Romaguera-Crona | Multi-layered client-server neural-net | harness real-time e-markets |
+----+---------------+----------+-------------------+----------------+---------------+--------------+-----------------+-----------------+-----------------+-----------------------+---------------+-----------------+----------------------------------------+-----------------------------+
1 row in set (0.001 sec)Conclusion and future work
The main purpose of this blog is to validate the power of CONNECT SE with simple syntax to create data from a custom data source and to get data from a remote server.
This is not all. We have just seen a single use case and scratched the surface of this great storage engine.
In the next blog, we are going to see how to properly create the connection string for a database and use it with the CONNECT SE.
Feedback Welcome
If you come across any problems in this feature preview, with the design, or edge cases that don’t work as expected, please let us know with a JIRA bug/feature request in the MDEV project. You are welcome to chat about it on Zulip.