Hive表基本操作_1

Hive表操作

一、Hive表操作

6、外部表

知识点:

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

复制表: 方式1: like方式复制表结构    注意: as方式不可以使用

删除外部表: drop table 外部表名;
	注意: 删除外部表效果是mysql中元数据被删除,但是存储在hdfs中的业务数据本身被保留
	
查看表格式化信息: desc formatted 表名; -- 外部表类型: EXTERNAL_TABLE

注意: 外部表不能使用truncate清空数据本身

总结: 外部表对HDFS上的业务数据的管理权限并不高，drop表不会删除业务数据，同时不能使用truncate和delete来删除表数据。我们可以通过HDFS的shell来删除业务数据

示例:

-- 创建数据库
create database if not exists day06;

-- 使用数据库
use day06;

-- 创建外部表
create external table outer_stu1(
    id int,
    name string
);

-- 添加数据
insert into outer_stu1 values (1,'zhangshan');

-- Hive底层对部分SQL语句进行了优化，不会变成MapReduce
select * from outer_stu1;

-- 创建外部表的方式2
-- 注意: 不管是什么方式创建外部表，一定要加上external关键字
create external table outer_stu2 like outer_stu1;
desc formatted outer_stu2;

-- 大小写转换快捷键: ctrl+shift+U
create EXTERNAL table outer_stu3 like outer_stu1;
desc formatted outer_stu3;

-- 这种方式创建的还是内部表
create table stu2 like outer_stu1;
-- 查看表的详细信息
desc formatted stu2;


-- 创建外部表的方式3
-- 注意: 针对外部表，不能使用create external table 外部表名 as select 来创建
create external table outer_stu4 as select * from outer_stu1;


-- 删除表
-- 该表的数据存放路径 hdfs://node1:8020/user/hive/warehouse/day06.db/outer_stu1
-- HDFS的路径中为什么是node1，因为namenode运行在node1上面
drop table outer_stu1;

-- 清空表
insert into outer_stu3 values (1,'zhangshan');
select * from outer_stu3;
-- truncate table outer_stu3;
delete from outer_stu3;
update outer_stu3 set name='wangwu';
select * from outer_stu3;


-- 创建数据库
create database if not exists day06;

-- 使用数据库
use day06;

-- 创建外部表
create external table outer_stu1(
    id int,
    name string
);

-- 添加数据
insert into outer_stu1 values (1,'zhangshan');

-- Hive底层对部分SQL语句进行了优化，不会变成MapReduce
select * from outer_stu1;

-- 创建外部表的方式2
-- 注意: 不管是什么方式创建外部表，一定要加上external关键字
create external table outer_stu2 like outer_stu1;
desc formatted outer_stu2;

-- 大小写转换快捷键: ctrl+shift+U
create EXTERNAL table outer_stu3 like outer_stu1;
desc formatted outer_stu3;

-- 这种方式创建的还是内部表
create table stu2 like outer_stu1;
-- 查看表的详细信息
desc formatted stu2;


-- 创建外部表的方式3
-- 注意: 针对外部表，不能使用create external table 外部表名 as select 来创建
create external table outer_stu4 as select * from outer_stu1;


-- 删除表
-- 该表的数据存放路径 hdfs://node1:8020/user/hive/warehouse/day06.db/outer_stu1
-- HDFS的路径中为什么是node1，因为namenode运行在node1上面
drop table outer_stu1;

-- 清空表
insert into outer_stu3 values (1,'zhangshan');
select * from outer_stu3;
-- truncate table outer_stu3;
delete from outer_stu3;
update outer_stu3 set name='wangwu';
select * from outer_stu3;

快速创建外部表不支持的操作：

注意: 针对外部表，不能使用create external table 外部表名 as select 来创建

清空外部表的时候遇到的错误：

原因: 不能使用truncate语句来清空外部表
解决办法: 可以使用delete from 外部表名称。但是有前提条件，需要开启表对事务的支持（了解）

如果执行delete会报如下错误:

原因: 对表数据使用delete进行删除的时候，需要先开启事务
注意: 在公司中，默认不会去开启Hive对事务的支持，事务开启后比较消耗性能。https://cwiki.apache.org/confluence/display/Hive/Hive+Transactions

7、查看和修改表

