黑马一站制造数仓实战4

- ODS层与DWD层的区别
  - 内容区别
    - ODS：原始数据
    - DWD：对ODS层ETL以后的数据
    - 本次数据来源于Oracle数据库，没有具体的ETL的需求，可以直接将ODS层的数据写入DWD层
  - 设计区别
    - ODS层：Avro格式分区数据表
    - DWD层：Orc格式分区数据表
  - 实现区别
    - ODS层建表：基于avsc文件指定Schema建表

create external table if not exists one_make_ods.ciss_base_areas 
partitioned by (dt string) 
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.avro.AvroSerDe'
STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat'
tblproperties ('avro.schema.url'='hdfs:///data/dw/ods/one_make/avsc/CISS4_CISS_BASE_AREAS.avsc')
location '/data/dw/ods/one_make/full_imp/ciss4.ciss_base_areas'

DWD层建表：自己指定每个字段的Schema建表

create external table if not exists one_make_dwd.ciss_base_areas(
    ID string,
    AREANAME string,
    PARENTID string,
    SHORTNAME string,
    LNG string,
    LAT string,
    RANK bigint,
    POSITION string,
    SORT bigint
) partitioned by (dt string) 
stored as orc
location '/data/dw/dwd/one_make/ciss_base_areas';

DWD层的构建需求

将ODS层的数据表直接加载到DWD层

insert into  dwd partition (dt = '20210101')
select
    *
from ods
where dt=20210101

- 建库需求：创建DWD层数据库one_make_dwd
- 建表需求：将ODS层中的每一张表创建一张对应的DWD层的表

- 问题1：建表的语法是什么？

create external table dwd.tbname(
    字段名 字段类型 字段注释
)
partitioned by (dt string)
location '/data/dw/dwd/one_make/ciss_base_areas';

- 问题2：表的名称名是什么，怎么获取？
  - 不分全量和增量
  - 所有表的名称都在列表中
- 问题3：表的注释怎么来？
  - Oracle元数据中有
- 问题4：表的字段怎么获取？
  - Oracle元数据中有
- 问题5：Oracle中的字段类型如果与Hive中的类型不一致怎么办？
  - 将Oracle中Hive没有类型转换为Hive的类型

DWD建库代码及实现测试

step1：DWD层的数据库名称是什么，建库的语法是什么？

create database if not exists one_make_dwd;

step2：如何实现DWD层数据库的构建？

cHiveTableFromOracleTable.executeCreateDbHQL(CreateMetaCommon.DWD_NAME)

代码测试

    # =================================todo: 5-DWD层建库建表=============================================#
    # 5.1 建库记录日志
    recordLog('DWD层创建数据库')
    # 创建DWD层数据库
    cHiveTableFromOracleTable.executeCreateDbHQL(CreateMetaCommon.DWD_NAME)

运行代码，查看结果

DWD建表代码及实现测试

step1：如何获取所有表名？

allTableName = [i for j in tableNameList for i in j]

step2：建表的语句是什么，哪些是动态变化的？

create external table if not exists one_make_dwd.ciss_base_areas(
    ID string comment '字段的注释',
    AREANAME string comment '字段的注释',
    PARENTID string comment '字段的注释',
    SHORTNAME string comment '字段的注释',
    LNG string comment '字段的注释',
    LAT string comment '字段的注释',
    RANK bigint comment '字段的注释',
    POSITION string comment '字段的注释',
    SORT bigint comment '字段的注释'
) 
comment '表的注释'
partitioned by (dt string) stored as orc
location '/data/dw/dwd/one_make/ciss_base_areas';

 - - 动态变化的信息如下：
    - 表名，表的注释
    - 字段
    - 路径
- step3：怎么获取字段信息？
  Oracle字段类型与Hive/SparkSQL字段类型不一致怎么办？
    - timestamp => long
  - number => bigint | dicimal
  - other => String
- step4：HDFS上的路径是什么？

/data/dw/dwd/one_make/tableName

- - step5：如何实现自动化
    - 遍历表名，对每张表调用自动化建表的方法：数据库名称、表的名称、None【不分全量或者增量】
    - 从Oracle中获取字段名，并实现类型转换
    - 添加表的注释、分区信息
    - 添加表的存储格式
    - 指定表的存储路径
    - 执行SQL语句
