1. Objective

To give the possibility to call the execution of external:

• Python.
• Or R Statistical Programming Language.

Scripts directly from MariaDB, just as SQL Server, from 2017 Edition onward, allows to do so:

• sp_execute_external_script (Transact-SQL)
• Run Python using T-SQL
• Using R functions with SQL Server data (R in SQL quickstart)

And PostgreSQL so does for Python:

• Chapter 42. PL/Python - Python Procedural Language
• 43.2. PL/Python Functions

The ultimate objetive is, for example, to trigger the execution of a remote Python/R Code for updating a predictive model in keras/h2o/tensorflow/... when new data is introduced into the database, or as part of a SQL procedure.

2. OPTION A: Through a Table

2.1 Implementation

One possible implementation could be as follows:

2.1.1. Enable External Script Execution: my.ini

[mysqld]

connect_enable_execute_external_script = {0|1}

connect_python_exe_dir = "C:/Users/J-tel/AppData/Local/Programs/Python/Python36-32/python.exe"

connect_r_exe_dir = "C:/Program Files/Microsoft/R Open/R-3.4.3/bin/Rscript.exe"

connect_max_exe_parallel = {1(Default)|Number}

2.1.2. Script Repository Creation: CREATE TABLE

For using the "CONNECT REMOTE_TABLE" Engine, the user will have to create an empty table as follows:

CREATE [OR REPLACE] [IF NOT EXISTS] TABLE tbl_name_connect

  ENGINE = CONNECT

  TABLE_TYPE = REMOTE_TABLE

  LANGUAGE = {R|Python}

  [PARALLEL={0|1}]

  [WITH VERSION CONTROL];

2.1.3. Call a Script: SELECT

There will be 3 main ways of calling a R / Python Script from MariaDB:

A. To Import an R / Python Script on the fly an call it from MariaDB Server:

SELECT

  LOAD_FILE('/path/.../script_file_name.txt'))

FROM

  tbl_name_connect;

B. To write a Script directly in the IDE and call it from MariaDB Server:

SELECT

  QUOTE("... [R or Python Script] ...")

FROM

  tbl_name_connect;

C. To call a previously saved R / Python Script into tbl_name:

SELECT

  (SELECT Col_Script FROM tbl_name_script_repository LIMIT 1;)

FROM

  tbl_name_connect;

2.2 Internals

Connect will:

1. Send the Script Text to the .exe file.
2. Retrieve a data.frame Table (Last Evaluated Object or return() Object), or retrieve any error / warnings and if the execution was complete.

An example R Script to execute in the RScript.exe, which makes use of an ODBC Connection, would be:

library(tidyverse)

library(DBI)

library(RMariaDB)

con <- dbConnect(RMariaDB::MariaDB(), group = "my-db")

dbWriteTable(con, "mtcars", mtcars)

dbReadTable(con, "mtcars")

dbDisconnect(con)

2.3. Security Issues

This new feature could imply security issues, so:

• It shall be disabled by default.
• Have its own USER security permission.
• MariaDB to include its own trusted distributions of R and Python.

Also another solution would be to have to force the users to store the Python and R .exe directly in the MariaDB installation folders, so that greater care of such executable files can be taken in the system.

3. OPTION B: Through a Stored Procedure

This new feature could imply security issues, so:

• It shall be disabled by default.
• Have its own USER security permission.
• MariaDB to include its own trusted distributions of R and Python.

Also another solution would be to have to force the users to store the Python and R .exe directly in the MariaDB installation folders, so that greater care of such executable files can be taken in the system.

4. Collaboration

I could ask the maintainer of the R "RMariaDB" package (Developed in C++ and R) in case he would be interested in collaborate in this project.