知识点:

查看所有表: show tables;
查看建表语句: show create table 表名;
查看表信息: desc 表名;
查看表结构信息: desc 表名; 
查看表格式化信息: desc formatted 表名;            注意: formatted能够展示详细信息

修改表名: alter table 旧表名 rename to 新表名
字段的添加: alter table 表名 add columns (字段名 字段类型);
字段的替换: alter table 表名 replace columns (字段名 字段类型 , ...);
替换的时候注意: 替换的时候，是使用新的字段信息替换原有的所有字段。也就是如果某些字段不想变化，你也需要把它写到替换的信息后面。

字段名和字段类型同时修改: alter table 表名 change 旧字段名 新字段名 新字段类型;
	注意: 字符串类型不能直接改数值类型，这句话是有方向的。也就是字符串不能随便变成数值，但是数值可以变成字符串。举例："hello world"变成数值的时候，Hive内部是不知道它对应的数值是多少；123 可以变成 "123"字符串
	
修改表路径: alter table 表名 set location 'hdfs中存储路径';             
注意: 建议使用默认路径
location:  建表的时候不写有默认路径/user/hive/warehouse/库名.db/表名,当然建表的时候也可以直接指定路径

修改表属性: alter table 表名 set tblproperties ('属性名'='属性值');     注意: 经常用于内外部表切换
内外部表类型切换:  外部表属性: 'EXTERNAL'='true'   内部表属性: 'EXTERNAL'='false'
注意: 属性中的EXTERNAL名称不能随意改动，必须与Hive官网保持一致。

表支持的属性: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-listTableProperties

示例:

use day06;

-- 创建内部表和外部表
create table inner_stu (
    id int,
    name string
);


create external table outer_stu (
    id int,
    name string
);

-- 添加数据
insert into inner_stu values(1,'zhangshan');
insert into outer_stu values(1,'zhangshan');

-- 查看当前数据库下面的所有表
show tables;

-- 查看建表语句
show create table inner_stu;
show create table outer_stu;

-- 不管是查看内部表还是外部表的建表语句，都是show create table 表名称的语法
-- show create external table outer_stu;

-- 查看表的详细信息
desc inner_stu;
desc formatted inner_stu;

-- 表字段的操作
-- 表添加字段（列名是同一个意思）
alter table inner_stu add columns(age int);

desc inner_stu;

-- 替换表中的字段
-- 替换指的是将表中原有的所有字段都替换
alter table outer_stu replace columns(age int);
alter table outer_stu replace columns(id int,age int);
alter table outer_stu replace columns(id int,age int,name string);
-- 注意：字符串类型的字段不能随便直接改成数值类型。
alter table outer_stu replace columns(id int,name string,age int);
alter table outer_stu replace columns(id int,name string,age string);
desc outer_stu;

select * from outer_stu;

-- 同时修改字段名称和数据类型
alter table outer_stu change age new_age varchar(10);
-- 注意：字符串类型的字段不能随便直接改成数值类型。
-- 其中的解决办法：重新建一张表，然后把旧表的数据全部插入到新表里面去
alter table outer_stu change new_age age int;
desc outer_stu;


-- 表的修改操作
-- 修改表名称
alter table inner_stu rename to my_inner_stu;

-- 修改表数据存放路径
-- 注意: 不推荐修改，就使用默认路径
desc formatted my_inner_stu;
alter table my_inner_stu set location '/dir/inner_stu';
desc formatted my_inner_stu;

-- 添加数据
insert into my_inner_stu values(1,'zhangshan',18);

select * from my_inner_stu;
desc formatted my_inner_stu;

-- 修改表属性
-- 内外部表相互转换
-- 内部表 -> 外部表
alter table my_inner_stu set tblproperties ('EXTERNAL'='true');

desc formatted my_inner_stu;


-- 外部表 -> 内部表
desc formatted outer_stu;
alter table outer_stu set tblproperties ('EXTERNAL'='false');
desc formatted outer_stu;

修改表路径前后对比：

show create table中可能遇到的问题：

原因: 不管是查看内部表还是外部表的建表语句，都是show create table 表名称的语法

原因: 字符串类型的字段不能随便变成数值类型

8、快速映射表

知识点:

创建表的时候指定分隔符: create [external] table 表名(字段名 字段类型)row format delimited fields terminated by 符号;

加载数据: load data [local] inpath '文件路径' into table Hive表名称

示例:

HDFS示例：

use day06;

-- 1- 创建表
create table jd_products(
    id int,
    name string,
    price float,
    c_id string
)row format delimited fields terminated by ',';


-- 2- 数据上传到HDFS中
-- hdfs dfs -put products.txt /day06

-- 3- 加载前先检查表数据
select * from jd_products;

-- 4- 将HDFS中的数据加载到Hive表中
load data inpath '/day06/products.txt' into table jd_products;

-- 5- 数据验证
select * from jd_products;

本地映射示例：

use day06;

-- 1- 创建表
create table jd_products_local(
    id int,
    name string,
    price float,
    c_id string
)row format delimited fields terminated by ',';


-- 2- 加载前先检查表数据
select * from jd_products_local;

-- 4- 将本地中的数据加载到Hive表中
-- 推举使用从HDFS上面将数据加载到Hive
load data local inpath '/home/products.txt' into table jd_products_local;

-- 5- 数据验证
select * from jd_products_local;

二、数据导入和导出

1、文件数据导入

1.1 直接上传文件

• window页面上传

需求: 已知emp1.txt文件在windows/mac系统,要求使用hdfs保存此文件
并且使用hivesql建表关联数据

use day06;

-- 1- 创建Hive表
create table emp1 (
    id int,
    name string,
    salary int,
    dept string
)row format delimited fields terminated by ',';

-- 2- 通过浏览器界面将数据上传到HDFS

-- 3- 将HDFS上的数据文件加载到Hive中
load data inpath '/emp1_dir' into table emp1;

-- 4- 数据验证
select * from emp1;


-- load数据的特殊演示
-- 1- 建表
create table emp111 (
    id int,
    name string,
    salary int,
    dept string
)row format delimited fields terminated by ',';

-- 2- 通过HDFS的shell命令移动/复制数据文件到表的目录下
-- hdfs dfs -cp /user/hive/warehouse/day06.db/emp1/emp1.txt /user/hive/warehouse/day06.db/emp111/emp2.txt
select * from emp111;

• linux本地put上传

需求: 已知emp2.txt文件在linux系统,要求使用hdfs保存此文件
并且使用hivesql建表关联数据

use day06;

-- 1- 创建Hive表
create table emp2 (
    id int,
    name string,
    salary int,
    dept string
)row format delimited fields terminated by ',';

-- 2- 通过命令或者界面将windows上的文件先上传到linux
-- rz

-- 3- 通过命令linux上的文件上传到HDFS，并且上传到表数据所在的目录
-- hdfs dfs -put emp2.txt /user/hive/warehouse/day06.db/emp2
-- hdfs dfs -ls /user/hive/warehouse/day06.db/emp2

-- 4- 验证数据
select * from emp2;

1.2 load加载文件

从hdfs路径把文件移动到表对应存储路径中: load data inpath '文件路径' [overwrite] into table 表名称;

从linux本地把文件上传到表对应存储路径中: load data local inpath '文件路径' [overwrite] into table 表名称;

• load移动HDFS文件

use day06;

-- 创建Hive表
-- \t表示的是制表符
create table search_log(
    dt string,
    uid string,
    name string,
    url string
)row format delimited fields terminated by '\t';

-- HDFS文件演示
-- 将windows本地文件上传到HDFS的非Hive表所在的目录
load data inpath '/dir/search_log.txt' into table search_log;
select * from search_log;

• load上传Linux文件

-- Linux本地文件演示
load data local inpath '/home/search_log.txt' into table search_log;
select * from search_log;

• load上传Linux文件并且使用overwrite（覆盖）

  -- Linux本地文件演示，并且带上overwrite
  -- overwrite效果：先清空表中的原有数据，然后是新数据填充
  load data local inpath '/home/search_log.txt' overwrite into table search_log;
  select * from search_log;
  

  

1.3 insert插入数据

从其他表查询数据'追加'插入到当前表中: insert into table 表名 select查询语句;

从其他表查询数据'覆盖'插入到当前表中: insert overwrite table 表名 select查询语句;

• insert追加数据

use day06;

