数仓常用表设计与实现

一：di表、ds表

           di表多用于事实表，例如：从数据库抽取的交易记录表，购买详单表等

           实现逻辑较为简单，一般直接ETL即可。

            ds表一般就是简单汇聚，例如：用户每天使用的pkg的数量表，一般直接group by 就行。

二：dd表，全量表

            dd表多用于记录每日的全量状态表例如：用户账号密码表、用户通讯录表

            实现逻辑一般为：取今天上报的全量的数据覆盖昨天表中的数据，完成更新

            SQL例子：

            insert into table dwd.dwd_user_info_dd(dt='20220503')
            select coalesce(b.user_id,a.user_id) as user_id,coalesce(b.username,a.username) as username,coalesce(b.passsword,a.passsword) as passsword from
            (select user_id,username,passsword from ods.ods_user_info_di where dt='20220503')a full join
            (select user_id,username,passsword from dwd.dwd_user_info_dd where dt='20220502')b on a.user_id = b.user_id;

三：trace_dd表，轨迹表

            trace_dd表多用于记录活跃轨迹：用户活跃轨迹表（一张表记录用户从历史到现在所有的活跃轨迹）

            实现逻辑一般为：一个字段记录01串，表示用户是否活跃，一般附加两个字段，初始活跃日期和最近活跃日期

           SQL例子：

           insert overwrite table dws.dws_user_active_trace_dd(dt='20220504')
           select coalesce(a.user_id,b.user_id) as user_id,if(b.user_id is not null,init_date,'20220504') as init_date,
                      if(a.user_id is not null,'20220504',init_date) as active_date,
                      if(a.user_id is not null and b.user_id is not null,concat(active_trace,'1'),
                      if(a.user_id is not null and b.user_id is null,'1',concat(active_trace,'0'))) as active_trace from
           (select user_id from dwd.dwd_user_active_di)a full join
           (select user_id,init_date,active_date,active_trace from dws.dws_user_active_trace_dd)b on a.user_id = b.user_id;

四：周表、双周表、月表

            周表，双周表，月表：一般有最近一周两周月表、累计一周两周月表两种形式。

            实现逻辑一般为：周表一般为直接计算7天的数据，双周表一般为单周表+计算7天的数据，月表一般为三周表+最近7天的数据（具体实现为一个公共函数，对外提供计算口径及方法）

            例子：用户pkg的使用时长表

object parseDate {

