数仓常用表设计与实现

一：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;

select gazj,hour,net_cate,net_traffic,active_trace,last_active_dt from (
    select coalesce(a.gazj,b.gazj,c.gazj) as gazj,coalesce(a.hour,b.hour,c.hour) as hour,coalesce(a.net_cate,b.net_cate,c.net_cate) as net_cate,
    if(length(active_trace)>=30,coalesce(CAST(a.net_traffic as bigint),0)-coalesce(CAST(b.net_traffic as bigint),0)+coalesce(CAST(c.net_traffic as bigint),0),coalesce(CAST(a.net_traffic as bigint),0)+coalesce(CAST(c.net_traffic as bigint),0) as net_traffic,
    if(c.net_traffic is not null,concat(coalesce(active_trace,''),'1'),concat(coalesce(active_trace,''),'0')) as active_trace,
    coalesce(dt,last_active_dt) as last_active_dt
    from (
        select gazj,hour,net_cate,net_traffic,if(length(active_trace)>=30,substr(active_trace,length(active_trace)-28,30),active_trace) as active_trace,last_active_dt
         from tranadm.adm_aegis_paymentsecurity_netuse_midlabel_di where dt='${dt_pre1day}' and cycle='30day' and length(replace(active_trace,'0',''))>0
    )a full join (
        select gazj,hour,net_cate,net_traffic from tranadm.adm_aegis_paymentsecurity_netuse_di where dt='${dt_pre30day}'
    )b on a.gazj=b.gazj and a.hour=b.hour and a.net_cate=b.net_cate full join (
        select gazj,hour,net_cate,net_traffic,dt from data
    )c on a.gazj=c.gazj and a.hour=c.hour and a.net_cate=c.net_cate)t where  length(gazj)=36;

insert overwrite table tranadm.adm_aegis_paymentsecurity_netuse_label_di PARTITION(dt='${dt}',cycle='30day_data')
select a.gazj,hour,net_traffic,active_day from
(select gazj,sum(net_traffic) as net_traffic,max(length(replace(active_trace,'0',''))) as active_day from data where net_cate in ('data') group by gazj
)a left join(
  select gazj,hour from (
    select gazj,hour,high_period,row_number() over (partition by gazj order by high_period desc) as rn from (
        select gazj,hour,sum(net_traffic) as high_period from data where net_cate in ('data') group by gazj,hour
    ) t
  )t where rn =1
)b on a.gazj=b.gazj;

四：周表、双周表、月表

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

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

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

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)
        }
    }
}
posted @   Kotlin  阅读(694)  评论(0)  编辑  收藏  举报
相关博文：
· mysql快速同步到数仓（airflow调度、java代码）
· 解析SPARKSQL语句
· 数据仓库之用户行为数仓开发
· 数仓数据接入心得
· 2022-02-10打卡
阅读排行：
· DeepSeek 开源周回顾「GitHub 热点速览」
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布：重大改进与新特性概览！
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 单线程的Redis速度为什么快？
Live2D
点击右上角即可分享
微信分享提示
AI FOR CODE 大赛
西雅图
4°
西雅图
14:14发布
西雅图
14:14发布
4°
晴
东南风
2级
空气质量
无
相对湿度
92%
今天
晴
3°/12°
周四
阴
4°/11°
周五
阴
2°/10°
Powered by 心知天气