hql语言学习

Show

-- 显示show
show databases;
show tables in database;
-- 显示表的一些属性
desc extended t;
-- 全部信息
desc formatted t;
-- 显示分区信息
show partitions t;

show partitions t partition(dt=20200811);

Create/Drop/Truncate/Alter

create (external) table if not exists t(
id int comment "id",
name string comment "name")
partitioned by (dt string)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',' --指定每行中字段分隔符为逗号
LINES TERMINATED BY '\n' --指定行分隔符
stored as orc --存储格式
location '/hive/t.db/t';--存储位置

--删除
-- 保留表结构只删除文件,只针对内部表
truncate table t; 
-- 删除内表
drop table t;
-- 删除外表
ALTER TABLE t1 SET TBLPROPERTIES('EXTERNAL'='False');
drop table t1;

--修改表名
alter table t rename TO t1;
-- 修改字段名
alter table t change column col_old_name col_new_name column_type [COMMENT col_comment] after column_name;
-- 增加字段
alter table table_name add columns (column_new_name column_new_type [comment 'comment'])
-- 更新列
alter table table_name replace columns (column_new_name new_type)
-- 增加分区
alter table t add partition (dt='2020');
-- 删除分区
alter table t drop partition (dt='2020');
-- 更改分区
alter table t partition partition_spec CHANGE [COLUMN] col_old_name col_new_name column_type COMMENT col_comment FIRST|AFTER column_name] [CASCADE|RESTRICT];

ALTER DATABASE ... SET LOCATION语句不会将数据库当前目录的内容移动到新指定的位置。 它不会更改与指定数据库下任何表/分区关联的位置。 它仅更改默认的父目录，在该目录中将为此数据库添加新表。 此行为类似于更改表目录不会将现有分区移动到其他位置。

ALTER DATABASE ... SET MANAGEDLOCATION语句不会将数据库的托管表目录的内容移动到新指定的位置。 它不会更改与指定数据库下任何表/分区关联的位置。 它仅更改默认的父目录，在该目录中将为此数据库添加新表。 此行为类似于更改表目录不会将现有分区移动到其他位置。

关于数据库的其他元数据无法更改。

Sort/Order/Cluster/Distribute/Group

select id,name from t1 order by id (asc/desc);
select id,name from t1 sort by id (asc/desc);

order by是全局排序 sort by只是确保每个reduce上面输出的数据有序。如果只有一个reduce时，和order by作用一样。

select id,name from t1 cluster by id;
select id,name from t1 distributeby id;
select id,name from t1 group by id;

 distribute by与group by 的区别

都是按key值划分数据 都使用reduce操作 **唯一不同的是，distribute by只是单纯的分散数据，distribute by col 

 按照col列把数据分散到不同的reduce。而group by把相同key的数据聚集到一起，后续必须是聚合操作。

row_number()/rank()/dense_rank()

select id,name,rank() over(partition by id order by score desc) as rank from t1;
select id,name,row_number() over(partition by id order by score desc) as rank from t1;
select id,name,dense_rank() over(partition by id order by score desc) as rank from t1;

 三个函数相当于group by 之后再来个order by.

row_number()仅仅是加了序号;

rank() 可以显示相同的数据，下一名的排序+1，即为跳跃排序;

dense_rank()，即使有相同的数据，也会按照连续排序

条件判断函数

IF,COALESCE,CASE WHEN
if(boolean testCondition, T valueTrue, T valueFalse Or Null)

if(boolean testCondition, T valueTrue, T valueFalse Or Null)
select if(a=a,’bbbb’,111)；
bbbb
当条件testCondition为TRUE时，返回valueTrue；否则返回valueFalseOrNull

nvl(T value, T default_value)
select nvl(principal,1);
如果value值为NULL就返回default_value,否则返回value,主要功能就是实现null值替换。

CASE a WHEN b THEN c [WHENd THEN e]* [ELSE f] END
case a when a<0 then -1 when a>0 then 1 else 2 end;

CASE WHEN a THEN b [WHEN cTHEN d]* [ELSE e] END
case when a=b then 1 when a>b then 2 else 3 end

