分布式数据库原理及技术

分布式数据库原理与技术

一、大数据领域科普概念（Lesson 1，仅作了解）

数据中台

数据湖仓

数据脱敏

分布式数据库雏形（ICBC Bank）

区块链

TCP 三次握手、四次挥手

联邦学习 / 深度学习

二、分布式数据库理论基础（Lesson 2）

1. 三级模式/两级映像
   在分布式数据库中仍然保留，并增加了新内容：全局模式和分片模式。

2. 性能对比指标
   与传统 RDBMS 对比的指标：存储空间大小、检索效率。

3. HDFS（GFS）的共享存储
   通过何种机制实现共享服务（问题提出，未在笔记中展开答案）。

4. 数据分类
   结构化数据：MySQL、Oracle 等
   半结构化数据：JSON、XML
   非结构化数据：视频等（处理路径：视频 → YOLO → JSON）

三、Hive 基础环境与工作模式（Lesson 2 - Lesson 3）

1. Hive 的三种部署方式（Lesson 2 Q4）
   嵌入式 Derby、远程 Metastore、本地 Metastore

2. Hive 的三种工作模式（Lesson 3 Q1）
   Derby 模式、远程模式、本地模式

3. 环境操作要点
   启动顺序：关闭防火墙 → 启动 Zookeeper → 检查状态 → 在 master 上sbin/start-all.sh启动 Hadoop
   MySQL 安装在 slave2，Hive 启用本地模式
   关闭顺序：Hive 中exit→ mastersbin/stop-all.sh→ 所有机器zkServer.sh stop→ 全部关机
   统一数据库名：bigdata2024，本地数据目录：/data

4. Metastore 存储位置
   存储在HDFS上的/warehousedir/home（除 Derby 模式外）

四、Hive 表与数据管理（Lesson 3 - Lesson 9）

4.1 基本操作

查看表结构：desc 表名（展示字段名）

查看详细参数：desc formatted 表名

显示建表语句：show create table 表名

永久配置：修改hive-site.xml；临时配置：Hive 控制台直接使用set命令，例如：

sethive.cli.print.current.db=true;sethive.cli.print.header=true;

4.2 表的默认存储与数据导入方式

默认存储位置：/warehousedir/home/xxxx.db/表名（即 Directory）

数据导入的三种方式：

1. insert 语句：insert into table 表名 values(...);或从其他表查询插入，会在表目录下生成文件000000_0

2. hdfs 上传：hdfs dfs -put 本地文件 表对应的HDFS目录，文件会直接成为表数据的一部分

3. load data 命令：load data local inpath '本地文件路径' into table 表名;会将本地文件复制到表目录下，若文件与表目录中已有文件重名，会自动生成带序号的副本（如xxx_copy_1）。若使用overwrite关键字则会先清空表目录下的所有文件再装载。

4.3 自定义存储位置（LOCATION）

可以使用location子句指定表在 HDFS 上的存储路径，此时不会自动创建数据库.db/表名的层级目录，而是直接使用给定路径。

要求：location 路径最好与默认管理体系一致，且精确到表名级别；建表时若有其他约束，location必须放在最后一行。

示例：

createtablestu_test(sidint,sname string,sageint,addr string)location'/testdb';

此时 HDFS 上只有/testdb目录，不会出现xxx.db/stu_test。

通常推荐的做法是让 location 的末级目录与表名一致，并放在数据库目录下，以便管理。

4.4 复杂数据类型（Array、Map、Struct）详细过程

1. Array 类型

建表语句：

createtablestudent1(sidint,sname string,grade array<float>)rowformat delimitedfieldsterminatedby','collection itemsterminatedby'#'linesterminatedby'\n';

分隔符说明：字段之间用逗号分隔；数组内部元素用#分隔；行之间用换行符。

数据文件student1.txt的内容格式如下：

1,zhangsan,80#90.5#35 2,lisi,90# #88 3,wangwu,87#87 4,mary, #60# 5,tom,60# # 6,jemy,78#

