hiveQL实战操作用来向hive装载数据，不过注意这操作不是把数据复制到表中，而是把文件和表建立起关联，当访问表的时候

一、DDL语句

这篇文章DDL语句讲得很全，（文章链接：www.cnblogs.com/qingyunzong…

SQL中的if表达式用法和流程控制用法：www.cnblogs.com/xuhaojun/p/…

concat(str1,str2)函数是用来连接字符串的，而concat_ws(连接符,str1,str2)函数也是用来连接字符串，但是第一个参数表示连接起来的分隔符

//预设str1字段是hello，str2字段是小江
concat(str1,str2)    //结果是hello小江
concat_ws(",",str1,str2)    //结果是hello,小江

二、DML语句

1、load data [local] inpath '文件路径' [overwrite] into table 表名

用来向hive装载数据，不过注意这操作不是把数据复制到表中，而是把文件和表建立起关联，当访问表的时候就去查询hdfs的数据。参数说明：如果加上local则支持用户从其本地文件装载数据（eg:file:///user/hive/example），如果没有local则从Hadoop配置变量fs.default.name中设定的路径加载文件（eg:hdfs://namenode:9000/user/hive/example）。如果有overwrite的话就把数据装载到一个早已建好的表中并且替换原来的数据。

2、insert into table 表名 values(row_values1),(row_values2); 用来插入表数据，支持多行插入。

