当前位置:   article > 正文

Hive---DDL_hive ddl

hive ddl

1.hive数据类型

hive的数据类型分为两种:
1.原生数据类型:数值类型、时间类型、字符串类型、杂项数据类型。
2.复杂数据类型:array数组、map映射、struct结构、union联合体。

1.1 原生数据类型

在这里插入图片描述

1.2 复杂数据类型

在这里插入图片描述

1.3 数据类型转换

HQL支持隐式和显示转换,原生类型从窄类型到宽类型的转换称为隐式转换,反之,则不允许。
下表描述了类型之间允许的隐式转换:
在这里插入图片描述

2.Hive读写机制

2.1 SerDe

SerDe是Serializer、DeSerializer的简称,目的是用于序列化和反序列化。序列化是将对象转化为字节码的过程,序列化是将字节码转化为对象的过程。
在这里插入图片描述

2.2 hive读取文件机制

首先调用InputFileFormat(在hdfs中默认为TextInputFormat按行读取),返回一条条kv键值对记录,然后调用SerDe的Deserializer将字节序列转换为对象,然后将一条记录中的value根据分隔符切分为各个字段。

2.3 hive写文件机制

将行写入文件时,首先调用serde的Serializer将对象转换为字节序列,然后调用OutputFileFormat将数据写入HDFS文件。

2.4 SerDe语法

在这里插入图片描述LazySimpleSerDe是Hive默认的序列化类,包含四种子语法,分别用于指定字段之间、集合元素之间、Map映射kv之间、换行之间的分隔符符号。如

row format delimited fileds terminated by ',';
  • 1

字段之间默认的分隔符是’\001’,是一种特殊的字符,使用的是ascii编码的值,键盘无法打出。
在这里插入图片描述

3.Hive存储路径

Hive表默认存储路径默认值为/user/hive/warehouse

3.1 指定存储路径

在hive建表的时候,可以通过location语法来更改数据在HDFS上的路径,使得建表加载数据更加灵活。

4.Hive建表高阶

4.1 hive内部表、外部表

内部表(Internal table)也称为被Hive拥有和管理的托管表(Managed table)。默认情况下创建的表就是内部表,Hive拥有该表的结构和文件。换句话说,Hive完全管理表(元数据和数据)的生命周期,类似于RDBMS中的表。当您删除内部表时,它会删除数据以及表的元数据。

外部表(External table)中的数据不是Hive拥有或管理的,只管理表元数据的生命周期。要创建一个外部表,需要使用EXTERNAL语法关键字。删除外部表只会删除元数据,而不会删除实际数据。在Hive外部仍然可以访问实际数据。而且外部表更为方便的是可以搭配location语法指定数据的路径。
外部表创建:使用external进行修饰

create external table student_ext(
    num int,
    name string,
    sex string,
    age int,
    dept string)
row format delimited
fields terminated by ','
location '/stu';

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10

在这里插入图片描述

4.2 分区表

现有一张表t_all_hero,同时映射加载了六份数据文件(分别记录了王者荣耀中六种位置的英雄的相关信息)。
如果不对表进行分区,那么在执行查询的时候,会对全表进行检索才能得出相应的结果,如果数据文件很多的话,则会大大降低查询效率,此时可以通过对表进行分区,避免查询时候全表扫描数据,极大的提升了查询效率。此外,hive支持根据用户指定的字段进行分区。
在这里插入图片描述
分区建表语法:

CREATE TABLE table_name (column1 data_type, column2 data_type) PARTITIONED BY (partition1 data_type, partition2 data_type,….);
  • 1
4.2.1 静态分区

静态分区:指的是分区的字段值是由用户在加载数据的时候手动指定的。
语法如下:

load data [local] inpath ' ' into table tablename partition(分区字段='分区值'...);
eg:
load data local inpath '/root/hivedata/archer.txt' into table t_all_hero_part partition(role='sheshou');
load data local inpath '/root/hivedata/assassin.txt' into table t_all_hero_part partition(role='cike');
  • 1
  • 2
  • 3
  • 4
4.2.2 动态分区

向分区表中加载数据时,如果需要创建的分区很多,则会复制修改很多sql去执行,效率低。因为hive是批处理系统,所以hive提供了一个动态分区功能,即根据查询参数的位置去推断分区的名称,从而建立分区
动态分区:分区的字段值是根据查询结果自动推断出来的。
启动hive分区之前,需要在hive会话中设置两个参数:

//开启动态分区功能
set hive.exec.dynamic.partition=true;
//指定动态分区的模式,nonstrict为非严格模式和strict严格模式
set hive.exec.dynamic.partition.mode=nonstrict;
  • 1
  • 2
  • 3
  • 4