  //输出三个字段
  def deal3days(dealdate:String) = {
    import java.text.SimpleDateFormat
    val simpleDateFormat = new SimpleDateFormat("yyyyMMdd")
    val formatdate = simpleDateFormat.parse(dealdate)
    if(formatdate.getTime/1000/60/60/24%3 ==0){
      import java.util.Calendar
      val calendar = Calendar.getInstance
      calendar.setTime(formatdate)
      calendar.add(Calendar.DAY_OF_YEAR,-2)
      "true"+"|"+simpleDateFormat.format(calendar.getTime)+"|"+dealdate
    }
    else {
      "false"+"||"
    }
  }
  //第二个字段表示有一组三天的日期还是两组三天的日期
  def dealweek(dealdate:String) = {
    import java.text.SimpleDateFormat
    import java.util.Calendar
    val calendar = Calendar.getInstance
    val simpleDateFormat = new SimpleDateFormat("yyyyMMdd")
    val formatdate = simpleDateFormat.parse(dealdate)
    calendar.setTime(formatdate)

    if(calendar.get(Calendar.DAY_OF_WEEK) == 1){
      val datethree = Array("","","","")
      var datethreeindex = 0
      while(calendar.getTimeInMillis/1000/60/60/24%3 !=0){
        datethree.update(datethreeindex,simpleDateFormat.format(calendar.getTime))
        datethreeindex = datethreeindex+1
        calendar.add(Calendar.DAY_OF_YEAR,-1)
      }
      val first3daysend = simpleDateFormat.format(calendar.getTime)//提取3days的后缀日期
      calendar.add(Calendar.DAY_OF_YEAR,-2)
      val first3daystart = simpleDateFormat.format(calendar.getTime)//提取3days的前缀日期
      calendar.add(Calendar.DAY_OF_YEAR,-1)

      if(datethreeindex == 2){
        while(calendar.get(Calendar.DAY_OF_WEEK) != 1){  //拿到周日期的起点
          datethree.update(datethreeindex,simpleDateFormat.format(calendar.getTime))
          datethreeindex = datethreeindex+1
          calendar.add(Calendar.DAY_OF_YEAR,-1)
        }
        "true"+"|"+1+"|"+first3daystart+"|"+first3daysend+"|"+datethree(0)+"|"+datethree(1)+"|"+datethree(2)+"|"+datethree(3)
      }
      else{
        val second3daysend = simpleDateFormat.format(calendar.getTime)//提取3days的前缀日期
        calendar.add(Calendar.DAY_OF_YEAR,-2)
        val second3daystart = simpleDateFormat.format(calendar.getTime)//提取3days的前缀日期
        while(calendar.get(Calendar.DAY_OF_WEEK) != 1){  //拿到周日期的起点
          calendar.add(Calendar.DAY_OF_YEAR,-1)
          datethree.update(datethreeindex,simpleDateFormat.format(calendar.getTime))
          datethreeindex = datethreeindex+1
        }
        "true"+"|"+2+"|"+first3daystart+"|"+first3daysend+"|"+second3daystart+"|"+second3daysend+"|"+datethree(0)
      }
    }
    else {
      "false"+"||||||"
    }
  }
  //第二个字段表示有一组三天的日期还是两组三天的日期
  def dealastweek(dealdate:String) = {
    import java.text.SimpleDateFormat
    import java.util.Calendar
    val calendar = Calendar.getInstance
    val simpleDateFormat = new SimpleDateFormat("yyyyMMdd")
    val formatdate = simpleDateFormat.parse(dealdate)
    calendar.setTime(formatdate)

    val datethree = Array("","","","")
    var datethreeindex = 0
    while(calendar.getTimeInMillis/1000/60/60/24%3 !=0){
      datethree.update(datethreeindex,simpleDateFormat.format(calendar.getTime))
      datethreeindex = datethreeindex+1
      calendar.add(Calendar.DAY_OF_YEAR,-1)
    }
    val first3daysend = simpleDateFormat.format(calendar.getTime)//提取3days的后缀日期
    calendar.add(Calendar.DAY_OF_YEAR,-2)
    val first3daystart = simpleDateFormat.format(calendar.getTime)//提取3days的前缀日期
    calendar.add(Calendar.DAY_OF_YEAR,-1)

    if(datethreeindex == 2){
      while(calendar.get(Calendar.DAY_OF_WEEK) != 1){  //拿到周日期的起点
        datethree.update(datethreeindex,simpleDateFormat.format(calendar.getTime))
        datethreeindex = datethreeindex+1
        calendar.add(Calendar.DAY_OF_YEAR,-1)
      }
      dealdate+"true"+"|"+1+"|"+first3daystart+"|"+first3daysend+"|"+datethree(0)+"|"+datethree(1)+"|"+datethree(2)+"|"+datethree(3)
    }
    else{
      val second3daysend = simpleDateFormat.format(calendar.getTime)//提取3days的前缀日期
      calendar.add(Calendar.DAY_OF_YEAR,-2)
      val second3daystart = simpleDateFormat.format(calendar.getTime)//提取3days的前缀日期
      while(calendar.get(Calendar.DAY_OF_WEEK) != 1){  //拿到周日期的起点
        calendar.add(Calendar.DAY_OF_YEAR,-1)
        datethree.update(datethreeindex,simpleDateFormat.format(calendar.getTime))
        datethreeindex = datethreeindex+1
      }
      dealdate+"true"+"|"+2+"|"+first3daystart+"|"+first3daysend+"|"+second3daystart+"|"+second3daysend+"|"+datethree(0)
    }
  }
  def dealtwoweek(dealdate:String) = {
    import java.text.SimpleDateFormat
    import java.util.Calendar
    val calendar = Calendar.getInstance
    val simpleDateFormat = new SimpleDateFormat("yyyyMMdd")
    val formatdate = simpleDateFormat.parse(dealdate)
    calendar.setTime(formatdate)

    if(calendar.get(Calendar.DAY_OF_WEEK_IN_MONTH)%2 == 0 && calendar.get(Calendar.DAY_OF_WEEK)==1){
      val second3daysend = simpleDateFormat.format(calendar.getTime)//提取3days的前缀日期
      calendar.add(Calendar.DAY_OF_YEAR,-6)
      val second3daystart = simpleDateFormat.format(calendar.getTime)//提取3days的前缀日期

      calendar.add(Calendar.DAY_OF_YEAR,-1)
      val first3daysend = simpleDateFormat.format(calendar.getTime)//提取3days的后缀日期
      calendar.add(Calendar.DAY_OF_YEAR,-6)
      val first3daystart = simpleDateFormat.format(calendar.getTime)//提取3days的前缀日期

      "true"+"|"+first3daystart+"|"+first3daysend+"|"+second3daystart+"|"+second3daysend
    }
    else{
      "false"+"||||"
    }
  }
  def dealastwoweek(dealdate:String) = {
    import java.text.SimpleDateFormat
    import java.util.Calendar
    val calendar = Calendar.getInstance
    val simpleDateFormat = new SimpleDateFormat("yyyyMMdd")
    val formatdate = simpleDateFormat.parse(dealdate)
    calendar.setTime(formatdate)

    val second3daysend = simpleDateFormat.format(calendar.getTime)//提取3days的前缀日期
    calendar.add(Calendar.DAY_OF_YEAR,-6)
    val second3daystart = simpleDateFormat.format(calendar.getTime)//提取3days的前缀日期

    calendar.add(Calendar.DAY_OF_YEAR,-1)
    val first3daysend = simpleDateFormat.format(calendar.getTime)//提取3days的后缀日期
    calendar.add(Calendar.DAY_OF_YEAR,-6)
    val first3daystart = simpleDateFormat.format(calendar.getTime)//提取3days的前缀日期

    "true"+"|"+first3daystart+"|"+first3daysend+"|"+second3daystart+"|"+second3daysend
  }
  def dealmonth(dealdate:String) = {
    import java.text.SimpleDateFormat
    import java.util.Calendar
    val calendar = Calendar.getInstance
    val simpleDateFormat = new SimpleDateFormat("yyyyMMdd")
    val formatdate = simpleDateFormat.parse(dealdate)
    calendar.setTime(formatdate)

    val calendarcopy = Calendar.getInstance
    calendarcopy.setTime(formatdate)
    calendarcopy.add(Calendar.DAY_OF_YEAR,1)

    if(calendarcopy.get(Calendar.DAY_OF_MONTH)==1){
      val datethree = Array("","","")
      var datethreeindex = 0
      while(calendar.get(Calendar.DAY_OF_WEEK_IN_MONTH)%2 != 0 && calendar.get(Calendar.DAY_OF_WEEK)!=1){
        datethree.update(datethreeindex,simpleDateFormat.format(calendar.getTime))
        datethreeindex = datethreeindex+1
        calendar.add(Calendar.DAY_OF_YEAR,-1)
      }
      val second3daysend = simpleDateFormat.format(calendar.getTime)//提取3days的前缀日期
      calendar.add(Calendar.DAY_OF_YEAR,-13)
      val second3daystart = simpleDateFormat.format(calendar.getTime)//提取3days的前缀日期

      calendar.add(Calendar.DAY_OF_YEAR,-1)
      val first3daysend = simpleDateFormat.format(calendar.getTime)//提取3days的后缀日期
      calendar.add(Calendar.DAY_OF_YEAR,-13)
      val first3daystart = simpleDateFormat.format(calendar.getTime)//提取3days的前缀日期

      while(calendar.get(Calendar.DAY_OF_MONTH)!=1 ){
        calendar.add(Calendar.DAY_OF_YEAR,-1)
        datethree.update(datethreeindex,simpleDateFormat.format(calendar.getTime))
        datethreeindex = datethreeindex+1
      }
      "true"+"|"+datethreeindex+"|"+first3daystart+"|"+first3daysend+"|"+second3daystart+"|"+second3daysend+"|"+datethree(0)+"|"+datethree(1)+"|"+datethree(2)
    }
    else{
      "false"+"||||||||"
    }

  }
  def dealastmonth(dealdate:String) = {
    import java.text.SimpleDateFormat
    import java.util.Calendar
    val calendar = Calendar.getInstance
    val simpleDateFormat = new SimpleDateFormat("yyyyMMdd")
    val formatdate = simpleDateFormat.parse(dealdate)
    calendar.setTime(formatdate)

    val calendarcopy = Calendar.getInstance
    calendarcopy.setTime(formatdate)
    calendarcopy.add(Calendar.DAY_OF_YEAR,1)

    val datethree = Array("","")
    var datethreeindex = 0
    val second3daysend = simpleDateFormat.format(calendar.getTime)//提取3days的前缀日期
    calendar.add(Calendar.DAY_OF_YEAR,-13)
    val second3daystart = simpleDateFormat.format(calendar.getTime)//提取3days的前缀日期

    calendar.add(Calendar.DAY_OF_YEAR,-1)
    val first3daysend = simpleDateFormat.format(calendar.getTime)//提取3days的后缀日期
    calendar.add(Calendar.DAY_OF_YEAR,-13)
    val first3daystart = simpleDateFormat.format(calendar.getTime)//提取3days的前缀日期

    while(datethreeindex <2){
      calendar.add(Calendar.DAY_OF_YEAR,-1)
      datethree.update(datethreeindex,simpleDateFormat.format(calendar.getTime))
      datethreeindex = datethreeindex+1
    }
    "true"+"|"+datethreeindex+"|"+first3daystart+"|"+first3daysend+"|"+second3daystart+"|"+second3daysend+"|"+datethree(0)+"|"+datethree(1)
  }
  def main(args: Array[String]):Unit= {
    val dealtype = 7
//    val dealdatelist = Array("20220109","20220116","20220123","20220130")
    val dealdatelist = Array("20220227","20220228","20220330","20220331")

    for(dealdate <- dealdatelist) {
      dealtype match{
        case 1 => println(deal3days(dealdate))
        case 2 => println(dealweek(dealdate))
        case 3 => println(dealastweek(dealdate))
        case 4 => println(dealtwoweek(dealdate))
        case 5 => println(dealastwoweek(dealdate))
        case 6 => println(dealmonth(dealdate))
        case 7 => println(dealastmonth(dealdate))
      }
    }
  }
}

            SQL例子：

                        天表：(每天运行)
                        insert overwrite table dws.dws_user_usedur_ds(dt='20220109')
                        select user_id,sum(usedur) as usedur from dwd.dwd_user_usedur_di where dt='20220109' group by user_id;

                        三天表：(每隔三天运行)
                        System.currentTimeMillis/1000/60/60/24%3 ==0
                        insert overwrite table dws.dws_user_usedur_4ds(dt='20220103_20220105')
                        select user_id,sum(usedur) as usedur from dws.dws_user_usedur_ds where dt>='20220103' and dt<='20220105' group by user_id;
                        insert overwrite table dws.dws_user_usedur_4ds(dt='20220106_20220108')
                        select user_id,sum(usedur) as usedur from dws.dws_user_usedur_ds where dt>='20220106' and dt<='20220108' group by user_id;

                        周表：(每周日运行，依赖三天表天表): 
                        insert overwrite table dws.dws_user_usedur_ws(dt='20220103_20220109')
                        select user_id,sum(usedur) as usedur from (
                                select user_id,usedur from dws.dws_user_usedur_4ds where dt='20220103_20220105'
                                union all
                                select user_id,usedur from dws.dws_user_usedur_4ds where dt='20220106_20220108'
                                union all
                                select user_id,usedur from dwd.dwd_user_usedur_di where dt='20220109'
                        )t group by user_id;
                        双周表：(每双周日运行，依赖周表):
                        insert overwrite table dws.dws_user_usedur_2ws(dt='20220103_20220116')
                        select user_id,sum(usedur) as usedur from (
                                select user_id,usedur from dws_user_usedur_ws where dt='20220103_20220109'
                                union all
                                select user_id,usedur from dws_user_usedur_ws where dt='20220110_20220116'
                        )t group by user_id;
                        月表：(每月底运行，依赖双周表天表):
                        insert overwrite table dws.dws_user_usedur_ms(dt='20220101_20220131')
                        select user_id,sum(usedur) as usedur from (
                                select user_id,usedur from dws.dws_user_usedur_2ws where dt='20220103_20220116'
                                union all
                                select user_id,usedur from dws.dws_user_usedur_2ws where dt='20220117_20220130'
                                union all
                                select user_id,usedur from dws.dws_user_usedur_ds where dt='20220101'
                                union all
                                select user_id,usedur from dws.dws_user_usedur_ds where dt='20220103'
                                union all
                                select user_id,usedur from dws.dws_user_usedur_ds where dt='20220131'
                        )t group by user_id;

                        周表：(每天运行，最近七天数据，依赖三天表天表):
                        insert overwrite table dws.dws_user_usedur_ws(dt='20220102_20220108')
                        select user_id,sum(usedur) as usedur from (
                                select user_id,usedur from dws.dws_user_usedur_4ds where dt='20220103_20220105'
                                union all
                                select user_id,usedur from dws.dws_user_usedur_4ds where dt='20220106_20220108'
                                union all
                                select user_id,usedur from dwd.dwd_user_usedur_di where dt='20220102'
                        )t group by user_id;
                        双周表：(每天运行，最近十四天数据，依赖周表):
                        insert overwrite table dws.dws_user_usedur_2ws(dt='20220104_20220117')
                        select user_id,sum(usedur) as usedur from (
                                select user_id,usedur from dws.dws_user_usedur_ws where dt='20220104_20220110'
                                union all
                                select user_id,usedur from dws.dws_user_usedur_ws where dt='20220111_20220117'
                        )t group by user_id;
                        月表：(每天运行，最近三十天数据，依赖双周表天表):
                        insert overwrite table dws.dws_user_usedur_ms(dt='20220103_20220201')
                        select user_id,sum(usedur) as usedur from (
                                select user_id,usedur from dws.dws_user_usedur_2ws where dt='20220103_20220116'
                                union all
                                select user_id,usedur from dws.dws_user_usedur_2ws where dt='20220117_20220130'
                                union all
                                select user_id,usedur from dws.dws_user_usedur_ds where dt='20220131'
                                union all
                                select user_id,usedur from dws.dws_user_usedur_ds where dt='20220201'
                        )t group by user_id;

