Hive

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

{
  "name": "songsong",
  "friends": ["bingbing" , "lili"] ,    //列表Array, 
  "children": {           //键值Map,
   "xiao song": 18 ,
    "xiaoxiao song": 19
  }
  "address": {           //结构Struct,
    "street": "hui long guan",
    "city": "beijing"
  }
}

1
2

songsong,bingbing_lili,xiao song:18_xiaoxiao song:19,hui long guan_beijing
yangyang,caicai_susu,xiao yang:18_xiaoxiao yang:19,chao yang_beijing

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

create table test(
name string,
friends array<string>,
children map<string, int>,
address struct<street:string, city:string>
)
row format delimited fields terminated by ','
collection items terminated by '_'
map keys terminated by ':'
lines terminated by '\n';

/*
#字段解释：
#row format delimited fields terminated by ',' -- 列分隔符
#collection items terminated by '_'    --MAP STRUCT 和 ARRAY 的分隔符(数据分割符号)
#map keys terminated by ':'            -- MAP中的key与value的分隔符
#lines terminated by '\n';              -- 行分隔符
*/

1

load data local inpath '/home/forsre/test.txt' into table test; 

1

select friends[1],children['xiao song'],address.city from test where name="songsong";

1

select '1'+2, cast('1'as int) + 2;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

usage: hive
 -d,--define <key=value>          Variable substitution to apply to Hive
                                  commands. e.g. -d A=B or --define A=B
    --database <databasename>     Specify the database to use
 -e <quoted-query-string>         SQL from command line
 -f <filename>                    SQL from files
 -H,--help                        Print help information
    --hiveconf <property=value>   Use value for given property
    --hivevar <key=value>         Variable substitution to apply to Hive
                                  commands. e.g. --hivevar A=B
 -i <filename>                    Initialization SQL file
 -S,--silent                      Silent mode in interactive shell
 -v,--verbose                     Verbose mode (echo executed SQL to the
                                  console)

1
2
3
4
5
6

#执行Linux命令
! uptime;
#查看hdfs文件系统
dfs -ls /;
#所有历史命令
cat $HOME/.hivehistory

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

1） unix_timestamp : 返回当前或指定的时间戳；
SELECT  unix_timestamp("2020-05-02 11:22:00"); ==>1588418520
2） from_unixtime : 将时间戳转化为日期格式
SELECT FROM_unixtime(1588418520); ==> 2020-05-02 11:22:00
3) current_date : 当前日期
4）current_timestamp: 当前日期 + 时间；
5）to_date : 获取日期部分
6）year/month/day/hour/minute/second() : 获取年、月、日、小时、分、秒
7）weekofyear(): 当前时间是一年中的第几周
8）dayofmonth(): 当前时间是一个月中的第几天
9）months_between() : 两个日期间的月份
10) datediff() : 两个日期相差的天数
11) add_months：日期加减月
12) date_add：日期加天数
13) date_sub：日期减天数
14) last_day: 日期的当月的最后一天

1
2
3

1) round： 四舍五入
2) ceil：  向上取整
3) floor： 向下取整

1
2
3
4
5
6
7
8

1）upper： 转大写
2）lower： 转小写
3）length： 长度
4）trim：  前后去空格
5）lpad： 向左补齐，到指定长度
6）rpad：  向右补齐，到指定长度
7）regexp_replace： SELECT regexp_replace('100-200', '(\\d+)', 'num') ；
	使用正则表达式匹配目标字符串，匹配成功后替换！

1
2
3
4
5

1） size： 集合中元素的个数
2） map_keys： 返回map中的key
3） map_values: 返回map中的value
4） array_contains: 判断array中是否包含某个元素
5） sort_array： 将array中的元素排序

1
2
3
4
5

1） 查看系统自带的函数
show functions;

2) 查询函数的用法
desc function extended 函数名

1
2
3
4
5
6

-- 语法：
nvl(value,default_value)