- 代码测试

    # 5.2 建表记录日志
    recordLog('DWD层创建表...')
    # 将所有表名合并到一个列表中
    allTableName = [i for j in tableNameList for i in j]
    # 取出每张表名
    for tblName in allTableName:
        # 实现DWD层建表：数据库one_make_dwd,表名,
        cHiveTableFromOracleTable.executeCreateTableHQL(CreateMetaCommon.DWD_NAME, tblName, None)

运行代码，查看结果

实现DWD层的构建思路分析

- 抽取目标：将ODS层中每张表的数据抽取到DWD层对应的数据表中
- 抽取语法

insert overwrite table dwd.tbname partition(dt = '20210101')
select
    字段1,
    字段2,
    字段3,
    ……
from ods.tbname
where dt = '20210101';

DWD层数据抽取

- step1：如何获取所有表名？
  - 所有表名都在list中
- step2：如何获取所有字段的信息？
  - 从Oracle中获取

代码测试

def loadTable(orclConn, hiveConn, tableName, partitionValue):
    """
    加载ODS层表的数据到DWD层
    :param orclConn: Oracle连接对象
    :param hiveConn: Hive连接对象
    :param tableName: 表名
    :param partitionValue: 分区值
    :return: None
    """
    # 从Oracle中获取表的元数据信息
    tableMeta = OracleMetaUtil.getTableMeta(orclConn, tableName.upper())
    # SQL拼接：insert overwrite table one_make_dwd.tbname partition(dt='20210101') select
    buffer = [
        "insert overwrite table " + CreateMetaCommon.DWD_NAME + "." + tableMeta.tableName + " partition(dt=" + partitionValue + ")\n",
        "select\n"]
    # 拼接所有列名
    allColumns = ', '.join(cname for cname in tableMeta.getColumnNameList())
    buffer.append(allColumns + "\n")
    # 拼接：form ods层的表
    buffer.append("from " + CreateMetaCommon.ODS_NAME + "." + tableMeta.tableName + "\n")
    # 过滤分区
    buffer.append("where dt='" + partitionValue + "'")
    logging.warning(f'SparkSql插入数据，sql\n{"".join(buffer).lower()}')
    # 将整个SQL语句转换为小写
    loadSQL = ''.join(buffer).lower()
    # 获取Hive连接的一个游标
    cursor = hiveConn.cursor()
    # 执行SQL语句，加载数据
    cursor.execute(loadSQL)

# =================================todo: 6-DWD层数据抽取=============================================#
    # 记录日志
    recordWarnLog('DWD层加载数据，此操作将启动Spark JOB执行，请稍后...')
    # 取出每张表的表名
    for tblName in allTableName:
        recordLog(f'加载dwd层数据到{tblName}表...')
        try:
            # 从ODS层抽取数据到DWD层：oracle连接、Hive连接、表名、分区的值：20210101
            LoadData2DWD.loadTable(oracleConn, hiveConn, tblName, partitionVal)
        except Exception as error:
            print(error)
        recordLog('完成!!!')

掌握整体代码的重难点

- 问题1：怎么读取表名的？
  - 表名：文件
  - FileUitil：读取文件
  - TableNameUtil：将表名拆分全量列表和增量列表
- 问题2：怎么构建连接的？
  - Oracle：cx_Oracle
    - conn(hostname,port,username,password,sid)
  - Hive/SparkSQL：PyHive
    - conn(hostname,port,username,password)
  - 执行SQL规则
    - step1：必须构建一个连接
    - step2：从连接中获取游标，定义SQL
    - step3：使用游标执行SQL语句
    - step4：释放资源
- 问题3：为什么要把连接地址写在文件里？
  - 开发规范
  - df.write.jdbc(url,table,properties- - )
    - 地址
    - 端口
    - 用户名
    - 密码
    - 表名
- 问题4：怎么拼接SQL语句的？
  - 字符串的拼接
- 问题5：怎么执行SQL语句的？
  - 游标：execute(SQL)
- 问题6：怎么获取Oracle的表的信息的？
  - Oracle将每张表的每一列的信息都存储Oracle系统表中
    - 通过SQL就可以查询到表的这些信息
  - TableMeta：表的信息
    - 表名：String
    - 表的注释：String
    - 列的信息：List[ColumnMeta]
  - ColumnMeta：列的信息
    - 列名
    - 列注释
    - 列类型
    - 长度
    - 精度