DML-Load加载数据：
load data [local] inpath 'filepath' [overwrite] into table tablename
[partition(partcol1=val1,partcol2=val2...)]
有local就在本地文件系统
没有local就在hdfs分布式文件系统
如果有overwrite关键字，则目标表（或者分区）中的已经存在的数据就会被删除，然后再将filepath指向的文件/目录中的内容添加到表/分区表中。
将数据文件插入到分区表中，但是不指定分区的情况：
在这种load不指定分区的情况下，会将load转变为insert as select。
DML-insert使用方式：
insert+select表示：将后面查询返回的结果作为内容插入到指定表当中，注意overwrite将覆盖已有数据。
1.需要保证查询结果列和数目和需要插入数据表格的列数据一致。
2.如果查询出来的数据类型和插入表格对应的列数据类型不一致，将会进行转换，但是不能保证转换一定成功，转换失败的数据将会为null。
insert overwrite table 和 insert into table。
动态分区插入：（先设置成非严格模式）
set hive.exec.dynamic.partition = true;
set hive.exec.dynamic.partition.mode = nonstrict;
select语法：
select [ALL|DISTINCT] select_expr,select_expr,....
from table
[where where_condition]
[group by col_list]
[order by col_list]
[
cluster by col_list | [distribute by col_list][sort by col_list]
]
[limit [offset,] rows];
1. select_expr的4种可能性：
查询所有字段或者指定字段
select * from t_usa_covid19_p;
select country,cases,deaths from t_usa_covid19_p;
查询匹配正则表达式的所有字段
SET hive.support.quoted.identifiers = none;
select '^c.*' from t_usa_covid19_p;;
查询当前数据库(省去from关键字)
select current_database();
查询使用函数
select count(country) from t_usa_covid19_p;
2.ALL和DISTINCT：
在select中默认是all，代表我们返回所有匹配的行。
如果是distinct的话，就代表是去重。
3.where：
在where中不能使用聚合函数，因为使用聚合函数的前提是已经由where进行筛选之后，获得了确定的结果集之后的，才能再使用聚合函数。
select * from t_usa_covid19_p where 1>2;
这个where的结果是false，如果是false的话，那么前面的select就不会执行，所以这个语句的查询结果就是空集。
select * from t_usa_covid19_p where 1=1;
这个where的结果是true，所以前面的select就会执行。
select * from t_usa_covid19_p where length(state)>10;
这个就会找到所有state的长度大于10的结果。
select * from A where A.a IN (select foo from B);
where子句支持子查询。
4.分区查询：
where的条件可以用来做分区查询，提高查询效率。
5.group by概念：
group by语句用于结合聚合函数，根据一个或者多个列对结果集进行分组。
但是出现在group by中select_expr的字段：要么是group by分组的字段，要么是被聚合函数应用的字段。
6.having：
在sql中增加having子句的原因是，where无法和聚合函数一起使用，那么having负责的是在group by分组之后的筛选结果集的任务。
也就是先执行where，然后再分组，然后再用having进行二次筛选。
where是在分组前过滤，having在分组后过滤。
7.limit：
limit 5，代表从偏移量0开始返回5行数据。
limit 2，3 代表从偏移量2（也就是第3行）开始返回3行数据。
8.执行顺序：
在查询过程中执行顺序：
from>where>group by>having>order>select;
9.order by:
Hive中的order by语法类似于标准sql语言中的order by语法，会对输出的结果进行全局排序。因此当底层使用MapReduce引擎执行的时候，只会有一个reducetask执行，如果输出的行数太大了，就会导致需要很长的时间来完成。
asc升序，desc降序。
10.cluster by：
根据指定字段将数据分组，然后再在组内进行排序，而且只能是正序排序。
说白了就是根据同一个字段，分且排序。
两个例子：
1.
select * from student cluster by num;
这个就是分为一组去排序。因为默认是1。
2.
先设置reducetask数量为2。
set mapreduce.job.reduces = 2;
select * from student cluster by num;
但是其实这也是他的局限性，如果我分组和排序的字段不是同一个字段，那么cluster by就无法处理了，而且局限性还有一个，那就是只能是升序排序。
11.distribute by+order by：
distribute管的是分组，order by管的是排序。
那么其实distribute by+order by就是相当于cluster by的。
总结一下，其实就是order by只能是全局排序，也就是reducetask只能有1个，不管你自己设置的mapreduce.job.reduces = 多少，只能是1。那么cluster by就是多了一个分组的功能，但是分组和排序的字段只能是1个，而且只能是升序排序。distribute by可以管分组，但是不管排序；sort by管分组后的排序，但是不管分组。如果将distribute by 和 sort by合起来使用就能够做到，又分组又排序。
12.union联合查询：
用于将多个select语句的结果合并为一个结果集。
union all 不去重，所有结果集加在一起。
union distinct 去重，所有结果集加在一起。
如果union后面不加all，那就是默认distinct，自动会去重的。
如果想要针对其中的select语句设置limit参数，那就必须如下操作：
如下就是，先联合，然后limit再生效。
13.from子句中子查询：
这里面的tmp 其实是给“select num,name from student_local”这个句子命名的
14.where子句中子查询：
where子句支持的子查询：
1.不相关子查询：该子查询不引用父查询中的列，可以将查询结果视为IN和NOT IN语句的常量。
select *
from t1
where t1.num IN (select num from t2 limit 2);
子查询根本没有涉及到父查询中的表和字段。
2.相关子查询：子查询引用父查询中的列。
select A
from t1
where exists (select B from t2 where t1.X = t2.Y);
在这里，子查询引用了父查询中的字段，这个就叫相关子查询。
关于相关子查询，其实是这样的，会先执行select A from t1这个操作，也就是先执行父查询这个操作，然后会逐个的到子查询的集合中去检查是否存在这个父查询的数据，如果子查询的集合中存在我这个父查询的条件，那么我就返回这行数据。但是在不相关子查询中，会先执行子查询的结果集，然后再根据子查询去限制父查询的结果。
in语句，实际上是先计算子查询的，是把外表和内表作hash连接。
exists语句，实际上是对外表做loop循环，每次loop循环再去对内表进行查询。也就是外表一行一行的把数据拿出来跟内表去比对。这样就不用去循环内表了，因为内表比较大。
子查询大的用exists，子查询小的用in。
15.CTE（common table expressions）：
公用表表达式（CTE）是一个临时结果集：该结果集是从WITH子句中指定的简单查询派生而来的，紧接在SELECT或者INSERT关键字之前。
CTE仅在单个语句的执行范围内定义。
CTE可以在SELECT，INSERT，CREATE TABLE AS SELECT 或者CREATE VIEW AS SELECT语句中使用。
例子：
with q1 as (select num,name,age from student where num = 95002)
select *
from q1;
with q1 as (select num,name,age from student where num = 95002)
from q1
select *;
CTEs链式
with q1 as(select * from student where num = 95002),
q2 as(select num,name,age from q1)
select * from (select num from q2) a;
with q1 as(select * from student where num = 95002)
insert overwrite table s1
select * from q1;
16.6种join语法：
inner join（内连接）：
left join（左连接）：
right join（右连接）：
full outer join（全外连接）：可以是full join
left semi join（左半开连接）：会返回左边表的记录，前提是其记录对于右边的表满足on语句中的判定条件。相当于是inner join，但是只返回左表的信息。
cross join（交叉连接，也叫做笛卡尔乘积）：两个表的行数乘积。
Hive内置运算符：
show functions 显示所有的函数和运算符
describe function count 查看运算符或者函数的使用说明
describe function extended count 使用extended可以查看更加详细的使用说明
关系运算符：
关系运算符是二元运算符，执行的是两个操作数的比较运算。
每个关系运算符都返回boolean类型结果（true或者falze）
举例子：
null 或者not null
select 1 from dual where 'itcast' is null; //这个语句where的条件是false，所以不会执行。
select 1 from dual where 'itcast' is not null; //这个就返回1，写死在这里的。
--like比较：_表示任意单个字符 %表示任意数量字符。
--否定比较：NOT A like B。
select 1 from dual where 'itcast' like 'it_'; //后面的_代表只有一个字符，所以返回空
select 1 from dual where 'itcast' like 'it%'; //后面的%代表可以自由匹配几个字符都行，所以返回1。
select 1 from dual where 'itcast' not like 'hadoo_'; //返回1，not like就是不匹配
select 1 from dual where not 'itcast' like 'hadoo_'; //也返回1，因为不匹配
--rlike：确定字符串是否匹配正则表达式，是REGEXP_LIKE()的同义词。
select 1 from dual where 'itcast' rlike '^i.*t$';
select 1 from dual where '123456' rlike '^\\d+$'; --判断是否全为数字。
select 1 from dual where '123456aa' rlike '^\\d+$'; --明显不都是数字。
算术运算符：
算术运算符操作数必须是数值类型。分为一元运算符和二元运算符：
一元运算符，只有一个操作数；
二元运算符有两个操作数，运算符在两个操作数之间。
--取整操作：div 给出将A除以B所得的整数部分。
select 17 div 3； --结果就是5
--取余操作：也叫做取模操作
select 17 % 3； --结果就是2
--位与操作： & A和B按位进行与操作的结果。
select 4 & 8 from dual；--4转换二进制：0100 8转换二进制：1000 结果是0
select 6 & 4 from dual；--4转换二进制：0100 6转换二进制：0110 结果是4
--位或操作：^ A和B按位进行异或操作的结果 异或表示两者的值不同，则结果为1。
select 4 ｜ 8 from dual; --4转换二进制： 0100 8转换二进制： 1000 结果是 12
select 6 ｜ 4 from dual; --6转换二进制： 0110 4转换二进制： 0100 结果是 6
--位异或操作：^ A和B按位进行异或操作的结果 异或表示两者的值不同，则结果为1
select 4 ^ 8 from dual; --4转换二进制： 0100 8转换二进制： 1000 结果是 12
select 6 ^ 4 from dual; --6转换二进制： 0110 4转换二进制： 0100 结果是 2
逻辑运算符：
--与操作： A and B 如果A和B均为true，则为true，否则为false。如果A或者B为NULL，则为NULL。
select 1 from dual where 3>1 and 2>1; //结果为1
--或操作：A or B 如果A或B或两者均为TRUE，则为TRUE，否则为false。
select 1 from dual where 3>1 or 2!=2; // 结果为1
--非操作：NOT A , !A. 如果A为false，则为true，如果A为NULL，则为NULL，否则为FALSE。
select 1 from dual where not 2>1; //返回结果是空。
select 1 from dual where !2=1; //。!2=1 和 not 2=1的结果其实是等价的。
--在：A IN（val1,val2,...）如果A等于任何值，则为TRUE。
select 1 from dual where 11 in (11,22,33); //结果为1
--不在：A NOT IN（val1,val2,...）如果A不等于任何值，则为TRUE。
select 1 from dual where 11 not in(22,33,44); //结果为true，因为11不在这里面。
--逻辑是否存在：[NOT] EXISTS (subquery)
--将主查询的数据，放到子查询中做条件验证，根据验证结果（TRUE或者FALSE）来决定主查询的数据结果是否得以保留。相关子查询。
select A.* from A where exists (select B.id from B where A.id = B.id);
Hive的函数：
内置函数（built-in Functions）：
数学类型函数：
--取整函数
select round(3.1415926);
--指定精度取整函数：round(double a,int d) //返回指定精度d的double类型。
--向下取整函数：
select floor(3.1415926); //3
select floor(-3.1415926); //-4
--向上取整函数：
select ceil(3.1415926);
select ceil(-3.1415926);
--取随机数函数：rand 每次执行都不一样 返回一个0到1范围内的随机数
select rand();
--指定种子取随机数函数：rand(int seed) 得到一个稳定的随机数序列
select rand(2);
日期类型函数：
--获取当前日期：
select current_date();
--获取当前时间戳：
select current_timestamp();
--获取当前unix时间戳函数：unix_timestamp
select unix_timestamp(); //此时此刻所对应的时间戳。
--日期转unix时间戳函数：unix_timestamp
select unix_timestamp("2011-12-07 13:01:03"); //这个时间的时间戳。
--指定格式日期转unix时间戳函数：unix_timestamp
select unix_timestamp('2011120713:01:03','yyyyMMdd HH:mm:ss');
--unix时间戳转日期函数：from_unixtime
select from_unixtime(xxxx);
select from_unixtime(0,'yyyy-MM-dd HH:mm:ss'); //结果为1970-01-01 00:00:00
--日期比较函数：datediff 日期格式要求'yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-dd'
select datediff('2012-12-08','2012-05-09'); //显示相差的结果：213
--日期增加函数：date_add('2012-02-28',10); //结果为2012-02-18
--日期减少函数：date_sub('2012-01-1',10); //结果为2011-12-22
字符串函数：
describe function extended concat；
--拼接函数：
select concat("angela","baby");
--规定分隔符的拼接函数：
select concat_ws('.','www',array('itcast','cn'));
--字符串截取函数：
select substr("angelababy",2); //ngelababy是从2开始截取的。
select substr("angelababy",-2)；//pos是从1开始的索引，如果为负数则倒着数，结果为by。
select substr("angelababy",2,2); //结果为ng。
--正则表达式替换函数：regexp_replace(str,regexp,rep)
select regexp_replace('100-200','(\\d+)','num'); 结果为num-num。
--正则表达式解析函数：regexp_extract(str,regexp[,idx])提取正则匹配到的指定组内容。
select regexp_extract('100-200','(\\d+)-(\\d+)',2); 结果为200。
-URL解析函数：parse_url 一次解析出多个，可以使用parse_url_tuple这个UDTF函数。
select parse_url('http://www.itcast.cn/path/p1.php?query=1','HOST');
-分割字符串函数：split(str,regex);
select split('apache hive','\\s+'); //记住\\s+代表任意的空白字符。拆出来的结果是一个数组。
-返回最后一个有数值的分区的函数：max_pt(table_full_name)
select * from table_name where pt = max_pt(table_name);
这样就会返回最后一个有数据的一级分区。
-拆分函数：split_part(拆分字段，拆分的标识符，从哪段开始拆分，要前几段)
split_part(ip,'.',1,3)；这个例子如果ip是234.213.32.34的话，那么最后的答案就应该是234.213.32。
-date_format(date,pattern);
将时间变成我们想要的格式。
date_format(2018-09-21,MMMM); September（九月份）。
集合函数：
用的少，如果需要用直接去视频里面看就行。
条件函数：
if条件判断：if（boolean testCondition, T valueTrue, T valueFalseOrNull）
select if(1=2,100,200);
select if(sex='男','M','W') from student limit 3; //完成了从中文到字符的一个替换过程。
--空判断函数：isnull();
select isnull("allen");
--非空判断函数：isnotnull();
select isnotnull("allen");
select isnotnull(null);
--空值转换函数：nvl(T value,T default_value)
select nvl("allen","itcast"); 如果第一个不为空，那就是第一个值。
select nvl(null,"itcast"); 如果第一个是空的，那么就要第二个值。
--非空查找函数：COALESCE(T v1, T v2, ...);
--返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULL。
select COALESCE(null,11,22,33); //11
select COALESCE(null,null.null,33); //33
select COALESCE(null,null,null); //null
--nullif(a,b)
--如果a=b，则返回null，否则返回第一个
select nullif(11,11); // null
select nullif(11,22); //11
--条件转换函数：
case a when b then c [when d then e] * [else f] end;
select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end; // mary
select case sex when '男' then 'male' else 'female' end from student limit 3;
// male female female
类型转换函数：//任意数据类型之间显式的转换：cast
select cast(12.14 as bigint); //12
select cast(12.14 as string); // '12.14'
select cast("hello" as int); // null 结果出来就是空的。
数据脱敏函数：主要完成对数据脱敏转换功能，屏蔽原始数据，主要如下：
mask:查询回的数据，大写字母转换为X，小写字母转换为x，数字转换为n。
select mask("abc123DEF"); //xxxnnnXXX
select mask("abc123DEF", '-' , '.' , '^');
//大写字母替换成-，小写字母替换成. ，数字替换为^。
//...^^^---
mask_first_n(string str[, int n]):对于前几个进行脱敏替换。
select mask_first_n("abc123DEF",4); //xxxn23DEF。
mask_last_n(string str[, int n]);
select mask_last_n("abc123DEF",4); //abc12nXXX。
mask_show_first_n(string str[, int n]); //除了前4个，其余都进行脱敏操作。
select mask_show_first_n("abc123DEF",4); //abc1nnXXX。
mask_show_last_n(string str[, int n]); //除了后4个，其余都进行脱敏操作。
select mask_show_last_N("abc123DEF",4); //xxxnn3DEF。
mask_hash(string|char|varchar str); //返回字符串的hash编码。
select mask_hash("abc123DEF");
其余杂项函数：
哈希加密：取哈希值
select hash("allen");
SHA-1加密：sha1(string/binary)
select sha1("allen");
SHA-2家族算法加密：sha2(string/binary, int) (SHA-224,SHA-256,SHA384,SHA-512)
select sha2("allen",224);
select sha2("allen",512);
crc32加密：
select crc32("allen");
MD5加密：md5(string/binary)
select md5("allen");
用户定义函数（User-Defined Functions）：
现在的范围是所有的函数，不一定是用户自定义的。
UDF：普通函数，一进一出。
UDAF：聚合函数，多进一出。
select sex from student;
select collect_set(sex) from student;
select collect_list(sex) from student;
UDTF：表生成函数，一进多出。
explode函数：只能接受array和map这两种类型的函数。
select explode('array'(11,22,33,44,55));
select explode('map'("id",10086,"name","allen","age",18)); //3个元素。
Lateral View是一种特殊的语法，主要搭配UDTF类型函数一起使用，用于解决UDTF函数的一些查询限制的问题。
只要是使用UDTF函数，就会固定搭配Lateral View使用。
举例子：
举例子：UDAF函数，也就是聚合函数。
--count(*):所有行进行统计，包括NULL行。
--count(1):所有行进行统计，包括NULL行。
--count(column):对column中非NULL进行统计（排除掉null）。
统计男生的数量第一种方法：select sum(if(sex='男',1,0);
统计男生的数量第二种方法：select sum(case when sex='男' then 1 else 0 end);
聚合函数不支持嵌套聚合函数：
select avg(count(*)) from student;
val1 val2
2 3
1 2
null 2
select sum(val1),sum(val1+val2) from tmp_1;// 3 8
sum在计算的时候val如果一个为null那就自动舍弃。
select
sum(coalesce(val1,0)),
sum(coalesce(val1,0)+val2)
from tmp_1;
下面这个的意思就是分成男女性别两组，然后max函数是自动计算struct里面第一个字段的最大值，所以也就是找出男女里面分别年龄最大的和他们的名字。
select
sex,
max(struct(age,name)).col1 as age,
max(struct(age,name)).col2 as name
from student
group by sex;
增强聚合--grouping sets
select
month,
day,
count(distinct cookieid) as nums,
grouping_id
from cookie_info
group by month,day
grouping sets(month,day)
order by grouping__id
窗口函数：
不光是sum，还可以是avg，min，max等。
select cookieid,createtime,pv,sum(pv) over() as total_pv
from website_pv_info;
select cookieid,createtime,pv,sum(pv) over(partition by cookieid) as total_pv
from website_pv_info;
select cookieid,createtime,pv,sum(pv) over(partition by cookieid order by createtime) as current_total_pv
from website_pv_info;
窗口表达式：windows expression
select cookieid,createtime,pv,sum(pv) over(partition by cookieid order by createtime) as pv1
from website_pv_info;
select cookieid,createtime,pv,sum(pv) over(partition by cookieid order by createtime rows between unbounded preceding and current row) as pv 2
from website_pv_info;
这两个其实是一个效果，都是从第一行到当前行。
select cookieid,createtime,pv,sum(pv) over(partition by cookieid order by createtime rows between 3 preceding and current row) as pv4
from website_pv_info;
select cookieid,createtime,pv,sum(pv) over(partition by cookieid order by createtime rows between 3 preceding and 1 following) as pv5
from website_pv_info;
select cookieid,createtime,pv,sum(pv) over(partition by cookieid order by createtime rows between current row and unbounded following) as pv6
from website_pv_info;
窗口排序函数--row_number家族(包括dense_rank,rank)：
select
cookieid,
createtime,
pv,
RANK() over(partition by cookieid order by pv desc) as rn1, //rank
DENSE_RANK() over(partition by cookieid order by pv desc) as rn2, //dense_rank
ROW_NUMBER() over(partition by cookieid order by pv desc) as rn3 //row_number
from website_pv_info
where cookieid = 'cookie1';
适合TopN业务分析
select *
from(
select cookieid1,createtime,pv,row_number() over(partition by cookieid order by pv desc) as rn1
from website_pv_info
) tmp
where tmp.rn1<=3;
窗口排序函数--ntile:
将每个分组内的数据分为指定的若干个桶里（分为若干个部分），并且为每一个桶分配一个桶编号。
如果不能平均分配，则优先分配较小编号的桶，并且各个桶中能放的行数最多相差1.
有时会有这样的需求，如果数据排序后分为三个部分，业务人员只关心其中的一部分，如何将这中间的三分之一数据拿出来呢？NTILE函数的作用就体现在这里。
select *
from(
select cookieid,createtime,pv,ntile(3) over(partition by cookieid order by createtime) as rn2
from website_pv_info
) tmp
where tmp.rn2 = 1;//这就是前三分之一的部分，也就是第一部分。
------------------------------------------------------------------------------
select *
from(
select cookieid,createtime,pv,ntile(3) over(partition by cookieid order by createtime) as rn2
from website_pv_info
) tmp
where tmp.rn2 = 2;//这就是中间那个部分，也就是第二部分。
-------------------------------------------------------------------------------
select *
from(
select cookieid,createtime,pv,ntile(3) over(partition by cookieid order by createtime) as rn2
from website_pv_info
) tmp
where tmp.rn2 = 3;//这就是最后那个部分，也就是第三部分。
总而言之，NTILE(x)可以将数据分成x个部分。
窗口分析函数：
LAG():用于统计窗口内往上第n行值
LEAD():用于统计窗口内往下第n行值
FIRST_VALUE():取分组内排序后，截止到当前行，第一个值
LAST_VALUE():取分组内排序后，截止到当前行，最后一个值
抽样函数：
三种方式：
1.随机抽样 ：使用rand()函数来确保随机获取数据，limit来限制抽取的数据个数。
速度有点慢，尤其是在表多的时候。
2.基于数据块抽样：
Block块采样允许随机获取n行数据，百分比数据或指定大小的数据。
但是缺点就是不随机。
3.基于分桶表抽样
针对分桶表进行了优化，优点是既随机速度也很快。文章来源：https://www.toymoban.com/news/detail-851699.html
TABLESAMPLE（BUCKET * OUT OF y [ON colname]）--回头再详细了解吧，用的不多。文章来源地址https://www.toymoban.com/news/detail-851699.html
到了这里，关于Hive sql的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
