大数据离线数仓---金融审批数仓

简介: 大数据离线数仓---金融审批数仓

前言

数据仓库(Data Warehouse),是为企业制定决策,提供数据支持的。可以帮助企业,改进业务流程、提高产品质量等。

数据仓库的输入数据通常包括:业务数据、用户行为数据和爬虫数据等。本项目仅对业务数据进行统计。

业务数据:就是各行业在处理事务过程中产生的数据。比如用户在电商网站中登录、下单、支付等过程中,需要和网站后台数据库进行增删改查交互,产生的数据就是业务数据。业务数据通常存储在MySQLOracle等数据库中。

数据仓库

image.png

项目需求架构设计

2.1 项目需求分析

本项目仅对业务数据分析处理。

image.png

系统数据流程设计

image.png

集群规模

image.png

金融租赁业务简介

 金融租赁简介

援引自:中国服务贸易指南网

金融租赁(Financing lease),又称“融资租赁”、”设备租赁”、“完全支付租赁”,是指在企业需要设备时,不是以现汇或向金融机构借款去购买,而是由租赁公司融资,把租赁来的设备或购入的设备租给承租人使用,承租人按照合同的规定,定期向租赁公司支付租金,租赁期满后退租、续租或留购的一种融资方式。融资租赁是一种实质上转移与资产所有权有关的全部或绝大部分风险和报酬的租赁,资产的所有权最终可以转移,也可以不转移。

融资租赁的特征表现为:

(1)租赁物由承租人决定,出租人出资购买并租赁给承租人使用,并且在租赁期间内只能租给一个企业使用。

(2)至少涉及三方当事人,即出租人、承租人和供货商。因为设备或供货商是承租人选定的,这就使得承租人先与供货商联系,再由出租人与供货商接触,最后出租人将所购设备租给承租人使用。

(3)出租人保留租赁物的所有权,承租人在租赁期间支付租金而享有使用权,并负责租赁期间租赁物的管理、维修和保养。

(4)租赁设备的所有权与使用权相分离。在租期内,设备的所有权在法律上属于出租人,而经济上的使用权则属于承租人。

(5)不可解约性。租赁合同一经签订,在租赁期间任何一方均无权单方面撤销合同。只有当设备自然毁坏并已证明丧失了使用效力的情况下才能终止合同,但必须以出租人不受经济损失为前提。

(6)租赁期满,承租人有退租、续租和留购的选择权。在通常情况下,出租人由于在租期内已收回了投资并得到了合理的利润,再加上设备的寿命已到,出租人以收取名义货价的形式,将设备的所有权转移给承租人。

融资租赁属于国际租赁方式之一,实际上是租赁公司给予用户的一种中长期信贷,出租人支付了全部设备的价款,等于对企业提供了100%的信贷,具有较浓厚的金融色彩。融资租赁被视为一项与设备有关的贷款业务,适用于价值较高和技术较为先进的大型设备。目前,发达国家企业的大型设备有近50%是通过融资租赁方式取得或购买的,融资租赁已成为国际上应用最为广泛的融资方式。

融资租赁具有许多不确定的风险因素,与市场、金融、贸易、技术、经济环境等紧密相关,充斥着产品市场风险、金融风险、贸易风险、技术风险、经济环境风险以及不可抗力等风险因素。融资租赁以承租人占用融资成本的时间计算租金,是市场经济发展到一定阶段而产生的一种适应性较强的融资方式。

1988年5月28日,在加拿大首都渥太华召开的国际外交会议上,通过了国际统一私法协会制定的《国际融资租赁公约国际融资租赁公约》。在我国,《中华人民共和国合同法》第14章对融资租赁合同作了专章规定。其中第237条规定:“融资租赁合同是出租人根据承租人对出卖人﹑租赁物的选择,向出卖人购买租赁物,提供给承租人使用,承租人支付租金的合同。”

业务数据介绍

4.1 金融系统表结构

金融审批业务系统表结构如下,共九张,实际项目中,业务系统表远不止这些,本项目对审批业务做了简化,但核心业务是完善的,统计思路与生产环境也有共通之处。

4.1.1 客户表(business_partner)

字段名

字段说明

id

编号(主键)

name

客户姓名

create_time

创建时间

update_time

更新时间

