HQL语句

创建数据库：
create database ***（数据库名称） （create：创造，创作；造成）

删除数据库：
drop database ***（数据库名称） （drop：vt. 滴；使降低；使终止；随口漏出 vi. 下降；终止 n. 滴；落下；空投；微量；滴剂 过去式 dropped或dropt 过去分词 dropped或dropt 现在分词 dropping）

建表：
create table ***（表名）（列名 列类型，列名 列类型，列名 列类型，…）
row format delimited fields terminated by ‘,’//字段的分割符号逗号分隔
collection items terminated by ‘_’//MAP STRUCT 和 ARRAY 的分隔符（数据分隔符号）
map keys terminated by ‘:’//MAP 中key与value的分隔符
lines terminated by ‘\n’ //行分隔符

数据：
AAA,BBB_lili,aaa1:18_aaa2:19,zhu xing zhuang_beijing
CCC,DDD_lala,ccc1:19_ccc2:19,zhu xing zhuang_beijing
例：
create table textc(name string,friends array,children map<string,int>,address structstress:string,city:string)
row format delimited fields terminated by ‘,’
collextion items terminated by ‘_’
map keys terminated by ‘:’
lines terminated by ‘\n’

删除表：
drop table ***（要删除的表名称）

查询数据：
select * from ***(表名称)

查询单个数据：
select friends[1],children[‘aaa1’],address.city from textc;

查询多个数据：
Select name,friends[1],children[‘aaa1’],children[‘ccc1’],address.stress,address.city from textc;

在其他数据库建表：
cteate table （数据库名称）.（表名称（列名称 列类型））；

查询表结构：
desc ***（表名称）；

查询详细表结构（只有输入输出使用的哪个类，没有看见是属于什么表）：
desc extended ***（表名称）；（extended：延长，扩充）

查询表的所有属性：
desc formatted ***（表名称）； （formatted：格式化（的）；格式化了的；有格式的）

创建表（数据结构来源与查询的数据：将查询数据的表的数据结构和数据拷贝到一个新创建的表中）
会运行MapReduce且此方式建立的表和查询的表的数据结构相同，会有数据结构和数据。）
create table ***（新表的名称）as select * from ***（要查询数据的表的名称）；

创建表（只有数据结构）：
create table ***（新表的名称）like ***（源表的名称）；

创建内部表指定存储路径：
create table ***（表名称（列名称 列类型，列名称 列类型，列名称 列类型…））
row format delimited fields terminated by ‘\t’ location’指定存储的路径’；
例：
create table dept（deptid int,deptname string,deptvalue int）
row format delimited fiedls terminated by ‘\t’ location ‘/user/hive/warehouse/dept’;

创建外部表指定存储路径：
create external table ***（表名称（列名称 列类型，列名称 列类型，列名称 列类型…））
row format delimited fields terminated by ‘\t’ location ‘指定存储的路径’；
create external table dept (deptid int,deptname string,deptvalue int)
row format delimited fields terminated by ‘\t’ location ‘/user/hive/warehouse/dept’;

创建外部表：
cteate external table ***（表名称（列名称 列类型，列名称 列类型，列名称 列类型））
row format delimited fields terminated by ‘\t’;

上传数据到表中：
load data local inpath ‘要上传数据的路径’ into table ***（表名称）；

修改内部表为外部表：
alter table ***（表名称）set tblproperties(‘EXTERNAL’=‘TRUE’); （alter：vt. 改变，更改 vi. 改变；修改 n. (Alter)人名；(英)奥尔特；(德、捷、葡、爱沙、立陶、拉脱、俄、西、罗、瑞典)阿尔特）

修改外部表为内部表：
alter table *** （表名称）set tblproperties(‘EXTERNAL’=‘FALSE’);

创建分区表：
cteate talbe ***（表名称（列名称 列类型，列名称 列类型，列名称 列类型…））partitioned by(分区表名称 分区表类型)
row format delimited fields terminated by ‘\t’;

