Comparando ClickHouse Cloud y BigQuery

En qué se diferencia BigQuery de ClickHouse Cloud

​

Organización de los recursos

La manera en que se organizan los recursos en ClickHouse Cloud es similar a la jerarquía de recursos de BigQuery. A continuación, describimos las diferencias específicas según el siguiente diagrama, que muestra la jerarquía de recursos de ClickHouse Cloud:

​

Organizaciones

Al igual que en BigQuery, las organizaciones son los nodos raíz de la jerarquía de recursos de ClickHouse Cloud. El primer usuario que configures en tu cuenta de ClickHouse Cloud se asigna automáticamente a una organización que le pertenece. Ese usuario puede invitar a otros usuarios a la organización.

​

Proyectos de BigQuery vs. servicios de ClickHouse Cloud

Dentro de las organizaciones, puede crear servicios aproximadamente equivalentes a los proyectos de BigQuery, ya que los datos almacenados en ClickHouse Cloud están asociados a un servicio. Hay varios tipos de servicio disponibles en ClickHouse Cloud. Cada servicio de ClickHouse Cloud se despliega en una región específica e incluye:

1. Un grupo de nodos de cómputo (actualmente, 2 nodos para un servicio del nivel Development y 3 para un servicio del nivel Production). Para estos nodos, ClickHouse Cloud admite el escalado vertical y horizontal, tanto manual como automático.
2. Una carpeta de almacenamiento de objetos donde el servicio almacena todos los datos.
3. Un endpoint (o varios endpoints creados desde la consola de ClickHouse Cloud) - una URL del servicio que usa para conectarse a él (por ejemplo, https://dv2fzne24g.us-east-1.aws.clickhouse.cloud:8443)

​

Datasets de BigQuery vs bases de datos de ClickHouse Cloud

ClickHouse agrupa lógicamente las tablas en bases de datos. Al igual que los datasets de BigQuery, las bases de datos de ClickHouse son contenedores lógicos que organizan y controlan el acceso a los datos de las tablas.

​

Carpetas de BigQuery

Actualmente, ClickHouse Cloud no tiene ningún concepto equivalente a las carpetas de BigQuery.

​

Reservas de slots y cuotas de BigQuery

Al igual que con las reservas de slots de BigQuery, puedes configurar el escalado automático vertical y horizontal en ClickHouse Cloud. En el caso del escalado automático vertical, puedes establecer el tamaño mínimo y máximo de la memoria y de los núcleos de CPU de los nodos de cómputo de un servicio. El servicio se escalará según sea necesario dentro de esos límites. Estos ajustes también están disponibles durante el flujo inicial de creación del servicio. Cada nodo de cómputo del servicio tiene el mismo tamaño. Puedes cambiar el número de nodos de cómputo de un servicio con el escalado horizontal.

Además, al igual que las cuotas de BigQuery, ClickHouse Cloud ofrece control de concurrencia, límites de uso de memoria y programación de E/S, lo que te permite aislar consultas en clases de carga de trabajo. Al establecer límites sobre recursos compartidos (núcleos de CPU, DRAM y E/S de disco y de red) para clases de carga de trabajo específicas, se garantiza que estas consultas no afecten a otras consultas críticas para el negocio. El control de concurrencia evita la sobresuscripción de hilos en escenarios con un gran número de consultas concurrentes.

ClickHouse realiza un seguimiento del tamaño en bytes de las asignaciones de memoria a nivel de servidor, usuario y consulta, lo que permite aplicar límites flexibles de uso de memoria. El overcommit de memoria permite que las consultas utilicen memoria libre adicional más allá de la memoria garantizada, al tiempo que mantiene los límites de memoria para otras consultas. Además, puede limitarse el uso de memoria para las cláusulas de agregación, ordenación y join, lo que permite recurrir a algoritmos externos cuando se supera el límite de memoria.

Por último, la programación de E/S te permite restringir los accesos a disco locales y remotos para las clases de carga de trabajo en función del ancho de banda máximo, las solicitudes en curso y la política.

​

Permisos

ClickHouse Cloud controla el acceso de los usuarios en dos ámbitos: mediante la consola de ClickHouse Cloud y mediante la base de datos. El acceso a la consola se gestiona a través de la interfaz de usuario de clickhouse.cloud. El acceso a la base de datos se gestiona mediante cuentas de usuario de base de datos y roles. Además, a los usuarios de la consola se les pueden conceder roles dentro de la base de datos para que puedan interactuar con ella a través de nuestra consola SQL.

​

Tipos de datos

ClickHouse ofrece una mayor granularidad en la precisión de los valores numéricos. Por ejemplo, BigQuery ofrece los tipos numéricos INT64, NUMERIC, BIGNUMERIC and FLOAT64. En contraste, ClickHouse ofrece varios niveles de precisión para decimales, flotantes y enteros. Con estos tipos de datos, puede optimizar el almacenamiento y la sobrecarga de memoria, lo que se traduce en consultas más rápidas y un menor consumo de recursos. A continuación, mostramos el tipo de ClickHouse equivalente para cada tipo de BigQuery:

BigQuery	ClickHouse
ARRAY	Array(t)
NUMERIC	Decimal(P, S), Decimal32(S), Decimal64(S), Decimal128(S)
BIG NUMERIC	Decimal256(S)
BOOL	Bool
BYTES	FixedString
DATE	Date32 (con un rango más limitado)
DATETIME	DateTime, DateTime64 (rango más limitado, mayor precisión)
FLOAT64	Float64
GEOGRAPHY	Tipos de datos geográficos
INT64	UInt8, UInt16, UInt32, UInt64, UInt128, UInt256, Int8, Int16, Int32, Int64, Int128, Int256
INTERVAL	N/D: compatible como expresión o mediante funciones
JSON	JSON
STRING	String (bytes)
STRUCT	Tuple, Nested
TIME	DateTime64
TIMESTAMP	DateTime64

Cuando se le presenten varias opciones de tipos de ClickHouse, tenga en cuenta el rango real de los datos y elija el tipo más pequeño que cumpla los requisitos. Además, considere utilizar codecs adecuados para lograr una compresión adicional.

​

Técnicas de aceleración de consultas

​

Claves primarias y externas e índice primario

En BigQuery, una tabla puede tener restricciones de clave primaria y clave externa. Normalmente, las claves primarias y externas se usan en bases de datos relacionales para garantizar la integridad de los datos. El valor de una clave primaria suele ser único para cada fila y no es NULL. Cada valor de clave externa de una fila debe estar presente en la columna de clave primaria de la tabla referenciada o ser NULL. En BigQuery, estas restricciones no se aplican, pero el optimizador de consultas puede usar esta información para optimizar mejor las consultas.

En ClickHouse, una tabla también puede tener una clave primaria. Al igual que BigQuery, ClickHouse no aplica unicidad a los valores de la columna de clave primaria de una tabla. A diferencia de BigQuery, los datos de una tabla se almacenan en disco ordenados por las columnas de la clave primaria. El optimizador de consultas aprovecha este orden para evitar tener que reordenar los datos, minimizar el uso de memoria en los joins y permitir la finalización anticipada en las cláusulas LIMIT. A diferencia de BigQuery, ClickHouse crea automáticamente un índice primario (disperso) basado en los valores de las columnas de la clave primaria. Este índice se usa para acelerar todas las consultas que contienen filtros sobre las columnas de la clave primaria. Actualmente, ClickHouse no admite restricciones de clave externa.

​

Índices secundarios (Solo disponibles en ClickHouse)

Además del índice primario creado a partir de los valores de las columnas de clave primaria de una tabla, ClickHouse permite crear índices secundarios en columnas distintas de las que forman la clave primaria. ClickHouse ofrece varios tipos de índices secundarios, cada uno adecuado para diferentes tipos de consultas:

• Índice de filtro Bloom:
  • Se utiliza para acelerar consultas con condiciones de igualdad (p. ej., =, IN).
  • Usa estructuras de datos probabilísticas para determinar si un valor existe en un bloque de datos.
• Índice de filtro Bloom por tokens:
  • Similar a un índice de filtro Bloom, pero se utiliza para cadenas tokenizadas y resulta adecuado para consultas de búsqueda de texto completo.
• Índice min-max:
  • Mantiene los valores mínimo y máximo de una columna para cada parte de datos.
  • Ayuda a omitir la lectura de partes de datos que quedan fuera del rango especificado.

​

Índices de búsqueda

Al igual que los índices de búsqueda de BigQuery, se pueden crear índices de texto completo para tablas de ClickHouse en columnas con valores de tipo cadena.

​

Índices vectoriales

BigQuery introdujo recientemente los índices vectoriales como una función Pre-GA. Del mismo modo, ClickHouse ofrece soporte experimental para índices que aceleran los casos de uso de búsqueda vectorial.

​

Particionamiento

Al igual que BigQuery, ClickHouse utiliza el particionamiento de tablas para mejorar el rendimiento y facilitar la gestión de tablas grandes, dividiéndolas en partes más pequeñas y manejables llamadas particiones. Describimos en detalle el particionamiento en ClickHouse aquí.

​

Agrupación

Con la agrupación, BigQuery ordena automáticamente los datos de la tabla en función de los valores de unas pocas columnas especificadas y los ubica juntos en bloques de tamaño óptimo. La agrupación mejora el rendimiento de las consultas, lo que permite a BigQuery estimar mejor el coste de ejecutar la consulta. Con columnas agrupadas, las consultas también evitan escanear datos innecesarios.

En ClickHouse, los datos se agrupan en disco automáticamente en función de las columnas de la clave primaria de una tabla y se organizan lógicamente en bloques que las consultas pueden localizar o descartar rápidamente mediante la estructura de datos del índice primario.

​

Vistas materializadas

Tanto BigQuery como ClickHouse admiten vistas materializadas: resultados precalculados a partir del resultado de una consulta de transformación sobre una tabla base, lo que mejora el rendimiento y la eficiencia.

​

Consulta de las vistas materializadas

Las vistas materializadas de BigQuery pueden consultarse directamente o el optimizador puede utilizarlas para procesar consultas sobre las tablas base. Si los cambios en las tablas base pueden invalidar la vista materializada, los datos se leen directamente de las tablas base. Si los cambios en las tablas base no invalidan la vista materializada, el resto de los datos se lee de la vista materializada y solo los cambios se leen de las tablas base.

En ClickHouse, las vistas materializadas solo pueden consultarse directamente. Sin embargo, a diferencia de BigQuery (donde las vistas materializadas se actualizan automáticamente en los 5 minutos siguientes a un cambio en las tablas base, pero no más de una vez cada 30 minutos), las vistas materializadas siempre están sincronizadas con la tabla base.

Actualización de vistas materializadas

BigQuery actualiza por completo las vistas materializadas de forma periódica ejecutando la consulta de transformación de la vista sobre la tabla base. Entre una actualización y otra, BigQuery combina los datos de la vista materializada con los datos nuevos de la tabla base para ofrecer resultados de consulta coherentes sin dejar de usar la vista materializada.

En ClickHouse, las vistas materializadas se actualizan de forma incremental. Este mecanismo de actualización incremental ofrece una alta escalabilidad y bajos costes de cómputo: las vistas materializadas con actualización incremental están diseñadas especialmente para escenarios en los que las tablas base contienen miles de millones o billones de filas. En lugar de consultar repetidamente la tabla base, que no deja de crecer, para actualizar la vista materializada, ClickHouse simplemente calcula un resultado parcial a partir de (solo) los valores de las filas recién insertadas en la tabla base. Este resultado parcial se fusiona de forma incremental en segundo plano con el resultado parcial calculado previamente. Esto se traduce en costes de cómputo drásticamente más bajos que actualizar repetidamente la vista materializada a partir de toda la tabla base.

​

Transacciones

A diferencia de ClickHouse, BigQuery admite transacciones de varias sentencias dentro de una sola consulta o en varias consultas cuando se usan sesiones. Una transacción de varias sentencias permite realizar operaciones de modificación de datos, como insertar o eliminar filas en una o más tablas, y confirmar o revertir los cambios de forma atómica. Las transacciones de varias sentencias están en la hoja de ruta de ClickHouse para 2024.

​

Funciones de agregación

En comparación con BigQuery, ClickHouse incorpora muchas más funciones de agregación integradas:

• BigQuery incluye 18 funciones de agregación y 4 funciones de agregación aproximadas.
• ClickHouse tiene más de 150 funciones de agregación predefinidas, además de potentes combinadores de agregación para ampliar el comportamiento de las funciones de agregación predefinidas. Por ejemplo, puedes aplicar estas más de 150 funciones de agregación predefinidas a arrays en lugar de a las filas de una tabla simplemente llamándolas con el sufijo -Array. Con el sufijo -Map, puedes aplicar cualquier función de agregación a maps. Y con el sufijo -ForEach, puedes aplicar cualquier función de agregación a arrays anidados.

​

Fuentes de datos y formatos de archivo

En comparación con BigQuery, ClickHouse admite una cantidad significativamente mayor de formatos de archivo y fuentes de datos:

• ClickHouse ofrece compatibilidad nativa para cargar datos en más de 90 formatos de archivo desde prácticamente cualquier fuente de datos
• BigQuery admite 5 formatos de archivo y 19 fuentes de datos

​

Características del lenguaje SQL

ClickHouse ofrece SQL estándar con muchas extensiones y mejoras que lo hacen más adecuado para tareas analíticas. Por ejemplo, ClickHouse SQL admite funciones lambda y funciones de orden superior, por lo que no es necesario desanidar ni expandir arrays al aplicar transformaciones. Esto supone una gran ventaja frente a otros sistemas, como BigQuery.

​

Arrays

En comparación con las 8 funciones de arrays de BigQuery, ClickHouse dispone de más de 80 funciones de arrays integradas para modelar y resolver de forma elegante y sencilla una amplia variedad de problemas.

Un patrón de diseño habitual en ClickHouse consiste en usar la función de agregación groupArray para transformar (temporalmente) valores de filas concretas de una tabla en un array. Después, este puede procesarse cómodamente con funciones de arrays, y el resultado puede volver a convertirse en filas individuales de la tabla mediante la función arrayJoin.

Como ClickHouse SQL admite funciones lambda de orden superior, muchas operaciones avanzadas sobre arrays pueden realizarse simplemente llamando a una de las funciones de arrays integradas de orden superior, en lugar de convertir temporalmente los arrays de nuevo en tablas, como suele ser necesario en BigQuery, por ejemplo, para el filtrado o la combinación de arrays. En ClickHouse, estas operaciones se reducen simplemente a llamar a las funciones de orden superior arrayFilter y arrayZip, respectivamente.

A continuación, mostramos una correspondencia entre las operaciones de arrays de BigQuery y ClickHouse:

BigQuery	ClickHouse
ARRAY_CONCAT	arrayConcat
ARRAY_LENGTH	length
ARRAY_REVERSE	arrayReverse
ARRAY_TO_STRING	arrayStringConcat
GENERATE_ARRAY	range

Crear un array con un elemento por cada fila de una subconsulta

BigQuery

función ARRAY

SELECT ARRAY
  (SELECT 1 UNION  ALL
   SELECT 2 UNION ALL
   SELECT 3) AS new_array;

/*-----------*
 | new_array |
 +-----------+
 | [1, 2, 3] |
 *-----------*/

ClickHouse

groupArray función de agregación

SELECT groupArray(*) AS new_array
FROM
(
    SELECT 1
    UNION ALL
    SELECT 2
    UNION ALL
    SELECT 3
)

   ┌─new_array─┐
1. │ [1,2,3]   │
   └───────────┘

Convertir un array en un conjunto de filas

BigQuery

operador UNNEST

SELECT *
FROM UNNEST(['foo', 'bar', 'baz', 'qux', 'corge', 'garply', 'waldo', 'fred'])
  AS element
WITH OFFSET AS offset
ORDER BY offset;

/*----------+--------*
 | element  | offset |
 +----------+--------+
 | foo      | 0      |
 | bar      | 1      |
 | baz      | 2      |
 | qux      | 3      |
 | corge    | 4      |
 | garply   | 5      |
 | waldo    | 6      |
 | fred     | 7      |
 *----------+--------*/

ClickHouse

cláusula ARRAY JOIN

WITH ['foo', 'bar', 'baz', 'qux', 'corge', 'garply', 'waldo', 'fred'] AS values
SELECT element, num-1 AS offset
FROM (SELECT values AS element) AS subquery
ARRAY JOIN element, arrayEnumerate(element) AS num;

/*----------+--------*
 | element  | offset |
 +----------+--------+
 | foo      | 0      |
 | bar      | 1      |
 | baz      | 2      |
 | qux      | 3      |
 | corge    | 4      |
 | garply   | 5      |
 | waldo    | 6      |
 | fred     | 7      |
 *----------+--------*/

Devolver un array de fechas

BigQuery

función GENERATE_DATE_ARRAY

SELECT GENERATE_DATE_ARRAY('2016-10-05', '2016-10-08') AS example;

/*--------------------------------------------------*
 | example                                          |
 +--------------------------------------------------+
 | [2016-10-05, 2016-10-06, 2016-10-07, 2016-10-08] |
 *--------------------------------------------------*/

Funciones range + arrayMap

ClickHouse

SELECT arrayMap(x -> (toDate('2016-10-05') + x), range(toUInt32((toDate('2016-10-08') - toDate('2016-10-05')) + 1))) AS example

┌─example───────────────────────────────────────────────┐
1. │ ['2016-10-05','2016-10-06','2016-10-07','2016-10-08'] │
   └───────────────────────────────────────────────────────┘

Devolver un array de timestamps

BigQuery

función GENERATE_TIMESTAMP_ARRAY

SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-05 00:00:00', '2016-10-07 00:00:00',
                                INTERVAL 1 DAY) AS timestamp_array;

