Clickhouse安装文档

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79

var_base=/kubernetes/helm
var_app=$var_base/clickhouse
var_version=3.6.3

mkdir -p $var_app
cd $var_app

helm repo add bitnami https://charts.bitnami.com/bitnami
helm repo update bitnami

helm search repo -l bitnami/clickhouse

helm pull bitnami/clickhouse --version $var_version
tar xf clickhouse-$var_version.tgz

helm upgrade --install --dry-run --debug \
  --namespace clickhouse \
  --create-namespace \
  --set global.storageClass=rook-cephfs \
  --set auth.username=admin \
  --set auth.password='TestPassword' \
  --set keeper.enabled=false \
  --set service.type=NodePort \
  --set ingress.enabled=false \
  --set persistence.enabled=true \
  --set persistence.size=50Gi \
  --set metrics.enabled=false \
  --set zookeeper.enabled=true \
  --set zookeeper.replicaCount=1 \
  --set zookeeper.service.ports.client=2181 \
  --set shards=1 \
  --set replicaCount=1 \
  clickhouse ./clickhouse

helm upgrade --install \
  --namespace clickhouse \
  --create-namespace \
  --set global.storageClass=rook-cephfs \
  --set auth.username=admin \
  --set auth.password='TestPassword' \
  --set keeper.enabled=false \
  --set service.type=NodePort \
  --set ingress.enabled=false \
  --set persistence.enabled=true \
  --set persistence.size=50Gi \
  --set metrics.enabled=false \
  --set zookeeper.enabled=true \
  --set zookeeper.replicaCount=1 \
  --set zookeeper.service.ports.client=2181 \
  --set shards=1 \
  --set replicaCount=1 \
  clickhouse ./clickhouse

helm upgrade --install \
  --namespace clickhouse \
  --create-namespace \
  --set global.storageClass=rook-cephfs \
  --set auth.username=admin \
  --set auth.password='TestPassword' \
  --set keeper.enabled=false \
  --set service.type=NodePort \
  --set ingress.enabled=false \
  --set persistence.enabled=true \
  --set persistence.size=20Gi \
  --set metrics.enabled=false \
  --set zookeeper.enabled=true \
  clickhouse ./clickhouse

export NODE_IP=$(kubectl get nodes --namespace clickhouse -o jsonpath="{.items[0].status.addresses[0].address}")
export NODE_PORT=$(kubectl get --namespace clickhouse -o jsonpath="{.spec.ports[0].nodePort}" services clickhouse)

echo "Username      : admin"
echo "Password      : $(kubectl get secret --namespace clickhouse clickhouse -o jsonpath="{.data.admin-password}" | base64 -d)"

watch kubectl --namespace clickhouse get pods

helm list --namespace clickhouse

helm uninstall clickhouse --namespace clickhouse

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200

git clone https://github.com/ClickHouse/clickhouse-jdbc-bridge.git
cd clickhouse-jdbc-bridge
cp ~/Downloads/ojdbc-12.1-SNAPSHOT.jar ~/Codes/GitHub/clickhouse-jdbc-bridge/
Dockerfile添加
#COPY ojdbc-12.1-SNAPSHOT.jar $JDBC_BRIDGE_HOME/drivers/

docker image rm peterydd/clickhouse-jdbc-bridge:v0.0.3
docker build -t peterydd/clickhouse-jdbc-bridge:v0.0.3 .
# or if you want to build the all-ine-one image
# docker build --build-arg revision=20.9.3 -f all-in-one.Dockerfile -t peterydd/clickhouse-jdbc-bridge:v0.0.3 .
docker run --name test1 --rm -it peterydd/clickhouse-jdbc-bridge:v0.0.3 /bin/bash
docker push peterydd/clickhouse-jdbc-bridge:v0.0.3

var_base=/kubernetes/helm
var_app=$var_base/clickhouse
var_version=3.6.3

mkdir -p $var_app
cd $var_app

cat <<EOF > ./clickhouse-jdbc-bridge-configmap.yaml
apiVersion: v1
kind: ConfigMap
metadata:
  name: jdbc-datasource-cm
  namespace: clickhouse
  labels:
    app: clickhouse-jdbc-bridge
    version: v1
