数仓中应该建多少个schema 数仓怎么搭建

1.安装Docker

1.查看版本cat /etc/redhat-release #查看centos版本2.添加yum源yum-config-manager --add-repo https://mirrors.aliyun.com/docker-ce/linux/centos/docker-ce.repo3.设置aliyun源dnf install https://mirrors.aliyun.com/docker-ce/linux/centos/8.3/x86_64/stable/Packages/containerd.io-1.4.4-3.1.el8.x86_64.rpm --allowerasing4.安装dockerdnf install docker-ce5.设置开机启动dockersystemctl enable docker.service6.启动dockesystemctl start docker.service7.查看正在运行的容器信息 -a也列出未运行的容器信息docker ps8.查看docker信息docker info9 .从线上仓库下载hadoop指定版本的容器 版本根据要求下载,我使用centos:7版本docker pull centos:710.查看本地仓库的容器文件docker images

1.从仓库中拉取镜像

docker pull centos:7

2.查看镜像

docker images

2.删除镜像

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SIQUD6mZ-1621866567324)(…/TypoarWrokPath/images/1618900368598.png)]

docker rm centos:7 或者 docker rm [8652b9f0cb4c]

3.启动并创建容器

1.bigdata 名字可以自己命名,需要关闭mysql服务,关闭其他的所有进程
2.mysql
systemctl disable mysqld.service 关闭mysql 开机自起
systemctl stop mysqld.service 关闭mysql 服务
docker run -itd --privileged --name bigdata -h bigdata 
 -p 2222:22 
 -p 3306:3306 
 -p 50070:50070 
 -p 8088:8088 
 -p 8080:8080 
 -p 10000:10000 
 -p 60010:60010 
 -p 9092:9092 
 centos:7 /usr/sbin/init

参数解析:

run :通过一个镜像运行一个容器

-i :提供一个终端

–privileged : 设置权限,如果不设置,在容器当中启动服务是会报错

–name :给容器起名

-h:容器主机名

-p :端口映射 宿主机端口:容器端口

centos:7: 镜像名称

/usr/sbin/init 和–privileged 连用的 相当于做一个初始化

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gWkjRRJZ-1621866567330)(…/TypoarWrokPath/images/1618901243246.png)]

4.通过名字关闭容器

docker stop bigdata

5.通过名字启动容器

docker start bigdata

6.通过名字重启容器

docker restart bigdata

7.查看容器

docker ps 参数 -a 查看所有的容器,包括没有运行的容器

8.删除容器

docker rm bigdata

9.进入容器

docker exec -it bigdata /bin/bash

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-u14lcVp3-1621866567335)(…/TypoarWrokPath/images/1618901370815.png)]

10.环境的准备

yum clean all

yum -y install unzip bzip2-devel vim bashname

3.2配置SSH免密登录

1.设置密码

passwd root 密码 :root

1.安装必要ssh服务

yum install -y openssh openssh-server openssh-clients openssl openssl-devel

2.生成密钥

ssh-keygen -t rsa -f ~/.ssh/id_rsa -P ‘’

#启动SSH服务
systemctl start sshd

3.配置免密登录

方式一:

cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys

2.配置免密

ssh-copy-id root@bigdata 用户加主机名

11.使用软件连接bigdata

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-X0YI03fm-1621866567339)(…/TypoarWrokPath/images/1618902910013.png)]

3.3设置时区

ln -sf /usr/share/zoneinfo/Asia/Shanghai /etc/localtime

2.查看时间

date

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-EccAeSmp-1621866567342)(…/TypoarWrokPath/images/1618903400971.png)]

3.4关闭防火墙(无效,可以不管)

systemctl stop firewa7lds ystemctl disable firewwalld

12.安装msyql

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rfchC1A5-1621866567344)(…/TypoarWrokPath/images/1618904165881.png)]

1.解压

tar xvf MySQL-5.5.40-1.el7.x86_64.rpm-bundle.tar

2.安装必要依赖

yum -y install libaio perl

3.安装服务

rpm -ivh MySQL-server-5.5.40-1.el7.x86_64.rpm

1.报错

解决: yum install net-tools 在重新执行安装服务命令

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8OMtfqDn-1621866567347)(…/TypoarWrokPath/images/1618904847742.png)]

4.安装客户端

rpm -ivh MySQL-client-5.5.40-1.el7.x86_64.rpm

5.启动mysql服务

systemctl start mysql

6.修改mysql 密码

方式一:/urs/bin/mysqladmin -u root passwd ‘root’

方式二: /usr/bin/mysql_secure_installation (本人使用第二种)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DM42EfNh-1621866567350)(…/TypoarWrokPath/images/1618906089891.png)]

7.进入客户端

mysql -uroot -proot

8.修改之后允许远程登录

update mysql.user set host=’%’ where host=‘localhost’;

9.删除

delete from mysql.user where host <> ‘%’ or user =’ ';

10.刷新权限

flush privileges;

13.安装jdk

1.解压

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mDuxwPKR-1621866567353)(…/TypoarWrokPath/images/1618906344585.png)]

2.创建软连接 in -s

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-D5buZA35-1621866567354)(…/TypoarWrokPath/images/1618906385090.png)]

3.配置环境变量

vi ~/.bashrc

1.添加如下

#javahome
export JAVA_HOME=/opt/install/java
export PATH= $ JAVA_HOME/bin:$PATH

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WBPpoOJC-1621866567355)(…/TypoarWrokPath/images/1618906904008.png)]

14.安装hadoop

tar zxvf hadoop-2.6.0.tar.gz -C /opt/install/

2.创建软件连接

ln -s hadoop-2.6.0 /hadoop

3.配置环境变量

#hadoophome
export HADOOP_HOME=/opt/install/hadoop
export PATH=$HADOOP_HOME/bin:$PATH
//刷新配置文件
 sourec ~/.bashrc

4.修改配置文件

1.core-site.xml

<configuration>
<property>
	<name>fs.defaultFS</name>
    <value>hdfs://bigdata:9000</value>
  </property>
  <property>
	<name>hadoop.tmp.dir</name>
    <value>/opt/install/hadoop/data/tmp</value>
  </property>
</configuration>

2.hdfs-site.xml

<configuration>
<property>
	<name>dfs.replication</name>
    <value>1</value>
  </property>
</configuration>

3.mapreduce-site.xml

<configuration>
<property>
	<name>dfs.replication</name>
    <value>1</value>
  </property>
</configuration>

4.yarn-site.xml

<configuration>

   <property>
	<name>yarn.nodemanager.aux-services</name>
    <value>mapreduce_shuffle</value>
  </property>
  
  <property>
	<name>yarn.resourcemanager.hostname</name>
    <value>bigdata</value>
  </property>
  
  <property>
	<name>yarn.log-aggregation-enable</name>
    <value>true</value>
  </property>
  
  <property>
	<name>yarn.log.aggregation.retain-seconds</name>
    <value>604800</value>
  </property>
</configuration>

5.hadoop-env.sh

export JAVA_HOME=/opt/install/java

6.mapreduce-env.sh