/*--------------------------------------------------------------------------*
 | timestamp_array                                                          |
 +--------------------------------------------------------------------------+
 | [2016-10-05 00:00:00+00, 2016-10-06 00:00:00+00, 2016-10-07 00:00:00+00] |
 *--------------------------------------------------------------------------*/

ClickHouse

funciones range + arrayMap

SELECT arrayMap(x -> (toDateTime('2016-10-05 00:00:00') + toIntervalDay(x)), range(dateDiff('day', toDateTime('2016-10-05 00:00:00'), toDateTime('2016-10-07 00:00:00')) + 1)) AS timestamp_array

Query id: b324c11f-655b-479f-9337-f4d34fd02190

   ┌─timestamp_array─────────────────────────────────────────────────────┐
1. │ ['2016-10-05 00:00:00','2016-10-06 00:00:00','2016-10-07 00:00:00'] │
   └─────────────────────────────────────────────────────────────────────┘

Filtrado de arrays

BigQuery

Requiere convertir temporalmente los arrays de vuelta a tablas mediante el operador UNNEST

WITH Sequences AS
  (SELECT [0, 1, 1, 2, 3, 5] AS some_numbers
   UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers
   UNION ALL SELECT [5, 10] AS some_numbers)
SELECT
  ARRAY(SELECT x * 2
        FROM UNNEST(some_numbers) AS x
        WHERE x < 5) AS doubled_less_than_five
