Hive
- 一、Hive 数据类型
- 1、基本数据类型
- 2、集合数据类型
- 3、案例实操
- 4、 类型转化
- 二、DDL 数据定义
- 1、创建数据库
- 2、查询数据库
- (1)显示数据库
- (2)查看数据库详情
- (3)切换当前数据库
- 3、修改数据库
- 4、删除数据库
- 5、创建表
- (1)管理表
- (2)外部表
- (2.1)案例实操
- (3)管理表与外部表的互相转换
- (4)建表时指定分隔符
- 6、修改表
- (1)重命名表
- (2)增加/修改/替换列信息
- (3)删除表
- 三、DML 数据操作
- 1、数据导入
- (1)向表中装载数据(Load)
- (2)通过查询语句向表中插入数据(Insert)
- (3)查询语句中创建表并加载数据(As Select)
- (4)创建表时通过 Location 指定加载数据路径
- (5)Import 数据到指定 Hive 表中
一、Hive 数据类型
1、基本数据类型
对于 Hive 的 String 类型相当于数据库的 varchar 类型,该类型是一个可变的字符串,不过它不能声明其中最多能存储多少个字符,理论上它可以存储 2GB 的字符数
2、集合数据类型
Hive 有三种复杂数据类型 ARRAY、MAP 和 STRUCT。ARRAY 和 MAP 与 Java 中的 Array 和 Map 类似,而 STRUCT 与 C 语言中的 Struct 类似,它封装了一个命名字段集合,复杂数据类型允许任意层次的嵌套。
3、案例实操
(1)假设某表有如下一行,我们用 JSON 格式来表示其数据结构。在 Hive 下访问的格式为
{
"name": "songsong",
"friends": ["bingbing" , "lili"] , //列表Array,
"children": { //键值Map,
"xiao song": 18 ,
"xiaoxiao song": 19
}
"address": { //结构Struct,
"street": "hui long guan",
"city": "beijing"
}
}
(2)基于上述数据结构,我们在 Hive 里创建对应的表,并导入数据。
创建本地测试文件 test.txt
songsong,bingbing_lili,xiao song:18_xiaoxiao song:19,hui long guan_beijing
yangyang,caicai_susu,xiao yang:18_xiaoxiao yang:19,chao yang_beijing
注意:MAP,STRUCT 和 ARRAY 里的元素间关系都可以用同一个字符表示,这里用“_”。
(3)Hive 上创建测试表 test
create table test( name string, friends array<string>,
children map<string, int>,
address struct<street:string, city:string>
)
row format delimited
fields terminated by','
collection items terminated by '_'
map keys terminated by ':'
lines terminated by '\n';
字段解释:
row format delimited fields terminated by ‘,’ – 列分隔符 collection items terminated by ‘_’ --MAP STRUCT 和 ARRAY 的分隔符(数据分割符号)
map keys terminated by ‘:’ – MAP 中的 key 与 value 的分隔符
lines terminated by ‘\n’; – 行分隔符
(4)导入文本数据到测试表
Hadooop fs -put test.txt /user/hive/warehouse/test
(5)访问三种集合列里的数据,以下分别是 ARRAY,MAP,STRUCT 的访问方式
hive (default)> select friends[1],children['xiao song'],address.city from test
where name="songsong";
4、 类型转化
- 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。
BOOLEAN 类型不可以转换为任何其它的类型。
2)可以使用 CAST 操作显示进行数据类型转换例如 CAST(‘1’ AS INT)将把字符串’1’ 转换成整数 1;如果强制类型转换失败,如执行
CAST(‘X’ AS INT),表达式返回空值 NULL。
二、DDL 数据定义
1、创建数据库
CREATE DATABASE [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];
1)创建一个数据库,数据库在 HDFS 上的默认存储路径是/user/hive/warehouse/*.db。
hive (default)> create database db_hive;
2)避免要创建的数据库已经存在错误,增加 if not exists 判断。(标准写法)
hive (default)> create database db_hive;
FAILED: Execution Error, return code 1 from
org.apache.hadoop.hive.ql.exec.DDLTask. Database db_hive already exists
hive (default)> create database if not exists db_hive;
3)创建一个数据库,指定数据库在 HDFS 上存放的位置
hive (default)> create database db_hive2 location '/db_hive2.db';
2、查询数据库
(1)显示数据库
1)显示数据库
hive> show databases;
2)过滤显示查询的数据库
hive> show databases like ‘db_hive*’;
OK
db_hive
db_hive_1
(2)查看数据库详情
1)显示数据库信息
hive> desc database db_hive;
OK
db_hive
hdfs://hadoop102:9820/user/hive/warehouse/db_hive.db
lyhUSER
2)显示数据库详细信息,extended
hive> desc database extended db_hive;
OK
db_hive
hdfs://hadoop102:9820/user/hive/warehouse/db_hive.db
lyhUSER
(3)切换当前数据库
hive (default)> use db_hive;
3、修改数据库
- 用户可以使用 ALTER DATABASE 命令为某个数据库的 DBPROPERTIES 设置键-值对属性值,
来描述这个数据库的属性信息。
hive (default)> alter database db_hive set dbproperties('createtime'='20170830');
在 hive 中查看修改结果
hive> desc database extended db_hive;
db_name comment location owner_name owner_type parameters
db_hive hdfs://hadoop102:9820/user/hive/warehouse/db_hive.db
atguigu USER {createtime=20170830}
4、删除数据库
1)删除空数据库
hive>drop database db_hive2;
2)如果删除的数据库不存在,最好采用 if exists 判断数据库是否存在
hive> drop database db_hive;
FAILED: SemanticException [Error 10072]: Database does not exist: db_hive
hive> drop database if exists db_hive2;
3)如果数据库不为空,可以采用 cascade 命令,强制删除
hive> drop database db_hive;
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. InvalidOperationException(message:Database db_hive is not empty. One or more tables exist.)
hive> drop database db_hive cascade;
5、创建表
建表语法
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
[(col_name data_type [COMMENT col_comment], ...)]
[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]
[ROW FORMAT row_format]
[STORED AS file_format]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)]
[AS select_statement] --根据查询来建表,比如A表中有十个字段,B中有五个字段都是A表中有的,就可以通过这个语句来从A中选取五个字段去建B表
字段解释说明
(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 允许用户复制现有的表结构,但是不复制数据。
(1)管理表
- 理论默认创建的表都是所谓的管理表,有时也被称为内部表。因为这种表,Hive 会(或多或少地)控制着数据的生命周期。Hive 默认情况下会将这些表的数据存储在由配置项 hive.metastore.warehouse.dir(例如,/user/hive/warehouse)所定义的目录的子目录下。
- 当我们删除一个管理表时,Hive 也会删除这个表中数据。管理表不适合和其他工具共享数据,元数据和hdfs中的都会被删掉。
(2)外部表
- 因为表是外部表,所以 Hive 并非认为其完全拥有这份数据。删除该表并不会删除掉这份数据,不过描述表的元数据信息会被删除掉,在hdfs中不会被删掉。
- 管理表和外部表的使用场景每天将收集到的网站日志定期流入 HDFS 文本文件。在外部表(原始日志表)的基础上做大量的统计分析,用到的中间表、结果表使用内部表存储,数据通过 SELECT+INSERT 进入内部表。
(2.1)案例实操
(0)原始数据
(1)上传数据到 HDFS
hive (default)> dfs -mkdir /student;
hive (default)> dfs -put /opt/module/datas/student.txt /student;
(2)建表语句,创建外部表
(3)查看创建的表
hive (default)>show tables;
(4)查看表格式化数据
hive (default)> desc formatted dept;
Table Type:
EXTERNAL_TABLE
(5)删除外部表
hive (default)> drop table dept;
外部表删除后,hdfs 中的数据还在,但是 metadata 中 dept 的元数据已被删除
(3)管理表与外部表的互相转换
(1)查询表的类型
hive (default)> desc formatted student2;
Table Type:
MANAGED_TABLE
(2)修改内部表 student2 为外部表
alter table student2 set tblproperties('EXTERNAL'='TRUE');
(3)查询表的类型
hive (default)> desc formatted student2;
Table Type:
EXTERNAL_TABLE
(4)修改外部表 student2 为内部表
alter table student2 set tblproperties('EXTERNAL'='FALSE');
(5)查询表的类型
hive (default)> desc formatted student2;
Table Type:
MANAGED_TABLE
注意:(‘EXTERNAL’=‘TRUE’)和(‘EXTERNAL’=‘FALSE’)为固定写法,区分大小写!
(4)建表时指定分隔符
如下述代码row format delimited fields terminated by ‘\t’;
以\t分割字段内容,如果要用hadoop fs -put 上传文件到表中,文件内容必须也是用\t(tab键)隔开才能在表中显示内容,不然会出现null,上传的内容也要符合字段的数据类型,不然也会出现null
create external table if not exists dept( deptno int, dname string, loc int )
row format delimited fields terminated by '\t';
6、修改表
(1)重命名表
1)语法
ALTER TABLE table_name RENAME TO new_table_name
2)实操案例
hive (default)> alter table dept_partition2 rename to dept_partition3;
(2)增加/修改/替换列信息
1)语法
(1)更新列
ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]
不能用int 替换 string(实测出来的)
(2)增加和替换列
ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)
注:ADD 是代表新增一字段,字段位置在所有列后面(partition 列前),
REPLACE 则是表示替换表中所有字段。 replace灵活运用可以有add和change的功能
2)实操案例
(1)查询表结构
hive> desc dept;
(2)添加列
hive (default)> alter table dept add columns(deptdesc string);
(3)查询表结构
hive> desc dept;
(4)更新列
hive (default)> alter table dept change column deptdesc desc string;
(5)查询表结构
hive> desc dept;
(6)替换列
hive (default)> alter table dept replace columns(deptno string, dname string, loc string);
(7)查询表结构
hive> desc dept;
(3)删除表
hive (default)> drop table dept;
三、DML 数据操作
1、数据导入
(1)向表中装载数据(Load)
1)语法
hive> load data [local] inpath '数据的path' [overwrite] into table student [partition (partcol1=val1,…)];
(1)load data:表示加载数据
(2)local:表示从本地加载数据到 hive 表;否则从 HDFS 加载数据到 hive 表 (第二种相当于剪切)
(3)inpath:表示加载数据的路径
(4)overwrite:表示覆盖表中已有数据,否则表示追加
(5)into table:表示加载到哪张表
(6)student:表示具体的表
(7)partition:表示上传到指定分区
2)实操案例
(0)创建一张表
hive (default)> create table student(id string, name string) row format delimited fields terminated by '\t';
(1)加载本地文件到 hive
hive (default)> load data local inpath
'/opt/module/hive/datas/student.txt' into table default.student;
(2)加载 HDFS 文件到 hive 中上传文件到 HDFS
hive (default)> dfs -put /opt/module/hive/data/student.txt
/user/lyh/hive;
加载 HDFS 上数据
hive (default)> load data inpath '/user/lyh/hive/student.txt' into table default.student;
(3)加载数据覆盖表中已有的数据
上传文件到 HDFS
hive (default)> dfs -put /opt/module/data/student.txt /user/lyh/hive;
加载数据覆盖表中已有的数据
hive (default)> load data inpath '/user/lyh/hive/student.txt' overwrite into table default.student;
(2)通过查询语句向表中插入数据(Insert)
1)创建一张表
hive (default)> create table student_par(id int, name string) row format delimited fields terminated by '\t';
2)基本插入数据
hive (default)> insert into table student_par values(1,'wangwu'),(2,'zhaoliu');
3)基本模式插入(根据单张表查询结果)
hive (default)> insert overwrite table student_par select id, name from student where month='201709';
insert into:以追加数据的方式插入到表或分区,原有数据不会删除
insert overwrite:会覆盖表中已存在的数据
注意:insert 不支持插入部分字段
4)多表(多分区)插入模式(根据多张表查询结果) 不常用
hive (default)> from student
insert overwrite table student partition(month='201707')
select id, name where month='201709'
insert overwrite table student partition(month='201706')
select id, name where month='201709';
(3)查询语句中创建表并加载数据(As Select)
根据查询结果创建表(查询的结果会添加到新创建的表中)
create table if not exists student3
as select id, name from student;
(4)创建表时通过 Location 指定加载数据路径
1)上传数据到 hdfs 上
hive (default)> dfs -mkdir /student;
hive (default)> dfs -put /opt/module/datas/student.txt /student;
2)创建表,并指定在 hdfs 上的位置
hive (default)> create external table if not exists student5(
id int, name string
)
row format delimited fields terminated by '\t'
location '/student';
3)查询数据
hive (default)> select * from student5;
(5)Import 数据到指定 Hive 表中
注意:先用 export 导出后,再将数据导入。
hive (default)> import table student2 from '/user/hive/warehouse/export/student';