ConstantV2.scala

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
}