ClickHouseJdbc.java

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;
    }
}