【Hive】——DDL（CREATE TABLE）

news/2024/7/7 21:33:28

1 CREATE TABLE 建表语法

在这里插入图片描述

在这里插入图片描述

2 Hive 数据类型

在这里插入图片描述
在这里插入图片描述

2.1 原生数据类型

在这里插入图片描述

2.2 复杂数据类型

在这里插入图片描述

2.3 Hive 隐式转换

在这里插入图片描述

2.4 Hive 显式转换

在这里插入图片描述

2.5 注意

在这里插入图片描述

3 SerDe机制

在这里插入图片描述
在这里插入图片描述

3.1 读写文件机制

在这里插入图片描述

3.2 SerDe相关语法

在这里插入图片描述

3.2.1 指定序列化类（ROW FORMAT SERDE ‘’）

在这里插入图片描述
在这里插入图片描述

3.2.2 指定分隔符（row format delimited fields terminated by “\t”）

在这里插入图片描述
在这里插入图片描述

4 Hive 默认分隔符

在这里插入图片描述
在这里插入图片描述

5 存储路径（location）

5.1 默认存储路径

在这里插入图片描述

在这里插入图片描述

5.2 Location 修改数据存储路径

在这里插入图片描述
在这里插入图片描述

6 练习

6.1 简单数据类型

6.1.1 建表语句

create table t_archer(
    id int comment "ID",
    name string comment "英雄名称",
    hp_max int comment "最大生命",
    mp_max int comment "最大法力",
    attack_max int comment "最高物攻",
    defense_max int comment "最大物防",
    attack_range string comment "攻击范围",
    role_main string comment "主要定位",
    role_assist string comment "次要定位"
) comment "王者荣耀射手信息"
    row format delimited fields terminated by "\t";

6.1.2 上传数据文件到hdfs

在这里插入图片描述

 hadoop dfs -put archer.txt /user/hive/warehouse/test.db/t_archer

6.1.3 查询

select * from t_archer;

在这里插入图片描述

6.2 复杂数据类型

6.2.1 建表语句

create table t_hot_hero_skin_price(
    id int,
    name string,
    win_rate int,
    skin_price map<string,int>
) row format delimited
    fields terminated by ',' --字段之间分隔符
    collection items terminated by '-'  --集合元素之间分隔符
    map keys terminated by ':'; --集合元素kv之间分隔符;

6.2.2 上传数据文件到hdfs

在这里插入图片描述

hadoop dfs -put hot_hero_skin_price.txt /user/hive/warehouse/test.db/t_hot_hero_skin_price

6.2.3 查询

select * from t_hot_hero_skin_price;
在这里插入图片描述

6.3 默认分隔符

6.3.1 建表语句

create table t_team_ace_player(
    id int,
    team_name string,
    ace_player_name string
); --没有指定row format语句 此时采用的是默认的\001作为字段的分隔符

6.3.2 上传数据文件到hdfs

在这里插入图片描述

hadoop dfs -put team_ace_player.txt /user/hive/warehouse/test.db/t_team_ace_player

6.3.3 查询

select * from t_team_ace_player;

在这里插入图片描述

6.4 指定数据存储路径

6.4.1 建表语句

create table t_team_ace_player_location(
id int,
team_name string,
ace_player_name string
)
location ‘/data’; --使用location关键字指定本张表数据在hdfs上的存储路径

6.4.2 上传数据文件到hdfs

在这里插入图片描述
hadoop dfs -put team_ace_player.txt /data

6.4.3 查询

select * from t_team_ace_player_location;
在这里插入图片描述

7 内部表、外部表 （external）

7.1 内部表

在这里插入图片描述
默认情况下创建的表就是内部表

7.2 外部表

在这里插入图片描述
在这里插入图片描述

7.3 内部表、外部表的差异

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

7.4 如何选择内部表或外部表

在这里插入图片描述

8 分区表（partitioned by）

8.1 概述

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

8.2 建表语句

在这里插入图片描述
在这里插入图片描述
注： 分区字段不能是表中已经存在的字段，因为分区字段最终也会以细腻字段的形式显示在表结构上。

--注意分区表创建语法规则
--分区表建表
create table t_all_hero_part(
    id int,
    name string,
    hp_max int,
    mp_max int,
    attack_max int,
    defense_max int,
    attack_range string,
    role_main string,
    role_assist string
) partitioned by (role string)--注意哦 这里是分区字段
    row format delimited fields terminated by "\t";