解析过程：Hive 读取文件时先按行分割，再按逗号切分出sid、sname和整个数组字符串"80#90.5#35"，然后用#切分数组元素，每个元素转换为 float。若某元素为空字符串（如90# #88中的第二个#之间为空），则解析为 NULL。末位的空元素（如78#）会被忽略，因此jemy的数组只含一个元素[78.0]。

装载数据：

loaddatalocalinpath'/opt/data/student1.txt'intotablestudent1;

查询数组元素（索引从 0 开始）：

selectgrade[1]assecond_gradefromstudent1wheresid=101;

2. Map 类型

建表语句：

createtablestudent2(sidint,sname string,grade map<string,float>)rowformat delimitedfieldsterminatedby','collection itemsterminatedby'#'mapkeysterminatedby':'linesterminatedby'\n';

分隔符说明：字段间用逗号；Map 中的键值对之间用#分隔；键和值之间用:分隔。

数据文件student2.txt示例：

1,zhangsan,语文:80#数学:90.5#英语:35 2,lisi,语文:90#数学:95#英语:88

装载方式同 Array。

查询 Map 中指定键的值：

selectgrade['数学']asmath_scorefromstudent2wheresid=101;

注意：如果建表时去掉collection items terminated by '#'，则 Hive 无法识别多个键值对之间的分隔，整个grade列会被当作一个单一字符串，无法正常解析为 Map。

3. Struct 类型

建表语句：

createtablestudent4(sidint,info struct<name:string,age:int,sex:string>)rowformat delimitedfieldsterminatedby','collection itemsterminatedby'#'linesterminatedby'\n';

数据文件student4.txt内容：

1,zhangsan#19#Female 2,lisi#20#male

解析说明：Struct 内部成员之间用#分隔，成员顺序与建表时定义的 struct 属性顺序严格一致。可以使用info.name、info.age等方式访问成员。

4. 复杂类型嵌套限制

如array<map<string,float>>或map<string,array<float>>等嵌套结构，无法直接使用分隔符方式定义。

若要实现此类嵌套，必须使用 JSON SerDe（如'org.apache.hive.hcatalog.data.JsonSerDe'），并将数据文件组织为标准 JSON 格式。示例：

createtablestudent3(sidint,sname string,grade array<map<string,float>>)rowformat serde'org.apache.hive.hcatalog.data.JsonSerDe';

数据文件内容需类似：

{"sid":1,"sname":"zhangsan","grade":[{"语文":80},{"数学":90.5}]}

核心结论：Array、Map、Struct 三种原生集合类型不能互相嵌套使用；Array 和 Struct 内部元素靠collection items分隔，Map 由于自带键值对分隔，隐含了 collection items 的功能。

4.5 表的类型与管理

1. 管理表（内部表）与外部表

创建外部表：

createexternaltablestudent_cp(sidint,sname string)rowformat delimitedfieldsterminatedby','location'/tmpdb/student_cp';

区别过程：使用drop table删除表时：

内部表：元数据与 HDFS 上的数据文件一并删除。

外部表：仅删除元数据，location 指向的文件夹及数据全部保留。下次创建同名、同结构的外部表并指向同一 location 时，可直接读取原有数据。

数据加载区别：

load data inpath '云端路径' into table 表名;会将 HDFS 上的文件移动到表目录，原路径文件消失。

load data local inpath '本地路径'是复制操作，本地文件不删除。

2. 分区表（静态分区与动态分区）

静态分区创建与装载全过程：

1. 建表，指定分区字段（注意：分区字段不能是表中已有的普通字段）：

createtablepart_table(sidint,sname string,sageint)partitionedby(addr string)rowformat delimitedfieldsterminatedby',';

1. 装载数据时明确写出分区值：

loaddatalocalinpath'/home/student100'intotablepart_tablepartition(addr='guangxi');

