CONNECT MONGO Table Type: Accessing Collections from MongoDB

You are viewing an old version of this article. View the current version here.

The MONGO table type is being introduced in Connect 1.6.

Classified as a NoSQL database program, MongoDB uses JSON-like documents (BSON) grouped in collections. The MONGO type is used to directly access MongoDB collections as tables via the MongoDB C Driver API1.

This type is currently not available from binary distributions but only for versions compiled from source. The current version of the MongoDB C Driver does not provide package recognition2. Therefore, distributed CMake ignores it, making adding the MONGO table type to be done manually. This is what must be done:

  1. Install and start a MongoDB server if none are available.
  2. Install the MongoDB C Driver.
  3. Add the mongo related source files: tabmgo.cpp, tabmgo.h, mongofam.cpp, mongofam.h.
  4. Add to the compile step the include directories …\mongo-c-driver\include\libbson-1.0 and …\mongo-c-driver\include\libmongoc-1.0.
  5. Add for the linker the libraries …\mongo-c-driver\lib\bson-1.0.lib and …\mongo-c-driver\lib\mongoc-1.0.lib.
  6. Add C++ definition -DMONGO_SUPPORT.
  7. Compile and install MariaDB.

Note: localize the paths according to your machine and system.

On Windows using Visual Studio, this can be done after the solution is generated by CMake. Note that this was not tested on Linux yet.

CONNECT MONGO Tables

A MONGO table is defined to access a MongoDB collection. The table rows will be the collection documents. For instance, to create a table based on the MongoDB sample collection restaurants, you can do something such as the following:

create table resto (
_id varchar(24) not null,
name varchar(64) not null,
cuisine char(200) not null,
borough char(16) not null,
restaurant_id varchar(12) not null)
engine=connect table_type=MONGO tabname='restaurants'
data_charset=utf8 connection='mongodb://localhost:27017';

Here we did not define all the items of the collection documents but only those that are JSON values. The database is test by default. The connection value is the URI used to establish a connection to a local or remote MongoDB server. The value shown in this example corresponds to a local server started with its default port. It is the default connection value for MONGO tables so we could have omit specifying it.

Using discovery is available. This table could have been created by:

create table resto
engine=connect table_type=MONGO tabname='restaurants'
data_charset=utf8 option_list='level=-1';

Here “level=-1” is used to create only columns that are simple values (no array or object). Without this, with the default value “level=0” the table had been created as:

