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
package mobvista.dmp.clickhouse.tracking
import mobvista.dmp.clickhouse.feature.Constant.{allZero, andriodIdPtn, didPtn, imeiPtn}
import org.apache.commons.lang3.StringUtils
/**
* @package: mobvista.dmp.clickhouse.tracking
* @author: wangjf
* @date: 2019-09-03
* @time: 10:46
* @email: jinfeng.wang@mobvista.com
* @phone: 152-1062-7698
*/
object Constant {
/*
val tracking_daily_sql: String =
s"""
|SELECT UPPER(device_id) device_id, device_model, os_version, country, city, offer_id, id, event_name, event_type, 0 log_type
| FROM dwh.etl_tracking_install_daily WHERE dt = '@date'
| UNION ALL
| SELECT UPPER(device_id) device_id, '' device_model, '' os_version, country, '' city, offer_id, id, event_name, event_type, 1 log_type
| FROM dwh.etl_3s_event_daily WHERE dt = '@date'
""".stripMargin
*/
val tracking_daily_sql: String =
"""
|SELECT *
| FROM dwh.etl_tracking_merge_daily WHERE dt = '@date'
""".stripMargin
val tracking_insight_sql: String =
s"""
|SELECT t.device_id device_id, COALESCE(platform,'') platform, COALESCE(country,'') country, COALESCE(age, 0) age, COALESCE(gender, 0) gender,
| COALESCE(install, array_int()) install, COALESCE(interest, array_string()) interest, offer_id, `id`, event_name, event_type, log_type
| FROM (
| SELECT UPPER(device_id) device_id, offer_id, id, event_name, event_type, 0 log_type FROM dwh.etl_tracking_install_daily WHERE dt = '@date' AND checkDevice(device_id)
| UNION ALL
| SELECT UPPER(device_id) device_id, offer_id, id, event_name, event_type, 1 log_type FROM dwh.etl_3s_event_daily WHERE dt = '@date' AND checkDevice(device_id)
| ) t
| LEFT JOIN (
| SELECT UPPER(device_id) device_id, platform, country, age, gender, install, interest FROM dwh.dm_user_info_v2 WHERE dt = '@dt' AND update_date = '@updateDate'
| ) u
| ON t.device_id = u.device_id
""".stripMargin
val insert_insight_sql: String =
s"""
|INSERT INTO dwh.tracking_insight_daily (device_id,platform,country,age,gender,install_apps,interest,offer_id,`id`,event_name,event_type,log_type,dt)
| SELECT device_id,platform,country,age,gender,install_apps,interest,offer_id,`id`,event_name,event_type,log_type,b.dt dt
| FROM dwh.ods_user_info a ANY RIGHT JOIN dwh.tracking_daily_all b
| USING device_id
| WHERE b.dt = '@date'
""".stripMargin
val adn_tracking_daily_sql: String =
s"""
|SELECT *
| FROM dwh.etl_tracking_adn_merge_daily WHERE dt = '@date'
""".stripMargin
val trackingIndexColumn: Seq[String] = Seq("device_id", "platform", "country", "age", "gender", "install_apps", "interest", "offer_id", "id", "event_name", "event_type", "log_type")
val trackingOrderColumn: Seq[String] = Seq("platform", "country", "age", "gender", "log_type")
val indexColumn: Seq[String] = Seq("device_id", "log_type")
val indexAdnColumn: Seq[String] = Seq("device_id", "log_type")
def checkDeviceId(device_id: String): Boolean = {
StringUtils.isNotBlank(device_id) && (device_id.matches(didPtn) && !device_id.equals(allZero) || device_id.matches(imeiPtn) || device_id.matches(andriodIdPtn))
}
// import org.apache.spark.sql.functions.udf
def array_int(): Array[Int] = {
Array.empty[Int]
}
def array_string(): Array[String] = {
Array.empty[String]
}
}