在上一篇的结尾，我们抛出了一个企业生产环境的建表语句，发现，这个建表语句中包含的知识点实在是太多了，如果面面俱到地去介绍，显然篇幅会过长。所以，本篇主要是对上一篇的建表语句中的知识点进行详细阐述。先把建表语句搬过来：
CREATE EXTERNAL TABLE mdm_corpus_yx_labeled (
`uid` string,
`serial` string,
`query` string COMMENT '问题',
`domain` string COMMENT '领域',
`intent` string COMMENT '意图',
`slots` string COMMENT '分槽，实体词',
`marked_domain` string COMMENT '标记的领域',
`marked_intent` string COMMENT '标记的意图',
`marked_slots` string COMMENT '标记的实体词',
`sid` string COMMENT '机器人sn号',
`b_answer` string COMMENT '回答')
PARTITIONED BY (`dates` STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':'
STORED AS PARQUET
LOCATION 'hdfs://xxxx/usr/corpus/unit_yx/mdm/mdm_corpus_yx_labeled';
外部表 外部表（EXTERNAL）指向已经在HDFS中存在的数据。它和内部表在元数据的组织上是相同的，而实际数据的存储则有较大的差异。内部表的创建过程和数据加载过程这两个过程可以分别独立完成，也可以在一个语句中完成，在加载数据的过程中，实际数据会被移动到数据仓库目录中；之后对数据的访问将会直接在数据仓库目录中完成。删除内部表时，表中的数据和元数据将被同时删除。而外部表只有一个过程，加载表和创建表同时完成（CREATE EXTERNAL TABLE …… LOCATION），实际数据存储在 LOCATION 后面指定的 HDFS 路径中，并不会移动到数据仓库目录中。当删除一个外部表时，其实只是删除该链接。
mdm_corpus_yx_labeled 表名是按照业务进行数据分层规范。那有的小伙伴就会问了，“什么是数据分层，为什么要进行数据分层？” 这是因为我们治理数据的时候，希望可以对数据有一个更加清晰的把控。详细来讲，为什么要进行数据分层有以下几个方面的原因：
- 清晰数据结构：数据的每一个分层都有其特点以及使用范围，这样我们在使用表的时候能更方便地定位和理解业务逻辑。
- 数据血缘追踪：通过数据分层，我们可以知道，顶层的表是依赖于底层的哪张表或者哪几张表。如果顶层的表一旦出现问题，我们可以迅速定位到底层的表；同样，如果底层的表出现了问题，我们同样可以快速定位到影响到哪几张顶层的业务表。
- 减少重复工作：规范数据分层，开发一些通用的中间层表，可以极大的减少重复计算。没有规范的数据分层时，我们每个需求任务都是从最原始的表中解析，获得相应的字段，然后再聚合分析输出。如果我们将数据规范按照业务线模块等进行分块，产生中间层表。那么我们再处理类似的需求时，就可以直接依赖于中间层表，而不需要每次都用原始表开始解析。
- 把复杂的问题简单化：将一个负责的任务分解成多个步骤来完成，每一层只处理单一的步骤，简化了问题。
对于数据分层的重要性，业界往往拿下面两张图做比喻。数据体系中的各个表的依赖就像是电线一样，我们都希望它是很规整，便于管理的。但是，大部分公司的数据往往是第一幅图，而非第二幅图。
那既然数据分层在数据仓库建设中有如此重要的地位，业界有没有一套统一的数据分层模型供大家参考呢？这里我们介绍一下猎豹移动的数据仓库建设：
在经过长时间的数据仓库迭代建设中，猎豹移动建成了适合自己业务的高可用数据仓库，其主要分为 ODM 层、MDM 层、IDM 层和 TDM 层四层数据仓库模型。
- ODM 层 源数据层（Original Data Model）存储源数据，面向业务应用。
- MDM 层 集市数据层（Market Data Model）存储基础集市，面向主题和业务线，提供相对中性，具有业务意义的初级加工数据。
- IDM 层 接口数据层（Interface Data Model）存储的是接口数据，面向具体应用，按需定制，提供对外数据服务或接口。
- TDM 层 临时数据层（Temp Data Model）存储业务处理过程中的临时数据。
-
PARTITIONED BY Partition 对应于关系数据库中的 Partition 列的密集索引，但是 Hive 中 Partition 的组织方式和数据库中的很不相同。在 Hive 中，表中的一个 Partition 对应于表下的一个目录，所有的 Partition 的数据都存储在对应的目录中。例如，
mdm_corpus_yx_labeled表中包含dates分区，则对应于dates = 2019-10-28的 HDFS 子目录为：/usr/corpus/unit_bhp/mdm/mdm_corpus_yx_labeled/2019-10-28
ROW FORMAT DELIMITED 是用来设置创建的表在加载数据的时候，支持的列分隔符，在表
mdm_corpus_yx_labeled中，列分隔符为\t。COLLECTION ITEMS TERMINATED BY 一个字段的各个
item的分隔符，在表mdm_corpus_yx_labeled中，字段中各个item分隔符为,。MAP KEYS TERMINATED BY Map集合中，
key和value之间的分隔符，在表mdm_corpus_yx_labeled中，集合中，key和value的分隔符为:。STORED AS Hive 支持 TextFile、RCFile、SequenceFile、AVRO、ORC 和 Parquet 等存储格式。那我们在建表的时候，到底要采用哪种格式呢？下面，我们来介绍一下各个存储格式的特点和适用场景：
- TextFile 每一行表示一条记录，每行都以换行符结尾。数据不做压缩，磁盘开销大，数据解析开销大。可以结合 gzip，bzip2 使用（系统自动会检查是否使用了上述2种压缩方式，并在执行查询时自动解压），但是采用这种方式，Hive 不会对数据进行切分，从而无法对数据进行并行操作。
- SequenceFile 是一种二进制文件存储方式，其具有使用方便、可分割、可压缩的特点，并支持三种压缩等级：NONE，RECORD，BLOCK，使用中一般建议采用 BLOCK 压缩。
- RCFile 是一种行列存储结合的存储方式。首先将数据按行分开，保证了同一条记录在一个快上，避免了读一条记录需要读取多个 BLOCK。其次，对于每个 BLOCK 列式存储，有利于数据的压缩和快速的列存取。
- ORC 提供了一种将数据存储在 Hive 表中的高效方法，其原理同样是将数据进行按行分块，每块按照列存储。但其效率要优于 RCFile，可以看做是 RCFile 的强化版。另外 ORC 还支持 ACID，支持单条记录的 update 和 delete。
- Parquet 也是一种列式存储的格式，相对于 ORC，Parquet 压缩比较低，且不支持单条记录的 update 和 delete，也不支持 ACID，但是 Parquet 支持 Impala 查询引擎，所以更推荐使用该存储格式。
下图为各个存储格式数据压缩比较：
- LOCATION 是外部表实际数据的存储路径。
其他常用命令汇总
- 创建视图，包含
query和domain字段：
hive> create view v_yx_labeled as select query,domain from mdm_corpus_yx_labeled;
- 查看数据库、表和视图：
hive> show databases;
hive> show databases like 'c.*'; # 以 c 开头的所有数据库
hive> use corpus;
hive> show tables;
- 向表中装载本地文件系统数据，并覆盖表中原有数据：
hive> load data local inpath '/user/liushaodong/unit_yx/data' overwrite into table mdm_corpus_yx_labeled_copy;
# 如果不覆盖表中原有数据，则去掉 overwrite 关键字
- 将分布式文件系统中的数据装载到表中，并覆盖表中原有数据：
hive> load data inpath 'hdfs://xxxx/usr/corpus/unit_yx/mdm/mdm_corpus_yx_labeled overwrite into table mdm_corpus_yx_labeled_copy;
# 去掉 local 关键字，即默认加载分布式文件系统中的数据
- 向表中插入数据，覆盖原有数据：
hive> insert overwrite table mdm_corpus_yx_labeled_copy select * from mdm_corpus_yx_labeled where dates = '2020-01-20';
- 向表中追加插入数据：
insert into table mdm_corpus_yx_labeled_copy select * from mdm_corpus_yx_labeled where dates = '2020-01-20';
# 追加数据即是将 overwrite 换成 into
OK，本篇到这里就要结束了，想了解更多有关于数据科学的小伙伴们，可以关注我哦！
