Starting with CONNECT version 1.06.0010, JSON, XML and possibly CSV data files can be retrieved as results from REST queries when creating or querying such tables. However, because this feature uses the Microsoft Casablanca (cpprestsdk) package, it is not included in the binary distributions of MariaDB. To get this feature, it is necessary to compile MariaDB from source.

First, install the package as explained in https://github.com/microsoft/cpprestsdk. Then get the source and make, compile and install MariaDB as explained in the MariaDB documentation. The result should be a MariaDB version with CONNECT having this feature enabled.

Note: On Windows, the compiled Debug version generated code must be compatible with the cpprest dll called by CONNECT when using REST. If not, this will cause a server crash. If this occurs, recompile CONNECT with Visual Studio after setting the Code Generation Runtime Library to the proper value. It could depend on the way cpprestsdk was installed and most of the time will be /MDd.

Note: If you want to use this feature with a binary distributed version of MariaDB, even a previous version not containing the last CONNECT version, it is also possible to add it as an OEM module as explained in Appendix B.

Creating Tables using REST

To do so, specify the HTTP of the web client and eventually the URI of the request in the CREATE TABLE statement. For example, for a query returning JSON data:

CREATE TABLE webusers (
  id bigint(2) NOT NULL,
  name char(24) NOT NULL,
  username char(16) NOT NULL,
  email char(25) NOT NULL,
  address varchar(256) DEFAULT NULL,
  phone char(21) NOT NULL,
  website char(13) NOT NULL,
  company varchar(256) DEFAULT NULL
) ENGINE=CONNECT DEFAULT CHARSET=utf8
TABLE_TYPE=JSON FILE_NAME='users.json' HTTP='http://jsonplaceholder.typicode.com' URI='/users';

As with standard JSON tables, discovery is possible, meaning that you can leave CONNECT to define the columns by analyzing the JSON file. Here you could just do:

CREATE TABLE webusers
ENGINE=CONNECT DEFAULT CHARSET=utf8
TABLE_TYPE=JSON FILE_NAME='users.json'
HTTP='http://jsonplaceholder.typicode.com' URI='/users';

Note that such tables are read only. In addition, the data will be retrieved from the web each time you query the table with a SELECT statement. This is fine if the result varies each time, such as when you query a weather forecasting site. But if you want to use the retrieved file many times without reloading it, just create another table on the same file without specifying the HTTP option.