hive基本命令

创建内外部表：

 外部表(非正式员工)：

1、先创建表再上传数据：

首先检查：hadoop fs -ls /tmp，确认不存在/tmp/test_ext1目录

创建外部表：create external table test_ext1(id int, name string) row format delimited fields terminated by ‘\t’ location ‘/tmp/test_ext1’;

可以看到，目录/tmp/test_ext1被创建

select * from test_ext1，空结果，无数据

上传数据： hadoop fs -put test_external.txt /tmp/test_ext1/

查询出数据：select * from test_ext1

-- 上传的数据必须和当前你创建表使用分隔符一致

2、先上传数据再创建表 :

创建目录：hadoop fs -mkdir /tmp/test_ext2

上传数据：hadoop fs -put test_external.txt /tmp/test_ext2/

创建表：

create external table test_ext2(id int, name string) row format delimited fields terminated by ‘\t’ location ‘/tmp/test_ext2’;

查询出数据：select * from test_ext2;
-- 上传的数据必须和当前你创建表使用分隔符一致

注：外部表的数据和元数据单独存在,外部表删除后,数据本身(hdfs中)存在,元数据(mysql中)被删除

 内部表(正式员工)：

create table stu2(id int ,name string) row format delimited fields terminated by '\t';

-- 不加external默认创建的是内部表

注：内部表的数据和元数据不独立,内部表删除后数据本身(hdfs中)和元数据(mysql中)全部删除

内外部表转换：

查看表类型：desc formatted hive.test_ext1;

转外部表： alter table hive.test_ext1 set tblproperties('EXTERNAL'='TRUE');

转内部表：alter table hive.test_ext1 set tblproperties('EXTERNAL'='FALSE');

加载和导出数据

load方式导入数据：

 sql语句方式导入数据：

INSERT [OVERWRITE | INTO] TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] select_statement1 FROM from_statement;

-- [OVERWRITE|INTO][覆盖|追加]

将SELECT查询语句的结果插入到其它表中，被SELECT查询的表可以是内部表或外部表。

sql语句导出数据：

insert overwrite [local] directory ‘path’ select_statement1 FROM from_statement;

-- [local]本地,不加则hdfs目录中

执行sql语句或sql脚本导出数据（ssh工具中操作）：

hive安装目录：bin/hive -e "select * from myhive.test_load;" > /home/hadoop/export3/export4.txt

hive安装目录：bin/hive -f export.sql > /home/hadoop/export4/export4.txt

分区表：

 

分区表：把表放在若干个文件夹中,使用文件夹筛选出数据。

修改和删除分区：

修改分区：

-- (内部表(正式员工)：数据本身和元数据非独立,元数据修改后数据本身也会修改

-- 外部表(非正式员工)：数据本身和元数据相互独立,元数据修改不影响数据本身)

alter table myhive.score2 partition(year='2023',month='06',day='11')rename to partition (year='2023',month='06',day='12');

删除分区：

-- (内部表(正式员工)：元数据删除后数据本身也会删除

-- 外部表(非正式员工)：数据本身和元数据相互独立,元数据删除不影响数据本身)

alter table myhive.score2 drop partition(year='2021',month='01',day='01') ;

分桶表：

开启分桶优化：set mapreduce.job.queuename=max;

创建分桶表：

create table course (c_id string,c_name string,t_id string) clustered by(c_id) into 3 buckets row format delimited fields terminated by '\t';

加载分桶表数据：

insert overwrite table myhive.course select * from myhive.course cluster by (c_id);

清空表(清空内部表):
TRUNCATE table score2;

分桶表：将表中的记录,对字段进行hash取模运算,分批存储到固定数量的桶中

即同一个分桶文件存放一批相同的字段数据,进而提高hive表的性能特性

hive复杂类型 :

 array类型：

