【大数据离线项目三：将HIve数仓中的dim进行拉宽聚合处理，PostgreSQL的使用，以及怎么将数据导出到PostgreSQL数据库中】

前言：
💞💞大家好，我是书生♡，今天主要和大家分享一下将HIve数仓中的dim进行拉宽聚合处理，PostgreSQL的使用，以及怎么将数据导出到PostgreSQL数据库中,希望对大家有所帮助。
💞💞代码是你的画笔，创新是你的画布，用它们绘出属于你的精彩世界，不断挑战，无限可能！

个人主页⭐： 书生♡
gitee主页🙋‍♂：闲客
专栏主页💞：大数据开发
博客领域💥：大数据开发，java编程，前端，算法，Python
写作风格💞：超前知识点，干货，思路讲解，通俗易懂
支持博主💖：关注⭐，点赞、收藏⭐、留言💬

1 将dim的数据导入到dwd层

问题1：dim层数据作用是什么？

dim层的数据，原理上属于ods源数据层，只不过dim上的数据不用进行清洗处理，dim的数据主要受为了在以后得dwd，dwm以及dws层上面进复用。几乎不需要进行很大的改变。

问题2：为什么要将dim的数据进行聚合生成新的表？

因为我们的dim上面的数据，如果在后面被引用，大概率也是会被进行聚合的。那样的话会导致SQL语句极其复杂并且消耗资源。因此在dwd层先进行一个聚合操作，将我们后续需要的表先生成，之后直接使用就可以了。

  大家想一下假设我们的商品表，有商品的id，名称，类别id ；类别表 有 类别id，类别名称 等等。
如果我们要在后面获取得到我们的 商品的id，名称，类别id，类别名称 那么我们就要对着2个表进行聚合连接，再加上原本的SQL语句会变的极其复杂。因此在dwd层先进行一个聚合操作，将我们后续需要的表先生成，之后直接使用就可以了，会让我们的SQL语句变得更加简洁并且资源消耗更少。

问题3：怎么判断 ，dim层的哪些表需要进行操作？新生成的表又是什么样子的？需要哪些字段？

其实，在我们的大数据开发中，需要哪些新的表被生成，以及表中有哪些字段，是依靠我们的业务去判断的，我们的需要需要哪些字段，以及我们的业务是什么样子的。

例如：我们这个项目是基于一个电商的业务，那么一定需要商品表，这些商品属于哪个类别，类别的级别试试怎么样的。
还有就是我们的店铺，在那个商圈的以及其他的一些关系？

虽然我们这些表经过轻微聚合是要导入到dwd中供其使用，但是依然还是要存储在dim层中，因为这些表并不需要进行清理，只需要调用就可以了。

那么我们应该怎么对这些表进行处理呢？

• 商品分类表(category): 需要进行分类拉平操作
  

• • 商品表(goods_info): 将分类ID替换为对应一二三级分类ID、编码和名称
    

• 门店表: 在门店宽表构建时，就添加了门店面积信息和区域名称信息，门店面积信息可以用来计算坪效等，区域名称信息可以用来上卷时显示区域名称。
  其中门店面积信息可以从分店面积明细表中获取。先取实际经营面积，如果取不到（实际经营面积为空或0）再取经营面积。
  其中区域名称信息从店组信息表中取，storegrouptype_no = ‘04’即对应区域的编码和名称。
  其中 storetypecode和managementtypecode 需要转换为整数类型。

• 门店日清商品表: 需要在门店商品表的基础上筛选出日清数据, 便于后续统计日清数据指标
  表结构与门店商品表一

1.1 将 时间维度表进行导入

1. 第一步肯定是创建表，因为不需要先进行任何操作所以字段是一样的，唯一的区别在于 这个是在dwd层的。
   创建表的代码如下：跟dim层的建表语句是一样子的