CREATE TABLE `resto` (
  `_id` char(24) NOT NULL,
  `address` varchar(512) NOT NULL,
  `borough` char(13) NOT NULL,
  `cuisine` char(64) NOT NULL,
  `grades` varchar(512) NOT NULL,
  `name` char(98) NOT NULL,
  `restaurant_id` char(8) NOT NULL
) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`='MONGO' `TABNAME`='restaurants' `DATA_CHARSET`='utf8';

Fixing problems with mysqldump

In some case or some platforms, when CONNECT is set up for use with JDBC table types, this causes mysqldump with the --all-databases option to fail.

This was reported by Robert Dyas who found the cause of it and how to fix it (see MDEV-11238).

This occurs when the Java JRE “Usage Tracker” is enabled. In that case, Java creates a directory #mysql50#.oracle_jre_usage in the mysql data directory that shows up as a database but cannot be accessed via MySQL Workbench nor apparently backed up by mysqldump --all-databases.

Per the Oracle documentation (https://docs.oracle.com/javacomponents/usage-tracker/overview/) the “Usage Tracker” is disabled by default. It is enabled only when creating the properties file <JRE directory>/lib/management/usagetracker.properties. This turns out to be WRONG on some platforms as the file does exist by default on a new installation, and the existence of this file enables the usage tracker.

The solution on CentOS 7 with the Oracle JVM is to rename or delete the usagetracker.properties file (to disable it) and then delete the bogus folder it created in the mysql database directory, then restart.

For example, the following works:

sudo mv /usr/java/default/jre/lib/management/management.properties /usr/java/default/jre/lib/management/management.properties.TRACKER-OFF
sudo reboot
sudo rm -rf  /var/lib/mysql/.oracle_jre_usage
sudo reboot

In this collection, the address column is a JSON object and the column grades is a JSON array. Unlike the JSON table, just specifying the column name with no Jpath result in displaying the JSON representation of them. For instance:

select name, address from resto limit 3;
nameaddress
Morris Park Bake Shop{"building":"1007","coord":[-73.8561,40.8484], "street":"Morris ParkAve", "zipcode":"10462"}
Wendy'S{"building":"469","coord":[-73.9617,40.6629], "street":"Flatbush Avenue", "zipcode":"11225"}
Reynolds Restaurant{"building":"351","coord":[-73.9851,40.7677], "street":"West 57Street", "zipcode":"10019"}

To address the items inside object or arrays, specify the Jpath in MongoDB syntax3:

create table newresto (
_id varchar(24) not null,
name varchar(64) not null,
cuisine char(200) not null,
borough char(16) not null,
street varchar(65) field_format='address.street',
building char(16) field_format='address.building',
zipcode char(5) field_format='address.zipcode',
grade char(1) field_format='grades.0.grade',
score int(4) not null field_format='grades.0.score', 
`date` date field_format='grades.0.date',
restaurant_id varchar(255) not null)
engine=connect table_type=MONGO tabname='restaurants'
data_charset=utf8 connection='mongodb://localhost:27017';

If this is not done, the Oracle JVM will start the usage tracker, which will create the hidden folder .oracle_jre_usage in the mysql home directory, which will cause a mysql dump of the server to fail.

select name, street, score, date from newresto limit 5;
namestreetscoredate
Morris Park Bake ShopMorris Park Ave203/03/2014
Wendy'SFlatbush Avenue830/12/2014
Dj Reynolds Pub And RestaurantWest 57 Street206/09/2014
Riviera CatererStillwell Avenue510/06/2014
Tov Kosher Kitchen63 Road2024/11/2014

MONGO Specific Options

The MongoDB syntax for Jpath does not allow the CONNECT specific items on arrays. The same effect can still be obtained by a different way. For this, additional options are used when creating MONGO tables.

OptionTypeDescription
ColistStringOptions to pass to the MongoDB cursor.
FilterStringQuery used by the MongoDB cursor.
PipelineBooleanIf True, Colist is a pipeline.
FullarrayBooleanUsed when creating with Discovery.

Note: For the content of these options, refer to the MongoDB documentation.

Colist Option

Used to pass different options when making the MongoDB cursor used to retrieve the collation documents. One of them is the projection, allowing to limit the items retrieved in documents. It is hardly useful because this limitation is made automatically by CONNECT. However, it can be used when using discovery to eliminate the _id (or another) column when you are not willing to keep it:

create table restest
engine=connect table_type=MONGO tabname='restaurants'
data_charset=utf8 option_list='level=-1'
colist='{"projection":{"_id":0},"limit":5}';

In this example, we added another cursor option, the limit option that works like the limit SQL clause.

Filter Option

This option is used to specify a “filter” that works as a where clause on the table. Supposing we want to create a table restricted to the restaurant making English cuisine that are not located in the Manhattan borough, we can do it by:

create table english
engine=connect table_type=MONGO tabname='restaurants'
data_charset=utf8
colist='{"projection":{"cuisine":0}}'
filter='{"cuisine":"English","borough":{"$ne":"Manhattan"}}'
option_list='Level=-1';

And if we ask:

select * from english;

This query will return:

_idboroughnamerestaurant_id
58ada47de5a51ddfcd5ee1f3BrooklynThe Park Slope Chipshop40816202
58ada47de5a51ddfcd5ee999BrooklynChip Shop41076583
58ada47ee5a51ddfcd5f13d5BrooklynThe Monro41660253
58ada47ee5a51ddfcd5f176eBrooklynDear Bushwick41690534
58ada47ee5a51ddfcd5f1e91QueensSnowdonia Pub50000290

Pipeline Option

When this option is specified as true (by YES or 1) the Colist option contains a MongoDB pipeline applying to the table collation. This is a powerful mean for doing things such as expanding arrays like we do with JSON tables. For instance:

create table resto2 (
name varchar(64) not null,
borough char(16) not null,
date datetime not null,
grade char(1) not null,
score int(4) not null)
engine=connect table_type=MONGO tabname='restaurants' data_charset=utf8
colist='{"pipeline":[{"$match":{"cuisine":"French"}},{"$unwind":"$grades"},{"$project":{"_id":0,"name":1,"borough":1,"date":"$grades.date","grade":"$grades.grade","score":"$grades.score"}}]}'
option_list='Pipeline=1';

In this pipeline “$match” is an early filter, “$unwind” means that the grades array will be expanded (one Document for each array values) and “$project” eliminates the _id and cuisine columns and gives the Jpath for the date, grade and score columns.

select name, grade, score, date from resto2
where borough = 'Bronx';

This query replies:

namegradescoredate
Bistro SkA1021/11/2014 01:00:00
Bistro SkA1219/02/2014 01:00:00
Bistro SkB1812/06/2013 02:00:00

This make possible to get things like we do with JSON tables:

select name, avg(score) average from resto2
group by name having average >= 25;

Can be used to get the average score inside the grades array.

nameaverage
Bouley Botanical25,0000
Cheri46,0000
Graine De Paris30,0000
Le Pescadeux29,7500

Fullarray Option

This option, like the Level option, is only interpreted when creating a table with Discovery (meaning not specifying the columns). It tells CONNECT to generate a column for all existing values in the array. For instance, let us see the MongoDB collection tar by:

create table seetar (
Collection varchar(300) not null field_format='*')
engine=CONNECT table_type=MONGO tabname=tar;

The format ‘*’ indicates we want to see the Json documents. This small collection is:

Collection
{"_id":{"$oid":"58f63a5099b37d9c930f9f3b"},"item":"journal","prices":[87.0,45.0,63.0,12.0,78.0]}
{"_id":{"$oid":"58f63a5099b37d9c930f9f3c"},"item":"notebook","prices":[123.0,456.0,789.0]}

The Fullarray option can be used here to generate enough columns to see all the prices of the document prices array.

create table tar
engine=connect table_type=MONGO
colist='{"projection":{"_id":0}}'
option_list='level=1,Fullarray=YES';

The table has been created as:

CREATE TABLE `tar` (
  `item` char(8) NOT NULL,
  `prices_0` double(12,6) NOT NULL `FIELD_FORMAT`='prices.0',
  `prices_1` double(12,6) NOT NULL `FIELD_FORMAT`='prices.1',
  `prices_2` double(12,6) NOT NULL `FIELD_FORMAT`='prices.2',
  `prices_3` double(12,6) DEFAULT NULL `FIELD_FORMAT`='prices.3',
  `prices_4` double(12,6) DEFAULT NULL `FIELD_FORMAT`='prices.4'
) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`='MONGO' `COLIST`='{"projection":{"_id":0}}' `OPTION_LIST`='level=1,Fullarray=YES';

