Controlador ODBC

El controlador ODBC de ClickHouse proporciona una interfaz conforme a los estándares para conectar aplicaciones compatibles con ODBC a ClickHouse. Implementa la API de ODBC y permite a las aplicaciones, las herramientas de BI y los entornos de scripting ejecutar consultas SQL, recuperar resultados e interactuar con ClickHouse mediante mecanismos habituales. El controlador se comunica con el servidor de ClickHouse mediante el protocolo HTTP, que es el protocolo principal compatible con todas las implementaciones de ClickHouse. Esto permite que el controlador funcione de forma coherente en diversos entornos, incluidas las instalaciones locales, los servicios administrados en la nube y los entornos donde solo está disponible el acceso basado en HTTP. El código fuente del controlador está disponible en el repositorio de GitHub de ClickHouse-ODBC.

Para una mayor compatibilidad, recomendamos encarecidamente actualizar el servidor de ClickHouse a la versión 24.11 o posterior.

Este controlador se encuentra en desarrollo activo. Es posible que algunas funcionalidades de ODBC aún no estén completamente implementadas. La versión actual se centra en proporcionar conectividad esencial y las funcionalidades básicas de ODBC, y se prevén funcionalidades adicionales para futuras versiones.Sus comentarios son muy valiosos y ayudan a orientar la priorización de nuevas funcionalidades y mejoras. Si detecta limitaciones, funcionalidades ausentes o comportamientos inesperados, comparta sus observaciones o solicitudes de funcionalidades mediante el gestor de issues en https://github.com/ClickHouse/clickhouse-odbc/issues

Instalación en Windows

Puede encontrar la versión más reciente del controlador en https://github.com/ClickHouse/clickhouse-odbc/releases/latest. Desde allí, puede descargar y ejecutar el instalador MSI y seguir los sencillos pasos de instalación.

Pruebas

Puede probar el driver ejecutando este sencillo script de PowerShell. Copie el texto siguiente, configure la URL, el usuario y la contraseña, y péguelo en el símbolo del sistema de PowerShell; después de ejecutar $reader.GetValue(0), debería mostrarse la versión del servidor de ClickHouse.