CREATE TABLE dim.dwd_dim_date_f (
    trade_date              STRING COMMENT '日期编码',
    year_code               BIGINT COMMENT '年编码',
    month_code              BIGINT COMMENT '月份编码',
    day_code                BIGINT COMMENT '日编码',
    quanter_code            BIGINT COMMENT '季度编码',
    quanter_name            STRING COMMENT '季度名称',
    week_trade_date         STRING COMMENT '周一时间',
    month_trade_date        STRING COMMENT '月一时间',
    week_end_date           STRING COMMENT '周末时间',
    month_end_date          STRING COMMENT '月末时间',
    last_week_trade_date    STRING COMMENT '上周一时间',
    last_month_trade_date   STRING COMMENT '上月一时间',
    last_week_end_date      STRING COMMENT '上周末时间',
    last_month_end_date     STRING COMMENT '上月末时间',
    year_week_code          BIGINT COMMENT '一年中第几周',
    week_day_code           BIGINT COMMENT '周几code',
    day_year_num            BIGINT COMMENT '一年第几天',
    month_days              BIGINT comment '本月有多少天',
    is_weekend              BIGINT COMMENT '是否周末（周六和周日）',
    days_after1             STRING COMMENT '1天后的日期',
    days_after2             STRING COMMENT '2天后的日期',
    days_after3             STRING COMMENT '3天后的日期',
    days_after4             STRING COMMENT '4天后的日期',
    days_after5             STRING COMMENT '5天后的日期',
    days_after6             STRING COMMENT '6天后的日期',
    days_after7             STRING COMMENT '7天后的日期'
) 
comment '时间维度表'
row format delimited fields terminated by ','
stored as orc
tblproperties ('orc.compress'='SNAPPY');

2. 创建完表，肯定就是将dim层的数据导入到dwd层中。
   我们直接将dim层的表直接导入就可以了。

-- HIVE基础参数; 仅在当前会话生效
--hive压缩
--开启中间结果压缩
set hive.exec.compress.intermediate=true;
--开启最终结果压缩
set hive.exec.compress.output=true;
--写入时压缩生效
set hive.exec.orc.compression.strategy=COMPRESSION;

-- 时间维度表
insert overwrite table dim.dwd_dim_date_f
select
    trade_date,
    year_code,
    month_code,
    day_code,
    quanter_code,
    quanter_name,
    week_trade_date,
    month_trade_date,
    week_end_date,
    month_end_date,
    last_week_trade_date,
    last_month_trade_date,
    last_week_end_date,
    last_month_end_date,
    year_week_code,
    week_day_code,
    day_year_num,
    month_days,
    is_weekend,
    days_after1,
    days_after2,
    days_after3,
    days_after4,
    days_after5,
    days_after6,
    days_after7
from dim.ods_dim_date_f;

1.2 交易类型映射表

交易类型映射表和时间维度表是一样子的，只要需要创建好表直接导入就可以了。

1. 创建表

CREATE TABLE IF NOT EXISTS dim.dwd_dim_source_type_map_i(
    company                     STRING COMMENT '公司:1.传智鲜 2.黑马优选',
    original_source_type        BIGINT COMMENT '原交易来源  传智鲜1:线下pos;2:线上订单;3:扫码购;4:美团;5:饿了么;6:百度外卖;7:京东到家;8:有赞;9:传智鲜精品;10:黑马;11:团购',
    original_source_type_name   STRING COMMENT '原交易来源名称',
    source_type                 BIGINT COMMENT '新交易来源：1',
    source_type_name            STRING COMMENT '新交易来源名称',
    is_online                   BIGINT COMMENT '是否线上交易 0否;1是')
comment '交易类型映射表'
partitioned by (dt STRING COMMENT '写入日期')
row format delimited fields terminated by ','
stored as orc
tblproperties ('orc.compress'='SNAPPY');

2. 导入

insert overwrite table dim.dwd_dim_source_type_map_i partition (dt)
select
    company,
    original_source_type,
    original_source_type_name,
    source_type,
    source_type_name,
    is_online,
    -- date_sub(current_date(),1) as dt,
    '2024-06-21' as dt
from dim.ods_dim_source_type_map_f ;

1.3 商品分类表处理

商品类别表，我们需要将其进行拉宽处理。
我们假设有父类 – 1级子类 – 2级子类
那我们应该怎么进行拉宽？比如下面的是我们的数据。id是编号，pid是我们父类的编号。

我们是不是应该让这个表自己与自己相连接，条件为 父.编号=子.父编号
我们有三个类别登记，那我们就 想这个表自己关联自己三次，分别取表名为 t1,t2,t3
条件为 t1.id=t2.pid t2.id=t3.pid

1. 建表
   因为我们只要分类级别的编号，编码以及名称就行啦。

