Constant.scala

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)
  }
}