一、需求
#1.创建一个新的clickhouse数据库，并同步chdm数据到新库中
#2.新建数据库用户，对新库拥有所有权限
二、同步数据
1.创建数据库
#1.进入clickhouse数据库
[root@stg-ck001 ~]$ clickhouse-client
ClickHouse client version 20.8.3.18.
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 20.8.3 revision 54438.
#2.查看数据库
stg-ck001 :) show databases
SHOW DATABASES
┌─name───────────────────────────┐
│ _temporary_and_external_tables │
│ chdm │ │
│ default │
│ jdl │
│ system │
└────────────────────────────────┘
6 rows in set. Elapsed: 0.001 sec.
#3.创建数据库
stg-ck001 :) create database chdmtest
#4.查看数据库
stg-ck001 :) show databases
SHOW DATABASES
┌─name───────────────────────────┐
│ _temporary_and_external_tables │
│ chdm │
│ chdmtest │
│ default │
│ jdl │
│ system │
└────────────────────────────────┘
6 rows in set. Elapsed: 0.001 sec.
2.同步chdm库的表
#1.使用chdm库
stg-ck001 :) use chdm
USE chdm
Ok.
0 rows in set. Elapsed: 0.001 sec.
#2.查看chdm库表
stg-ck001 :) show tables
SHOW TABLES
┌─name────────────────────┐
│ COHORTID_MJNID │
│ COHORTID_MJNID_TMP │
│ DMP_ENCRYPT │
│ DM_TAG_ALL │
│ DM_TAG_ALL1 │
│ DM_TAG_BASE │
│ DM_TAG_DIAMOND_MINE_NEW │
│ DM_TAG_LOYALTY_MALL │
│ DM_TAG_LOYALTY_MALL1 │
│ DM_TAG_LOYALTY_MALL_02 │
│ DM_TAG_MINI_CRM_NEW │
│ DM_TAG_MINI_CRM_NEW1 │
│ STATIC_COHORT │
│ marketing_flow │
│ marketing_flow2 │
│ marketing_flow_22 │
└─────────────────────────┘
16 rows in set. Elapsed: 0.001 sec.
#3.同步chdm表
stg-ck001 :) CREATE TABLE IF NOT EXISTS chdmtest.COHORTID_MJNID as chdm.COHORTID_MJNID;
stg-ck001 :) CREATE TABLE IF NOT EXISTS chdmtest.COHORTID_MJNID_TMP as chdm.COHORTID_MJNID_TMP;
stg-ck001 :) CREATE TABLE IF NOT EXISTS chdmtest.DMP_ENCRYPT as chdm.DMP_ENCRYPT;
stg-ck001 :) CREATE TABLE IF NOT EXISTS chdmtest.DM_TAG_ALL as chdm.DM_TAG_ALL ;
stg-ck001 :) CREATE TABLE IF NOT EXISTS chdmtest.DM_TAG_ALL1 as chdm.DM_TAG_ALL1 ;
stg-ck001 :) CREATE TABLE IF NOT EXISTS chdmtest.DM_TAG_BASE as chdm.DM_TAG_BASE ;
stg-ck001 :) CREATE TABLE IF NOT EXISTS chdmtest.DM_TAG_DIAMOND_MINE_NEW as chdm.DM_TAG_DIAMOND_MINE_NEW;
stg-ck001 :) CREATE TABLE IF NOT EXISTS chdmtest.DM_TAG_LOYALTY_MALL as chdm.DM_TAG_LOYALTY_MALL;
stg-ck001 :) CREATE TABLE IF NOT EXISTS chdmtest.DM_TAG_LOYALTY_MALL1 as chdm.DM_TAG_LOYALTY_MALL1;
stg-ck001 :) CREATE TABLE IF NOT EXISTS chdmtest.DM_TAG_LOYALTY_MALL_02 as chdm.DM_TAG_LOYALTY_MALL_02;
stg-ck001 :) CREATE TABLE IF NOT EXISTS chdmtest.DM_TAG_MINI_CRM_NEW as chdm.DM_TAG_MINI_CRM_NEW;
stg-ck001 :) CREATE TABLE IF NOT EXISTS chdmtest.DM_TAG_MINI_CRM_NEW1 as chdm.DM_TAG_MINI_CRM_NEW1;
stg-ck001 :) CREATE TABLE IF NOT EXISTS chdmtest.STATIC_COHORT as chdm.STATIC_COHORT;
stg-ck001 :) CREATE TABLE IF NOT EXISTS chdmtest.marketing_flow as chdm.marketing_flow;
stg-ck001 :) CREATE TABLE IF NOT EXISTS chdmtest.marketing_flow2 as chdm.marketing_flow2;
stg-ck001 :) CREATE TABLE IF NOT EXISTS chdmtest.marketing_flow_22 as chdm.marketing_flow_22;
3.导出chdm表数据
#导出数据
clickhouse-client -h 127.0.0.1 --database="chdm" --query="select * from chdm.COHORTID_MJNID FORMAT CSV" > COHORTID_MJNID.csv
clickhouse-client -h 127.0.0.1 --database="chdm" --query="select * from chdm.COHORTID_MJNID_TMP FORMAT CSV" > COHORTID_MJNID_TMP.csv
clickhouse-client -h 127.0.0.1 --database="chdm" --query="select * from chdm.DMP_ENCRYPT FORMAT CSV" > DMP_ENCRYPT.csv
clickhouse-client -h 127.0.0.1 --database="chdm" --query="select * from chdm.DM_TAG_ALL FORMAT CSV" > DM_TAG_ALL.csv
clickhouse-client -h 127.0.0.1 --database="chdm" --query="select * from chdm.DM_TAG_ALL1 FORMAT CSV" > DM_TAG_ALL1.csv
clickhouse-client -h 127.0.0.1 --database="chdm" --query="select * from chdm.DM_TAG_BASE FORMAT CSV" > DM_TAG_BASE.csv
clickhouse-client -h 127.0.0.1 --database="chdm" --query="select * from chdm.DM_TAG_DIAMOND_MINE_NEW FORMAT CSV" > DM_TAG_DIAMOND_MINE_NEW.csv
clickhouse-client -h 127.0.0.1 --database="chdm" --query="select * from chdm.DM_TAG_LOYALTY_MALL FORMAT CSV" > DM_TAG_LOYALTY_MALL.csv
clickhouse-client -h 127.0.0.1 --database="chdm" --query="select * from chdm.DM_TAG_LOYALTY_MALL1 FORMAT CSV" > DM_TAG_LOYALTY_MALL1.csv
clickhouse-client -h 127.0.0.1 --database="chdm" --query="select * from chdm.DM_TAG_LOYALTY_MALL_02 FORMAT CSV" > DM_TAG_LOYALTY_MALL_02.csv
clickhouse-client -h 127.0.0.1 --database="chdm" --query="select * from chdm.DM_TAG_MINI_CRM_NEW FORMAT CSV" > DM_TAG_MINI_CRM_NEW.csv
clickhouse-client -h 127.0.0.1 --database="chdm" --query="select * from chdm.DM_TAG_MINI_CRM_NEW1 FORMAT CSV" > DM_TAG_MINI_CRM_NEW1.csv
clickhouse-client -h 127.0.0.1 --database="chdm" --query="select * from chdm.STATIC_COHORT FORMAT CSV" > STATIC_COHORT.csv
clickhouse-client -h 127.0.0.1 --database="chdm" --query="select * from chdm.marketing_flow FORMAT CSV" > marketing_flow.csv
clickhouse-client -h 127.0.0.1 --database="chdm" --query="select * from chdm.marketing_flow2 FORMAT CSV" > marketing_flow2.csv
clickhouse-client -h 127.0.0.1 --database="chdm" --query="select * from chdm.marketing_flow_22 FORMAT CSV" > marketing_flow_22.csv
4.导入数据
#导入数据
clickhouse-client -h 127.0.0.1 --database="chdmtest" --query="insert into chdmtest.COHORTID_MJNID FORMAT CSV" < COHORTID_MJNID.csv
clickhouse-client -h 127.0.0.1 --database="chdmtest" --query="insert into chdmtest.COHORTID_MJNID_TMP FORMAT CSV" < COHORTID_MJNID_TMP.csv
clickhouse-client -h 127.0.0.1 --database="chdmtest" --query="insert into chdmtest.DMP_ENCRYPT FORMAT CSV" < DMP_ENCRYPT.csv
clickhouse-client -h 127.0.0.1 --database="chdmtest" --query="insert into chdmtest.DM_TAG_ALL FORMAT CSV" < DM_TAG_ALL.csv
clickhouse-client -h 127.0.0.1 --database="chdmtest" --query="insert into chdmtest.DM_TAG_ALL1 FORMAT CSV" < DM_TAG_ALL1.csv
clickhouse-client -h 127.0.0.1 --database="chdmtest" --query="insert into chdmtest.DM_TAG_BASE FORMAT CSV" < DM_TAG_BASE.csv
clickhouse-client -h 127.0.0.1 --database="chdmtest" --query="insert into chdmtest.DM_TAG_DIAMOND_MINE_NEW FORMAT CSV" < DM_TAG_DIAMOND_MINE_NEW.csv
clickhouse-client -h 127.0.0.1 --database="chdmtest" --query="insert into chdmtest.DM_TAG_LOYALTY_MALL FORMAT CSV" < DM_TAG_LOYALTY_MALL.csv
clickhouse-client -h 127.0.0.1 --database="chdmtest" --query="insert into chdmtest.DM_TAG_LOYALTY_MALL1 FORMAT CSV" < DM_TAG_LOYALTY_MALL1.csv
clickhouse-client -h 127.0.0.1 --database="chdmtest" --query="insert into chdmtest.DM_TAG_LOYALTY_MALL_02 FORMAT CSV" < DM_TAG_LOYALTY_MALL_02.csv
clickhouse-client -h 127.0.0.1 --database="chdmtest" --query="insert into chdmtest.DM_TAG_MINI_CRM_NEW FORMAT CSV" < DM_TAG_MINI_CRM_NEW.csv
clickhouse-client -h 127.0.0.1 --database="chdmtest" --query="insert into chdmtest.DM_TAG_MINI_CRM_NEW1 FORMAT CSV" < DM_TAG_MINI_CRM_NEW1.csv
clickhouse-client -h 127.0.0.1 --database="chdmtest" --query="insert into chdmtest.STATIC_COHORT FORMAT CSV" < STATIC_COHORT.csv
clickhouse-client -h 127.0.0.1 --database="chdmtest" --query="insert into chdmtest.marketing_flow FORMAT CSV" < marketing_flow.csv
clickhouse-client -h 127.0.0.1 --database="chdmtest" --query="insert into chdmtest.marketing_flow2 FORMAT CSV" < marketing_flow2.csv
clickhouse-client -h 127.0.0.1 --database="chdmtest" --query="insert into chdmtest.marketing_flow_22 FORMAT CSV" < marketing_flow_22.csv
5.验证数据
#1.查看chdmtest库下某张表总量
stg-ck001 :) use chdmtest
stg-ck001 :) select count(*) from COHORTID_MJNID
SELECT count(*)
FROM COHORTID_MJNID
┌──count()─┐
│ 18283146 │
└──────────┘
1 rows in set. Elapsed: 0.001 sec.
#2.查看chdm库下某张表总量
stg-ck001 :) use chdm
USE chdm
Ok.
0 rows in set. Elapsed: 0.001 sec.
stg-ck001 :) select count(*) from COHORTID_MJNID
SELECT count(*)
FROM COHORTID_MJNID
┌──count()─┐
│ 18283146 │
└──────────┘
1 rows in set. Elapsed: 0.001 sec.
#3.通过对比上述表数量发下表数据完全一致
三、用户权限管理
1.查看数据库相关用户
stg-ck001 :) show users
SHOW USERS
┌─name─────┐
│ Gatorade │
│ ch_dba │
│ chdmuser │
│ default │
│ jdladmin │
└──────────┘
6 rows in set. Elapsed: 0.001 sec.
2.创建用户角色
stg-ck001 :) create role chdmtest
stg-ck001 :) grant all on chdmtest.* to chdmtest
3.创建用户
stg-ck001 :) create user chdmtest IDENTIFIED WITH PLAINTEXT_PASSWORD BY '密码'
4.授权角色给用户
stg-ck001 :) grant chdmtest to chdmtest
四、验证登录
#1.表示登录成功
[root@stg-ck001 ~]$ clickhouse-client -h 127.0.0.1 -u chdmtest --password 密码
ClickHouse client version 20.11.4.13 (official build).
Connecting to 127.0.0.1:9000 as user chdmtest.
Connected to ClickHouse server version 20.11.4 revision 54442.
stg-ck001 :)