data:
  oracle-prod.json: |
    {
      "\$schema": "../datasource.jschema",
      "oracle-prod": {
        "aliases": [
          "test-prod"
        ],
        "driverUrls": [
          "/etc/clickhouse-jdbc-bridge/drivers/12.2.0.1/ojdbc8-12.2.0.1.jar"
        ],
        "driverClassName": "oracle.jdbc.driver.OracleDriver",
        "jdbcUrl": "jdbc:oracle:thin:@//oracle21c-xe.test-prod:1521/xepdb1",
        "username": "test",
        "password": "Password",
        "connectionTestQuery": ""
      }
    }
  oracle-dev.json: |
    {
      "\$schema": "../datasource.jschema",
      "oracle-dev": {
        "aliases": [
          "test-dev"
        ],
        "driverUrls": [
          "/etc/clickhouse-jdbc-bridge/drivers/12.2.0.1/ojdbc8-12.2.0.1.jar"
        ],
        "driverClassName": "oracle.jdbc.driver.OracleDriver",
        "jdbcUrl": "jdbc:oracle:thin:@//oracle21c-xe.test-uat:1521/xepdb1",
        "username": "test",
        "password": "TestPassword",
        "connectionTestQuery": ""
      }
    }
  mysql.json: |
    {
      "\$schema": "../datasource.jschema",
      "mysql": {
        "aliases": [
          "test-mysql"
        ],
        "driverUrls": [
          "/etc/clickhouse-jdbc-bridge/drivers/mysql-connector-java-8.0.28.jar"
        ],
        "driverClassName": "com.mysql.cj.jdbc.Driver",
        "jdbcUrl": "jdbc:mysql://mysql-headless.test-prod:3306",
        "username": "testcdr_readonly",
        "password": "TestcdrPassword",
        "maximumPoolSize": 5
      }
    }
EOF

kubectl apply -f ./clickhouse-jdbc-bridge-configmap.yaml
kubectl -n clickhouse get configmap jdbc-datasource-cm -o yaml

cat <<EOF > ./clickhouse-jdbc-bridge-deployment.yaml
apiVersion: apps/v1
kind: Deployment
metadata:
  name: clickhouse-jdbc-bridge
  namespace: clickhouse
  labels:
    app: clickhouse-jdbc-bridge
    version: v1
spec:
  replicas: 1
  selector:
    matchLabels:
      app: clickhouse-jdbc-bridge
      version: v1
  template:
    metadata:
      name: clickhouse-jdbc-bridge
      labels:
        app: clickhouse-jdbc-bridge
        version: v1
    spec:
      volumes:
        - name: tz-config
          hostPath:
            path: /usr/share/zoneinfo/America/Vancouver
        - name: jdbc-datasource-oracle-dev
          configMap:
            name: jdbc-datasource-cm
            items:
              - key: "oracle-dev.json"
                path: "oracle-dev.json"
        - name: jdbc-datasource-oracle-prod
          configMap:
            name: jdbc-datasource-cm
            items:
              - key: "oracle-prod.json"
                path: "oracle-prod.json"
        - name: jdbc-datasource-mysql
          configMap:
            name: jdbc-datasource-cm
            items:
              - key: "mysql.json"
                path: "mysql.json"
      containers:
        - name: clickhouse-jdbc-bridge
          image: peterydd/clickhouse-jdbc-bridge:v0.0.3
          imagePullPolicy: IfNotPresent
          ports:
            - name: jdbc
              protocol: TCP
              containerPort: 9019
          volumeMounts:
            - name: tz-config
              mountPath: /etc/localtime
            - name: jdbc-datasource-oracle-dev
              mountPath: /etc/clickhouse-jdbc-bridge/config/datasources/oracle-dev.json
              subPath: oracle-dev.json
            - name: jdbc-datasource-oracle-prod
              mountPath: /etc/clickhouse-jdbc-bridge/config/datasources/oracle-prod.json
              subPath: oracle-prod.json
            - name: jdbc-datasource-mysql
              mountPath: /etc/clickhouse-jdbc-bridge/config/datasources/mysql.json
              subPath: mysql.json
EOF
kubectl apply -f ./clickhouse-jdbc-bridge-deployment.yaml
kubectl -n clickhouse rollout restart deployment clickhouse-jdbc-bridge
watch kubectl get pods -n clickhouse

cat <<EOF > ./clickhouse-jdbc-bridge-service.yaml
apiVersion: v1
kind: Service
metadata:
  name: clickhouse-jdbc-bridge
  namespace: clickhouse
  labels:
    app: clickhouse-jdbc-bridge
    version: v1
spec:
  selector:
    app: clickhouse-jdbc-bridge
    version: v1
  type: ClusterIP
  ports:
  - protocol: TCP
    name: jdbc
    port: 9019
    targetPort: jdbc