四：复杂类型操作：相加表

            一般的表会有复杂类型的数据，例如maplist，部分表会存在将map合并或者list相加减的表。

            实现逻辑一般为：建立UDAF

package com.transsion.bigdata.aggregate

import org.apache.hadoop.hive.ql.exec.UDF
import org.apache.spark.sql.Row
import org.apache.spark.sql.expressions.{MutableAggregationBuffer, UserDefinedAggregateFunction}
import org.apache.spark.sql.types.{DataType, MapType, StringType, StructField, StructType,ArrayType}

import scala.collection.mutable

class AggList extends UserDefinedAggregateFunction {
  override def inputSchema: StructType = StructType(StructField("input",ArrayType(StringType)):: Nil)

  // 缓存区数据结构
  override def bufferSchema: StructType = StructType(StructField("buffer",ArrayType(StringType)):: Nil)

  // 聚合函数返回值数据结构
  override def dataType: DataType = ArrayType(StringType)

  // 聚合函数是否是幂等的，即相同输入是否总是能得到相同输出
  override def deterministic: Boolean = true

  // 初始化缓冲区
  override def initialize(buffer: MutableAggregationBuffer): Unit = {
    buffer(0) = new java.util.ArrayList[String]()
  }

  // 给聚合函数传入一条新数据进行处理
  //传入字段做字符串反转
  override def update(buffer: MutableAggregationBuffer, input: Row): Unit = {
    agg(buffer,input)
  }

