clickhouse linux安装使用以及Oracle数据库导入

安装

下载clickhouse
yum install clickhouse-server clickhouse-client
编辑配置文档
vi /etc/clickhouse-server/config.xml
sudo rpm --import https://repo.clickhouse.tech/CLICKHOUSE-KEY.GPG
sudo -u clickhouse clickhouse-server --config-file=/etc/clickhouse-server/config.xml

su -s /bin/sh 'clickhouse' -c '/usr/bin/clickhouse-server --config-file /etc/clickhouse-server/confi--pid-file /var/run/clickhouse-server/clickhouse-server.pid --daemon'

[root@izbp15488qcahuq3k0n0qiz ~]# ps -ef | grep clickhouse
clickho+ 17234     1  0 15:23 ?        00:00:00 clickhouse-watchdog        --config=/etc/clickhouse-server/config.xml --pid-file=/run/clickhouse-server/clickhouse-server.pid
clickho+ 17235 17234  2 15:23 ?        00:00:02 /usr/bin/clickhouse-server --config=/etc/clickhouse-server/config.xml --pid-file=/run/clickhouse-server/clickhouse-server.pid
root     17889 14123  0 15:25 pts/0    00:00:00 grep --color=auto clickhouse

简单查询

/usr/bin/./clickhouse-client 进入客户端

[root@izbp15488qcahuq3k0n0qiz ~]# /usr/bin/./clickhouse-client
ClickHouse client version 21.8.4.51 (official build).
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 21.8.4 revision 54449.

izbp15488qcahuq3k0n0qiz :) select 1;

SELECT 1

Query id: 62d7634c-c267-44e9-a6bd-734e29d07bfc

┌─1─┐
│ 1 │
└───┘

1 rows in set. Elapsed: 0.003 sec.

izbp15488qcahuq3k0n0qiz :) select version();

SELECT version()

Query id: e1329c81-add7-43d3-b090-207dd34226ca

┌─version()─┐
│ 21.8.4.51 │
└───────────┘

1 rows in set. Elapsed: 0.003 sec.

izbp15488qcahuq3k0n0qiz :) show databases;

SHOW DATABASES

Query id: 79800762-98a2-4e75-9898-566ad5212e29

┌─name────┐
│ default │
│ system  │
└─────────┘

2 rows in set. Elapsed: 0.003 sec.

izbp15488qcahuq3k0n0qiz :) use default ;

USE default

Query id: dc4481c7-6ea9-4cc1-8f68-48c013cb0e45

Ok.

0 rows in set. Elapsed: 0.001 sec.

izbp15488qcahuq3k0n0qiz :) show tables;

SHOW TABLES

Query id: 7108c4f1-cc83-4c5f-8712-b217b74c095a

Ok.

0 rows in set. Elapsed: 0.002 sec.

数据导入

建表

CREATE TABLE  FDM_QUERY_E_BASEINFO  
   (	 PRIPID   String , 
	 UNISCID_REGNO   Nullable(String),  #允许空值，不加Nullable会默认为非空
	 REGORG   Nullable(String), 
	 DOMDISTRICT   Nullable(String), 
	 LOCALADM   Nullable(String), 
	 REGCAP Nullable(Decimal(24,6)), 
	RECCAP Nullable(Decimal(18,6)), 
	 ENT_SCALE  Nullable(String), 
	 BUSST   Nullable(String), 
	 PROFIT_FLAG   Nullable(String), 
	 RISK_LEVEL   Nullable(String), 
	 REGSTATE   Nullable(String), 
	 ESTDATE  Nullable(DATE), 
	 ENT_AGE  Nullable(Int256),
	 CANDATE  Nullable(DATE), 
	 ENTTYPE1    Nullable(String),
	 ENTTYPE_NAME1    Nullable(String),
	 ENTTYPE2    Nullable(String),
	 ENTTYPE_NAME2    Nullable(String),
	 ENTTYPE3    Nullable(String),
	 ENTTYPE_NAME3    Nullable(String),
	 ENTTYPE4    Nullable(String),
	 ENTTYPE_NAME4    Nullable(String),
	 ENTTYPE5    Nullable(String),
	 ENTTYPE_NAME5    Nullable(String),
	 INDUSTRYCO   Nullable(String), 
	 INDUSTRY  Nullable(Int256),
	 INDUSTRY_NAME   Nullable(String), 
	 INDUSTRYPHY   Nullable(String), 
	 INDUSTRYPHY_NAME   Nullable(String), 
	 INDUSTRYPHY_B   Nullable(String), 
	 INDUSTRYPHY_B_NAME   Nullable(String), 
	 INDUSTRYPHY_C   Nullable(String), 
	 INDUSTRYPHY_C_NAME   Nullable(String), 
	 OPA_CNT  Nullable(Int256),
	 OPA_OUT_CNT  Nullable(Int256),
	 DIS_CNT  Nullable(Int256),
	 DIS_OUT_CNT  Nullable(Int256),
	 ALTER_CNT  Nullable(Int256),
	 CURRYEAR_ALTER_CNT  Nullable(Int256),
	 CHECK_CNT  Nullable(Int256),
	 CURRYEAR_CHECK_CNT  Nullable(Int256),
	 S_EXT_DATATIME  DATE
   )  ENGINE = MergeTree(S_EXT_DATATIME, (PRIPID), 8192);   # S_EXT_DATATIME时间字段  PRIPID主键 均不能为空 8192默认值

