package mobvista.dmp.datasource.id_mapping import mobvista.dmp.common.MobvistaConstant import org.apache.commons.lang.StringUtils import org.apache.spark.sql.types.{StringType, StructField, StructType} import java.net.URLDecoder /** * @package: mobvista.dmp.datasource.id_mapping * @author: wangjf * @date: 2021/11/30 * @time: 3:02 下午 * @email: jinfeng.wang@mobvista.com */ object Constant { case class IosTab(idfa: String, idfv: String, sysid: String, bkupid: String, xwho: String, user_id: String) extends java.io.Serializable val iosSchema: StructType = StructType(Array( StructField("idfa", StringType), StructField("idfv", StringType), StructField("sysid", StringType), StructField("bkupid", StringType), StructField("xwho", StringType), StructField("user_id", StringType), StructField("country", StringType), StructField("ip", StringType), // IP StructField("ua", StringType), // UA StructField("brand", StringType), // 品牌 StructField("model", StringType), // 型号 StructField("os_version", StringType), // 操作系统版本 StructField("osv_upt", StringType), // 操作系统更新时间 StructField("upt", StringType) // 开机时间 )) case class AdrTab(imei: String, android_id: String, oaid: String, gaid: String, sysid: String, bkupid: String, xwho: String, user_id: String) extends java.io.Serializable val adrSchema: StructType = StructType(Array( StructField("imei", StringType), StructField("android_id", StringType), StructField("oaid", StringType), StructField("gaid", StringType), StructField("sysid", StringType), // MTG 自建ID StructField("bkupid", StringType), // MTG 自建ID StructField("xwho", StringType), // tkio 账号 StructField("user_id", StringType), // tkio 账号 StructField("country", StringType), // 国家信息 StructField("ip", StringType), // IP StructField("ua", StringType), // UA StructField("brand", StringType), // 品牌 StructField("model", StringType), // 型号 StructField("os_version", StringType), // 操作系统版本 StructField("osv_upt", StringType), // 操作系统更新时间 StructField("upt", StringType) // 开机时间 )) val dsp_req_sql = """ |SELECT idfa, gaid, exitid, platform, country | FROM dwh.etl_dsp_request_daily_hours | WHERE dt = '@date' |""".stripMargin val dsp_req_sql_v2 = """ |SELECT idfa, googleadid gaid, ext5 exitid, os platform, countrycode country, deviceip ip, | parseUA(ext6) ua, make brand, model, osv os_version, '0' osv_upt, '0' upt | FROM adn_dsp.log_adn_dsp_request_orc_hour | WHERE CONCAT(yr,mt,dt) = '@date' @hour | GROUP BY idfa, googleadid, ext5, os, countrycode, deviceip, parseUA(ext6), make, model, osv |""".stripMargin val adn_request_sql = """ |SELECT idfa, gaid, oaid, idfv, androidid, extsysid, imei, platform, country | FROM dwh.etl_adn_org_request_daily_hours | WHERE CONCAT(yt,mt,dt) = '@date' |""".stripMargin // ext_data val adn_request_sql_v2 = """ |SELECT idfa, gaid, ext_oaid oaid, getDevId(cdn_ab) idfv, dev_id androidid, ext_sysid extsysid, imei, platform, | country_code country, ip, parseUA(ext_systemuseragent) ua, ext_brand brand, ext_model model, os_version, | COALESCE(get_json_object(ext_data,'$.osvut'),0) osv_upt, COALESCE(get_json_object(ext_data,'$.upt'),0) upt | FROM dwh.ods_adn_trackingnew_request | WHERE CONCAT(yyyy,mm,dd) = '@date' | GROUP BY idfa, gaid, oaid, cdn_ab, dev_id, ext_sysid, imei, platform, country_code, ip, parseUA(ext_systemuseragent), | ext_brand, ext_model, os_version, COALESCE(get_json_object(ext_data,'$.osvut'),0), COALESCE(get_json_object(ext_data,'$.upt'),0) |""".stripMargin val sss_sql = """ |""".stripMargin def parseUA(ua: String): String = { if (StringUtils.isNotBlank(ua)) { URLDecoder.decode(ua.replaceAll("%(?![0-9a-fA-F]{2})", "%25"), "UTF-8") } else { "" } } def getDevId(devId: String): String = { var dev_id = "" if (StringUtils.isNotBlank(devId) && !",".equals(devId)) { val ids = devId.split(",", -1) if (StringUtils.isNotBlank(ids(0)) && ids(0).matches(MobvistaConstant.didPtn) && !ids(0).matches(MobvistaConstant.allZero)) { dev_id = ids(0) } else if (ids.length == 2 && !ids(1).matches(MobvistaConstant.didPtn) && !ids(1).matches(MobvistaConstant.allZero)) { dev_id = ids(1) } } dev_id } }