-- 说明：
1）如果value 为null，则返回default_value ，否则返回vaule；
2）如果两个值（value , default_value）均为null，则返回null；

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

-- 示例：
select 
  dept_id,
  sum(case sex when '男' then 1 else 0 end) male_count,
  sum(case sex when '女' then 1 else 0 end) female_count
from 
  emp_sex
group by
  dept_id;
  
  /*  解读：
  1.按照dept_id 进行分组，同一组的数据先进行计算；
  2.假设dept_id=10的数据有10条，则10数据分别在sum函数中进行计算，计算完成以后得出一个结果；
  3.一组数据最后得到一条数据结果。
  */

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

-- 相关函数
1） concat('str1','str2','str3',...) : 表示将str1/str2/str3... 依次进行连接，str1/str2/str3... 可以说任何数据类型；
-- 示例：SELECT  concat('132','-','456'); ==> 132-456

2) concat_ws('连接符'，'str1','str2',...) : 表示使用'连接符'将str1/str2...依次进行连接，str1/str2...只能是字符串或者是字符串数组。
-- 示例：
SELECT  concat_ws('-','java','maven'); ==> java-maven;
SELECT  concat_ws(null,'java','maven'); ==> null -- 当连接符为null时，结果返回null
SELECT  concat_ws('.', 'www', array('facebook', 'com')) ；==> www.facebook.com

3） collect_set(col) : 函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段
-- 示例：
SELECT COLLECT_set(deptno) from emp; ==>[20,30,10]

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- 语法：
lateral view explode (split(字段，分割符)) 表名 as 列名
-- 说明：
lateral view : 侧写；
explode(): 将指定的集合拆解分成多行 -- 炸裂
split(字段，分割符) : 将指定的字符串按照分割符封装成一个集合。

-- 示例：
SELECT movie,category_name 
FROM movie_info 
lateral VIEW
explode(split(category,",")) movie_info_tmp  AS category_name ; -- categor_name 为炸裂的列名，move_info_tmp为侧写的表名

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

相关函数说明：开窗函数是为每一条数据进行开窗
1） over() : 单独使用此函数，默认的窗口大小为结果集的大小。
2） partition by : 在窗口函数中进行分区
 	over(partition by 字段) ：对结果集内进行分区，每条数据的开窗大小为该结果集中分区集的大小。
3) over( order by 字段) ： 在窗口函数中只用到了order by 排序时，也会对每条数据进行开一个窗口，默认的开窗大小为：从结果集的开始位置到当前处理数据的位置。
-- 实例：
-- 1.查询在2017年4月份购买过的顾客及总人数
-- 解析，顾客全部要，多个顾客，多行，人数为一个值，一行，则是需要进行开窗，因为不是一一匹配的。
        SELECT  name ,
        COUNT(*)   OVER () `人数`
        from business 
        WHERE  SUBSTRING(orderdate,1,7)='2017-04' 
        group by name ;

-- 2.查询顾客的购买明细及月购买总额
    SELECT name ,orderdate ,cost ,
    sum (cost) over(partition by MONTH (orderdate))
    from business;
    
-- 3.上述的场景, 将每个顾客的cost按照日期进行累加
    SELECT name ,orderdate ,cost ,
    sum (cost) over(partition by name order by orderdate)
    from business;
    
4） CURRENT ROW：当前行
	n PRECEDING：往前n行数据
	n FOLLOWING：往后n行数据
5）UNBOUNDED：起点，
	UNBOUNDED PRECEDING 表示从前面的起点 
    UNBOUNDED FOLLOWING 表示到后面的终点
6）LAG(col,n,default_val)：往前第n行数据
7）LEAD(col,n, default_val)：往后第n行数据
8）NTILE(n)：把有序窗口的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。注意：n必须为int类型。
示例：
-- 需求：查询前20%时间的订单信息
select * from (
    select name,orderdate,cost, ntile(5) over(order by orderdate) sorted
    from business
) t
where sorted = 1;