export JAVA_HOME=/opt/install/java

7.yarn-env.sh

export JAVA_HOME=/opt/install/java

8.配置slaves

vim slaves 
删除localhost 改成 bigdata

15.安装hive

1.解压hive

tar zxvf apache-hive-1.2.2-bin.tar.gz -C /opt/install/

2.修改配置文件

1.没有hive-site.xml 直接创建 vim hive-site.xml

<?xml version="1.0" encoding="UTF-8"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
	<property>
	    <name>hive.metastore.warehouse.dir</name>
	    <value>/home/hadoop/hive/warehouse</value>
	    <description>数据库及内部表的数据存放位置</description>
	</property>
<!-- mysql 数据配置 -->
	<property>
	    <name>javax.jdo.option.ConnectionURL</name>
	    <value>jdbc:mysql://bigdata:3306/hive?createDatabaseIfNotExist=true</value>
	    <description>管理元数据的数据库</description>
	</property>
	<property>
	    <name>javax.jdo.option.ConnectionDriverName</name>
	    <value>com.mysql.jdbc.Driver</value>
	    <description>JDBC连接驱动器</description>
	</property>
	<property>
	    <name>javax.jdo.option.ConnectionUserName</name>
	    <value>root</value>
	    <description>JDBC连接数据库登录的用户名</description>
	</property>
	<property>
	    <name>javax.jdo.option.ConnectionPassword</name>
	    <value>root</value>
	    <description>JDBC连接数据库登录的密码</description>
	</property>
	<!-- 配置 Hive 临时HDFS文件存储地址 -->
	<property>
	    <name>hive.exec.scratchdir</name>
	    <value>/tmp/hive/data/hive-${user.name}</value>
	    <description>Scratch space for Hive jobs</description>
	</property>
	
	<property>
	    <name>hive.exec.local.scratchdir</name>
	    <value>/tmp/hive/data/${user.name}</value>
	    <description>Local scratch space for Hive jobs</description>
	</property>
</configuration>

2.配置 mv hive-env.sh.t开头的 hive-env.sh 配置下面的文件

HADOOP_HOME=/opt/install/hadoop
 export HIVE_CONF_DIR=/opt/install/hive/conf

3.配置hive的环境变量

#hive home
export HIVE_HOME=/opt/install/hive
export PATH=$HIVE_HOME/bin:$PATH
//刷新配置文件
 sourec ~/.bashrc

16.启动元数据服务

nohup hive --service metastore &

17.启动hiveserver2服务

nohup hive --service hiveserver2 &

18.进入hive

bin/hive

19.报错信息

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6YB0C3kr-1621866567363)(…/TypoarWrokPath/images/1618916121759.png)]

1.解决

在hive目录下执行

//拷贝

cp jline-2.12.jar /opt/install/hadoop/share/hadoop/yarn/lib/

//修改弃用

2. mv jline-0.9.94.jar jline-0.9.94.jar.bak

20.安装sqoop

1.解压

tar zxvf sqoop-1.4.7.bin__hadoop-2.6.0.tar.gz -C /opt/install/

2.拷贝

2.cp sqoop-env-template.sh sqoop-env.sh

3.修改配置文件 sqoop-env.sh

export HADOOP_COMMON_HOME=/opt/install/hadoop

export HIVE_HOME=/opt/install/hive

4.将mysql驱动和java-json.jar拷贝到sqoop的lib下

cp mysql-connector-java-5.1.13-bin.jar /opt/install/sqoop/lib/

cp java-json.jar /opt/install/sqoop/lib/

5.修改配置文件

#sqoophome
export SQOOP_HOME=/opt/install/sqoop
export PATH=$SQOOP_HOME/bin:$PATH
//刷新配置文件
 sourec ~/.bashrc

6.查看版本

sqoop version

21.Hive配置tez引擎

9.0 关闭服务

关闭hive的metastore和hiveserver2服务

关闭hadoop服务

9.1 版本选择

hadoop-2.6.0、hive-1.2.2

tez-0.7.1

9.2 上传并解压

tar -zxvf /opt/software/apache-tez-0.7.1-bin.tar.gz -C /opt/install

ln -s /opt/install/apache-tez-0.7.1-bin /opt/install/

9.21创建软连接

ln -s apache-tez-0.7.1-bin / tez

9.3 上传 tez-0.7.1.tar.gz 依赖到HDFS

cd /opt/install/tez/share/

hadoop fs -mkdir /tez

hadoop fs -put /opt/install/tez/share/tez-0.7.1.tar.gz /tez

9.4 修改配置

9.4.1 修改tez-site.xml

touch $HADOOP_HOME/etc/hadoop/tez-site.xml

vim $HADOOP_HOME/etc/hadoop/tez-site.xml

<?xml version="1.0" encoding="UTF-8"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
	<property>
		<name>tez.lib.uris</name>
 		<value>${fs.defaultFS}/tez/tez-0.7.1.tar.gz</value>
	</property>

	<property>
 		<name>tez.use.cluster.hadoop-libs</name>
 		<value>true</value>
	</property>

	<property>
 		<name>tez.am.resource.memory.mb</name>
 		<value>1024</value>
	</property>

	<property>
 		<name>tez.am.resource.cpu.vcores</name>
 		<value>1</value>
	</property>

	<property>
 		<name>tez.container.max.java.heap.fraction</name>
 		<value>0.4</value>
	</property>

	<property>
 		<name>tez.task.resource.memory.mb</name>
 		<value>1024</value>
	</property>

	<property>
 		<name>tez.task.resource.cpu.vcores</name>
 		<value>1</value>
	</property>
</configuration>

9.4.2 修改yarn-site.xml

vim $HADOOP_HOME/etc/hadoop/yarn-site.xml

<property>
		<name>yarn.nodemanager.vmem-check-enabled</name>
		<value>false</value>
</property>

9.4.3 修改hadoop-env.sh

vim $HADOOP_HOME/etc/hadoop/hadoop-env.sh

