To install an ClickHouse ODBC driver to Exploratory Collaboration Server, you can create a customized Docker image based on the default R environment Docker image of Exploratory Collaboration Server.
Run the following command to start a Docker container based on the default r-exploratory Docker image. In this example, we use the Docker image from Collaboration Server version 6.12.3.6. Please replace the version number in the command with the one you are using.
docker run -it r-exploratory:6.12.3.6 /bin/bashWith the shell you opened, install the ODBC driver that you want to use on the Collaboration Server. Make sure the ODBC driver works with unixODBC.
As an example, let's add an Amazon Redshift ODBC driver.
To download the latest ClickHouse ODBC driver, you can type in the below commands first.
curl https://api.github.com/repos/ClickHouse/clickhouse-odbc/releases/latestThen you will get result like this. So write down the "url" under "assets" section. In this example, https://api.github.com/repos/ClickHouse/clickhouse-odbc/releases/assets/77347265
{
"url": "https://api.github.com/repos/ClickHouse/clickhouse-odbc/releases/76285645",
"assets_url": "https://api.github.com/repos/ClickHouse/clickhouse-odbc/releases/76285645/assets",
"upload_url": "https://uploads.github.com/repos/ClickHouse/clickhouse-odbc/releases/76285645/assets{?name,label}",
"html_url": "https://github.com/ClickHouse/clickhouse-odbc/releases/tag/v1.2.1.20220905",
"id": 76285645,
"author": {
"login": "Enmk",
"id": 1110183,
"node_id": "MDQ6VXNlcjExMTAxODM=",
"avatar_url": "https://avatars.githubusercontent.com/u/1110183?v=4",
"gravatar_id": "",
"url": "https://api.github.com/users/Enmk",
"html_url": "https://github.com/Enmk",
"followers_url": "https://api.github.com/users/Enmk/followers",
"following_url": "https://api.github.com/users/Enmk/following{/other_user}",
"gists_url": "https://api.github.com/users/Enmk/gists{/gist_id}",
"starred_url": "https://api.github.com/users/Enmk/starred{/owner}{/repo}",
"subscriptions_url": "https://api.github.com/users/Enmk/subscriptions",
"organizations_url": "https://api.github.com/users/Enmk/orgs",
"repos_url": "https://api.github.com/users/Enmk/repos",
"events_url": "https://api.github.com/users/Enmk/events{/privacy}",
"received_events_url": "https://api.github.com/users/Enmk/received_events",
"type": "User",
"site_admin": false
},
"node_id": "RE_kwDOBRXkGs4EjAbN",
"tag_name": "v1.2.1.20220905",
"target_commitish": "master",
"name": "Release 1.2.1.20220905",
"draft": false,
"prerelease": false,
"created_at": "2022-09-09T09:24:39Z",
"published_at": "2022-09-09T10:53:45Z",
"assets": [
{
"url": "https://api.github.com/repos/ClickHouse/clickhouse-odbc/releases/assets/77347265",
"id": 77347265,Then try the following command with the URL you just wrote down.
curl -H "Accept:application/octet-stream" -i https://api.github.com/repos/ClickHouse/clickhouse-odbc/releases/assets/77347265Then you'll get a following result.
HTTP/2 302
server: GitHub.com
date: Mon, 10 Apr 2023 06:47:45 GMT
content-type: text/html;charset=utf-8
content-length: 0
location: https://objects.githubusercontent.com/github-production-release-asset-2e65be/85320730/a0db406a-e2e8-46ba-a238-a93d13bf9962?X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=AKIAIWNJYAX4CSVEH53A%2F20230410%2Fus-east-1%2Fs3%2Faws4_request&X-Amz-Date=20230410T064745Z&X-Amz-Expires=300&X-Amz-Signature=e7882f669b2334ca65d93e8b74d06f22c6d2c7c4f5bcece79fc7583e5bef9368&X-Amz-SignedHeaders=host&actor_id=0&key_id=0&repo_id=85320730&response-content-disposition=attachment%3B%20filename%3Dclickhouse-odbc-linux.zip&response-content-type=application%2Foctet-stream
x-github-api-version-selected: 2022-11-28
access-control-expose-headers: ETag, Link, Location, Retry-After, X-GitHub-OTP, X-RateLimit-Limit, X-RateLimit-Remaining, X-RateLimit-Used, X-RateLimit-Resource, X-RateLimit-Reset, X-OAuth-Scopes, X-Accepted-OAuth-Scopes, X-Poll-Interval, X-GitHub-Media-Type, X-GitHub-SSO, X-GitHub-Request-Id, Deprecation, Sunset
access-control-allow-origin: *
strict-transport-security: max-age=31536000; includeSubdomains; preload
x-frame-options: deny
x-content-type-options: nosniff
x-xss-protection: 0
referrer-policy: origin-when-cross-origin, strict-origin-when-cross-origin
content-security-policy: default-src 'none'
vary: Accept-Encoding, Accept, X-Requested-With
x-ratelimit-limit: 60
x-ratelimit-remaining: 56
x-ratelimit-reset: 1681112456
x-ratelimit-resource: core
x-ratelimit-used: 4
x-github-request-id: C404:7CE6:790E62A:7CEFE29:6433B111From the result, write down the URL set to "location"and try the below command with the URL.
curl "https://objects.githubusercontent.com/github-production-release-asset-2e65be/85320730/a0db406a-e2e8-46ba-a238-a93d13bf9962?X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=AKIAIWNJYAX4CSVEH53A%2F20230410%2Fus-east-1%2Fs3%2Faws4_request&X-Amz-Date=20230410T064745Z&X-Amz-Expires=300&X-Amz-Signature=e7882f669b2334ca65d93e8b74d06f22c6d2c7c4f5bcece79fc7583e5bef9368&X-Amz-SignedHeaders=host&actor_id=0&key_id=0&repo_id=85320730&response-content-disposition=attachment%3B%20filename%3Dclickhouse-odbc-linux.zip&response-content-type=application%2Foctet-stream" -i -o clickhouse.zipOnce the download is done, unzip it.
unzip clickhouse.zip And you will see clickhouse-odbc-1.2.1-Linux.tar.gz is created as follows.
warning [clickhouse.zip]: 820 extra bytes at beginning or within zipfile
(attempting to process anyway)
inflating: clickhouse-odbc-1.2.1-Linux.tar.gz.sha256
inflating: clickhouse-odbc-1.2.1-Linux.tar.gz Extract the clickhouse-odbc-1.2.1-Linux.tar.gz
tar -xf clickhouse-odbc-1.2.1-Linux.tar.gzAnd if you type ls -altr you'll get following result.
-rw-r--r-- 1 root root 40192082 Apr 10 06:50 clickhouse.zip
drwxr-xr-x 4 root root 4096 Apr 10 06:52 clickhouse-odbc-1.2.1-LinuxMove the clickhouse-odbc-1.2.1-Linux/lib64/ folder under /usr/local/ with the below command.
mv clickhouse-odbc-1.2.1-Linux/lib64/ /usr/local/And lastly, add ODBC driver entry to odbcinst.ini file with the below command.
/usr/bin/odbcinst -i -d -f /clickhouse-odbc-1.2.1-Linux/share/doc/clickhouse-odbc/config/odbcinst.ini.sampleAnd now the /etc/odbcinst.ini file looks like this.
[ClickHouse ODBC Driver (ANSI)]
Description=ODBC Driver (ANSI) for ClickHouse
Driver=/usr/local/lib64/libclickhouseodbc.so
Setup=/usr/local/lib64/libclickhouseodbc.so
UsageCount=1
[ClickHouse ODBC Driver (Unicode)]
Description=ODBC Driver (Unicode) for ClickHouse
Driver=/usr/local/lib64/libclickhouseodbcw.so
Setup=/usr/local/lib64/libclickhouseodbcw.so
UsageCount=1Open another shell on the host Linux machine, and run the following command to find the ID of the Docker container we just ran and made the modifications on.
docker psThe above command should give the table that looks like the following as the output.
CONTAINER ID IMAGE COMMAND PORTS
aad6f8d956a9 r-exploratory:6.12.3.6 "/bin/bash" 6311/tcp Find the Container ID for the Docker container from the table. In this case, it's aad6f8d956a9.
With the ID, you can now create a Docker image with the modifications. Here, let's give the new image the customized version number 6.12.3.6.1.
docker commit -c 'CMD ["R", "-e", "Rserve::run.Rserve(remote=TRUE)"]' -c 'EXPOSE 6311' aad6f8d956a9 r-exploratory:6.12.3.6.1Start a Docker container with the new Docker image you just created.
docker run -it r-exploratory:6.12.3.6.1 /bin/bashStart R in it.
RIn the R's command-line interface, type in odbc::odbcListDrivers() and you can verify that the ODBC driver is recognized as follows.
> odbc::odbcListDrivers()
name attribute
1 PostgreSQL ANSI Description
2 PostgreSQL ANSI Driver
3 PostgreSQL ANSI Setup
4 PostgreSQL ANSI Debug
5 PostgreSQL ANSI CommLog
6 PostgreSQL ANSI UsageCount
7 PostgreSQL Unicode Description
8 PostgreSQL Unicode Driver
9 PostgreSQL Unicode Setup
10 PostgreSQL Unicode Debug
11 PostgreSQL Unicode CommLog
12 PostgreSQL Unicode UsageCount
13 ODBC Driver 18 for SQL Server Description
14 ODBC Driver 18 for SQL Server Driver
15 ODBC Driver 18 for SQL Server UsageCount
16 SnowflakeDSIIDriver APILevel
17 SnowflakeDSIIDriver ConnectFunctions
18 SnowflakeDSIIDriver Description
19 SnowflakeDSIIDriver Driver
20 SnowflakeDSIIDriver DriverODBCVer
21 SnowflakeDSIIDriver SQLLevel
22 ODBC Drivers ClickHouse ODBC Driver (ANSI)
23 ODBC Drivers ClickHouse ODBC Driver (Unicode)
24 ODBC Drivers UsageCount
25 ClickHouse ODBC Driver (ANSI) Description
26 ClickHouse ODBC Driver (ANSI) Driver
27 ClickHouse ODBC Driver (ANSI) Setup
28 ClickHouse ODBC Driver (ANSI) UsageCount
29 ClickHouse ODBC Driver (Unicode) Description
30 ClickHouse ODBC Driver (Unicode) Driver
31 ClickHouse ODBC Driver (Unicode) Setup
32 ClickHouse ODBC Driver (Unicode) UsageCount
value
1 PostgreSQL ODBC driver (ANSI version)
2 psqlodbca.so
3 libodbcpsqlS.so
4 0
5 1
6 1
7 PostgreSQL ODBC driver (Unicode version)
8 psqlodbcw.so
9 libodbcpsqlS.so
10 0
11 1
12 1
13 Microsoft ODBC Driver 18 for SQL Server
14 /opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.1.so.2.1
15 1
16 1
17 YYY
18 Snowflake DSII
19 /usr/lib64/snowflake/odbc/lib/libSnowflake.so
20 03.52
21 1
22 Installed
23 Installed
24 1
25 ODBC Driver (ANSI) for ClickHouse
26 /usr/local/lib64/libclickhouseodbc.so
27 /usr/local/lib64/libclickhouseodbc.so
28 1
29 ODBC Driver (Unicode) for ClickHouse
30 /usr/local/lib64/libclickhouseodbcw.so
31 /usr/local/lib64/libclickhouseodbcw.so
32 1
> Locate the line in the docker-compose.yml file that specified the Docker image version for the r-exploratory Docker container that looks like the following.
...
rserve:
image: r-exploratory:6.12.3.6
...Modify the version to the one you just created.
...
rserve:
image: r-exploratory:6.12.3.6.1
...Now, restarting the Collaboration Server would make it start using the new R environment with the custom modifications.