需求:项目中要使用ClickHouse做数据库。
具体实现:
1,在pom.xml中添加clickhouse依赖
<!-- https://mvnrepository.com/artifact/com.clickhouse/clickhouse-jdbc -->
<dependency>
<groupId>com.clickhouse</groupId>
<artifactId>clickhouse-jdbc</artifactId>
<version>0.3.2</version>
</dependency>
2, 配置文件中添加clickhouse的配置属性:
spring:
datasource:
clickhouse:
driverClassName: ru.yandex.clickhouse.ClickHouseDirver
url: jdbc:clickhouse:IP:端口号/数据库
user: 用户名
password: 密码
#这部分配置用于kerberos认证
clickhouse:
security:
enabled: true
isMachineUser: false
3,配置Clickhouse连接
@Configuration
public class ClickHouseConfig {
@Value(“{spring.datasource.clickhouse.url}”)
private String clickhouseUrl;
@Value(“{spring.datasource.clickhouse.user}”)
private String clickhouseUser;
@Value(“{spring.datasource.clickhouse.password}”)
private String clickhousePassword;
@Value(“{clickhouse.seurity.enabled}”)
private Boolean isSecurity;
@Value(“{clickhouse.isMachineUser}”)
private Boolean isMachineUser;
@Bean
public DataSource dataSource() {
ClickHouseProperties clickHouseProperties = new ClickHouseProperties();
clickHouseProperties .setSocketTimeout(60000);
//下面三行用于开启kerberos认证
if(isSecurity & isMachineUser) {
clickHouseProperties.setMachineUser(true);
clickHouseProperties.setMachineUserKeytabPath(System.getProperty("user.dir") + File.separator + "src" + File.separator + "main" + File.separator + "resources" + File.separator + "user.keytab");
}
clickHouseProperties.setSsl(true);
clickHouseProperties.setSslMode("none");
clickHouseProperties.setUser(clickhouseUser);
clickHouseProperties.setPassword(clickhousePassword);
ClickHouseDataSource clickHouseDataSource = new ClickHouseDataSource (clickhouseUrl, clickHouseProperties);
return clickHouseDataSource;
}
}
4,实现具体的Mapper。
其他的操作就跟mysql的操作一致了。
注意:clickhouse跟mysql的SQL语法不一致。
问题一:clickhouse集成kerberos认证后,本地(windows)运行正常,但是打成jar包发布到linux报错。
具体错误如下:Cause:org.springframework.jdbc.CannotGetJdbcConnectionException:Failed to obtain JDBC Connection: nested exception is ru.yandex.clickhouse.except.ClickHouseException:ClickHouse exception.code: 516,host:xxxx.xx.xx.x,port: 24126;Code: 516. e.displayText()=DB:Exception: xxxx:Authentication failed: password is incorrect or there is no user with such name (version 21.3.4.25)。
问题原因:打成jar包发布到linux上以后,无法获取到src/resources目录下的user.keytab文件.
错误代码:
clickHouseProperties.setMachineUserKeytabPath(System.getProperty("user.dir") + File.separator + "src" + File.separator + "main" + File.separator + "resources" + File.separator + "user.keytab");
注:System.getProperty(“user.dir”) + File.separator + “src” + File.separator + “main” + File.separator + "resources"这种方式获取的是绝对路径,但是发布到linux后,获取的jar包里面的路径不正确。
解决办法:将resources下的user.keytab放到linux的/opt/xbtx/analys/config目录下(jar包外面),然后项目里直接配置成linux的绝对路径即可。 (项目催的急,怎么简单怎么来)。
clickHouseProperties.setMachineUserKeytabPath(“/opt/xbtx/analys/config/user.keytab”);