FROM Sequences;

/*------------------------*
 | doubled_less_than_five |
 +------------------------+
 | [0, 2, 2, 4, 6]        |
 | [4, 8]                 |
 | []                     |
 *------------------------*/

ClickHouse

función arrayFilter

WITH Sequences AS
    (
        SELECT [0, 1, 1, 2, 3, 5] AS some_numbers
        UNION ALL
        SELECT [2, 4, 8, 16, 32] AS some_numbers
        UNION ALL
        SELECT [5, 10] AS some_numbers
    )
SELECT arrayMap(x -> (x * 2), arrayFilter(x -> (x < 5), some_numbers)) AS doubled_less_than_five
FROM Sequences;

   ┌─doubled_less_than_five─┐
1. │ [0,2,2,4,6]            │
   └────────────────────────┘
   ┌─doubled_less_than_five─┐
2. │ []                     │
   └────────────────────────┘
   ┌─doubled_less_than_five─┐
3. │ [4,8]                  │
   └────────────────────────┘

Emparejamiento de arrays

BigQuery

Requiere convertir temporalmente los arrays nuevamente en tablas mediante el operador UNNEST

WITH
  Combinations AS (
    SELECT
      ['a', 'b'] AS letters,
      [1, 2, 3] AS numbers
  )
SELECT
  ARRAY(
    SELECT AS STRUCT
      letters[SAFE_OFFSET(index)] AS letter,
      numbers[SAFE_OFFSET(index)] AS number
    FROM Combinations
    CROSS JOIN
      UNNEST(
        GENERATE_ARRAY(
          0,
          LEAST(ARRAY_LENGTH(letters), ARRAY_LENGTH(numbers)) - 1)) AS index
    ORDER BY index
  );

