Hive学习笔记：03Hive的数据类型与HiveQL的常用操作

wangzhongyudie

已于 2022-08-03 09:22:59 修改

阅读量411

点赞数

分类专栏：大数据 Hive 文章标签： hive 学习 hadoop

于 2022-07-12 00:16:05 首次发布

本文链接：https://blog.csdn.net/wangzhongyudie/article/details/125734263

版权

大数据同时被 2 个专栏收录

47 篇文章 2 订阅

订阅专栏

Hive

4 篇文章 0 订阅

订阅专栏

概述

Hive的SQL“方言被称为”HiveQL，他是SQL-92、MYSQL和Oracle SQL语言的混合体。所以很多操作都和传统的数据库的操作非常类似，尤其是MySQL。

Hive的数据类型

简单数据类型

HiveQL类型	描述	补充说明
TINYINT	1字节有符号整数-128~127	1Y
SMALLINT	2字节有符号整数，-32768~32767	1S
INT	4字节有符号整数	1
BIGINT	8字节的有符号整数	1L
BOOLEAN	布尔类型true/false
FLOAT	4字节单精度浮点数	1.f
DOUBLE	8字节双精度浮点数
STRING	变长字符串	类似于mysql的text
VARCHAR	变长字符串	VARCHAR(2)
CHAR	定长字符串	CHAR(2)
BINARY	二进制字节数组
TIMESTAMP	时间戳，纳秒精度
DECIMAL(precision,scale)	任意精度的带符号小数	精度为0则表示整数
DATE	日期类型

Hive的基本类型和MySQL数据库的类型基本一致，但是也有些细微的区别：

STRING存储变长的文本，对长度没有限制。理论上将STRING可以存储的大小为2GB，但是存储特别大的对象时效率可能受到影响，可以考虑使用Sqoop提供的大对象支持。
VARCHAR与STRING类似，但是长度上只允许在1-65355之间。例如VARCHAR(100).
CHAR则用固定长度来存储数据。
TIMESTAMP存储的是时间戳，精度是纳秒

复杂数据类型

HiveQL类型	描述	补充说明	使用
ARRAY	数组类型	数组的元素必须是同类型的数据	ARRAY
MAP	键值对类型	Map的Key必须是简单数据类型，值可以是任意类型	MAP<STRING,INT>
STRUCT	结构体类型	类似于C语言中的Struct	STRUCT(a:STRING,b:INT)
UNION	联合	只能是一组数据中的一个	UNIONTYPE(STRING,INT,BOOLEAN)

复杂数据类型定义表格


CREATE TABLE TEST(
    COLUMN1 ARRAY<INT>,
    COLUMN2 MAP<STRING,INT>,
    COLUMN3 STRUCT<name:STRING,age:INT>,
    COLUMN4 UNIONTYPE<STRING,INT>
)

HiveQL

数据库DATABASE/SCHEMA

Hive中也有数据库的概念，使用HiveQL的操作如下：

创建数据库

语法：

CREATE [REMOTE] (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
  [COMMENT database_comment]
  [LOCATION hdfs_path]
  [MANAGEDLOCATION hdfs_path]
  [WITH DBPROPERTIES (property_name=property_value, ...)];

示例

create database zealink;
create schema zealink;
create database if not exists zealink;

删除数据库

语法：

DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];

示例：

drop database zealink;
drop schema zealink;
drop database if exists zealink;

查看数据库

show databases;

切换数据库

语法：
use + 数据库名称
示例：

use zealink;

表的DDL操作

创建表

语法：

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name    -- (Note: TEMPORARY available in Hive 0.14.0 and later)
  [(col_name data_type [column_constraint_specification] [COMMENT col_comment], ... [constraint_specification])]
  [COMMENT table_comment]
  [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
  [CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
  [SKEWED BY (col_name, col_name, ...)                  -- (Note: Available in Hive 0.10.0 and later)]
     ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)
     [STORED AS DIRECTORIES]
  [
   [ROW FORMAT row_format] 
   [STORED AS file_format]
     | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]  -- (Note: Available in Hive 0.6.0 and later)
  ]
  [LOCATION hdfs_path]
  [TBLPROPERTIES (property_name=property_value, ...)]   -- (Note: Available in Hive 0.6.0 and later)
  [AS select_statement];   -- (Note: Available in Hive 0.5.0 and later; not supported for external tables)