1. HDFS 存储结构：
   数据文件会被放置于/warehousedir/home/exam.db/part_table/addr=guangxi/目录下，文件名保持为student100。原有数据内容并不包含addr列，该列的值完全由分区目录名提供，查询时自动作为虚拟列出现。

2. 若重复执行相同分区的装载，同一目录下会出现student100_copy_1等带编号的副本文件。

3. 管理分区：

altertablepart_tableaddpartition(addr='guangxi');-- 添加分区（新建目录）altertablepart_tabledroppartition(addr='guangxi');-- 删除分区（删除对应目录及所有文件）

1. 多级分区：

createtablepart_table2(sidint,sname string)partitionedby(sageint,addr string)rowformat delimitedfieldsterminatedby',';-- 装载时需指定所有分区字段的值loaddatalocalinpath'/home/student100'intotablepart_table2partition(sage=20,addr='guangxi');

目录结构变为.../sage=20/addr=guangxi/。

非分区表无法转换为分区表，只能重建。

动态分区：建表语法与静态分区完全相同，只是在插入数据时由查询语句决定分区值（例如通过insert ... select addr from ...），装载速度更快，但对数据质量的控制不如静态分区精准。

3. 桶表

建表时指定分桶字段和排序字段：

createtablebuck_table(sidint,sname string)clusteredby(sid)sortedby(snamedesc)into4bucketsrowformat delimitedfieldsterminatedby',';

数据装载的关键区别：

1. 若使用load data local inpath ...直接装载文件，HDFS 上不会生成分桶目录，仅有一个原始文件，且数据未按sid分桶，也未按sname排序。

2. 必须通过insert into从其他表查询导入，才会真正执行分桶和排序：

insertintotablebuck_tableselect*fromsource_table;

此时 HDFS 表目录下会出现000000_0、000001_0等文件，编号从 0 开始，每个文件对应一个桶。

采样查询：

select*frombuck_table tablesample(bucket1outof4onsid);

无论数据是通过load data还是insert进入的，采样语法都可执行。但对于load data进入的未分桶数据，采样可能返回空或不符合预期结果；只有经过insert分桶后的数据才能正确按桶采样。

五、数据查询与连接（Lesson 10）

5.1 连接类型详解

笛卡尔连接：select * from student100, sc100;无连接条件，返回两表行数的乘积，通常无实际意义。

内连接（自然连接）：join ... on ...，只返回两表中满足等值条件的行，学生必须同时出现在 student100 和 sc100 中（不要求一定有成绩）。

左连接：left join ... on ...，以左表（student100）为主，返回左表所有行。右表无匹配时，sc100 的字段为 NULL。

右连接：right join ... on ...，以右表（sc100）为主，返回右表所有行。

全外连接：full outer join ... on ...，同时包含左右连接的结果，相当于left join和right join的并集（已去重）。

左半连接：left semi join ... on ...，本质是嵌套查询，只返回左表中能够在右表找到匹配键的行，且查询结果中不允许出现右表的字段。它在 Hive 中相当于where exists (select 1 from 右表 where 条件)但性能更优。

限制：Hive 的left semi join只支持一层嵌套。若要实现多层筛选（如选课人数大于2的学生），必须借助中间表或派生表：

-- 先找出人数>2的课程对应的学号，作为派生表select*fromstudent100innerjoin(selectsc100.snofromsc100jointempsc100onsc100.cno=tempsc100.cnowherecnt>2)aonstudent100.sno=a.sno;

这样就能避免直接使用多层left semi join的限制，且不需要创建物理中间表。

5.2 数据导出方式

输出到 HDFS 目录：

insertoverwrite directory'/hdfs路径'rowformat delimitedfieldsterminatedby'#'select*from表名;

从 HDFS 下载到本地：hadoop fs -get /hdfs路径 本地路径

拷贝表：

深拷贝：create table new_table as select * from old_table;—— 执行 MapReduce，复制表结构和数据。

