day03_新零售课程笔记

今日内容:

• 1- sqoop的基本使用操作 (掌握)
• 2- 业务数据准备工作 (参考笔记搞定)
• 3- HIVE的基础优化 (了解, 需要记录, 形成HIVE的优化文档)
• 4- 完成ODS层数据采集操作 (全量采集 和 增量采集) (操作,尽量独立尝试完成)

1 sqoop的基本使用操作

    sqoop是apache旗下顶级项目. 主要是用于 RDBMS 和 大数据生态圈之间的数据导入导出的工具, 从RDBMS 到大数据生态圈 是导入操作, 反之为导出操作

sqoop本质上也是一款翻译软件, 将sqoop的命令翻译为 MR程序

关于使用sqoop 将数据导入到HIVE, 支持两种导入方案: 原生导入方案 和 hcatalog方式

区别点:  
    如果使用原生导入方式, 导入HIVE , 仅支持 textFile导入方式
    hcatalog支持数据存储方案比较多: textFile, ORC, sequence, parquet....
    原生方式支持数据覆盖导入
    hcatalog仅支持追加导入
    原生方式在导入时候, 根据字段的顺序, 导入到HIVE中
    hcatalog在导入的时候, 是根据字段的名称导入的    
    (此部分建议在导入到HIVE , hive表字段的顺序 和 mysql表字段顺序保持一致, 名称保持一致)
后续主要采用 hcatalog的导入方式, 因为建表的时候, 主要存储格式为 ORC

1.1 基本使用操作

• 1- 如何查看 sqoop的 帮助文档

sqoop  help
如何查看某个操作下的相关的参数信息:
sqoop 操作 --help

• 2- 查询mysql中所有的库有那些?

思考: 连接mysql需要知道什么信息呢? 
    1- 用户名  2- 密码  3- 连接地址 
sqoop list-databases --connect jdbc:mysql://hadoop01:3306  --username root  --password 123456

• 3- 查询mysql中scm中所有的表

sqoop list-tables \
--connect jdbc:mysql://hadoop01:3306/scm \
--username root \
--password 123456


\ : 未完待续

1.2 数据全量导入操作

准备工作:

create database test default character set utf8mb4 collate utf8mb4_unicode_ci;
use test;

create table emp
(
    id     int         not null
        primary key,
    name   varchar(32) null,
    deg    varchar(32) null,
    salary int         null,
    dept   varchar(32) null
);

INSERT INTO emp (id, name, deg, salary, dept) VALUES (1201, 'gopal', 'manager', 50000, 'TP');
INSERT INTO emp (id, name, deg, salary, dept) VALUES (1202, 'manisha', 'Proof reader', 50000, 'TP');
INSERT INTO emp (id, name, deg, salary, dept) VALUES (1203, 'khalil', 'php dev', 30000, 'AC');
INSERT INTO emp (id, name, deg, salary, dept) VALUES (1204, 'prasanth', 'php dev', 30000, 'AC');
INSERT INTO emp (id, name, deg, salary, dept) VALUES (1205, 'kranthi', 'admin', 20000, 'TP');

create table emp_add
(
    id     int         not null
        primary key,
    hno    varchar(32) null,
    street varchar(32) null,
    city   varchar(32) null
);

INSERT INTO emp_add (id, hno, street, city) VALUES (1201, '288A', 'vgiri', 'jublee');
INSERT INTO emp_add (id, hno, street, city) VALUES (1202, '108I', 'aoc', 'sec-bad');
INSERT INTO emp_add (id, hno, street, city) VALUES (1203, '144Z', 'pgutta', 'hyd');
INSERT INTO emp_add (id, hno, street, city) VALUES (1204, '78B', 'old city', 'sec-bad');
INSERT INTO emp_add (id, hno, street, city) VALUES (1205, '720X', 'hitec', 'sec-bad');

create table emp_conn
(
    id    int         not null
        primary key,
    phno  varchar(32) null,
    email varchar(32) null
);

INSERT INTO emp_conn (id, phno, email) VALUES (1201, '2356742', 'gopal@tp.com');
INSERT INTO emp_conn (id, phno, email) VALUES (1202, '1661663', 'manisha@tp.com');
INSERT INTO emp_conn (id, phno, email) VALUES (1203, '8887776', 'khalil@ac.com');
INSERT INTO emp_conn (id, phno, email) VALUES (1204, '9988774', 'prasanth@ac.com');
INSERT INTO emp_conn (id, phno, email) VALUES (1205, '1231231', 'kranthi@tp.com');

• 如何全量将数据导入到HDFS中:
  • 需求一: 将 emp表中数据导入到HDFS中

需要知道什么信息?  数据库的基本信息(连接地址, 用户名, 密码, 表名)  目的地的路径信息

方式一:  
sqoop import \
--connect jdbc:mysql://hadoop01:3306/test \
--username root \
--password 123456 \
--table emp

说明: 
    1- 当不指定导出路径的时候, 默认会将数据导入到当前操作用户的HDFS的家目录下, 再次目录下以表名创建一个文件夹, 将数据放置到这个文件夹中
    2- 发现在导入数据的时候, 有多少条数据, 就会运行多少个mapTask, 最高和cpu核数相等
    3- 数据之间的分隔符号为 逗号

思考: 是否可以将其导入到其他位置呢? --target-dir  和  --delete-target-dir 
sqoop import \
--connect jdbc:mysql://hadoop01:3306/test \
--username root \
--password 123456 \
--table emp \
--delete-target-dir \
--target-dir /sqoop_works/emp

说明:
    --target-dir : 将数据导入到HDFS的那个位置中
    --delete-target-dir: 如果目的地路径以存在, 先删除

思考: 是否可以设置其mapTask的数量呢?  -m  和 --split-by
sqoop import \
--connect jdbc:mysql://hadoop01:3306/test \
--username root \
--password 123456 \
--table emp \
--delete-target-dir \
--target-dir /sqoop_works/emp \
-m 2 \
--split-by id

说明:
    如果  -m为1 , 表示只允许一个mapTask, 此时可能省略 --split-by 
    --split-by  表示按照那个字段进行切割数据表, 一般设置为主键字段, 如果主键字段是多个, 那么就写多个, 用逗号隔开


思考: 是否可以调整分隔符号呢? 比如说 设置为 |  参数: --fields-terminated-by 
sqoop import \
--connect jdbc:mysql://hadoop01:3306/test \
--username root \
--password 123456 \
--table emp \
--delete-target-dir \
--target-dir /sqoop_works/emp \
--fields-terminated-by  '|' \
-m 1

• 如何全量导入数据到HIVE中
  • 需求: 将 emp_add 这个表的全部数据导入到HIVE的对应表中

# 1- 在HIVE中创建目标表
create database if not exists day03_xls;

create table if not exists day03_xls.emp_add(
    id int,
    hno string,
    street string,
    city string
)
row format delimited fields terminated by '\t' stored as orc;

# 2- 编写 sqoop命令 完成数据导入操作
sqoop import \
--connect jdbc:mysql://hadoop01:3306/test \
--username root \
--password 123456 \
--table emp_add \
--fields-terminated-by  '\t' \
--hcatalog-database 'day03_xls' \
--hcatalog-table 'emp_add' \
-m 1