1
2
3
4
5
6
7

-- 函数说明
1) RANK() 排序相同时会重复，总数不会变; 
   -- 1 2 2 4 5 5 7
2) DENSE_RANK() 排序相同时会重复，总数会减少; 
  -- 1 2 2 3 3 4 4 5
3) ROW_NUMBER() 会根据顺序计算。
  -- 1 2 3 4 5 6 

1
2
3
4
5
6
7
8

自定函数的分类：
1） UDF（User-Defined-Function） -- 一进一出

2） UDAF（User-Defined Aggregation Function） -- 聚集函数，多进一出
	类似于：count/max/min
	
3） UDTF（User-Defined Table-Generating Functions） -- 一进多出
	如lateral view explode()

1
2

示例：
select my_len("abcd"); ==> 4 

1
2
3
4
5
6
7

<dependencies>
		<dependency>
			<groupId>org.apache.hive</groupId>
			<artifactId>hive-exec</artifactId>
			<version>3.1.2</version>
		</dependency>
</dependencies>

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54

package com.forsre.hive;

import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
import org.apache.hadoop.hive.ql.exec.UDFArgumentLengthException;
import org.apache.hadoop.hive.ql.exec.UDFArgumentTypeException;
import org.apache.hadoop.hive.ql.metadata.HiveException;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDF;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;

/**
 * 自定义UDF函数，需要继承GenericUDF类
 * 需求: 计算指定字符串的长度
 */
public class MyStringLength extends GenericUDF {
    /**
     *
     * @param arguments 输入参数类型的鉴别器对象
     * @return 返回值类型的鉴别器对象
     * @throws UDFArgumentException
     */
    @Override
    public ObjectInspector initialize(ObjectInspector[] arguments) throws UDFArgumentException {
        // 判断输入参数的个数
        if(arguments.length !=1){
            throw new UDFArgumentLengthException("Input Args Length Error!!!");
        }
        // 判断输入参数的类型
        if(!arguments[0].getCategory().equals(ObjectInspector.Category.PRIMITIVE)){
            throw new UDFArgumentTypeException(0,"Input Args Type Error!!!");
        }
        //函数本身返回值为int，需要返回int类型的鉴别器对象
        return PrimitiveObjectInspectorFactory.javaIntObjectInspector;
    }

    /**
     * 函数的逻辑处理
     * @param arguments 输入的参数
     * @return 返回值
     * @throws HiveException
     */
    @Override
    public Object evaluate(DeferredObject[] arguments) throws HiveException {
       if(arguments[0].get() == null){
           return 0 ;
       }
       return arguments[0].get().toString().length();
    }

    @Override
    public String getDisplayString(String[] children) {
        return "";
    }
}

1

add jar /opt/module/hive/datas/myudf.jar;

1

create temporary function my_len as "com.forsre.hive. MyStringLength";

1

select ename,my_len(ename) ename_len from emp;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63

package com.forsre.udtf;

import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
import org.apache.hadoop.hive.ql.metadata.HiveException;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspectorFactory;
import org.apache.hadoop.hive.serde2.objectinspector.StructObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;

import java.util.ArrayList;
import java.util.List;

public class MyUDTF extends GenericUDTF {

    private ArrayList<String> outList = new ArrayList<>();

    @Override
    public StructObjectInspector initialize(StructObjectInspector argOIs) throws UDFArgumentException {


        //1.定义输出数据的列名和类型
        List<String> fieldNames = new ArrayList<>();
        List<ObjectInspector> fieldOIs = new ArrayList<>();

        //2.添加输出数据的列名和类型
        fieldNames.add("lineToWord");
        fieldOIs.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);

