Pilote ODBC

Documentation du pilote ODBC ClickHouse

Le pilote ODBC ClickHouse fournit une interface conforme aux normes permettant de connecter des applications compatibles ODBC à ClickHouse. Il implémente l’API ODBC et permet aux applications, aux outils de BI et aux environnements de script d’exécuter des requêtes SQL, de récupérer des résultats et d’interagir avec ClickHouse à l’aide de mécanismes familiers.

Le pilote communique avec le serveur ClickHouse via le protocole HTTP, qui est le principal protocole pris en charge dans tous les déploiements ClickHouse. Il peut ainsi fonctionner de manière cohérente dans divers environnements, notamment les installations locales, les services managés dans le cloud et les environnements où seul un accès HTTP est disponible.

Le code source du pilote est disponible dans le dépôt GitHub ClickHouse-ODBC.

Pour une meilleure compatibilité, nous vous recommandons vivement de mettre à jour votre serveur ClickHouse vers la version 24.11 ou une version ultérieure.

Ce pilote est en cours de développement actif. Certaines fonctionnalités ODBC ne sont peut-être pas encore entièrement implémentées. La version actuelle se concentre sur la fourniture d’une connectivité essentielle et des fonctionnalités ODBC de base, tandis que des fonctionnalités supplémentaires sont prévues dans de futures versions.

Vos retours sont très précieux et contribuent à prioriser les nouvelles fonctionnalités et améliorations. Si vous rencontrez des limitations, des fonctionnalités manquantes ou un comportement inattendu, veuillez partager vos observations ou demandes de fonctionnalités via le gestionnaire d’issues à l’adresse https://github.com/ClickHouse/clickhouse-odbc/issues

​

Installation sous Windows

Vous trouverez la dernière version du pilote à l’adresse https://github.com/ClickHouse/clickhouse-odbc/releases/latest. Vous pouvez y télécharger et exécuter le programme d’installation MSI, puis suivre les étapes d’installation.

​

Test

Vous pouvez tester le driver en exécutant ce script PowerShell simple. Copiez le texte ci-dessous, renseignez votre URL, votre utilisateur et votre mot de passe, puis collez-le dans votre invite de commandes PowerShell. Après avoir exécuté $reader.GetValue(0), la version de votre serveur ClickHouse devrait s’afficher.

$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()

​

Paramètres de configuration

Les paramètres ci-dessous correspondent aux réglages les plus couramment utilisés pour établir une connexion avec le pilote ODBC ClickHouse. Ils couvrent les principales options d’authentification, de comportement de connexion et de traitement des données. La liste complète des paramètres pris en charge est disponible sur la page GitHub du projet https://github.com/ClickHouse/clickhouse-odbc.

• Url : spécifie l’endpoint HTTP(S) complet du serveur ClickHouse. Il comprend le protocole, l’hôte, le port et le chemin facultatif.
• Username : le nom d’utilisateur utilisé pour l’authentification auprès du serveur ClickHouse.
• Password : le mot de passe associé au nom d’utilisateur spécifié. S’il n’est pas fourni, le pilote se connecte sans authentification par mot de passe.
• Database : la base de données par défaut à utiliser pour la connexion.
• Timeout : la durée maximale (en secondes) pendant laquelle le pilote attend une réponse du serveur avant d’abandonner la requête.
• ClientName : un identifiant personnalisé envoyé au serveur ClickHouse dans les métadonnées du client. Utile pour le traçage ou pour distinguer le trafic provenant de différentes applications. Ce paramètre fait partie de l’en-tête User-Agent des requêtes HTTP générées par le pilote.
• Compression : active ou désactive la compression HTTP des charges utiles de requête et de réponse. Lorsqu’elle est activée, elle peut réduire l’utilisation de la bande passante et améliorer les performances pour les jeux de résultats volumineux.
• SqlCompatibilitySettings : active des paramètres de requête qui permettent à ClickHouse de se comporter davantage comme une base de données relationnelle traditionnelle. Cela est utile lorsque les requêtes sont générées automatiquement par des outils tiers, par exemple Power BI. Ces outils ne connaissent généralement pas certains comportements spécifiques à ClickHouse et peuvent produire des requêtes entraînant des erreurs ou des résultats inattendus. Consultez les paramètres ClickHouse utilisés par le paramètre de configuration SqlCompatibilitySettings pour plus de détails.

Voici quelques exemples de chaînes de connexion complètes transmises au pilote pour établir une connexion.

• Un serveur ClickHouse installé localement sur une instance WSL

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

• Une instance ClickHouse Cloud.

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

​

Intégration à Microsoft Power BI

Vous pouvez utiliser le pilote ODBC pour connecter Microsoft Power BI à un serveur ClickHouse. Power BI propose deux options de connexion : le connecteur ODBC générique et le connecteur ClickHouse, tous deux inclus dans les installations standard de Power BI.

Les deux connecteurs s’appuient en interne sur ODBC, mais leurs fonctionnalités diffèrent :

• ClickHouse Connector (recommandé) Utilise ODBC en interne, mais prend en charge le mode DirectQuery. Dans ce mode, Power BI génère automatiquement des requêtes SQL et récupère uniquement les données nécessaires à chaque visualisation ou opération de filtrage.

• Connecteur ODBC Prend uniquement en charge le mode Importation. Power BI exécute la requête fournie par l’utilisateur (ou sélectionne la table entière) et importe l’intégralité du jeu de résultats dans Power BI. Les actualisations ultérieures réimportent l’ensemble du dataset.

Choisissez le connecteur en fonction de votre cas d’utilisation. DirectQuery est particulièrement adapté aux dashboards interactifs contenant de grands datasets. Choisissez le mode Importation lorsque vous avez besoin de copies locales complètes des données.

