6Hive Sql 大全

本节根本涵盖了Hive日常使用的全部SQL，因为SQL太多，所以将SQL举行了如下分类：
一、DDL语句（数据定义语句）：
对数据库的操作：包罗创建、修改数据库
对数据表的操作：分为内部表及外部表，分区表和分桶表
二、DQL语句（数据查询语句）：
单表查询、关联查询
hive函数：包罗聚合函数，条件函数，日期函数，字符串函数等
行转列及列转行：lateral view 与 explode 以及 reflect
窗口函数与分析函数
其他一些窗口函数
1hive 的 DDL 语法

1.1对数据库的操作

对数据库的操作
-创建数据库:

1. create database if not exists myhive;

复制代码

说明：hive的表存放位置模式是由hive-site.xml当中的一个属性指定的 :hive.metastore.warehouse.dir
创建数据库并指定hdfs存储位置 :

1. create database myhive2 location '/myhive2';

复制代码

-修改数据库:

1. alter  database  myhive2  set  dbproperties('createtime'='20210329');

复制代码

说明：可以使用alter database 命令来修改数据库的一些属性。但是数据库的元数据信息是不可更改的，包括数据库的名称以及数据库地点的位置
-查看数据库详细信息
查看数据库根本信息

1. hive (myhive)> desc  database  myhive2;

复制代码

查看数据库更多详细信息

1. hive (myhive)> desc database extended  myhive2;

复制代码

-删除数据库
删除一个空数据库，如果数据库下面有数据表，那么就会报错

1. drop  database  myhive2;

复制代码

欺压删除数据库，包罗数据库下面的表一起删除

1. drop  database  myhive  cascade;

复制代码

1.2对数据表的操作

对管理表(内部表)的操作:

-建内部表:

1. hive (myhive)> use myhive; -- 使用myhive数据库
2. hive (myhive)> create table stu(id int,name string);
3. hive (myhive)> insert into stu values (1,"zhangsan");
4. hive (myhive)> insert into stu values (1,"zhangsan"),(2,"lisi");  -- 一次插入多条数据
5. hive (myhive)> select * from stu;

复制代码

-hive建表时候的字段范例:

对decimal范例简单表明下：
用法：decimal(11,2) 代表最多有11位数字，其中后2位是小数，整数部分是9位；如果整数部分凌驾9位，则这个字段就会变成null；如果小数部分不敷2位，则背面用0补齐两位，如果小数部分凌驾两位，则超出部分四舍五入
也可直接写 decimal，背面不指定位数，默认是 decimal(10,0) 整数10位，没有小数
-创建表并指定字段之间的分隔符

1. create  table if not exists stu2(id int ,name string) row format delimited fields terminated by '\t' stored as textfile location '/user/stu2';

复制代码

row format delimited fields terminated by ‘\t’ 指定字段分隔符，默认分隔符为 ‘\001’
stored as 指定存储格式
location 指定存储位置
-根据查询结果创建表

1. create table stu3 as select * from stu2;

复制代码

-根据已经存在的表布局创建表

1. create table stu4 like stu2;

复制代码

-查询表的布局
只查询表内字段及属性

1. desc stu2;

复制代码

详细查询

1. desc formatted  stu2;

复制代码

-查询创建表的语句

1. show create table stu2;

复制代码

对外部表操作

外部表因为是指定其他的hdfs路径的数据加载到表当中来，所以hive表会以为自己不完全独占这份数据，所以删除hive表的时候，数据仍旧存放在hdfs当中，不会删掉，只会删除表的元数据
-构建外部表

1. create external table student (s_id string,s_name string) row format delimited fields terminated by '\t';

复制代码

-从当地文件系统向表中加载数据
追加操作

1. load data local inpath '/export/servers/hivedatas/student.csv' into table student;

复制代码

覆盖操作

1. load data local inpath '/export/servers/hivedatas/student.csv' overwrite  into table student;

复制代码

-从hdfs文件系统向表中加载数据

1. load data inpath '/hivedatas/techer.csv' into table techer;

复制代码

加载数据到指定分区

1. load data inpath '/hivedatas/techer.csv' into table techer partition(cur_date=20201210);

复制代码

-留意：
1.使用 load data local 表现从当地文件系统加载，文件会拷贝到hdfs上
2.使用 load data 表现从hdfs文件系统加载，文件会直接移动到hive相关目次下，留意不是拷贝过去，因为hive以为hdfs文件已经有3副本了，没必要再次拷贝了
3.如果表是分区表，load 时不指定分区会报错
4.如果加载类似文件名的文件，会被主动重命名
对分区表的操作

-创建分区表的语法

1. create table score(s_id string, s_score int) partitioned by (month string);

复制代码

-创建一个表带多个分区

1. create table score2 (s_id string, s_score int) partitioned by (year string,month string,day string);

复制代码

