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
package mobvista.dmp.common;
import mobvista.dmp.util.PropertyUtil;
import ru.yandex.clickhouse.ClickHouseConnection;
import ru.yandex.clickhouse.ClickHouseDataSource;
import ru.yandex.clickhouse.except.ClickHouseException;
import ru.yandex.clickhouse.settings.ClickHouseProperties;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Random;
/**
* @package: mobvista.dmp.common
* @author: wangjf
* @date: 2020/5/9
* @time: 4:51 下午
* @email: jinfeng.wang@mobvista.com
* @phone: 152-1062-7698
*/
public class ClickHouseJdbc {
private static final String[] set_values = PropertyUtil.getProperty("config.properties", "http.private.host.server.ip").split(",");
private static String driver = PropertyUtil.getProperty("config.properties", "datasource.clickhouse.driverClassName");
private static String url = PropertyUtil.getProperty("config.properties", "datasource.clickhouse.url");
private static String username = PropertyUtil.getProperty("config.properties", "datasource.clickhouse.username");
private static String password = PropertyUtil.getProperty("config.properties", "datasource.clickhouse.password");
private static String database = PropertyUtil.getProperty("config.properties", "datasource.clickhouse.database");
private static int timeout = Integer.parseInt(PropertyUtil.getProperty("config.properties", "datasource.clickhouse.timeout"));
public static final String[] SET_VALUES = PropertyUtil.getProperty("config.properties", "http.private.host.server.ip").split(",");
public static final String URL = PropertyUtil.getProperty("config.properties", "datasource.clickhouse_jiangfan.url");
public static final String USERNAME = PropertyUtil.getProperty("config.properties", "datasource.clickhouse_jiangfan.username");
public static final String PASSWORD = PropertyUtil.getProperty("config.properties", "datasource.clickhouse_jiangfan.password");
public static final String DATABASE = PropertyUtil.getProperty("config.properties", "datasource.clickhouse_jiangfan.database");
public static final int TIMEOUT = Integer.parseInt(PropertyUtil.getProperty("config.properties", "datasource.clickhouse_jiangfan.timeout"));
public static ClickHouseConnection connection() throws InterruptedException, SQLException {
ClickHouseProperties properties = new ClickHouseProperties();
properties.setUser(username);
properties.setPassword(password);
properties.setDatabase(database);
properties.setSocketTimeout(timeout);
properties.setConnectionTimeout(timeout);
Random random = new Random();
int shard = random.nextInt(3);
String[] ips = set_values[shard].split(":");
ClickHouseDataSource dataSource = new ClickHouseDataSource(url.replace("host", ips[new Random().nextInt(2)]), properties);
ClickHouseConnection connection;
try {
connection = dataSource.getConnection();
} catch (ClickHouseException e) {
Thread.sleep(100);
// ipId = random.nextInt(3);
ips = set_values[shard].split(":");
dataSource = new ClickHouseDataSource(url.replace("host", ips[new Random().nextInt(2)]), properties);
connection = dataSource.getConnection();
}
return connection;
}
public static ClickHouseConnection connectionByTime(int times) throws InterruptedException, SQLException {
ClickHouseProperties properties = new ClickHouseProperties();
properties.setUser(username);
properties.setPassword(password);
properties.setDatabase(database);
properties.setSocketTimeout(timeout);
properties.setConnectionTimeout(timeout);
int shard = times % 3;
String[] ips = set_values[shard].split(":");
ClickHouseDataSource dataSource = new ClickHouseDataSource(url.replace("host", ips[new Random().nextInt(2)]), properties);
ClickHouseConnection connection;
try {
connection = dataSource.getConnection();
} catch (ClickHouseException e) {
Thread.sleep(100);
ips = set_values[shard].split(":");
dataSource = new ClickHouseDataSource(url.replace("host", ips[new Random().nextInt(2)]), properties);
connection = dataSource.getConnection();
}
return connection;
}
public static ResultSet getMaxPartition(ClickHouseConnection connection, String database, String table) {
ResultSet resultSet = null;
String sql = "SELECT partition FROM system.parts WHERE database = '@database' AND table = '@table' ORDER BY partition DESC LIMIT 1";
sql = sql.replace("@database", database).replace("@table", table);
try {
resultSet = connection.createStatement().executeQuery(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return resultSet;
}
}