        return ObjectInspectorFactory.getStandardStructObjectInspector(fieldNames, fieldOIs);
    }

    @Override
    public void process(Object[] args) throws HiveException {
        
        //1.获取原始数据
        String arg = args[0].toString();

        //2.获取数据传入的第二个参数，此处为分隔符
        String splitKey = args[1].toString();

        //3.将原始数据按照传入的分隔符进行切分
        String[] fields = arg.split(splitKey);

        //4.遍历切分后的结果，并写出
        for (String field : fields) {

            //集合为复用的，首先清空集合
            outList.clear();

            //将每一个单词添加至集合
            outList.add(field);

            //将集合内容写出
            forward(outList);
        }
    }

    @Override
    public void close() throws HiveException {

    }
}

1
2
3
4
5
6
7
8

1.显示数据库
show databases;
2.切换数据库
use 数据库名；
3.查询数据库详细信息
desc database [extended] 数据库名
4.查询表的详细信息
desc [formatted] 表名

1
2
3
4

CREATE DATABASE [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path] 
[WITH DBPROPERTIES (property_name=property_value, ...)];

1
2
3
4
5
6
7

1.create database db_hive;

2.create database if not exists db_hive; 
/* 加上 if not exists 后，当该数据库已存在时，不抛异常，也不做创建数据库的操作*/

3.create database db_hive2 location '/db_hive2.db';
/*指定数据创建时，在hdfs上的路径，如果没有此操作，则默认的路径为：/user/hive/warehouse/数据库名*/

1
2
3
4
5
6

1.删除空的数据库（何为空的数据库？指该数据中没有表）
drop database db_hive2 ;
2.当数据库不存在时，避免抛异常
drop database if not exists db_hive2 ;
3.当数据库不为空时，加上cascade进行删除
drop database if not exists db_hive2  cascade ;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name 
[(col_name data_type [COMMENT col_comment], ...)] 
[COMMENT table_comment] 
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] 
[CLUSTERED BY (col_name, col_name, ...) 
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] 
[ROW FORMAT row_format] 
[STORED AS file_format] 
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)]
[AS select_statement]

1
2
3
4

1.管理表：也称内部表，当删除管理表时，hdfs中的数据和mysql中的元数据均会被删除 -- 控制表的生命周期
2.外部表：当删除管理表时，hdfs中的数据不会被删除，mysql中的元数据会被删除  -- 不能控制表的生命周期

在实战过程中，我们一般都是使用外部表。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

1.定义：
创建表单时，加上 external 关键字则表示为外部表。

2.查看：
通过 desc formatted 表名 。

3.转换：
alter table 表名 set tblproperties('EXTERNAL'='TRUE');
注意事项：
a、TRUE :   转换为外部表；
b、FALSE ： 转换为内部表;
c、('EXTERNAL'='TRUE')和('EXTERNAL'='FALSE')为固定写法，均需要大写！

1
2
3
4

-- 语法：
alter table 旧表名 rename to 新表名 ；
-- 示例：
alter table dept_partition2 rename to dept_partition3;

1
2
3
4

-- 语法：
alter table 表名 change 旧列名 新列名 数据类型  
-- 示例：
alter table emp change ename naem string first deptno;

1
2
3
4

-- 语法：
alter table 表名 add 列名 数据类型 [字段注释] [first / after  列名]
-- 示例：
alter table emp add loc string ;

1
2
3
4

-- 语法：
drop table 表名
-- 示例：
drop table emp ;

1

当导入数据时，如果加载本地的文件，并是将数据加载到有分区和分桶表的hive表中时，因为此导入数据的过程会跑mr程序，该本地文件需要在所有节点都需要，不然会报文件不存在异常。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- 语法：
load data [local] inpath '数据的路径'  [overwrite] into table 表名 [partition (分区字段 = value1) (分区字段 = value2)]

-- 说明：
local : 如果使用了，则'数据的路径'写linux本地的路径；
	    如果未使用，则'数据的路径'写hdfs上的路径； 
partition (分区字段 = value1)   ：表示数据上传到哪一个分区，后面详细介绍。  
overwrite : 表示覆盖写。