insert overwrite table 表名 partition(分区字段=’分区值') select子句; 用来把查询的数据插入到目标表中。

具体参考这篇文章：blog.csdn.net/babydavic/a…

3、update 表名 set 列名=值 where 条件 更新表数据

4、delete 表名 where 条件 删除表数据

5、select 列名 from 表名1 join 表名2 on (表1唯一字段=表2唯一字段) 等值连接

select 列名 from 表名1 left join 表名2 on (表1唯一字段=表2唯一字段) 左外连接（左表有的都要出现）

select 列名 from 表名1 right join 表名2 on (表1唯一字段=表2唯一字段) 右外连接

select 列名 from 表名1 full outer join 表名2 on (表1唯一字段=表2唯一字段) 全外连接（左右表有的都要出现）

select 列名 from 表名1 left semi join 表名2 on (表1唯一字段=表2唯一字段)

左半连接（右边的表只能在on子句中设置过滤条件，不能在where设置过滤条件，最终select结果只允许出现左表，右表的数据被过滤掉了，左表的记录在右表中一旦找到对应的记录，右侧表既停止扫描，所以性能会高些），PS：没有右半连接。

6、修改hive表结构

在指定列中增加一列做法：分两步，先在列末尾增加一列，之后移动到指定列后
alter table 表名 add columns (列名 数据类型 comment '数据注释');    //在最后添加一列
alter table 表名 change 要移动的列名 数据类型 after 指定列名;    //移动列的位置

重命名hive表
//alter table 旧表名 rename to 新表名
alter table table_old rename to table_new;

修改hive表列字段或数据类型
//alter table 表名 change 要修改的列名 新列名 新数据类型
alter table table_name change col1 col2 string;

三、窗口函数

和SQL一样有sum()、count()、max()、min()、avg()等聚合函数，还有

ntile(参数)：指的是根据传入的参数划分为几份，并返回每一份的序号。

row_number()：指的是生成一列连续的序号，从1开始。

rank()：指的是生成一列连续的序号，但和row_number()区别的是相同的值这一项都为相同的序号，下一个序号就跳过。

dense_rank()：与rank()相反，下一个序号不跳过。

lag(参数1,参数2,参数3)：第一个参数是排序列名，第二个参数是往后移动的开始行，第三个是没有数据时的默认值。

lead(参数1,参数2,参数3)：这和lag()函数是相反的，区别是它往前移动。

first_value(参数)：，参数是该列，对该列到目前为止的第一个值。

last_value(参数)：参数是该列，对该列到目前为止的最后一个值，注意：经测试这个函数内的参数得是数值型，否则效果不对。

以上窗口函数总结主要参考这篇文章：www.jianshu.com/p/9fda829b1…

四、进阶函数使用

实现列转行的函数

collect_list(列名)，collect_set(列名)，都是将分组中某列转换为一个数组，区别在于collect_list(列名)不会去重操作，而collect_set(列名)会去重操作，而且还可以利用这两个函数来突破group by的限制，从而取到分组列的某个值，具体看参考文章例子就一目了然。附参考文章：www.cnblogs.com/cc11001100/…

union all和union

union all对两个结果集进行并集操作，不进行去重操作，也没有进行排序

union对两个结果集进行并集操作，但有进行去重操作和排序

grouping sets和cube

这两个都是基于group by来实现特殊需求的，cube是按维度group by分组后，再对这些维度进行全组合，相当于多次group by，而grouping sets类似于cube，区别在于grouping sets是可以选择要在这些组合中自定义选择，等价于将不同维度的group by结果集进行union all操作。另外还有rollup，是按维度group by分组后，前一维度为null后一位维度必须为null，前一维度取非null时，下一维度随意。demo结果参考文章，学习参考文章

select uid,uname,sex,count(1) from person
group by uid,uname,sex
    grouping sets(
         (uid,uname)
         ,(uid,uname,sex)
   ) 
--with cube
--with rollup

get_json_object函数和json_tuple函数

附学习参考文章：blog.csdn.net/sinat_29581…

这两个函数都是用来解析获取json格式的文本数据的，不过它俩就区别在get_json_object一次只能解析一个键对象的数据，而json_tuple一次可以解析多个键对象的数据。假设content字段是存放着json数据（即键值对）

select get_json_object(content,'$.键名') from test   //这样就解析获取到对应键名的数据了
select a.* from test lateral view json_tuple(content,'键名1','键名2','键名3') a as k1,k2,k3    //这样就解析获取多个键名的数据了

//这一段代码例子是从这篇文章https://www.cnblogs.com/db-record/p/11414611.html获取而来
INSERT OVERWRITE TABLE DM_PACZ_APPLY_POLICY_DUTYINFOLIST
SELECT current_date AS stat_date
&emsp;&emsp;&emsp;&emsp;, a.apply_policy_no
&emsp;&emsp;&emsp;&emsp;, d.dutycode
&emsp;&emsp;&emsp;&emsp;, NVL(d.insuredamount, 0) AS insuredamount
FROM ${OdsSafeDatabase}.PACZCB_PACZDATA_CZ_POLICY_DETAIL_INFO a
LATERAL VIEW json_tuple(data, 'dutyInfoList') b AS dutyinfolist
LATERAL VIEW explode(split(regexp_replace(regexp_extract(b.dutyinfolist,'^\\[(.+)\\]$',1),'\\}\\,\\{', '\\}\\|\\|\\{'),'\\|\\|')) c AS list
LATERAL VIEW json_tuple(c.list, 'dutyCode', 'insuredAmount') d AS dutycode, insuredamount

explore函数和lateral view用法

附学习参考文章：blog.csdn.net/weixin_3904…

上面文章内附例子。简单理解，explore函数是把一组数据拆分为一列数据，而lateral view是一个表生成函数，将explore拆分的一列数据转成一张虚拟表。通常lateral view都是和explore或者split函数搭配使用的。

from_unixtime函数

from_unixtime用法，将int(11)类型存起来的时间（即时间戳）格式化为YYYY-MM-DD格式

from_unixtime(5481445451,'yyyyMMdd HH:mm:ss')

四、hive语句使用正则表达式

1、regexp_extract(第一个参数,第二个参数,第三个参数)

作用：根据正则规则匹配把字段的值拆分哪几组并选择第几组作为结果输出。第一个参数是字符串或者字段名，第二个参数是正则拆分规则，每个()拆分为一组，第三个参数是取第几组，0表示把整个正则表达式对应的结果全部返回。举例：select regexp_extract(abcde_ver_lev2,'(abcdeOS-)([0-9]+\.[0-9]+)',2) from 表名，拿到的结果是类似于7.3这种数据。

2、regexp_replace(第一个参数,第二个参数,第三个参数)

作用：根据正则规则匹配把字段的值替换掉。第一个参数是字符串或者字段名，第二个参数是正则替换规则，第三个参数是替换的文本。

3、字符型字段 rlike 正则规则

作用：把符合正则规则的给筛选出来，如果要把不符合的给筛选出来可以在前面加个not。 注意：sql正则表达式开始和结束标志^和$前面不需要\转义，和java不一样。 举例：

select phone from phone_table where phone not rlike '^1\d{10}$';    //拿到的就是不符合11位合理手机号的数据
select * from 表名  where 字段名 rlike '^\\d+$';    //匹配全是数字的数据
select fphone * 表名 where phone rlike '^((\\+86)|(86))?1\\d{10}$'    //拿到的就是包含+86、86、1开头的手机号。
where dev_ver not rlike '[\\\\\\\\u4e00-\\\\\\\\u9fa5]'   //过滤掉含有中文的固件版本数据

关于正则表达式的基础标识就看我的这篇文章

五、hive命令

1、ANALYZE TABLE命令，主要用来优化查询，加快查询的速度

ANALYZE TABLE 表名 partition (stat_date=%(yyyymmddhh)s) COMPUTE STATISTICS for COLUMNS;

2、MISC REPAIR TABLE 表名命令，主要用来解决通过hdfs dfs -put写入hive分区表数据在hive中无法被查询到的问题，其实可以理解为就是修复元分区

如果不是通过hive的insert方式插入数据进分区表，很多分区信息在metastore中是没有的，如果分区数少我们还可以先通过alter table 表名 add partition 来添加分区再通过hdfs的put命令插入数据，如果多了则不方便，我们可以直接先hdfs的put命令插入数据后使用MISC REPAIR TABLE 表名来修复元分区，这样分区信息就写入到metastore了。

附：期间阅读

HCatalog简介：www.jianshu.com/p/017acc804…

hive工作流程：www.cnblogs.com/lyr999736/p…

hive面试题：blog.csdn.net/wxfghy/arti…