clickhouse聚合任务demo

代码如下：

package com.chinasoft.union;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;

import ru.yandex.clickhouse.ClickHouseDataSource;
import ru.yandex.clickhouse.settings.ClickHouseProperties;

public class UnionDataTest {

	public static void main(String[] args) {
		// 2020-01-16 19:02:11
		// 2020-01-19 07:29:24
		long startTime = 1579172400L;// 2020-01-16 19:00:00
		long endTime = 1579392000L;// 2020-01-19 08:00:00
		System.out.println("start:" + new Date());
		unionData(startTime, endTime);
		System.out.println("end:" + new Date());
//		countData(startTime, endTime);
	}

	private static void countData(long startTime, long endTime) {
		Connection connection = getConn();
		try {
			Statement statement = connection.createStatement();
			for (long i = startTime; i < endTime; i += 3600) {
				long ts = i;
				String sql = "select count(*) from samples where val>=0 " + "and toUnixTimestamp(ts)>=" + i
						+ " and toUnixTimestamp(ts)<" + (i + 3600) + " group by `date`,name,tags";
				ResultSet rs = statement.executeQuery(sql);
				System.out.println("sql:" + sql);
				while (rs.next()) {
					System.out.println("end sql:" + rs.getInt(1));
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	private static void unionData(long startTime, long endTime) {
		Connection connection = getConn();
		try {
			Statement statement = connection.createStatement();
			for (long i = startTime; i < endTime; i += 3600) {
				System.out.println("start sql:" + new Date());
				long ts = i;
				String sql = "insert into samplesnew(`date`,name,tags,avg,max,min,ts,updated) select `date`,name,tags,avg(val) as avg,"
						+ "max(val) as max,min(val) as min, " + ts + " as ts," + ts
						+ " as updated from samples where val>=0 " + "and toUnixTimestamp(ts)>=" + ts
						+ " and toUnixTimestamp(ts)<" + (ts + 3600) + " group by `date`,name,tags";
				statement.execute(sql);
				System.out.println("end sql:" + new Date());
				Thread.currentThread().sleep(1000L);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	private static String clickhouseAddress = "jdbc:clickhouse://117.78.23.187:32123";

	private static String clickhouseUsername = "root";

	private static String clickhousePassword = "123456";

	private static String clickhouseDB = "metrics";

	private static Integer clickhouseSocketTimeout = 600000;

	private static Connection getConn() {
		ClickHouseProperties properties = new ClickHouseProperties();
		properties.setUser(clickhouseUsername);
		properties.setPassword(clickhousePassword);
		properties.setDatabase(clickhouseDB);
		properties.setSocketTimeout(clickhouseSocketTimeout);
		properties.setConnectionTimeout(60000);
		ClickHouseDataSource clickHouseDataSource = new ClickHouseDataSource(clickhouseAddress, properties);
		try {
			return clickHouseDataSource.getConnection();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

}