clickhouse with odbc, for docker-compose and kubernetes

clickhouse_schema.sql

DROP DICTIONARY IF EXISTS default.mysql_dict;
CREATE DICTIONARY IF NOT EXISTS default.mysql_dict(
 id Int64,
 value String
)
PRIMARY KEY id
LAYOUT ( COMPLEX_KEY_HASHED() )
SOURCE ( MYSQL( port 3306 user 'test' password 'test' replica(host 'mysql' priority 1) db 'test' table 'test')  )
LIFETIME (60);

DROP DICTIONARY IF EXISTS default.postgres_dict;
CREATE DICTIONARY IF NOT EXISTS default.postgres_dict(
    id Int64,
    value String
)
PRIMARY KEY id
LAYOUT ( COMPLEX_KEY_HASHED() )
SOURCE ( POSTGRESQL( port 5432 user 'test' password 'test' replica(host 'postgres' priority 1) db 'test' table 'test')  )
LIFETIME (60);

DROP DICTIONARY IF EXISTS default.odbc_mysql_dict;
CREATE DICTIONARY IF NOT EXISTS default.odbc_mysql_dict(
    id Int64,
    value String
)
PRIMARY KEY id
LAYOUT ( COMPLEX_KEY_HASHED() )
SOURCE ( ODBC( connection_string 'DSN=mysql' db 'test' table 'test')  )
LIFETIME (60);

DROP DICTIONARY IF EXISTS default.odbc_postgres_dict;
CREATE DICTIONARY IF NOT EXISTS default.odbc_postgres_dict(
    id Int64,
    value String
)
PRIMARY KEY id
LAYOUT ( COMPLEX_KEY_HASHED() )
SOURCE ( ODBC( connection_string 'DSN=postgres' table 'test')  )
LIFETIME (60);

DROP DICTIONARY IF EXISTS default.odbc_mssql_dict;
CREATE DICTIONARY IF NOT EXISTS default.odbc_mssql_dict(
    id Int64,
    value String
)
PRIMARY KEY id
LAYOUT ( COMPLEX_KEY_HASHED() )
SOURCE ( ODBC( connection_string 'DSN=mssql;UID=test;PWD=test2021PASSWORD;Database=test' table 'test')  )
LIFETIME (60);

/* oracle enforce UPPER_CASE */
DROP DICTIONARY IF EXISTS default.odbc_oracle_dict;
CREATE DICTIONARY IF NOT EXISTS default.odbc_oracle_dict(
    ID Int64,
    VALUE String
)
PRIMARY KEY ID
LAYOUT ( COMPLEX_KEY_HASHED() )
SOURCE ( ODBC( connection_string 'DSN=oracle' table 'TEST')  )
LIFETIME (60);


SYSTEM RELOAD DICTIONARIES;
SELECT * FROM system.dictionaries FORMAT Vertical;

docker-compose.yaml

version: "3"
services:
  clickhouse:
    image: docker.io/clickhousepro/clickhouse-server:odbc
    build:
      dockerfile: Dockerfile
      context: ./
    volumes:
      - ./clickhouse_schema.sql:/docker-entrypoint-initdb.d/clickhouse_schema.sql
      - ./odbc.ini:/etc/odbc.ini
      - ./tnsnames.ora:/opt/oracle/instantclient_21_3/network/admin/tnsnames.ora
    depends_on:
      - mysql
      - postgres
      - oracle
      - mssql

  mysql:
    image: docker.io/mysql:${MYSQL_VERSION:-latest}
    volumes:
      - ./mysql_schema.sql:/docker-entrypoint-initdb.d/mysql_schema.sql
    environment:
      - MYSQL_ROOT_PASSWORD=mysql

  postgres:
    image: docker.io/postgres:${POSTGRES_VERSION:-latest}
    environment:
      - POSTGRES_PASSWORD=postgres
    volumes:
      - ./postgres_schema.sql:/docker-entrypoint-initdb.d/postges_schema.sql

  mssql:
    image: mcr.microsoft.com/mssql/server:${MSSQL_VERSION:-2019-latest}
    volumes:
      - ./mssql_entrypoint.sh:/mssql_entrypoint.sh
      - ./mssql_schema.sql:/docker-entrypoint-initdb.d/mssql_schema.sql
    entrypoint: bash -x /mssql_entrypoint.sh
    environment:
      - SA_PASSWORD=sa2021PASSWORD
      - ACCEPT_EULA=Y

  oracle:
    # need docker login to allow to image
    image: container-registry.oracle.com/database/express:${ORACLE_VERSION:-latest}
    environment:
      - ORACLE_PWD=oracle
    volumes:
      - ./oracle_schema.sql:/docker-entrypoint-initdb.d/setup/oracle_schema.sql