注意:
    由于 hive表的存储格式为 orc, 所以无法使用sqoop的原生导入方案, 必须使用hcatalog

1.3 数据条件导入数据

• 方式一: 通过 where条件的方式, 将部分数据导入到HDFS中

# 1- 以 emp为例, 在这个表中, 新增一条数据, 尝试将这一条数据导入到HDFS中
INSERT INTO  test.emp VALUES(1206,'zhangsan','bigdata dev','30000','TP');


# 2- 通过 sqoop实现数据条件导入
sqoop import \
--connect jdbc:mysql://hadoop01:3306/test \
--username root \
--password 123456 \
--table emp \
--where 'id > 1205' \
--delete-target-dir \
--target-dir /sqoop_works/emp_1 \
--fields-terminated-by  '|' \
-m 1

• 方式二: 通过 SQL的方式, 将部分数据导入到HDFS中:

# 1- 以 emp为例, 在这个表中, 新增一条数据, 尝试将这一条数据导入到HDFS中
INSERT INTO  test.emp VALUES(1207,'lisi','bigdata dev','50000','TP');

# 2- 使用sqoop条件导入数据: 采用 SQL形式
sqoop import \
--connect jdbc:mysql://hadoop01:3306/test \
--username root \
--password 123456 \
--query 'select * from emp where id > 1206 and $CONDITIONS' \
--delete-target-dir \
--target-dir /sqoop_works/emp_2 \
--fields-terminated-by  '|' \
-m 1

注意: 
    1- 当使用 --query 方式的时候, 不允许在使用 --table, 因为 SQL中已经明确需要导入那个表的数据
    2- 当使用 --query 方式的时候, 编写的SQL语句必须添加where条件, 条件最后必须要跟 $CONDITIONS, 如果使用双引号包裹SQL, $前面必须加一个 \ 进行转义操作, 当如果没有条件的时候,建议写成: where 1=1 and $CONDITIONS

• 如何导入HIVE呢? 以其中SQL为例

#1- 先在mysql中, 新增一条数据:
INSERT INTO  test.emp_add VALUES(1206,'412Q','aoc','sec-bad');

#2- 使用 sqoop 完成导入操作
sqoop import \
--connect jdbc:mysql://hadoop01:3306/test \
--username root \
--password 123456 \
--query 'select * from emp_add where id = 1206 and $CONDITIONS' \
--hcatalog-database 'day03_xls' \
--hcatalog-table 'emp_add' \
--fields-terminated-by  '\t' \
-m 1


思考: 如果HIVE中表的字段名字 和 mysql中表字段的名字不一样, 如何搞呢?  
    使用SQL方式来导入, 在导入的时候, 通过对字段重新起别名的方式进行导入操作  
        select id as sid from xxx

1.4 数据全量导出操作

• 需求: 将HIVE中 emp_add表中所有的数据全量导出MySQL中

步骤一:  在MySQL中创建目标表
CREATE TABLE test.emp_add_mysql (
    id INT,
    hno VARCHAR(20),
    street VARCHAR(20),
    city VARCHAR(20)
);

步骤二: 执行sqoop导出操作

sqoop export \
--connect jdbc:mysql://hadoop01:3306/test \
--username root \
--password 123456 \
--table emp_add_mysql \
--fields-terminated-by  '\t' \
--hcatalog-database 'day03_xls' \
--hcatalog-table 'emp_add' \
-m 1


注意:
    在执行数据导出的时候, 必须先创建目标表, 然后才能触发执行导出

1.5 相关的sqoop参数

— null-string 和 —null-non-string 主要用于将数据源中 NULL数据,转换为 空

2. 业务数据准备工作

2.1 安装 DBeaver

• 1. 将资料中提供的 DBeaver压缩包, 将其解压到一个没有中文和空格目录下

• 1. 对dbeaver软件进行绿化操作:
  • 2.1: 将绿化包中 dbeaver-agent.jar 拷贝到 dbeaver的解压目录下

• 2.2: 编辑 dbeaver下的 dbeaver.ini文件

  添加如下的内容: 
  -javaagent:dbeaver-agent.jar的绝对路径地址
  

  

• 2.3 在电脑中添加jdk环境变量

  • 2.3.1 将资料中提供的 jdk1.8的目录放置到一个没有中文和空格目录下
  • 2.3.2 打开电脑的环境变量的配置:

• 2.4 启动dbeaver:
  • 启动后, 如果提示更新或者其他, 一律选择不处理, 直接关闭, 或者取消

2.2 在dbeaver连接mysql数据库

2.3 导入业务端数据

• 业务数据脚本

• 基于dbeaver实现数据导入操作

说明: 如果部分同学, 使用dbeaver无法将数据导入进去, 切换为 sqlyog完成即可, 不要使用dbeaver导入了

方式三:

执行:

命令：
    source  SQL脚本路径 


前提:  
    需要下将脚本上传到linux上

3. hive的基础优化(都不要去调整)

3.1 HDFS的副本数量的调整

 一般HDFS的副本数量设置为3, 如果数据不是特别重要建议设置为2

注意: 在HDFS的3.0版本后, 起始可以调整为1.5 (1个副本  + 纠删码(占用副本50%大小))

3.2 nodemanager的相关基础配置 (测试暂不调整)

• CPU配置操作:

  • 配置项：yarn.nodemanager.resource.cpu-vcores (直接在CM上修改YARN配置即可)
  • 默认值为: 8 yarn不会自动检测服务器的CPU核心数, 一般都需要手动调整

  • 推荐配置: 对应服务器是多少核 就配置多少核

    查看服务器CPU的核心数: 
    方式一: 命令行方式
    grep 'processor' /proc/cpuinfo | sort -u | wc -l
    方式二: 直接通过CM查询
    

    
    

  • 如何配置: 直接在CM上进行调整即可

• 内存配置:
  • 配置项：
    • yarn.nodemanager.resource.memory-mb (直接在CM上进行配置即可)
    • yarn.scheduler.maximum-allocation-mb :调度器内存配置, 和第一个保持一致
    • yarn.app.mapreduce.am.command-opts : 略小于第一个配置内存大小
  • 默认值: 8GB yarn不会自动检测服务器的内存值, 一般都需要手动调整
  • 推荐配置: 剩余内存 * 0.8 左右
  • 如何配置: 直接在CM上配置即可

• 本地目录的配置
  • 配置项:yarn.nodemanager.local-dirs（Yarn）
  • 说明: 在执行MR过程中, nodemanager产生临时文件存储在那些目录中
  • 推荐配置: 服务器挂载了几个磁盘, 就配置几个目录 一个目录对应其中一个磁盘位置

    磁盘挂载目录的查询命令:
    df -lh
    

3.3 MapReduce的内存配置

相关内存配置内容 (一般都是先不配置, 正常使用即可, 如果发现执行效率非常慢, 可以尝试调整内存配置)

mapreduce.map.memory.mb:  用于配置map的内存大小
mapreduce.reduce.memory.mb: 用于配置reduce的内存大小