4.1.2 合同表(contract)

字段名

字段说明

id

编号(主键)

execution_time

起租时间

signed_time

签约时间

status

合同状态:1.新建 2.已签约 3.已起租 4.已取消

credit_id

授信ID

create_time

创建时间

update_time

更新时间

4.1.3 授信表(credit)

字段名

字段说明

id

编号(主键)

cancel_time

取消时间

contract_produce_time

合同制作时间

credit_amount

授信金额

credit_occupy_time

授信占用时间

status

授信状态:1.新建 2.已占用 3.已制作合同 4.已取消

contract_id

合同ID

credit_facility_id

授信申请ID

create_time

创建时间

update_time

更新时间

4.1.4 授信申请表(credit_facility)

字段名

字段说明

id

编号(主键)

credit_amount

申请授信金额

lease_organization

业务方向

status

所处环节:1.新建 2.未达到风控 3.待分配 4.已分配信审经办 5.信审经办审核通过 6.信审经办审核复议 7.业务反馈已提交 8.一级评审通过 9.一级评审复议 10.二级评审通过 11.二级评审复议 12.项目评审会审核通过 13.项目评审会复议 14.总经理/分管总审核通过 15.总经理/分管总审核复议 16.出具批复审核通过 17.出具批复审核复议 18.不满批复金额 19.新增授信 20.拒绝 21.取消

business_partner_id

客户ID

credit_id

授信ID

industry_id

行业ID

reply_id

批复ID

salesman_id

业务经办ID

create_time

创建时间

update_time

更新时间

4.1.5 审核记录表(credit_facility_status)

字段名

字段说明

id

编号(主键)

action_taken

批复结果 1.通过 2.未通过

status

所处环节:1.新建 2.未达到风控 3.待分配 4.已分配信审经办 5.信审经办审核通过 6.信审经办审核复议 7.业务反馈已提交 8.一级评审通过 9.一级评审复议 10.二级评审通过 11.二级评审复议 12.项目评审会审核通过 13.项目评审会复议 14.总经理/分管总审核通过 15.总经理/分管总审核复议 16.出具批复审核通过 17.出具批复审核复议 18.不满批复金额 19.新增授信 20.拒绝 21.取消

credit_facility_id

授信申请ID

employee_id

相关责任人员工ID

signatory_id

加签人

create_time

创建时间

update_time

更新时间

4.1.6 部门表(department)

字段名

字段说明

id

编号(主键)

department_level

部门级别

department_name

部门名称

superior_department_id

上级部门ID

create_time

创建时间

update_time

更新时间

4.1.7 员工表(employee)

字段名

字段说明

id

编号(主键)

name

员工姓名

type

员工类型(员工类型:1.业务经办 2.风控员 3.风控经理 4.信审经办 5.一级评审员 6.一级评审加签人 7.二级评审员 8.总经理/分管总)

department_id

部门ID

create_time

创建时间

update_time

更新时间

4.1.8 行业表(industry)

字段名

字段说明

id

编号(主键)

industry_level

行业级别

industry_name

行业名称

superior_industry_id

上级行业ID

create_time

创建时间

update_time

更新时间

4.1.9 批复表(reply)

字段名

字段说明

id

编号(主键)

credit_amount

批复授信金额

credit_facility_id

授信申请ID

irr

还款利率

period

还款期数

create_time

创建时间

update_time

更新时间

数仓开发之ODS层

ODS层的设计要点如下:

(1)ODS层的表结构设计依托于从业务系统同步过来的数据结构。

(2)ODS层要保存全部历史数据,故其压缩格式应选择压缩比较高的,此处选择gzip。

(3)ODS层表名的命名规范为:ods_表名_单分区增量全量标识(inc/full)。


ODS_使用get_json_object方法解析Json

CREATE EXTERNAL TABLE IF NOT EXISTS ods_business_partner_full
(
    `id`          STRING COMMENT '客户ID',
    `create_time` STRING COMMENT '创建时间',
    `update_time` STRING COMMENT '更新时间',
    `name`        STRING COMMENT '客户姓名'
) COMMENT '客户全量表'
    PARTITIONED BY (`dt` STRING)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    NULL DEFINED AS ''
    LOCATION '/warehouse/financial_lease/ods/ods_business_partner_full/'
    TBLPROPERTIES ('compression.codec' = 'org.apache.hadoop.io.compress.GzipCodec');