-- 复杂数据类型
-- array数组类型
create table myhive.test_array(
	name string,
	work_location array<string>
)
row format delimited fields terminated by "\t" -- 字段分隔符
collection items terminated by ","; -- 元素分隔符
load data local inpath '/root/test_array_data.txt' overwrite into table myhive.test_array;
select * from myhive.test_array;
-- 查询数组元素
select name,work_location[0] location from myhive.test_array;
-- 查询数组个数
select name,size(work_location) as work_location_size from myhive.test_array;
-- 查询数组中包含‘beijing’的信息
select name,work_location from myhive.test_array where array_contains(work_location,'beijing')
alter table myhive.test_array change location work_location array<string>;

map类型： 

-- map集合类型
create table myhive.test_map(
	id int,
	name string,
	members map<string,string>,
	age string
)
row format delimited fields terminated by "," -- 字段分隔符
collection items terminated by '#' -- 键值对分隔符
map keys terminated by ':'; -- k、v分隔符
load data local inpath '/root/test_map_data.txt' overwrite into table myhive.test_map;
select * FROM myhive.test_map;
-- 获取每条记录指定的key的value值
select id,name,members['father'] as father,members ['sister'] as sister from myhive.test_map;
-- 获取全部的key值作为array返回
select id,name,map_keys(members) as members_count from myhive.test_map;
-- 获取全部value值作为array返回
select id,name,map_values(members) as members_count from myhive.test_map;
-- 统计键值对个数
select id,name,size(members) as members_count from myhive.test_map;
-- 查询value中包含美美的记录
select * from myhive.test_map where array_contains(map_values(members),"美美");
-- 查询key中包含'brother'的记录
select * from myhive.test_map where array_contains(map_keys(members),"brother")

struct类型： 

-- struct类型
create table myhive.test_sturct(
	id int,
	info struct<name:string,age:string>
)
row format delimited fields terminated by "#" -- 字段分隔符
collection items terminated by ":" -- 二级列struct分隔符
load data local inpath "/root/testDate/test_struct_data.txt" overwrite into table myhive.test_sturct;
-- 直接查看struct类型的字段info,是一个object对象。
select id,info.name,info.age from myhive.test_sturct;

正则表达式： 

-- 查找广东省的数据(rlike '.*广东.*'相当于like '%广东%')
select * from itheima.orders where useraddress rlike '.*广东.*';
-- 查找用户地址是：**省**市**区的数据
select * from itheima.orders where useraddress rlike '..省 ..市 ..区';
-- 查找用户姓为张、王、邓
select * from itheima.orders where username rlike '[张王邓]\\S+'
-- 查找手机号符合：188****0***规则 userphone rlike '188[0-9]{4}0[0-9]{3}'
select * from itheima.orders where userphone rlike '188\\S{4}0\\S{3}'
-- 查找邮箱202****142@qq.com
select * from itheima.orders where email rlike '[0-9]{10}@[a-z]{2}.com'

union联合查询：

-- 默认去重复,
select * from itheima.course
	union
select * from itheima.course

-- all 不去重
select * from itheima.course
	union all
select * from itheima.course

-- 将union查询的内容作为子表,分组查询
select name,count(*) from 
	(select * from itheima.course where name = '周杰轮'
		union all
	select * from itheima.course  where name = '王力鸿')
as c group by name;


-- 将两个联合查询去除重复值作为sql的查询结果插入到新的表中
insert into itheima.course2
(select * from itheima.course where name = '周杰轮'
	union all
select * from itheima.course where name = '王力鸿');

数据抽样： 

-- 桶抽样 对username字段hash值对10取余分桶,从桶中取出3份抽样(每次抽样结果一致)
select * from itheima.orders tablesample(bucket 3 out of 10 on username);
-- 桶抽样 使用随机数第hash值10取余分桶,从桶中取出3份抽样(每次抽样结果随机)
select * from itheima.orders tablesample(bucket 3 out of 10 on rand());
-- 块抽样 从第1条开始取出10条
select * from itheima.orders tablesample (6 rows);
-- 块抽样 从开头抽取出前%的条数
select * from itheima.orders tablesample (1 percent);
-- 块抽样 从开头取出固定大小(K|M|G)的条数
select * from itheima.orders tablesample (1K);

虚拟字段： 