CREATE TABLE IF NOT EXISTS dim.dwd_dim_category_statistics_i(
    first_category_id       BIGINT COMMENT '一级分类ID',
    first_category_no       STRING COMMENT '一级分类编码',
    first_category_name     STRING COMMENT '一级分类',
    
    second_category_id      BIGINT COMMENT '二级分类ID',
    second_category_no      STRING COMMENT '二级分类编码',
    second_category_name    STRING COMMENT '二级分类',
    
    third_category_id       BIGINT COMMENT '三级分类ID',
    third_category_no       STRING COMMENT '三级分类编码',
    third_category_name     STRING COMMENT '三级分类',

    `status` BIGINT) 
comment '分类等级表'
partitioned by (dt STRING COMMENT '写入日期')
row format delimited fields terminated by ','
stored as orc
tblproperties ('orc.compress'='SNAPPY');

2. 导入

-- 开启动态分区方案
-- 开启非严格模式
set hive.exec.dynamic.partition.mode=nonstrict;
-- 开启动态分区支持(默认true)
set hive.exec.dynamic.partition=true;
-- 设置各个节点生成动态分区的最大数量: 默认为100个  (一般在生产环境中, 都需要调整更大)
set hive.exec.max.dynamic.partitions.pernode=10000;
-- 设置最大生成动态分区的数量: 默认为1000 (一般在生产环境中, 都需要调整更大)
set hive.exec.max.dynamic.partitions=100000;
-- hive一次性最大能够创建多少个文件: 默认为10w
set hive.exec.max.created.files=150000;
--hive压缩
--开启中间结果压缩
set hive.exec.compress.intermediate=true;
--开启最终结果压缩
set hive.exec.compress.output=true;
--写入时压缩生效
set hive.exec.orc.compression.strategy=COMPRESSION;


-- 分类表拉宽操作
insert overwrite table dim.dwd_dim_category_statistics_i partition (dt)
select
    t1.id as first_category_id,
    t1.category_no as first_category_no,
    t1.category_name as first_category_name,
    t2.id as second_category_id,
    t2.category_no as second_category_no,
    t2.category_name as second_category_name,
    t3.id as third_category_id,
    t3.category_no as third_category_no,
    t3.category_name as third_category_name,
    0 as status,
    date_sub(current_date(),1) as dt
from dim.ods_dim_category_f t1
    join dim.ods_dim_category_f t2 on t1.id = t2.parent_id
    join dim.ods_dim_category_f t3 on t2.id = t3.parent_id;

1.4 商品表处理

首先我们要知道我们的商品表都需要哪些字段，商品id，商品类别id，以及商品类别的名称，
所以我们就需要将商品表和类别表进行关联。

关联的条件就是 商品的编号=类别表.第三类别编号

1. 建表

CREATE TABLE IF NOT EXISTS dim.dwd_dim_goods_i(
    goods_id                BIGINT COMMENT '商品ID',
    goods_no                STRING COMMENT '商品编码',
    goods_name              STRING COMMENT '名称',
    first_category_id       BIGINT COMMENT '一级分类ID',  -- 类别表
    first_category_no       STRING COMMENT '一级分类编码',-- 类别表
    first_category_name     STRING COMMENT '一级分类',-- 类别表
    second_category_id      BIGINT COMMENT '二级分类ID',-- 类别表
    second_category_no      STRING COMMENT '二级分类编码',-- 类别表
    second_category_name    STRING COMMENT '二级分类',-- 类别表
    third_category_id       BIGINT COMMENT '三级分类ID',-- 类别表
    third_category_no       STRING COMMENT '三级分类编码',-- 类别表
    third_category_name     STRING COMMENT '三级分类',-- 类别表
    brand_no                STRING COMMENT '品牌编号',
    spec                    STRING COMMENT '商品规格',
    sale_unit               STRING COMMENT '销售单位',
    life_cycle_status       STRING COMMENT '生命周期状态',
    tax_rate_status         BIGINT COMMENT '税率审核状态 (0：未提交审核 1：待财务审核 2：税率已审核 3：未通过)',
    tax_rate                STRING COMMENT '税率code',
    tax_value               DECIMAL(27, 3) COMMENT '税率',
    order_multiple          DECIMAL(27, 2) COMMENT '订货倍数',
    pack_qty                DECIMAL(27, 3) COMMENT '箱装数量',
    split_type              STRING COMMENT '分割属性',
    is_sell_by_piece        BIGINT COMMENT '是否拆零，0:不拆；1:拆',
    is_self_support         BIGINT COMMENT '是否自营 0:非自营；1:自营',
    is_variable_price       BIGINT COMMENT '分店可变价 0:不可；1:可以',
    is_double_measurement   BIGINT COMMENT '是否双计量商品 0:否；1:是',
    is_must_sell            BIGINT COMMENT '必卖品  0:非；1:是',
    is_seasonal             BIGINT COMMENT '季节性商品  0:非；1:是',
    seasonal_start_time     STRING COMMENT '季节性开始时间',
    seasonal_end_time       STRING COMMENT '季节性结束时间',
    is_deleted              BIGINT COMMENT '是否删除0:正常；1:删除',
    goods_type              STRING COMMENT '商品类型 1-国产食品 2-进口食品 3-国产非食品 4-进口非食品',
    create_time             TIMESTAMP COMMENT '该记录创建时间',
    update_time             TIMESTAMP COMMENT '该记录最后更新时间')
