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')

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

--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 在外部就可以运行了

#!/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

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');

#数仓的幂等性
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

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';

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');

-行业数据导入
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