/*------------------------------*
 | pairs                        |
 +------------------------------+
 | [{ letter: "a", number: 1 }, |
 |  { letter: "b", number: 2 }] |
 *------------------------------*/

ClickHouse

arrayZip función

WITH Combinations AS
    (
        SELECT
            ['a', 'b'] AS letters,
            [1, 2, 3] AS numbers
    )
SELECT arrayZip(letters, arrayResize(numbers, length(letters))) AS pairs
FROM Combinations;

   ┌─pairs─────────────┐
1. │ [('a',1),('b',2)] │
   └───────────────────┘

Agregación de arrays

BigQuery

Requiere volver a convertir los arrays en tablas mediante el operador UNNEST

WITH Sequences AS
  (SELECT [0, 1, 1, 2, 3, 5] AS some_numbers
   UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers
   UNION ALL SELECT [5, 10] AS some_numbers)
SELECT some_numbers,
  (SELECT SUM(x)
   FROM UNNEST(s.some_numbers) AS x) AS sums
FROM Sequences AS s;

/*--------------------+------*
 | some_numbers       | sums |
 +--------------------+------+
 | [0, 1, 1, 2, 3, 5] | 12   |
 | [2, 4, 8, 16, 32]  | 62   |
 | [5, 10]            | 15   |
 *--------------------+------*/

ClickHouse

arraySum, arrayAvg, … función, o cualquiera de los más de 90 nombres de funciones de agregación existentes, como argumento de la función arrayReduce

WITH Sequences AS
    (
        SELECT [0, 1, 1, 2, 3, 5] AS some_numbers
        UNION ALL
        SELECT [2, 4, 8, 16, 32] AS some_numbers
        UNION ALL
        SELECT [5, 10] AS some_numbers
    )
SELECT
    some_numbers,
    arraySum(some_numbers) AS sums
FROM Sequences;

   ┌─some_numbers──┬─sums─┐
1. │ [0,1,1,2,3,5] │   12 │
   └───────────────┴──────┘
   ┌─some_numbers──┬─sums─┐
2. │ [2,4,8,16,32] │   62 │
   └───────────────┴──────┘
   ┌─some_numbers─┬─sums─┐
3. │ [5,10]       │   15 │
   └──────────────┴──────┘