留意：
hive表创建的时候可以用 location 指定一个文件或者文件夹，当指定文件夹时，hive会加载文件夹下的全部文件，当表中无分区时，这个文件夹下不能再有文件夹，否则报错
当表是分区表时，比如 partitioned by (day string)， 则这个文件夹下的每一个文件夹就是一个分区，且文件夹名为 day=20201123 这种格式，然后使用：msck repair table score; 修复表布局，成功之后即可看到数据已经全部加载到表当中去了
-加载数据到一个分区的表中

1. load data local inpath '/export/servers/hivedatas/score.csv' into table score partition (month='201806');

复制代码

-加载数据到一个多分区的表中去

1. load data local inpath '/export/servers/hivedatas/score.csv' into table score2 partition(year='2018',month='06',day='01');

复制代码

-查看分区

1. show  partitions  score;

复制代码

-添加一个分区

1. alter table score add partition(month='201805');

复制代码

-同时添加多个分区

1. alter table score add partition(month='201804') partition(month = '201803');

复制代码

留意：添加分区之后就可以在hdfs文件系统当中看到表下面多了一个文件夹
-删除分区

1. alter table score drop partition(month = '201806');

复制代码

对分桶表操作

将数据按照指定的字段举行分成多个桶中去，就是按照分桶字段举行哈希划分到多个文件当中去
分区就是分文件夹，分桶就是分文件
分桶优点：

• 提高join查询服从
  
• 提高抽样服从
  

-开启hive的捅表功能

1. set hive.enforce.bucketing=true;

复制代码

-设置reduce的个数

1. set mapreduce.job.reduces=3;

复制代码

-创建桶表

1. create table course (c_id string,c_name string) clustered by(c_id) into 3 buckets;

复制代码

桶表的数据加载：由于桶表的数据加载通过hdfs dfs -put文件或者通过load data均不可以，只能通过insert overwrite 举行加载
所以把文件加载到桶表中，需要先创建普通表，并通过insert overwrite的方式将普通表的数据通过查询的方式加载到桶表当中去
-通过insert overwrite给桶表中加载数据

1. insert overwrite table course select * from course_common cluster by(c_id);  -- 最后指定桶字段

复制代码

修改表和删除表

-修改表名称

1. alter  table  old_table_name  rename  to  new_table_name;

复制代码

-增加/修改列信息
查询表布局

1. desc score5;

复制代码

添加列

1. alter table score5 add columns (mycol string, mysco string);

复制代码

更新列

1. alter table score5 change column mysco mysconew int;

复制代码

-删除表操作

1. drop table score5;

复制代码

-清空表操作

1. truncate table score6;

复制代码

说明：只能清空管理表，也就是内部表；清空外部表，会产生错误
留意：truncate 和 drop：
如果 hdfs 开启了回收站，drop 删除的表数据是可以从回收站恢复的，表布局恢复不了，需要自己重新创建；truncate 清空的表是不进回收站的，所以无法恢复truncate清空的表
所以 truncate 肯定慎用，一旦清空将无力回天
向hive表中加载数据

-直接向分区表中插入数据

1. insert into table score partition(month ='201807') values ('001','002','100');

复制代码

-通过load方式加载数据

1. load data local inpath '/export/servers/hivedatas/score.csv' overwrite into table score partition(month='201806');

复制代码

-通过查询方式加载数据

1. insert overwrite table score2 partition(month = '201806') select s_id,c_id,s_score from score1;

复制代码

-查询语句中创建表并加载数据

1. create table score2 as select * from score1;

复制代码

-在创建表是通过location指定加载数据的路径

1. create external table score6 (s_id string,c_id string,s_score int) row format delimited fields terminated by ',' location '/myscore';

复制代码

-export导出与import 导入 hive表数据（内部表操作）

1. create table techer2 like techer; --依据已有表结构创建表
3. export table techer to  '/export/techer';
5. import table techer2 from '/export/techer';

复制代码

hive表中数据导出

-insert导出
将查询的结果导出到当地

1. insert overwrite local directory '/export/servers/exporthive' select * from score;

复制代码

将查询的结果格式化导出到当地

1. insert overwrite local directory '/export/servers/exporthive' row format delimited fields terminated by '\t' collection items terminated by '#' select * from student;

复制代码

将查询的结果导出到HDFS上(没有local)

1. insert overwrite directory '/export/servers/exporthive' row format delimited fields terminated by '\t' collection items terminated by '#' select * from score;

复制代码

-Hadoop命令导出到当地

1. dfs -get /export/servers/exporthive/000000_0 /export/servers/exporthive/local.txt;

复制代码

-hive shell 命令导出
根本语法：（hive -f/-e 执行语句或者脚本 > file）

1. hive -e "select * from myhive.score;" > /export/servers/exporthive/score.txt
3. hive -f export.sh > /export/servers/exporthive/score.txt

复制代码

-export导出到HDFS上

1. export table score to '/export/exporthive/score';

复制代码

2hive 的 DQL 查询语法

2.1单表查询