严格模式要求至少有一个分区为静态分区

案例:

//创建一张新的分区表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";
//执行动态分区插入,tmp.*表示插入的总数据,tmp.role_main表示分区值,即查询参数的位置
insert into table t_all_hero_part_dynamic partition(role) select tmp.*,tmp.role_main from t_all_hero tmp;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16

注意:tmp.*和tmp.role_main位置不可颠倒

4.2.3 分区表的注意事项

一、 分区表不是建表的必要语法规则,是一种优化手段表,可选;
二、 分区字段不能是表中已有的字段,不能重复;
三、 分区字段是虚拟字段,其数据并不存储在底层的文件中;
四、 分区字段值的确定来自于用户价值数据手动指定(静态分区)或者根据查询结果位置自动推断(动态分区)
五、 Hive支持多重分区,也就是说在分区的基础上继续分区,划分更加细粒度

4.2.4 多重分区

多重分区下,分区之间是一种递进关系,可以理解为在前一个分区的基础上继续分区。从HDFS的角度来看就是文件夹下继续划分子文件夹。比如:把全国人口数据首先根据省进行分区,然后根据市进行划分,如果你需要甚至可以继续根据区县再划分,此时就是3分区表。

//双分区表,按省份和市分区
create table t_user_province_city 
(id int, name string,
age int) 
partitioned by (province string, city string);

load data local inpath '文件路径' into table t_user_province_city
partition(province='zhejiang',city='hangzhou');
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
4.3 分桶表

分桶表也叫做桶表,源自建表语法中bucket单词。是一种用于优化查询而设计的表类型。该功能可以让数据分解为若干个部分易于管理。
在分桶时,我们要指定根据哪个字段将数据分为几桶(几个部分)。默认规则是:Bucket number = hash_function(bucketing_column) mod num_buckets。
可以发现桶编号相同的数据会被分到同一个桶当中。hash_function取决于分桶字段bucketing_column的类型:
如果是int类型,hash_function(int) == int;
如果是其他类型,比如bigint,string或者复杂数据类型,hash_function比较棘手,将是从该类型派生的某个数字,比如hashcode值。

4.3.1 分桶表的语法
CREATE [EXTERNAL] TABLE [db_name.]table_name
[(col_name data_type, ...)]
CLUSTERED BY (col_name)
INTO N BUCKETS;
  • 1
  • 2
  • 3
  • 4

其中CLUSTERED BY (col_name)表示根据哪个字段进行分;INTO N BUCKETS表示分为几桶(也就是几个部分)。需要注意的是,分桶的字段必须是表中已经存在的字段。

4.3.2 分桶表的数据加载
//开启分桶的功能 从Hive2.0开始不再需要设置
set hive.enforce.bucketing=true;

//把源数据加载到普通hive表中
CREATE TABLE itcast.t_usa_covid19(
       count_date string,
       county string,
       state string,
       fips int,
       cases int,
       deaths int)
row format delimited fields terminated by ",";
//将源数据上传到HDFS,t_usa_covid19表对应的路径下
hadoop fs -put us-covid19-counties.dat /user/hive/warehouse/itcast.db/t_usa_covid19

//使用insert+select语法将数据加载到分桶表中
insert into t_usa_covid19_bucket select * from t_usa_covid19;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18

分桶表将数据分为了五个部分:
在这里插入图片描述

4.3.3 分桶表的好处

1、基于分桶字段查询时,减少全表扫描
2、JOIN时可以提高MR程序效率,减少笛卡尔积数量
3、分桶表数据进行抽样

4.4 事务表

hive中通过事务解决以下问题:
1.流式传输数据
使用如Apache Flume或Apache Kafka之类的工具将数据流式传输到Hadoop集群中。虽然这些工具可以每秒数百行或更多行的速度写入数据,但是Hive只能每隔15分钟到一个小时添加一次分区。频繁添加分区会很快导致表中大量的分区。因此通常使用这些工具将数据流式传输到现有分区中,但是这会使读者感到脏读(也就是说,他们将在开始查询后看到写入的数据),并将许多小文件留在目录中,这将给NameNode带来压力。通过事务功能,同时允许读者获得一致的数据视图并避免过多的文件。
2.尺寸变化缓慢(例如:表结构变化)
在典型的星型模式数据仓库中,维度表随时间缓慢变化。例如,零售商将开设新商店,需要将其添加到商店表中,或者现有商店可能会更改其平方英尺或某些其他跟踪的特征。这些更改导致插入单个记录或更新 记录(取决于所选策略)。
3.数据重述
有时发现收集的数据不正确,需要更正。从Hive 0.14开始,可以通过INSERT,UPDATE和 DELETE支持这些用例 。