EOF
kubectl apply -f ./clickhouse-jdbc-bridge-service.yaml
watch kubectl get svc -n clickhouse

kubectl -n clickhouse edit configmap clickhouse
      <!-- Jdbc_bridge configuration -->
      <jdbc_bridge>
        <host>clickhouse-jdbc-bridge</host>
        <port>9019</port>
      </jdbc_bridge>

kubectl -n clickhouse rollout restart statefulset clickhouse-shard0

# SELECT * FROM jdbc('oracle-dev', 'test', 'test_banner');

SELECT * FROM jdbc('oracle-dev', 'select count(1) from TEST.TEST_BANNER');
SELECT * FROM jdbc('oracle-dev', 'select * from test_order');

SELECT * FROM jdbc('mysql', 'select count(1) from testmobile.test_finalmatch');
SELECT * FROM jdbc('mysql', 'select * from testmobile.test_finalmatch limit 10000');
SELECT * FROM jdbc('mysql', 'testmobile', 'test_finalmatch');

SELECT * FROM jdbc('oracle-dev', 'select count(1) from TEST_ORDER');
SELECT * FROM jdbc('oracle-prod', 'select count(1) from TEST_ORDER');
select count(1) from TEST_ORDER;
select * from TEST_ORDER;

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114

  SELECT * FROM jdbc('oracle-dev', 'select * from test_banner');

CREATE DATABASE IF NOT EXISTS test;

CREATE TABLE test.test_banner (
  ID UInt32, 
  TITLE String,
  CHANNEL String,
  PIC_LOCATION String,
  IS_GROUP Float32, 
  MAIN_PIC_URL String,
  MAIN_PIC_URL_EN String,
  LINK_TO String,
  LINK_TO_EN String,
  BASE_PIC_URL String,
  START_TIME String,
  END_TIME String,
  IS_ENABLE Float32, 
  PIC_ORDER Float32, 
  CREATE_TIME DateTime, 
  UPDATE_TIME DateTime, 
  IS_DELETED Float32
)
ENGINE = MergeTree()
PRIMARY KEY ID;

select * from test.test_banner;

INSERT INTO test.test_banner (
ID,
TITLE,
CHANNEL,
PIC_LOCATION,
IS_GROUP,
MAIN_PIC_URL,
MAIN_PIC_URL_EN,
LINK_TO,
LINK_TO_EN,
BASE_PIC_URL,
START_TIME,
END_TIME,
IS_ENABLE,
PIC_ORDER,
CREATE_TIME,
UPDATE_TIME,
IS_DELETED
)
SELECT * FROM jdbc('oracle-dev', 'select * from test_banner');




CREATE TABLE test_order (
ORDER_ID Int32, 
ORDER_NO String, 
FIRST_NAME String, 
LAST_NAME String, 
EMAIL String, 
USER_ID Int32, 
DISTRIBUTOR_ID Int32, 
ORDER_STATE Int32, 
ORDER_TYPE Int32, 
BIZ_CODE String, 
CHANNEL String, 
PAY_METHOD String, 
TAX_ZIP_CODE String, 
TOTAL_AMOUNT Float32, 
COUPON_AMOUNT Float32, 
TAX_AMOUNT Float32, 
PAY_AMOUNT Float32, 
PAY_NO String, 
COUPON_CODE String, 
BILLING_ADDR_ID Int32, 
REFUND_STATE Int32, 
REFUND_AMOUNT Float32, 
ACTIVITY_CODE String, 
REFER_CODE String, 
AUTO_NEW Int32, 
IS_LOGISTICS Int32, 
LOGISTICS_ID Int32, 
CONTACT_PHONE String, 
LANGUAGE String, 
CREATE_TIME DateTime, 
UPDATE_TIME DateTime, 
IS_DELETED Int32, 
COLLEGE_ID Int32, 
GRADE_NAME String, 
DEPARTURE_DATE String, 
PLAT_FORM String
)
ENGINE = MergeTree()
PRIMARY KEY ORDER_ID;


INSERT INTO test.test_banner (
ID,
TITLE,
CHANNEL,
PIC_LOCATION,
IS_GROUP,
MAIN_PIC_URL,
MAIN_PIC_URL_EN,
LINK_TO,
LINK_TO_EN,
BASE_PIC_URL,
START_TIME,
END_TIME,
IS_ENABLE,
PIC_ORDER,
CREATE_TIME,
UPDATE_TIME,
IS_DELETED
)
SELECT * FROM jdbc('oracle-dev', 'select * from test_banner');