-- 创建Hive表
-- \t表示的是制表符
create table search_log_copy(
    dt string,
    uid string,
    name string,
    url string
)row format delimited fields terminated by '\t';

select * from search_log_copy;

-- 通过insert select 语句加载其他表中的数据到当前表中
insert into table search_log_copy select * from search_log;

select * from search_log_copy;

• insert覆盖数据

-- insert overwrite覆盖数据
insert overwrite table search_log_copy select * from search_log;

select * from search_log_copy;

总结:
1- 如果文件就在windows上面，可以通过直接上传文件的方式
2- 如果文件在linux操作系统上面，可以选择直接上传文件或者load加载文件
3- 如果我们是需要从其他表中将数据复制到我自己的表中，可以使用insert插入数据

2、文件数据导出

2.1 直接下载文件

• web页面下载

需求: 已知search_log.txt文件在HFDS的/user/hive/warehouse/day06.db/search_log路径下,要下载到window系统

• get命令下载文件

需求: 已知search_log.txt文件在HFDS的/user/hive/warehouse/day06.db/search_log路径下,要下载到linux系统

[root@node1 home]# hdfs dfs -get /user/hive/warehouse/day06.db/search_log/search_log.txt .

2.2 insert导出数据

查询数据导出到hdfs其他路径: insert overwrite directory 'HDFS路径' select语句；

查询数据导出到linux本地中: insert overwrite local directory 'Linux路径' select语句；

注意:
	1- overwrite会覆盖掉路径中已有的文件，千万注意。推荐指定一个新的空目录
	2- 如果不指定分隔符，导出的文件中使用默认的Hive分隔符\001

导出数据指定分隔符添加(以HDFS为例): 
insert overwrite directory '/dir'
row format delimited fields terminated by ','
select * from search_log;

• insert导出到hdfs

use day06;

-- 将Hive表数据导出到HDFS的路径下
-- overwrite：会覆盖指定目录中文件
insert overwrite directory '/dir' select * from search_log;

-- 指定分隔符
insert overwrite directory '/dir'
row format delimited fields terminated by ','
select * from search_log;

• insert导出linux

-- 将Hive表数据导出到Linux的路径下
insert overwrite local directory '/home'
row format delimited fields terminated by ','
select * from search_log;

2.3 hive_shell命令

hive命令执行sql语句: hive -e "Hive 语句">存储该结果数据的Linux文件路径

hive命令执行sql脚本: hive -f hivesql文件>存储该结果数据的Linux文件路径

• hql语句导出

hive -e "select * from day06.search_log">/home/1.txt

• hql脚本导出（推荐）

[root@node1 home]# cat my_sql.sql 
select * from day06.search_log

hive -f my_sql.sql > /home/2.txt

• 总结

  1- 如果SQL语句比较简单，SQL的行数在3行以内，可以使用hive -e

  2- 如果SQL语句比较复杂，推荐使用hive -f

2.4 总结

1- 如果数据在Hive表的某一个文件中，可以使用直接下载文件的方式
2- 如果想将Hive表中的数据导出到HDFS路径，推荐使用insert overwrite导出命令
3- 如果只是想将Hive表中的数据导出到linux路径，可以使用insert overwrite导出命令或者hive sell命令

三、分区表

1、介绍

特点: 分区表会在HDFS上产生目录。查询数据的时候使用分区字段筛选数据，可以避免全表扫描，从而提升查询效率
注意: 如果是分区表，在查询数据的时候，如果没有使用分区字段，它回去进行全表扫描，会降低效率

只需要记住一点，分区表是用来提升Hive的数据分析效率

2、一级分区

知识点:

创建分区表: create [external] table [if not exists] 表名称(字段名称1 字段数据类型,字段名称2 字段数据类型..) partitioned by (分区字段 字段数据类型);

自动生成分区目录并插入数据: load data [local] inpath '文件路径' into table 表名称 partition (分区字段=值);

注意: 如果使用load导入数据，没有写local，文件路径就是HDFS上的路径。否则就是linux的路径

示例:

use day06;

-- 1- 创建分区表
create table one_part_tb(
    id int,
    name string,
    price double,
    num int
) partitioned by (year int)
row format delimited fields terminated by ' ';


