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
package mobvista.dmp.datasource.adn_request_sdk
/**
* @package: mobvista.dmp.datasource.adn_request_sdk
* @author: wangjf
* @date: 2020/4/4
* @time: 11:13 上午
* @email: jinfeng.wang@mobvista.com
* @phone: 152-1062-7698
*/
object Constant {
val adn_sql: String =
s"""
|select
| 'M' as system,
| re as region,
| case
| when platform in ('ios') and idfa not in ('', '0', '00000000-0000-0000-0000-000000000000', '0000000000000000', '00000000') and length(idfa) > 10 then idfa
| when platform in ('android') and gaid not in ('', '0', '00000000-0000-0000-0000-000000000000', '0000000000000000', '00000000') and length(gaid) >10 then gaid
| when platform in ('ios') and (length(idfa) < 10 or idfa in ('', '0', '00000000-0000-0000-0000-000000000000', '0000000000000000', '00000000')) then
| case
| when ext_sysId like "%,%" and length(split(ext_sysId,",")[0])>0 then split(ext_sysId,",")[0]
| when ext_sysId like "%,%" and length(split(ext_sysId,",")[0])=0 and length(split(ext_sysId,",")[1])>0 then split(ext_sysId,",")[1]
| else '' end
| when platform in ('android') and (length(gaid) < 10 or gaid in ('', '0', '00000000-0000-0000-0000-000000000000', '0000000000000000', '00000000')) then
| case
| when (imei not regexp '^0*$$') then imei
| when (imei regexp '^0*$$') then
| case
| when (dev_id not regexp "^0*$$") then dev_id
| when (dev_id regexp "^0*$$") then
| case
| when ext_sysId like "%,%" and length(split(ext_sysId,",")[0])>0 then split(ext_sysId,",")[0]
| when ext_sysId like "%,%" and length(split(ext_sysId,",")[0])=0 and length(split(ext_sysId,",")[1])>0 then split(ext_sysId,",")[1]
| else '' end
| else '' end
| else '' end
| else '' end as devid
| FROM
| dwh.ods_adn_trackingnew_request
| WHERE
| concat(yyyy, mm, dd) = '20200401'
| AND
| strategy like ('%MNormalAlphaModelRanker%')
""".stripMargin
val dsp_sql: String =
s"""
|SELECT
| 'DSP' as system,
| rg as region,
| googleadid as devid
|FROM
| adn_dsp.log_adn_dsp_request_orc_hour
|WHERE
| concat(yr, mt, dt) = '20200401'
| AND
| googleadid not in ('', '0', '00000000-0000-0000-0000-000000000000', '0000000000000000', '00000000')
""".stripMargin
}