mapreduce.map.java.opts : 用于配置map的jvm的内存大小
mapreduce.reduce.java.opts: 用于配置reduce的jvm的内存大小

说明:  
    1. 对应jvm的内存配置要略小于 map或者reduce的内存大小配置
    2. map和reduce的内存配置大小, 不能超过nodemanager的内存大小

一行配置 均可在CM上直接配置

3.4 hive的基础配置

• hiveserver2的java堆栈的内存配置
  • 配置项: HiveServer2 的 Java 堆栈大小（字节）

此配置后续会有一个报错, 反应就是此配置过小:

此操作在遇到问题, 在进行调整, 此时暂不调整

• HIVE并行编译
  • 默认情况, HIVE同时只能编译一段HIVE SQL, 并上锁
  • 配置项: hive.driver.parallel.compilation

• 生成动态分区的线程数量:
  • 配置项: hive.load.dynamic.partitions.thread (直接在CM上调整)
  • 默认值: 15
  • 此配置, 值越大, 执行效率越高, 但是资源损耗也会越高
• 监听输入的线程数量:
  • 配置项: hive.exec.input.listing.max.threads
  • 默认值: 15
  • 此配置, 值越大, 读取效率越高, 但是资源损耗也会越高

3.5 压缩的配置

HIVE 本质上, 将SQL翻译为MR , 所以hive压缩配置, 更多指的是对MR的压缩配置操作

关于压缩的相关的配置:

set hive.exec.compress.intermediate=true; 是否开启hive的支持中间结果压缩配置  默认关闭的

mapreduce.map.output.compress : 是否开启map端压缩配置   默认开启的
mapreduce.map.output.compress.codec: map端采用何种压缩方案
    选择为: 
        org.apache.hadoop.io.compress.SnappyCodec

set hive.exec.compress.output=true; 是否开启hive的最终结果的压缩配置  默认是关闭的

mapreduce.output.fileoutputformat.compress: 是否开启reduce端压缩配置   默认关闭的
mapreduce.output.fileoutputformat.compress.codec : reduce端采用何种压缩方案
    选择为: 
        org.apache.hadoop.io.compress.SnappyCodec
mapreduce.output.fileoutputformat.compress.type : 配置压缩方案
    选择为: BLOCK(块压缩)


注意:
    只要不是带有 set操作的配置, 都是直接在cm上进行配置操作

    带有set的 是需要在客户端中进行配置的

均可以直接在CM上进行配置

3.6 执行引擎选择:

配置项: hive.execution.engine

4. 完成ODS层数据采集操作

作用: 对接数据源, 一般和数据源保持相同的粒度

ODS层: 处于在HIVE端

业务数据: MySQL

目标: 将MySQL中业务库的表数据 导入到 ODS层中

技术: sqoop 完成导入的操作

4.1 数据存储格式和压缩方案

• 存储格式:

    在hive中, 数据存储格式主要分为两大类:  行式存储  和 列式存储

    行式存储(textFile):
        优点: 可读性较好  执行 select  * 效率比较高
        弊端:  耗费磁盘资源  执行 select 字段 效率比较低

    列式存储(ORC):
        优点: 节省磁盘空间. 执行 select 字段 效率比较高
        弊端: 执行 select * 效率比较低 , 可读性不是特别好

    ORC是兼具行式存储优势又具有列式存储优势, 数据按行分块, 每块中按列存储数据, 同时在每个块内部, 对数据构建索引, 提升查询的效率

思考: 在hive建表中, 一般采用那种存储格式呢?  
    在hive中, 一般我们的选择都是ORC存储格式, 除非需求对接的数据源是普通文本文件数据, 此时会让对接此文件的表构建为textFile,其余的层次结构的表依然使用ORC

• 压缩方案:

思考: 压缩有什么用? 能够在有限的空间下, 存储更多的数据

在进行压缩的时候, 压缩的方案其实有很多种: ZIP(GZIP), SNAPPY, LZO,ZLIB ....

思考, 具体使用那种压缩的方案呢?  性价比比较高的 (压缩比, 解压缩的性能)
    zlib(gzip):  具有良好的压缩比, 但是解压缩的性能一般 
    snappy: 具有良好的解压缩的性能, 同时具有较好的压缩比, 弊端 没有zlib压缩比好, 同时hadoop默认原生是不支持snappy压缩的(CDH版本直接支持的)


本项目采用那种方案?  主要采用snappy
    在 ODS层, 一般会使用zlib
    在其他层次中, 一般采用snappy

说明: 
    如果读取次数较少, 写入了较大, 优先保证压缩比   --- zlib(gzip)  比如说 ODS层
    如果读取次数比较高, 优先保障解压缩性能   -- snappy  比如说 DW层相关的表

    如果不清楚, 建议使用snappy, 或者如果空间足够, 统一采用snappy也没有问题

• 创建表的时候, 选择内部表, 还是外部表?

判断标准: 对表数据是否有管理的权限

有权限删除数据, 那么我们可以构建内部表, 当然也可以构建外部表
如果没有权限删除数据, 只能构建外部表

内部表转换为外部表: 
    alter table 表名 set tblproperties('EXTERNAL'='FALSE');
    通过true和false 来修改是否为内部表还是外部表

一般来说, 在数仓中,  除了ODS层可能会出现外部表以外, 其余的层次结构, 大多数还是内部表

• 创建表的时候, 是否需要构建分区表呢?

    一般情况下, 都是分区表(分区的字段大多数的都是以时间为主)

4.2 数据同步方式

• 1- 全量覆盖同步方式

适用于:  
    表数据变更的频次并不多,不需要记录其历史数据 而且整个表数据量相对较少  这个时候可以采用全量覆盖的操作

操作方式: 
    每次同步数据, 都是要先将原有的数仓中表数据全部删除, 然后重新从业务端导入即可
    建表的时候, 不需要构建分区表

比如说: 地区表  时间表

• 2- 仅新增同步方式

适用于: 
    业务端数据只会有新增的操作, 不会有变更的时候, 数据量比较多

操作方式: 
    在数仓中建表的时候, 需要构建分区表, 分区字段和同步数据的周期是一致的, 比如说: 每天都需要同步数据, 分区字段 需要按天  如果每月同步一次, 分区字段按照月
    每次进行同步的时候, 将对应周期下的新增数据放置到对应日期分区下

比如说:  登录日志表, 访问日志表

• 3- 新增及更新同步方式

, 比如说: 每天都需要同步数据, 分区字段 需要按天  如果每月同步一次, 分区字段按照月
适用于: 
    业务端表数据既有更新操作, 又有新增操作的时候, 而且数据量比较多

操作方式: 
    在数仓中建表的时候, 需要构建分区表 , 分区字段和同步数据的周期是一致的, 比如说: 每天都需要同步数据, 分区字段 需要按天  如果每月同步一次, 分区字段按照月
    每次进行同步的时候, 将对应这个周期的下新增数据和更新数据放置到对应日期分区下即可

比如说:
    订单表, 商品表, 用户表....

• 4- 全量同步方式(了解)