  // 合并聚合函数缓冲区
  override def merge(buffer1: MutableAggregationBuffer, buffer2: Row): Unit = {
    agg(buffer1,buffer2)
  }

  // 计算最终结果
  override def evaluate(buffer: Row) = buffer.getList[String](0)

  def agg(buffer1: MutableAggregationBuffer, buffer2: Row) = {
    val bufferdata:java.util.List[String] =new java.util.ArrayList[String]()
    bufferdata.addAll(buffer1.getList[String](0))
    if(buffer2.get(0) != null){
      bufferdata.removeAll(buffer2.getList[String](0))
      bufferdata.addAll(buffer2.getList[String](0))
    }
    bufferdata.remove("")

    buffer1.update(0,bufferdata)

    //      println("bufferdata:"+bufferdata)
    //      println()

  }
}

package com.transsion.bigdata.aggregate

import org.apache.hadoop.hive.ql.exec.UDF
import org.apache.spark.sql.Row
import org.apache.spark.sql.expressions.{MutableAggregationBuffer, UserDefinedAggregateFunction}
import org.apache.spark.sql.types._
import org.apache.spark.sql.types.MapType

import scala.collection.mutable

class AggMap extends UserDefinedAggregateFunction {

  override def inputSchema: StructType = StructType(StructField("input",MapType(StringType,StringType)):: Nil)