comment '商品表'
partitioned by (dt STRING COMMENT '写入日期')
row format delimited fields terminated by ','
stored as orc
tblproperties ('orc.compress'='SNAPPY');

2. 导入

导入的话我们肯定是要，保留全部的商品，因此我们选择left join 左连接，并且我们还需要只关联昨天的数据。

insert overwrite table dim.dwd_dim_goods_i partition (dt)
select
    t1.id as goods_id,
    t1.goods_no,
    t1.goods_name,
    t2.first_category_id,
    t2.first_category_no,
    t2.first_category_name,
    t2.second_category_id,
    t2.second_category_no,
    t2.second_category_name,
    t2.third_category_id,
    t2.third_category_no,
    t2.third_category_name,
    t1.brand_no,
    t1.spec,
    t1.sale_unit,
    t1.life_cycle_status,
    t1.tax_rate_status,
    t1.tax_rate,
    t1.tax_value,
    t1.order_multiple,
    t1.pack_qty,
    t1.split_type,
    t1.is_sell_by_piece,
    t1.is_self_support,
    t1.is_variable_price,
    t1.is_double_measurement,
    t1.is_must_sell,
    t1.is_seasonal,
    t1.seasonal_start_time,
    t1.seasonal_end_time,
    t1.is_deleted,
    t1.goods_type,
    t1.create_time,
    t1.update_time,
    date_sub(current_date(),1) as dt
from dim.ods_dim_goods_info_f t1
        left join (select * from dim.dwd_dim_category_statistics_i where dt = date_sub(current_date(),1)) t2 
                        on t1.category_no = t2.third_category_no;

1.5 门店商品表

门店商品表：肯定就是门店表与商品分类表进行关联，然后通过店的编号进行关联

1. 建表

CREATE TABLE IF NOT EXISTS dim.dwd_dim_store_goods_i(
    uid                     STRING COMMENT '唯一标识',
    store_no                STRING COMMENT '门店编码',
    goods_no                STRING COMMENT '商品编码',
    goods_name              STRING COMMENT '商品简称',
    first_category_no       STRING COMMENT '一级分类编码',
    first_category_name     STRING COMMENT '一级分类',
    second_category_no      STRING COMMENT '二级分类编码',
    second_category_name    STRING COMMENT '二级分类',
    third_category_no       STRING COMMENT '三级分类编码',
    third_category_name     STRING COMMENT '三级分类',
    is_clear                BIGINT COMMENT '商品是否日清，0-否，1-是',
    is_must_order           BIGINT COMMENT '是否必订品，0-否，1-是',
    is_orderable            BIGINT COMMENT '是否可订，0-否，1-是',
    order_multiple          DECIMAL(27,2) COMMENT '订货倍数',
    min_order_qty           DECIMAL(27,2) COMMENT '最小起订量',
    vendor_no               STRING COMMENT '主供应商编码',
    vendor_name             STRING COMMENT '主供应商名称',
    group_no                STRING COMMENT '采购柜组编码',
    group_name              STRING COMMENT '采购柜组名称',
    dc_no                   STRING COMMENT '采购仓库编码',
    dc_name                 STRING COMMENT '采购仓库名称',
    tag                     BIGINT COMMENT '商品标识，1-传智鲜标品；2-黑马标品;3-生鲜品;4-其它',
    create_time             TIMESTAMP COMMENT '创建时间',
    update_time             TIMESTAMP COMMENT '最后修改时间',
    is_deleted              BIGINT COMMENT '是否删除0:正常；1:删除')