8.3 分区表数据加载–静态分区

在这里插入图片描述

--双分区表，按省份和市分区
--分区字段之间是一种递进的关系 因此要注意分区字段的顺序 谁在前在后
create table t_user_province_city (id int, name string,age int) partitioned by (province string, city string);
静态上传文件
SQL
load data local inpath '/root/data/all_hero/archer.txt' into table t_all_hero_part partition(role='sheshou');
load data local inpath '/root/data/all_hero/assassin.txt' into table t_all_hero_part partition(role='cike');
load data local inpath '/root/data/all_hero/mage.txt' into table t_all_hero_part partition(role='fashi');
load data local inpath '/root/data/all_hero/support.txt' into table t_all_hero_part partition(role='fuzhu');
load data local inpath '/root/data/all_hero/tank.txt' into table t_all_hero_part partition(role='tanke');
load data local inpath '/root/data/all_hero/warrior.txt' into table t_all_hero_part partition(role='zhanshi');

在这里插入图片描述

8.4 分区表数据加载–动态分区

8.4.1 概述

在这里插入图片描述

8.4.2 开启动态分区

在这里插入图片描述

SQL
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;

8.4.3 动态分区插入

--创建一张新的分区表 t_all_hero_part_dynamic
create table t_all_hero_part_dynamic(
    id int,
    name string,
    hp_max int,
    mp_max int,
    attack_max int,
    defense_max int,
    attack_range string,
    role_main string,
    role_assist string
) partitioned by (role string)
    row format delimited
        fields terminated by "\t";


insert into table t_all_hero_part_dynamic partition(role) --注意这里 分区值并没有手动写死指定
select tmp.*,tmp.role_main from t_all_hero tmp;

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

8.5 多重分区表

8.5.1 概述

在这里插入图片描述

8.5.2 加载数据

--双分区表的数据加载 静态分区加载数据
load data local inpath '/root/hivedata/user.txt' into table t_user_province_city
    partition(province='zhejiang',city='hangzhou');
load data local inpath '/root/hivedata/user.txt' into table t_user_province_city
    partition(province='zhejiang',city='ningbo');
load data local inpath '/root/hivedata/user.txt' into table t_user_province_city
    partition(province='shanghai',city='pudong');

8.5.3 双分区进行过滤

--双分区表的使用  使用分区进行过滤 减少全表扫描 提高查询效率
select * from t_user_province_city where  province= "zhejiang" and city ="hangzhou";

9 分桶表 （buckets）

9.1 概述

在这里插入图片描述

9.2 规则

在这里插入图片描述
在这里插入图片描述

9.3 语法

在这里插入图片描述在这里插入图片描述

9.4 案例

9.4.1 创建分桶表

CREATE TABLE t_usa_covid19_bucket(
    count_date string,
    county string,
    state string,
    fips int,
    cases int,
    deaths int
)
CLUSTERED BY(state) INTO 5 BUCKETS; --分桶的字段一定要是表中已经存在的字段

9.4.2 创建普通表

CREATE TABLE t_usa_covid19(
    count_date string,
    county string,
    state string,
    fips int,
    cases int,
    deaths int
)
    row format delimited fields terminated by ",";

9.4.3 向普通表导入数据

hadoop dfs -put us-covid19-counties.dat /user/hive/warehouse/test.db/t_usa_covid19

在这里插入图片描述

9.4.5 基于分桶字段查询

--基于分桶字段state查询来自于New York州的数据
--不再需要进行全表扫描过滤
--根据分桶的规则hash_function(New York) mod 5计算出分桶编号
--查询指定分桶里面的数据 就可以找出结果  此时是分桶扫描而不是全表扫描
select *
from t_usa_covid19_bucket where state="New York";

普通查询耗时：181ms
基于分桶字段查询耗时：175ms

9.5 好处

9.5.1 减少全表扫描

基于分桶字段state查询,不再需要进行全表扫描过滤,根据分桶的规则hash_function(New York) mod 5计算出分桶编号,查询指定分桶里面的数据 就可以找出结果 此时是分桶扫描而不是全表扫描

9.5.2 JOIN时可以提高MR程序效率，减少笛卡尔积数量

在这里插入图片描述

9.5.3 分桶表数据进行高效抽样