Pour plus d’informations sur l’intégration de Microsoft Power BI à ClickHouse, consultez la page de documentation ClickHouse consacrée à l’intégration à Power BI.

​

Paramètres de compatibilité SQL

ClickHouse possède son propre dialecte SQL et, dans certains cas, se comporte différemment d’autres bases de données telles que MS SQL Server, MySQL ou PostgreSQL. Ces différences constituent souvent un avantage, car elles introduisent une syntaxe améliorée qui facilite l’utilisation des fonctionnalités de ClickHouse.

Toutefois, le pilote ODBC est souvent utilisé dans des environnements où les requêtes sont générées par des outils tiers, tels que Power BI, plutôt que rédigées par les utilisateurs. Ces requêtes reposent généralement sur un sous-ensemble minimal de la norme SQL. Dans ce cas, les écarts de ClickHouse par rapport à la norme SQL peuvent produire des résultats ou des erreurs inattendus. Le pilote ODBC fournit un paramètre de configuration supplémentaire, SqlCompatibilitySettings, qui active des paramètres de requête spécifiques afin de rapprocher davantage le comportement de ClickHouse de celui de la norme SQL.

​

Paramètres ClickHouse activés par le paramètre de configuration SqlCompatibilitySettings

Cette section décrit les paramètres que le pilote ODBC modifie et explique pourquoi.

cast_keep_nullable

Par défaut, ClickHouse n’autorise pas la conversion de types nullable en types non nullable. Cependant, de nombreux outils BI ne distinguent pas les types nullable des types non nullable lors des conversions de type. Il n’est donc pas rare que les outils BI génèrent des requêtes telles que celle-ci :

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

Par défaut, si la colonne value accepte les valeurs NULL, cette requête échoue avec le message :

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)

L’activation de cast_keep_nullable modifie le comportement de CAST afin de préserver la nullabilité de ses arguments. Cela rapproche le comportement de ClickHouse de celui des autres bases de données et de la norme SQL pour ce type de conversion.

prefer_column_name_to_alias

ClickHouse permet de référencer des expressions dans la même liste SELECT à l’aide de leurs alias. Par exemple, cette requête évite les répétitions et est plus facile à écrire :

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

Cette fonctionnalité est largement utilisée, mais les autres bases de données ne résolvent généralement pas les alias de cette façon dans une même liste SELECT, et de telles requêtes généreraient une erreur. Les problèmes sont particulièrement visibles lorsqu’un alias porte le même nom qu’une colonne. Par exemple :

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

Quelle value avg(value) doit-elle agréger ? Par défaut, ClickHouse privilégie l’alias, ce qui transforme de fait cette expression en agrégation imbriquée, alors que ce n’est pas ce à quoi s’attendent la plupart des outils.

Cela pose rarement problème en soi, mais certains outils de BI génèrent des requêtes avec des sous-requêtes qui réutilisent des alias de colonnes. Par exemple, Power BI génère souvent des requêtes similaires à la suivante :

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

Les références à C1 peuvent générer l’erreur suivante :

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)

Les autres bases de données ne résolvent généralement pas les alias de cette façon au même niveau et considèrent plutôt C1 comme une colonne de la sous-requête. Afin de préserver un comportement similaire dans ClickHouse et de permettre l’exécution de telles requêtes sans erreur, le pilote ODBC active prefer_column_name_to_alias.

Dans la plupart des cas, l’activation de ces paramètres ne devrait pas poser de problème. Toutefois, les utilisateurs dont le paramètre readonly est défini sur 1 ne peuvent modifier aucun paramètre, même pour les requêtes SELECT. Pour ces utilisateurs, l’activation de SqlCompatibilitySettings entraînera une erreur. La section suivante explique comment permettre à ce paramètre de configuration de fonctionner pour les utilisateurs en lecture seule.

​

Utiliser les paramètres de compatibilité SQL avec des utilisateurs en lecture seule

Lors de la connexion à ClickHouse via le pilote ODBC avec le paramètre SqlCompatibilitySettings activé, un utilisateur dont le paramètre readonly est défini sur 1 rencontrera une erreur, car le pilote tente de modifier les paramètres de requête :

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)

Cela se produit car les utilisateurs en mode lecture seule ne sont pas autorisés à modifier les paramètres, même pour des requêtes SELECT individuelles. Il existe plusieurs façons de résoudre ce problème.

Option 1. Définir readonly sur 2

C’est l’option la plus simple. Définir readonly sur 2 permet de modifier les paramètres tout en maintenant l’utilisateur en mode lecture seule.

ALTER USER your_odbc_user MODIFY SETTING
    readonly = 2

Dans la plupart des cas, définir readonly sur 2 est le moyen le plus simple et recommandé de résoudre ce problème. Si cela ne fonctionne pas dans votre cas, utilisez la deuxième option.

Option 2. Modifier les paramètres utilisateur pour qu’ils correspondent à ceux définis par le pilote ODBC.

C’est également simple : mettez à jour les paramètres utilisateur afin qu’ils correspondent déjà à ceux que le pilote ODBC tente de définir.

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

Avec cette modification, le pilote ODBC peut toujours tenter d’appliquer les paramètres, mais comme les valeurs correspondent déjà, aucune modification effective n’est apportée et l’erreur est ainsi évitée.

Cette option est également simple, mais elle nécessite une maintenance : les versions plus récentes du pilote peuvent modifier la liste des paramètres ou en ajouter de nouveaux à des fins de compatibilité. Si vous définissez ces paramètres en dur pour votre utilisateur ODBC, vous devrez peut-être les mettre à jour chaque fois que le pilote ODBC commencera à appliquer des paramètres supplémentaires.