comment '门店商品信息'
partitioned by (dt STRING COMMENT '写入日期')
row format delimited fields terminated by ','
stored as orc
tblproperties ('orc.compress'='SNAPPY');

2. 导入

insert overwrite table dim.dwd_dim_store_goods_i partition (dt)
select
    t1.uid,
    t1.store_no,
    t1.goods_no,
    t1.goods_name,
    t2.first_category_no,
    t2.first_category_name,
    t2.second_category_no,
    t2.second_category_name,
    t2.third_category_no,
    t2.third_category_name,
    t1.is_clear,
    t1.is_must_order,
    t1.is_orderable,
    t1.order_multiple,
    t1.min_order_qty,
    t1.vendor_no,
    t1.vendor_name,
    t1.group_no,
    t1.group_name,
    t1.dc_no,
    t1.dc_name,
    t1.tag,
    t1.create_time,
    t1.update_time,
    t1.is_deleted,
    date_sub(current_date(),1) as dt
from dim.ods_dim_store_goods_f t1 
        join (select * from dim.dwd_dim_category_statistics_i where dt = date_sub(current_date(),1)) t2 
                on t1.category_no = t2.third_category_no;

1.6 日清门店商品表处理

1. 建表

CREATE TABLE IF NOT EXISTS dim.dwd_dim_store_clear_goods_i(
    uid                     STRING COMMENT '唯一标识',
    store_no                STRING COMMENT '门店编码',
    goods_no                STRING COMMENT '商品编码',
    goods_name              STRING COMMENT '商品简称',
    first_category_no       STRING COMMENT '一级分类编码',
    first_category_name     STRING COMMENT '一级分类', 
    second_category_no      STRING COMMENT '二级分类编码',
    second_category_name    STRING COMMENT '二级分类',
    third_category_no       STRING COMMENT '三级分类编码',
    third_category_name     STRING COMMENT '三级分类',
    is_clear                BIGINT COMMENT '商品是否日清，0-否，1-是',
    is_must_order           BIGINT COMMENT '是否必订品，0-否，1-是',
    is_orderable            BIGINT COMMENT '是否可订，0-否，1-是',
    order_multiple          DECIMAL(27,2) COMMENT '订货倍数',
    min_order_qty           DECIMAL(27,2) COMMENT '最小起订量',
    vendor_no               STRING COMMENT '主供应商编码',
    vendor_name             STRING COMMENT '主供应商名称',
    group_no                STRING COMMENT '采购柜组编码',
    group_name              STRING COMMENT '采购柜组名称',
    dc_no                   STRING COMMENT '采购仓库编码',
    dc_name                 STRING COMMENT '采购仓库名称',
    tag                     BIGINT COMMENT '商品标识，1-传智鲜标品；2-黑马标品;3-生鲜品;4-其它',
    create_time             TIMESTAMP COMMENT '创建时间',
    update_time             TIMESTAMP COMMENT '最后修改时间',
    is_deleted              BIGINT COMMENT '是否删除0:正常；1:删除') 
comment '门店日清商品信息'
partitioned by (dt STRING COMMENT '写入日期')
row format delimited fields terminated by ','
stored as orc
tblproperties ('orc.compress'='SNAPPY');

2. 导入
   注意我们只需要导入 ，is_clear 为 1 也就是需要日清的数据

insert overwrite table dim.dwd_dim_store_clear_goods_i partition (dt)
select
    uid,
    store_no,
    goods_no,
    goods_name,
    first_category_no,
    first_category_name,
    second_category_no,
    second_category_name,
    third_category_no,
    third_category_name,
    is_clear,
    is_must_order,
    is_orderable,
    order_multiple,
    min_order_qty,
    vendor_no,
    vendor_name,
    group_no,
    group_name,
    dc_no,
    dc_name,
    tag,
    create_time,
    update_time,
    is_deleted,
    dt
from dim.dwd_dim_store_goods_i where dt = date_sub(current_date(),1) and is_clear = 1;