具体数据类型可参考【篇三|ClickHouse的数据类型】

下载相关驱动
- 下载 https://github.com/ClickHouse/clickhouse-jdbc-bridge/releases/download/v2.0.3/clickhouse-jdbc-bridge-2.0.3-shaded.jar
- 下载 https://maven.xwiki.org/externals/com/oracle/jdbc/ojdbc8/12.2.0.1/ojdbc8-12.2.0.1.jar（与导入Oracle数据库版本相对应）

创建文件目录结构

[root@localhost clickhouse-jdbc]# tree
.
├── clickhouse-jdbc-bridge-2.0.3-shaded.jar
├── config
│   ├── darasources
│   └── oralce.json
├── drivers
│   └── ojdbc8-12.2.0.1.jar
└── logs
    └── out.log (自动创建)

4 directories, 4 files

vim config/datasources/oracle.json 在这个json里面配置对应的连接

{
  "oracle11": {
    "driverClassName":"oracle.jdbc.OracleDriver",
    "jdbcUrl": "jdbc:oracle:thin:<username>/<password>@//<host>:<ip>/<server>",
    "ionFailTimeout": 0,
    "minimumIdle": 0,
    "maximumPoolSize": 10
  }
}

bridge的启动和日志报错的排查
后台运行，日志保存到logs目录下
nohup java -Duser.timezone=CN -jar clickhouse-jdbc-bridge-2.0.3-shaded.jar >> ./logs/out.log 2>&1 &
-Duser.timezone=CN 是在请求oracle11 的时候，会有时区的报错添加这个报错消失
Caused by: java.sql.SQLException: ORA-00604: error occurred at
recursive SQL level 1 ORA-01882: timezone region not found
导入数据
insert into TEST select * from jdbc('jdbc:oracle:thin:<username>/<password>@//<host>:1521/<servers>','SELECT * FROM TEST');

FAQ

clickhouse 对查询字段、函数的大小写敏感，之前的字段都是大写，后端写的接口中字段均为小写，无法调用，故新建一个表，字段恢复小写。

create table  fdm_query_e_baseinfo2  
   (	 pripid   String , 
	 uniscid_regno   Nullable(String), 
	 regorg   Nullable(String), 
	 domdistrict   Nullable(String), 
	 localadm   Nullable(String), 
	 regcap Nullable(Decimal(24,6)), 
	reccap Nullable(Decimal(18,6)), 
	 ent_scale  Nullable(String), 
	 busst   Nullable(String), 
	 profit_flag   Nullable(String), 
	 risk_level   Nullable(String), 
	 regstate   Nullable(String), 
	 estdate  Nullable(Date), 
	 ent_age  Nullable(Int256),
	 candate  Nullable(Date), 
	 enttype1    Nullable(String),
	 enttype_name1    Nullable(String),
	 enttype2    Nullable(String),
	 enttype_name2    Nullable(String),
	 enttype3    Nullable(String),
	 enttype_name3    Nullable(String),
	 enttype4    Nullable(String),
	 enttype_name4    Nullable(String),
	 enttype5    Nullable(String),
	 enttype_name5    Nullable(String),
	 industryco   Nullable(String), 
	 industry  Nullable(Int256),
	 industry_name   Nullable(String), 
	 industryphy   Nullable(String), 
	 industryphy_name   Nullable(String), 
	 industryphy_b   Nullable(String), 
	 industryphy_b_name   Nullable(String), 
	 industryphy_c   Nullable(String), 
	 industryphy_c_name   Nullable(String), 
	 opa_cnt  Nullable(Int256),
	 opa_out_cnt  Nullable(Int256),
	 dis_cnt  Nullable(Int256),
	 dis_out_cnt  Nullable(Int256),
	 alter_cnt  Nullable(Int256),
	 curryear_alter_cnt  Nullable(Int256),
	 check_cnt  Nullable(Int256),
	 curryear_check_cnt  Nullable(Int256),
	 s_ext_datatime  Date
   )  engine = MergeTree(s_ext_datatime, (pripid), 8192);

再将原表修改表名rename table wisdom.FDM_QUERY_E_BASEINFO to wisdom.FDM_QUERY_E_BASEINFO2;做备份；
将新表表名替换rename table wisdom.fdm_query_e_baseinfo2 to wisdom.FDM_QUERY_E_BASEINFO;

原文链接：https://blog.csdn.net/qq_32392597/article/details/119870068