一、数据类型
1.基本数据类型
Hive数据类型 | Java数据类型 | 长度 | 例子 |
TINYINT | byte | 1byte有符号整数 | 20 |
SMALINT | short | 2byte有符号整数 | 20 |
INT | int | 4byte有符号整数 | 20 |
BIGINT | long | 8byte有符号整数 | 20 |
BOOLEAN | boolean | 布尔类型,true或者false | TRUE FALSE |
FLOAT | float | 单精度浮点数 | 3.14159 |
DOUBLE | double | 双精度浮点数 | 3.14159 |
STRING | string | 字符系列。可以指定字符集。可以使用单引号或者双引号。 | ‘now is the time’ “for all good men” |
TIMESTAMP | 时间类型 | ||
BINARY | 字节数组 |
对于Hive的String类型相当于数据库的varchar类型,该类型是一个可变的字符串,不过它不能声明其中最多能存储多少个字符,理论上它可以存储2GB的字符数。
2.集合数据类型
数据类型 | 描述 | 语法示例 |
STRUCT | 和c语言中的struct类似,都可以通过“点”符号访问元素内容。例如,如果某个列的数据类型是STRUCT{first STRING, last STRING},那么第1个元素可以通过字段.first来引用。 | struct() 例如struct<street:string, city:string> |
MAP | MAP是一组键-值对元组集合,使用数组表示法可以访问数据。例如,如果某个列的数据类型是MAP,其中键->值对是’first’->’John’和’last’->’Doe’,那么可以通过字段名[‘last’]获取最后一个元素 | map() 例如map<string, int> |
ARRAY | 数组是一组具有相同类型和名称的变量的集合。这些变量称为数组的元素,每个数组元素都有一个编号,编号从零开始。例如,数组值为[‘John’, ‘Doe’],那么第2个元素可以通过数组名[1]进行引用。 | Array() 例如array<string> |
Hive有三种复杂数据类型ARRAY、MAP 和 STRUCT。ARRAY和MAP与Java中的Array和Map类似,而STRUCT与C语言中的Struct类似,它封装了一个命名字段集合,复杂数据类型允许任意层次的嵌套。
3.类型转化
Hive的原子数据类型是可以进行隐式转换的,类似于Java的类型转换,例如某表达式使用INT类型,TINYINT会自动转换为INT类型,但是Hive不会进行反向转化,例如,某表达式使用TINYINT类型,INT不会自动转换为TINYINT类型,它会返回错误,除非使用CAST操作。
1.隐式类型转换规则如下
(1)任何整数类型都可以隐式地转换为一个范围更广的类型,如TINYINT可以转换成INT,INT可以转换成BIGINT。
(2)所有整数类型、FLOAT和STRING类型都可以隐式地转换成DOUBLE。
(3)TINYINT、SMALLINT、INT都可以转换为FLOAT。
(4)BOOLEAN类型不可以转换为任何其它的类型。
2.可以使用CAST操作显示进行数据类型转换
例如CAST('1' AS INT)将把字符串'1' 转换成整数1;如果强制类型转换失败,如执行CAST('X' AS INT),表达式返回空值 NULL。
select '1'+2, cast('1'as int) + 2;
二、 DDL
1.创建数据库
CREATE DATABASE [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];
1)创建一个数据库,数据库在HDFS上的默认存储路径是
2.)创建一个数据库,指定数据库在HDFS上存放的位置
create database db_hive2 location '/db_hive2.db';
2.查询数据库
1)显示数据库
show databases ;
2)过滤显示查询的数据库
show databases like 't*';
3.查看数据库详情
1)显示数据库信息
desc database default;
2)显示数据库详细信息,extended
desc database extended test;
3)切换当前数据库
use test;
4.修改数据库
ALTER DATABASE命令为某个数据库的DBPROPERTIES设置键-值对属性值,来描述这个数据库的属性信息。数据库的其他元数据信息都是不可更改的,包括数据库名和数据库所在的目录位置。
ALTER DATABASE test set DBPROPERTIES ("111"="222","444"="555");
在hive中查看修改结果
desc database extended test;
5.删除数据库
1)删除空数据库
drop test2;
2)如果删除的数据库不存在,最好采用 if exists判断数据库是否存在
drop database if exists test2;
3)如果数据库不为空,可以采用cascade命令,强制删除
drop database test2 cascade;
6.创建表
1)建表语法
1.CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
#创建一个指定名字的表。IF NOT EXISTS 选项来忽略表已存在异常。
#EXTERNAL关键字可以让用户创建一个外部表,在建表的同时可以指定一个指向实际数据的路径(LOCATION),在删除表的时候,内部表的元数据和数据会被一起删除,而外部表只删除元数据,不删除数据。
2.[(col_name data_type [COMMENT col_comment], ...)] COMMENT:为表和列添加注释。
[COMMENT table_comment]
3.[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] #PARTITIONED BY创建分区表
4.[CLUSTERED BY (col_name, col_name, ...) #CLUSTERED BY创建分桶表
5.[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] #SORTED BY不常用,对桶中的一个或多个列另外排序
6.[ROW FORMAT row_format] #列分隔符
7.[STORED AS file_format] #指定文件存储格式
8.[LOCATION hdfs_path] #指定表在hdfs的存储路径
9.[TBLPROPERTIES (property_name=property_value, ...)] #表的属性
10.[AS select_statement] #用select结果来创建一个表
2)字段解释说明
(1)CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在,则抛出异常;用户可以用 IF NOT EXISTS 选项来忽略这个异常。
(2)EXTERNAL关键字可以让用户创建一个外部表,在建表的同时可以指定一个指向实际数据的路径(LOCATION),在删除表的时候,内部表的元数据和数据会被一起删除,而外部表只删除元数据,不删除数据。
(3)COMMENT:为表和列添加注释。
(4)PARTITIONED BY创建分区表
(5)CLUSTERED BY创建分桶表
(6)SORTED BY不常用,对桶中的一个或多个列另外排序
(7)ROW FORMAT
DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
| SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]
用户在建表的时候可以自定义SerDe或者使用自带的SerDe。如果没有指定ROW FORMAT 或者ROW FORMAT DELIMITED,将会使用自带的SerDe。在建表的时候,用户还需要为表指定列,用户在指定表的列的同时也会指定自定义的SerDe,Hive通过SerDe确定表的具体的列的数据。
SerDe是Serialize/Deserilize的简称, hive使用Serde进行行对象的序列与反序列化。
(8)STORED AS指定存储文件类型
常用的存储文件类型:SEQUENCEFILE(二进制序列文件)、TEXTFILE(文本)、RCFILE(列式存储格式文件)
如果文件数据是纯文本,可以使用STORED AS TEXTFILE。如果数据需要压缩,使用 STORED AS SEQUENCEFILE。
(9)LOCATION :指定表在HDFS上的存储位置。
(10)AS:后跟查询语句,根据查询结果创建表。
(11)LIKE允许用户复制现有的表结构,但是不复制数据。
7.管理表
1)普通创建表
create table test
(id int comment "ID",name string comment "Name")
comment "Test Table"
row format delimited fields terminated by '\t' #表的分列条件
location "/xxx" #指定hdfs下映射的名字及路径
tblproperties ("aaa"="bbb");
2)查看表的详细信息
desc formatted test;
# col_name | data_type | comment |
id | int | ID |
name | string | Name |
# Detailed Table Information | ||
Database: | test | |
OwnerType: | USER | |
Owner: | atguigu | |
CreateTime: | Fri Mar 11 17:42:55 CST 2022 | |
LastAccessTime: | UNKNOWN | |
Retention: | 0 | |
Location: | hdfs://hadoop102:8020/xxx | |
Table Type: | MANAGED_TABLE | |
Table Parameters: | ||
COLUMN_STATS_ACCURATE | {\"BASIC_STATS\":\"true\",\"COLUMN_STATS\":{\"id\":\"true\",\"name\":\"true\"}} | |
aaa | bbb | |
bucketing_version | 2 | |
comment | Test Table | |
numFiles | 0 | |
numRows | 0 | |
rawDataSize | 0 | |
totalSize | 0 | |
transient_lastDdlTime | 1646991775 | |
# Storage Information | ||
SerDe Library: | org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe | |
InputFormat: | org.apache.hadoop.mapred.TextInputFormat | |
OutputFormat: | org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat | |
Compressed: | No | |
Num Buckets: | -1 | |
Bucket Columns: | [] | |
Sort Columns: | [] | |
Storage Desc Params: | ||
field.delim | \t | |
serialization.format | \t |
3)重命名表
alter table test rename to test2;
4)增加/修改/替换列信息
(0)更新列信息
alter table test2 change id id string;
(1)增加一列或多列
alter table test2 add columns (class string comment "class.NO");
(2)重写列信息
alter table test2 replace columns (id double comment"ID",name string);
5)删除表
drop table test2;
6)用查询结果建表
create table stu_result as select * from stu_par where id=1001;
8.内外部表
1)外部表
当创建表的时候使用external关键字时就会建立一张外部表
create external table if not exists test(id int,name string);
hive只对表的元数据有管理权限,而对这张表的数据没有管理权限,数据的生命不由hive决定,在删表的时候只会删除掉表的元数据,对数据本身没有影响。
内部表向外部表转换
alter table test set tblproperties ("EXTERNAL"="TRUE");
外部表转换为内部表
alter table test set tblproperties ("EXTERNAL"="FALSE");
9.分区表
1.1)建立分区表
create table stu_par
(id int,name string)
partitioned by (class string)
row format delimited fields terminated by '\t';
1.2)向表中插入数据
#hdfs
load data inpath '/data/student.txt' [overwrite] into table stu_par partition(class='03');
#本地
load data [local] inpath '/opt/module/dates/student.txt' into table stu_par partition(class='01');
1.3)查表时,选择发分区,可以减少数据扫描量
select * from stu_par where class="01";
select * from stu_par where id=1001;
1.4)查询分区表的分区
show partitions stu_par;
1.5)如果提前准备数据,但是没有元数据,修复方式
(1)添加分区
alter table stu_par add partition (class="03");
(2)命令行直接修复
msck repair table stu_par;
(3)上传上传时带分区
load data local inpath '/opt/module/dates/student.txt' into table stu_par partition(class='01');
2.1)建立二级分区表
create table stu_par2
(id int,name string)
partitioned by (grade string, class string)
row format delimited fields terminated by '\t';
2.2)插入数据,指定到二级分区
load data local inpath '/opt/module/dates/student.txt' into table stu_par2 partition(grade='01',class='03');
3.分区的增删改查
3.1)增加分区
alter table stu_par add partition(class="05");
3.2)一次增加多个分区
alter table stu_par add partition (class="06") partition(class="07");
3.3)删除分区
alter table stu_par drop partition (class="05");
3.4)删除多个分区
alter table stu_par drop partition (class="06"),partition (class="07");