Nuevo SQL Engine on MongoDB

Introducción

Tras varios meses de pruebas se ha hecho pública la nueva versión del motor SQL para MongoDB.

Este nuevo motor SQL está basado en el proyecto opensource GitHub - vincentrussell/sql-to-mongo-db-query-converter: sql-to-mongo-db-query-converter al que se han añadido mejoras y cubre la parte de queries de lectura (selects).

Este proyecto, está construido puramente en código Java, por lo que es fácilmente extensible y se pueden incluir nuevas funcionalidades o correcciones de bugs, aportando a la comunidad en el mismo repositorio de github.

A diferencia del motor anterior (Quasar) que funcionaba con la versión de mongo 3.4, este motor, funciona con versiones de mongo desde la 4.0 en adelante. Por defecto, en plataforma, se monta sobre la versión más actual hasta el momento, la 4.4.

Otra gran diferencia, es la eficiencia a la hora de traducir las queries haciendo que el coste de transformación sea mínimo. En Quasar, en ciertos casos, había queries que provocaban un coste alto en la traducción.

Migración desde el antiguo SQL Engine (Quasar)

Existen varios cambios respecto al antiguo motor Quasar, de modo que, si se plantea una migración, hay que tener en cuenta que el nuevo motor, funciona manteniendo un SQL más estricto y evitando el uso del framework map-reduce, evitando bloqueos de colecciones y la generación de tablas intermedias, priorizando el rendimiento y reduciendo los posibles errores:

• Los datos tipo string son con ' siempre

• Los joins, se limitan al uso sólo de inner join y left join. No es válida la sintaxis de múltiples orígenes comas select * from ontology1, ontology2,….

• El timestamp desde string cambia de timestamp '' a la función to_timestamp('')

• El $oid desde string cambia de OID '' a la función OID('') o toObjectId('')

• Las proyecciones de array u objetos tipo .* .0 [2] [*] {*} no están soportadas, es posible su implementación con funciones

• No se puede concatenar con ||, hay que usar la función concat()

• No se puede usar el alias o el nombre de la ontologías para hacer las proyecciones ni con *. Serían incorrectas estas queries (en su lugar hay que usar select * from Ontology):

Consideraciones del ObjectID y el _id

A la hora de devolver los datos de tipo objectId de mongoDB, Quasar evitaba devolver el _id

Open

Si se le indicaba explícitamente, quasar lo devuelve en formato string:

Open

En el nuevo motor, por retrocompatibilidad (con un cierto coste debido al parseo de la respuesta), está el parámetro (si no se indica se pone a true por defecto, como es el caso de actualizaciones):

Se puede deshabilitar dentro de las configuraciones de plataforma:

Si se deshabilita (por defecto en nuevas instalaciones) se devuelve en formato de mongoDB:

Si se usa un * en el nuevo motor, se devuelve siempre el _id.

Extensibilidad (Desde 2.2.1-hyperblast)

Existe la posibilidad de ampliar el número de funciones de las que dispone este motor, de modo que desde el controlpanel se pueden incluir nuevas funcionalidades como se puede ver aquí

Extensibilidad del nuevo motor SQL de mongo

Sintaxis

Data Types

Clauses

The following clauses are supported:

The following operators are supported:

The following functions are supported by default (this can be extended with new functions). Those are case insensitive:

Ejemplos de uso

Queries Básicas:

select * from ISO3166_1 devuelve la colección, junto con el _id y el contextData:

select c.ISO3166 from ISO3166_1 as c devuelve sólo la colección:

Where:

Se puede hacer uso de filtros en la clausula where con diferentes operadores:

• Relational: =, >=, <=, <>, IN, NOT IN, LIKE, NOT LIKE

• Boolean: AND, OR, NOT

select c.ISO3166 from ISO3166_1 as c where c.ISO3166.name='Zambia':

select c.ISO3166 from ISO3166_1 as c where c.ISO3166.name='Zambia' and c.ISO3166.language='EN':

select c.ISO3166.name as name from ISO3166_1 as c where c.ISO3166.name like 'S%' and c.ISO3166.language='EN':

select c.ISO3166.name as name from ISO3166_1 as c where c.ISO3166.name in ('Sudan','Suriname') and c.ISO3166.language='EN':

Group/Having:

select c.ISO3166.language as name, count(*) as c from ISO3166_1 as c group by c.ISO3166.language

or

select c.ISO3166.language as name, count(*) as c from ISO3166_1 as c group by name:

select c.ISO3166.language as name, count(*) as c from ISO3166_1 as c group by c.ISO3166.language having count(*) > 250

or

select c.ISO3166.language as name, count(*) as c from ISO3166_1 as c group by name having c > 250:

Limit/offset

select c.ISO3166.name as name, count(*) as c from ISO3166_1 as c where c.ISO3166.name like 'S%' group by name limit 1:

select c.ISO3166.name as name, count(*) as c from ISO3166_1 as c where c.ISO3166.name like 'S%' group by name offset 1:

select c.ISO3166.name as name, count(*) as c from ISO3166_1 as c where c.ISO3166.name like 'S%' group by name limit 3 offset 1

or

select c.ISO3166.name as name, count(*) as c from ISO3166_1 as c where c.ISO3166.name like 'S%' group by name offset 1 limit 3 (retrocompatibility):

Case when else (Desde 2.2.1-hyperblast)

Es posible usar la sintaxis case when default como proyección o en la parte de group by (para hacer agrupaciones condicionales aunque como en cualquier group by tiene que ser un campo que esté en la proyección o el alias del mismo). Es obligatorio usar la clausula default.

select h.Helsinki.population as population, case when h.Helsinki.population between 0 and 50000 then 'low' else 'high' end from HelsinkiPopulation as h

select case when h.Helsinki.population between 0 and 50000 then 'low' else 'high' end as grouprange, count(*) as c from HelsinkiPopulation as h group by cca

Mathematical operators (Desde 2.2.1-hyperblast)

Es posible usar operadores matemáticos en las operaciones SQL

select h.Helsinki.population as pop, h.Helsinki.population+1000 as pop2 from HelsinkiPopulation as h

select toInt(c.Helsinki.year/1000) as m, sum(c.Helsinki.population) as s, sum(c.Helsinki.population_men) as sm, sum(c.Helsinki.population_women) as sw, concat(toString(toInt(10000*sum(c.Helsinki.population_men)/sum(c.Helsinki.population))/100),'%') as percentm, concat(toString(toInt(10000*sum(c.Helsinki.population_women)/sum(c.Helsinki.population))/100),'%') as percentw from HelsinkiPopulation as c group by m

Function NOW()

Esta función de plataforma devuelve la fecha y hora del sistema, se puede usar en cualquier parte de la query y añadirá esta fecha en formato string. Si queremos parsearla a fecha de mongo y operar con la misma podemos hacer un toDate(now())

NOW(“format“,'unitTime', amount)

• “format“: formateo de la fecha, por defecto se usa "yyyy-MM-dd'T'HH: mm: ss'Z '"

• 'unitTime': unidad de tiempo para incrementar o disminuir el número de horas, días, ... los valores posibles de unitTime son: 'year', 'month', 'date', 'hour', 'minute', 'second ',' millisecond '

• "amount": entero positivo o negativo, con la cantidad de unitTime a añadir o resta sobre la fecha del sistema

select Helsinki, now() as nowstr, toDate(now()) as nowdate from HelsinkiPopulation LIMIT 3

Joins/Subqueries

Es posible hacer joins de tipo inner join y left join incluso unirlos con subqueries

select re.countrysrc as countrysrc,re.countrydest as countrydest,re.count, iso.ISO3166.latitude as latitude, iso.ISO3166.longitude as longitude from ( select rx.routesexten.countrysrc As countrysrc, rx.routesexten.countrydest As countrydest, count(re.routesexten.countrysrc) As count from routesexten as rx group by rx.routesexten.countrysrc, rx.routesexten.countrydest order by count desc) As re inner join ISO3166_1 As iso on re.countrydest = iso.ISO3166.name

Union all (Desde 2.2.1-hyperblast)

Con esta clausula puedes añadir datos de otras queries (los datos duplicados no serán eliminados)

select 1 as v from Restaurants limit 1 union all

select 2 as v from Restaurants limit 1 union all

select 3 as v from Restaurants limit 1

Debido a restricciones de mongodb, se limita el uso a “union all” no a “union“. Por lo tanto, si es necesaria una union de datos sin duplicados, estos deberán agruparse (group by) después de hacer la union de los mismos.

Functions (Desde 2.2.1-hyperblast)

Se dispone de varias funciones incluidas en el motor por defecto (estas son ampliables mediante configuración). Se usan en modo case insensitive por lo que pueden escribirse indistintamente en mayúsculas o minúsculas:

Count Distinct clause (Desde 4.2.1-predator)

Con esta clausula puedes realizar la operación de conteo de distintos valores de un grupo (el incluido en el distinct) para otro grupo dado (el que está fuera del distinct)

select count(distinct Restaurant.borough) from Restaurants

select Restaurant.cuisine, count(distinct Restaurant.borough, Restaurant.name) from Restaurants

Por restricciones del lenguaje de consultas de mongodb, no esta permitido la combinación con otros elementos diferentes de columnas.