上传数据到某个分区表中：
load data local inpath ‘数据所在的路径’ into table ***（表名称）partiton (分区表名称=‘分区表中列名称’)；
例：
load data local inpath ‘/a.txt’ into table beauties partition (nation=‘china’);
查询单个国籍
select * from beauties where nation=‘china’;
创建二级分区表 分区字段是两个:
create table （表名称（列名称 列类型，列名称 列类型，列名称 列类型…））partitioned by（分区表列名称 分区表列类型，分区表列名称 分区表列类型）
row format delimited fields terminated by ‘\t’; （partitioned：n. 划分，分开；[数] 分割；隔墙；隔离物 vt. [数] 分割；分隔；区分）
上传数据到分区下的子分区：
load data local inpath’数据所在的路径’ into table ***（表名称）partition （分区表名称=‘分区表中列名称’,子分区表名称=‘子分区表中的列名称’）;
例：
load data local inpath’/a.txt’ into table stu_data partition(month=‘201903’,day=‘20’);

查询多个分区的数据：
select * from （表名称）where 分区表名称=‘分区表列名称’;
select * from （表名称）where 分区表名称=‘分区表列名称’
and 分区表名称=‘分区表列名称’；and:两个条件都成立才会走
select * from （表名称）where 分区表名称=‘分区表列名称’
or 分区表名称=‘分区表列名称’；or:两个条件成立一个就会走

添加一个分区：
alter table （表名称）add partition(分区表名称=‘分区表列名称’,分区表名称=‘分区表列名称’);

添加多个分区：
alter talbe （表名称）add partition(分区表名称=‘分区表列名称’,分区表名称=‘分区表列名称’) partition(分区表名称=‘分区表列名称’,分区表名称=‘分区表列名称’);

删除分区：
alter table (表名称) drop partition(分区表名称=‘分区列名称’)；
alter table (表名称) drop partition(分区表名称=‘分区列名称’,分区表名称=‘分区列名称’);

删除多个分区：
alter table (表名称) drop partition(分区表名称=‘分区列名称’,分区表名称=‘分区列名称’),partition(分区表名称=‘分区列名称’,分区表名称=‘分区列名称’);
注意：分区的字段不能用创建表时候的字段。

注意：分区表的数据如果是自己mkdir创建的一个目录然后在put进去数据使用hql语句是查询不出来的
原因：创建分区表的时候创建的分区字段建立了元数据信息，可以查询的到，但是手动创建的时候没有创建元数据和字段的映射关系，所以查询不到
解决方法3种：把数据直接上传到分区目录上，让分区表和数据产生关联的三种方式
1：上传数据后修复
上传数据到hdfs：
dfs -mkdir -p /user/hive/warehouse/dept/month=201709/day=12;
dfs -put /opt/module/datas/dept.txt /user/hive/warehouse/dept/month=201709/day=12;
查询数据（查询不到刚上传的数据）:
select * from dept where month=‘201709’ and day=‘12’;
执行修复命令:
msck repair tale dapt;
再次查询数据:
select * from dept where month=‘201709’ and day=‘12’;
2:上传数据后添加分区
上传数据到hdfs：
dfs -mkdir -p /user/hive/warehouse/dept/month=201709/day=11;
dfs -put /opt/module/datas/dept.txt /user/hive/warehouse/dept/month=201709/day=11;
执行添加分区：
alter table dpet add partition(month=‘201709’,day=‘11’);
查询数据：
select * from dept where month=‘201709’ and day=‘11’;
3、上传数据后load数据到分区
创建目录：
dfs -mkdir -p /user/hive/warhouse/dept/month=201709/day=10;
上传数据：
load data local inpath '/opt/module/data/dept.txt into table dept partition(month=‘201709’,day=‘10’);
查询数据：
select * from dept where month=‘201709’ and day=‘10’;
重命名：
alter table student rename to dept;

替换列的字段：
alter table 表名称 replace columns(id int);
注意：如果你替换的时候，原来的表有多个字段，你指定了一个，会把所有的字段全部替换为你指定的哪个

