Hive 操作基础（进阶篇☝️）

Hive 入门操作

创建表

EXTERNAL，创建外部表
PARTITIONED BY， 分区表
CLUSTERED BY，分桶表
STORED AS，存储格式
LOCATION，存储位置
......，

数据类型

内部表

创建普通内部表: create table [if not exists] 表名(字段名字段类型 , 字段名字段类型...) [row format delimited fields terminated by '指定分隔符'];

删除内部表: drop table 内部表名; 注意: 删除mysql中元数据同时也会删除hdfs中存储数据

修改表名: alter table 旧表名 rename to 新表名;

修改表字段名称和类型: alter table 表名 change 旧字段名新字段名新字段类型;

修改表之添加字段(列): alter table 表名 add columns (字段名字段类型);

修改表之替换字段(列):alter table 表名 replace columns (字段名字段类型);

查看所有表: show tables;

查看指定表基本信息: desc 表名;

查看指定表扩展信息: desc extended 表名;

查看指定表格式信息: desc formatted 表名;

查看指定表建表语句: show create table 表名;

-- 创建内部表方式1: create table if not exists 表名(字段名 字段类型,字段名 字段类型);
create table if not exists stu (id int,name string,age int);-- 查看表信息
desc stu; -- 查看列数据.
desc formatted stu; -- 查看表信息. Table Type: MANAGED_TABLE [内部表]-- 方式2: 复制表结构
create table if not exists stu2 like stu;
desc formatted stu2; -- 查看表信息. todo Table Type: MANAGED_TABLE [内部表]-- 方式3: 复制表结构和数据
-- 为了演示复制数据,临时插入数据到stu表
insert into stu values(1,'tom',18),(2,'jerry',18),(3,'rese',18),(4,'jack',18);
create table stu3 as select * from stu;-- truncate 清空表数据
truncate table stu3;
select * from stu3; -- 空空如也-- 删除表 drop -- 内部表会删除元数据和hdfs上的文件数据.
drop table stu;
select * from stu;-- Table not found 'stu' [元数据库(mysql)中没有记录stu表]

外部表

创建外部表:
create external table [if not exists] 内部表名(
字段名字段类型 , ...
)...;

复制外部表: create table 表名 as select 语句;
复制表结构: create table 表名 like 存在的表名;

删除外部表: drop table 外部表名;

查看表格式化信息: desc formatted 表名;

注意: 外部表不能使用truncate关键字清空数据

use db1;-- 创建外部表
create external table if not exists t_user (id int,name string,age int);-- 查看表结构
desc formatted t_user; -- todo Table Type: EXTERNAL_TABLE-- 添加数据给t_user
insert into t_user values(1,'tom',18),(2,'jerry',18),(3,'rese',18),(4,'jack',18);-- 查询表数据
select * from t_user;-- 方式2: 复制表结构
create external table if not exists t_user2 like t_user;
desc formatted t_user2;
select * from t_user2;-- 方式3: 复制表结构和数据
-- create external table if not exists t_user3 as select * from t_user;-- create - table - as - select不能创建外部表-- 清空表
-- truncate table t_user; -- Cannot truncate non-managed table t_user.-- 删除表
drop table t_user;-- 元数据被删除.但是hdfs上的数据还在.
select * from t_user; -- Table not found 't_user'

Hive 进阶操作

内部表

-- 创建表
create table stu(id int ,name string,age int);-- 插入数据
insert into stu values (1,'tom',18),(2,'jerry',19);-- 查看数据
select * from stu;-- 复制表结构: CREATE TABLE 表名 like 存在的表名;
create table stu2 like stu;
desc stu2;-- 复制表结构和数据: CREATE TABLE 表名 as select语句;
create table stu3 as select * from stu;
select * from stu3;-- 删除内部表: drop table 内部表名;
drop table stu3; -- 数据也会被删除-- 查看表格式化信息:desc formatted 表名;
desc formatted stu2;-- truncate清空内部表数据: truncate table 内部表名;
truncate table stu;

外部表