当数据量特别大时，对全体数据进行处理存在困难时，抽样就显得尤其重要了。抽样可以从被抽取的数据中估计和推断出整体的特性，是科学实验、质量检验、社会调查普遍采用的一种经济有效的工作和研究方法。

10 事务表（transactional）

10.1 概述

在这里插入图片描述
事务表创建几个要素：开启参数、分桶表、存储格式orc、表属性

10.2 事务配置

开启事务配置（可以使用set设置当前session生效 也可以配置在hive-site.xml中）

set hive.support.concurrency = true; --Hive是否支持并发
set hive.enforce.bucketing = true; --从Hive2.0开始不再需要 是否开启分桶功能
set hive.exec.dynamic.partition.mode = nonstrict; --动态分区模式 非严格
set hive.txn.manager = org.apache.hadoop.hive.ql.lockmgr.DbTxnManager; –
set hive.compactor.initiator.on = true; --是否在Metastore实例上运行启动线程和清理线程
set hive.compactor.worker.threads = 1; --在此metastore实例上运行多少个压缩程序工作线程。

10.3 创建事务表

create table trans_student(
   id int,
   name String,
   age int
)
clustered by (id) into 2 buckets 
stored as orc 
TBLPROPERTIES('transactional'='true');

10.4 insert、update、delete操作

insert into trans_student values(1,"allen",18);

update trans_student
set age = 20
where id = 1;

delete from trans_student where id =1;

select *
from trans_student;

11 视图（view）

11.1 概述

在这里插入图片描述
不支持删除修改
在这里插入图片描述

11.2 创建视图

--1、创建视图
create view v_usa_covid19 as select count_date, county,state,deaths from t_usa_covid19 limit 5;

--2、从已有的视图中创建视图
create view v_usa_covid19_from_view as select * from v_usa_covid19 limit 2;

11.3 显示当前已有的视图

show tables;
show views;--hive v2.2.0之后支持

11.4 视图的查询使用

select * from v_usa_covid19;

11.5 查看视图定义

show create table v_usa_covid19;

11.6 删除视图

drop view v_usa_covid19_from_view;

11.7 更改视图属性

alter view v_usa_covid19 set TBLPROPERTIES ('comment' = 'This is a view');

11.8 更改视图定义

alter view v_usa_covid19 as  select county,deaths from t_usa_covid19 limit 2;

11.9 好处

  1. 通过视图来限制数据访问可以用来保护信息不被随意查询
create table userinfo(firstname string, lastname string, ssn string, password string);

create view safer_user_info as select firstname, lastname from userinfo;
  1. 降低查询的复杂度，优化查询语句

from (
         select * from people join cart
                                   on(cart.pepople_id = people.id) where firstname = 'join'
     )a select a.lastname where a.id = 3;

--把嵌套子查询变成一个视图
create view shorter_join as
select * from people join cart
                          on (cart.pepople_id = people.id) where firstname = 'join';

--基于视图查询
select lastname from shorter_join where id = 3;

12 物化视图（materialized view）

12.1 概述

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

12.2 语法

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述## 12.3 物化视图、视图的区别
在这里插入图片描述

12.4 物化视图查询重写

在这里插入图片描述
在这里插入图片描述

12.5 案例

create table student(
    num int,
    name string,
    sex string,
    age int,
    dept string)
    row format delimited
        fields terminated by ',';
select * from student;

12.5.2 创建事务表

CREATE TABLE student_trans (
       sno int,
       sname string,
       sdept string
)
clustered by (sno) into 2 buckets stored as orc TBLPROPERTIES('transactional'='true');

12.5.3 通过普通表 向事务表导入数据

insert overwrite table student_trans
select num,name,dept
from student;

12.5.4 查询事务表（耗时20s）

SELECT sdept, count(*) as sdept_cnt from student_trans group by sdept;

在这里插入图片描述

12.5.5 创建物化视图

CREATE MATERIALIZED VIEW student_trans_agg
AS SELECT sdept, count(*) as sdept_cnt from student_trans group by sdept;

在这里插入图片描述

12.5.6 再次查询事务表，查询重写，高效查询（耗时240ms）

SELECT sdept, count(*) as sdept_cnt from student_trans group by sdept;

12.5.7 禁止物化视图自动重写，再次查询事务表