$url = "http://127.0.0.1:8123/"
$username = "default"
$password = ""
$conn = New-Object System.Data.Odbc.OdbcConnection("`
    Driver={ClickHouse ODBC Driver (Unicode)};`
    Url=$url;`
    Username=$username;`
    Password=$password")
$conn.Open()
$cmd = $conn.CreateCommand()
$cmd.CommandText = "select version()"
$reader = $cmd.ExecuteReader()
$reader.Read()
$reader.GetValue(0)
$reader.Close()
$conn.Close()

Parámetros de configuración

Los siguientes parámetros corresponden a los ajustes más utilizados para establecer una conexión con el controlador ODBC de ClickHouse. Cubren las opciones esenciales de autenticación, comportamiento de la conexión y gestión de datos. La lista completa de parámetros compatibles está disponible en la página de GitHub del proyecto https://github.com/ClickHouse/clickhouse-odbc.

Url: Especifica el endpoint HTTP(S) completo del servidor de ClickHouse. Incluye el protocolo, el host, el puerto y una ruta opcional.
Username: El nombre de usuario utilizado para autenticarse con el servidor de ClickHouse.
Password: La contraseña asociada al nombre de usuario especificado. Si no se proporciona, el controlador se conecta sin autenticación mediante contraseña.
Database: La base de datos predeterminada que se utilizará para la conexión.
Timeout: El tiempo máximo (en segundos) que el controlador espera una respuesta del servidor antes de cancelar la solicitud.
ClientName: Un identificador personalizado que se envía al servidor de ClickHouse como parte de los metadatos del cliente. Resulta útil para el tracing o para distinguir el tráfico de distintas aplicaciones. Este parámetro formará parte del encabezado User-Agent de las solicitudes HTTP generadas por el controlador.
Compression: Activa o desactiva la compresión HTTP para las cargas útiles de las solicitudes y respuestas. Cuando está activada, puede reducir el uso de ancho de banda y mejorar el rendimiento en conjuntos de resultados grandes.
SqlCompatibilitySettings: Activa ajustes de consulta que hacen que ClickHouse se comporte más como una base de datos relacional tradicional. Esto resulta útil cuando herramientas de terceros, como Power BI, generan consultas automáticamente. Estas herramientas normalmente no conocen determinados comportamientos específicos de ClickHouse y pueden generar consultas que producen errores o resultados inesperados. Consulte Ajustes de ClickHouse utilizados por el parámetro de configuración SqlCompatibilitySettings para obtener más información.

Estos son algunos ejemplos de cadenas de conexión completas que se pasan al controlador para establecer una conexión.

Un servidor de ClickHouse instalado localmente en una instancia de WSL

Driver={ClickHouse ODBC Driver (Unicode)};Url=http://localhost:8123/;Username=default

Una instancia de ClickHouse Cloud.

Driver={ClickHouse ODBC Driver (Unicode)};Url=https://you-instance-url.gcp.clickhouse.cloud:8443/;Username=default;Password=your-password

Integración con Microsoft Power BI

Puede usar el controlador ODBC para conectar Microsoft Power BI a un servidor de ClickHouse. Power BI ofrece dos opciones de conexión: el conector ODBC genérico y el conector ClickHouse; ambos se incluyen en las instalaciones estándar de Power BI. Ambos conectores usan ODBC internamente, pero difieren en sus capacidades:

Conector ClickHouse (recomendado) Usa ODBC internamente, pero admite el modo DirectQuery. En este modo, Power BI genera automáticamente consultas SQL y recupera solo los datos necesarios para cada visualización u operación de filtrado.
Conector ODBC Solo admite el modo Importar. Power BI ejecuta la consulta proporcionada por el usuario (o selecciona la tabla completa) e importa el conjunto de resultados completo a Power BI. Las actualizaciones posteriores vuelven a importar todo el conjunto de datos.

Elija el conector según su caso de uso. DirectQuery es más adecuado para dashboards interactivos con conjuntos de datos grandes. Elija el modo Importar cuando necesite copias locales completas de los datos. Para obtener más información sobre la integración de Microsoft Power BI con ClickHouse, consulte la página de documentación de ClickHouse sobre la integración con Power BI.

Configuración de compatibilidad con SQL

ClickHouse tiene su propio dialecto de SQL y, en algunos casos, se comporta de forma distinta a otras bases de datos, como MS SQL Server, MySQL o PostgreSQL. A menudo, estas diferencias son una ventaja, ya que incorporan una sintaxis mejorada que facilita el uso de las funcionalidades de ClickHouse. Sin embargo, el controlador ODBC se utiliza con frecuencia en entornos donde las consultas las generan herramientas de terceros, como Power BI, en lugar de los usuarios. Estas consultas suelen basarse en un subconjunto mínimo del estándar SQL. En estos casos, las diferencias de ClickHouse con respecto al estándar SQL pueden no comportarse como se espera y producir resultados inesperados o errores. El controlador ODBC proporciona un parámetro de configuración adicional, SqlCompatibilitySettings, que permite aplicar ajustes de consulta específicos a las consultas para que el comportamiento de ClickHouse se ajuste más al SQL estándar.

Ajustes de ClickHouse habilitados por el parámetro de configuración SqlCompatibilitySettings

Esta sección describe qué ajustes modifica el controlador ODBC y por qué. cast_keep_nullable De forma predeterminada, ClickHouse no permite convertir tipos que admiten valores NULL en tipos que no los admiten. Sin embargo, muchas herramientas de BI no distinguen entre tipos que admiten valores NULL y los que no al realizar conversiones de tipo. Como resultado, no es raro ver consultas como la siguiente, generadas por herramientas de BI:

SELECT sum(CAST(value, 'Int32'))
FROM values

De forma predeterminada, si la columna value admite valores NULL, esta consulta fallará con el mensaje:

DB::Exception: Cannot convert NULL value to non-Nullable type: while executing 'FUNCTION CAST(__table1.value :: 2,
'Int32'_String :: 1) -> CAST(__table1.value, 'Int32'_String) Int32 : 0'. (CANNOT_INSERT_NULL_IN_ORDINARY_COLUMN)

Habilitar cast_keep_nullable cambia el comportamiento de CAST para que conserve la nulabilidad de sus argumentos. Esto acerca el comportamiento de ClickHouse al de otras bases de datos y al estándar SQL para este tipo de conversión. prefer_column_name_to_alias ClickHouse permite hacer referencia a expresiones de la misma lista SELECT mediante sus alias. Por ejemplo, esta consulta evita repeticiones y es más fácil de escribir:

SELECT
    sum(value) AS S,
    count() AS C,
    S / C
FROM test

Esta característica se utiliza ampliamente, pero otras bases de datos normalmente no resuelven los alias de este modo en la misma lista SELECT, por lo que dichas consultas generarían un error. Los problemas son más evidentes cuando un alias tiene el mismo nombre que una columna. Por ejemplo:

SELECT
    sum(value) AS value,
    avg(value)
FROM test

¿Qué value debería agregar avg(value)? De forma predeterminada, ClickHouse prefiere el alias, lo que convierte esto, en la práctica, en una agregación anidada, algo que la mayoría de las herramientas no espera. Por sí solo, esto rara vez representa un problema, pero algunas herramientas de BI generan consultas con subconsultas que reutilizan alias de columnas. Por ejemplo, Power BI suele generar consultas similares a la siguiente:

SELECT
    sum(C1) AS C1,
    count(C1) AS C2
FROM
(
    SELECT sum(value) AS C1
    FROM test
    GROUP BY group_index
) AS TBL

Las referencias a C1 pueden generar el siguiente error:

Code: 184. DB::Exception: Received from localhost:9000. DB::Exception: Aggregate function sum(C1) AS C1 is found
inside another aggregate function in query. (ILLEGAL_AGGREGATION)

Otras bases de datos normalmente no resuelven los alias en el mismo nivel de esta manera y, en su lugar, tratan C1 como una columna de la subconsulta. Para mantener un comportamiento similar en ClickHouse y permitir que estas consultas se ejecuten sin errores, el controlador ODBC habilita prefer_column_name_to_alias. En la mayoría de los casos, habilitar esta configuración no debería suponer ningún problema. Sin embargo, los usuarios con readonly establecido en 1 no pueden modificar ninguna configuración, ni siquiera para consultas SELECT. Para estos usuarios, habilitar SqlCompatibilitySettings provocará un error. La siguiente sección explica cómo hacer que este parámetro de configuración funcione para usuarios de solo lectura.

Hacer que la configuración de compatibilidad con SQL funcione para usuarios de solo lectura

Al conectarse a ClickHouse mediante el controlador ODBC con el parámetro SqlCompatibilitySettings habilitado, un usuario con el ajuste readonly establecido en 1 recibirá un error porque el controlador intenta modificar ajustes de consulta:

Code: 164. DB::Exception: Cannot modify 'cast_keep_nullable' setting in readonly mode. (READONLY)
Code: 164. DB::Exception: Cannot modify 'prefer_column_name_to_alias' setting in readonly mode. (READONLY)

Esto ocurre porque los usuarios en modo de solo lectura no pueden cambiar la configuración, ni siquiera en consultas SELECT individuales. Hay varias formas de solucionarlo. Opción 1. Establecer readonly en 2 Esta es la opción más sencilla. Establecer readonly en 2 permite cambiar la configuración y mantener al usuario en modo de solo lectura.

ALTER USER your_odbc_user MODIFY SETTING
    readonly = 2

En la mayoría de los casos, establecer readonly en 2 es la forma más sencilla y recomendada de resolver este problema. Si no le funciona, utilice la segunda opción. Opción 2. Cambiar la configuración del usuario para que coincida con la establecida por el controlador ODBC. Esto también es sencillo: actualice la configuración del usuario para que coincida con lo que el controlador ODBC intenta establecer.

ALTER USER your_odbc_user MODIFY SETTING
    cast_keep_nullable = 1,
    prefer_column_name_to_alias = 1

Con este cambio, el controlador ODBC puede seguir intentando aplicar la configuración, pero, como los valores ya coinciden, no se realiza ningún cambio efectivo y se evita el error. Esta opción también es sencilla, pero requiere mantenimiento: las versiones más recientes del controlador pueden modificar la lista de opciones de configuración o añadir otras nuevas para fines de compatibilidad. Si codifica de forma fija estas opciones de configuración para su usuario ODBC, es posible que deba actualizarlas cada vez que el controlador ODBC empiece a aplicar opciones de configuración adicionales.

Fundamentos

Conceptos de ClickHouse

Buenas prácticas

Instalación en Windows

Pruebas

Parámetros de configuración

Integración con Microsoft Power BI

Configuración de compatibilidad con SQL

Ajustes de ClickHouse habilitados por el parámetro de configuración SqlCompatibilitySettings

Hacer que la configuración de compatibilidad con SQL funcione para usuarios de solo lectura

​Instalación en Windows

​Pruebas

​Parámetros de configuración

​Integración con Microsoft Power BI

​Configuración de compatibilidad con SQL

​Ajustes de ClickHouse habilitados por el parámetro de configuración SqlCompatibilitySettings

​Hacer que la configuración de compatibilidad con SQL funcione para usuarios de solo lectura

Instalación en Windows

Pruebas

Parámetros de configuración

Integración con Microsoft Power BI

Configuración de compatibilidad con SQL

Ajustes de ClickHouse habilitados por el parámetro de configuración SqlCompatibilitySettings

Hacer que la configuración de compatibilidad con SQL funcione para usuarios de solo lectura