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
package mobvista.dmp.datasource.datatory
import java.util.Properties
import mobvista.dmp.util.PropertyUtil
import org.apache.spark.sql.{DataFrame, SparkSession}
/**
* @package: mobvista.dmp.datasource.datatory
* @author: wangjf
* @date: 2019-10-28
* @time: 11:37
* @email: jinfeng.wang@mobvista.com
* @phone: 152-1062-7698
*/
object ConstantV2 {
def jdbcConnection(spark: SparkSession, database: String, table: String): DataFrame = {
val mysqlUrl = PropertyUtil.getProperty("config.properties", "mysql.mob_adn.url")
val username = PropertyUtil.getProperty("config.properties", "mysql.mob_adn.user")
val password = PropertyUtil.getProperty("config.properties", "mysql.mob_adn.password")
val properties = new Properties()
properties.put("driver", "com.mysql.jdbc.Driver")
properties.put("user", username)
properties.put("password", password)
properties.put("characterEncoding", "utf8")
spark.read.jdbc(url = s"jdbc:mysql://${mysqlUrl}:3306/${database}", table = table, properties = properties)
}
val tracking_3s_install_sql_v2: String =
"""
|SELECT b.device_id, CAST(b.offer_id AS string) offer_id, device_model, os_version, country, city, CAST(COALESCE(a.id,'') AS string) id, COALESCE(a.event_name,'') event_name, a.event_type FROM
| (SELECT devid device_id, uuid offer_id, device device_model, os_version, country, city FROM dwh.ods_3s_trackingnew_install WHERE CONCAT(yyyy,mm,dd) = '@date'
| GROUP BY devid, uuid, device, os_version, country, city) b
| LEFT JOIN
| (SELECT id, event_name, event_type, offer_id FROM dwh.ods_3s_trackingcsv_event_define WHERE yyyymmdd = '@date') a
| ON a.offer_id = b.offer_id
""".stripMargin
val tracking_3s_event_sql: String =
"""
|SELECT devid, uuid offer_id, tracking_event.network channel_id, event_name, country, campaign_list.id campaign_id
| FROM dwh.ods_3s_trackingcsv_event_info tracking_event
| LEFT JOIN campaign_list ON tracking_event.uuid = campaign_list.network_cid
| WHERE yyyymmdd = '@date' AND campaign_list.create_src = '0'
""".stripMargin
val tracking_ss_event_sql: String =
"""
|SELECT android_id, imei, gaid, idfa, platform, campaign_id, country, event_name
| FROM dwh.ods_adn_tracking_ss_event tracking_ss
| LEFT JOIN campaign_list ON tracking_ss.campaign_id = campaign_list.id
| WHERE CONCAT(yyyy,mm,dd) = '@date' AND campaign_list.create_src IN ('1','2','3')
""".stripMargin
val campaing_info_sql:String=
"""
|SELECT campaign_id, event_name
| FROM tracking_ss,tracking_event
| GROUP BY campaign_id, event_namel
""".stripMargin
val tracking_adn_install_sql: String =
"""
|SELECT dev_id, idfa, gaid, imei, platform, app_id, unit_id, advertiser_id, campaign_id, creative_id, ad_type, os_version, device_model,
| country_code, ios_ab
| FROM dwh.ods_adn_trackingnew_install
| WHERE CONCAT(yyyy,mm,dd) = '@date'
""".stripMargin
}