TEZ_CONF_DIR=/opt/install/hadoop/etc/hadoop/tez-site.xml
TEZ_JARS=/opt/install/tez
export HADOOP_CLASSPATH=${HADOOP_CLASSPATH}:${TEZ_CONF_DIR}:${TEZ_JARS}/*:${TEZ_JARS}/lib/*

9.4.4 修改hive-site.xml

vim $HIVE_HOME/conf/hive-site.xml

<property>
 		<name>hive.cli.print.header</name>
 		<value>true</value>
 	</property>

 	<property>
 		<name>hive.cli.print.current.db</name>
 		<value>true</value>
 	</property>

	<property>
 		<name>hive.execution.engine</name>
 		<value>tez</value>
	</property>

	<property>
 		<name>hive.tez.container.size</name>
 		<value>1024</value>
	</property>

9.5 解决日志jar包冲突

mv /opt/install/tez/lib/slf4j-log4j12-1.7.5.jar /opt/install/tez/lib/slf4j-log4j12-1.7.5.jar.bak

9.6 启动服务测试

启动hadoop服务

启动hive服务

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-F5MXm3Gq-1621866567364)(…/TypoarWrokPath/images/1618978222342.png)]

2.创建集群

方式一:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4I9jXwYw-1621866567365)(…/TypoarWrokPath/images/1618921425126.png)]

3.理论建模

数仓分层

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tDoB19wI-1621866567366)(…/TypoarWrokPath/images/1619513897338.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1mZxBDtF-1621866567367)(…/TypoarWrokPath/images/1619513914481.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yaOU84tp-1621866567368)(…/TypoarWrokPath/images/1619056092040.png)]

1.什么是数据仓库

1.数据仓库是一个面向主题的,集成的,非易失的,且随时间变的数据集合

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-MelC3pzV-1621866567370)(…/TypoarWrokPath/images/1618973858151.png)]

2.面向主题

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qYSHUKQ3-1621866567372)(…/TypoarWrokPath/images/1618973943769.png)]

1.什么是主题
主题是在较高层次上将企业信息系统中的数据进行综合、归类和分析利用的一个抽象概念，每一个主题基本对应一个宏观的分析领域。在逻辑意义上，它是对应企业中某一宏观分析领域所涉及的分析对象。

简单说，一个主题对应一个分析对象。分析对象就是在决策、分析时重点关注的东西，这个东西其实是非常主观的，在不同的企业，或者企业的不同发展时期，所关注的点会不一样，从而影响有些主题可能存在或者不存在。

数据仓库是面向主题的应用，主要功能是将数据综合、归类并进行分析利用。数据仓库模型设计除横向的分层外，通常还需要根据业务情况纵向划分主题域。主题域是业务对象高度概括的概念层次归类，目的是便于数据的管理和应用。

2.如何划分主题

在业务调研之后，可以进行主题域的划分。划分主题域，需要分析各个业务模块中有哪些业务活动。通常我们按照以下方法划分主题域，可以按照用户企业的部门划分，也可以按照业务过程或者业务板块中的功能模块划分。

按照系统划分：业务系统有几种，就划分为几类

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tA5Prf9e-1621866567373)(https://i.loli.net/2021/05/14/kh69jGJTSlDmCYr.png)]

按业务过程划分：比如业务系统中有商品、交易、物流等。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-iSsGuNG9-1621866567374)(https://i.loli.net/2021/05/14/VhgfxWqu2K6Zl5F.png)]

按部门规划：比如公司内的生产、供应链、研发、销售等。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-92SEW1Vx-1621866567375)(https://i.loli.net/2021/05/14/oHiIK8JPd5xa2vt.png)]

3.提取主题1[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0sAXOM7v-1621866567378)(…/TypoarWrokPath/images/1618973985006.png)]

4.提取主题2

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-apOGlvx3-1621866567379)(…/TypoarWrokPath/images/1618974043627.png)]

5.主题类别[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-A617YPXG-1621866567381)(…/TypoarWrokPath/images/1618974114353.png)]

6.主题组合

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wq7Cx2Hq-1621866567382)(…/TypoarWrokPath/images/1618974202263.png)]

2.集成

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CKOmzVqi-1621866567383)(…/TypoarWrokPath/images/1618974291158.png)]

3.非易失

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-grbZz8yo-1621866567384)(…/TypoarWrokPath/images/1618974338829.png)]

4.随时间变化

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BsoRh4ZD-1621866567385)(…/TypoarWrokPath/images/1618974386721.png)]

2.数据仓库和数据库的区别

OLAP（On-Line Analytical Processing）联机分析处理，也称为面向交易的处理过程，其基本特征是前台接收的用户数据可以立即传送到计算中心进行处理，并在很短的时间内给出处理结果，是对用户操作快速响应的方式之一。应用在数据仓库，使用对象是决策者。OLAP系统强调的是数据分析，响应速度要求没那么高。

OLTP（On-Line Transaction Processing）联机事务处理，它使分析人员能够迅速、一致、交互地从各个方面观察信息，以达到深入理解数据的目的。它具有FASMI(Fast Analysis of Shared Multidimensional Information)，即共享多维信息的快速分析的特征。主要应用是传统关系型数据库。OLTP系统强调的是内存效率，实时性比较高。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8WyetSrb-1621866567386)(…/TypoarWrokPath/images/1618986497321.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nW1a0t4f-1621866567387)(…/TypoarWrokPath/images/1618974913150.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nIdKIJwE-1621866567389)(…/TypoarWrokPath/images/1618974740411.png)]

3.数据仓库的架构

1.Inmon架构

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KcKoKEwp-1621866567391)(…/TypoarWrokPath/images/1618986926741.png)]

2.Kimball架构

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KJoDT3SP-1621866567392)(…/TypoarWrokPath/images/1618987198120.png)]

3.混合型架构

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tRYY6oEy-1621866567394)(…/TypoarWrokPath/images/1618987481590.png)]

4.数据仓库的解决方案

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dGGMkoeT-1621866567396)(…/TypoarWrokPath/images/1618987616379.png)]

4.数据ETL

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ReYaZqS8-1621866567397)(…/TypoarWrokPath/images/1618988363445.png)]

1.ETL工具

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yttUw8jK-1621866567398)(…/TypoarWrokPath/images/1618988334007.png)]

2.数据转换

1.统一数据编码:

比如性别有些是0 和1 有些是’F’ 和’M’ ,我们需要给他统一一下

2.预计算:

比如订单表里面有订单id,有商家id,数量,价格,日期,这里我们就可以将根据数量和价格提前做好计算

3.重新排序:

提高查询性能

4.行列转换:lateral view explode

5.去重:数据可能有些重复的数据,会影响到后续的分析,所以我们需要根据实际情况进行去重

5.数据创库的建模

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-T3cCtCuP-1621866567399)(…/TypoarWrokPath/images/1618991638274.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ansiJome-1621866567400)(…/TypoarWrokPath/images/1618991648518.png)]

1.事实表:

一堆的维度id + 度量

2.维度表:

将描述信息抽取出来的表,通过用来表示 ‘谁?’ ‘在哪’ ’ 什么时间’

3.声明粒度

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-IQbVmdTk-1621866567401)(…/TypoarWrokPath/images/1618994617892.png)]

1.声明粒度

订单中，每个商品项作为下单事实表中的一行，粒度为每次下单

每周的订单次数作为一行，粒度就是每周下单。

每月的订单次数作为一行，粒度就是每月下单

数据粒度指数据仓库的数据中保存数据的细化程度或综合程度的级别。

声明粒度意味着精确定义事实表中的一行数据表示什么，应该尽可能选择最小粒度，以 此来应各种各样的需求。

4.确认维度

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8IeTyrpl-1621866567401)(…/TypoarWrokPath/images/1618994741035.png)]

说明了事实表的数据是从哪里来的.

典型的就是维度名词:日期,商店,库存.

维度表存储了某一维度的所有相关数据:

例如:日期维度应该包括年,季度,月,周,日,等数据

5.确认事实

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WbUPbOg3-1621866567402)(…/TypoarWrokPath/images/1618994801067.png)]

6.星型模型

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WeblcD6w-1621866567403)(…/TypoarWrokPath/images/1618994932307.png)]

星型模型:由中间的一张表事实表和周围的一圈维度表所构成

雪花模型:由中间的一张事实和周围的多层维度表所构成

星座模型:两张事实表通过一张维度表间接的关联在数仓的发张后期,随着事实表不断增多,大都演变成 了星座模型

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rom2XGwI-1621866567404)(…/TypoarWrokPath/images/1618996137568.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Kv0LjvgI-1621866567405)(…/TypoarWrokPath/images/1618996194640.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Hl2sQlOP-1621866567406)(…/TypoarWrokPath/images/1618996293284.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hHrbsXmf-1621866567406)(…/TypoarWrokPath/images/1618996348953.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rSid9o1A-1621866567407)(…/TypoarWrokPath/images/1618996372022.png)]

7.使用sqoop从MySQL导出数据到HDFS 上

注意每个 \ 后面不能有空格,有空格就会报错

1.hdfs上的路径可以不存在
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-65e3uI0Y-1621866567409)(…/TypoarWrokPath/images/1618998459083.png)]
参数说明
--connect #关系型数据库连接
--username #关系型数据库连接用户名
--password #关系型数据库连接密码
--table #关系型数据库的表
--split-by #如果-m的数量不为1，则一定要加上该参数且最好是数值类型，否则会报错
--direct　#快速模式，使用mysql自带的mysqldump导出数据
--delete-target-dir　#如果hdfs的目录已经存在则先删除
--target-dir #导入到hdfs时的目标目录
--export-dir #从hdfs导出时的源目录
--fields-terminated-by #导入到hdfs时的hdfs文件分隔符
--input-fields-terminated-by #从hdfs导出时的hdfs文件分隔符
--hive-drop-import-delims #导入hive中的数据某列中如果有换行符或回车键可以删除
--hive-database #hive的数据库
--hive-table #hive的表
--hive-overwrite #覆盖之前的分区插入数据
--hive-partition-key #hive分区字段
--hive-partition-value #hive分区值
--m #指定map数量，也是生成的文件数
1.从RDB数据库导出数据到HDFS
--"\"为换行符
sqoop import \
--connect jdbc:mysql://localhost:3306/bigdata \
--driver com.mysql.jdbc.Driver  \
--username root \
--password root \
--table view_stu \
--target-dir /data/view_stu \  
--m 3
(1)一堆的维度id + 度量  度量都是数字类型的，可累加，可计算，如成本、数量、金额等
2.1.从RDB数据库导出数据到HDFS 带条件
--携带的条件注意需要使用双引号包住
sqoop import \
--connect jdbc:mysql://localhost:3306/retail_db \
--driver com.mysql.jdbc.Driver \
--username root \
--password root \
--table customers \
--where "customer_id <= 10" \
--target-dir /data/retail_db \
--delete-target-dir  \
--m 1
3.从RDB数据库导出数据到HDFS指定列名
1.使用columns指定列名,每个字段使用逗号隔开
sqoop import \
--connect jdbc:mysql://localhost:3306/retail_db \
--driver com.mysql.jdbc.Driver \
--username root \
--password root \
--table customers \
--columns "customer_id,customer_fname,customer_lname" \
--target-dir /data/retail_db_columnes \
--delete-target-dir  \
--m 1
4.从RDB数据库导出数据到HDFS指定列名,query方式导入数据
1.所有查询都应该以 $CONDITIONS结束;sqoop内部使用该条件 将记录范围分发给所 有Mapper
2.一定要指定分割列split-by
3.必须要指定hdfs的路径 --target-dir
sqoop import \
--connect jdbc:mysql://localhost:3306/retail_db \
--driver com.mysql.jdbc.Driver \
--username root \
--password root \
--query "select * from customers where customer_id <=10 and \$CONDITIONS" \
--split-by customer_fname \
--target-dir /data/retail_db_query \
--delete-target-dir  \
--m 1
5.使用条件过滤导入MySQL数据到HDFS不指定目录
sqoop import \
--connect jdbc:mysql://localhost:3306/retail_db \
--driver com.mysql.jdbc.Driver \
--username root \
--password root \
--where "customer_id <=10" \
--table customers \
--m 1
6.从RDB数据库导出数据到HDFS,使用sqoop增量导入数据
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-I738maXc-1621866567410)(…/TypoarWrokPath/images/1619437351513.png)]
**1.Incremental指定增量导入的模式–增量导入方式为append 追加数据至已经存在的 **

HDFS数据集

append：追加数据记录

lastmodified：可追加更新的数据

2.Lastmodified 和Append的区别

Lastmodified 和Append模式的区别：
Append模式处理不了更新数据，而
Lastmodified模式可以

1.Append 支持动态增加 不支持修改

语法范式解析： sqoop import: SQOOP 命令，从关系型数据库导数到Hadoop –check-column: 用于检查增量数据的列 –incremental append: 设置为增量模式 –last-value :源数据中所有大于–last value的值都会被导入Hadoop

2.Lastmodified 可以修改数据 也可以增加

语法范式解析： sqoop import: SQOOP 命令，从关系型数据库导数到Hadoop –check-column: 必须是timestamp列 –incremental lastmodified: 设置为最后改动模式 –merge-key: 必须是唯一主键 –last-value: 所有大于最后一个时间的数据都会被更新

2.check-column -->指定递增的列,一般都使用每张表的主键

**3.last-value ‘2015-10-10’–>指定上一次导入的最大值,**比如说有十条记录,last-value的值为八,那么就只有两条数据导入,意思就是从八开始导入到最后
--使用append
sqoop import \
--connect jdbc:mysql://localhost:3306/retail_db \
--driver com.mysql.jdbc.Driver \
--username root \
--password root \
--table customers \
--incremental append \
--check-column customer_id \
--last-value  12433 \
--target-dir /data/retail_db_Incremental \
--m 1

--使用lastmodified
--注意 check-column 参数一定要是日期类型,也可以是时间戳类型
sqoop import \
--connect jdbc:mysql://localhost:3306/retail_db \
--driver com.mysql.jdbc.Driver \
--username root \
--password root \
--table orders \
--incremental lastmodified \
--check-column order_date \
--last-value  0 \
--target-dir /data/retail_db_lastmodified \
--m 1

7.从RDB数据库导出数据到HDFS,指定文件格式

导入时指定文件格式参数

–as-textfile 导入数据为text文件（默认）
–as-avrodatafile 导入数据为avro文件
–as-sequencefile 导入数据为sequence文件
–as-parquetfile 导入数据为parquet文件
sqoop import \
--connect jdbc:mysql://localhost:3306/retail_db \
--driver com.mysql.jdbc.Driver \
--username root \
--password root \
--table customers \
--where "customer_id <= 10" \
--target-dir /data/retail_db_as \
--delete-target-dir  \
--m 1  \
--as-sequencefile

8.从RDB导入数据到Hive,导入数据的同时创建表

1. 通过hive-import指定导入到Hive指定创建一个新的hive表，如果表已经存在则报错

2.表可以提前创建好,–导数据时不要表的语句就ok

sqoop import \
--connect jdbc:mysql://localhost:3306/retail_db \
--driver com.mysql.jdbc.Driver \
--username root \
--password root \
--table customers \
--hive-import \
--create-hive-table \
--hive-database retail_db \
--hive-table customers \
--m 3
1.报错
2解决 hive-exec-1.2.2.ja hive-common-1.2.2.jar
cp /opt/install/hive/lib/hive-exec-1.2.2.jar /opt/install/sqoop/lib/
cp /opt/install/hive/lib/hive-common-1.2.2.jar /opt/install/sqoop/lib/
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-IdMa5xJL-1621866567411)(…/TypoarWrokPath/images/1619171824685.png)]
9.从RDB导入数据到Hive,导入hive分区表
1.创建分区表
注意如果创表时指定了分隔符 ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’;
在sqoop的时候需要加上 --fields-terminated-by ‘,’ \
create table retail_db.orders_partition(
order_id string,
order_date string,
order_customer_id int,
order_status string
)
partitioned by(year string);sqoop import \
--connect jdbc:mysql://localhost:3306/retail_db \
--driver com.mysql.jdbc.Driver \
--username root \
--password root \
--query "select * from orders where order_date like '%2013%' and  \$CONDITIONS" \
--split-by order_id \
--target-dir /data/hive_orders \
--delete-target-dir  \
--hive-import \
--hive-table retail_db.orders_partition \
--hive-partition-key "year" \
--hive-partition-value "2013" \
--m 1
10.从hdfs中把数据导入到mysql
--注意分割符 数据从hive中导入到hdfs中默认分隔符是 \001
--方式一
sqoop export \
--connect jdbc:mysql://bigdata:3306/bigdata \
--username root \
--password root \
--table score_demo \
--fields-terminated-by '\001' \
--export-dir /data/tmp/school/analyzee/result/000000_0 \
--m 1

--方式二
--指定编码格式(具体不生效)
sqoop export \
--connect jdbc:mysql://bigdata:3306/bigdata?useUnicode=true&characterEncoding=utf-8 \
--username root \
--password root \
--table score_demo \
--fields-terminated-by '\001' \
--export-dir /data/tmp/school/analyzee/result/000000_0 \
--m 1
11.将HIVE的数据上传到HDFS
insert overwrite directory '/data/tmp/school/analyzee/result'
select * from score_hive ;
1.结果
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Icxx5V2k-1621866567411)(…/TypoarWrokPath/images/1619014982172.png)]
2.查看上传成功的文件(中文乱码)
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-B0Z4AHq4-1621866567412)(…/TypoarWrokPath/images/1619015010826.png)]
2.sqoop的bug,可以不管
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-uHiDc0Gg-1621866567413)(…/TypoarWrokPath/images/1618998466525.png)]

8.在mysql中直接使用.sql文创建表和导入数据

1.source /root/view_syu.sql source /路径

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-L58Hbomj-1621866567414)(…/TypoarWrokPath/images/1619013996169.png)]

9.案例步骤

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Mu8VB8as-1621866567415)(…/TypoarWrokPath/images/1619059237279.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RxY4wkFs-1621866567416)(…/TypoarWrokPath/images/1619059387325.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vyvVDHYG-1621866567416)(…/TypoarWrokPath/images/1619059477734.png)]

1 0.数据仓库的概念

数据仓库，英文名称为Data Warehouse，可简写为DW或DWH。数据仓库，是为企业所有级别的决策制定过程，提供所有类型数据支持的战略集合。它是单个数据存储，出于分析性报告和决策支持目的而创建。 为需要业务智能的企业，提供指导业务流程改进、监视时间、成本、质量以及控制。

11.数仓分层结构图

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-paSKhkEL-1621866567417)(…/TypoarWrokPath/images/1619060605178.png)]

ODS 层
原始数据层，存放原始数据**，直接加载原始日志、数据，数据保持原貌不做处理。**
DWD层
明细数据层
结构和粒度与ods层保持一致，对ods层数据进行清洗(去除空值，脏数据，超过极限范围的数据)，也有公司叫dwi。
DWS层
服务数据层
以dwd为基础，进行轻度汇总。一般聚集到以用户当日，设备当日，商家当日，商品当日等等的粒度。
在这层通常会有以某一个维度为线索，组成跨主题的宽表，比如 一个用户的当日的签到数、收藏数、评论数、抽奖数、订阅数、点赞数、浏览商品数、添加购物车数、下单数、支付数、退款数、点击广告数组成的多列表。
ADS层
数据应用层， 也有公司或书把这层成为app层、dal层、dm层，叫法繁多。
面向实际的数据需求，以DWD或者DWS层的数据为基础，组成的各种统计报表。
统计结果最终同步到RDS以供BI或应用系统查询使用。

4.数仓项目步骤

1.mysql的存储过程

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QgoOBs3q-1621866567418)(…/TypoarWrokPath/images/1619402516925.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JJn5wA8f-1621866567419)(…/TypoarWrokPath/images/1619402704252.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VtrZEb3O-1621866567420)(…/TypoarWrokPath/images/1619403253752.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-P2gfnMam-1621866567421)(…/TypoarWrokPath/images/1619403415700.png)]

2.在mysql建表语句

# 建库语句
create database if not exists sales_source default charset utf8 collate utf8_general_ci

# 建立商品表
create table product(
    product_code int(11) not null auto_increment,
    product_name varchar(128) not null,
    product_category varchar(256) not null,
    primary key(product_code)
)

# 建立用户表
create table customer(
	customer_number int(11) not null auto_increment,
    customer_name varchar(128) not null,
    customer_street_address varchar(256) not null,
    customer_zip_code int(11) not null,
    customer_city varchar(32) not null,
    customer_state varchar(32) not null,
    primary key (customer_number)
)

# 销售订单表
create table sales_order(
	order_number int(11) not null auto_increment,
    customer_number int(11) not null,
    product_code int(11) not null,
    order_date datetime not null,
    entry_date datetime not null,
    order_amount decimal(18,2) not null,
    primary key (order_number)
)

insert into customer(
	customer_name,
    customer_street_address,
    customer_zip_code,
    customer_city,
    customer_state
)
values('Big Customers', '7500 Louise Dr.', '17050','Mechanicsburg','PA'),
( 'Small Stores', '2500 Woodland St. ','17055' ,'Pittsburgh','PA'),
( 'Medium Retailers' ,'11111 Ritter Rd. ','17055','Pittsburgh',' PA'),
('Good Companies', ' 9500 Scott St.', ' 17050',' Mechanicsburg','PA' ),
( 'Wonderful Shops', '3333 Rossmoyne Rd. ', ' 17050','Mechani csburg','PA' ),
('Loyal Clients', '7070 Ritter Rd. ', ' 17055','Pittsburgh', ' PA');


 INSERT INTO product (product_name , product_category) VALUES
('Hard Disk', 'Storage'),
('Floppy Drive', 'Storage'),
('lcd panel', 'monitor')
;

--如果存在就删除
DROP PROCEDURE IF EXISTS usp_generate_order_data;
--指定结束符号,默认是;,替换成//
DELIMITER //
----创建存储过程
CREATE PROCEDURE usp_generate_order_data()
--开始
BEGIN
--如果表tmp_sales_order存在就删除
DROP TABLE IF EXISTS tmp_sales_order;
--创建一张表 tmp_sales_order并使用 as select 方式导入表结构,where 1=0意思就是不要导入数据
CREATE TABLE tmp_sales_order AS SELECT * FROM sales_order WHERE 1=0;
--创建变量 @+名字 mysql默认的格式 使用UNIX_TIMESTAMP()把时间转换成时间戳
SET @start_date := UNIX_TIMESTAMP('2018-1-1') ;
--创建变量 @+名字 mysql默认的格式 使用UNIX_TIMESTAMP()把时间转换成时间戳
SET @end_date := UNIX_TIMESTAMP('2018-11-23') ;
--创建变量 @+名字
SET @i := 1;
--while 循环10万次
WHILE @i<=100000 DO
--创建客户id变量 并且使用随机数生成1-6之间的数字,使用floor向下取整
SET @customer_number := FLOOR (1+RAND() *6) ;
--创建商品id变量 并且使用随机数生成1-4之间的数字,使用floor向下取整
SET @product_code := FLOOR(1+RAND()* 3) ;
--创建订单日期变量 将时间戳转换成日期格式
SET @order_date := FROM_UNIXTIME (@start_date+RAND() * (@end_date-@start_date) );
--订单总金额
SET @amount := FLOOR (1000+RAND() *9000) ;
--将数据插入到tmp_sales_order表里面去
INSERT INTO tmp_sales_order VALUES (@i, @customer_number, @product_code, @order_date, @order_date, @amount);
--创建变量 对i 进行累加
SET @i := @i +1;
--结束while循环
END WHILE;
--TRUNCATE 清空表数据,恢复自增到1
TRUNCATE TABLE sales_order;
--将数据导入到sales_order
INSERT INTO sales_order
SELECT NULL, customer_number, product_code, order_date,entry_date, order_amount
FROM tmp_sales_order ;
--提交
COMMIT ;
--最后删除tmp_sales_order
DROP TABLE tmp_sales_order;
--结束存储过程
END //
--调用储存过程
CALL usp_generate_order_data();

3.模型搭建

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-NDz8oKd5-1621866567422)(…/TypoarWrokPath/images/1619407264194.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-n7SpDbqI-1621866567423)(…/TypoarWrokPath/images/1619407822078.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-o83cetwR-1621866567424)(…/TypoarWrokPath/images/1619407999260.png)]

--模型搭建分析
将原本在mysql中的sales_order表进行细分,为多个维度表和一张事实表,将日期,订单号,提出来,把订单提出来是开考虑业务上面的需求,比如说订单里面还有订单状态,订单是否支付,参加什么活动,订单备注等,所以分成一张订单维度表,并且添加了版本号,起始日期,结束日期,事实表里面包含了,订单id,顾客id,商品id,订单日期,订单总金额

4.在hive中建库,建表,rdo ,ods 范指源数据层

1.使用source /opt/data/xx.sql 导入数据 ,本案例是使用上面的步骤2的数据

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hnhs2L6S-1621866567425)(…/TypoarWrokPath/images/1619408368482.png)]

5.RDS层在hive中创建库建表,使用sqoop导入数据

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-j1lWYWNq-1621866567426)(…/TypoarWrokPath/images/1619408498970.png)]

1.创建数据库

create database sales_rds;
use sales_rds;
drop table if exists rds.customer;
drop table if exists rds.product;
drop table if exists rds.sales_order;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kuBYiiHD-1621866567427)(…/TypoarWrokPath/images/1619408522332.png)]

--创建顾客表
create external table sales_rds.customer(
    customer_number int,
    customer_name string,
    customer_street_address string,
    customer_zip_code int,
    customer_city string,
    customer_state string
    )
    location '/data/sales_rds/customer';
    
    
--使用sqoop从mysql导入数据
sqoop import \
--connect jdbc:mysql://localhost:3306/sales_source \
--driver com.mysql.jdbc.Driver \
--username root \
--password root \
--table customer \
--hive-import \
--hive-database sales_rds \
--hive-table customer \
--m 3

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-M90KMKdd-1621866567428)(…/TypoarWrokPath/images/1619408545024.png)]

--创建商品表
create external table sales_rds.product(
    product_code int,
    product_name string,
    product_category string
)
location '/data/sales_rds/product';

--使用sqoop从mysql导入数据
sqoop import \
--connect jdbc:mysql://localhost:3306/sales_source \
--driver com.mysql.jdbc.Driver \
--username root \
--password root \
--table product \
--hive-import \
--hive-database sales_rds \
--hive-table product \
--m 3

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4KWXysct-1621866567429)(…/TypoarWrokPath/images/1619408574438.png)]

--创建订单数据
CREATE external TABLE sales_rds.sales_order
(
    order_number int,
    customer_number int,
    product_code int,
    order_date timestamp,
    entry_date timestamp,
    order_amount DECIMAL(18,2)
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
STORED AS TEXTFILE
location '/data/sales_rds/sales_order';
   
--使用sqoop从mysql导入数据
sqoop import \
--connect jdbc:mysql://localhost:3306/sales_source \
--driver com.mysql.jdbc.Driver \
--username root \
--password root \
--table sales_order \
--hive-import \
--fields-terminated-by '\t' \
--hive-database sales_rds \
--hive-table sales_order \
--m 3


--增量导入 (使用)
--使用sqoop从mysql导入数据
sqoop job --delete myjob
sqoop job --create myjob \
-- import \
--connect jdbc:mysql://localhost:3306/sales_source \
--driver com.mysql.jdbc.Driver \
--username root \
--password-file /sqoop/pwd/sqooppwd.pwd \
--table sales_order \
--hive-import \
--fields-terminated-by '\t' \
--lines-terminated-by '\n' \
--hive-database sales_rds \
--hive-table sales_order \
--check-column entry_date \
--incremental append \
--last-value '1900-1-1' \
--m 1

--执行
sqoop job --exec myjob

6.数据仓库

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lLVpXsvw-1621866567430)(…/TypoarWrokPath/images/1619426623851.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fEXtSzz8-1621866567431)(…/TypoarWrokPath/images/1619427359327.png)]

7.ETL过程

Extract抽取

CDC组件:动态 数据 捕获

1.基于时间戳:

要求源数据表里有插入时间,和更新时间,抽取新增数据时根据插入时间,抽取修改数据时根据跟新时间抽取

1.抽取新增数据

select * from tablename where create_time > last_load_time

2.缺点:

1.不能识别多次更新的数据,只能难道最后一次更新的数据

2.不能记录触发有删除逻辑的记录

2.基于触发器的CDC

当执行insert update delete 这些操作的时候可以使用数据库的触发器来执行一些动作,比如触发器将变更的数据保存到临时表中,然后从临时表抽取数据到ODS层

缺点:触发器会影响数据库的性能,这种方式使用的不多

3.基于快照的CDC

如果没有时间戳,也不允许触发器,就可以使用快招标,每天将mysql的表都导入到ODS层,每天存成一张新的表,这样子的话,我就可以追溯到任何一天的历史数据

缺点:占用了太多的存储空间

4.基于日志的CDC

最复杂也最没有入侵性的CDC方式就是基于日志的CDC方式,我们可以开启binlog,在这个日志当中,会记录我们的增删改操作,我们可以使用一些手段 / 工具将binlog解析出来,然后做出相应的操作

缺点:复杂

现在国内用的较多的插件是canal [阿里系]

8.全量抽取

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BYDCnAhu-1621866567433)(…/TypoarWrokPath/images/1619436517946.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ag3s6QGT-1621866567434)(…/TypoarWrokPath/images/1619437077414.png)]

--全量导入商品表
sqoop import \
--connect jdbc:mysql://localhost:3306/sales_source \
--driver com.mysql.jdbc.Driver \
--username root \
--password root \
--table product \
--hive-import \
--hive-database sales_rds \
--hive-table product \
--hive-overwrite \
--m 3

9.增量抽取

10.加载DW层

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-j9xLzMtu-1621866567435)(…/TypoarWrokPath/images/1619450960324.png)]

1.数据转换就是将数据进行重构以及标准化,消除数据的不一致性,转换最主要的作用就是数据清洗

一般数据清洗包含一下内容:

1.预处理:

对于大数据文件的加载,尤其是新文件,需要进行判断,不能贸然加载

2.标准化:

将不一致的数据进行统一

3.去重处理:

disinct[在hive中不推荐,效率低],group by ,row_number

4.错误值处理:

产生原因一般就是业务系统不够完善,导致一些不合法的值进入到了数据库,例如日期格式不正确,数值越界(年龄),全角半角(符号)等等,处理方式需要和老大/领导沟通一下,对数据是舍弃,还是修改

5.缺失值处理:

首先判断一下重要程度,然后判断数据量大不大,如果数据很重要,但是缺失很多,对接第三方库

6.建表建库

create database sales_dw;
use sales_dw;
--商品维度表
create table sales_dw.dim_product(
    product_sk int,
    product_code int,
    product_name string,
    product_category string,
    version string,
    effective_date date,
    expiry_date date
)
clustered by (product_sk) into 8 buckets
stored as orc tblproperties('transactional'='true');


--顾客维度表
create table sales_dw.dim_customer(
    customer_sk int,
    customer_number int,
    customer_name string,
    customer_streer_address string,
    customer_zip_code int,
    customer_city string,
    customer_state string,
    version string,
    effective_date date,
    expiry_date date
)
clustered by (customer_sk) into 8 buckets
stored as orc tblproperties('transactional'='true');
--日期维度表
create table dim_date(
    date_sk int,
    `date` date,
    month int,
    month_name string,
    quarter int,
    year int
)
row format delimited fields terminated by ','
stored as textfile;

--订单维度表
create table dim_order(
    order_sk int,
    order_number int,
    version string,
    effective_date date,
    expiry_date date
)
clustered by (order_sk) into 8 buckets
stored as orc tblproperties('transactional'='true');
--事实表
create table fact_sales_order(
    order_sk int,
    customer_sk int,
    product_sk int,
    order_date_sk int,
    order_amount decimal(18,2)
)
partitioned by (order_date string)
clustered by (order_sk) into 8 buckets
stored as orc tblproperties('transactional'='true');

11.Load加载

加载dim_product

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QhM6Ijuu-1621866567437)(…/TypoarWrokPath/images/1619457429484.png)]

insert overwrite table sales_dw.dim_product
select * from 
(
    select
    row_number() over(order by sp.product_code) rn,
    sp.product_code,
    sp.product_name,
    sp.product_category,
    '1.0',
    '2018-1-1',
    '2050-1-1'
    from sales_rds.product sp
) tmp;

加载dim_customer

insert into table sales_dw.dim_customer 
select * from 
(
select
    row_number() over(order by customer_number) rn,
    sp.customer_number ,
    sp.customer_name ,
    sp.customer_street_address ,
    sp.customer_zip_code ,
    sp.customer_city ,
    sp.customer_state ,
    '1.0',
    '2018-1-1',
    '2050-1-1'
   from  sales_rds.customer sp 
)tmp;

加载dim_order

insert into table sales_dw.dim_order
select * from (
     select
      row_number() over(order by order_number) rn,
      sp.order_number,
       '1.0',
       '2018-1-1',
       '2050-1-1'
    from sales_rds.sales_order  sp
)tmp;

加载dim_date

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-D7KIMILx-1621866567438)(…/TypoarWrokPath/images/1619487170054.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7aDg7YN8-1621866567440)(…/TypoarWrokPath/images/1619487215409.png)]

日期脚本

#!/bin/bash
#起始日期
date1="$1"
#终止日期
date2="$2"
#日期
tempdate = `date -d "$date1" +%F`
#起始日期时间戳
tempdateSec = `date -d "$date1" +%s`
#终止日期时间戳
enddateSec = `date -d "$date2" +%s`
min = 1
#max = `expr \ ($enddateSec - $tempdateSec \) / \(24 \* 60 \* 60 \) + 1`
#循环次数
max = 14611
#开始循环
while [[$min - le $max]]
do
#月份
month = `date -d "$tempdate" +%m`
#月英文缩写
month_name =`date -d "$tempdate" +%B`
#季度
quarter=`echo $month | awk '{print int(($0-1)/3 +1) }'`
#年
year = `date -d "$tempdate" +%Y`
#拼接字符串,写入到文件中
echo ${min}","${tempdate}","${month}","${month_name}","${quarter}","${year} >> ./dim_date.csv
#计算下一次的日期
tempdate = `date -d "+$min day $date1" +%F`
#计算下一周的时间戳
tempdateSec =`date -d "+min day $date1" +%s`
#变量加1
min =`expr $min + 1`
done

#!/bin/bash
date1="$1"
date2="$2"
tempdate=`date -d "$date1" +%F`
tempdateSec=`date -d "$date1" +%s`
enddateSec=`date -d "$date2" +%s`
min=1
max=14611
while [ $min -le $max ]
do
month=`date -d "$tempdate" +%m`
month_name=`date -d "$tempdate" +%B`
quarter=`echo $month | awk '{print int(($0-1)/3 +1) }'`
year=`date -d "$tempdate" +%Y`
echo ${min}","${tempdate}","${month}","${month_name}","${quarter}","${year} >> ./dim_date.csv
tempdate=`date -d "+$min day $date1" +%F`
tempdateSec=`date -d "+min day $date1" +%s`
min=`expr $min + 1`
done

加载fact_sales_order

1.DBeaver

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-c1MzdLLX-1621866567441)(…/TypoarWrokPath/images/1619496406160.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kEoWgH13-1621866567442)(…/TypoarWrokPath/images/1619497150012.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-462QE1QE-1621866567443)(…/TypoarWrokPath/images/1619496831149.png)]

-- 设置动态分区模式为非严格模式
set hive.exec.dynamic.partition.mode=nonstrict;
-- 开启动态分区
set hive.exec.dynamic.partition;

from (
    select 
    so.order_sk,
    sc.customer_sk,
    sp.product_sk,
    sd.date_sk,
    ss.order_amount,
    substring(ss.order_date,1,7) order_date
    from sales_rds.sales_order ss
    join sales_dw.dim_order so on ss.order_number =so.order_number
    join sales_dw.dim_customer sc on ss.customer_number =sc.customer_number
    join sales_dw.dim_product sp on ss.product_code =sp.product_code
    join sales_dw.dim_date sd on date(ss.order_date) =sd.`date`
)tmp
insert into table sales_dw.fact_sales_order partition(order_date)
select * ;

12.DM层(数据集市)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mOozZTyO-1621866567444)(…/TypoarWrokPath/images/1619505884731.png)]

-- 创建DM层数据库
create database if not exists sales_dm;
-- 切换数据库
use sales_dm;
-- 判断表是否存在
drop table if exists dm_order;
-- 创建表
create table dm_order as
select
sc.customer_sk,
sc.customer_number,
sc.customer_name,
sc.customer_streer_address,
sc.customer_zip_code,
sc.customer_city,
sc.customer_state,
sp.product_sk,
sp.product_code,
sp.product_name,
sp.product_category,
sd.date_sk,
sd.`date`,
sd.month,
sd.month_name,
sd.quarter,
sd.year,
sum(case when datediff('2018-05-13',sd.`date`)=0 then 1 else 0 end ) one_order_cnt, -- 当天订单个数
sum(case when datediff('2018-05-13',sd.`date`)=0 then sf.order_amount else 0 end ) one_order_amount, -- 当天订单金额
sum(case when datediff('2018-05-13',sd.`date`)<=1 then 1 else 0 end ) two_order_cnt, -- 近两天订单个数
sum(case when datediff('2018-05-13',sd.`date`)<=1 then sf.order_amount else 0 end) two_order_amount -- 近两天订单金额
from sales_dw.fact_sales_order sf
join sales_dw.dim_customer sc on sf.customer_sk=sc.customer_sk
join sales_dw.dim_product sp on sf.product_sk=sp.product_sk
join sales_dw.dim_date sd on sf.order_date_sk=sd.date_sk
where sd.`date`='2018-05-13'
group by
sc.customer_sk,
sc.customer_number,
sc.customer_name,
sc.customer_streer_address,
sc.customer_zip_code,
sc.customer_city,
sc.customer_state,
sp.product_sk,
sp.product_code,
sp.product_name,
sp.product_category,
sd.date_sk,
sd.`date`,
sd.month,
sd.month_name,
sd.quarter,
sd.year;

select
sc.customer_sk,sc.customer_number,sc.customer_name,sc.customer_streer_address,
sc.customer_zip_code,sc.customer_city,sc.customer_state,
sp.product_sk,sp.product_code,sp.product_name,sp.product_category,
sd.date_sk,sd.`date`,sd.month,sd.month_name,sd.quarter,sd.year,
sum(case when datediff('2018-08-22',sd.`date`)=0 then 1 else 0 end ) intraday_order_cnt -- 当天订单个数
from sales_dw.fact_sales_order sf
join sales_dw.dim_customer sc on sf.customer_sk=sc.customer_sk
join sales_dw.dim_product sp on sf.product_sk=sp.product_sk
join sales_dw.dim_date sd on sf.order_date_sk=sd.date_sk
where sd.`date`='2018-08-22'
group by
sc.customer_sk,sc.customer_number,sc.customer_name,sc.customer_streer_address,
sc.customer_zip_code,sc.customer_city,sc.customer_state,
sp.product_sk,sp.product_code,sp.product_name,sp.product_category,
sd.date_sk,sd.`date`,sd.month,sd.month_name,sd.quarter,sd.year;

13.数据仓库分层解析

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TOgzTrJL-1621866567445)(…/TypoarWrokPath/images/1619507313988.png)]

1.mysql 原始表结构

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6aiSORCr-1621866567446)(…/TypoarWrokPath/images/1619507862022.png)]

2.rds层表结构

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kfBV24AA-1621866567447)(…/TypoarWrokPath/images/1619507967641.png)]

3.dw层表结构,一张事实表,四张维度表

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BPJpIqBO-1621866567447)(…/TypoarWrokPath/images/1619507983081.png)]

',sd.date)<=1 then 1 else 0 end ) two_order_cnt, – 近两天订单个数
 sum(case when datediff(‘2018-05-13’,sd.date)<=1 then sf.order_amount else 0 end) two_order_amount – 近两天订单金额
 from sales_dw.fact_sales_order sf
 join sales_dw.dim_customer sc on sf.customer_sk=sc.customer_sk
 join sales_dw.dim_product sp on sf.product_sk=sp.product_sk
 join sales_dw.dim_date sd on sf.order_date_sk=sd.date_sk
 where sd.date=‘2018-05-13’
 group by
 sc.customer_sk,
 sc.customer_number,
 sc.customer_name,
 sc.customer_streer_address,
 sc.customer_zip_code,
 sc.customer_city,
 sc.customer_state,
 sp.product_sk,
 sp.product_code,
 sp.product_name,
 sp.product_category,
 sd.date_sk,
 sd.date,
 sd.month,
 sd.month_name,
 sd.quarter,
 sd.year;

~~~sql
select
sc.customer_sk,sc.customer_number,sc.customer_name,sc.customer_streer_address,
sc.customer_zip_code,sc.customer_city,sc.customer_state,
sp.product_sk,sp.product_code,sp.product_name,sp.product_category,
sd.date_sk,sd.`date`,sd.month,sd.month_name,sd.quarter,sd.year,
sum(case when datediff('2018-08-22',sd.`date`)=0 then 1 else 0 end ) intraday_order_cnt -- 当天订单个数
from sales_dw.fact_sales_order sf
join sales_dw.dim_customer sc on sf.customer_sk=sc.customer_sk
join sales_dw.dim_product sp on sf.product_sk=sp.product_sk
join sales_dw.dim_date sd on sf.order_date_sk=sd.date_sk
where sd.`date`='2018-08-22'
group by
sc.customer_sk,sc.customer_number,sc.customer_name,sc.customer_streer_address,
sc.customer_zip_code,sc.customer_city,sc.customer_state,
sp.product_sk,sp.product_code,sp.product_name,sp.product_category,
sd.date_sk,sd.`date`,sd.month,sd.month_name,sd.quarter,sd.year;

13.数据仓库分层解析

[外链图片转存中…(img-TOgzTrJL-1621866567445)]

1.mysql 原始表结构

[外链图片转存中…(img-6aiSORCr-1621866567446)]

2.rds层表结构

[外链图片转存中…(img-kfBV24AA-1621866567447)]

3.dw层表结构,一张事实表,四张维度表

[外链图片转存中…(img-BPJpIqBO-1621866567447)]