hive中的事务的限制:
 尚不支持BEGIN,COMMIT和ROLLBACK。所有语言操作都是自动提交的。
 仅支持ORC文件格式(STORED AS ORC)。
 默认情况下事务配置为关闭。需要配置参数开启使用。
 表必须是分桶表(Bucketed)才可以使用事务功能。
 表参数transactional必须为true;
 外部表不能成为ACID表,不允许从非ACID会话读取/写入ACID表。

4.4.1 操作事务表
--Hive中事务表的创建使用
--1、开启事务配置(可以使用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实例上运行多少个压缩程序工作线程。

--2、创建Hive事务表
create table trans_student(
    id int,
    name String,
    age int
)clustered by (id) into 2 buckets stored as orc TBLPROPERTIES('transactional'='true');

--3、针对事务表进行insert update delete操作
insert into trans_student (id, name, age)
values (1,"allen",18);

update trans_student
set age = 20
where id = 1;

delete from trans_student where id =1;

//查询事务表
select *
from trans_student;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
4.5 Hive View

Hive中的视图(view)是一种虚拟表,只保存定义,不实际存储数据。通常从真实的物理表查询中创建生成视图,也可以从已经存在的视图上创建新视图。
创建视图时,将冻结视图的架构,如果删除或更改基础表,则视图将失败,并且视图不能存储数据,操作数据,只能查询。

4.5.1 操作视图
--hive中有一张真实的基础表t_usa_covid19
select *
from itcast.t_usa_covid19;

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

--能否从已有的视图中创建视图呢  可以的
create view v_usa_covid19_from_view as select * from v_usa_covid19 limit 2;

--2、显示当前已有的视图 
show tables;
show views;--hive v2.2.0之后支持

--3、视图的查询使用
select *
from v_usa_covid19;

--能否插入数据到视图中呢?
--不行 报错  SemanticException:A view cannot be used as target table for LOAD or INSERT
insert into v_usa_covid19 select count_date,county,state,deaths from t_usa_covid19;

--4、查看视图定义
show create table v_usa_covid19;

--5、删除视图
drop view v_usa_covid19_from_view;
--6、更改视图属性
alter view v_usa_covid19 set TBLPROPERTIES ('comment' = 'This is a view');
--7、更改视图定义
alter view v_usa_covid19 as  select county,deaths from t_usa_covid19 limit 2;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
4.5.2 视图的优点

1.提高数据的安全性,只将特定列的数据展示给用户
2.降低查询的复杂度,优化查询语句

4.6 物化视图

在传统的数据库领域基本已经都实现了物化视图, 属于数据库的高级功能。物化视图(Materialized View)是一个包括查询结果的数据库对像,可以用于预先计算并保存表连接或聚集等耗时较多的操作的结果。这样,在执行查询时,就可以避免进行这些耗时的操作,而从快速的得到结果。

4.6.1 物化视图和视图的区别

视图是虚拟的,逻辑存在的,只有定义没有存储数据。
物化视图是真实的,物理存在的,里面存储着预计算的数据。
视图的目的是简化降低查询的复杂度,而物化视图的目的是提高查询性能。

4.6.2 语法
--物化视图的创建语法
CREATE MATERIALIZED VIEW [IF NOT EXISTS] [db_name.]materialized_view_name
    [DISABLE REWRITE]
    [COMMENT materialized_view_comment]
    [PARTITIONED ON (col_name, ...)]
    [CLUSTERED ON (col_name, ...) | DISTRIBUTED ON (col_name, ...) SORTED ON (col_name, ...)]
    [
    [ROW FORMAT row_format]
    [STORED AS file_format]
    | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]
  ]
  [LOCATION hdfs_path]
  [TBLPROPERTIES (property_name=property_value, ...)]
AS SELECT ...;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
4.6.3 基于物化视图的查询重写

物化视图创建后即可用于相关查询的加速,用户提交查询query,若该query经过重写后可命中已建视图,则被重写命中相关已建视图实现查询加速。
是否重写查询使用物化视图可以通过全局参数控制,默认为true:

SET hive.materializedview.rewriting=true;
  • 1

用户可选择性的失能物化视图的重写:

ALTER MATERIALIZED VIEW [db_name.]materialized_view_name ENABLE|DISABLE REWRITE;
  • 1
声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/盐析白兔/article/detail/855445
推荐阅读
相关标签
  

闽ICP备14008679号