- 《自开发了一款短视频去水印小程序》(2023年06月06日)
- 《es 字符串时间格式的字符串怎么做小时查询》(2024年04月11日)
先在clickhouse建表和库
CREATE TABLE "xxx"
(
"USER_ID" String,
"DOOR_INDEX_CODE" String,
"DOOR_NAME" String,
"IN_OUT_TYPE" String,
"ORG_INDEX_CODE" String,
"ORG_INDEX_NAME" String,
"ACADEMY" String,
"BIRTHPLACE" String,
"CENSUS_REGISTER" String,
"CERT_NO" String,
"COMPANY_UNIT" String,
"DORMITOR" String,
"EDUCATION" String,
"EMAIL" String,
"HOUSE_HOLD_REL" String,
"SFTYPE" String,
"MOBILE" String,
"NATION" String,
"ORGID" String,
"PERSON_ID" String,
"PERSON_NAME" String,
"SEX" String,
"CERTTYPE" String,
"EVENT_TIME" String,
"CARD_NO" String,
"IS_MEN" String,
"JOB_NO" String,
"DEV_INDEX_CODE" String,
"DEV_NAME" String,
"DOOR_REGION_INDEX_CODE" String,
"PIC_URI" String,
"ORG_NAME" String,
"EVENT_TYPE" String,
"RECEIVE_TIME" String,
"CREATE_TIME" String,
"CREATE_USER" String,
"UPDATE_TIME" String,
"UPDATE_USER" String,
"ID" String,
"IS_DELETE" String DEFAULT '0',
"ORG_ID" String,
"EVENT_ID" String DEFAULT ''
)
ENGINE = MergeTree()
PRIMARY KEY ("ID")
clickhouse-client 导入
INSERT INTO qwuser.B_T_PEOPLE_INOUT_LOGS
FROM INFILE '/root/sql/sql.sql' FORMAT MySQLDump
clickhouse常用操作命令
启动Server服务
systemctl start clickhouse-server
1
重启server
systemctl restart clickhouse-server
1
停止server
systemctl stop clickhouse-server
1
client命令行连接-多命令行模式
clickhouse-client -m --password <密码>
1
client命令行远程连接
clickhouse-client --host 192.168.45.10 --port 9000 --database default --user default --password “”
1
client命令行执行sql文件
clickhouse-client --user default --password 密码 -d default --multiquery < /root/temp.sql
- 部署clickhouse-jdbc-bridge
clickhouse-jdbc-bridge是通过jdbc将Clickhouse与其他数据沟通的桥梁,可将其他数据库数据导入clickhouse中,也可以直接在clickhouse中访问其他数据库。
1.1 部署环境
Ubuntu 16.04 64位
1.2 重新编译bridge
git clone https://github.com/ClickHouse/clickhouse-jdbc-bridge.git
cd clickhouse-jdbc-bridge
mvn -Drevision=2.0.0 package
1.3 下载Microsoft JDBC驱动
把下载的驱动放到clickhouse-jdbc-bridge目录下的drivers子目录
本次JDBC版本为:mssql-jdbc-8.4.1.jre14.jar
注意:jdbc版本需与Ubuntu环境中的java版本一致
- 配置clickhouse-jdbc-bridge
cd target
mkdir -p config/datasources #创建config目录及datasources子目录用于存放配置
在datasources目录下创建数据源配置文件,msjdbc.json 文件名尽量与配置中的Datasource名一致,加入以下配置:
{
"$schema": "../datasource.jschema",
"msjdbc": {
"driverUrls": [
"/data/clickhouse-jdbc-bridge/drivers/mysql-connector-j-8.0.33.jar"
],
"driverClassName": "com.mysql.jdbc.Driver",
"jdbcUrl": "jdbc:mysql://xx.xx/test?compress=false&useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=CONVERT_TO_NULL&serverTimezone=Asia/Shanghai&nullCatalogMeansCurrent=true",
"username": "root",
"password": "xxx",
"connectionTestQuery": ""
}
}
- 运行clickhouse-jdbc-bridge
cd target
java -jar clickhouse-jdbc-bridge-2.0.0.jar
如果成功运行,会出现已加载的数据源信息,如前面的msjdbc。
- 使用clickhouse-jdbc-bridge迁移数据
4.1 先用DBeaver或clickhouse-client创建表
CREATE TABLE test(id String, operateTime DateTime) ENGINE = MergeTree
PARTITION BY toYYYYMM(operateTime)
ORDER BY id
SETTINGS index_granularity = 8192;
4.2 进入clickhouse-client 执行insert into语句
INSERT INTO test SELECT * FROM jdbc('msjdbc', 'SELECT * FROM dbo.test');
到/etc/clickhouse-server目录下的config.xml文件,找到以下代码,移除注释,并修改host为执行clickhouse-jdbc-bridge-2.0.7-shaded.jar的ip地址:
<jdbc_bridge>
<host>XX.XX.XX.XX</host>
<port>9019</port>
</jdbc_bridge>
保存之后,重启clickhoust服务
systemctl restart clickhouse-server
超时问题解决:
# 1.创建数据库
CREATE DATABASE test charset=utf8mb4;
USE test;
# 2.创建表
CREATE TABLE User (
userId INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL UNIQUE,
registrationDate DATETIME NOT NULL,
lastLogin DATETIME,
createTime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, -- 创建时间
updateTime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP -- 更新时间
);
# 3.插入测试数据
INSERT INTO test.`User` (username, email, registrationDate, lastLogin) VALUES
('JohnDoe01', 'john.doe01@example.com', '2023-02-01 08:00:00', '2023-02-02 09:00:00'),
('JaneDoe02', 'jane.doe02@example.com', '2023-02-02 10:00:00', '2023-02-03 11:00:00'),
('MikeSmith03', 'mike.smith03@example.com', '2023-02-03 12:00:00', '2023-02-04 13:00:00'),
('LucyBrown04', 'lucy.brown04@example.com', '2023-02-04 14:00:00', '2023-02-05 15:00:00'),
('DavidWilson05', 'david.wilson05@example.com', '2023-02-05 16:00:00', '2023-02-06 17:00:00'),
('LindaTaylor06', 'linda.taylor06@example.com', '2023-02-06 18:00:00', '2023-02-07 19:00:00'),
('RobertJones07', 'robert.jones07@example.com', '2023-02-07 20:00:00', '2023-02-08 21:00:00'),
('PatriciaWhite08', 'patricia.white08@example.com', '2023-02-08 22:00:00', '2023-02-09 23:00:00'),
('MichaelHarris09', 'michael.harris09@example.com', '2023-02-09 08:30:00', '2023-02-10 09:30:00'),
('SarahMartin10', 'sarah.martin10@example.com', '2023-02-10 10:30:00', '2023-02-11 11:30:00');
# 4.批量插入100w数据
# 4.1 创建存储过程
DELIMITER $$
CREATE PROCEDURE InsertUsers()
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i < 1000000 DO
INSERT INTO User (username, email, registrationDate, lastLogin) VALUES
(CONCAT('User', LPAD(i, 7, '0')), CONCAT('user', LPAD(i, 7, '0'), '@example.com'), NOW(), NOW());
SET i = i + 1;
END WHILE;
END$$
DELIMITER ;
# 4.2 调用存储过程,生成100w用户数据
CALL InsertUsers();
步骤1、安装好clickhouse数据库
https://ooolo.net/article/442.html
步骤2、安装etl中间件ClickHouse JDBC Bridge
Clickhouse-jdbc-bridge:是clickhouse提供的一个jdbc组件,用于通过JDBC的方式远程访问其他数据库表。
Clickhouse支持通过JDBC连接外部数据库,所有支持JDBC驱动的数据库都能直接接入clickhouse。要实现JDBC连接,clickhouse需要使用以后台进程运行的程序 clickhouse-jdbc-bridge。
JDBC表引擎可以对接Mysql、Postgresql、SQLite等数据库,但是JDBC表引擎不能单独完成对接工作,需要依赖clickhouse-jdbc-bridge的查询代理服务,clickhouse-jdbc-bridge是java语言实现的sql代理服务,项目地址为:
https://github.com/ClickHouse/clickhouse-jdbc-bridge #作者的资源库有直接可以去下载RPM安装包
https://clickhouse.com/docs/zh/engines/table-engines/integrations/jdbc # clickhouse官网的介绍
我们看懂架构图,则需要保证
ClickHouse JDBC Bridge第三方插件要能正常连接到2个数据库。我们可以把这个插件看出是2个数据库直接的桥梁!
达梦8数据库肯定是完整支持jdbc协议的,所以这个就存在2个数据库数据类型的一一映射关系,这个关系,就在ClickHouse JDBC Bridge维护。
步骤3、ClickHouse JDBC Bridge安装部署方法
(1)在官网:https://github.com/ClickHouse/clickhouse-jdbc-bridge,
下载源码程序到本地,通过在idea或者eclipse中执行mvn的clean、packge完成打包后,到打包目录里面找到clickhouse-jdbc-bridge-2.0.7-shaded.jar。
也可以下载编译好的包。。
(2)将clickhouse-jdbc-bridge-2.0.7-shaded.jar
放在Linux服务器的目录/data/clickhouse-jdbc-bridge下,并在该文件同级目录下新建两个目录:
config/datasources --存放数据源配置文件
drivers --jdbc驱动存放目录
(3)以连接JDBC访问Oracle 11g为例:
①在官网https://mvnrepository.com下载Oracle对应的驱动包ojdbc8-12.2.0.1.jar放置到/data/clickhouse-jdbc-bridge/drivers目录下。
②设置clickhouse-jdbc-bridge远程的数据库信息
mkdir -p config/datasources #创建config目录及其子目录
在datasources目录下创建数据源配置文件,msjdbc.json文件名尽量与配置中的datasource名一致,如下配置:
{
"msjdbc": {
"driverUrls": [
"/data/clickhouse-jdbc-bridge/drivers/ojdbc8-12.2.0.1.jar"
],
"driverClassName": "oracle.jdbc.driver.OracleDriver",
"jdbcUrl": "jdbc:oracle:thin:@远程连接的数据库ip:端口:服务名",
"username": "账号",
"password": "密码",
"connectionTestQuery": ""
}
}
当前Linux服务器的目录为:
|--data
|--clickhouse-jdbc-bridge
|--clickhouse-jdbc-bridge-2.0.7-shaded.jar
|--noput.out
|--drivers
|--ojdbc8-12.2.0.1.jar
|--config
|-- datasources
|-- msjdbc.json
③运行clickhouse-jdbc-bridge
nohup java -jar clickhouse-jdbc-bridge-2.0.7-shaded.jar &
tail -f nohup.out
成功启动:
④在clickhouse部署的那台服务器中
到/etc/clickhouse-server目录下的config.xml文件,找到以下代码,移除注释,并修改host为执行clickhouse-jdbc-bridge-2.0.7-shaded.jar的ip地址:
<jdbc_bridge>
<host>XX.XX.XX.XX</host>
<port>9019</port>
</jdbc_bridge>
保存之后,重启clickhoust服务
systemctl restart clickhouse-server
3、访问
(1)查看能成功访问的的链接:select * from jdbc('','show datasource')
(2)远程查询oracle的某个表
select from jdbc('msjdbc', 'select * from test_tb')
(3)直接远程连接
select from jdbc('jdbc:oracle:thin:账号/密码@xx.xx.xx.xx:端口/服务名', 'select * from test_tb')