1.7 门店表处理

每一个门店，都会有实际面积，经营面积，合同面积，公摊面积几种，
假设我们按照 实际面积=8，经营面积7，合同面积=1，公摊面积=2 来分类，要求门店表输出一个面积。我们应该怎么判断呢？

1. 建表

CREATE TABLE IF NOT EXISTS dim.dwd_dim_store_i(
    id                      BIGINT COMMENT '自增主键',
    store_no                STRING COMMENT '分店编号',
    store_name              STRING COMMENT '分店名称',
    store_sale_type         BIGINT COMMENT '门店销售类型',
    store_type_code         BIGINT COMMENT '分店类型',
    city_id                 BIGINT COMMENT '城市ID',
    city_name               STRING COMMENT '城市名称',
    region_code             STRING COMMENT '区域ID',
    region_name             STRING COMMENT '区域名称',
    worker_num              BIGINT COMMENT '员工人数',
    manager_name            STRING COMMENT '经理姓名',
    telephone               STRING COMMENT '分店电话',
    opening_date            STRING COMMENT '开店日期',
    open_time               STRING COMMENT '营业开始时间',
    close_time              STRING COMMENT '营业结束时间',
    `status`                BIGINT COMMENT '状态 1：开店  2：闭店',
    is_deleted              BIGINT COMMENT '是否删除 0:否  1:是',
    create_time             TIMESTAMP COMMENT '创建时间',
    update_time             TIMESTAMP COMMENT '更新时间',
    store_area              DECIMAL(27,2) COMMENT '经营面积',
    decoration_code         STRING COMMENT '装修标识',
    is_day_clear            BIGINT COMMENT '是否日清，1-日清，0-非日清'
)
comment '门店表'
partitioned by (dt STRING COMMENT '写入日期')
row format delimited fields terminated by ','
stored as orc
tblproperties ('orc.compress'='SNAPPY');

2. 导入

• 门店表: 在门店宽表构建时，就添加了门店面积信息和区域名称信息，门店面积信息可以用来计算坪效等，区域名称信息可以用来上卷时显示区域名称。
• 其中门店面积信息可以从分店面积明细表中获取。
  • 先取实际经营面积(8)，
  • 如果取不到（实际经营面积为空或0）再取经营面积(7)。
  • 如果取不到（经营面积为空或0）再取合同面积(1)。
  • 如果取不到（合同面积为空或0）再取外滩面积(2)。
  • 总结一下: 优先级就是 8 > 7 > 1 > 2
• 其中区域名称信息从店组信息表中取，store_group_type_no = '04’即对应区域的编码和名称。
• 其中 management_type_code 和 store_type_code 需要转换为整数类型

想一下，我们怎么能通过优先级去取到对应的值呢？
没错，通过判断就可以，我们常用的判断方法有 if 和 case when ，因为if比较繁琐我们就可以使用case
如果 面积为 8 我们给个变量 1 ，面积为 7 我们给个变量 2，面积为 1我们给个变量 3，面积为 2 我们给个变量 4，
这样我就得到了一列只，通过对这个对变量进行升序操作取最小值也就是优先级最高的面积。

with t1 as (
    select
        store_no,
        area_type_no,
        area,
        row_number() over (
            partition by store_no
            order by (
                case
                   when area_type_no=8 then 1
                   when area_type_no=7 then 2
                   when area_type_no=1 then 3
                   when area_type_no=2 then 4
                   else 99
                end
            ) asc
        ) as rn
    from dim.ods_dim_store_area_info_f
)
, t2 as (
    select * from t1
    where rn = 1
)
insert overwrite table dim.dwd_dim_store_i partition(dt)
select
    t3.id,
    t3.store_no,
    t3.store_name,
    cast(t3.management_type_code as int) as store_sale_type,
    cast(t3.store_type_code as int) as store_type_code,
    t3.city_id,
    t3.city_name,
    t3.region_code,
    t4.store_group_name as region_name, -- 需要关联
    t3.worker_num,
    t3.manager_name,
    t3.telephone,
    t3.opening_date,
    t3.open_time,
    t3.close_time,
    t3.status,
    t3.is_deleted,
    t3.create_time,
    t3.update_time,
    t2.area as  store_area,
    t3.decoration_code,
    if(flag=16, 1, 0) as is_day_clear,
    '2024-06-21' as dt