row_format 行的格式解析定义

row_format
  : DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char]
        [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
        [NULL DEFINED AS char]   -- (Note: Available in Hive 0.13 and later)
  | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]

行的格式解析由几个部分组成：

定义字段之间分隔字符
定义Collection的元素之间的分隔字符，主要是Array，UnionType，MAP的值的元素等数据类型
定义行的分隔字符

file_format 文件格式的定义，默认是TEXTFILE

  : SEQUENCEFILE
  | TEXTFILE    -- (Default, depending on hive.default.fileformat configuration)
  | RCFILE      -- (Note: Available in Hive 0.6.0 and later)
  | ORC         -- (Note: Available in Hive 0.11.0 and later)
  | PARQUET     -- (Note: Available in Hive 0.13.0 and later)
  | AVRO        -- (Note: Available in Hive 0.14.0 and later)
  | JSONFILE    -- (Note: Available in Hive 4.0.0 and later)
  | INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname

指定约束列

column_constraint_specification:
  : [ PRIMARY KEY|UNIQUE|NOT NULL|DEFAULT [default_value]|CHECK  [check_expression] ENABLE|DISABLE NOVALIDATE RELY/NORELY ]

设定默认值

default_value:
  : [ LITERAL|CURRENT_USER()|CURRENT_DATE()|CURRENT_TIMESTAMP()|NULL ]