1. SELECT [ALL | DISTINCT] select_expr, select_expr, ...
2. FROM table_reference
3. [WHERE where_condition]
4. [GROUP BY col_list [HAVING condition]]
5. [CLUSTER BY col_list
6.   | [DISTRIBUTE BY col_list] [SORT BY| ORDER BY col_list]
7. ]
8. [LIMIT number]

复制代码

留意：
1、order by 会对输入做全局排序，因此只有一个reducer，会导致当输入规模较大时，需要较长的计算时间。
2、sort by不是全局排序，其在数据进入reducer前完成排序。因此，如果用sort by举行排序，并且设置mapred.reduce.tasks>1，则sort by只保证每个reducer的输出有序，不保证全局有序。
3、distribute by(字段)根据指定的字段将数据分到差别的reducer，且分发算法是hash散列。
4、Cluster by(字段) 除了具有Distribute by的功能外，还会对该字段举行排序。
因此，如果分桶和sort字段是同一个时，此时，cluster by = distribute by + sort by
WHERE语句

1. select * from score where s_score < 60;

复制代码

留意：
小于某个值是不包罗null的，如上查询结果是把 s_score 为 null 的行剔除的
GROUP BY 分组

1. select s_id ,avg(s_score) from score group by s_id;

复制代码

分组后对数据举行筛选，使用having

1. select s_id ,avg(s_score) avgscore from score group by s_id having avgscore > 85;

复制代码

留意：
如果使用 group by 分组，则 select 背面只能写分组的字段或者聚合函数
where和having区别：
1 having是在 group by 分完组之后再对数据举行筛选，所以having 要筛选的字段只能是分组字段或者聚合函数
2 where 是从数据表中的字段直接举行的筛选的，所以不能跟在gruop by背面，也不能使用聚合函数
join 毗连

INNER JOIN 内毗连：只有举行毗连的两个表中都存在与毗连条件相匹配的数据才会被保存下来

1. select * from techer t [inner] join course c on t.t_id = c.t_id; -- inner 可省略

复制代码

LEFT OUTER JOIN 左外毗连：左边全部数据会被返回，右边符合条件的被返回

1. select * from techer t left join course c on t.t_id = c.t_id; -- outer可省略

复制代码

RIGHT OUTER JOIN 右外毗连：右边全部数据会被返回，左边符合条件的被返回、

1. select * from techer t right join course c on t.t_id = c.t_id;

复制代码

FULL OUTER JOIN 满外(全外)毗连: 将会返回全部表中符合条件的全部记录。如果任一表的指定字段没有符合条件的值的话，那么就使用NULL值替换。
SELECT * FROM techer t FULL JOIN course c ON t.t_id = c.t_id ;
注：1. hive2版本已经支持不等值毗连，就是 join on条件背面可以使用大于小于符号了;并且也支持 join on 条件后跟or (早前版本 on 后只支持 = 和 and，不支持 > < 和 or)
2.如hive执行引擎使用MapReduce，一个join就会启动一个job，一条sql语句中如有多个join，则会启动多个job
留意：表之间用逗号(,)毗连和 inner join 是一样的
select * from table_a,table_b where table_a.id=table_b.id;
它们的执行服从没有区别，只是书写方式差别，用逗号是sql 89标准，join 是sql 92标准。用逗号毗连背面过滤条件用 where ，用 join 毗连背面过滤条件是 on。
order by 排序

全局排序，只会有一个reduce

1. ASC（ascend）: 升序（默认） DESC（descend）: 降序
2. SELECT * FROM student s LEFT JOIN score sco ON s.s_id = sco.s_id ORDER BY sco.s_score DESC;

复制代码

留意：order by 是全局排序，所以末了只有一个reduce，也就是在一个节点执行，如果数据量太大，就会耗费较长时间
sort by 局部排序

每个MapReduce内部举行排序，对全局结果集来说不是排序。
设置reduce个数

1. set mapreduce.job.reduces=3;

复制代码

查看设置reduce个数

1. set mapreduce.job.reduces;

复制代码

查询成绩按照成绩降序分列

1. select * from score sort by s_score;

复制代码

将查询结果导入到文件中（按照成绩降序分列）

1. insert overwrite local directory '/export/servers/hivedatas/sort' select * from score sort by s_score;

复制代码

distribute by 分区排序

distribute by：类似MR中partition，举行分区，联合sort by使用
设置reduce的个数，将我们对应的s_id划分到对应的reduce当中去

1. set mapreduce.job.reduces=7;

复制代码

通过distribute by 举行数据的分区

1. select * from score distribute by s_id sort by s_score;

复制代码

留意：Hive要求 distribute by 语句要写在 sort by 语句之前
cluster by

当distribute by和sort by字段类似时，可以使用cluster by方式.
cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是正序排序，不能指定排序规则为ASC或者DESC。
以下两种写法等价

1. select * from score cluster by s_id;
2. select * from score distribute by s_id sort by s_id;

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。