浅拷贝：create table new_table like old_table;—— 仅复制表结构，数据为空。

六、视图、临时表与行列转换（Lesson 11 - Lesson 12）

6.1 视图与物理表的选择

创建视图：

createviewviewdbasselect...fromtb_casegroupbydname;

创建过程不启动 MapReduce，视图只是存储查询定义。当执行select * from viewdb时会启动 MapReduce 计算。

创建物理表：

createtabletmp_caseasselect...fromtb_casegroupbydname;

会在执行时启动 MapReduce，并将结果持久化到 HDFS，后续查询不重复计算。

临时性结果集：with t as (select ...)可用于复杂查询的分解，提升可读性。

6.2 条件函数与行列转换

if(条件, 真值, 假值)用于二分类。

case when ... then ... else ... end用于多条件转换，例如：

selectname,dname,casegenderwhen'男'then'm'when'女'then'f'endfromtb_case;

行转列（透视）示例：统计各部门男女数量。

selectdname,count(*)ascnt,sum(if(gender='男',1,0))asM,sum(if(gender='女',1,0))asFfromtb_casegroupbydname;

聚合注意事项：sum(jb+jj+tc)不能简写为sum(jb, jj, tc)。

greatest()是行内最大值函数（取一行多列的最大值），max()是列聚合函数（取多行某一列的最大值）。

七、常用函数与实战案例（Lesson 13 - Lesson 16）

7.1 字符串函数

length(),concat(),trim()（只去除开头和结尾空格），upper(),lower(),substr(),instr()

示例：

selectlength('你好');-- 返回字符数selecttrim(concat(' hello ',' world '));-- 返回去掉首尾空格的结果

7.2 日期与数值函数（身份证号实战）

提取出生日期并格式化：

altertablecid_infoaddcolumns(birthday string);-- 使用 insert overwrite 实现只更新新增列（需要包含所有旧字段）insertoverwritetablecid_infoselectid,cid,substr(cid,8,8)asbirthdayfromcid_info;altertablecid_infoaddcolumns(birth_date string);insertoverwritetablecid_infoselectid,cid,birthday,from_unixtime(unix_timestamp(substr(cid,8,8),'yyyyMMdd'),'yyyy-MM-dd')asbirth_datefromcid_info;

计算实岁与虚岁：

altertablecid_infoaddcolumns(age_shifloat,age_xuint);insertoverwritetablecid_infoselectid,cid,birthday,birth_date,birth_year,birth_month,birth_day,round(datediff(current_date,birth_date)/365,1)asage_shi,year(current_date)-birth_yearasage_xufromcid_info;

实岁：通过当前日期与出生日期相差天数除以365，保留一位小数。

虚岁：当前年份减去出生年份。

round 函数取整规则：

round(45.836, -1)对个位四舍五入，结果为50.0。

round(45.836, -2)对十位四舍五入，结果为0.0。

round(95.836, -2)对十位四舍五入，结果为100.0。

7.3 Hive 查询核心思路与限制

查询语句结构：select→distinct→from→join/on→where→group by→order by→limit

ORC 表在开启事务后可执行update和delete，而TextFile格式不支持行级更新，只能进行insert或insert overwrite覆盖。

当需要对已有表增加列并填充数据时，不能直接 update 某一列，通常采用alter table add columns添加列，然后通过insert overwrite table全量回填所有列的方式实现。

八、JSON 数据处理（Lesson 15）

解析 JSON 需借助外部 jar 包或内置函数get_json_object。

对于复杂嵌套类型（如array<map<string,float>>），必须使用 JSON SerDe，并在建表时指定：

createtablestudent3(sidint,sname string,grade array<map<string,float>>)rowformat serde'org.apache.hive.hcatalog.data.JsonSerDe';

数据文件每行必须为标准 JSON，例如：

{"sid":1,"sname":"zhangsan","grade":[{"语文":80},{"数学":90.5}]}