-- 示例：
本地 ： load data local inpath '/opt/module/hive/datas/emp' into table emp;
hdfs :  load data inpath '/user/hive/warehouse/emp' into table emp;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

-- 语法：
1) insert into table 表名 select 字段 from 表名； -- 追加的方式，原数据不会丢失

2) insert overwrite table 表名 select 字段 from 表名； -- 覆盖原数据的方式，原数据被覆盖

3) insert into table 表名 select 字段 from 表名 partition (分区字段 = Value)； 多分区的插入模式

-- 示例：
1) insert into table emp select id ,name from emp1;

2) insert overwrite table emp  select id ,name from emp1;

3) insert into table emp select id ,name from emp1 partition (month = '2020-02-04');

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

-- 语法：
建表语句 + as  + 查询语句

-- 示例：
create  [external] table [if not exists] emp (
id int ,
name string
)
row format delimited fields terminated by '\t'  
as select id , name from emp1; 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

-- 语法：
建表语句 + location + 'hdfs数据路径'  

-- 说明：
数据路径：只能是hdfs上的路径，当该路径是一个目录时，则表示加载该文件夹下的所有文件

-- 示例：
create  [external] table [if not exists] emp (
id int ,
name string
)
row format delimited fields terminated by '\t'  
location '/user/hive/warehouse/emp' ;

1
2

-- 示例：
import table student2  from '/user/hive/warehouse/export/student'

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

-- 语法：
insert  overwrite    [local] directory '输出文件路径' [row format delimited fields terminated by '分割符'] 查询语句

-- 说明：
overwrite ：overwrite 是覆盖原文件的数据写入
[local] ：加它，表示导出到本地，不加，则表示导出到hdfs上
'输出文件路径' ： 配合local来的，加了local，则写本地linux路径，不加，则写hdfs路径
[row format delimited fields terminated by '分割符'] ：表示文件输出的格式

-- 示例：
-- 导入到本地
1）insert overwrite local directory '/opt/module/hive/datas/export/student'  select * from student;

-- 导出到本地，并指定导出的行数据的分割符
2）insert overwrite local directory '/opt/module/hive/datas/export/student1' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'  select * from student;

-- 导出到hdfs上，并指定导出的行数据的分割符
3）insert overwrite directory '/user/lianzp/student2'ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' select * from student;

1
2

-- 语法：
hdfs dfs -get hdfs数据的输出路径 linux输入路径

1
2

-- 语法：
hive -e 查询语句 > linux输入路径

1
2

-- 语法：
export table 表名 to  '文件输出路径'  -- 此路径为hdfs路径

1
2

-- 语法：
truncate table 表名 ；

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

-- 建表：
1) partitioned by :分区表
2）clustered by  : 分桶表

-- 查询：
1） order by : 全排序
2） distribute by : 查询中做分区
3） sort by : 查询中每个MapReduce内部排序
4） cluster by : 查询中做分区排序

-- 窗口函数：
1) partition by :窗口函数中做分区
2) order by ：窗口函数中做排序

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

1. from ;
2. on ;
3. join ;
4. where ; -- 不能使用列的别名
5. group by ; -- 不能使用列的别名
6. having ; -- 可以使用列的别名
7. select ;
8. distinct ;
9. order by ; -- 可以使用列的别名
10. limit ; -- 可以使用列的别名

注意事项： 表名一旦使用了别名，所有的位置均需使用表的别名。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

-- 语法：
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
  FROM table_reference
  [WHERE where_condition]
  [GROUP BY col_list]
  [ORDER BY col_list]
  [CLUST BY col_list
    | [DISTRIBUTE BY col_list] [SORT BY col_list]
  ]
 [LIMIT number]
 
-- 说明：
 DISTINCT ：去重；
 CLUST BY col_list  
   
 

1
2
3

-- 语法：
select * from 表名 ；  -- 全表查询
select 列名1、列名2 from 表名 ； -- 特定列查询