-- 2- 通过load将HDFS中的文件导入到Hive表中
load data inpath '/source/order202251.txt' into table one_part_tb partition (year=2022);
load data inpath '/source/order202351.txt' into table one_part_tb partition (year=2023);
load data inpath '/source/order202352.txt' into table one_part_tb partition (year=2023);
load data inpath '/source/order2023415.txt' into table one_part_tb partition (year=2023);

-- 3- 数据验证
select * from one_part_tb;

-- 4- 使用分区
select * from one_part_tb where year=2022;

-- 5- 如果没有指定分区，那么会进行全表扫描，拖慢了效率
select * from one_part_tb where price>=20;

3、多级分区

知识点:

创建分区表: create [external] table [if not exists] 表名称(字段名称1 字段数据类型,字段名称2 字段数据类型..) partitioned by (分区字段1 字段数据类型,分区字段2 字段数据类型...);

自动生成分区目录并插入数据: load data [local] inpath '文件路径' into table 表名称 partition (分区字段1=值,分区字段2=值....);

注意: 如果使用load导入数据，没有写local，文件路径就是HDFS上的路径。否则就是linux的路径

示例:

use day06;

-- 1- 创建多级分区表
create external table multi_pat_tb(
    id int,
    name string,
    price double,
    num int
) partitioned by (year string,month string,day string)
row format delimited fields terminated by ' ';

-- 2- 加载HDFS数据到Hive表中
load data inpath '/source/order202251.txt' into table multi_pat_tb partition (year="2022",month="5",day="1");
load data inpath '/source/order202351.txt' into table multi_pat_tb partition (year="2023",month="5",day="1");
load data inpath '/source/order202352.txt' into table multi_pat_tb partition (year="2023",month="5",day="2");
load data inpath '/source/order2023415.txt' into table multi_pat_tb partition (year="2023",month="4",day="15");

-- 3- 数据验证
select * from multi_pat_tb;

-- 4- 使用分区
-- 注意: 如果是多分区，使用分区来提升效率的时候，需要根据需求来决定到底使用几个分区。并不需要所有的分区都用到
-- 需求：要对2023全年的销售情况进行分析
select * from multi_pat_tb where year="2023";
-- 需求：要对2023年5月整个月的销售情况进行分析
select * from multi_pat_tb where year="2023" and month="5";
select * from multi_pat_tb where year="2023" and month="5" and day="2";

-- 5- 不使用分区
select * from multi_pat_tb where price>=20;

4、分区操作

知识点:

添加分区: alter table 分区表名 add partition (分区字段1=值,分区字段2=值..);

删除分区: alter table 分区表名 drop partition (分区字段1=值,分区字段2=值..);

修改分区名: alter table 分区表名 partition (分区字段1=旧分区值,分区字段2=旧分区值..) rename to partition (分区字段1=新分区值,分区字段2=新分区值..);

查看所有分区: show partitions 分区表名;

同步/修复分区: msck repair table 分区表名;

注意: 如果删除内部表的分区，那么对应的HDFS分区目录也被删除了；如果删除外部表的分区，那么对应的HDFS分区目录还保留着

示例:

use day06;

-- 查询表的分区信息
show partitions one_part_tb;
show partitions multi_pat_tb;

-- 添加分区
alter table one_part_tb add partition (year=2024);
-- 如果是多级分区，那么添加分区的时候，需要将所有的分区都添加上
alter table multi_pat_tb add partition (year="2024");
alter table multi_pat_tb add partition (year="2024",month="1",day="1");

-- 修改分区
alter table one_part_tb partition (year=2024) rename to partition (year=2000024);

-- 删除分区
-- 注意：如果删除内部表的分区，那么对应的分区目录也被删除了；如果删除外部表的分区，那么对应的HDFS分区目录还保留着
alter table one_part_tb drop partition (year=2000024);
alter table multi_pat_tb drop partition (year="2024",month="1",day="1");

-- 修复分区
-- 在执行下面的语句之前，需要手动去/user/hive/warehouse/day06.db/one_part_tb路径下创建一个year=2025分区目录
msck repair table one_part_tb;

给多级分区表添加分区遇到的错误：

原因: 如果是多级分区，那么添加分区的时候，需要将所有的分区都添加上

修改分区效果：