And is displayed as:

itemprices_0prices_1prices_2prices_3prices_4
journal87.0045.0063.0012.0078.00
notebook123.00456.00789.00NULLNULL

Data Modifying Operations

All modifying operations are supported. However, inserting into arrays must be done in a specific way. Like with the Fullarray option, we must have enough columns to specify the array values. For instance, we can create a new table by:

create table testin (
n int not null,
m char(12) not null,
surname char(16) not null field_format='person.name.first',
name char(16) not null field_format='person.name.last',
age int(3) not null field_format='person.age',
price_1 double(8,2) field_format='d.0',
price_2 double(8,2) field_format='d.1',
price_3 double(8,2) field_format='d.2')
engine=connect table_type=MONGO tabname='tin'
connection='mongodb://localhost:27017';

Now it is possible to populate it by:

insert into testin values
(1789, 'Welcome', 'Olivier','Bertrand',56, 3.14, 2.36, 8.45),
(1515, 'Hello', 'John','Smith',32, 65.17, 98.12, NULL),
(2014, 'Coucou', 'Foo','Bar',20, -1.0, 74, 81356);

The result will be:

nmsurnamenameageprice_1price_2price_3
1789WelcomeOlivierBertrand563,142,368,45
1515HelloJohnSmith3265,1798,12NULL
2014CoucouFooBar20-17481356

To look how the array is generated, let us create another table:

create table tintin (
n int not null,
name char(16) not null field_format='person.name.first',
prices varchar(255) field_format='d')
engine=connect table_type=MONGO tabname='testin';

This table is displayed as:

nnameprices
1789Olivier{"0":3.14,"1":2.36,"2":8.45}
1515John{"0":65.17,"1":98.12}
2014Foo{"0":-1.0,"1":74.0,"2":81356.0}

As a matter of fact, MongoDB has created a “pseudo” array, an object whose keys are “0”, “1”, “2”. Indeed, this is how MongoDB often interpret the notion of array; and it behaves like an array.

There are no such problems with UPDATE and DELETE that can be used without restriction.

Status of MONGO Table Type

This table type is still under development. It has significant advantages over the JSON type to access MongoDB collections. Firstly, the access being direct, tables are always up to date whether the collection has been modified by another application. Performance wise, it is much faster than JSON, because most processing is done by MongoDB on BSON, its internal representation of JSON data, which is designed to optimize all operations.

Current Restrictions

SRCDEF does not apply to MONGO tables.

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.