1
2
3
4
5
6

定义： 在查询中紧跟列名，也可以在列名与别名之间加as；
注意事项：
1）在hive中，中文的别名使用 一对 `` 来注释；
2）as 一般可以省略 ；
3） where 、 group by 后面不能使用列的别名；
4）having 、order by 、limit 可以使用列的别名 ；

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

1)  c求和 ： sum（）；
2） 求平均数 ： avg () ;
3)  求最大值 ： max（）；
4） 求最小值 ： min（）；
5） 求个数 ： count（）；

-- 说明：
1） count（）：不计算null值；
2） avg () : 计算平均数时，分母也是不计算null个数的；
3） 所以： avg (字段) = sum (字段) / count(字段)，因此我们在计算一些列的平均值时，一般使用count（*）或者是count（1）；

1
2

1） 条件的筛选；
2） 紧跟from后面。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

1） like
% : 代表零个或者是多个字符（即时任意字符）	
_ : 代表一个字符；
\ :  转义字符；

2） Rlike ：后面紧跟随正则表达式
 \ : 转义字符，即屏蔽特殊字符的含义：\$;
 ^ : 从头开始匹配，如：name  rlike ^a  : 表示以a开头的name
 $ : 匹配结尾 ，如  name Rlike t$ ：匹配以t结尾的name
 * ： 0-n 个 ，如 name rlike a* : 匹配 0-n a的name
[] : 表示范围，如 [0-9,a-z]:匹配0-9或者是a-z都可以。

1
2
3

- 常和聚合函数在一起;
- 出现在 group by 中的字段可以出现在 select中，也可以不出现，
  但是出现在 select中字段（除函数和常量外）必须在group by 出现过的字段。

1
2
3

 having  与 where 的不同
1） where 后面不能写分组函数，但是 having 可以 ；
2） having 只用于 Group by 分组统计语句；

1
2
3
4
5
6
7

-- 说明：
1） 常见的7种 join 要会写；
2） 不支持非等值连接；
3） 支持满外连接 ： full join ;
4)  关于主表和从表： -- 左右外连接，主表数据全要，从表数据只要交集的部分。
 	左外连接 ： 左边为主表，右边为从表 ；
	右外连接 ： 右边为主表，左边为从表。

1
2
3
4
5
6
7
8

1)  全局排序，只能有一个Reducer ；
2） DESC : 降序 ；
3)  ASC : 升序（'默认值'）；
4） Order by 子句必须在SELECT语句的结尾 ；
5)  排序的字段可以是多个；
示例：
select id , name ,sal from emp order by sal desc ,name asc ;
-- 先按照薪水降序，薪水相同的，则按照名字进行升序排序；

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

1)  理解：
理解为在 reduce 中进行排序。所以一般是需要有多个 reduce 才有作用，是在每个reduce中进行排序，属于局部排序，而不是全局排序。
2） 使用场景：
当数据量很大时，不要进行全局排序，只需要进行局部排序。
3） 一般不单独使用，因为无法控制什么样的数据进入同一个 reduce 中；
-- 一般配合distribute by 使用，分区排序就是指定什么样的数据会进入同一个reduce中。
4） 单独使用时，进入同一个 reduce 任务中的数据是随机的。 -- 伪随机，就是每次计算的结果是一样的，但是进入每一个reduce 中的数据是随机的。

-- 示例：
1） 设置reducer的个数：
set mapreduce.job.reduces=3; -- 设置reduce个数为3
2） 根据部门编号降序查看员工信息 ： 
select * from emp sort by deptno desc;
-- 此时生成3个结果文件，并且每个结果文件中均是按照deptno 进行降序排序。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

1. 理解 ： 类似在 MapReduce 中的自定义分区（partition ）;
2. 一般就是配合 sort by 使用；
3. 同样，在使用的时候，不能是一个reduce，需要多个reduce；
4. 什么样的数据会进行同一个reduce 呢 ？
  1）首先，这个分区不是很智能，使用的方式是：分区的字段的 （ hashcode  % reduce的个数 ），计算值相等的，则进入同一个reduce；
  2）不会使用toString方式进行分区。
