苏州网站建设自学,宁阳网站开发,北京模板网站开发,重庆建筑招聘网数据仓库和数据库
数据库和数仓区别
数据库与数据仓库的区别实际讲的是OLTP与OLAP的区别 操作型处理(数据库)#xff0c;叫联机事务处理OLTP#xff08;On-Line Transaction Processing#xff09;#xff0c;也可以称面向用户交易的处理系统#xff0c;它是针对具体业务…数据仓库和数据库
数据库和数仓区别
数据库与数据仓库的区别实际讲的是OLTP与OLAP的区别 操作型处理(数据库)叫联机事务处理OLTPOn-Line Transaction Processing也可以称面向用户交易的处理系统它是针对具体业务在数据库联机的日常操作通常进行增删改查操作。用户较为关心操作的响应时间、数据的安全性、完整性和并发支持的用户数等问题。传统的数据库系统作为数据管理的主要手段主要用于操作型处理。 分析型处理(数据仓库)叫联机分析处理OLAPOn-Line Analytical Processing,也可以称为面向专业分析人员进行数据分析,通常进行查询分析操作,一般针对某些主题的历史数据进行分析支持管理决策。
数据仓库主要特征:
面向主题的Subject-Oriented 、集成的Integrated、非易失的Non-Volatile和时变的Time-Variant
数据仓库是在数据库已经大量存在的情况下为了进一步挖掘数据资源、为了决策需要而产生的。
数据仓库的出现并不是要取代数据库,主要区别如下: 数据库是面向事务的设计数据仓库是面向主题设计的。 数据库一般存储业务数据数据仓库存储的一般是历史数据。 数据库是为捕获数据而设计数据仓库是为分析数据而设计 数据库设计是尽量避免冗余一般针对某一业务应用进行设计比如一张简单的User表记录用户名、密码等简单数据即可符合业务应用但是不符合分析。 数据仓库在设计是有意引入冗余依照分析需求分析维度、分析指标进行设计。
数仓的分层架构
数据仓库架构可分为三层——源数据层、数据仓库层、数据应用层: 源数据层ODS此层数据无任何更改直接沿用外围系统数据结构和数据 不对外开放为临时存储层是接口数据的临时存储区域为后一步的数据处理做准备。
数据仓库层DW也称为细节层DW层的数据应该是一致的、准确的、干 净的数据即对源系统数据进行了清洗去除了杂质后的数据。
数据应用层DA或APP前端应用直接读取的数据源根据报表、专题分析需求而计算生成的数据。
ETL和ELT
广义上ETL:数据仓库从各数据源获取数据及在数据仓库内的数据转换和流动都可以认为是ETL抽取Extract, 转化Transform , 装载Load的过程。 但是在实际操作中将数据加载到仓库却产生了两种不同做法:ETL和ELT。 狭义上ETL: 先从数据源池中抽取数据数据保存在临时暂存数据库中ODS。然后执行转换操作将数据结构化并转换为适合目标数据仓库系统的形式,然后将结构化数据加载到数据仓库中进行分析。 ELT: 从数据源中抽取后立即加载。没有专门的临时数据库ODS这意味着数据会立即加载到单一的集中存储库中,数据在数据仓库系统中直接进行转换然后进行分析
数据库操作 创建数据库: create database [if not exists] 库名 [comment 注释] [location 路径] [with dbproperties (kv)]; 注意: location路径默认是: hdfs://node1:8020/user/hive/warehouse 创建数据库语法:CREATE DATABASE [IF NOT EXISTS] db_name [LOCATION position]; create database if not exists myhive; use myhive; 查看数据库详细信息 desc database myhive; 数据库本质上就是在HDFS之上的文件夹。 默认数据库的存放路径是HDFS的/user/hive/warehouse内 创建数据库并指定hdfs存储位置
create database myhive2 location /myhive2;
使用location关键字可以指定数据库在HDFS的存储路径。 删除一个空数据库如果数据库下面有数据表那么就会报错
drop database myhive; 强制删除数据库包含数据库下面的表一起删除
drop database myhive2 cascade; 修改数据库的存储位置
alter database 库名set location hdfs://node1.itcast.cn:8020/路径; 修改存储位置不会响应之前存入的数据,会影响修改后存入的数据. 修改数据库属性. 修改当前数据库: use 库名;
alter database 库名 set dbproperties(‘k’’v’);
查看数据库:
查看指定数据库信息: desc database 库名;
查看指定数据库扩展信息: desc database extended 库名;
查看当前使用的数据库: select current_database();
查看所有的数据库: show databases;
查看某库建库语句: show create database 库名;
1. 数据库和HDFS的关系 Hive的库在HDFS上就是一个以.db结尾的目录 默认存储1在/user/hive/warehouse内 可以通过LOCATION关键字在创建的时候指定存储目录
-- todo ----------------------------hive 库操作-----------------------------
-- 创建数据库--------[常用]
create database if not exists myhive;
-- 使用数据库
use myhive;
-- 查看库的详细信息 -- 创建库就是在hdfs上创建一个文件夹
desc database myhive; -- hdfs://node1.itcast.cn:8020/user/hive/warehouse/myhive.db
-- 创建库并指定路径.[不常用]
create database if not exists myhive2 location /data/myhive2.db;
desc database myhive2;
-- 删除数据库
drop database myhive2;
-- 强制删除库
-- 为了演示创建一个表.
use myhive;
create table test (id int,name string);
-- 删除库
drop database myhive;-- 删除失败: Database myhive is not empty. One or more tables exist.
-- 强制删除库
drop database myhive cascade ;
-- 修改数据库路径: alter database 库名 set location hdfs://node1.itcast.cn:8020/路径
alter database myhive2 set location /aaa/myhive2.db; -- is not absolute. Please specify a complete absolute uri. /aaa/myhive2.db
alter database myhive2 set location hdfs://node1.itcast.cn:8020/aaa/myhive2.db;
desc database myhive2; -- hdfs://node1.itcast.cn:8020/aaa/myhive2.db [此时 hdfs页面看不到.]
use myhive2;
create table test (id int,name string); -- 在库下面创建表以后aaa目录和myhive2.db目录下都有test表
-- 修改数据库属性: alter database 库名 set dbproperties (kv);
alter database myhive2 set dbproperties (nametom);-- 可以给库携带键值对信息, 一般不常用.如果使用设置的一般是指定的key和value
-- 查看指定数据库信息: desc database 库名;
desc database myhive2; -- 可以查看库的位置信息,拥有者
-- 查看指定数据库扩展信息: desc database extended 库名;
desc database extended myhive2;-- 可以查看库的位置信息,拥有者,属性信息.
-- 查看当前使用的数据库: select current_database();
select current_database();
-- 查看所有的数据库: show databases;
show databases ;
-- 查看某库建库语句: show create database 库名;
show create database myhive2;
Hive操作表
表操作语法和数据类型
创建表语法 EXTERNAL创建外部表
PARTITIONED BY 分区表
CLUSTERED BY分桶表
STORED AS存储格式
LOCATION存储位置
......这些关键字后续会慢慢接触。
数据类型 分类 类型 描述 字面量示例 原始类型 BOOLEAN true/false TRUE TINYINT 1字节的有符号整数 -128~127 1Y SMALLINT 2个字节的有符号整数-32768~32767 1S INT 4个字节的带符号整数 1 BIGINT 8字节带符号整数 1L FLOAT 4字节单精度浮点数1.0 DOUBLE 8字节双精度浮点数 1.0 DEICIMAL 任意精度的带符号小数 1.0 STRING 字符串变长 “a”,’b’ VARCHAR 变长字符串 “a”,’b’ CHAR 固定长度字符串 “a”,’b’ BINARY 字节数组 TIMESTAMP 时间戳毫秒值精度 122327493795 DATE 日期 ‘2016-03-29’ 时间频率间隔 复杂类型 ARRAY 有序的的同类型的集合 array(1,2) MAP key-value,key必须为原始类型value可以任意类型 map(‘a’,1,’b’,2) STRUCT 字段集合,类型可以不同 struct(‘1’,1,1.0), named_stract(‘col1’,’1’,’col2’,1,’clo3’,1.0) UNION 在有限取值范围内的一个值 create_union(1,’a’,63)
Hive中支持的数据类型还是比较多的
其中红色的是使用比较多的类型
内部表和外部表区别
表分类
Hive中可以创建的表有好几种类型 分别是
内部表(MANAGED_TABLE) 分区表 分桶表
外部表(EXTERNAL_TABLE) 分区表 分桶表
在HDFS的表默认存储路径/user/hive/warehouse/库名.db 查看表类型和详情: DESC FORMATTED 表名;
内部表和外部表 内部表CREATE TABLE table_name ...
未被external关键字修饰的即是内部表,内部表又称管理表或者托管表。
删除内部表:直接删除元数据和存储的业务数据 外部表CREATE EXTERNAL TABLE table_name ...
被external关键字修饰的即是外部表,外部表又称非管理表或者非托管表。
删除外部表:仅仅是删除元数据存储的业务数据并不会被删除
快速对比一下内部表和外部表 1. Hive表的类型?
内部表(MANAGED_TABLE) 分区表 分桶表
外部表(EXTERNAL_TABLE) 分区表 分桶表
2. 内部表区别?
删除内部表:直接删除元数据metadata及存储数据
删除外部表:仅仅是删除元数据表的信息不会删除数据本身
内部表 创建内部表: create table [if not exists] 内部表名( 字段名 字段类型 , ... )...;
复制内部表: 复制表结构: CREATE TABLE 表名 like 存在的表名; 复制表结构和数据: CREATE TABLE 表名 as select语句;
删除内部表: drop table 内部表名;
查看表格式化信息:desc formatted 表名;
truncate清空内部表数据: truncate table 内部表名;
1. 创建内部表语法? create table [if not exists] 内部表名( 字段名 字段类型 , ... )...;
2. 内部表类型? MANAGED_TABLE
3. 删除内部表效果? 直接删除元数据metadata及存储数据
外部表 创建外部表: create external table [if not exists] 内部表名( 字段名 字段类型 , ... )...;
复制外部表: 复制表结构: CREATE TABLE 表名 like 存在的表名;
删除外部表: drop table 外部表名;
查看表格式化信息: desc formatted 表名;
注意: 外部表不能使用truncate关键字清空数据
1. 创建外部表语法? create external table [if not exists] 内部表名( 字段名 字段类型 , ... )...;
2. 外部表类型? EXTERNAL_TABLE
3. 删除外部表效果? 仅仅是删除元数据表的信息不会删除数据本身
4. 清空外部表效果? Cannot truncate non-managed table 表名 -- 外部表不能清空
查看/修改表
查看表操作
查看所有表: show tables;
查看建表语句: show create table 表名;
查看表信息: desc 表名;
查看表结构信息: desc extended 表名;
查看表格式化信息: desc formatted 表名;
修改表操作
修改表名: alter table 旧表名 rename to 新表名;
修改表路径: alter table 表名 set location hdfs中存储路径; 注意: 建议使用默认路径
修改表属性: alter table 表名 set tblproperties (属性名属性值); 注意: 经常用于内外部表切换
内外部表转换 内部表转外部表
alter table stu set tblproperties(EXTERNALTRUE); 外部表转内部表
alter table stu set tblproperties(EXTERNALFALSE); 通过stu set tblproperties来修改属性
要注意(EXTERNALFALSE) 或 (EXTERNALTRUE)为固定写法区分大小写
字段的添加: alter table 表名 add columns (字段名 字段类型);
字段的替换: alter table 表名 replace columns (字段名 字段类型 , ...);
字段名和字段类型同时修改: alter table 表名 change 旧字段名 新字段名 新字段类型;
注意: 字符串类型不能直接改数值类型
其余属性可参见 https://cwiki.apache.org/confluence/display/Hive/LanguageManualDDL#LanguageManualDDL-listTableProperties
1. 查看建表语句的语法?
show create table 表名;
2. 内部表转外部表
alter table stu set tblproperties(EXTERNALTRUE);
3. 外部表转内部表
alter table stu set tblproperties(EXTERNALFALSE);
默认分隔符
创建内部表
内部表的创建语法就是标准的CREATE TABLE table_name...... 创建一个基础的表 建库: create database if not exists myhive; 使用库:use myhive; 建表: create table if not exists stu(id int,name string); 插入数据:insert into stu values (1,zhangsan); 查询数据:select * from stu;
查看表的数据存储 使用谷歌浏览器在HDFS上查看表的数据存储文件000000_0中内容 数据分隔符
创建hive基础表的时候,默认的数据间分隔符是\001(^A)是一种特殊字符是ASCII值
在Notepad文本编辑器中是显示为SOH的。 Hive的默认字段分隔符?
\001(^A)是一种特殊字符是ASCII值
在Notepad文本编辑器中是显示为SOH的。
快速映射表
案例需求:
已知HDFS的/source/products.txt数据文件中,要求创建对应的Hive表,最终把文件中数据快速映射到Hive表中 Hive映射表的流程步骤 数据加载和导出
数据加载 - LOAD语法
我们使用 LOAD 语法从外部将数据加载到Hive内语法如下 建表 数据 load data local inpath /home/hadoop/search_log.txt into table myhive.test_load;
load data inpath /tmp/search_log.txt overwrite into table myhive.test_load;
注意基于HDFS进行load加载数据源数据文件会消失本质是被移动到表所在的目录中
数据加载 - INSERT SELECT 语法
除了load加载外部数据外我们也可以通过SQL语句从其它表中加载数据。
语法
INSERT [OVERWRITE | INTO] TABLE 表名 [PARTITION (...) ] select语句;
将SELECT查询语句的结果插入到其它表中被SELECT查询的表可以是内部表或外部表。
示例
INSERT INTO TABLE tbl1 SELECT * FROM tbl2;
INSERT OVERWRITE TABLE tbl1 SELECT * FROM tbl2;
数据加载 - 多种语法的选择
对于数据加载我们学习了LOAD和INSERT SELECT的方式那么如何选择它们使用呢
数据文件在window或者mac : 直接使用hdfs页面操作即可 方式1(页面操作): 在hdfsweb页面,直接上传windows文件到指定表的location路径下 数据文件在linux本地 推荐 load data local上传 方式2(load加载): 在hive客户端上,输入load data local inpath /linux本地文件路径 into table 表名; put命令上传 方式3(put上传): 在linux命令行,输入hdfs dfs -put /Linux本地文件路径 /HDFS的指定表的location路径下 数据文件在HDFS中: 直接页面移动文件或者使用load data方式移动 方式4(load加载): 在hive客户端上,输入load data inpath /HDFS的文件路径 into table 表名; 注意: 方式4本质就是把/HDFS的文件路径下的文件 移动 到 /HDFS的指定表的location路径下 数据已经在表中,且没有对应文件 INSERT into 表名 SELECT方式追加导入 INSERT overwrite table 表名 SELECT方式覆盖导入 方式5(insert插入): insert [overwrite | into] table 表名 select 语句; 注意: 方式5如果不加overwrite就是追加写入数据,如果加了overwrite就是覆盖原有数据 load命令注意事项 hive表数据导出 方式1(页面操作): 在hdfsweb页面,直接下载指定hdfs文件,到windows文件系统 方式2(get下载): 在linux命令行,输入hdfs dfs -get /HDFS的指定表的location路径下 /Linux本地文件路径 方式3(覆盖导出到linux): insert overwrite local directory /Linux本地文件路径 select 语句; *** 方式4(覆盖导出到hdfs): insert overwrite directory /HDFS文件路径 select 语句; 方式5(执行sql语句重定向到文件): hive -e sql语句 文件 #跳过登录直接运行sql命令 方式6(执行sql脚本重定向到文件): hive -f sql脚本 文件 #跳过登录直接运行sql脚本 1.导出都windows 2.导出到linux上 3.使用insert 导出到linux上 # 把表导出到linux目录中.分隔符默认
insert overwrite local directory /opt/export1 select * from products;
# 把表导出到linux目录中.分隔符指定为逗号.
insert overwrite local directory /opt/export2 row format delimited fields terminated by , select * from products;方式4(覆盖导出到hdfs): insert overwrite directory /HDFS文件路径 select 语句; # 不加local就是导出到hdfs上
insert overwrite directory /export1 select * from products; 方式5(执行sql语句重定向到文件): hive -e sql语句 文件 hive命令在linux执行: # hive -e sql : 免登录执行sql语句. # 把查询结果重定向到文件中 [rootnode1 export2]# hive -e select * from db1.products /opt/export3.txt
方式6(执行sql脚本重定向到文件): hive -f sql脚本 文件 # hive -f sql脚本文件 : 免登录执行sql脚本
[rootnode1 opt]# hive -f myhive.sql products4.txt分区表
在大数据中最常用的一种思想就是分治我们可以把大的文件切割划分成一个个的小的文件这样每次操作一个小的文件就会很容易了
同样的道理在hive当中也是支持这种思想的就是我们可以把大的数据按照每天或者每小时进行切分成一个个的小的文件这样去操作小的文件就会容易得多了。 如图一个典型的按月份分区的表
每一个分区是一个文件夹
同时Hive也支持多个字段作为分区多分区带有层级关系如图 分区表的使用 创建单级分区表
注意: 分区的列名不能和数据列名相同.分区列会当做虚拟列出现在数据列的后面.
create table order_one_part(id int,pname string,price int,uint int )partitioned by (year string) row format delimited fields terminated by ;
---添加数据
load data inpath /order202251.txt into table order_one_part partition(year2022);
load data inpath /order202351.txt into table order_one_part partition(year2023);
load data inpath /order202352.txt into table order_one_part partition(year2023);
-- 查询数据 -- 查询2022年的所有订单
-- 查询2022的数据2023目录中的数据没有参数进来.查询效率高.避免全表扫描.
select * from order_one_part where year2022;
-- 全查
select * from order_one_part; -- 不会提升效率 创建多分区表
-- 注意: 分区的列名不能和数据列名相同.分区列会当做虚拟列出现在数据列的后面.
create table order_multi_part(id int,pname string,price int,uint int )partitioned by (year string,month string,day string) row format delimited fields terminated by ;
---添加数据
load data inpath /order202251.txt into table order_multi_part partition(year2022,month5,day1);
load data inpath /order202351.txt into table order_multi_part partition(year2023,month5,day1);
load data inpath /order202352.txt into table order_multi_part partition(year2023,month5,day2);
load data inpath /order2023415.txt into table order_multi_part partition(year2023,month4,day15);
-- 查询2023年5月1号中的数据.查询效率高.避免全表扫描.
select * from order_multi_part where year2023 and month5 and day1; 查看分区(注意score为表名) show partitions score; 添加一个分区 alter table score add partition(month202005); 同时添加多个分区 alter table score add partition(month202004) partition(month202003);
注意添加分区之后就可以在hdfs文件系统当中看到表下面多了一个文件夹 删除分区 alter table score drop partition(month202006); 修改分区值 alter table score partition(month202005) rename to partition(month202305); 同步/修改分区 msck repair table 分区表名;
1. 分区表概念以及使用场景
建表的使用partitioned by (分区字段名 分区字段类型)关键字
分区其实就是HDFS上的不同文件夹
分区表可以极大的提高特定场景下Hive的操作性能
2. 分区表的完整语法
create table tablename(...) partitioned by (分区列 列类型, ...) row format delimited fields terminated by ;
分区表的分区列在partitioned by 中定义不在普通列中定义
分桶表
分桶和分区一样也是一种通过改变表的存储模式从而完成对表优化的一种调优方式
但和分区不同分区是将表拆分到不同的子文件夹中进行存储而分桶是将表拆分到固定数量的不同文件中进行存储。 如果没有分桶设置插入加载数据只是简单的将数据放入到 表的数据存储文件夹中没有分区 表指定分区的文件夹中带有分区 一旦有了分桶设置比如分桶数量为3那么表内文件或分区内数据文件的数量就限定为3
当数据插入的时候需要一分为3进入三个桶文件内。 分桶表准备
开启分桶的自动优化 开启分桶功能,
注意: Hive2.x版本移除了此项,默认开启
set hive.enforce.bucketingtrue;
基础分桶表
创建基本分桶表,设置分桶数量为3
create table course (
cid int,
cname string,
sname string
)
clustered by(cid) into 3 buckets
row format delimited fields terminated by \t;分桶表关键字解析:
clustered by(cid) : 指定进行分桶的字段,这里是根据字段id进行分桶
into 3 buckets : 设置桶的数量,这里表示设置了3个桶
注意: 桶数量决定了hdfs对应表路径中桶文件的数量
基础分桶表结果如下: 分桶表的关键字 clustered by(字段名) : 指定按照哪个字段进行分桶 into x buckets : 设置分桶数量为x个
什么是桶内排序呢?
定义: 对桶内的数据根据指定的字段列表进行排序 分桶表: 根据age分为三个桶 因为有排序,排序可以减少后续数据的扫描量, 从而减省扫描时间提高效率
分桶表桶内排序语法 1- 在clustered by 后面添加 分桶排序关键词: sorted by()
2- 在括号中设置排序字段列表和排序的规则
分桶表桶内排序案例实现
需求: 说明需求: 根据age字段分桶, 并按照age字段进行升序排序, 分桶数量为3 需求: 根据资料提供的teacher.txt文件数据, 构建HIVE分桶表, 要求根据地区进行分2个桶, 然后根据性别升序排序根据年龄进行倒序排列
数据样例 create table teacher(tid string,tname string,sex string,age int,address string
)clustered by (sex) into 2 buckets
row format delimited fields terminated by ,;
load data inpath /teacher.txt into table teacher;
select * from teacher;-- 查询数据 -- 没有提高效率
select * from teacher where sex男; -- todo 查询数据 -- 提高效率.先计算1在那个桶.然后只查询该桶的数据. Hash取模
-- todo --- 分桶原理--哈希取模法-- hash(列名) % 桶个数 桶编号
-- 取哈希值列名为值
select hash(值);
-- 取哈希值后取绝对值为正数列名为值
select abs(hash(值));
-- 取桶编号设桶个数为3
select abs(hash(值)) % 3;
结果如下 注意列名为整数数字取的哈希值为原数字
Hash算法是一种数据加密算法其原理我们不去详细讨论我们只需要知道其主要特征 同样的值被Hash加密后的结果是一致的 比如字符串“binzi”被Hash后的结果是93742710仅作为示意那么无论计算多少次字符串“binzi”的结果都会是93742710。 比如字符串“bigdata”被Hash后的结果是-114692950仅作为示意那么无论计算多少次字符串“bigdata”的结果都会是-114692950。
基于如上特征在辅以有3个分桶文件的基础上将Hash的结果基于3取模除以3 取余数
那么可以得到如下结果 无论什么数据得到的取模结果均是0、1、2 其中一个 同样的数据得到的结果一致如’binzi’ hash取模结果是0无论计算多少次它的取模结果都是0
Hash取模确定数据归属哪个分桶文件
基于Hash取模数据中的每一个分桶列的值都被hash取模得到0、1、2其中一个数
基于结果存入对应序号的桶文件中。 什么是Hash取模 基于Hash算法Hash算法是一种数据加密算法,对值进行计算注意同一个值得到同样的结果 分桶表对分桶字段值hash结果进行取模除以桶数量得到余数取模结果一样的放到同一个桶文件 同样分桶列的值的数据会在同一个桶中。
分桶表的性能提升
如果说分区表的性能提升是在指定分区列的前提下减少被操作的数据量从而提升性能。
分桶表的性能提升就是基于分桶列的特定操作如过滤、JOIN、分组、抽样等均可带来性能提升。 基于分桶列过滤单个值
基于分桶列过滤值hadoop
基于分桶列过滤值mapreduce 基于分桶列进行双表JOIN 桶文件对桶文件
基于分桶列group by 分组 自动归并为各个组了
分桶表能带来什么性能提升
在基于分桶列做操作的前提下
单值过滤
JOIN
GROUP BY
抽样
复杂类型操作
复杂类型
Hive支持的数据类型很多除了基本的int、string、varchar、timestamp等
还有一些复杂的数据类型 array
集合/数组类型 map
key-value映射类型 struct
结构类型
理解Hive SerDe机制
前面我们已经学习了hive的自定义分隔符格式: ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘指定分隔符’
那么这么长的格式具体是什么意思呢?那就跟着老师一起来了解下Hive SerDe机制吧.
在Hive的建表语句中和SerDe相关的语法为 其中ROW FORMAT是语法关键字DELIMITED和SERDE二选其一。本次我们主要学习DELIMITED关键字相关知识点
如果使用delimited: 表示底层默认使用的Serde类:LazySimpleSerDe类来处理数据。
如果使用serde:表示指定其他的Serde类来处理数据,支持用户自定义SerDe类。 LazySimpleSerDe是Hive默认的序列化类
包含4种子语法分别用于指定字段之间、集合元素之间、map映射 kv之间、换行的分隔符号。 在建表的时候可以根据数据的类型特点灵活搭配使用。
COLLECTION ITEMS TERMINATED BY 分隔符 : 指定集合类型array/结构类型(struct)元素的分隔符
MAP KEYS TERMINATED BY 分隔符 表示映射类型(map)键值对之间用的分隔
array类型
如下数据文件有2个列locations列包含多个城市:
说明:name与locations之间制表符分隔locations中元素之间逗号分隔 可以使用array数组类型存储locations的数据
建表语句
create table if not exists table_array(name string,address arraystring)
row format delimited fields terminated by \t
collection items terminated by ,; fields terminated by \t 表示列分隔符是\t COLLECTION ITEMS TERMINATED BY , 表示集合array元素的分隔符是逗号 导入数据:
-- 加载linux上的数据到hive表中 --特点: 加载完毕后linux上的数据依然存在.
load data local inpath /mnt/data_for_array_type.txt into table table_array;
常用array类型查询
select * from table_array;
-- 需求: 查询zhangsan的地址有几个?
select address from table_array where namezhangsan;
select name, size(address) as addr_size from table_array where namezhangsan;-- 需求: 查询zhangsan的第二个地址?
select name,address[1] as addr from table_array where namezhangsan;-- 需求: 查询zhangsan是否在tianjin住过?
select * from table_array where array_contains(address,tianjin);
select name,array_contains(address,tianjin) 入住记录 from table_array where name zhangsan;
结果如下 1. array类型主要存储数组格式 保存一堆同类型的元素如1, 2, 3, 4, 5
2. 定义格式 array类型 数组元素之间的分隔符collection items terminated by 分隔符
3. 在查询中使用 数组[数字序号]可以取出指定需要元素从0开始 size(数组)可以统计数组元素个数 array_contains(数组, 数据)可以查看指定数据是否在数组中存在
struct类型
struct类型是一个复合类型可以在一个列中存入多个子列每个子列允许设置类型和名称 有如下数据文件说明字段之间#分割struct之间冒号分割 建表语句
create table table_struct(id int,info structname:string,age:int ,birth:string)
row format delimited fields terminated by #
collection items terminated by :; fields terminated by # 表示列分隔符是# COLLECTION ITEMS TERMINATED BY 表示集合array元素的分隔符是逗号 导入数据
-- 加载数据
load data local inpath /mnt/data_for_struct_type.txt into table table_struct;
常用查询
-- 查询所有列
select * from table_struct;
-- 需求: 获取所有的姓名
select info.name from table_struct;
-- 需求: 获取所有的年龄
select info.age from table_struct;
结果如下 1. struct类型主要存储复合格式 可以包含多个二级列二级列支持列名和类型如 “a”: 1, “b”: “foo”, “c”: “2000-01-01”
2. 定义格式 structname:string, age:int struct的分隔符只需要COLLECTION ITEMS TERMINATED BY 分隔符 只需要分隔数据即可数据中不记录keykey是建表定义的固定的
3. 在查询中使用 struct字段.子字段名 即可取得对应的值
map类型
map类型其实就是简单的指代Key-Value型数据格式。 有如下数据文件其中members字段是key-value型数据 字段与字段分隔符: “,”需要map字段之间的分隔符#map内部k-v分隔符: 建表语句
create table if not exists table_map(id int,name string, relation mapstring,string,age int)
row format delimited fields terminated by ,
collection items terminated by #
map keys terminated by :;
MAP KEYS TERMINATED BY : 表示key-value之间用:分隔 导入数据
-- 加载数据
load data local inpath /mnt/data_for_map_type.txt into table table_map;
常用查询
select * from table_map;
-- 需求: 查看所有人的father,mother信息
select name,relation[father],relation[mother] from table_map;
-- 需求: 查看所有人的家庭相关角色
select name,map_keys(relation) from table_map;
-- 需求: 查看所有人的家庭相关姓名
select name,map_values(relation) from table_map;
-- 需求: 查看所有人的家庭相关人员个数
select name,size(map_keys(relation)) from table_map;
-- 需求: 查看马大云是否包含brother角色
select name,array_contains(map_keys(relation),brother) 是否有兄弟 from table_map where name马大云;
结果如下 1. map类型主要存储K-V键值对类型数据
保存一堆同类型的键值对如“a”:1, “b”: 2, “c”: 3
2. 定义格式
mapkey类型, value类型
不同键值对之间COLLECTION ITEMS TERMINATED BY 分隔符 分隔
一个键值对内使用 MAP KEYS TERMINATED BY 分隔符 分隔K-V
如father:xiaoming#mother:xiaohuang#brother:xiaoxu 不同KV之间使用#分隔同一个KV内用:分隔K和V
3. 在查询中使用
map[key]来获取指定key的值
map_keys(map)取到全部的key作为array返回map_values(map)取到全部values
size(map)可以统计K-V对的个数
array_contains(map_values(map), 数据) 可以统计map是否包含指定数据
复杂类型array、map、struct总结