is null和is not null

insert/load

-- 本人的hive版本是这样
-- 写入
insert into t1(a,b,c) select a,b,b from t;
insert into t(a,b,c) values(a,b,c);
-- 去掉为null的行
insert overwrite table student select * from student where id is not null;
-- 本地导入 没有local,直接搬移元数据，有local元数据
load data (local) inpath '/home/hadoop/1.txt' into table t;
-- hdfs导入
load data inpath '/home/hadoop/1.txt' into table t;
-- 坑位
"orcfile为压缩格式，可以节约大量存储空间，
但orc还有个特点就是不能直接load数据！
要想load数据，我们要建一个存储格式为textfile的中间表"
-- add patition也可以向表内写数据
alter table itemdt add if not exists partition(dt='20211004') location 'file:///home/spark/rec_item';
alter table itemdt add if not exists partition(dt='20211005') location 'hdfs:///tmp/itemrec';

行列转换

-- 行列转换
select id,tag,tag_new
from t_row_to_column_tmp
lateral view explode(split(tag, ',')) num as tag_new
-- 列行转换
select id,
concat_ws(',',collect_set(tag_new)) as tag_col
from t_column_to_row
group by id;

join

SELECT * from t  WHERE runoob_author LIKE '%COM';
select * from t where conditions order by colu_name limit 3;
-- 可以使用max,min,count
select max(a) as a, max(b) as b, count(c) from group by name;
-- 外联
select a.id,a.name,b.age
from  a
left/right/full outer join  b
on (a.id = b.id);
-- 以LEFT SEMI JOIN关键字前面的表为主表，返回主表的KEY也在副表中的记录
select a.id,a.name,b.age
from  a
left semi join  b
on (a.id = b.id);
-- 返回两个表的笛卡尔积结果，及全组合
select a.id,a.name,b.age
from  a cross join  b
on (a.id = b.id);
-- case when
select t.name,
case when (t.dt='2020.1') then '0'
else '1' end from t;

date

-- 返回结束日期减去开始日期的天数
-- 日期转换函数
to_date(string timestamp)
-- 返回值: string

-- 日期比较函数: 
datediff(string enddate, string startdate)
-- 返回值: int

-- 日期增加函数: 
date_add(string startdate, int days)
-- 返回值: string

-- 日期减少函数: 
date_sub (string startdate, int days)
-- 返回值: string

SELECT CURRENT_DATE；

--- 月份差
select int(datediff(current_date,'2019-9-30')/30);

---2017-06-15
 
SELECT CURRENT_TIMESTAMP；

--2017-06-15 19:54:44
 
SELECT from_unixtime(unix_timestamp());

--2017-06-15 19:55:04

select from_unixtime(unix_timestamp('20171205','yyyymmdd'),'yyyy-mm-dd') from dual;

--2017-12-05

json

-- json 解析函数
-- 说明:解析 json 的字符串 json_string,返回 path 指定的内容。如果输入的 json 字符串无 效，那么返回 NULL
get_json_object(string json_string, string path)
-- 返回值: string
select id,get_json_object(concat('{',name,'}'),'$.Aa') as a,
get_json_object(concat('{',name,'}'),'$.Bb') as b,dt
from json

select json.id,tmp.a,tmp.b
from zxx.json json
lateral view json_tuple(name,'Aa','Bb')tmp as a,b;

查询结果保存到本地

-- 保存到本地文件
hive -e "select user, login_timestamp from user_login" > /tmp/out.txt

hive -f file.sql > /tmp/out.txt

insert overwrite local directory "/tmp/out/"
row format delimited fields terminated by "\t"                                     
select user, login_time from user_login;

--保存结果到hdfs
insert overwrite directory "/tmp/out/"
row format delimited fields terminated by "\t" 
select user, login_time from user_login;

内外表转换

内部转外部
alter table tableA set TBLPROPERTIES('EXTERNAL'='true')
外部转内部
alter table tableA set TBLPROPERTIES('EXTERNAL'='false')