//分区问题介绍:
--load data加载数据到分区表中 如果填写分区时正确的操作 最终是剪切操作
--load data加载数据如果不添加分区 默认写入 __HIVE_DEFAULT_PARTITION__分区中 错误操作 多谢一份数据
load data inpath '/origin_data/financial_lease/business_partner_full/2024-05-09'
into table ods_business_partner_full partition (dt='2024-05-09')

--hive解析json数据

desc function extended get_json_object

image.png

create external table json_text(
  json_txt string
)loaction "/textjson"
select get_json_object(json_txt,'$.database') `database_name`,
       get_json_object(json_txt,'$.table') `table_name`,
       get_json_object(json_txt,'$.type') `type`
from json_test

第二种解析Json---JsonSerde

--hive解析json数据
desc function extended get_json_object;

--使用建表语句直接解析json
create external table my_json(
    `database` string,
    `table` string,
    `type`  string,
    `ts`    bigint,
    `data` struct<id:bigint,create_time:string,update_time:string>  --json嵌套
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.JsonSerDe'
stored as textfile
location "/testjson"

--如果要访问嵌套的json数据
select `database`,data.id,data.create_time,data.update_time
from my_json

表格分区修复

--表格分区修复
select * from ods_business_partner_full;
msck repair table ods_business_partner_full;
 hive -e 在外部就可以运行了

数据装载脚本

(1)在hadoop102的/home/atguigu/bin目录下创建financial_hdfs_to_ods.sh

$ vim financial_hdfs_to_ods.sh

(2)编写如下内容

#!/bin/bash
 
APP=financial_lease
 
if [ -n "$2" ] ;then
   do_date=$2
else 
   do_date=`date -d '-1 day' +%F`
fi
 
load_data(){
    sql=""
    for i in $*; do
        #判断路径是否存在
        hadoop fs -test -e /origin_data/$APP/${i:4}/$do_date
        #路径存在方可装载数据
        if [[ $? = 0 ]]; then
            sql=$sql"load data inpath '/origin_data/$APP/${i:4}/$do_date/' OVERWRITE into table ${APP}.$i partition(dt='$do_date');"
        fi
    done
    hive -e "$sql"
}
 
case $1 in
    ods_business_partner_full | ods_department_full | ods_employee_full | ods_industry_full | ods_credit_facility_inc | ods_credit_facility_status_inc | ods_reply_inc | ods_credit_inc | ods_contract_inc)
        load_data $1
    ;;
 
    "all")
        load_data "ods_business_partner_full" "ods_department_full" "ods_employee_full" "ods_industry_full" "ods_credit_facility_inc" "ods_credit_facility_status_inc" "ods_reply_inc" "ods_credit_inc" "ods_contract_inc"
    ;;
esac
(3)增加脚本执行权限
[atguigu@hadoop102 bin]$ chmod +x financial_hdfs_to_ods.sh
(4)脚本用法
[atguigu@hadoop102 bin]$ financial_hdfs_to_ods.sh all 2023-05-09

 数仓开发之DIM层

DIM层设计要点:

(1)DIM层的设计依据是维度建模理论,该层存储维度模型的维度表。

(2)DIM层的数据存储格式为orc列式存储+snappy压缩。

(3)DIM层表名的命名规范为dim_表名_全量表或者拉链表标识(full/zip)

部门维度表

1)建表语句

DROP TABLE IF EXISTS dim_department_full;
CREATE EXTERNAL TABLE IF NOT EXISTS dim_department_full
(
    `department3_id`   STRING COMMENT '三级部门ID',
    `department3_name` STRING COMMENT '三级部门名称',
    `department2_id`   STRING COMMENT '二级部门ID',
    `department2_name` STRING COMMENT '二级部门名称',
    `department1_id`   STRING COMMENT '一级部门ID',
    `department1_name` STRING COMMENT '一级部门名称'
) COMMENT '部门维度表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/financial_lease/dim/dim_department_full/'
    TBLPROPERTIES ('orc.compress' = 'snappy');

2)数据装载

#数仓的幂等性
insert overwrite table dim_department_full
    partition (dt = '2023-05-09')