constraint_specification:
  : [, PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE RELY/NORELY ]
    [, PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE RELY/NORELY ]
    [, CONSTRAINT constraint_name FOREIGN KEY (col_name, ...) REFERENCES table_name(col_name, ...) DISABLE NOVALIDATE 
    [, CONSTRAINT constraint_name UNIQUE (col_name, ...) DISABLE NOVALIDATE RELY/NORELY ]
    [, CONSTRAINT constraint_name CHECK [check_expression] ENABLE|DISABLE NOVALIDATE RELY/NORELY ]

创建普通表

创建一个含有Array，Struct，MAP等复杂数据类型的数据表

有一个文件student.txt，它的内容内容如下：

1000001,huangwei,32,羽毛球-篮球-乒乓球-足球,homeAddress:湖北省武汉市东湖高新区-CompanyAddress:湖北省武汉市慧谷时空,lisi-34
1000002,lisi,32,羽毛球-篮球-乒乓球-足球,homeAddress:湖北省武汉市东湖高新区-CompanyAddress:湖北省武汉市慧谷时空,wangwu-36
1000003,wangwu,12,羽毛球-篮球-乒乓球-足球,homeAddress:湖北省武汉市东湖高新区-CompanyAddress:湖北省武汉市慧谷时空,zhaosi-34
1000004,huangwei,22,羽毛球-篮球-乒乓球-足球,homeAddress:湖北省武汉市东湖高新区-CompanyAddress:湖北省武汉市慧谷时空,zhangsan-45
1000005,hangwei2,36,羽毛球-篮球-乒乓球-足球,homeAddress:湖北省武汉市东湖高新区-CompanyAddress:湖北省武汉市慧谷时空,wangmazi-18
1000006,huanwei,58,羽毛球-篮球-乒乓球-足球,homeAddress:湖北省武汉市东湖高新区-CompanyAddress:湖北省武汉市慧谷时空,fengzi-65

建表sql

针对该文件创建student表，来解析此文件，建表SQL语句如下：

CREATE TABLE student(
    id bigint comment "ID",
    name STRING comment "姓名",
    age SMALLINT comment "年龄",
    likes ARRAY<STRING> comment "爱好",
    address MAP<STRING,STRING> comment "地址",
    friend STRUCT<name:STRING,age:INT> comment "好友"
)
ROW format DELIMITED
    FIELDS TERMINATED BY ','  //字段之间以','分割
    COLLECTION ITEMS TERMINATED BY '-'  //容器的元素之间以'-'分割
    MAP KEYS TERMINATED BY ':'   //MAP的键值对之间以':'分割
    LINES TERMINATED BY '\n'     //行与行之间以'\n'分割
;

创建好了student表之后，可以进行导入数据

导入数据

语法：LOAD DATA [LOCAL] INPATH ‘文件路径’ INTO TABLE + tablename

LOCAL

是本地文件系统，如果从本地文件系统导入数据到HIVE中时，需要加LOCAL关键字，如果不加LOCAL，默认是从hdfs的文件系统导入数据。

eg:从本地文件系统，将数据导入到hive的student表中

hive> LOAD DATA LOCAL INPATH '/home/data/student.txt' INTO table student;
Loading data to table default.student
OK
Time taken: 0.787 seconds
hive>

查询数据

数据导入完毕后，可以查询表：

hive> select * from student;
OK
1000001	huangwei	32	["羽毛球","篮球","乒乓球","足球"]	{"homeAddress":"湖北省武汉市东湖高新区","CompanyAddress":"湖北省武汉市慧谷时空"}{"name":"lisi","age":34}
1000002	lisi	32	["羽毛球","篮球","乒乓球","足球"]	{"homeAddress":"湖北省武汉市东湖高新区","CompanyAddress":"湖北省武汉市慧谷时空"}	{"name":"wangwu","age":36}
1000003	wangwu	12	["羽毛球","篮球","乒乓球","足球"]	{"homeAddress":"湖北省武汉市东湖高新区","CompanyAddress":"湖北省武汉市慧谷时空"}	{"name":"zhaosi","age":34}
1000004	huangwei	22	["羽毛球","篮球","乒乓球","足球"]	{"homeAddress":"湖北省武汉市东湖高新区","CompanyAddress":"湖北省武汉市慧谷时空"}{"name":"zhangsan","age":45}
1000005	hangwei2	36	["羽毛球","篮球","乒乓球","足球"]	{"homeAddress":"湖北省武汉市东湖高新区","CompanyAddress":"湖北省武汉市慧谷时空"}{"name":"wangmazi","age":18}
1000006	huanwei	58	["羽毛球","篮球","乒乓球","足球"]	{"homeAddress":"湖北省武汉市东湖高新区","CompanyAddress":"湖北省武汉市慧谷时空"}	{"name":"fengzi","age":65}
NULL	NULL	NULL	NULL	NULL	NULL
Time taken: 0.166 seconds, Fetched: 7 row(s)

查询Map类型的指定Key的值

使用列名[key]来查询MAP类型的值

hive> select address["homeAddress"] from student;
OK
湖北省武汉市东湖高新区
湖北省武汉市东湖高新区
湖北省武汉市东湖高新区
湖北省武汉市东湖高新区
湖北省武汉市东湖高新区
湖北省武汉市东湖高新区
NULL
Time taken: 0.193 seconds, Fetched: 7 row(s)

查询Struct类型的字段信息

使用字段名+"."属性名来查询结构体对象的属性字段值

hive> select friend.name,friend.age from student;
OK
lisi	34
wangwu	36
zhaosi	34
zhangsan	45
wangmazi	18
fengzi	65

查询Array

使用下标来访问数组类型的数据的值，如果下标越界，则返回NULL

hive> select likes[0],likes[1],likes[2] from student;
OK
羽毛球	篮球	乒乓球
羽毛球	篮球	乒乓球
羽毛球	篮球	乒乓球
羽毛球	篮球	乒乓球
羽毛球	篮球	乒乓球
羽毛球	篮球	乒乓球

创建分区表

创建分区表DDL

CREATE TABLE student2(
    ID BIGINT,
    NAME STRING,
    AGE SMALLINT
)
PARTITIONED BY (TYPE STRING COMMENT "类别");

创建分区表，分区字段不能在创建表的sql语句的主体中出现，否则会出现字段重复的错误
可以指定多级分区，如PARTITIONED BY (TYPE STRING,GRADE STRING)

分区表导入数据

方式一：通过查询其他表的方式

FROM STUDENT
INSERT OVERWRITE table student2 partition(TYPE="P2")
SELECT ID,NAME, AGE;

方式二：导入文件的方式(静态分区)

LOAD DATA LOCAL INPATH '/home/data/student2.txt' INTO TABLE student2 PARTITION(type="P2");

以上两种方式导入的数据都是静态分区，需要手工指定导入到哪个分区中，但是很多时候一个大的数据文件中含有各种数据，我们不可能都一个个手工指定导入到某个分区，所以就需要动态创建分区

方式三：数据动态分区

FROM STUDENT
INSERT OVERWRITE table student2 partition(TYPE)
SELECT ID,NAME, AGE TYPE DISTRIBUTE BY TYPE

使用SELECT 的方式创建表

CREATE TABLE STUDENT4 AS SELECT * FROM STUDENT;

创建临时表

语法和创建普通表语法一样

CREATE TEMPORARY TABLE(
   ID STRING,
   NAME STRING
);

TIPS:临时表在会话终止退出的时候就会被删掉，不会永久保存。

删除表Drop TABLE

drop table if exists student2;

截断表

truncate table student2;

修改表

重命名表

alter table partion_table_test rename to student2;

重命名表

alter table partion_table_test rename to student2;

修改表注释

alter table student2 set tblproperties ('comment'='student2');

添加列

alter table student2 add columns( col4 STRING);

修改列

修改列名

语法：CHANGE 列名新的列名字段类型

alter table student2 change id classid string;

修改列的数据类型

Alter table student change classid varchar(20);

修改列在表中的位置

hive> desc student2;
OK
clsid               	string              	                    
name                	string              	                    
age                 	int                 	                    
col4                	string              	                    
type                	string              	??                  
	 	 
# Partition Information	 	 
# col_name            	data_type           	comment             
type                	string              	??                  
Time taken: 0.113 seconds, Fetched: 9 row(s)
hive> alter table student2 change clsid  clsid string  after name;
OK
Time taken: 0.202 seconds
hive> desc student2;
OK
name                	string              	                    
clsid               	string              	                    
age                 	int                 	                    
col4                	string              	                    
type                	string              	??                  
	 	 
# Partition Information	 	 
# col_name            	data_type           	comment             
type                	string              	??                  
Time taken: 0.093 seconds, Fetched: 9 row(s)

修改列注释

hive> alter table student2 change clsid  clsid string comment 'test change comment';
OK
Time taken: 0.203 seconds
hive> desc student2;
OK
name                	string              	                    
clsid               	string              	test change comment 
age                 	int                 	                    
col4                	string              	                    
type                	string              	??                  
	 	 
# Partition Information	 	 
# col_name            	data_type           	comment             
type                	string              	??                  
Time taken: 0.094 seconds, Fetched: 9 row(s)

删除列

alter table student2 drop columns

添加分区

//内部表添加分区
ALTER TABLE STUDENT2 ADD PARTITION(TYPE="P1");
//外部表添加分区
ALTER TABLE STUDENT2 ADD PARTITION(TYPE="P1") LOCATION '/root/data/part10000'

Tips:

只能针对分区表执行添加分区操作，如果是普通表则会报错
如果需要将普通表更改为分区表，则需要先将普通表删掉，重新创建分区表
如果需要更改分区的字段，则需要先删掉所有的分区，重新指定分区字段

删除分区

alter table student2 drop if exists partition(type="student");
alter table student2 drop  partition(type="student");

更改分区数据存放路径

alter table student2 partition(type=“student”) set location “new location”;

alter table student2 partition(type="P4") set location "/root/data/student2/P2";

wangzhongyudie

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
Hive学习笔记：03Hive的数据类型与HiveQL的常用操作

Hive的SQL“方言被称为”HiveQL，他是SQL-92、MYSQL和Oracle SQL语言的混合体。所以很多操作都和传统的数据库的操作非常类似，尤其是MySQL。Hive的基本类型和MySQL数据库的类型基本一致，但是也有些细微的区别：HiveQL数据库DATABASE/SCHEMAHive中也有数据库的概念，使用HiveQL的操作如下：示例删除数据库语法：示例：查看数据库切换数据库语法：use + 数据库名称示例：表的DDL操作创建表语法：
复制链接

扫一扫