create external table teacher(id int ,name string,age int) location '/teacher';-- 插入数据
insert into teacher values (1,'张三',18),(2,'李四',19);-- 复制表结构: CREATE TABLE 表名 like 存在的表名;
create table teacher2 like teacher; -- 复制外部表 不加 external 结果还是内部表
create external table teacher3 like teacher; -- 复制外部表 加 external 结果是外部表.-- 复制表结构: CREATE TABLE 表名  as sql语句;
create table teacher4 as select * from teacher;
select * from teacher4;-- 删除外部表: drop table 外部表名;
drop table teacher;
select * from teacher; -- 表已经被删了.但是表对应的数据还在.-- 查看表格式化信息: desc formatted 表名;
desc formatted teacher2;-- 注意: 外部表不能使用truncate关键字清空数据
truncate table teacher3; --Cannot truncate non-managed table teacher3

查看表

show tables;               : -- 查看所有
show create table 表名;     : -- 查看建表语句
desc 表名;                  :-- 查看表中的列
desc extended 表名;         :-- 查看表的详细信息--一行展示的.不够美观
desc formated 表名;         :-- 查看表的详细信息--格式化展示.

修改表

字段的添加: 
alter table 表名 add columns (字段名 字段类型);
字段的替换: 
alter table 表名 replace columns (字段名 字段类型 , ...);
字段名和字段类型同时修改: 
alter table 表名 change 旧字段名 新字段名 新字段类型;
注意: 字符串类型不能直接改数值类型修改表名: 
alter table 旧表名 rename to 新表名;
修改表路径: 
alter table 表名 set location 'hdfs中存储路径';            
注意: 建议使用默认路径
修改表属性: 
alter table 表名 set tblproperties ('属性名'='属性值');  
alter table stu set tblproperties('EXTERNAL'='TRUE');#必须大写-- 外部表
alter table stu set tblproperties('EXTERNAL'='FALSE');#必须大写 --内部表

分隔符

默认分隔符:
经过查看发现我们建表不指定分隔符.hive会在我们插入数据的时候默认使用\001作为分隔符.

指定分隔符:
格式: create table 表名 (列名...) row format delimited fields terminated by '指定分隔符';

指定分隔符建表可以快速映射文件数据.

create table product(id int,name string,price int,category string) row format delimited fields terminated by ',';
-- 加载数据--(把数据文件放置在product表目录下)select * from product; -- 可以查询到product.txt文件中的所有数据.

文件导入

方式1(页面操作): 在hdfsweb页面,直接上传windows文件到指定表的location路径下。

方式2(put上传): 在linux命令行,输入hdfs dfs -put /Linux本地文件路径 /HDFS的指定表的location路径下。

方式3(load加载): 在hive客户端上,输入load data local inpath '/linux本地文件路径' into table 表名;

方式4(load加载): 在hive客户端上,输入load data inpath '/HDFS的文件路径' into table 表名;

注意: 方式4本质就是把'/HDFS的文件路径'下的文件移动到 /HDFS的指定表的location路径下

方式5(insert插入): insert [overwrite | into] table 表名 select 语句;

注意: 方式5如果不加overwrite就是追加写入数据,如果加了overwrite就是覆盖原有数据

数据导出

方式1(页面操作): 在hdfsweb页面,直接下载指定hdfs文件,到windows文件系统。

方式2(get下载): 在linux命令行,输入hdfs dfs -get /HDFS的指定表的location路径下 /Linux本地文件路径。

方式3(覆盖导出到linux): insert overwrite local directory '/Linux本地文件路径' select 语句;

方式4(覆盖导出到hdfs): insert overwrite directory '/HDFS文件路径' select 语句;

方式5(执行sql语句重定向到文件): hive -e sql语句 > 文件 #跳过登录直接运行sql命令

方式6(执行sql脚本重定向到文件): hive -f sql脚本 > 文件 #跳过登录直接运行sql脚本

导出至Windows

导出至Linux

使用insert 导出至Linux

# 把表导出到linux目录中.分隔符默认
insert overwrite local directory '/opt/export1' select * from products; # 把表导出到linux目录中.分隔符指定为逗号.
insert overwrite local directory '/opt/export2' row format delimited fields terminated by ',' select * from products;

覆盖导出到hdfs

# 不加local就是导出到hdfs上
insert overwrite directory '/export1' select * from products;

执行sql语句重定向到文件

hive命令在linux执行:
# hive -e 'sql' : 免登录执行sql语句.
# 把查询结果重定向到文件中
[root@node1 export2]# hive -e 'select * from db1.products' > /opt/export3.txt

执行sql脚本重定向到文件

# hive -f sql脚本文件 : 免登录执行sql脚本
[root@node1 opt]# hive -f myhive.sql > products4.txt