--禁用物化视图自动重写
ALTER MATERIALIZED VIEW student_trans_agg DISABLE REWRITE;
--启用物化视图自动重写
ALTER MATERIALIZED VIEW student_trans_agg ENABLE REWRITE;


SELECT sdept, count(*) as sdept_cnt from student_trans group by sdept;

禁用后，无法查询命中，查询效率低下

12.5.8 删除物化视图

drop materialized view student_trans_agg;

12.5.9 查看物化视图

show materialized views;

http://lihuaxi.xjx100.cn/news/1889686.html

相关文章

kernel(三):kernel移植

kernel(三):kernel移植

本文主要探讨210官方kernel移植。 配置文件选择 选择配置文件smdkv210_android_defconfig(arch/arm/configs) 修改主Makefile 配置cpu架构和交叉编译工具链 vim MakefileARCH ? armCROSS_COMPILE ? /root/arm-2009q3/bin/arm-none-linux-gnueabi- 初步编译烧…
阅读更多...
Nougat：结合光学神经网络，引领学术PDF文档的智能解析、挖掘学术论文PDF的价值

Nougat：结合光学神经网络，引领学术PDF文档的智能解析、挖掘学术论文PDF的价值

Nougat&#xff1a;结合光学神经网络&#xff0c;引领学术PDF文档的智能解析、挖掘学术论文PDF的价值 这是Nougat的官方存储库&#xff0c;Nougat是一种学术文档PDF解析器&#xff0c;可以理解LaTeX数学和表格。 Project page: https://facebookresearch.github.io/nougat/ …
阅读更多...
掌握Web、DNS、FTP、DHCP服务器的配置。掌握简单网络方案的规划和设计

掌握Web、DNS、FTP、DHCP服务器的配置。掌握简单网络方案的规划和设计

1、Web服务器配置 2、综合设计 配置完后，所有的终端主机都要能够访问外网服务器，并进行测试。（本题可以自行选题，自行设计，但必须包含路由器、服务器（web、dns、DHCP、）、交换机及防火墙）。 3.做好规划并搭建拓扑图： 4.给PC机与服务器配置好IP，网关 5.给每个交换机…
阅读更多...
广州旅游攻略(略说一二）

广州旅游攻略(略说一二）

广州是中国南方的一个重要城市&#xff0c;也是广东省的省会&#xff0c;拥有着悠久的历史和丰富的文化遗产。作为中国最繁华的城市之一&#xff0c;广州吸引了大量的游客前来探索其独特的魅力。今天我将为大家介绍一份广州旅游攻略&#xff0c;希望能帮助各位游客更好地了解这…
阅读更多...
C语言小游戏之三子棋（可以做期末设计作业）

C语言小游戏之三子棋（可以做期末设计作业）

哈喽大家好&#xff0c;今天为大家带来一个用C语言写的小游戏--三子棋&#xff0c;就是大家小时候用树枝和石子玩的那种游戏&#xff0c;这个小项目可以用于大家的C语言期末设计作业&#xff0c;不会很难&#xff0c;都是C语言基本的操作 下面是游戏截图&#xff1a; 完全可以…
阅读更多...
Mapreduce小试牛刀(1)

Mapreduce小试牛刀(1)

1.与hdfs一样&#xff0c;mapreduce基于hadoop框架&#xff0c;所以我们首先要启动hadoop服务器 --------------------------------------------------------------------------------------------------------------------------------- 2.修改hadoop-env.sh位置JAVA_HOME配…
阅读更多...
虚拟机元空间

虚拟机元空间

1.元空间 元空间&#xff08;Metaspace&#xff09;是在Java 8及以后的版本中替代了永久代&#xff08;Permanent Generation&#xff09;的一种新的内存区域&#xff0c;用于存储类的元数据和静态变量。 与永久代不同&#xff0c;元空间并不是在JVM的堆内存中&#xff0c;而…
阅读更多...
【Spring】02 Bean 的命名

【Spring】02 Bean 的命名

文章目录 1. 定义2. 使用优势3. 如何命名4. 注解驱动5. 最佳实践1&#xff09;使用明确的业务名词2&#xff09;避免缩写和首字母缩略词2&#xff09;不要过度使用别名 结语 在 Spring 框架中&#xff0c;Bean 是应用程序中的主要组件&#xff0c;负责承载和管理应用的核心功能…
阅读更多...
最新文章

Copyright @ 2022~2023