Soporte ANSI JOIN en N1QL

Visión general

Se añade soporte ANSI JOIN en N1QL a la versión 5.5 de Couchbase. En versiones anteriores de Couchbase, el soporte de join estaba limitado a lookup join e index join, que funcionan muy bien cuando la clave de documento de un lado del join puede ser producida por el otro lado del join - es decir, join en una relación padre-hijo o hijo-padre a través de una clave de documento.

Este método se queda corto cuando la unión se realiza sobre campos arbitrarios o expresiones de campos, o cuando se requieren múltiples condiciones de unión. ANSI JOIN es una sintaxis de unión estandarizada ampliamente utilizada en bases de datos relacionales. ANSI JOIN es mucho más flexible que lookup join e index join, ya que permite realizar join sobre expresiones arbitrarias en cualquier campo de un documento. Esto hace que las operaciones de unión sean mucho más sencillas y potentes.

Sintaxis ANSI JOIN:

lhs-expression [ join-type ] JOIN rhs-keyspace ON [ join-condition ]

El lado izquierdo de la unión, expresión lhs, puede ser un espacio de claves, una expresión N1QL, una subconsulta o una unión anterior. El lado derecho de la unión, espacio-clave-rhs, debe ser un espacio-clave. La cláusula ON especifica la condición de la unión, que puede ser cualquier expresión arbitraria, aunque debe contener predicados que permitan una búsqueda de índices en el espacio clave del lado derecho. El tipo de unión puede ser INNER, LEFT OUTER, RIGHT OUTER. Las palabras clave INNER y OUTER son opcionales, por lo que JOIN es lo mismo que INNER JOIN, y LEFT JOIN es lo mismo que LEFT OUTER JOIN. En bases de datos relacionales join-type también puede ser FULL OUTER o CROSS, aunque FULL OUTER JOIN y CROSS JOIN no son soportados actualmente en N1QL.

Detalles de la compatibilidad con ANSI JOIN

Usaremos ejemplos para mostrarle nuevas formas de ejecutar consultas usando la sintaxis ANSI JOIN, y cómo transformar sus consultas join existentes en N1QL de la sintaxis lookup join o index join a la nueva sintaxis ANSI JOIN. Cabe señalar que lookup join e index join continuarán siendo soportados en N1QL por compatibilidad con versiones anteriores, sin embargo no se puede mezclar lookup join o index join con la nueva sintaxis ANSI JOIN en el mismo bloque de consulta, por lo que se recomienda a los clientes migrar a la nueva sintaxis ANSI JOIN.

Para seguirlo, instalar la muestra de viaje cubo de muestra.

Ejemplo 1: ANSI JOIN con una condición de unión arbitraria

La condición de unión (cláusula ON) para ANSI JOIN puede ser cualquier expresión que incluya cualquier campo de los documentos que se van a unir. Por ejemplo:

Índice requerido:

Índice opcional:

Consulta:

En esta consulta estamos uniendo un campo ("faa") del documento del aeropuerto con un campo ("sourceairport") del documento de la ruta (véase la cláusula ON de la unión). Este tipo de unión no es posible con lookup join o index join en N1QL, ya que ambos requieren la unión en la clave del documento solamente.

ANSI JOIN requiere un índice apropiado en el espacio clave del lado derecho ("Índice obligatorio" más arriba). También puede crear otros índices (por ejemplo, el "Índice opcional" anterior) para acelerar la consulta. Sin el índice opcional se utilizará un escaneo primario y la consulta seguirá funcionando, sin embargo sin el índice requerido la consulta no funcionará y devolverá un error.

Mirando las explicaciones:

Verá que se utiliza un operador NestedLoopJoin para realizar la unión, y debajo se utiliza un operador IndexScan3 para acceder al espacio clave de la derecha, "route". Los spans para el escaneo del índice se ven así:

La exploración de índices para el espacio clave del lado derecho ("ruta") utiliza un campo ("faa") del espacio clave del lado izquierdo ("aeropuerto") como clave de búsqueda. Para cada documento del espacio de claves del lado exterior "aeropuerto", el operador NestedLoopJoin realiza una exploración de índice en el espacio de claves del lado interior "ruta" para encontrar documentos coincidentes, y produce resultados de unión. La unión se realiza en forma de bucle anidado, en el que el bucle externo produce el documento del espacio de claves del lado externo y un bucle interno anidado busca el documento del lado interno que coincida con el documento actual del lado externo.

La información explicada también puede visualizarse gráficamente en el Query Workbench, haciendo clic en el botón Explicar seguido del botón Plan:

En este ejemplo, el escaneo de índice en el espacio clave de la derecha es un escaneo de índice cubierto. En caso de que el escaneo de índice no esté cubierto, un operador fetch seguirá al operador de escaneo de índice para obtener el documento.

Debe tenerse en cuenta que la unión de bucles anidados requiere un índice secundario apropiado en el espacio de claves del lado derecho de ANSI JOIN. El índice primario no se tiene en cuenta a estos efectos. Si no se puede encontrar un secundario apropiado, se devolverá un error para la consulta.

Además, habrá observado que el filtro route.type = "ruta" también aparece en la cláusula ON. La cláusula ON se diferencia de la cláusula WHERE en que la cláusula ON se evalúa como parte de la unión, mientras que la cláusula WHERE se evalúa una vez realizadas todas las uniones. Esta distinción es importante, especialmente para las uniones externas. Por lo tanto, se recomienda incluir filtros en el espacio de claves de la derecha para una unión en la cláusula ON también, además de cualquier filtro de unión.

Ejemplo 2: ANSI JOIN con múltiples condiciones de unión

Mientras que lookup join e index join sólo unen en una única condición de unión (igualdad de la clave del documento), la cláusula ON de ANSI JOIN puede contener múltiples condiciones de unión.

Índice requerido:

Índice opcional:

Consulta:

Si nos fijamos en la explicación, los intervalos de índice para el índice ("landmark_city_country") del espacio clave de la derecha ("landmark") son:

Por lo tanto, múltiples predicados de unión pueden generar potencialmente múltiples claves de búsqueda de índice para la exploración de índice del lado interno de una unión de bucle anidado.

Ejemplo 3: ANSI JOIN con expresiones join complejas

La condición de unión en la cláusula ON puede ser una expresión de unión compleja. Por ejemplo, el campo "airlineid" del documento "route" corresponde a la clave del documento "airline", pero también puede construirse concatenando "airline_" con el campo "id" del documento "airline".

Índice requerido:

Índice opcional:

Consulta:

La explicación contiene los siguientes espacios de índice para el espacio clave de la derecha("ruta"):

La expresión se evaluará en tiempo de ejecución para generar las claves de búsqueda para la exploración de índices en el lado interno de la unión de bucles anidados.

Ejemplo 4: ANSI JOIN con cláusula IN

No es necesario que la condición de unión sea un predicado de igualdad. Se puede utilizar una cláusula IN como condición de unión.

Índice requerido:

Índice opcional:

Consulta:

La explicación contiene los siguientes índices para el espacio clave de la derecha ("aeropuerto"):

 

Ejemplo 5: ANSI JOIN con cláusula OR

De forma similar a la cláusula IN, la condición de unión para un ANSI JOIN también puede contener una cláusula OR. Las diferentes ramas de la cláusula OR pueden hacer referencia a diferentes campos del espacio de claves del lado derecho, siempre que existan los índices adecuados.

Índice requerido (índice route_airports igual que en el ejemplo 1):

Índice opcional (igual que en el ejemplo 1):

Consulta:

La explicación muestra un UnionScan que se utiliza bajo NestedLoopJoin, para manejar la cláusula OR:

 

Ejemplo 6: ANSI JOIN con sugerencias

En las uniones por búsqueda y por índice, las sugerencias sólo pueden especificarse en el espacio de claves del lado izquierdo de la unión. Para ANSI JOIN, también se pueden especificar sugerencias en el espacio clave del lado derecho. Utilizando la misma consulta que en el ejemplo 1 (añadiendo la sugerencia USE INDEX):