适用于:
    业务端数据量不是特别大, 但是也存在更新和新增, 而且不需要保留太多的历史版本

操作方式:
    在数仓中建表的时候, 需要构建分区表, 分区字段和同步数据的周期是一致的, 比如说: 每天都需要同步数据, 分区字段 需要按天  如果每月同步一次, 分区字段按照月
    每次导入都是导入截止当前时间的全量数据, 定期将历史的日期数据删除即可

4.3 中文乱码问题

注意: 在后续hive中建表的时候, 如果表字段说明信息是中文, 可能hive会出现乱码情况, 类似于下图

解决方案:

在mysql的hive库中, 执行一下SQL即可:
use hive;
alter table COLUMNS_V2 modify column COMMENT varchar(256) character set utf8;
alter table TABLE_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;
alter table PARTITION_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8 ;
alter table PARTITION_KEYS modify column PKEY_COMMENT varchar(4000) character set utf8;
alter table INDEX_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;

4.4 创建ODS层相关的表

此操作, 需要在hive客户端中执行, ODS层所有的操作, 需要在本地环境演示, 云端环境不需要处理, 因为云端环境以及帮大家将ODS层工作全部做完了, 只需要做ODS层以上的工具即可

• 1- 创建库

drop database if exists yp_ods;
create  database if not exists yp_ods;

• 2- 构建ODS层表

  建表说明: 
   ODS层表与 业务库的表保持一致, 也就说, 业务库中有哪些表, 那么我们就需要在ODS层构建有那些表, 表中到的字段也要一致, 额外在HIVE建表的时候, 需要多加一个分区字段, 用于标记数据在何时导入进来的
  
   然后判断, 那些表是属于全量覆盖的表, 那些表是属于仅新增的表, 那些表是属于全量及更新的表, 基于各个不同同步方式, 构建对应表即可
  

• 2.1- 全量覆盖表: ```sql — 区域表: t_district DROP TABLE if exists yp_ods.t_district; CREATE TABLE yp_ods.t_district ( id string COMMENT ‘主键ID’, code string COMMENT ‘区域编码’, name string COMMENT ‘区域名称’, pid int COMMENT ‘父级ID’, alias string COMMENT ‘别名’ ) comment ‘区域字典表’ row format delimited fields terminated by ‘\t’ stored as orc tblproperties (‘orc.compress’=’ZLIB’);

— 日期表: t_date drop table yp_ods.t_date; CREATE TABLE yp_ods.t_date ( dim_date_id string COMMENT ‘日期’, date_code string COMMENT ‘日期编码’, lunar_calendar string COMMENT ‘农历’, year_code string COMMENT ‘年code’, year_name string COMMENT ‘年名称’, month_code string COMMENT ‘月份编码’, month_name string COMMENT ‘月份名称’, quanter_code string COMMENT ‘季度编码’, quanter_name string COMMENT ‘季度名称’, year_month string COMMENT ‘年月’, year_week_code string COMMENT ‘一年中第几周’, year_week_name string COMMENT ‘一年中第几周名称’, year_week_code_cn string COMMENT ‘一年中第几周（中国）’, year_week_name_cn string COMMENT ‘一年中第几周名称（中国’, week_day_code string COMMENT ‘周几code’, week_day_name string COMMENT ‘周几名称’, day_week string COMMENT ‘周’, day_week_cn string COMMENT ‘周(中国)’, day_week_num string COMMENT ‘一周第几天’, day_week_num_cn string COMMENT ‘一周第几天（中国）’, day_month_num string COMMENT ‘一月第几天’, day_year_num string COMMENT ‘一年第几天’, date_id_wow string COMMENT ‘与本周环比的上周日期’, date_id_mom string COMMENT ‘与本月环比的上月日期’, date_id_wyw string COMMENT ‘与本周同比的上年日期’, date_id_mym string COMMENT ‘与本月同比的上年日期’, first_date_id_month string COMMENT ‘本月第一天日期’, last_date_id_month string COMMENT ‘本月最后一天日期’, half_year_code string COMMENT ‘半年code’, half_year_name string COMMENT ‘半年名称’, season_code string COMMENT ‘季节编码’, season_name string COMMENT ‘季节名称’, is_weekend string COMMENT ‘是否周末（周六和周日）’, official_holiday_code string COMMENT ‘法定节假日编码’, official_holiday_name string COMMENT ‘法定节假日’, festival_code string COMMENT ‘节日编码’, festival_name string COMMENT ‘节日’, custom_festival_code string COMMENT ‘自定义节日编码’, custom_festival_name string COMMENT ‘自定义节日’, update_time string COMMENT ‘更新时间’ ) COMMENT ‘时间维度表’ row format delimited fields terminated by ‘\t’ stored as orc tblproperties (‘orc.compress’ = ‘ZLIB’);

   - 2.2 - 仅新增同步表:
```sql
-- 订单评价表 : t_goods_evaluation
DROP TABLE if exists yp_ods.t_goods_evaluation;
CREATE TABLE yp_ods. ( 
    `id` string, 
    `user_id` string COMMENT '评论人id', 
    `store_id` string COMMENT '店铺id', 
    `order_id` string COMMENT '订单id', 
    `geval_scores` INT COMMENT '综合评分', 
    `geval_scores_speed` INT COMMENT '送货速度评分0-5分(配送评分)', 
    `geval_scores_service` INT COMMENT '服务评分0-5分', 
    `geval_isanony` TINYINT COMMENT '0-匿名评价，1-非匿名', 
    `create_user` string, 
    `create_time` string, 
    `update_user` string, 
    `update_time` string, 
    `is_valid` TINYINT COMMENT '0 ：失效，1 ：开启'
)
comment '商品评价表' 
partitioned by (dt string) 
row format delimited fields terminated by '\t' stored as orc tblproperties ('orc.compress'='ZLIB');

-- 登录记录表:  t_user_login
DROP TABLE if exists yp_ods.t_user_login;
CREATE TABLE yp_ods.t_user_login( 
    id string, 
    login_user string, 
    login_type string COMMENT '登录类型（登陆时使用）',
    client_id string COMMENT '推送标示id(登录、第三方登录、注册、支付回调、给用户推送消息时使用)', 
    login_time string, 
    login_ip string, 
    logout_time string 
)
COMMENT '用户登录记录表' 
partitioned by (dt string)
row format delimited fields terminated by '\t' stored as orc tblproperties ('orc.compress' = 'ZLIB');

-- 订单组支付表:  t_order_pay
DROP TABLE if exists yp_ods.t_order_pay;
CREATE TABLE yp_ods.t_order_pay ( 
    id string, 
    group_id string COMMENT '关联shop_order_group的group_id,一对多订单', 
    order_pay_amount DECIMAL(11,2) COMMENT '订单总金额;', 
    create_date string COMMENT '订单创建的时间,需要根据订单创建时间进行判断订单是否已经失效', 
    create_user string, 
    create_time string, 
    update_user string, 
    update_time string, 
    is_valid TINYINT COMMENT '是否有效 0: false; 1: true; 订单是否有效的标志'
)
comment '订单支付表' 
partitioned by (dt string) 
row format delimited fields terminated by '\t' stored as orc tblproperties ('orc.compress' = 'ZLIB');