select
 d3.department3_id,
       d3.department3_name,
       d2.department2_id,
       d2.department2_name,
       d1.department1_id,
       d1.department1_name
from
(
    --过滤出三级分区
    select id,department_name,superior_department_id
from ods_business_partner_full
wheredt dt='2023-5-09'
and department_level='3')d3
left json (
    --过滤二级分区
    select id,department_name,superior_department_id
from ods_business_partner_full
wheredt dt='2023-5-09'
and department_level='2'
    )d2
on d3.superior_department_id=d2.id
left join (
     --过滤一级分区
    select id,department_name
from ods_business_partner_full
wheredt dt='2023-5-09'
and department_level='1'
    )d1
on d2.superior_department_id=d1.id

员工维度表

1)建表语句

DROP TABLE IF EXISTS dim_employee_full;
CREATE EXTERNAL TABLE IF NOT EXISTS dim_employee_full
(
    `id`             STRING COMMENT '员工ID',
    `name`           STRING COMMENT '员工姓名',
    `type`           STRING comment '员工类型:1.业务经办 2.风控员 3.风控经理 4.信审经办 5.一级评审员/一级评审加签人 6.二级评审员 7.总经理/分管总',
    `department3_id` STRING comment '三级部门ID'
) COMMENT '员工维度表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/financial_lease/dim/dim_employee_full/'
    TBLPROPERTIES ('orc.compress' = 'snappy');

数据装载

insert overwrite table dim_employee_full
    partition (dt = '2023-05-09')
select id,
       name,
       type,
       department_id department3_id
from ods_employee_full
where dt = '2023-05-09';

行业维度表

1)建表语句

DROP TABLE IF EXISTS dim_industry_full;
CREATE EXTERNAL TABLE IF NOT EXISTS dim_industry_full
(
    `industry3_id`   STRING COMMENT '三级行业ID',
    `industry3_name` STRING COMMENT '三级行业名称',
    `industry2_id`   STRING COMMENT '二级行业ID',
    `industry2_name` STRING COMMENT '二级行业名称',
    `industry1_id`   STRING COMMENT '一级行业ID',
    `industry1_name` STRING COMMENT '一级行业名称'
) COMMENT '行业维度表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/financial_lease/dim/dim_industry_full/'
    TBLPROPERTIES ('orc.compress' = 'snappy');

2)数据装载

-行业数据导入
insert overwrite table dim_industry_full
    partition (dt = '2023-05-09')
select i3.id industry3_id,
       i3.industry_name industry3_name ,
       i2.id industry2_id,
       i2.industry_name industry2_name ,
      i1.id industry1_id,
       i1.industry_name industry1_name ,
from
(
        --三级行业
    select id,industry_name,superior_industry_id
    from ods_industry_full
where dt='20230-050-09'
    and industry_level='3'

)i3
left join(
       --二级行业
    select id,industry_name,superior_industry_id
    from ods_industry_full
where dt='20230-050-09'
    and industry_level='2'
    )i2
on i3.superior_industry_id=i2.id
left on(
     --一级行业
    select id,industry_name,superior_industry_id
    from ods_industry_full
where dt='20230-050-09'
    and industry_level='1'
    )i1