La sugerencia USE INDEX limita el número de índices que el planificador debe tener en cuenta para realizar la unión.

También se pueden especificar sugerencias en el espacio de claves de la izquierda de ANSI JOIN.

 

Ejemplo 7: ANSI LEFT OUTER JOIN

Hasta ahora hemos visto las uniones internas. También puede realizar un LEFT OUTER JOIN simplemente incluyendo las palabras clave LEFT o LEFT OUTER delante de la palabra clave JOIN en la especificación de la unión.

Índice requerido (igual que en el ejemplo 1):

Índice opcional (igual que en el ejemplo 1):

Consulta:

El conjunto de resultados de esta consulta contiene todos los resultados unidos, así como cualquier documento del lado izquierdo ("aeropuerto") que no se una con el documento del lado derecho ("ruta"), de acuerdo con la semántica de LEFT OUTER JOIN. Así, encontrará resultados que sólo contengan airport.airportname pero no route.airlineid (que falta). También puede seleccionar sólo el documento del lado izquierdo ("airport") que no se une con el documento del lado derecho ("route") añadiendo un predicado IS MISSING en el espacio clave del lado derecho ("route"):

 

Ejemplo 8: ANSI RIGHT OUTER JOIN

ANSI RIGHT OUTER JOIN es similar a ANSI LEFT OUTER JOIN, salvo que conservamos el documento del lado derecho si no se produce ninguna unión. Podemos modificar la consulta del ejemplo 7 cambiando los espacios de claves izquierdo y derecho y sustituyendo la palabra clave LEFT por RIGHT:

Observe que, aunque hemos cambiado el aeropuerto y la ruta en la especificación del join, el filtro de la ruta (ahora el espacio clave del lado izquierdo) sigue apareciendo en la cláusula ON del join, ya que la ruta sigue estando en el lado subordinado en este join externo.

RIGHT OUTER JOIN se convierte internamente en LEFT OUTER JOIN.

Si una consulta contiene múltiples uniones, una RIGHT OUTER JOIN sólo puede ser la primera unión especificada. Dado que N1QL sólo soporta uniones lineales, es decir, el lado derecho de cada unión debe ser un único espacio clave, si un RIGHT OUTER JOIN no es la primera unión especificada, entonces después de convertir a LEFT OUTER JOIN, el lado derecho de la unión ahora contiene múltiples espacios clave, lo cual no es soportado. Si especifica RIGHT OUTER JOIN en cualquier posición que no sea la primera unión, se devolverá un error de sintaxis.

Ejemplo 9: ANSI JOIN utilizando Hash Join

N1QL soporta dos métodos de unión para ANSI JOIN. El método de unión por defecto para un ANSI JOIN es nested-loop join. La alternativa es hash join. La unión hash utiliza una tabla hash para emparejar documentos de ambos lados de la unión. Hash join tiene un lado de construcción y un lado de sondeo, donde cada documento del lado de construcción se inserta en una tabla hash basada en los valores de la expresión equi-join del lado de construcción; posteriormente, cada documento del lado de sondeo se busca en la tabla hash basada en los valores de la expresión equi-join del lado de sondeo. Si se encuentra una coincidencia, se realiza la operación de unión.

En comparación con la unión de bucles anidados, la unión hash puede ser más eficiente cuando la unión es grande, por ejemplo, cuando hay decenas de miles de documentos o más en el lado izquierdo de la unión (después de aplicar filtros). Si se utiliza la unión de bucle anidado, por cada documento del lado izquierdo se debe realizar un escaneo de índice en el índice del lado derecho. A medida que aumenta el número de documentos del lado izquierdo, la unión en bucle anidado pierde eficacia.

En el caso de la unión por hash, el lado más pequeño de la unión debe utilizarse para construir la tabla hash, y el lado más grande de la unión debe utilizarse para sondear la tabla hash. Hay que tener en cuenta que el hash join requiere más memoria que el nested-loop join, ya que se necesita una tabla hash en memoria. La cantidad de memoria necesaria es proporcional al número de documentos del lado de la construcción, así como al tamaño medio de cada documento.

