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