hive使用与优化

CREATE TABLE psn(
id int,
name string,
likes array<string>,
address map<string,string>
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
COLLECTION ITEMS TERMINATED BY '-'
MAP KEYS TERMINATED BY ':'
LINES TERMINATED BY '\n';

查看表的详细信息

desc formatted psn;

外部表：

CREATE EXTERNAL TABLE psnexternal(
id int,
name string,
likes array<string>,
address map<string,string>
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
COLLECTION ITEMS TERMINATED BY '-'
MAP KEYS TERMINATED BY ':'
LINES TERMINATED BY '\n'
LOCATION '/user/hive/externalTable';

B、hive 读检查

读检查的体现：

1. 将符合表结构解析规范的数据，上传到表对应的 hdfs 文件夹，在使用 select 查询的时候，可以查到上传的文件夹的数据

2. 将不符合表结构解析规范的数据，则没有成功解析的字段信息会显示为 null

1,小明1,lol-book-movie,beijing:chaoyang-shanghai:pudong
2,小明2,lol-book,beijing:chaoyang-shanghai:pudong
3,小明3,lol-movie,beijing:chaoyang
4,小明4,lol,beijing:chaoyang-shanghai:pudong
5,小明5,book-movie,beijing:chaoyang-shanghai:pudong
6,小明6,lol-book-movie,beijing:chaoyang-shanghai:pudong
7,小明7,movie,beijing:chaoyang-shanghai:pudong
8,小明8,lol-book-movie,shanghai:pudong
9,小明9,lol-book-movie,beijing:chaoyang-shanghai:pudong


-- 不符合psn 表的数据1
10,小明10,lol-book-movie,beijing:chaoyang-shanghai:pudong
11,小明11,
12,小明12,
13,小明13,
14,小明14,
15,小明15,
16,小明16,
17,小明17,
18,小明18,
19,小明19,

查询数据

select * from psn;

C. 静态分区

1.创建分区表

CREATE TABLE psnpartition(
id int,
name string,
likes array<string>,
address map<string,string>
)
PARTITIONED BY (age int, sex string)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
COLLECTION ITEMS TERMINATED BY '-'
MAP KEYS TERMINATED BY ':'
LINES TERMINATED BY '\n';

2.上传数据

load data

local -- 表示数据来自于linux服务器

inpath '/hiveData/myData/psn.txt' -- 需要加压的linux服务器上的数据路径

into table psnpartition -- 将数据加载到哪个表中

partition (age=18, sex='boy'); -- 手动设置导入的数据中 partition 所属的 age，sex 值

load data local inpath '/hiveData/myData/psn.txt' into table psnpartition partition (age=18, sex='boy');

加载第二部分，分区的数据

该分区的age=25, sex='boy'

load data local inpath '/hiveData/myData/psnpartition.txt' into table psnpartition partition (age=25, sex='boy');

-- 第一部分的数据
1,小明1,lol-book-movie,beijing:chaoyang-shanghai:pudong
2,小明2,lol-book,beijing:chaoyang-shanghai:pudong
3,小明3,lol-movie,beijing:chaoyang
4,小明4,lol,beijing:chaoyang-shanghai:pudong
5,小明5,book-movie,beijing:chaoyang-shanghai:pudong
6,小明6,lol-book-movie,beijing:chaoyang-shanghai:pudong
7,小明7,movie,beijing:chaoyang-shanghai:pudong
8,小明8,lol-book-movie,shanghai:pudong
9,小明9,lol-book-movie,beijing:chaoyang-shanghai:pudong

-- 第二部分的数据
10,小红100,lol,beijing:chaoyang
11,小红111,lol,henan:chaoyang
12,小红122,lol,beijing:chaoyang
13,小红133,lol,henan:chaoyang
14,小红144,lol,beijing:chaoyang
15,小红155,lol,henan:chaoyang
16,小红166,lol,beijing:chaoyang
17,小红177,lol,henan:chaoyang
18,小红188,lol,beijing:chaoyang
19,小红199,lol,beijing:chaoyang

3. 查询数据

和正常的查询操作没有任何区别，将分区的属性看成正常的字段进行查询操作。

SELECT * FROM psnpartition WHERE age > 18;

4.删除分区

alter table psnpartition drop partition (sex='boy');

如果有多个相同的分区，在删除的时候没有指定具体删除的是那一个，则会将所有等于该属性的分区删除。

5.添加分区

alter table psnpartition add partition(age=10, sex='man');

通过这种方式添加的分区，里面是不存在数据的，所添加的分区属性必须是，在创建表的时候定义过的分区字段。

D. 通过查询插入数据

通过这种方式进行数据的插入，需要跑 MapReduce 任务。

创建出需要导入数据的表，创建出源数据表并添加数据

可以一次性的对多张表插入数据

在进行数据插入的时候，从 psn 中只查询出几个字段，psn1 表的字段必须有几个字段

-- 表
CREATE TABLE psn(
id int,
name string,
likes array<string>,
address map<string,string>
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
COLLECTION ITEMS TERMINATED BY '-'
MAP KEYS TERMINATED BY ':'
LINES TERMINATED BY '\n';

-- 表1
CREATE TABLE psn1(
id int,
name string,
likes array<string>
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
COLLECTION ITEMS TERMINATED BY '-'
LINES TERMINATED BY '\n';

-- 表2
CREATE TABLE psn2(
id int,
name string,
address map<string,string>
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
MAP KEYS TERMINATED BY ':'
LINES TERMINATED BY '\n';

-- 插入数据
FROM psn
INSERT OVERWRITE TABLE psn1 SELECT id,name,likes
INSERT OVERWRITE TABLE psn2 SELECT id,name,address;

跑 MapReduce 任务

E. 正则匹配，加载数据

SERDEPROPERTIES 序列化，反序列化

CREATE TABLE logtable(
    host string,
    identity string,
    t_user string,
    time string,
    request string,
    referer string,
    agent string
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES(
    "input.regex" = "([^ ]*) ([^ ]*) ([^ ]*) \\[(.*)\\] \"(.*)\" (-|[0-9]*) (-|[0-9]*)"
)
STORED AS TEXTFILE;

-- 正则表达式的对应关系
([^ ]*)      ([^ ]*)  ([^ ]*)    \\[(.*)\\]                   \"(.*)\"                     (-|[0-9]*)    (-|[0-9]*)
192.168.57.4 -        -          [29/Feb/2016:18:14:35 +0800] "GET /bg-upper.png HTTP/1.1" 304            -

数据

192.168.57.4 - - [29/Feb/2016:18:14:35 +0800] "GET /bg-upper.png HTTP/1.1" 304 -
192.168.57.4 - - [29/Feb/2016:18:14:35 +0800] "GET /bg-nav.png HTTP/1.1" 304 -
192.168.57.4 - - [29/Feb/2016:18:14:35 +0800] "GET /asf-logo.png HTTP/1.1" 304 -
192.168.57.4 - - [29/Feb/2016:18:14:35 +0800] "GET /bg-button.png HTTP/1.1" 304 -
192.168.57.4 - - [29/Feb/2016:18:14:35 +0800] "GET /bg-middle.png HTTP/1.1" 304 -
192.168.57.4 - - [29/Feb/2016:18:14:36 +0800] "GET / HTTP/1.1" 200 11217
192.168.57.4 - - [29/Feb/2016:18:14:36 +0800] "GET / HTTP/1.1" 200 11217
192.168.57.4 - - [29/Feb/2016:18:14:36 +0800] "GET /tomcat.css HTTP/1.1" 304 -
192.168.57.4 - - [29/Feb/2016:18:14:36 +0800] "GET /tomcat.png HTTP/1.1" 304 -
192.168.57.4 - - [29/Feb/2016:18:14:36 +0800] "GET /asf-logo.png HTTP/1.1" 304 -
192.168.57.4 - - [29/Feb/2016:18:14:36 +0800] "GET /bg-middle.png HTTP/1.1" 304 -
192.168.57.4 - - [29/Feb/2016:18:14:36 +0800] "GET /bg-button.png HTTP/1.1" 304 -
192.168.57.4 - - [29/Feb/2016:18:14:36 +0800] "GET /bg-nav.png HTTP/1.1" 304 -
192.168.57.4 - - [29/Feb/2016:18:14:36 +0800] "GET /bg-upper.png HTTP/1.1" 304 -
192.168.57.4 - - [29/Feb/2016:18:14:36 +0800] "GET / HTTP/1.1" 200 11217
192.168.57.4 - - [29/Feb/2016:18:14:36 +0800] "GET /tomcat.css HTTP/1.1" 304 -
192.168.57.4 - - [29/Feb/2016:18:14:36 +0800] "GET /tomcat.png HTTP/1.1" 304 -
192.168.57.4 - - [29/Feb/2016:18:14:36 +0800] "GET / HTTP/1.1" 200 11217
192.168.57.4 - - [29/Feb/2016:18:14:36 +0800] "GET /tomcat.css HTTP/1.1" 304 -
192.168.57.4 - - [29/Feb/2016:18:14:36 +0800] "GET /tomcat.png HTTP/1.1" 304 -
192.168.57.4 - - [29/Feb/2016:18:14:36 +0800] "GET /bg-button.png HTTP/1.1" 304 -
192.168.57.4 - - [29/Feb/2016:18:14:36 +0800] "GET /bg-upper.png HTTP/1.1" 304 -

加载数据

load data local inpath '/hiveData/myData/lodatable.txt' into table lodatable;

查询验证

F. beeline 客户端

使用 hiveserver2 的方式启动服务端的服务

ss -nal 查看服务端口是否开启

如果在启动客户端的时候出现这种报错，请看问题2

处理完之后正常的连接

注意添加hive-jdbc 的版本和 hive 的版本一致

<dependencies>
        <dependency>
            <groupId>org.apache.hive</groupId>
            <artifactId>hive-jdbc</artifactId>
            <version>3.1.2</version>
            <exclusions>
                <exclusion>
                    <groupId>org.eclipse.jetty.aggregate</groupId>
                    <artifactId>*</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
    </dependencies>

使用 jdbc 进行连接查询操作

import java.sql.*;

/**
 * 服务端使用 hiveserver2 启动服务
 *
 * Create by yang_zzu on 2020/9/11 on 10:38
 */
public class HiveJdbcClient {
    private static String driverName = "org.apache.hive.jdbc.HiveDriver";

    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        Class.forName("org.apache.hive.jdbc.HiveDriver");
        Connection conn = DriverManager.getConnection("jdbc:hive2://192.168.232.100:10000/default", "root", "");
        Statement stmt = conn.createStatement();
        String sql = "select * from psn limit 5 ";

        try {
            ResultSet res = stmt.executeQuery(sql);
            while (res.next()) {
                System.out.println(res.getString(1) + "-" + res.getString("name"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            if (stmt != null) {
                stmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
        if (stmt != null) {
            stmt.close();
        }
        if (conn != null) {
            conn.close();
        }

    }
}

G. UDF 函数

使用 metastore 开启hive 服务端

hive --service metastore

import org.apache.hadoop.hive.ql.exec.UDF;
import org.apache.hadoop.io.Text;

/**
 * udf 函数
 * Create by yang_zzu on 2020/9/11 on 14:00
 */
public class FirstFunction extends UDF {

    public Text evaluate(final Text s) {
        if (s == null) {
            return null;
        }
        String string = s.toString().substring(0, 3) + "*********";

        return new Text(string);

    }

}

pom文件

<dependencies>
        <dependency>
            <groupId>org.apache.hive</groupId>
            <artifactId>hive-jdbc</artifactId>
            <version>3.1.2</version>
            <exclusions>
                <exclusion>
                    <groupId>org.eclipse.jetty.aggregate</groupId>
                    <artifactId>*</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>org.apache.hive</groupId>
            <artifactId>hive-exec</artifactId>
            <version>3.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.glassfish</groupId>
            <artifactId>javax.el</artifactId>
            <version>3.0.1-b06</version>
        </dependency>
    </dependencies>

将jar包添加到 hive仓库

add jar /hiveData/myData/hello.jar;

创建函数 helloworld , 该函数是临时函数，退出该hive 会话，则 helloworld 函数失效

create temporary function helloworld as 'com.sys.hive.FirstFunction';

H. like 方式创建表结构

like 的方式创建表，只能创建表的结构，不能复制数据

create table psnlike like psn;

i. 设置hive 属性

1. 命令行的方式设置，

这种设置的生命周期为当前hive会话，退出则设置的内容消失

当命令行后面不带有参数的时候，是对属性配置内容的查看

-- 查看标题头的属性设置
set hive.cli.print.header;

当命令行后面带有参数的时候，是进行属性的设置

-- 设置hive显示标题头
set hive.cli.print.header=true;

退出hive 会话再次登录

2. 使用 .hiverc 配置文件

这种方式的配置，是在hive 客户端启动的时候自动加载进行配置

 vim .hiverc

关闭客户端再次启动，已经设置为true

J. 动态分区

动态分区，起始和静态分区没有什么大的区别，动态分区的时候，需要开启一些配置，加载数据的不需要手动的进行分区的指定，会更具分区的属性信息自动的进行数据的分配

编辑 .hiverc 文件设置属性

set hive.cli.print.header=true;
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nostrict;
set hive.exec.max.dynamic.partitions.pernode=100;
set hive.exec.max.dynamic.partitions=1000;
set hive.exec.max.created.files=100000;

每个配置的解释信息

-- 设置hive显示标题头
set hive.cli.print.header=true;
-- 开启动态分区
set hive.exec.dynamic.partition=true;
-- 至少有一个分区是静态分区
set hive.exec.dynamic.partition.mode=nostrict;
-- 每一个执行mr节点上，允许创建的动态分区的最大数量(默认值:100)
set hive.exec.max.dynamic.partitions.pernode=100;
-- 所有执行mr节点上，允许创建的所有动态分区的最大数量(默认值:1000)
set hive.exec.max.dynamic.partitions=1000;
-- 所有的mr job允许创建的文件的最大数量(默认值:100000)
set hive.exec.max.created.files=100000;

创建动态分区的表，和正常的分区表一样

CREATE TABLE psndynamic(
id int,
name string,
likes array<string>,
address map<string,string>
)
partitioned by(age int, sex string)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
COLLECTION ITEMS TERMINATED BY '-'
MAP KEYS TERMINATED BY ':'
LINES TERMINATED BY '\n';

创建原始数据表

CREATE TABLE psndata(
id int,
name string,
age int,
sex string,
likes array<string>,
address map<string,string>
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
COLLECTION ITEMS TERMINATED BY '-'
MAP KEYS TERMINATED BY ':'
LINES TERMINATED BY '\n';

原始数据

1,小明1,18,man,lol-book-movie,beijing:chaoyang-shanghai:pudong
2,小明2,18,man,lol-book,beijing:chaoyang-shanghai:pudong
3,小明3,18,boy,lol-movie,beijing:chaoyang
4,小明4,18,man,lol,beijing:chaoyang-shanghai:pudong
5,小明5,18,boy,book-movie,beijing:chaoyang-shanghai:pudong
6,小明6,18,man,lol-book-movie,beijing:chaoyang-shanghai:pudong
7,小明7,18,boy,movie,beijing:chaoyang-shanghai:pudong
8,小明8,18,man,lol-book-movie,shanghai:pudong
9,小明9,18,man,lol-book-movie,beijing:chaoyang-shanghai:pudong
11,小明11,28,man,lol-book-movie,beijing:chaoyang-shanghai:pudong
12,小明12,28,man,lol-book,beijing:chaoyang-shanghai:pudong
13,小明13,28,boy,lol-movie,beijing:chaoyang
14,小明14,28,man,lol,beijing:chaoyang-shanghai:pudong
15,小明15,28,boy,book-movie,beijing:chaoyang-shanghai:pudong
16,小明16,28,man,lol-book-movie,beijing:chaoyang-shanghai:pudong
17,小明17,28,boy,movie,beijing:chaoyang-shanghai:pudong
18,小明18,28,man,lol-book-movie,shanghai:pudong
19,小明19,28,boy,lol-book-movie,beijing:chaoyang-shanghai:pudong

将原始数据加载到psndata 表

load data local inpath '/hiveData/myData/psndata.txt' into table psndata;

psndynamic 表加载数据

注意：

select 后面查询的字段顺序，要和插入的表的的字段结构保持一致，分区的字段要放在后面，且和建表时候指定的分区字段顺序保持一致

否则在进行数据插入的时候，会出现某些字段为null 的情况

因为hive 是读检查，所以在进行插入的时候，如果字段类型不匹配，也能够正常的写入到文件，但是在读取的时候不能正常的读取数据，所以显示 null

可以看到，age 这个字段是存在的，但是查询的时候显示的为空

1. 不指定分区，这个时候不会执行 reduce 操作

overwrite 表示的是覆盖数据

-- 覆盖添加数据，写法一，from 放在上面
from psndata
insert overwrite table psndynamic partition(age, sex)  
select id, name, likes, address, age, sex distribute by age, sex;

-- 直接插入数据，写法二，正常的查询操作
INSERT INTO TABLE psndynamic
PARTITION (sex, age)
select id, name, likes, address, age, sex FROM psndata where age<18;

2. 指定分区，这个时候会执行 reduce 的操作

overwrite 表示的是覆盖数据

-- 覆盖添加数据，写法一，from 放在上面
from psndata
insert overwrite table psnpartition partition(age, sex)  
select id, name, likes, address, age, sex distribute by age, sex;

-- 直接插入数据，写法二，正常的查询操作
INSERT INTO TABLE psnpartition
PARTITION (sex, age)
select id, name, likes, address, age, sex FROM psndata where age<18;

K.分桶

分桶表是对列值取哈希值的方式，将不同数据放到不同文件中存储。

对于hive中每一个表、分区都可以进一步进行分桶。

由列的哈希值除以桶的个数来决定每条数据划分在哪个桶中。

分桶，只适用于：数据抽样（ sampling ）、map-join

-- 开启分桶
set hive.enforce.bucketing = true;

我这是为了省劲，将属性配置放到 .hiverc 文件中，在生产环境的时候，不建议这样进行配置，因为每一个项目所需要的环境和配置都不太一样

set hive.cli.print.header=true;
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nostrict;
set hive.exec.max.dynamic.partitions.pernode=100;
set hive.exec.max.dynamic.partitions=1000;
set hive.exec.max.created.files=100000;
set hive.enforce.bucketing = true;

创建数据表

CREATE TABLE bucketdata(
id int,
name string,
age int
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
COLLECTION ITEMS TERMINATED BY '-'
MAP KEYS TERMINATED BY ':'
LINES TERMINATED BY '\n';

创建分桶表，桶按照 age 进行划分，个数为4

CREATE TABLE psnbucket(
id int,
name string,
age int
)
CLUSTERED BY(age) INTO 4 BUCKETS
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
COLLECTION ITEMS TERMINATED BY '-'
MAP KEYS TERMINATED BY ':'
LINES TERMINATED BY '\n';

数据

1,小红11,0
2,小红12,0
3,小红13,0
4,小红14,0
5,小红15,0
6,小红16,0
7,小红17,0
8,小红18,0
9,小红19,0
11,小红11,10
12,小红12,10
13,小红13,10
14,小红14,10
15,小红15,10
16,小红16,10
17,小红17,10
18,小红18,10
19,小红19,10
21,小红21,18
22,小红22,18
23,小红23,18
24,小红24,18
25,小红25,18
26,小红26,18
27,小红27,18
28,小红28,18
29,小红29,18
31,小红31,24
32,小红32,24
33,小红33,24
34,小红34,24
35,小红35,24
36,小红36,24
37,小红37,24
38,小红38,24
39,小红39,24
41,小红41,28
42,小红42,28
43,小红43,28
44,小红44,28
45,小红45,28
46,小红46,28
47,小红47,28
48,小红48,28
49,小红49,28

数据表中加载数据

load data local inpath '/hiveData/myData/bucketdata.txt' into table bucketdata;

向分桶表中插入数据

INSERT into TABLE psnbucket
SELECT id, name, age from bucketdata WHERE name like '小红%';

执行完后，看到有两个桶里面是没有分到数据

进行数据抽样

select id,name,age from psnbucket tablesample(bucket 2 out of 4 on age);

bucket 2 out of 4 on age
表示的是从 2 号桶开始抽样，抽样的桶的个数为桶的总数/4 （可见简单的理解为这样）如果想要了解更具体的，可以查看 tablesample 的抽样方法

L. 视图

视图，可以简单的看成一个表，删除、查看，都和表一样

查询操作，没有创建视图的时候，sql语句

select
count(distinct(myCol1)), count(distinct(myCol2)) from psn
LATERAL VIEW explode(likes) myTable1 AS myCol1
LATERAL VIEW explode(address) myTable2 AS myCol2, myCol3;

因为 address 是 map 键值对，所以需要两个变量 myCol2, myCol3 一个代表 key 一个代表 value

创建视图

CREATE VIEW v_psn
comment '查询psn表的id,name'
as select id,name from psn;

查看视图信息

desc formatted v_psn;

查询，使用视图进行查询操作

select * from v_psn;

删除视图

DROP VIEW v_psn;

查看所有视图和表

show tables;

M.索引

创建索引

CREATE INDEX psn_name on table psn (name)
AS 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler'
with deferred rebuild
in table t1_index_table;

as：指定索引器；
in table：指定索引表，若不指定默认生成在default__psn_t1_index__表中

在进行索引创建的时候出现了不识别 create index 不知道是我安装的问题还是其他原因造成的，没有解决

对表重建索引

ALTER INDEX psn_name ON psn REBUILD;

查看索引信息

select * from psn_name;

查看表，存在的索引

show idnex on psn;

删除索引

DROP INDEX IF EXISTS psn_name ON psn;

N. hive的运行方式

1.hive 会话执行linux命令

在 hive 环境中执行 hdfs 语句

dfs -cat /user/hive/warehouse/psnbucket/000002_0;

强行与 linux 进行交互

! cat /hiveData/myData/word.txt;

2.bash会话执行 hive 命令

hive -e 'select * from psn'

hive -S -e 'select * from psn'

使用 -S 不显示下面这两处，没什么作用

将查询结果输出到文件中

hive -e 'select * from psn' >> bbb.txt

cat bbb.txt

创建文件

执行文件中的sql语句

hive -f hive.txt

hive -i hive.txt

表示的是在执行完sql 之后会进入到 hive 会话。

3.在 hive 中调用 linux 文件执行

在使用 source hive.txt;

的时候，启动 hive会话的时候必须在文件的路径下，否则无法执行，文件中的sql 语句

O. hive 优化

把Hive SQL 当做Mapreduce程序去优化

显示 SQL 的执行计划

explain extended select count(id) from psn;

加载文件的最大值

set hive.exec.mode.local.auto.inputbytes.max;

a.严格模式：

1、对于分区表，必须添加where对于分区字段的条件过滤；

2、order by语句必须包含limit输出限制；

3、限制执行笛卡尔积的查询。

 set hive.mapred.mode;

设置为严格模式后，

对存在分区的表进行排序的时候 where 后面必须要有分区的字段条件，且要有 limit

select * from psnpartition where age>10 order by age limit 10;

对不存分区的表进行排序的时候，where 后面必须有添加，且要有 limit

select * from psnpartition where name like '小%' order by age limit 30;

我这里又和别人的不一样的，我这里显示的是未定义，其他人的显示的是默认为：nonstrict非严格模式

-- 开启本地模式
set hive.exec.mode.local.auto=true;
-- 加载文件的最大值，大于该值，则以集群的方式运行(默认128M)
set hive.exec.mode.local.auto.inputbytes.max=134217728;
-- 开启并行模式，如果系统资源不足的情况下，不建议开启并行模式
-- 如果资源足够的情况下，在执行SQL的时候，可能有多个子查询一起查询(默认值false)
set hive.exec.parallel=true;
-- 一次SQL计算中允许并行执行的job个数的最大值(默认值8)
set hive.exec.parallel.thread.number=8;
-- 开启严格模式(默认为：nonstrict非严格模式)
set hive.mapred.mode=strict;

编辑 .hiverc

set hive.exec.mode.local.auto=true;
set hive.exec.mode.local.auto.inputbytes.max=134217728;
set hive.exec.parallel=true;
set hive.exec.parallel.thread.number=8;
set hive.mapred.mode=strict;

b.排序

Cluster By不能通过asc、desc的方式指定排序规则

Cluster By - 相当于 Sort By + Distribute By

select * from psnpartition where age>-1 distribute by age sort by age desc;

c.join

Join计算时，将小表（驱动表）放在join的左边

可以开启自动的mapjoin

-- 开启自动mapjoin(默认为true)
set hive.auto.convert.join=true;
-- 大表小表判断的阈值，如果表的大小小于该值则会被加载到内存中运行(默认25000000)
set hive.mapjoin.smalltable.filesize=25000000;
-- 是否忽略mapjoin hint 即mapjoin标记(默认为true)
set hive.ignore.mapjoin.hint=true;
-- 将普通的join转化为普通的mapjoin时，是否将多个mapjoin转化为一个mapjoin(默认为true)
set hive.auto.convert.join.noconditionaltask=true;
-- 将多个mapjoin转化为一个mapjoin时，其表的最大值(默认10000000)
set hive.auto.convert.join.noconditionaltask.size=10000000;

编辑 .hiverc

设置的时候，一般只设置前面三个参数，后面两个保持默认

set hive.auto.convert.join=true;
set hive.mapjoin.smalltable.filesize=25000000;
set hive.ignore.mapjoin.hint=true;
set hive.auto.convert.join.noconditionaltask=true;
set hive.auto.convert.join.noconditionaltask.size=10000000;

手动进行mapjoin 这个时候如果有分区表，因为开启了严格模式，所以这里必须要有分区条件

SELECT  /*+ MAPJOIN(psn) */ psn.id, psn.name,  psnpartition.name, psnpartition.age, psnpartition.id, psnpartition.address
FROM  psn  JOIN  psnpartition ON  psn.name = psnpartition.name where psnpartition.age>10;

没有分区表，则正常的进行 join on 就行了

SELECT  /*+ MAPJOIN(psn) */ psn.id, psn.name,  psndata.name, psndata.age, psndata.id 
FROM  psn  JOIN  psndata ON  psn.name  =  psndata.name;

sql语句中 /*+ MAPJOIN(psn) */ 这种是固定的写法不能删除

在开启自动mapjoin 后，不需要指定那个是小表，Hive自动对左边的表统计量，如果是小表就加入内存，即对小表使用Map join

SELECT psn.id, psn.name, psndata.name, psndata.age, psndata.id 
FROM psn JOIN psndata ON psn.name = psndata.name;

d.聚合

-- 开启在Map端的聚合(默认为true)
set hive.map.aggr=true;
-- map端group by执行聚合时处理的多少行数据(默认100000)
set hive.groupby.mapaggr.checkinterval=100000;
-- 进行聚合的最小比例(默认0.5)(预先对100000条数据做聚合,若聚合之后的数据量/100000的值大于该配置0.5,则不会聚合)
set hive.map.aggr.hash.min.reduction=0.5;
-- map端聚合使用的内存的最大值(默认0.5)
set hive.map.aggr.hash.percentmemory=0.5;
-- map端做聚合操作是hash表的最大可用内容，大于该值则会触发flush(默认0.9)
set hive.map.aggr.hash.force.flush.memory.threshold=0.9;
-- 是否对GroupBy产生的数据倾斜做优化，默认为false
set hive.groupby.skewindata=true;

编辑 .hiverc 文件

set hive.map.aggr=true;
set hive.groupby.mapaggr.checkinterval=100000;
set hive.map.aggr.hash.min.reduction=0.5;
set hive.map.aggr.hash.percentmemory=0.5;
set hive.map.aggr.hash.force.flush.memory.threshold=0.9;
set hive.groupby.skewindata=true;

e. map、 reduce 数量

如果进行了分桶操作，则 reduce 的数量等于桶的数量

-- map 数量
-- 一个split的最大值，即每个map处理文件的最大值(默认256000000)
set mapred.max.split.size=256000000;
-- 一个节点上split的最小值(默认1)
set mapred.min.split.size.per.node=1;
-- 一个机架上split的最小值(默认1)
set mapred.min.split.size.per.rack=1;

-- reduce 数量
-- 强制指定reduce任务的数量(默认-1)
set mapred.reduce.tasks=-1;
-- 每个reduce任务处理的数据量(默认256000000)
set hive.exec.reducers.bytes.per.reducer=256000000;
-- 每个任务最大的reduce数(默认1009)
set hive.exec.reducers.max=1009;

修改 .hiverc 文件

set mapred.max.split.size=256000000;
set mapred.min.split.size.per.node=1;
set mapred.min.split.size.per.rack=1;
set mapred.reduce.tasks=-1;
set hive.exec.reducers.bytes.per.reducer=256000000;
set hive.exec.reducers.max=1009;

f. jvm 重用

适用场景： 1、小文件个数过多 2、task个数过多

修改 .hiverc 文件

set mapred.job.reuse.jvm.num.tasks=n;

这个配置默认是没有定义的

设置开启之后，task插槽会一直占用资源，不论是否有task运行，直到所有的task即整个job全部执行完成时，才会释放所有的task插槽资源！

问题

问题1：NoViableAltException(308@[2389:1: columnNameTypeOrConstraint : ( ( tableConstraint ) | ( columnNameTypeConstraint ) );])

建表的时候，这里虽然出现了列名类型或约束怎么样，但是在sql 语句中没有发现有什么异常的内容

看最后一句的失败原因是在第5行的第4列，找到这个地方，发现列名称写的是 time

time 可能和系统中的字段出现了重复，导致一直报错，将 time 修改为 t_time 正常的运行

CREATE TABLE logtable(
    host string,
    identity string,
    t_user string,
    time string,
    request string,
    referer string,
    agent string
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES(
    "input.regex" = "([^ ]*) ([^ ]*) ([^ ]*) \\[(.*)\\] \"(.*)\" (-|[0-9]*) (-|[0-9]*)"
)
STORED AS TEXTFILE;

NoViableAltException(308@[2389:1: columnNameTypeOrConstraint : ( ( tableConstraint ) | ( columnNameTypeConstraint ) );])
at org.antlr.runtime.DFA.noViableAlt(DFA.java:158)
at org.antlr.runtime.DFA.predict(DFA.java:116)
at org.apache.hadoop.hive.ql.parse.HiveParser.columnNameTypeOrConstraint(HiveParser.java:34044)
at org.apache.hadoop.hive.ql.parse.HiveParser.columnNameTypeOrConstraintList(HiveParser.java:29840)
at org.apache.hadoop.hive.ql.parse.HiveParser.createTableStatement(HiveParser.java:6662)
at org.apache.hadoop.hive.ql.parse.HiveParser.ddlStatement(HiveParser.java:4295)
at org.apache.hadoop.hive.ql.parse.HiveParser.execStatement(HiveParser.java:2494)
at org.apache.hadoop.hive.ql.parse.HiveParser.statement(HiveParser.java:1420)
at org.apache.hadoop.hive.ql.parse.ParseDriver.parse(ParseDriver.java:220)
at org.apache.hadoop.hive.ql.parse.ParseUtils.parse(ParseUtils.java:74)
at org.apache.hadoop.hive.ql.parse.ParseUtils.parse(ParseUtils.java:67)
at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:616)
at org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:1826)
at org.apache.hadoop.hive.ql.Driver.compileAndRespond(Driver.java:1773)
at org.apache.hadoop.hive.ql.Driver.compileAndRespond(Driver.java:1768)
at org.apache.hadoop.hive.ql.reexec.ReExecDriver.compileAndRespond(ReExecDriver.java:126)
at org.apache.hadoop.hive.ql.reexec.ReExecDriver.run(ReExecDriver.java:214)
at org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriver.java:239)
at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:188)
at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:402)
at org.apache.hadoop.hive.cli.CliDriver.executeDriver(CliDriver.java:821)
at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:759)
at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:683)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.hadoop.util.RunJar.run(RunJar.java:323)
at org.apache.hadoop.util.RunJar.main(RunJar.java:236)
FAILED: ParseException line 5:4 cannot recognize input near 'time' 'string' ',' in column name or constraint

问题2：User: root is not allowed to impersonate abc (state=08S01,code=0)

使用 beeline 连接的时候，出现

java.lang.RuntimeException: org.apache.hadoop.ipc.RemoteException(org.apache.hadoop.security.authorize.AuthorizationException): User: root is not allowed to impersonate abc (state=08S01,code=0)

修改 hadoop 的core-site.xml 文件

<configuration>
  <property>
    <name>fs.defaultFS</name>
    <value>hdfs://mycluster</value>
  </property>
  <property>
    <name>hadoop.tmp.dir</name>
    <value>/var/yang/hadoop/ha</value>
  </property>
  <property>
    <name>hadoop.http.staticuser.user</name>
    <value>root</value>
  </property>
  <property>
   <name>ha.zookeeper.quorum</name>
   <value>yang101:2181,yang102:2181,yang103:2181</value>
  </property>
  <property>
    <name>hadoop.proxyuser.root.hosts</name>
    <value>*</value>
  </property>
  <property>
    <name>hadoop.proxyuser.root.groups</name>
    <value>*</value>
  </property>
</configuration>

将 core-site.xml 分发到其他的节点

关闭hadoop 服务

stop-dfs.sh

开启hadoop

start-dfs.hs

开启yarn

start-yarn.sh

问题3：Required field 'serverProtocolVersion' is unset!

Required field 'serverProtocolVersion' is unset!

hive 的版本要和 hive-jdbc 的版本保持一致

<dependencies>
        <dependency>
            <groupId>org.apache.hive</groupId>
            <artifactId>hive-jdbc</artifactId>
            <version>3.1.2</version>
            <exclusions>
                <exclusion>
                    <groupId>org.eclipse.jetty.aggregate</groupId>
                    <artifactId>*</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
    </dependencies>

问题4：Error running 'HiveJdbcClient': Command line is too long. Shorten command line for HiveJdbcClient or also for Application default configuration.

在将 hive-jdbc 的版本修改后，出现下面的问题

Error running 'HiveJdbcClient': Command line is too long. Shorten command line for HiveJdbcClient or also for Application default configuration.

修改 run configuration 的配置信息

正常执行

问题5：Failure to find org.glassfish:javax.el:pom:3.0.1-b06-SNAPSHOT in

Failure to find org.glassfish:javax.el:pom:3.0.1-b06-SNAPSHOT in

进到本地存储maven 仓库，将这几个的pom 文件的名称，进行修改

实践项目：

1. 统计掉话率

a.数据

粘贴部分数据，可以自己伪造更多的数据

record_time：通话时间

imei：基站编号

cell：手机编号

drop_num：掉话的秒数

duration：通话持续总秒数

record_time	imei	cell	ph_num	call_num	drop_num	duration	drop_rate	net_type	erl
2011-07-13 00:00:00+08	356966	29448-37062	0	0	0	0	0	G	0
2011-07-13 00:00:00+08	352024	29448-51331	0	0	0	0	0	G	0
2011-07-13 00:00:00+08	353736	29448-51331	0	0	0	0	0	G	0
2011-07-13 00:00:00+08	353736	29448-51333	0	0	0	0	0	G	0
2011-07-13 00:00:00+08	351545	29448-51333	0	0	0	0	0	G	0
2011-07-13 00:00:00+08	353736	29448-51343	1	0	0	8	0	G	0
2011-07-13 00:00:00+08	359681	29448-51462	0	0	0	0	0	G	0
2011-07-13 00:00:00+08	354707	29448-51462	0	0	0	0	0	G	0
2011-07-13 00:00:00+08	356137	29448-51470	0	0	0	0	0	G	0
2011-07-13 00:00:00+08	352739	29448-51971	0	0	0	0	0	G	0
2011-07-13 00:00:00+08	354154	29448-51971	0	0	0	0	0	G	0
2011-07-13 00:00:00+08	127580	29448-51971	0	0	0	0	0	G	0
2011-07-13 00:00:00+08	354264	29448-51973	0	0	0	0	0	G	0
2011-07-13 00:00:00+08	354733	29448-51973	1	0	0	36	0	G	0
2011-07-13 00:00:00+08	356807	29448-51973	0	0	0	0	0	G	0
2011-07-13 00:00:00+08	125470	29448-51973	1	0	0	13	0	G	0
2011-07-13 00:00:00+08	353530	29448-52061	1	0	0	46	0	G	0
2011-07-13 00:00:00+08	352417	29448-5231	1	0	0	2	0	G	0
2011-07-13 00:00:00+08	353419	29448-5231	0	0	0	0	0	G	0
2011-07-13 00:00:00+08	306416	29448-5231	0	0	0	0	0	G	0
2011-07-13 00:00:00+08	356208	29448-5233	0	0	0	0	0	G	0
2011-07-13 00:00:00+08	357238	29448-5233	1	0	0	21	0	G	0
2011-07-13 00:00:00+08	354154	29448-52541	0	0	0	0	0	G	0
2011-07-13 00:00:00+08	358662	29448-53050	0	0	0	0	0	G	0
2011-07-13 00:00:00+08	357470	29448-53523	0	0	0	0	0	G	0
2011-07-13 00:00:00+08	354555	29448-53523	0	0	0	0	0	G	0
2011-07-13 00:00:00+08	864301	29448-53871	0	0	0	0	0	G	0
2011-07-13 00:00:00+08	357727	29448-53871	0	0	0	0	0	G	0
2011-07-13 00:00:00+08	356049	29448-53871	0	0	0	0	0	G	0
2011-07-13 00:00:00+08	356569	29448-54853	0	0	0	0	0	G	0
2011-07-13 00:00:00+08	353257	29448-54874	0	0	0	0	0	G	0
2011-07-13 00:00:00+08	355287	29448-55671	0	0	0	0	0	G	0
2011-07-13 00:00:00+08	358675	29448-55672	0	0	0	0	0	G	0
2011-07-13 00:00:00+08	358212	29448-55672	0	0	0	0	0	G	0
2011-07-13 00:00:00+08	358086	29448-55672	0	0	0	0	0	G	0
2011-07-13 00:00:00+08	125470	29448-55672	1	0	0	27	0	G	0
2011-07-13 00:00:00+08	865524	29448-55813	0	0	0	0	0	G	0
2011-07-13 00:00:00+08	863149	29448-55823	0	0	0	0	0	G	0
2011-07-13 00:00:00+08	860139	29448-55823	0	0	0	0	0	G	0
2011-07-13 00:00:00+08	352983	29448-55823	0	0	0	0	0	G	0

b. 建表

-- 数据表
create table cell_monitor(
record_time string,
imei string,
cell string,
ph_num int,
call_num int,
drop_num int,
duration int,
drop_rate DOUBLE,
net_type string,
erl string
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE;

-- 结果表
create table cell_drop_monitor(
imei string,
total_call_num int,
total_drop_num int,
d_rate DOUBLE
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE;

c. 加载数据

load data local inpath '/hiveData/myData/cell.csv' into table cell_monitor;

d. 进行sql 语句的计算

from cell_monitor cm
insert overwrite table cell_drop_monitor
select cm.imei ,sum(cm.drop_num),sum(cm.duration),sum(cm.drop_num)/sum(cm.duration) d_rate
group by cm.imei
sort by d_rate desc;

e. 结果

select * from cell_drop_monitor limit 10;

2. 统计单词

a.数据

hello world hadoop
hadoop hive bash
hive hello nginx
hbash hive hdfs

b. 建表

-- 数据存储
create table docs(line string);

-- 结果表
create table wc(word string, totalword int);

c. 加载数据

load data local inpath '/hiveData/myData/word.txt' into table docs;

d. sql 计算

from (select explode(split(line, ' ')) as word from docs) w
insert into table wc
select word, count(1) as totalword
group by word
order by totalword desc;