Hash join es compatible con sólo edición enterprise. Para utilizar la unión hash, debe especificarse una sugerencia USE HASH en el espacio de claves del lado derecho de ANSI JOIN. Utilizando la misma consulta que en el ejemplo 1:

La sugerencia USE HASH(build) indica al planificador N1QL que realice una unión hash para el ANSI JOIN especificado, y el espacio de claves del lado derecho ("route") se utiliza en el lado de construcción de la unión hash. Mirando la explicación, hay un operador HashJoin:

El operador hijo ("~hijo") de un operador HashJoin es siempre el lado de construcción de la unión hash. Para esta consulta, se trata de una exploración de índice en el espacio de claves "route" del lado derecho.

Observe que para acceder al documento "ruta" ya no podemos utilizar la información del espacio de claves del lado izquierdo ("aeropuerto") como clave de búsqueda del índice (mire la información sobre "spans" en la sección de explicación anterior). A diferencia de la unión de bucles anidados, la exploración del índice en "ruta" ya no está vinculada a un documento individual del lado izquierdo y, por lo tanto, no se puede utilizar ningún valor del documento "aeropuerto" como clave de búsqueda para la exploración del índice en "ruta".

La sugerencia USE HASH(build) utilizada en la consulta anterior indica al planificador que utilice el espacio de claves del lado derecho como lado de construcción de la unión hash. También puede especificar la sugerencia USE HASH(probe) para indicar al planificador que utilice el espacio de claves del lado derecho como el lado de sondeo de la unión hash.

Si miras la explicación, encontrarás el operador HashJoin:

El operador hijo ("~hijo") para HashJoin es un escaneo de índice de intersección en el espacio clave del lado izquierdo del ANSI JOIN, "aeropuerto", seguido de un operador fetch.

La sugerencia USE HASH sólo se puede especificar en el espacio de claves del lado derecho en un ANSI JOIN. Por lo tanto, dependiendo de si desea que el espacio de claves del lado derecho sea el lado de construcción o el lado de sondeo de una unión hash, se debe especificar una sugerencia USE HASH(build) o USE HASH(probe) en el espacio de claves del lado derecho.

La unión hash sólo se tiene en cuenta cuando se especifica la sugerencia USE HASH(build) o USE HASH(probe). La unión hash requiere predicados de unión de igualdad para funcionar. La unión en bucle anidado requiere un índice secundario apropiado en el espacio de claves del lado derecho, mientras que la unión hash no (la exploración del índice primario es una opción para la unión hash). Sin embargo, la unión por hash requiere más memoria que la unión por bucle anidado, ya que se necesita una tabla hash en memoria para que la unión por hash funcione. Además, la unión por hash se considera una operación "bloqueante", lo que significa que el motor de consulta debe terminar de construir la tabla hash antes de poder producir el primer resultado de la unión, por lo que para las consultas que sólo necesitan los primeros resultados rápidamente (por ejemplo, con una cláusula LIMIT) la unión por hash puede no ser la mejor opción.

Si se especifica una sugerencia USE HASH, pero no se puede generar correctamente una unión hash (por ejemplo, falta de predicados de unión de igualdad), se considerará una unión de bucle anidado.

Ejemplo 10: ANSI JOIN con múltiples sugerencias

Ahora puede especificar varias sugerencias para un espacio clave en el lado derecho de un ANSI JOIN. Por ejemplo, la sugerencia USE HASH puede utilizarse junto con la sugerencia USE INDEX.

Tenga en cuenta que cuando se utilizan varias sugerencias a la vez, sólo es necesario especificar la palabra clave "USE" una vez, como en el ejemplo anterior.

La sugerencia USE HASH también puede combinarse con la sugerencia USE KEYS.

Ejemplo 11: ANSI JOIN con uniones múltiples

ANSI JOIN se pueden encadenar. Por ejemplo:

Índices necesarios (el índice route_airports es el mismo que en el ejemplo 1):

