分布式数据库原理及技术

分布式数据库原理与技术一、大数据领域科普概念Lesson 1仅作了解数据中台数据湖仓数据脱敏分布式数据库雏形ICBC Bank区块链TCP 三次握手、四次挥手联邦学习 / 深度学习二、分布式数据库理论基础Lesson 2三级模式/两级映像在分布式数据库中仍然保留并增加了新内容全局模式和分片模式。性能对比指标与传统 RDBMS 对比的指标存储空间大小、检索效率。HDFSGFS的共享存储通过何种机制实现共享服务问题提出未在笔记中展开答案。数据分类结构化数据MySQL、Oracle 等半结构化数据JSON、XML非结构化数据视频等处理路径视频 → YOLO → JSON三、Hive 基础环境与工作模式Lesson 2 - Lesson 3Hive 的三种部署方式Lesson 2 Q4嵌入式 Derby、远程 Metastore、本地 MetastoreHive 的三种工作模式Lesson 3 Q1Derby 模式、远程模式、本地模式环境操作要点启动顺序关闭防火墙 → 启动 Zookeeper → 检查状态 → 在 master 上sbin/start-all.sh启动 HadoopMySQL 安装在 slave2Hive 启用本地模式关闭顺序Hive 中exit→ mastersbin/stop-all.sh→ 所有机器zkServer.sh stop→ 全部关机统一数据库名bigdata2024本地数据目录/dataMetastore 存储位置存储在HDFS上的/warehousedir/home除 Derby 模式外四、Hive 表与数据管理Lesson 3 - Lesson 94.1 基本操作查看表结构desc 表名展示字段名查看详细参数desc formatted 表名显示建表语句show create table 表名永久配置修改hive-site.xml临时配置Hive 控制台直接使用set命令例如sethive.cli.print.current.dbtrue;sethive.cli.print.headertrue;4.2 表的默认存储与数据导入方式默认存储位置/warehousedir/home/xxxx.db/表名即 Directory数据导入的三种方式insert 语句insert into table 表名 values(...);或从其他表查询插入会在表目录下生成文件000000_0hdfs 上传hdfs dfs -put 本地文件 表对应的HDFS目录文件会直接成为表数据的一部分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 arrayfloat)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.txtintotablestudent1;查询数组元素索引从 0 开始selectgrade[1]assecond_gradefromstudent1wheresid101;2. Map 类型建表语句createtablestudent2(sidint,sname string,grade mapstring,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_scorefromstudent2wheresid101;注意如果建表时去掉collection items terminated by #则 Hive 无法识别多个键值对之间的分隔整个grade列会被当作一个单一字符串无法正常解析为 Map。3. Struct 类型建表语句createtablestudent4(sidint,info structname: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. 复杂类型嵌套限制如arraymapstring,float或mapstring,arrayfloat等嵌套结构无法直接使用分隔符方式定义。若要实现此类嵌套必须使用 JSON SerDe如org.apache.hive.hcatalog.data.JsonSerDe并将数据文件组织为标准 JSON 格式。示例createtablestudent3(sidint,sname string,grade arraymapstring,float)rowformat serdeorg.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. 分区表静态分区与动态分区静态分区创建与装载全过程建表指定分区字段注意分区字段不能是表中已有的普通字段createtablepart_table(sidint,sname string,sageint)partitionedby(addr string)rowformat delimitedfieldsterminatedby,;装载数据时明确写出分区值loaddatalocalinpath/home/student100intotablepart_tablepartition(addrguangxi);HDFS 存储结构数据文件会被放置于/warehousedir/home/exam.db/part_table/addrguangxi/目录下文件名保持为student100。原有数据内容并不包含addr列该列的值完全由分区目录名提供查询时自动作为虚拟列出现。若重复执行相同分区的装载同一目录下会出现student100_copy_1等带编号的副本文件。管理分区altertablepart_tableaddpartition(addrguangxi);-- 添加分区新建目录altertablepart_tabledroppartition(addrguangxi);-- 删除分区删除对应目录及所有文件多级分区createtablepart_table2(sidint,sname string)partitionedby(sageint,addr string)rowformat delimitedfieldsterminatedby,;-- 装载时需指定所有分区字段的值loaddatalocalinpath/home/student100intotablepart_table2partition(sage20,addrguangxi);目录结构变为.../sage20/addrguangxi/。非分区表无法转换为分区表只能重建。动态分区建表语法与静态分区完全相同只是在插入数据时由查询语句决定分区值例如通过insert ... select addr from ...装载速度更快但对数据质量的控制不如静态分区精准。3. 桶表建表时指定分桶字段和排序字段createtablebuck_table(sidint,sname string)clusteredby(sid)sortedby(snamedesc)into4bucketsrowformat delimitedfieldsterminatedby,;数据装载的关键区别若使用load data local inpath ...直接装载文件HDFS 上不会生成分桶目录仅有一个原始文件且数据未按sid分桶也未按sname排序。必须通过insert into从其他表查询导入才会真正执行分桶和排序insertintotablebuck_tableselect*fromsource_table;此时 HDFS 表目录下会出现000000_0、000001_0等文件编号从 0 开始每个文件对应一个桶。采样查询select*frombuck_table tablesample(bucket1outof4onsid);无论数据是通过load data还是insert进入的采样语法都可执行。但对于load data进入的未分桶数据采样可能返回空或不符合预期结果只有经过insert分桶后的数据才能正确按桶采样。五、数据查询与连接Lesson 105.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.cnotempsc100.cnowherecnt2)aonstudent100.snoa.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 126.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男thenmwhen女thenfendfromtb_case;行转列透视示例统计各部门男女数量。selectdname,count(*)ascnt,sum(if(gender男,1,0))asM,sum(if(gender女,1,0))asFfromtb_casegroupbydname;聚合注意事项sum(jbjjtc)不能简写为sum(jb, jj, tc)。greatest()是行内最大值函数取一行多列的最大值max()是列聚合函数取多行某一列的最大值。七、常用函数与实战案例Lesson 13 - Lesson 167.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→limitORC 表在开启事务后可执行update和delete而TextFile格式不支持行级更新只能进行insert或insert overwrite覆盖。当需要对已有表增加列并填充数据时不能直接 update 某一列通常采用alter table add columns添加列然后通过insert overwrite table全量回填所有列的方式实现。八、JSON 数据处理Lesson 15解析 JSON 需借助外部 jar 包或内置函数get_json_object。对于复杂嵌套类型如arraymapstring,float必须使用 JSON SerDe并在建表时指定createtablestudent3(sidint,sname string,grade arraymapstring,float)rowformat serdeorg.apache.hive.hcatalog.data.JsonSerDe;数据文件每行必须为标准 JSON例如{sid:1,sname:zhangsan,grade:[{语文:80},{数学:90.5}]}