5. distribute by 必须写在sort by 的前面；
6. tez 引擎会进行reduce的优化，即假设设置为3个reduce，但是运行时有可能是2个reduce，所以验证时032，需使用mr引擎。-- set hive.execution.engine=mr;

-- 示例：
insert overwrite local directory '/opt/module/hive/datas/distribute-result' select * from emp distribute by deptno sort by empno desc; -- 假设 reduce = 3 ；
-- 先按照deptno进行分区(m = hashcode(deptno) % 3 , m值相等的数据进入同一个分区），然后在分区内进行局部排序，最后将查询的结果导出到本地指定的一个文件中。

1
2
3
4
5
6
7

1. 理解 ：当distribute by 和 sort by 的字段相同时，可以使用Cluster by 进行替代；
2. 不能指定排序的顺序，只能是升序。

-- 示例：
方式一 ：select * from emp  cluster by deptno ;
方式二 ：select * from emp distribute by deptno sort by deptno ;
-- 方式一和方式二是等价的。

1
2
3
4
5
6

-- 理解：
1） Hive 中的分区就是分目录 ；
2） 分区表对应一个hdfs文件系统的独立的文件；
3） 实际上是把一个大的数据集根据业务的需求分割成多个小的数集；
4） 在查询时，通过where语句进行条件筛选，指定数据在哪个分区内，提高查询的效率；
5)  同时用于解决数据倾斜的问题。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

-- 语法：
create table [if not exists] 表名 (
字段1 数据类型1，
字段2 数据类型2，
字段3 数据类型3，
    ...
)
partition by (字段1 数据类型1 ， 字段2 数据类型2 ，...) -- 分区，字段不能与表中属性字段相同
clustered by (字段1 ， 字段2 ， ...) -- 分桶，字段来自于表中的字段，所以是没有数据类型的。
row format delimited fields terminated by '\t'
-- 分区的字段也是可以作为表的字段使用。
-- 示例：
create table dept_partition(
deptno int ,
dname string ,
loc string
)
partition by (month string , day string) -- 二级分区，先按照月进行分区，在月中再根据day进行分区
row format delimited fields terminated by '\t'

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

方式一 ： 常规加载数据 load方式
-- 语法：
load data local inpath '本地数据路径' into table 表名 partition by (字段1'***',字段2  '***')
-- 示例：
load data local inpath '/opt/module/hive/datas/2020-04-04.log' into table dept_partition partition by (month='2020-04',day='04')

方式二：上传数据后修复 -- 因为单独上传数据到指定的目录下，hive是不能自动读取，需要进行数据的修复
第一步： 根据分区字段的信息，创建文件夹，此文件夹与表的路径相同
第二步： 本地的数据上传到指定的目录下，使用 【 hdfs dfs -put 本地数据路径 hdfs文件路径 】
第三步： 进行数据的修复 ，使用语句 【msck repair table 表名】

方式三： 上传数据后添加分区的方式 -- 该方式使用的情况最多
第一步和第二步与方式二完全相同；
第三步： 执行添加分区的方式
alter table 表名 add partition (字段1='***',字段2='***')

-- 实例：
第一步：hdfs dfs -mkdir -p  /user/hive/warehouse/dept_partition/month=2020-04/day=04 ;
第二步：hdfs dfs -put /opt/module/hive/datas/2020-04-04.logs /user/hive/warehouse/dept_partition/month=2020-04/day=04
第三步：
方式二： msck repair table dept_partition;
方式二： alter table dept_partition add partition (month='2020-04',day='04');

1
2
3
4
5

-- 语法：
查询语句 + where 分区字段='***' ;

-- 示例：
select  * from dept_partition where day='04' or day='05' ；