Índice opcional (igual que en el ejemplo 1):

Consulta:

Dado que no se ha especificado ninguna sugerencia USE HASH en la consulta, la explicación debería mostrar dos operadores NestedLoopJoin.

Puede mezclar uniones hash con uniones de bucles anidados añadiendo la sugerencia USE HASH a cualquiera de las uniones de una cadena de ANSI JOINs.

o

La explicación visual de la última consulta es la siguiente:

Como ya se ha mencionado, N1QL sólo admite uniones lineales, es decir, el lado derecho de cada unión debe ser un espacio de claves.

Ejemplo 12: ANSI JOIN con matrices del lado derecho

Aunque ANSI JOIN viene del estándar SQL, como Couchbase N1QL maneja documentos JSON y los arrays son un aspecto importante de JSON, extendimos el soporte de ANSI JOIN a los arrays también.

Para ejemplos en el manejo de arreglos por favor cree un cubo "default" e inserte los siguientes documentos:

A continuación, cree los siguientes índices:

Cuando el predicado de unión implica una matriz en el lado derecho de ANSI JOIN, es necesario crear un índice de matriz en el espacio de claves del lado derecho.

Consulta:

Observe que parte de la condición de unión es una cláusula ANY que especifica que el campo del lado izquierdo b1.c12 puede coincidir con cualquier elemento de la matriz del lado derecho b2.a21. Para que esta unión funcione correctamente, necesitamos un índice de matriz en b2.a21, por ejemplo, el índice default_ix_right creado anteriormente.

El plan explain muestra un NestedLoopJoin, siendo el operador hijo un escaneo distinto en el índice del array default_ix_right.

 

Ejemplo 13: ANSI JOIN con matrices del lado izquierdo

Si ANSI JOIN incluye una matriz en el lado izquierdo, existen dos opciones para realizar la unión.

Opción 1: utilizar UNNEST

Utilice la cláusula UNNEST para aplanar primero la matriz de la izquierda antes de realizar la unión.

Después del UNNEST el array se convierte en campos individuales, y la unión subsiguiente es como un ANSI JOIN "normal" con campos de ambos lados.

Opción 2: utilizar la cláusula IN

Alternativamente, utilice la cláusula IN como condición de unión.

La cláusula IN se cumple cuando cualquier elemento de la matriz del espacio clave del lado izquierdo ("b1.a11") coincide con el campo del lado derecho ("b2.c21").

Tenga en cuenta que hay una diferencia semántica entre las dos opciones. Cuando hay duplicados en la matriz, a la opción UNNEST no le importan los duplicados y aplanará los documentos del lado izquierdo a tantos documentos como elementos haya en la matriz, por lo que puede producir resultados duplicados; la opción IN-clause no producirá resultados duplicados si hay elementos duplicados en la matriz. Además, cuando se realiza un LEFT OUTER JOIN, puede haber un número diferente de documentos conservados debido al aplanamiento de la matriz con la opción UNNEST. Por lo tanto, se aconseja al usuario que elija la opción que refleje la semántica necesaria para la consulta.

Ejemplo 14: ANSI JOIN con matrices en ambos lados

Aunque es poco común, es posible realizar un ANSI JOIN cuando ambos lados de la unión son matrices. En estos casos, puede utilizar una combinación de las técnicas descritas anteriormente. Utilice el índice de array para manejar el array del lado derecho, y utilice la opción UNNEST o la opción IN-clause para manejar el array del lado izquierdo.

Opción 1: utilizar la cláusula UNNEST

Opción 2: utilizar la cláusula IN

Una vez más, las dos opciones no son semánticamente idénticas y pueden dar resultados diferentes. Elija la opción que refleje la semántica deseada.

Ejemplo 15: migración lookup join

N1QL continuará soportando lookup join e index join por compatibilidad con versiones anteriores, sin embargo, no puede mezclar ANSI JOIN con lookup join o index join en la misma consulta. Puede convertir sus consultas existentes usando lookup join e index join a la sintaxis ANSI JOIN. Este ejemplo muestra cómo convertir una consulta lookup join a la sintaxis ANSI JOIN.