加载数据：尽量使用load加载数据，数据比较安全，覆盖的方式加载数据，加上一个overwrite,之前的数据全部消失，替换为你加载的数据
load data local inpath ‘/dept.txt’ overwrite into table dept;
从hdfs加载数据，只需要去掉local，注意：如果是hdfs加载数据，源数据会移动到对应的表的相应的hdfs目录
追加：
load data inpath ‘/dept.txt’ into table dept;
覆写：
load data inpath ‘/dept.txt’ overwrite into table dept;
insert 方式加载数据，后面查询的数据的字段和分区表创建表的字段相对应
insert into table 表名称 partition(nation=‘china’) select * from stuent1;
如果使用insert方法加载数据会启动MapReduce的方式加载数据
多次插入：一次性插入多个数据
from 表名称
insert into table beauties partition(nation=‘china’)
select *
insert into table beauties partition(nation=‘usa’)
select ;
带条件多插入：
from 表名称
insert into table beauties partition(nation=‘china’)
select id,name where id=1
insert into table beauties partition(nation=‘china’)
select id,name where id=2;
导出数据不指定格式，自动创建目录：
insert overwrite local directory ‘/local_path’ select * from table_name;
指定导出数据的格式：
insert overwrite local directory ‘/local_path’ row fromat delimited fields terminated by ‘\t’ select * from table_name;
数据导出到hdfs：
insert overwrite directory ‘/hdfs_path’ select * from table_name;
指定数据导出的格式hdfs：
insert overwrite directory ‘/hdfs_path’ row format delimited fields terminated by ‘\t’ select * from table_name;
hive清空表数据：
truncate table table_name;
limit字句:
select * from table_name limit 5; 查询前5行数据
like语句:
查询某列中以2开头的值
select * from table_name where table_column like ‘2%’;
查询某列中第二个值为2的值
select * from table_name where talbe_column like ‘_2%’;
rlike语句：
查询某列中含有2的值
select * from table_name where table_column rlike ‘[2]’;
与或非：
select * from table_name where table_column>123 and table_id=1;
and:只有两边的条件都通过才会运行
select * from table_name where table_column>123 or table_id=1;
or：两点的条件只要有一个都通过就会运行，但是有个优先级
select * from table_name where table_column not in (8000,900);
分组：
group by 语句：
select table_department,max(table_sal) sal from talbe_name group by table_department;
select table_department,table_name,max(table_sal) sal from table_name group by table_department,table_name;
select table_department,avg(table_sal) avg_sal from table_name group by table_department having avg_sal > 15000;
注意：having和where不同点
1、where针对表中的列发挥作用，查询数据；having针对查询结果中的列发挥作用，筛选数据
2、where后面不能写分组函数，而having后面可以使用分组函数。
3、having只用于group by 分组统计语句
having只能用在group by之后，对分组后的结果进行筛选（即使用having的前提条件是分组）。
where肯定在group by之前
where后的条件表达式里不允许使用聚合函数，而having可以。
当一个查询语句同时出现了where,group by,having,order by的时候，执行顺序和编写顺序是where->group by 分组 ->having->排序
where不能和聚合函数一起使用，因为select语句执行在where之后。
join
hive通常支持sql的join语句，但是只支持等值连接。
select e.empname,d.deptname from emp e join dept d on e.empdepartment=d.dpetdepartment;
左连接：符合左边数据对应的数据
select e.empname,d.deptname from emp e left join dept d on e.empdepartment=d.deptdepartment;
右连接：符合右边数据对应的数据
select e.empname,d.deptname from emp e right join dept d on e.empdepartment=d.department;
多表join：
select e.empname,d.deptname,y.yanzhidengji from emp ejoin dept on e.emppartment=d.deptpartment join yanzhi y on e.empyanzhi=y.empyanzhi;
排序：全局排序升序降序 一个reduce
之前的mapreduce如果对全局进行排序必须设置一个reduce
排序按照字典集排序，如果排序的时候是int类型，那么是按照自然数进行排序，如果排序的类型是string类型，那么按照字典集排序。
select * from table_name order by table_column_int;(自然数)
select * from table_name order by table_column_string;(字典集)
按照别名排序：
select value_name,value_id10 id from table_name order by id;
二次排序：注意：二次排序的后面的字段必选在select后面一句存在
如果想要详细的数据，只需要在select后面补充就可以；
select column_department,column_id from table_name order by column_department,column_id;
select column_department,column_id,column_name from table_name order by column_department,column_id;
sort by：每个reduce内部进行排序，对全局结果来说，并不是排序
注意：是在每个reduce中进行排序
设置reduce数量：
set mapred.reduce.tasks=2; set mapreduce.job.reduces=1;
set mapred.reduce.tasks;(查看reduce的数量)
select * from table_name sort by column_id;
分区排序：
distribute by ：
distribute by 类似mapreduce2个关键词的结合使用
partition 进行分区sort by 使用
注意：hive要求distribute by 语句要写在 sort by前面。
select * from table_name distribute by column_department sort by column_sal desc;
(asc：升序 desc：降序)
cluster by：cluster by是distribute by 和 sort by 的集合体
select * from table_name cluster by column_department;
||
select * from table_name distribute by column_department sort by column_department;
分桶表：
分区针对的是存储路径，分桶针对的是数据文件。
分区是提供一个隔离数据和优化
分桶是将数据结合分成若干部分的另一个技术，分桶表有自己的一条查询规则
分桶表示对建表是的某个字段进行分桶 3代表3个桶
查询分桶表的状态，默认是关闭的
set hive.enforce.bucketing;
开启：
set hive.enforce.bucketing=true;
分桶表中插入数据：
insert into table table_name select * from table_bucket_name;
空字段赋值：
函数nvl功能是如果某个字段的数据为null，那么通过nvl函数可以把null的数据变为你指定的数据，方法有2种：
第一种方法：
select column_id,column_idtwo,nvl(column_idthree,1) from table_name;
第二种方法：
selelct column_id,column_idtwo,nvl(column_idthree,column_idtwo) from table_name;
case when:
case when 条件 then 条件为真是的值 else 条件为假时的值 end；
select table_column
sum(case column_sex when ‘男’ then 1 else 0 end) sum_man,
sum(case column_sex when ‘女‘ then 1 else 0 end)sum_female
from table_name
group by column_room;
order by:
order by会对输入做全局排序，因此只有一个reduce（多个reduce无法保证全局有序）
只有一个reduce会导致当输入规模较大时，需要较长的计算时间。
set hive.mapred.mode=nonstrice;(defaule value / 默认值)
set hive.mapred.mode=strict;
order by和数据库中的order by功能一致，按照某一项&几项 排序输出。
与数据库中的order by的区别在与hive.mapred.mode=strict 模式下必须制定limit否则执行会报错。
hive> select * from test order by id;
FAILED: Error in semantic analysis: 1:28 In strict mode, if ORDER BY is specified, LIMIT must also be specified. Error encountered near token ‘id’
原因：在order by状态下所有数据会到一台服务器进行reduce操作也即只有一个reduce，如果在数据量大的情况下会出现无法输出结果的情况，如果进行limit n ，那么只有nmap number 条记录。只有一个reduce也可以处理过来。
sort by：
sort by不是全局排序，其在数据进入reduce前完成。因此，如果用sort by进行排序，并且设置mapred.reduce.tasks>1，则sort by只保证每个reduce的输出有序，不保证全局有序。
sort by不受hive.mapred.mode是否为strict，nostrict的影响。
sort by的数据只能保证在同一reuce中的数据可以按指定的字段排序。
使用sort by你可以指定执行的reduce个数（set mapred.reduce.tasks=）,对输出的数据在执行归并排序，即可以得到全部的结果。
注意：可以使用limit子句大大减少数据量。使用limit n 后，传输到reduce段（单机）的数据减少到n(map个数)。否则由于数据过大可能出不来结果。
distribute by：
按照指定的字段对数据进行划分到不同的输出reduce/文件中。
insert overwrite local directory ‘/home/hadoop/out’ select * from table_name order by column_name distribute by length(column_name);
此方法会根据column_name的长度划分到不同reduce中，最终输出到不同的文件中。
length是内建函数，也可以指定其他的函数或使用自定义函数。
cluster by：
clusret by 除了具有distribute by 的功能外还兼具sort by 的功能。但是，排序只能是倒序怕徐，不能指定排序规则为asc或者desc。
行转列：lateral view expolde
select column_id,column_exlie from table_name lateral view explode(split(column_exlie,’,’))num as table_name where column_id=1;
列转行：
函数：concat_ws(’,’,collect_set(column))
collect_list：不去重。collect_set去重。column的数据类型要求是string