-- INPUT__FILE__NAME 显示数据行所在文件偏移量
-- block__offset__inside__file 显示数据行所在文件的偏移量 
-- row__offset__inside__block 显示数据所在hdfs块的偏移量
select orderid,username,INPUT__FILE__NAME,block__offset__inside__file,row__offset__inside__block from itheima.orders_bucket ob ;
-- 查询分桶表中每个桶中记录数
select INPUT__FILE__NAME,count(*) as row_counts from itheima.orders_bucket 
group by INPUT__FILE__NAME;
-- 查询数据行所在文件的偏移量 >10000
select orderid,username,block__offset__inside__file from itheima.orders_bucket ob 
where block__offset__inside__file>10000;

函数： 

-- 取整函数（四舍五入）
select round(3.14159);
-- 指定精度取证函数（四舍五入）
select round(3.14159,4);
-- 取随机数函数
select rand();
-- 指定种子取随机数函数
select rand(3);
-- 求数字绝对值
select abs (-3);
-- 获取pi值(小数点15为精确度)
select pi();
-- 返回map类型元素个数
select size(members) from itheima.map_data;
--返回array类型元素个数
select size(work_location) from itheima.array_data;
-- 返回map内中的全部key值的arry数组
select map_keys(members) from itheima.map_data;
-- 返回map内中的全部values值的array数组
select map_values(members) from itheima.map_data;
-- 查询array中是否包含指定的value
select * from itheima.array_data where array_contains(work_location,"tianjin");
-- 根据数组的自然顺序排序
select sort_array(work_location) from itheima.array_data;
-- 将给定字符安川转为二进制
select binary('hadoop');
-- 将表达式的结果转换为指定类型
select cast('666' as int)
-- 返回当前时间戳
select current_timestamp();
-- 返回当前日期
select current_date();
-- 返回指定的年、季度、月、日、当前月份第几天
select year(current_date);
select quarter(current_date);
select month(current_date);
select day(current_date);
select dayofmonth(current_date);
-- 返回小时、分钟、秒、本年第几周
select hour('2023-06-06 23:59:59');
select minute('2023-06-06 23:59:59');
select second('2023-06-06 23:59:59');
select weekofyear('2023-06-06 23:59:59');
-- 返回两个日期之间的天数
select datediff('2023-06-16','2023-06-10');
-- 日期相加
select date_add('2023-06-16',1)
-- 日期相减
select date_sub('2023-06-16',1)

load data local inpath "/root/testData/itheima_users3.txt" overwrite into table  itheima.users;

-- if(条件判断,值1,值2 )
select if(truename="","没有名字",truename) as isname from itheima.users;
-- isnull
select isnull(truename) from itheima.users;
-- isnotnull
select isnotnull(truename) from itheima.users;
-- 判断truename是否为null,为null返回设置的值,否则返回本身
select nvl(truename,"没有名字") from itheima.users;
-- 当两个都为null时返回null
select coalesce(username,truename) from itheima.users;
-- 当case的truename等于when设置值,返回then后面的值(when...then...可以有多个),如果前面都不匹配,返回else设置的值
select case truename when "" then "没有名字" else truename end from users;
-- 当when的值为true,返回then的值(when...then...可以有多个),如果前面都不匹配,返回else设置值
select case when truename="" then "没有名字" else truename end from users;
-- 如果username字段中的值=truename字段中的值,返回null,否则返回username字段中的值
select nullif(username,truename) from users;
-- 如果设置的条件不为true,则引发报错信息(用于排错使用)
select assert_true(2<1);
-- 连接两个字符串
select concat("study","hadoop");
-- 设置分隔符并连接两个字符串
select concat_ws("+","study","hadoop")
-- 字符串长度
select length("studyhadoop")
-- 转大写
select upper("hadoop");
-- 转小写
select lower("HADOOP");
-- 取出两端空格
select trim("  hadoop   ");
-- 按照分隔符分割字符串
select split('hadoop:itcast:itheima',':')
-- 对字符串hash加密
select mask_hash("hadoop")
-- 返回hash数字
select hash("hadoop");
-- 返回当前登录用户
select current_user();
-- 返回当前选择的数据库
select current_database();
-- 返回当前的hive 版本
select version();
-- 返回当前指定参数的md5值
select md5("hadoop");