Cree el siguiente índice para acelerar la consulta (igual que en el ejemplo 1):

Se trata de una consulta que utiliza la sintaxis lookup join (observe la cláusula ON KEYS):

En lookup join, el lado izquierdo de la unión ("route") debe producir claves de documento para el lado derecho de la unión ("airline"), lo que se consigue mediante la cláusula ON KEYS. La condición de unión (implícita en la sintaxis) es route.airlineid = meta(aerolinea).idpor lo que la misma consulta puede especificarse utilizando la sintaxis ANSI JOIN:

En este ejemplo, la cláusula ON KEYS contiene una única clave de documento. Es posible que la cláusula ON KEYS contenga una matriz de claves de documento, en cuyo caso la cláusula ON convertida tendrá la forma de una cláusula IN en lugar de una cláusula de igualdad. Supongamos que cada documento de ruta tiene un array de claves de documento para aerolínea, entonces la cláusula ON KEYS original:  

se puede convertir en:

 

Ejemplo 16: migración de index join

Este ejemplo muestra cómo convertir una unión de índice en sintaxis ANSI JOIN.

Índice requerido (igual que en el ejemplo 3):

Índice opcional (igual que en el ejemplo 3):

Consulta utilizando la sintaxis de unión de índices (observe la cláusula ON KEY ... FOR ...):

En la unión por índices, la clave de documento del lado izquierdo ("compañía aérea") se utiliza para sondear un índice en una expresión ("route.airlineid" que aparece en la cláusula ON KEY) del lado derecho ("route") que corresponde a la clave de documento del lado izquierdo ("compañía aérea" que aparece en la cláusula FOR). La condición de unión (implícita en la sintaxis) es route.airlineid = meta(aerolinea).idpor lo que la misma consulta puede especificarse utilizando la sintaxis ANSI JOIN:

 

Ejemplo 17: ANSI NEST

Couchbase N1QL soporta la operación NEST. Anteriormente NEST se podía hacer usando lookup nest o index nest, similar a lookup join e index join, respectivamente. Con el soporte ANSI JOIN, la operación NEST también puede hacerse usando una sintaxis similar, es decir, usando la cláusula ON en lugar de las cláusulas ON KEYS (lookup nest) o ON KEY ... FOR ... (index nest). Esta nueva variante se denomina ANSI NEST.

Índice requerido (índice route_airports igual que en el ejemplo 1, índice route_airline_distance igual que en el ejemplo 4):

Índice opcional:

Consulta:

Como puede ver, la sintaxis de ANSI NEST es muy similar a la de ANSI JOIN. Sin embargo, hay una propiedad peculiar para nest. Por definición, la operación nest crea una matriz de todos los documentos del lado derecho coincidentes para cada documento del lado izquierdo, lo que significa que la referencia al espacio clave del lado derecho, "ruta" en esta consulta, tiene un significado diferente dependiendo de dónde se encuentre la referencia. La cláusula ON se evalúa como parte de la operación NEST, por lo que las referencias a "route" hacen referencia a un único documento. Por el contrario, las referencias en la cláusula de proyección, o la cláusula WHERE, se evalúan después de la operación NEST, y por lo tanto las referencias a "route" significan el array anidado, por lo que debe tratarse como un array. Observe que la cláusula de proyección de la consulta anterior tiene una construcción ARRAY con una cláusula FOR para acceder a cada documento individual dentro del array (es decir, la referencia a "route" está ahora en un contexto de array).

Resumen

ANSI JOIN proporciona mucha más flexibilidad en las operaciones de unión en Couchbase N1QL, en comparación con el soporte previo de lookup join e index join, los cuales requieren la unión en la clave del documento solamente. Los ejemplos anteriores muestran varias formas de usar ANSI JOIN en consultas. Ya que ANSI JOIN es ampliamente usado en el mundo relacional, el soporte para ANSI JOIN en Couchbase N1QL debería hacer mucho más fácil migrar aplicaciones de una base de datos relacional a Couchbase N1QL.