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
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
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);
}
}