• 2.3 - 新增及更新同步表 ```sql — 商品评价明细表 t_goods_evaluation_detail DROP TABLE if exists yp_ods.t_goods_evaluation_detail; CREATE TABLE yp_ods.t_goods_evaluation_detail ( id string, user_id string COMMENT ‘评论人id’, store_id string COMMENT ‘店铺id’, goods_id string COMMENT ‘商品id’, order_id string COMMENT ‘订单id’, order_goods_id string COMMENT ‘订单商品表id’, geval_scores_goods INT COMMENT ‘商品评分0-10分’, geval_content string, geval_content_superaddition string COMMENT ‘追加评论’, geval_addtime string COMMENT ‘评论时间’, geval_addtime_superaddition string COMMENT ‘追加评论时间’, geval_state TINYINT COMMENT ‘评价状态 1-正常 0-禁止显示’, geval_remark string COMMENT ‘管理员对评价的处理备注’, revert_state TINYINT COMMENT ‘回复状态0未回复1已回复’, geval_explain string COMMENT ‘管理员回复内容’, geval_explain_superaddition string COMMENT ‘管理员追加回复内容’, geval_explaintime string COMMENT ‘管理员回复时间’, geval_explaintime_superaddition string COMMENT ‘管理员追加回复时间’, create_user string, create_time string, update_user string, update_time string, is_valid TINYINT COMMENT ‘0 ：失效，1 ：开启’ ) comment ‘商品评价明细’ partitioned by (dt string) row format delimited fields terminated by ‘\t’ stored as orc tblproperties (‘orc.compress’=’ZLIB’);

— 订单配送详细信息表 : t_order_delievery_item DROP TABLE if exists yp_ods.t_order_delievery_item; CREATE TABLE yp_ods.t_order_delievery_item ( id string COMMENT ‘主键id’, shop_order_id string COMMENT ‘订单表ID’, refund_order_id string, dispatcher_order_type TINYINT COMMENT ‘配送订单类型1.支付单; 2.退款单’, shop_store_id string COMMENT ‘卖家店铺ID’, buyer_id string COMMENT ‘购买用户ID’, circle_master_user_id string COMMENT ‘圈主ID’, dispatcher_user_id string COMMENT ‘配送员ID’, dispatcher_order_state TINYINT COMMENT ‘配送订单状态:0.待接单.1.已接单,2.已到店.3.配送中 4.商家普通提货码完成订单.5.商家万能提货码完成订单。6，买家完成订单’, order_goods_num TINYINT COMMENT ‘订单商品的个数’, delivery_fee DECIMAL(11,2) COMMENT ‘配送员的运费’, distance INT COMMENT ‘配送距离’, dispatcher_code string COMMENT ‘收货码’, receiver_name string COMMENT ‘收货人姓名’, receiver_phone string COMMENT ‘收货人电话’, sender_name string COMMENT ‘发货人姓名’, sender_phone string COMMENT ‘发货人电话’, create_user string, create_time string, update_user string, update_time string, is_valid TINYINT COMMENT ‘是否有效 0: false; 1: true’ ) comment ‘订单配送详细信息表’ partitioned by (dt string) row format delimited fields terminated by ‘\t’ stored as orc tblproperties (‘orc.compress’ = ‘ZLIB’);

— 所有交易记录信息 t_trade_record DROP TABLE if exists yp_ods.t_trade_record; CREATE TABLE yp_ods.t_trade_record ( id string COMMENT ‘交易单号’, external_trade_no string COMMENT ‘(支付,结算.退款)第三方交易单号’, relation_id string COMMENT ‘关联单号’, trade_type TINYINT COMMENT ‘1.支付订单; 2.结算订单; 3.退款订单；4.充值单；5.提现单；6.分销单；7缴纳保证金单8退还保证金单9,冻结通联订单，10通联通账户余额充值，11.扫码单’, status TINYINT COMMENT ‘1.成功;2.失败;3.进行中’, finnshed_time string COMMENT ‘订单完成时间,当配送员点击确认送达时,进行更新订单完成时间,后期需要根据订单完成时间,进行自动收货以及自动评价’, fail_reason string COMMENT ‘交易失败的原因’, payment_type string COMMENT ‘支付方式:小程序,app微信,支付宝,快捷支付,钱包，银行卡,消费券’, trade_before_balance DECIMAL(11,2) COMMENT ‘交易前余额’, trade_true_amount DECIMAL(11,2) COMMENT ‘交易实际支付金额,第三方平台扣除优惠以后实际支付金额’, trade_after_balance DECIMAL(11,2) COMMENT ‘交易后余额’, note string COMMENT ‘业务说明’, user_card string COMMENT ‘第三方平台账户标识/多钱包用户钱包id’, user_id string COMMENT ‘用户id’, aip_user_id string COMMENT ‘钱包id’, create_user string, create_time string, update_user string, update_time string, is_valid TINYINT COMMENT ‘是否有效 0: false; 1: true; 订单是否有效的标志’ ) comment ‘所有交易记录信息’ partitioned by (dt string) row format delimited fields terminated by ‘\t’ stored as orc tblproperties (‘orc.compress’ = ‘ZLIB’);

— 商圈表: t_trade_area DROP TABLE if exists yp_ods.t_trade_area; CREATE TABLE yp_ods.t_trade_area ( id string COMMENT ‘主键’, user_id string COMMENT ‘用户ID’, user_allinpay_id string COMMENT ‘通联用户表id’, trade_avatar string COMMENT ‘商圈logo’, name string COMMENT ‘商圈名称’, notice string COMMENT ‘商圈公告’, distric_province_id INT COMMENT ‘商圈所在省份ID’, distric_city_id INT COMMENT ‘商圈所在城市ID’, distric_area_id INT COMMENT ‘商圈所在县ID’, address string COMMENT ‘商圈地址’, radius double COMMENT ‘半径’, mb_title_img string COMMENT ‘手机商圈 页头背景图’, deposit_amount DECIMAL(11,2) COMMENT ‘商圈认购费用总额’, hava_deposit INT COMMENT ‘是否有交过保证金 1：是0：否’, state TINYINT COMMENT ‘申请商圈状态 -1 ：未认购 ；0 ：申请中；1 ：已认购 ；’, search_key string COMMENT ‘商圈搜索关键字’, create_user string, create_time string, update_user string, update_time string, is_valid TINYINT COMMENT ‘是否有效 0: false; 1: true’ ) comment ‘商圈表’ partitioned by (dt string) row format delimited fields terminated by ‘\t’ stored as orc tblproperties (‘orc.compress’=’ZLIB’);

— 地址信息 : t_location DROP TABLE if exists yp_ods.t_location; CREATE TABLE yp_ods.t_location ( id string COMMENT ‘主键’, type INT COMMENT ‘类型 1：商圈地址；2：店铺地址；3.用户地址管理;4.订单买家地址信息;5.订单卖家地址信息’, correlation_id string COMMENT ‘关联表id’, address string COMMENT ‘地图地址详情’, latitude double COMMENT ‘纬度’, longitude double COMMENT ‘经度’, street_number string COMMENT ‘门牌’, street string COMMENT ‘街道’, district string COMMENT ‘区县’, city string COMMENT ‘城市’, province string COMMENT ‘省份’, business string COMMENT ‘百度商圈字段，代表此点所属的商圈’, create_user string, create_time string, update_user string, update_time string, is_valid TINYINT COMMENT ‘是否有效 0: false; 1: true’, adcode string COMMENT ‘百度adcode,对应区县code’ ) comment ‘地址信息’ partitioned by (dt string) row format delimited fields terminated by ‘\t’ stored as orc tblproperties (‘orc.compress’=’ZLIB’);

— 商品表店铺(SKU) DROP TABLE if exists yp_ods.t_goods; CREATE TABLE yp_ods.t_goods ( id string, store_id string COMMENT ‘所属商店ID’, class_id string COMMENT ‘分类id:只保存最后一层分类id’, store_class_id string COMMENT ‘店铺分类id’, brand_id string COMMENT ‘品牌id’, goods_name string COMMENT ‘商品名称’, goods_specification string COMMENT ‘商品规格’, search_name string COMMENT ‘模糊搜索名称字段:名称+真实名称’, goodssort INT COMMENT ‘商品排序’, goods_market_price DECIMAL(11,2) COMMENT ‘商品市场价’, goods_price DECIMAL(11,2) COMMENT ‘商品销售价格(原价)’, goods_promotion_price DECIMAL(11,2) COMMENT ‘商品促销价格(售价)’, goods_storage INT COMMENT ‘商品库存’, goods_limit_num INT COMMENT ‘购买限制数量’, goods_unit string COMMENT ‘计量单位’, goods_state TINYINT COMMENT ‘商品状态 1正常，2下架,3违规（禁售）’, goods_verify TINYINT COMMENT ‘商品审核状态: 1通过，2未通过，3审核中’, activity_type TINYINT COMMENT ‘活动类型:0无活动1促销2秒杀3折扣’, discount INT COMMENT ‘商品折扣(%)’, seckill_begin_time string COMMENT ‘秒杀开始时间’, seckill_end_time string COMMENT ‘秒杀结束时间’, seckill_total_pay_num INT COMMENT ‘已秒杀数量’, seckill_total_num INT COMMENT ‘秒杀总数限制’, seckill_price DECIMAL(11,2) COMMENT ‘秒杀价格’, top_it TINYINT COMMENT ‘商品置顶：1-是，0-否’, create_user string, create_time string, update_user string, update_time string, is_valid TINYINT COMMENT ‘0 ：失效，1 ：开启’ ) comment ‘商品表店铺(SKU)’ partitioned by (dt string) row format delimited fields terminated by ‘\t’ stored as orc tblproperties (‘orc.compress’=’ZLIB’);

— 商品分类店铺 DROP TABLE if exists yp_ods.t_goods_class; CREATE TABLE yp_ods.t_goods_class ( id string, store_id string COMMENT ‘店铺id’, class_id string COMMENT ‘对应的平台分类表id’, name string COMMENT ‘店铺内分类名字’, parent_id string COMMENT ‘父id’, level TINYINT COMMENT ‘分类层级’, is_parent_node TINYINT COMMENT ‘是否为父节点:1是0否’, background_img string COMMENT ‘背景图片’, img string COMMENT ‘分类图片’, keywords string COMMENT ‘关键词’, title string COMMENT ‘搜索标题’, sort INT COMMENT ‘排序’, note string COMMENT ‘类型描述’, url string COMMENT ‘分类的链接’, is_use TINYINT COMMENT ‘是否使用:0否,1是’, create_user string, create_time string, update_user string, update_time string, is_valid TINYINT COMMENT ‘0 ：失效，1 ：开启’ ) comment ‘商品分类店铺’ partitioned by (dt string) row format delimited fields terminated by ‘\t’ stored as orc tblproperties (‘orc.compress’=’ZLIB’);

— 品牌（店铺） DROP TABLE if exists yp_ods.t_brand; CREATE TABLE yp_ods.t_brand ( id string, store_id string COMMENT ‘店铺id’, brand_pt_id string COMMENT ‘平台品牌库品牌Id’, brand_name string COMMENT ‘品牌名称’, brand_image string COMMENT ‘品牌图片’, initial string COMMENT ‘品牌首字母’, sort INT COMMENT ‘排序’, is_use TINYINT COMMENT ‘0禁用1启用’, goods_state TINYINT COMMENT ‘商品品牌审核状态 1 审核中,2 通过,3 拒绝’, create_user string, create_time string, update_user string, update_time string, is_valid TINYINT COMMENT ‘0 ：失效，1 ：开启’ ) comment ‘品牌（店铺）’ partitioned by (dt string) row format delimited fields terminated by ‘\t’ stored as orc tblproperties (‘orc.compress’=’ZLIB’);

— 订单表 DROP TABLE if exists yp_ods.t_shop_order; CREATE TABLE yp_ods.t_shop_order ( id string COMMENT ‘根据一定规则生成的订单编号’, order_num string COMMENT ‘订单序号’, buyer_id string COMMENT ‘买家的userId’, store_id string COMMENT ‘店铺的id’, order_from TINYINT COMMENT ‘是来自于app还是小程序,或者pc 1.安卓; 2.ios; 3.小程序H5 ; 4.PC’, order_state INT COMMENT ‘订单状态:1.已下单; 2.已付款, 3. 已确认 ;4.配送; 5.已完成; 6.退款;7.已取消’, create_date string COMMENT ‘下单时间’, finnshed_time timestamp COMMENT ‘订单完成时间,当配送员点击确认送达时,进行更新订单完成时间,后期需要根据订单完成时间,进行自动收货以及自动评价’, is_settlement TINYINT COMMENT ‘是否结算;0.待结算订单; 1.已结算订单;’, is_delete TINYINT COMMENT ‘订单评价的状态:0.未删除; 1.已删除;(默认0)’, evaluation_state TINYINT COMMENT ‘订单评价的状态:0.未评价; 1.已评价;(默认0)’, way string COMMENT ‘取货方式:SELF自提;SHOP店铺负责配送’, is_stock_up INT COMMENT ‘是否需要备货 0：不需要 1：需要 2:平台确认备货 3:已完成备货 4平台已经将货物送至店铺 ‘, create_user string, create_time string, update_user string, update_time string, is_valid TINYINT COMMENT ‘是否有效 0: false; 1: true; 订单是否有效的标志’ ) comment ‘订单表’ partitioned by (dt string) row format delimited fields terminated by ‘\t’ stored as orc tblproperties (‘orc.compress’ = ‘ZLIB’);

— 订单详情表 DROP TABLE if exists yp_ods.t_shop_order_address_detail; CREATE TABLE yp_ods.t_shop_order_address_detail ( id string COMMENT ‘关联订单的id’, order_amount DECIMAL(11,2) COMMENT ‘订单总金额:购买总金额-优惠金额’, discount_amount DECIMAL(11,2) COMMENT ‘优惠金额’, goods_amount DECIMAL(11,2) COMMENT ‘用户购买的商品的总金额+运费’, is_delivery string COMMENT ‘0.自提；1.配送’, buyer_notes string COMMENT ‘买家备注留言’, pay_time string, receive_time string, delivery_begin_time string, arrive_store_time string, arrive_time string COMMENT ‘订单完成时间,当配送员点击确认送达时,进行更新订单完成时间,后期需要根据订单完成时间,进行自动收货以及自动评价’, create_user string, create_time string, update_user string, update_time string, is_valid TINYINT COMMENT ‘是否有效 0: false; 1: true; 订单是否有效的标志’ ) comment ‘订单详情表’ partitioned by (dt string) row format delimited fields terminated by ‘\t’ stored as orc tblproperties (‘orc.compress’ = ‘ZLIB’);

— 订单结算表 DROP TABLE if exists yp_ods.t_order_settle; CREATE TABLE yp_ods.t_order_settle ( id string COMMENT ‘结算单号’, order_id string, settlement_create_date string COMMENT ‘用户申请结算的时间’, settlement_amount DECIMAL(11,2) COMMENT ‘如果发生退款,则结算的金额 = 订单的总金额 - 退款的金额’, dispatcher_user_id string COMMENT ‘配送员id’, dispatcher_money DECIMAL(11,2) COMMENT ‘配送员的配送费(配送员的运费(如果退货方式为1:则买家支付配送费))’, circle_master_user_id string COMMENT ‘圈主id’, circle_master_money DECIMAL(11,2) COMMENT ‘圈主分润的金额’, plat_fee DECIMAL(11,2) COMMENT ‘平台应得的分润’, store_money DECIMAL(11,2) COMMENT ‘商家应得的订单金额’, status TINYINT COMMENT ‘0.待结算；1.待审核 ; 2.完成结算；3.拒绝结算’, note string COMMENT ‘原因’, settle_time string COMMENT ‘ 结算时间’, create_user string, create_time string, update_user string, update_time string, is_valid TINYINT COMMENT ‘是否有效 0: false; 1: true; 订单是否有效的标志’, first_commission_user_id string COMMENT ‘一级分佣用户’, first_commission_money DECIMAL(11,2) COMMENT ‘一级分佣金额’, second_commission_user_id string COMMENT ‘二级分佣用户’, second_commission_money DECIMAL(11,2) COMMENT ‘二级分佣金额’ ) comment ‘订单结算表’ partitioned by (dt string) row format delimited fields terminated by ‘\t’ stored as orc tblproperties (‘orc.compress’ = ‘ZLIB’);

— 退款订单表 DROP TABLE if exists yp_ods.t_refund_order; CREATE TABLE yp_ods.t_refund_order ( id string COMMENT ‘退款单号’, order_id string COMMENT ‘订单的id’, apply_date string COMMENT ‘用户申请退款的时间’, modify_date string COMMENT ‘退款订单更新时间’, refund_reason string COMMENT ‘买家退款原因’, refund_amount DECIMAL(11,2) COMMENT ‘订单退款的金额’, refund_state TINYINT COMMENT ‘1.申请退款;2.拒绝退款; 3.同意退款,配送员配送; 4:商家同意退款,用户亲自送货 ;5.退款完成’, refuse_refund_reason string COMMENT ‘商家拒绝退款原因’, refund_goods_type string COMMENT ‘1.上门取货(买家承担运费); 2.买家送达;’, refund_shipping_fee DECIMAL(11,2) COMMENT ‘配送员的运费(如果退货方式为1:则买家支付配送费)’, create_user string, create_time string, update_user string, update_time string, is_valid TINYINT COMMENT ‘是否有效 0: false; 1: true; 订单是否有效的标志’ ) comment ‘退款订单表’ partitioned by (dt string) row format delimited fields terminated by ‘\t’ stored as orc tblproperties (‘orc.compress’ = ‘ZLIB’);

— 订单分组表 DROP TABLE if exists yp_ods.t_shop_order_group; CREATE TABLE yp_ods.t_shop_order_group ( id string, order_id string COMMENT ‘订单id’, group_id string COMMENT ‘订单分组id’, is_pay TINYINT COMMENT ‘是否已支付,0未支付,1已支付’, create_user string, create_time string, update_user string, update_time string, is_valid TINYINT ) comment ‘订单分组表’ partitioned by (dt string) row format delimited fields terminated by ‘\t’ stored as orc tblproperties (‘orc.compress’ = ‘ZLIB’);

— 店铺表 DROP TABLE if exists ypods.t_store; CREATE TABLE yp_ods.t_store ( id string COMMENT ‘主键’, user_id string, store_avatar string COMMENT ‘店铺头像’, address_info string COMMENT ‘店铺详细地址’, name string COMMENT ‘店铺名称’, store_phone string COMMENT ‘联系电话’, province_id INT COMMENT ‘店铺所在省份ID’, city_id INT COMMENT ‘店铺所在城市ID’, area_id INT COMMENT ‘店铺所在县ID’, mb_title_img string COMMENT ‘手机店铺 页头背景图’, store_description string COMMENT ‘店铺描述’, notice string COMMENT ‘店铺公告’, is_pay_bond TINYINT COMMENT ‘是否有交过保证金 1：是0：否’, trade_area_id string COMMENT ‘归属商圈ID’, delivery_method TINYINT COMMENT ‘配送方式 1 ：自提 ；3 ：自提加配送均可; 2 : 商家配送’, origin_price DECIMAL, free_price DECIMAL, store_type INT COMMENT ‘店铺类型 22天街网店 23实体店 24直营店铺 33会员专区店’, store_label string COMMENT ‘店铺logo’, search_key string COMMENT ‘店铺搜索关键字’, end_time string COMMENT ‘营业结束时间’, start_time string COMMENT ‘营业开始时间’, operating_status TINYINT COMMENT ‘营业状态 0 ：未营业 ；1 ：正在营业’, create_user string, create_time string, update_user string, update_time string, is_valid TINYINT COMMENT ‘0关闭，1开启，3店铺申请中’, state string COMMENT ‘可使用的支付类型:MONEY金钱支付;CASHCOUPON现金券支付’, idCard string COMMENT ‘身份证’, deposit_amount DECIMAL(11,2) COMMENT ‘商圈认购费用总额’, delivery_config_id string COMMENT ‘配送配置表关联ID’, aip_user_id string COMMENT ‘通联支付标识ID’, search_name string COMMENT ‘模糊搜索名称字段:名称+真实名称’, automatic_order TINYINT COMMENT ‘是否开启自动接单功能 1：是 0 ：否’, is_primary TINYINT COMMENT ‘是否是总店 1: 是 2: 不是’, parent_store_id string COMMENT ‘父级店铺的id，只有当is_primary类型为2时有效’ ) comment ‘店铺表’ partitioned by (dt string) row format delimited fields terminated by ‘\t’ stored as orc tblproperties (‘orc.compress’=’ZLIB’);

— 订单和商品的中间表 DROP TABLE if exists yp_ods.t_shop_order_goods_details; CREATE TABLE yp_ods.t_shop_order_goods_details ( id string COMMENT ‘id主键’, order_id string COMMENT ‘对应订单表的id’, shop_store_id string COMMENT ‘卖家店铺ID’, buyer_id string COMMENT ‘购买用户ID’, goods_id string COMMENT ‘购买商品的id’, buy_num INT COMMENT ‘购买商品的数量’, goods_price DECIMAL(11,2) COMMENT ‘购买商品的价格’, total_price DECIMAL(11,2) COMMENT ‘购买商品的价格 = 商品的数量 * 商品的单价 ‘, goods_name string COMMENT ‘商品的名称’, goods_image string COMMENT ‘商品的图片’, goods_specification string COMMENT ‘商品规格’, goods_weight INT, goods_unit string COMMENT ‘商品计量单位’, goods_type string COMMENT ‘商品分类 ytgj:进口商品 ytsc:普通商品 hots爆品’, refund_order_id string COMMENT ‘退款订单的id’, goods_brokerage DECIMAL(11,2) COMMENT ‘商家设置的商品分润的金额’, is_refund TINYINT COMMENT ‘0.不退款; 1.退款’, create_user string, create_time string, update_user string, update_time string, is_valid TINYINT COMMENT ‘是否有效 0: false; 1: true’ ) comment ‘订单和商品的中间表’ partitioned by (dt string) row format delimited fields terminated by ‘\t’ stored as orc tblproperties (‘orc.compress’ = ‘ZLIB’);

— 购物车 DROP TABLE if exists yp_ods.t_shop_cart; CREATE TABLE yp_ods.t_shop_cart ( id string COMMENT ‘主键id’, shop_store_id string COMMENT ‘卖家店铺ID’, buyer_id string COMMENT ‘购买用户ID’, goods_id string COMMENT ‘购买商品的id’, buy_num INT COMMENT ‘购买商品的数量’, create_user string, create_time string, update_user string, update_time string, is_valid TINYINT COMMENT ‘是否有效 0: false; 1: true’ ) comment ‘购物车’ partitioned by (dt string) row format delimited fields terminated by ‘\t’ stored as orc tblproperties (‘orc.compress’ = ‘ZLIB’);

— 店铺收藏 DROP TABLE if exists yp_ods.t_store_collect; CREATE TABLE yp_ods.t_store_collect ( id string, user_id string COMMENT ‘收藏人id’, store_id string COMMENT ‘店铺id’, create_user string, create_time string, update_user string, update_time string, is_valid TINYINT COMMENT ‘0 ：失效，1 ：开启’ ) comment ‘店铺收藏’ partitioned by (dt string) row format delimited fields terminated by ‘\t’ stored as orc tblproperties (‘orc.compress’=’ZLIB’);

— 商品收藏 DROP TABLE if exists yp_ods.t_goods_collect; CREATE TABLE yp_ods.t_goods_collect ( id string, user_id string COMMENT ‘收藏人id’, goods_id string COMMENT ‘商品id’, store_id string COMMENT ‘通过哪个店铺收藏的（因主店分店概念存在需要）’, create_user string, create_time string, update_user string, update_time string, is_valid TINYINT COMMENT ‘0 ：失效，1 ：开启’ ) comment ‘商品收藏’ partitioned by (dt string) row format delimited fields terminated by ‘\t’ stored as orc tblproperties (‘orc.compress’=’ZLIB’);

<a name="c15e6fc6"></a>
### 4.5 通过sqoop将数据导入到ODS层

- 1- 采用全量覆盖导入操作(第一次):

```properties
# 日期表:   t_date
sqoop import \
--connect jdbc:mysql://hadoop01:3306/yipin \
--username root \
--password 123456 \
--query 'select * from t_date where 1 = 1 and $CONDITIONS' \
--hcatalog-database 'yp_ods' \
--hcatalog-table 't_date' \
--fields-terminated-by  '\t' \
-m 1