Dockerfile

FROM yandex/clickhouse-server:${CLICKHOUSE_VERSION:-latest}

USER root
ARG UBUNTU_NAME=focal
ARG UBUNTU_VERSION=20.04

RUN echo "Begin ODBC install" && \
    #MySQL repo
    wget -qO- "http://keyserver.ubuntu.com/pks/lookup?op=get&search=0xa4a9406876fcbd3c456770c88c718d3b5072e1f5" | apt-key add - && \
    echo "deb http://repo.mysql.com/apt/ubuntu/ ${UBUNTU_NAME} mysql-8.0" >/etc/apt/sources.list.d/mysql-oracle.list && \
    echo "deb http://repo.mysql.com/apt/ubuntu/ ${UBUNTU_NAME} mysql-tools" >>/etc/apt/sources.list.d/mysql-oracle.list && \
    # PostgreSQL repo
    wget -qO- https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add - && \
    echo "deb http://apt.postgresql.org/pub/repos/apt ${UBUNTU_NAME}-pgdg main" > /etc/apt/sources.list.d/postgresql.list && \
    # MSSQL repo
    wget -qO- https://packages.microsoft.com/keys/microsoft.asc | apt-key add - && \
    wget -qO- https://packages.microsoft.com/config/ubuntu/${UBUNTU_VERSION}/prod.list > /etc/apt/sources.list.d/mssql.list && \
    # Install packages
    apt-get update && \
    apt-get install -y --no-install-recommends unixodbc curl unzip libaio1 tcpdump less && \
    apt-get install -y --no-install-recommends mysql-client mysql-connector-odbc && \
    apt-get install -y --no-install-recommends postgresql-client odbc-postgresql && \
    ACCEPT_EULA=Y apt-get install -y --no-install-recommends msodbcsql17 mssql-tools && \
    # Oracle \
    # look latest URL from https://www.oracle.com/ru/database/technologies/instant-client/linux-x86-64-downloads.html
    wget -q -O /tmp/instantclient-basic-linux.x64-21.3.0.0.0.zip https://download.oracle.com/otn_software/linux/instantclient/213000/instantclient-basic-linux.x64-21.3.0.0.0.zip && \
    wget -q -O /tmp/instantclient-sqlplus-linux.x64-21.3.0.0.0.zip https://download.oracle.com/otn_software/linux/instantclient/213000/instantclient-sqlplus-linux.x64-21.3.0.0.0.zip && \
    wget -q -O /tmp/instantclient-odbc-linux.x64-21.3.0.0.0.zip https://download.oracle.com/otn_software/linux/instantclient/213000/instantclient-odbc-linux.x64-21.3.0.0.0.zip && \
    mkdir -p /opt/oracle/ && \
    unzip /tmp/instantclient-basic-linux.x64-*.zip -d /opt/oracle && \
    unzip /tmp/instantclient-sqlplus-linux.x64-*.zip -d /opt/oracle && \
    unzip /tmp/instantclient-odbc-linux.x64-*.zip -d /opt/oracle && \
    echo /opt/oracle/instantclient_21_3 > /etc/ld.so.conf.d/oracle-instantclient.conf && \
    ldconfig && \
    bash /opt/oracle/instantclient_21_3/odbc_update_ini.sh / /opt/oracle/instantclient_21_3 && \
    sed -i -e 's/Driver=psqlodbc/Driver=\/usr\/lib\/x86_64-linux-gnu\/odbc\/psqlodbc/' /etc/odbcinst.ini && \
    rm -rf /var/lib/apt/lists/* /var/cache/debconf /tmp/* /root/.odbc.ini && \
    apt-get clean && \
    echo "Done"

http.pcap

k8s_clickhouse_odbc.yaml

---
apiVersion: v1
kind: "ConfigMap"
metadata:
  name: "clickhouse-odbc-config"
data:
  odbc.ini: |
    [mysql]
    Driver       = MySQL ODBC 8.0 Unicode Driver
    SERVER       = mysql
    PORT         = 3306
    USER         = test
    Password     = test
    Database     = test

    [postgres]
    Driver = PostgreSQL Unicode
    Servername = postgres
    PORT = 5432
    DATABASE = test
    USERNAME = test
    PASSWORD = test

    [mssql]
    Driver    = ODBC Driver 17 for SQL Server
    # Server = [protocol:]server[,port]
    Server   = tcp:mssql,1433

    [oracle]
    Driver = Oracle 21 ODBC driver
    # Should reference the connection in the /opt/oracle/instantclient_21_3/network/admin/tnsnames.ora file
    ServerName = oracle
    UserID = test_user
    Password = test
    AggregateSQLType = FLOAT
    Application Attributes = T
    Attributes = W
    BatchAutocommitMode = IfAllSuccessful
    BindAsFLOAT = F
    CacheBufferSize = 20
    CloseCursor = F
    DisableDPM = F
    DisableMTS = T
    DisableRULEHint = T
    EXECSchemaOpt =
    EXECSyntax = T
    Failover = T
    FailoverDelay = 10
    FailoverRetryCount = 10
    FetchBufferSize = 64000
    ForceWCHAR = F
    LobPrefetchSize = 8192
    Lobs = T
    Longs = T
    MaxLargeData = 0
    MaxTokenSize = 8192
    MetadataIdDefault = F
    QueryTimeout = T
    ResultSets = T
    SQLGetData extensions = F
    SQLTranslateErrors = F
    StatementCache = F
    Translation DLL =
    Translation Option = 0
    UseOCIDescribeAny = F
  tnsnames.ora: |
    oracle =
    (
      DESCRIPTION =
      (
        ADDRESS_LIST =
          (ADDRESS =
            (PROTOCOL = TCP)
            (HOST = oracle)
            (PORT = 1521)
           )
      )
      (
        CONNECT_DATA = (SERVICE_NAME = XE)
      )
    )
---
apiVersion: "clickhouse.altinity.com/v1"
kind: "ClickHouseInstallation"
metadata:
  name: "odbc"
spec:
  defaults:
    templates:
      podTemplate: pod-template
      dataVolumeClaimTemplate: data-volume-template
      logVolumeClaimTemplate: log-volume-template
  clusters:
    - name: odbc
      layout:
        shardsCount: 1
        replicasCount: 1
  templates:
    podTemplates:
      - name: pod-template
        spec:
          containers:
            - name: clickhouse
              image: docker.io/clickhousepro/clickhouse-server:odbc
              imagePullPolicy: Always
              volumeMounts:
                - name: "clickhouse-odbc-config"
                  mountPath: "/etc/odbc.ini"
                  subPath: "odbc.ini"
                - name: "clickhouse-odbc-config"
                  mountPath: "/opt/oracle/instantclient_21_3/network/admin/tnsnames.ora"
                  subPath: "tnsnames.ora"
          volumes:
            - name: "clickhouse-odbc-config"
              configMap:
                name: "clickhouse-odbc-config"
    volumeClaimTemplates:
      - name: data-volume-template
        spec:
          accessModes:
            - ReadWriteOnce
          resources:
            requests:
              storage: 1Gi
      - name: log-volume-template
        spec:
          accessModes:
            - ReadWriteOnce
          resources:
            requests:
              storage: 1Gi

k8s_mssql.yaml

# just test manifest, don't use in production
---
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: mssql-pv-claim
spec:
  accessModes:
    - ReadWriteOnce
  resources:
    requests:
      storage: 10Gi
---
apiVersion: v1
kind: Service
metadata:
  name: mssql
spec:
  ports:
    - port: 1433
  selector:
    app: mssql
  clusterIP: None
---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: mssql
spec:
  replicas: 1
  selector:
    matchLabels:
      app: mssql
  strategy:
    type: Recreate
  template:
    metadata:
      labels:
        app: mssql
    spec:
      securityContext:
        runAsGroup: 0
        runAsUser: 10001
      containers:
        - image: mcr.microsoft.com/mssql/server:latest
          name: mssql
          env:
            - name: "SA_PASSWORD"
              value: "sa2021PASSWORD"
            - name: "ACCEPT_EULA"
              value: "Y"
          ports:
            - containerPort: 1433
              name: mssql
          volumeMounts:
            - name: mssql-persistent-storage
              mountPath: /var/opt/mssql
      volumes:
        - name: mssql-persistent-storage
          persistentVolumeClaim:
            claimName: mssql-pv-claim

k8s_mysql.yaml

# just test manifest, don't use in production
---
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: mysql-pv-claim
spec:
  accessModes:
    - ReadWriteOnce
  resources:
    requests:
      storage: 10Gi
---
apiVersion: v1
kind: Service
metadata:
  name: mysql
spec:
  ports:
    - port: 3306
  selector:
    app: mysql
  clusterIP: None
---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: mysql
spec:
  replicas: 1
  selector:
    matchLabels:
      app: mysql
  strategy:
    type: Recreate
  template:
    metadata:
      labels:
        app: mysql
    spec:
      containers:
        - image: docker.io/mysql:latest
          name: mysql
          env:
            - name: MYSQL_ROOT_PASSWORD
              value: mysql
          ports:
            - containerPort: 3306
              name: mysql
          volumeMounts:
            - name: mysql-persistent-storage
              mountPath: /var/lib/mysql
      volumes:
        - name: mysql-persistent-storage
          persistentVolumeClaim:
            claimName: mysql-pv-claim

k8s_oracle.yaml

# just test manifest, don't use in production
---
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: oracle-oradata
  labels:
    app: oracle
spec:
  accessModes:
    - ReadWriteOnce
  resources:
    requests:
      storage: 12Gi
---
apiVersion: v1
kind: ConfigMap
metadata:
  name: oracle-config
  labels:
    app: oracle
data:
  ORACLE_CHARACTERSET: "AL32UTF8"
  ORACLE_PWD: "oracle"

---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: oracle
  labels:
    app: oracle
  
spec:
  replicas: 1
  selector:
    matchLabels:
      app: oracle
  strategy:
    type: Recreate
  template:
    metadata:
      labels:
        app: oracle
    spec:
      containers:
        - name: oracle
          image: container-registry.oracle.com/database/express:latest
          envFrom:
            - configMapRef:
                name: oracle-config
          ports:
            - containerPort: 1521
              name: oracle-listener
          volumeMounts:
            - name: oradata
              mountPath: /opt/oracle/oradata
          readinessProbe:
            initialDelaySeconds: 1200
            periodSeconds: 60
            exec:
              command:
                - /bin/bash
                - "-l"
                - "-c"
                - "/opt/oracle/checkDBStatus.sh || exit 1"
          livenessProbe:
            initialDelaySeconds: 1200
            periodSeconds: 60
            exec:
              command:
                - /bin/bash
                - "-l"
                - "-c"
                - "/opt/oracle/checkDBStatus.sh || exit 1"
      volumes:
        - name: oradata
          persistentVolumeClaim:
            claimName: oracle-oradata
---
apiVersion: v1
kind: Service
metadata:
  name: oracle
  labels:
    app: oracle
spec:
  ports:
    - name: listener
      port: 1521
      targetPort: oracle-listener
  selector:
    app: oracle

k8s_postgres.yaml

# just test manifest, don't use in production
---
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: postgres-pv-claim
spec:
  accessModes:
    - ReadWriteOnce
  resources:
    requests:
      storage: 10Gi
---
apiVersion: v1
kind: Service
metadata:
  name: postgres
spec:
  ports:
    - port: 5432
  selector:
    app: postgres
  clusterIP: None
---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: postgres
spec:
  replicas: 1
  selector:
    matchLabels:
      app: postgres
  strategy:
    type: Recreate
  template:
    metadata:
      labels:
        app: postgres
    spec:
      containers:
        - image: docker.io/postgres:latest
          name: postgres
          env:
            - name: POSTGRES_PASSWORD
              value: postgres
          ports:
            - containerPort: 5432
              name: postgres
          volumeMounts:
            - name: postgres-persistent-storage
              mountPath: /var/lib/postgresql
      volumes:
        - name: postgres-persistent-storage
          persistentVolumeClaim:
            claimName: postgres-pv-claim

local-path-default-storageclass.yaml

apiVersion: storage.k8s.io/v1
kind: StorageClass

metadata:
  annotations:
    storageclass.kubernetes.io/is-default-class: "true"       
  name: default

provisioner: rancher.io/local-path
allowVolumeExpansion: true
reclaimPolicy: Delete
volumeBindingMode: WaitForFirstConsumer

mssql_entrypoint.sh

#!/bin/bash
bash -xc "sleep 30s && /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P sa2021PASSWORD -d master -i /docker-entrypoint-initdb.d/mssql_schema.sql" &
/opt/mssql/bin/sqlservr

mssql_schema.sql

CREATE DATABASE test;
GO
USE test;
GO
CREATE TABLE test (id int, value nvarchar(max));
GO
INSERT INTO test VALUES(1,'test'),(2,'test');
GO
CREATE LOGIN test WITH PASSWORD = 'test2021PASSWORD';
GO
CREATE USER test FOR LOGIN test;
GO
GRANT ALL ON DATABASE::test TO test;
GO
GRANT ALL ON test TO test;
GO

mysql_schema.sql

CREATE DATABASE IF NOT EXISTS test;
CREATE USER IF NOT EXISTS test IDENTIFIED BY 'test';
CREATE TABLE test.test(
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    value VARCHAR(100)
);
INSERT INTO test.test VALUES(1,'test1'),(2, 'test2');
GRANT ALL ON test.* TO test;

odbc.ini

[mysql]
Driver       = MySQL ODBC 8.0 Unicode Driver
SERVER       = mysql
PORT         = 3306
USER         = test
Password     = test
Database     = test

[postgres]
Driver = PostgreSQL Unicode
Servername = postgres
PORT = 5432
DATABASE = test
USERNAME = test
PASSWORD = test

[mssql]
Driver    = ODBC Driver 17 for SQL Server
# Server = [protocol:]server[,port]
Server   = tcp:mssql,1433

[oracle]
Driver = Oracle 21 ODBC driver
# Should reference the connection in the /opt/oracle/instantclient_21_3/network/admin/tnsnames.ora file
ServerName = oracle
UserID = test_user
Password = test
AggregateSQLType = FLOAT
Application Attributes = T
Attributes = W
BatchAutocommitMode = IfAllSuccessful
BindAsFLOAT = F
CacheBufferSize = 20
CloseCursor = F
DisableDPM = F
DisableMTS = T
DisableRULEHint = T
EXECSchemaOpt =
EXECSyntax = T
Failover = T
FailoverDelay = 10
FailoverRetryCount = 10
FetchBufferSize = 64000
ForceWCHAR = F
LobPrefetchSize = 8192
Lobs = T
Longs = T
MaxLargeData = 0
MaxTokenSize = 8192
MetadataIdDefault = F
QueryTimeout = T
ResultSets = T
SQLGetData extensions = F
SQLTranslateErrors = F
StatementCache = F
Translation DLL =
Translation Option = 0
UseOCIDescribeAny = F

oracle_schema.sql

ALTER SESSION SET "_ORACLE_SCRIPT"=true;

CREATE TABLESPACE test_tablespace
    DATAFILE 'test_tablespace.dbf'
    SIZE 1m;

CREATE USER test_user IDENTIFIED BY "test";

GRANT CREATE SESSION TO test_user;
GRANT CREATE TABLE TO test_user;
GRANT CREATE SESSION TO test_user;
GRANT CREATE TABLE TO test_user;
GRANT CREATE VIEW, CREATE PROCEDURE, CREATE SEQUENCE TO test_user;
ALTER USER test_user QUOTA UNLIMITED ON test_tablespace;
GRANT UNLIMITED TABLESPACE TO test_user;

CREATE TABLE TEST_USER.TEST (
    ID       NUMBER(5) PRIMARY KEY,
    VALUE    VARCHAR2(100) NOT NULL
)
TABLESPACE test_tablespace
STORAGE ( INITIAL 50K);

INSERT INTO TEST_USER.TEST (ID, VALUE) VALUES (1, 'test1');
INSERT INTO TEST_USER.TEST (ID, VALUE) VALUES (2, 'test2');
COMMIT;

postgres_schema.sql

CREATE USER test WITH PASSWORD 'test';
CREATE DATABASE test;
ALTER DATABASE test OWNER TO test;
GRANT ALL PRIVILEGES ON DATABASE test TO test;

\c test

DROP TABLE IF EXISTS test;
CREATE TABLE IF NOT EXISTS test(id SERIAL NOT NULL PRIMARY KEY, value VARCHAR(100));

INSERT INTO test(id, value) VALUES (1,'test1'), (2, 'test2');

ALTER TABLE test OWNER TO test;

run_docker_compose.sh

#!/bin/bash
docker-compose down
docker volume prune -f
docker system prune -f
docker pull docker.io/yandex/clickhouse-server:${CLICKHOUSE_VERSION:-latest}
docker-compose pull
docker-compose build clickhouse
docker-compose push clickhouse
docker-compose up -d mysql postgres mssql oracle
# need time to startup servers and apply schemas
while [[ "0" == $(docker-compose logs oracle | grep -c "DATABASE IS READY TO USE") ]]; do
  printf "."
  sleep 10
done

docker-compose ps -a
docker-compose up -d clickhouse
sleep 10
docker-compose exec clickhouse clickhouse-client -q "SELECT * FROM default.mysql_dict"
docker-compose exec clickhouse clickhouse-client -q "SELECT * FROM default.postgres_dict"
docker-compose exec clickhouse clickhouse-client -q "SELECT * FROM default.odbc_mssql_dict"
docker-compose exec clickhouse clickhouse-client -q "SELECT * FROM default.odbc_mysql_dict"
docker-compose exec clickhouse clickhouse-client -q "SELECT * FROM default.odbc_postgres_dict"
docker-compose exec clickhouse clickhouse-client -q "SELECT * FROM default.mysql_dict"

sleep 30
docker-compose logs oracle
docker-compose exec oracle bash -l -c 'lsnrctl status'
docker-compose exec clickhouse bash -c 'echo "SELECT * FROM TEST" | isql oracle -v'
docker-compose exec clickhouse clickhouse-client -q "SELECT * FROM default.odbc_oracle_dict"

run_k8s.sh

#!/bin/bash

# kubectl
if [[ ! -f /usr/bin/kubectl ]]; then
  sudo -H curl -sL "https://storage.googleapis.com/kubernetes-release/release/$(curl -s https://storage.googleapis.com/kubernetes-release/release/stable.txt)/bin/linux/amd64/kubectl" -o /usr/bin/kubectl
  sudo -H chmod +x /usr/bin/kubectl
fi

# k0s
if [[ -f /usr/local/bin/k0s ]]; then
  sudo -H bash -c "k0s stop || true"
else
  curl -sSLf https://get.k0s.sh | sudo sh
fi

sudo -H bash -c "k0s reset || true"
sudo -H k0s install controller --single
sudo -H k0s start
sleep 5
sudo -H k0s status
sudo -H k0s kubeconfig admin > ~/.kube/config


# PVC provisioner
kubectl apply -f https://raw.githubusercontent.com/rancher/local-path-provisioner/master/deploy/local-path-storage.yaml
kubectl apply -f ./local-path-default-storageclass.yaml

# create namespace
kubectl delete ns test || true
kubectl create ns test || true

# require for access to oracle enterprise image
# sudo -H docker login
# kubectl create -n test secret generic docker-auth --from-file=.dockerconfigjson=/root/.docker/config.json --type=kubernetes.io/dockerconfigjson

# related databases manifests
kubectl apply -n test -f ./k8s_oracle.yaml
kubectl apply -n test -f ./k8s_mssql.yaml
kubectl apply -n test -f ./k8s_mysql.yaml
kubectl apply -n test -f ./k8s_postgres.yaml

# need time to pull images (2-3Gb) and initialize system databases
echo "Wait when any pod created in test namespace"
while [[ "0" == $(kubectl get pods -n test | wc -l) ]]; do
  echo "."
  sleep 5
done

echo "Wait when all pods ready in test namespace"
while [[ "0" != $(kubectl get pods -n test | grep -c "0/1") ]]; do
  echo "."
  sleep 5
done

ORACLE_POD=$(kubectl get -n test pods -o name | grep oracle | cut -d "/" -f 2)
kubectl cp -n test ./oracle_schema.sql "$ORACLE_POD":/tmp/oracle_schema.sql
kubectl exec -n test "$ORACLE_POD" -- bash -l -c "su -p oracle -c \"printf \\\"@/tmp/oracle_schema.sql;\\nexit;\\n\\\" | \$ORACLE_HOME/bin/sqlplus / as sysdba\""

POSTGRES_POD=$(kubectl get -n test pods -o name | grep postgres | cut -d "/" -f 2)
kubectl cp -n test ./postgres_schema.sql "$POSTGRES_POD":/tmp/postgres_schema.sql
kubectl exec -n test "$POSTGRES_POD" -- bash -c 'psql -v ON_ERROR_STOP=1 --username "postgres" --no-password < /tmp/postgres_schema.sql'

MSSQL_POD=$(kubectl get -n test pods -o name | grep mssql | cut -d "/" -f 2)
kubectl cp -n test ./mssql_schema.sql "$MSSQL_POD":/tmp/mssql_schema.sql
kubectl exec -n test "$MSSQL_POD" -- /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P sa2021PASSWORD -d master -i /tmp/mssql_schema.sql

MYSQL_POD=$(kubectl get -n test pods -o name | grep mysql | cut -d "/" -f 2)
kubectl cp -n test ./mysql_schema.sql "$MYSQL_POD":/tmp/mysql_schema.sql
kubectl exec -n test "$MYSQL_POD" -- bash -c "mysql -u root -pmysql < /tmp/mysql_schema.sql"

# install clickhouse-operator to kube-system
kubectl apply -f https://raw.githubusercontent.com/Altinity/clickhouse-operator/master/deploy/operator/clickhouse-operator-install.yaml

# clickhouse ODBC manifest
kubectl apply -n test -f ./k8s_clickhouse_odbc.yaml

echo "Wait when all pods ready in test namespace"
while [[ "0" != $(kubectl get pods -n test | grep -c "0/1") ]]; do
  echo "."
  sleep 5
done

# clickhouse schema
CLICKHOUSE_POD=$(kubectl get pods -n test -o name | grep 0-0-0 | cut -d "/" -f 2)
kubectl cp -n test -c clickhouse ./clickhouse_schema.sql "$CLICKHOUSE_POD":/tmp/clickhouse_schema.sql
kubectl exec -n test "$CLICKHOUSE_POD" -c clickhouse -- bash -c "cat /tmp/clickhouse_schema.sql | clickhouse-client -mn --echo"

# check integrations
kubectl exec -n test "$CLICKHOUSE_POD" -c clickhouse -- clickhouse-client -q "SELECT * FROM default.mysql_dict"
kubectl exec -n test "$CLICKHOUSE_POD" -c clickhouse -- clickhouse-client -q "SELECT * FROM default.postgres_dict"
kubectl exec -n test "$CLICKHOUSE_POD" -c clickhouse -- clickhouse-client -q "SELECT * FROM default.odbc_mssql_dict"
kubectl exec -n test "$CLICKHOUSE_POD" -c clickhouse -- clickhouse-client -q "SELECT * FROM default.odbc_mysql_dict"
kubectl exec -n test "$CLICKHOUSE_POD" -c clickhouse -- clickhouse-client -q "SELECT * FROM default.odbc_postgres_dict"
kubectl exec -n test "$CLICKHOUSE_POD" -c clickhouse -- clickhouse-client -q "SELECT * FROM default.odbc_oracle_dict"

tnsnames.ora

oracle =
(
  DESCRIPTION =
  (
    ADDRESS_LIST =
      (ADDRESS =
        (PROTOCOL = TCP)
        (HOST = oracle)
        (PORT = 1521)
       )
  )
  (
    CONNECT_DATA = (SERVICE_NAME = XE)
  )
)