BaiChuanExcludeMain.java

package mobvista.dmp.datasource.baichuan;

import ch.qos.logback.classic.Logger;
import ch.qos.logback.classic.LoggerContext;
import ch.qos.logback.classic.joran.JoranConfigurator;
import ch.qos.logback.core.joran.spi.JoranException;
import mobvista.dmp.util.DateUtil;
import mobvista.dmp.util.PropertyUtil;
import org.slf4j.LoggerFactory;
import ru.yandex.clickhouse.ClickHouseConnection;
import ru.yandex.clickhouse.ClickHouseDataSource;
import ru.yandex.clickhouse.ClickHousePreparedStatement;
import ru.yandex.clickhouse.except.ClickHouseException;
import ru.yandex.clickhouse.settings.ClickHouseProperties;

import java.sql.SQLException;
import java.util.Date;
import java.util.Random;
import java.util.Set;
import java.util.concurrent.CopyOnWriteArraySet;

/**
 * @package: mobvista.dmp.datasource.baichuan
 * @author: wangjf
 * @date: 2019-10-14
 * @time: 14:56
 * @email: jinfeng.wang@mobvista.com
 * @phone: 152-1062-7698
 */
public class BaiChuanExcludeMain {
    private static final String[] SET_VALUES = PropertyUtil.getProperty("config.properties", "http.private.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"));

    private static Set<AsoDevice> deviceInfoSet = new CopyOnWriteArraySet<>();

    private static String dt = DateUtil.format(new Date(), "yyyy-MM-dd");

    public static void main(String[] args) throws JoranException, SQLException, InterruptedException {
        LoggerContext context = (LoggerContext) LoggerFactory.getILoggerFactory();
        JoranConfigurator configurator = new JoranConfigurator();
        configurator.setContext(context);
        context.reset();
        configurator.doConfigure(BaiChuanServer.class.getClassLoader().getResourceAsStream("logback-syslog.xml"));
        Logger logger = context.getLogger("baichuan-exclude");

        if (args.length >= 1) {
            dt = DateUtil.format(DateUtil.parse(args[0], "yyyyMMdd"), "yyyy-MM-dd");
        } else {
            logger.info("Please Input Partition Date");
            System.exit(1);
        }
        long start = System.currentTimeMillis();
        ClickHouseProperties properties = new ClickHouseProperties();
        properties.setUser(username);
        properties.setPassword(password);
        properties.setDatabase(database);
        properties.setSocketTimeout(timeout);
        properties.setConnectionTimeout(timeout);

        /**
         * foreach runAllNode
         */
        for (int ipId = 0; ipId < SET_VALUES.length; ipId++) {
            String[] ips = SET_VALUES[ipId].split(":"); // ips[new Random().nextInt(2)]
            ClickHouseDataSource dataSource = new ClickHouseDataSource(url.replace("host", ips[new Random().nextInt(2)]), properties);
            ClickHouseConnection connection = null;
            ClickHousePreparedStatement preparedStatement = null;
            try {
                try {
                    connection = dataSource.getConnection();
                } catch (ClickHouseException e) {
                    Thread.sleep(200);
                }
                String date = DateUtil.getDayByString(dt, "yyyy-MM-dd", -1);

                assert connection != null;
                preparedStatement = (ClickHousePreparedStatement) connection.prepareStatement(buildSql(dt, date));
                preparedStatement.execute();
            } catch (SQLException | InterruptedException e) {
                e.printStackTrace();
            } finally {
                if (connection != null) {
                    connection.close();
                    Thread.sleep(200);
                }
                if (preparedStatement != null) {
                    preparedStatement.close();
                    Thread.sleep(200);
                }
            }
        }
        long end = System.currentTimeMillis();
        logger.info("all runtime ==>> " + (end - start));
    }

    private static String buildSql(String dt, String date) {
        String insertSql = "INSERT INTO dwh.baichuan_exclude_ios_daily (device_id,dt) " +
                "SELECT device_id,'@date' dt " +
                "FROM (SELECT UPPER(device_id) device_id FROM dwh.etl_baichuan_daily WHERE dt = '@date' AND app_id = 2 AND app_os = 2 GROUP BY device_id) a " +
                "ALL LEFT JOIN (SELECT UPPER(device_id) device_id FROM dwh.baichuan_install_daily_all WHERE dt = '@dt' AND platform = 'ios') b " +
                "USING device_id WHERE b.device_id = ''";

        return insertSql.replace("@dt", dt)
                .replace("@date", date);
    }
}