• 2- 仅新增同步方式的表 导入操作: 第一次导入(范围是什么? 截止到上一天的所有的数据)

# 订单评价表 : t_goods_evaluation
sqoop import \
--connect jdbc:mysql://hadoop01:3306/yipin \
--username root \
--password 123456 \
--query 'select *, "2022-04-26" as dt from t_goods_evaluation where create_time <= "2022-04-26 23:59:59" and $CONDITIONS' \
--hcatalog-database 'yp_ods' \
--hcatalog-table 't_goods_evaluation' \
--fields-terminated-by  '\t' \
-m 1

• 3- 新增及更新同步方式的表导入操作: 第一次导入

# 订单表
sqoop import \
--connect jdbc:mysql://hadoop01:3306/yipin \
--username root \
--password 123456 \
--query 'select *, "2022-04-26" as dt from t_shop_order where create_time <= "2022-04-26 23:59:59" OR update_time <= "2022-04-26 23:59:59" and $CONDITIONS' \
--hcatalog-database 'yp_ods' \
--hcatalog-table 't_shop_order' \
--fields-terminated-by  '\t' \
-m 1


如果要获取上一天的新增和更新的数据:
select *, "2022-04-26" as dt from t_shop_order where create_time between "2022-04-26 00:00:00" and "2022-04-26 23:59:59" OR update_time between  "2022-04-26 00:00:00" and "2022-04-26 23:59:59"