  // 缓存区数据结构
  override def bufferSchema: StructType = StructType(StructField("buffer",MapType(StringType,StringType)):: Nil)

  // 聚合函数返回值数据结构
  override def dataType: DataType = MapType(StringType,StringType)

  // 聚合函数是否是幂等的，即相同输入是否总是能得到相同输出
  override def deterministic: Boolean = true

  // 初始化缓冲区
  override def initialize(buffer: MutableAggregationBuffer): Unit = {
    buffer(0) = mutable.Map()
  }

  // 给聚合函数传入一条新数据进行处理
  //传入字段做字符串反转
  override def update(buffer: MutableAggregationBuffer, input: Row): Unit = {
    evaluate(buffer,input)
  }

  // 合并聚合函数缓冲区
  override def merge(buffer1: MutableAggregationBuffer, buffer2: Row): Unit = {
    evaluate(buffer1,buffer2)
  }

  // 计算最终结果
  override def evaluate(buffer: Row) = buffer.getMap[String,String](0)

  def evaluate(buffer1: MutableAggregationBuffer, buffer2: Row) = {
    //    println("-----buffer1.size === "+buffer1.size)
    //    println("-----buffer2.size === "+buffer2.size)

    val bufferdata = buffer1.getMap[String,String](0)
    val inputdata = buffer2.getMap[String,String](0)
    if(inputdata != null){
      val keyset = bufferdata.keySet.toList.++:(inputdata.keySet.toList).distinct
      //    println("-----keySet === "+bufferdata.keySet.toList.++:(inputdata.keySet.toList).distinct)
      //    println("-----bufferdata|inputdata === "+bufferdata+"|"+inputdata)
      var finalMap:mutable.Map[String,String] =mutable.Map[String,String]()
      for (key <- keyset){
        if(!key.equals("")){
          //      println("-----key:"+(key.equals("")))
          //      if(bufferdata.contains(key) && inputdata.contains(key) )
          //        println("-----bufferdata + inputdata === " + bufferdata.apply(key).toString.toDouble + inputdata.apply(key).toString.toDouble)
          //      else if(bufferdata.contains(key))
          //        println("-----bufferdata === "+bufferdata.apply(key).toString.toDouble)
          //      else if(inputdata.contains(key)) println("-----inputdata === "+inputdata.apply(key).toString.toDouble)

          val values = {if(bufferdata.contains(key) && inputdata.contains(key) )   bufferdata.apply(key).toString.toDouble + inputdata.apply(key).toString.toDouble
          else if(bufferdata.contains(key)) bufferdata.apply(key).toString.toDouble
          else if(inputdata.contains(key)) inputdata.apply(key).toString.toDouble }
          finalMap.put(key,values.toString)
        }
      }
      //    println("-----finalMap.toMap === "+ finalMap.toMap)
      //    println()
      buffer1.update(0,finalMap.toMap)
    }
  }
}