on i2.superior_industry_id=i1.id
相关实践学习
数据库实验室挑战任务-初级任务
本场景介绍如何开通属于你的免费云数据库,在RDS-MySQL中完成对学生成绩的详情查询,执行指定类型SQL。
阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
云原生数据仓库AnalyticDB MySQL版是一种支持高并发低延时查询的新一代云原生数据仓库,高度兼容MySQL协议以及SQL:92、SQL:99、SQL:2003标准,可以对海量数据进行即时的多维分析透视和业务探索,快速构建企业云上数据仓库。 了解产品 https://www.aliyun.com/product/ApsaraDB/ads
相关文章
|
12天前
|
分布式计算 DataWorks 大数据
MaxCompute产品使用合集之大数据计算MaxCompute如何实现通过离线同步脚本模式
MaxCompute作为一款全面的大数据处理平台,广泛应用于各类大数据分析、数据挖掘、BI及机器学习场景。掌握其核心功能、熟练操作流程、遵循最佳实践,可以帮助用户高效、安全地管理和利用海量数据。以下是一个关于MaxCompute产品使用的合集,涵盖了其核心功能、应用场景、操作流程以及最佳实践等内容。
|
1天前
|
存储 数据挖掘 大数据
大数据数仓建模基础理论【维度表、事实表、数仓分层及示例】
数据仓库建模是组织和设计数据以支持数据分析的过程,包括ER模型和维度建模。ER模型通过实体和关系描述数据结构,遵循三范式减少冗余。维度建模,特别是Kimball方法,用于数据仓库设计,便于分析和报告。事实表存储业务度量,如销售数据,分为累积、快照、事务和周期性快照类型。维度表提供描述性信息,如时间、产品、地点和客户详情。数仓通常分层为ODS(源数据)、DWD(明细数据)、DIM(公共维度)、DWS(数据汇总)和ADS(应用数据),以优化数据管理、质量、查询性能和适应性。
|
12天前
|
分布式计算 Java 数据库连接
实时数仓 Hologres产品使用合集之该创建外部表maxCompute的这个服务器列表如何解决
实时数仓Hologres是阿里云推出的一款高性能、实时分析的数据库服务,专为大数据分析和复杂查询场景设计。使用Hologres,企业能够打破传统数据仓库的延迟瓶颈,实现数据到决策的无缝衔接,加速业务创新和响应速度。以下是Hologres产品的一些典型使用场景合集。
|
12天前
|
存储 运维 监控
|
12天前
|
分布式计算 大数据 Hadoop
数据仓库(13)大数据数仓经典最值得阅读书籍推荐
从事数仓工作,在工作学习过程也看了很多数据仓库方面的数据,此处整理了数仓中经典的,或者值得阅读的书籍,推荐给大家一下,希望能帮助到大家。建议收藏起来,后续有新的书籍清单会更新到这里。
276 2
数据仓库(13)大数据数仓经典最值得阅读书籍推荐
|
12天前
|
存储 SQL HIVE
金融审批数仓(离线)--DWD层、ADS层
金融审批数仓(离线)--DWD层、ADS层
101 4
|
12天前
|
分布式计算 大数据 BI
MaxCompute产品使用合集之MaxCompute项目的数据是否可以被接入到阿里云的Quick BI中
MaxCompute作为一款全面的大数据处理平台,广泛应用于各类大数据分析、数据挖掘、BI及机器学习场景。掌握其核心功能、熟练操作流程、遵循最佳实践,可以帮助用户高效、安全地管理和利用海量数据。以下是一个关于MaxCompute产品使用的合集,涵盖了其核心功能、应用场景、操作流程以及最佳实践等内容。
|
12天前
|
SQL 分布式计算 大数据
MaxCompute产品使用合集之怎样可以将大数据计算MaxCompute表的数据可以导出为本地文件
MaxCompute作为一款全面的大数据处理平台,广泛应用于各类大数据分析、数据挖掘、BI及机器学习场景。掌握其核心功能、熟练操作流程、遵循最佳实践,可以帮助用户高效、安全地管理和利用海量数据。以下是一个关于MaxCompute产品使用的合集,涵盖了其核心功能、应用场景、操作流程以及最佳实践等内容。
|
12天前
|
分布式计算 DataWorks 关系型数据库
MaxCompute产品使用合集之可以使用什么方法将MySQL的数据实时同步到MaxCompute
MaxCompute作为一款全面的大数据处理平台,广泛应用于各类大数据分析、数据挖掘、BI及机器学习场景。掌握其核心功能、熟练操作流程、遵循最佳实践,可以帮助用户高效、安全地管理和利用海量数据。以下是一个关于MaxCompute产品使用的合集,涵盖了其核心功能、应用场景、操作流程以及最佳实践等内容。
|
12天前
|
分布式计算 DataWorks 数据库
DataWorks操作报错合集之DataWorks使用数据集成整库全增量同步oceanbase数据到odps的时候,遇到报错,该怎么处理
DataWorks是阿里云提供的一站式大数据开发与治理平台,支持数据集成、数据开发、数据服务、数据质量管理、数据安全管理等全流程数据处理。在使用DataWorks过程中,可能会遇到各种操作报错。以下是一些常见的报错情况及其可能的原因和解决方法。
27 0