5. ODS层增量数据采集操作

5.1 模拟一份增量数据操作

此操作在实际生产环境中是不存在的, 因为 实际生产环境中, 本身每天就是有增量数据

-- 日期表(全量覆盖的方式):
--    新增一条数据
INSERT INTO `t_date` VALUES ('20310101','2031-01-01','20301201','2031','2031年','01','01月','1','Q1','203101','52','203052','01','203101','3','星期三','01','01','4','3','01','001','20301225','20301201','10000000','20300101','20310101','20310131','1','上半年','S04','冬季','否','H01','元旦','F01','元旦','','','2021-12-20 14:20:57.401');

-- 订单评价表(仅新增同步):
INSERT INTO `t_goods_evaluation` VALUES ('10001','430eff5a55d911e998ec7cd30ad32e2e','7b09b44e5b6d11e998ec7cd30ad32e2e','dd190411306814f41f',10,10,10,1,'430eff5a55d911e998ec7cd30ad32e2e','2021-12-20 09:42:02',NULL,NULL,1);

-- 店铺表(新增及更新同步):
update  `t_store` set name='北京传智教育科技有限公司' , update_time = '2021-12-20 05:50:55' where id = '0afb5daf777d11e998ec7cd30ad32e2e';

INSERT INTO `t_store` VALUES ('10010','02554155777c11e998ec7cd30ad32e2e',NULL,NULL,'黑马程序员',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'0af148be777d11e998ec7cd30ad32e2e',NULL,NULL,NULL,24,NULL,NULL,'22:00','08:00',0,'02554155777c11e998ec7cd30ad32e2e','2021-12-20 05:50:55',NULL,NULL,3,'MONEY',NULL,NULL,'0afaf8ad777d11e998ec7cd30ad32e2e',NULL,'名称_**亿隆电子科技开发有限公司',0,1,NULL);

5.2 完成增量数据采集操作