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
package mobvista.dmp.clickhouse.feature
/**
* @package: com.mobvista.dataplatform.clickhouse.dmp
* @author: wangjf
* @date: 2019-07-17
* @time: 15:12
* @email: jinfeng.wang@mobvista.com
* @phone: 152-1062-7698
*/
object Constant {
val user_feature_sql_v2: String =
s"""
|SELECT UPPER(device_id) device_id, platform, model, os_version, UPPER(country) country, age, gender, install, interest, frequency, behavior, tag_week, tag_month,
| region, update_date, publish_date
| FROM dwh.dm_user_info WHERE dt = '@date'
| AND ((country != '' AND country IS NOT NULL) OR (age != '' AND age IS NOT NULL) OR (gender != '' AND gender IS NOT NULL) OR (size(interest) != 0 AND interest IS NOT NULL)
| OR (size(install) != 0 AND install IS NOT NULL) OR (frequency != '' AND frequency IS NOT NULL))
""".stripMargin
val user_feature_sql: String =
s"""
|SELECT UPPER(device_id) device_id, platform, model, os_version, UPPER(country) country, age, gender, install, interest, frequency, behavior, tag_week, tag_month,
| region, update_date, publish_date
| FROM dwh.dm_user_info WHERE dt = '@date'
""".stripMargin
val old2new_sql: String =
"""
|SELECT new_first_id, new_second_id FROM dwh.dm_old2new_tag
""".stripMargin
/**
* pre_create materialized view
*/
val pre_tracking_view_sql: String =
s"""
|CREATE MATERIALIZED VIEW IF NOT EXISTS dwh.tracking_insight_view_pre
| ENGINE = MergeTree() PARTITION BY toYYYYMMDD(update_date)
| ORDER BY (dt,country,age,gender,offer_id,`id`,event_name,event_type,log_type) SETTINGS index_granularity = 8192
| POPULATE AS SELECT device_id,country,age,gender,install_apps,interest,offer_id,`id`,event_name,event_type,log_type,a.dt update_date,b.dt dt
| FROM dwh.ods_user_info a ANY INNER JOIN (SELECT * FROM dwh.tracking_daily_all WHERE dt BETWEEN '@start' AND '@end') b USING device_id
""".stripMargin
val rename_view_sql: String =
"""
|RENAME TABLE dwh.tracking_insight_view_pre TO dwh.tracking_insight_view
""".stripMargin
val indexColumn: Seq[String] = Seq("device_id", "platform", "model", "os_version", "country", "age", "gender", "install_apps", "interest", "behavior", "frequency", "tag_week", "tag_month", "update_date")
val orderColumn: Seq[String] = Seq("platform", "country", "age", "gender", "update_date")
def buildPart(partSize: Int): Seq[String] = {
var list: scala.List[String] = scala.List()
for (i <- 0 until partSize) {
list = list.::(s"rand() % ${partSize} = ${i}")
}
list = list.reverse
list
}
val didPtn = "^[0-9a-fA-F]{8}(-[0-9a-fA-F]{4}){3}-[0-9a-fA-F]{12}$"
val imeiPtn = "^([0-9]{15,17})$"
val andriodIdPtn = "^[a-zA-Z0-9]{16}$"
val allZero = "00000000-0000-0000-0000-000000000000"
def checkDeviceId(device_id: String): Boolean = {
device_id.matches(didPtn) && !device_id.equals(allZero) || device_id.matches(imeiPtn) || device_id.matches(andriodIdPtn)
}
}