from dim.ods_dim_store_f t3
    left join (select * from dim.ods_dim_store_group_f where store_group_type_no='04') t4 on t3.region_code=t4.store_group_no
    left join t2 on t3.store_no=t2.store_no;

2. postgrep数据库

我们都知道，我们将数据导入到HIve中将进行处理清洗，在转到本地的数据库提供给报表进行分析。

问题：按我们用什么数据库接受数据？MySQL？
其实并不是，我们在开发中使用postgrep数据库是比较常见的，因为其开源免费还有很强的功能性。

2.1 什么是postgrep数据库

PostgreSQL（有时也被称为“Postgres”或简称为“PG”）是一个强大的、开源的对象-关系数据库管理系统（ORDBMS）。它使用了和许多其他SQL数据库管理系统（如MySQL、SQL
Server、Oracle等）相似的查询语言，但它也提供了许多扩展功能，如复杂查询、外键、触发器、视图、事务完整性、多版本并发控制（MVCC）等。

PostgreSQL的主要特点包括：

• 扩展性：PostgreSQL支持大量的扩展，这些扩展可以增加新的数据类型、函数、操作符、索引类型、过程语言等。
  强大的SQL标准支持：PostgreSQL遵循SQL标准，并提供了许多额外的功能。
• 事务完整性：它支持ACID事务属性，即原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）。
• 并发控制：通过多版本并发控制（MVCC），PostgreSQL允许在没有锁定整个表的情况下进行读和写操作。
• 灵活性：用户定义的函数和操作符允许用户根据需要进行定制。
• 安全性：PostgreSQL提供了多种安全特性，如行级安全性、加密、审计等。
• 社区支持：PostgreSQL有一个庞大的社区，提供了大量的文档、教程、工具和帮助。
• 跨平台：PostgreSQL可以在许多操作系统上运行，包括Linux、Unix、Windows和macOS。
  

2.2 PostgreSQL基本操作

我们在安装好PostgreSQL数据库之后，怎么使用呢？
我们在客户端，使用命令连接。

#psql -h 服务器 -p 端口地址 -d 数据库 -U 用户名     
psql -h 127.0.0.1 -p 5432 -d postgres -U postgres
#密码：itcast123

• 查看帮助命令

help   查看帮助命令

• 数据库/表的基本操作
  查看库
  
  切换数据库

显示所有表

查询语句

select *
from exercise_db.public.courses;

2.3 Data grip 链接PostgreSQL

需求：

在 PostgreSQL 中创建一个名为 db_school 的数据库，并在其中创建一个包含 student_id, name, age, gender 列的 tb_student 表，并为表和列增加注释

create database exercise_db;
create table students(
  id serial primary key ,
  name varchar(20),
  age int
);

create table courses(
  course_id serial primary key ,
  course_name varchar(20),
  course_no int
);

insert into exercise_db.public.students(name,age)  values ('aaa',10), ('bbb',20), ('ccc',30);
insert into exercise_db.public.courses(course_name,course_no)  values ('语文',101), ('数学',102), ('英语',103);


select *
from exercise_db.public.courses;


select *
from students;

3. 基于DataX完成数据导出

准备条件：
在PostgreSQL数据库创建好表

1. 首先我们创建一个数据源
   
   

2. 构建模板
这里我们使用之前的任务模版

3. 创建任务
   注意: hive中以-i结尾的维表是有分区的,每个分区保存一个快照，而postgresql中只保留最新的快照数据。所以构建reader读取hive表时不需要dt字段(手动传递最新分区)，导入到postgresql时，默认只导入最新的快照。
   操作如下:
   注意：在构建reader时，要指定导出的分区，指定的方式是在path中通过传参的方式，${partition}在运行时动态指定。

这个案例中path为: /user/hive/warehouse/dim.db/dwd_dim_goods_i/${partition}
注意: datax工具底层固定找的变量名叫partition,所以此处咱们变量名必须起程partition,
把分区字段值直接替换${partition}  示例: /user/hive/warehouse/dim.db/dwd_dim_goods_i/dt=2024-06-21

注意: 后续循环导出的时候,为了防止postgresql中的历史数据有脏数据，在导入之前可以先清空数据。所以在构建postgresql writer时，需要加上前置sql：truncate table public.dwd_dim_goods_i。