数据仓库之Hive

mac2026-06-04 10

文章目录

Hive 简介Hive 架构Hive 的架构Operator Hive 搭建模式单机模式分布式模式 Hive 之 DDLDDL 语法 Hive 表内部表外部表区别* Hive 分区分区建表添加分区表语法删除分区向指定分区添加数据语法查询执行分区语法Hive查询表的分区信息语法动态分区 Hive 之 DML*加载数据的方式更新操作 Hive SerDeHive 正则匹配 Hive BeelineHive JDBCHive 函数自定义 UDF Hive 案例struct 结构体WordCount基站掉话率统计 Hive 参数Hive 分桶分桶概念适用场景分桶操作分桶案例 Hive Laternal ViewHive 视图Hive 索引Hive 运行方式Hive 权限管理 Hive 优化*Hive抓取策略Hive运行方式并行计算严格模式Hive排序Hive JoinMap-Side聚合合并小文件控制Hive中Map以及Reduce的数量Hive- JVM重用

Hive 简介

Hive 数据仓库Hive 解释器，编译器，优化器Hive 运行时，元数据存储在关系型数据库里面

Hive 架构

CLI： command line interface 命令行接口

JDBC/ODBC: Java 连接数据库（MySQL、Oracle）

Web GUI： Hive web 用户界面

metastore：表、字段的约束

Driver： Driver 服务，负责 Hadoop 和 Hive 之间的联系（）

（1）用户接口主要有三个：CLI，Client 和 WUI。其中最常用的是CLI，Cli启动的时候，会同时启动一个Hive副本。Client是Hive的客户端，用户连接至Hive Server。在启动 Client模式的时候，需要指出Hive Server所在节点，并且在该节点启动Hive Server。 WUI是通过浏览器访问Hive。（2）Hive将元数据存储在数据库中，如mysql、derby。Hive中的元数据包括表的名字，表的列和分区及其属性，表的属性（是否为外部表等），表的数据所在目录等。（3）解释器、编译器、优化器完成HQL查询语句从词法分析、语法分析、编译、优化以及查询计划的生成。生成的查询计划存储在HDFS中，并在随后有MapReduce调用执行。（4）Hive的数据存储在HDFS中，大部分的查询、计算由MapReduce完成（包含*的查询，比如select * from tbl不会生成MapRedcue任务）。

Hive 的架构

编译器将一个Hive SQL转换操作符操作符是Hive的最小的处理单元每个操作符代表HDFS的一个操作或者一道MapReduce作业

Operator

Operator都是hive定义的一个处理过程 Operator都定义有: protected List <Operator<? extends Serializable >> childOperators; protected List <Operator<? extends Serializable >> parentOperators; protected boolean done; // 初始化值为false

ANTER 词法语法分析工具解析 SQL

Hive 搭建模式

单机模式

通过网络连接到一个数据库中

搭建环境准备：

HOST/SoftMySQLHivenode01*hode02*

搭建过程步骤：

安装 MySQL

# 安装 mysql 服务 yum install mysql-server -y # 输入 msyql 会出现这样的错误信息，原因是 mysqld 服务未启动 # ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) # 启动 mysqld 服务 service mysqld start # 配置 mysql 远程连接以及用户名和密码 grant all privileges on *.* to root@'%' identified by '123' with grant option; # 刷新权限 flush privileges; # 删除 mysql.user 表中的除了远程连接用户外其他用户的记录 delete from user where mysql.host != '%' # 查看 mysql 用户表 select host,user,password from mysql.user

安装 Hive

# 安装 hive # 上传 hive 压缩包并解压 tar -zxvf hive.x.y.z.tar.gz # 移动到 /opt/sxt 目录下 mv hive.x.y.z /opt/sxt # 配置 hive 环境变量,编辑 vi /etc/profile 文件 export HIVE_HOME=/opt/sxt/hive.x.y.z export PATH=$PATH:$HIVE_HOME/bin # 使 /etc/profile 生效 . /etc/profile # 输入 hive 命令，查看 hive 是否安装成功 # 修改配置文件 cp hive-.xml hive-site.xml vi hive-site.xml <property> <name>hive.metastore.warehouse.dir</name> <value>/user/hive_remote/warehouse</value> </property> <property> <name>javax.jdo.option.ConnectionURL</name> <value>jdbc:mysql://node01/hive_remote?createDatabaseIfNotExist=true</value> </property> <property> <name>javax.jdo.option.ConnectionDriverName</name> <value>com.mysql.jdbc.Driver</value> </property> <property> <name>javax.jdo.option.ConnectionUserName</name> <value>root</value> </property> <property> <name>javax.jdo.option.ConnectionPassword</name> <value>123</value> </property> # 更新 jar 资源 # 将 jline.jar 调整为高版本，同时将 hadoop 的低版本删除 cd $HODOOP_HOME/share/hadoop/yarn/lib/ rm -fr jline-0.9.94.jar cp $HIVE_HOME/lib/jline-2.12.jar ./ # 启动 hive

分布式模式

用于非Java客户端访问元数据库，在服务器端启动 MetaStoreServer，客户端利用 Thrift 协议通过MetaStoreServer访问元数据库

搭建环境准备：

mysqlhive-serverhive-clientnode01*node03*node04*

搭建步骤：

# 搭建分布式 Hive 是建立在单机模式之上 # 从之前的 node02 节点上拷贝 $HIVE_HOME 目录到 node03、node04 上 # 其中 node03 作为 Hive 服务端， node04 作为 Hive 客户端 scp -r apache-hive-1.2.1-bin/ root@node03:`pwd`/ scp -r apache-hive-1.2.1-bin/ root@node04:`pwd`/ # 配置 node03 node04 的 HIVE 环境变量 # 更新 jar 资源 # 将 jline.jar 调整为高版本，同时将 hadoop 的低版本删除 cd $HODOOP_HOME/share/hadoop/yarn/lib/ rm -fr jline-0.9.94.jar cp $HIVE_HOME/lib/jline-2.12.jar ./ # 在 node03 上启动 hive metastore 服务 hive --service metastore # 在 node04 上启用 hive 客户端 hive

配置环境变量的目的：

找可执行性文件方便其他框架或者服务使用。 eg: HIVE 通过 HADOOP 的环境变量连接到 Hadoop 上

Hive 之 DDL

官方文档地址：

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-Overview

DDL 语法

创建表 CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name -- (Note: TEMPORARY available in Hive 0.14.0 and later) [(col_name data_type [column_constraint_specification] [COMMENT col_comment], ... [constraint_specification])] [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] [SKEWED BY (col_name, col_name, ...) -- (Note: Available in Hive 0.10.0 and later)] ON ((col_value, col_value, ...), (col_value, col_value, ...), ...) [STORED AS DIRECTORIES] [ [ROW FORMAT row_format] [STORED AS file_format] | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)] -- (Note: Available in Hive 0.6.0 and later) ] [LOCATION hdfs_path] [TBLPROPERTIES (property_name=property_value, ...)] -- (Note: Available in Hive 0.6.0 and later) [AS select_statement]; -- (Note: Available in Hive 0.5.0 and later; not supported for external tables) CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name LIKE existing_table_or_view_name [LOCATION hdfs_path]; data_type : primitive_type | array_type | map_type | struct_type | union_type -- (Note: Available in Hive 0.7.0 and later) primitive_type : TINYINT | SMALLINT | INT | BIGINT | BOOLEAN | FLOAT | DOUBLE | DOUBLE PRECISION -- (Note: Available in Hive 2.2.0 and later) | STRING | BINARY -- (Note: Available in Hive 0.8.0 and later) | TIMESTAMP -- (Note: Available in Hive 0.8.0 and later) | DECIMAL -- (Note: Available in Hive 0.11.0 and later) | DECIMAL(precision, scale) -- (Note: Available in Hive 0.13.0 and later) | DATE -- (Note: Available in Hive 0.12.0 and later) | VARCHAR -- (Note: Available in Hive 0.12.0 and later) | CHAR -- (Note: Available in Hive 0.13.0 and later) array_type : ARRAY < data_type > map_type : MAP < primitive_type, data_type > struct_type : STRUCT < col_name : data_type [COMMENT col_comment], ...> union_type : UNIONTYPE < data_type, data_type, ... > -- (Note: Available in Hive 0.7.0 and later) row_format : DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char] [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char] [NULL DEFINED AS char] -- (Note: Available in Hive 0.13 and later) | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)] file_format: : SEQUENCEFILE | TEXTFILE -- (Default, depending on hive.default.fileformat configuration) | RCFILE -- (Note: Available in Hive 0.6.0 and later) | ORC -- (Note: Available in Hive 0.11.0 and later) | PARQUET -- (Note: Available in Hive 0.13.0 and later) | AVRO -- (Note: Available in Hive 0.14.0 and later) | JSONFILE -- (Note: Available in Hive 4.0.0 and later) | INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname column_constraint_specification: : [ PRIMARY KEY|UNIQUE|NOT NULL|DEFAULT [default_value]|CHECK [check_expression] ENABLE|DISABLE NOVALIDATE RELY/NORELY ] default_value: : [ LITERAL|CURRENT_USER()|CURRENT_DATE()|CURRENT_TIMESTAMP()|NULL ] constraint_specification: : [, PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE RELY/NORELY ] [, PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE RELY/NORELY ] [, CONSTRAINT constraint_name FOREIGN KEY (col_name, ...) REFERENCES table_name(col_name, ...) DISABLE NOVALIDATE [, CONSTRAINT constraint_name UNIQUE (col_name, ...) DISABLE NOVALIDATE RELY/NORELY ] [, CONSTRAINT constraint_name CHECK [check_expression] ENABLE|DISABLE NOVALIDATE RELY/NORELY ]

练习：

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 ':'; load data local inpath '/root/data/data' into table psn; -- 查看表结构 desc formatted 表名

Hive 表

内部表

create table psn3 ( id int, name string, likes array<string>, address map<string,string> ) row format delimited fields terminated by '\001' collection items terminated by '\002' map keys terminated by '\003';

外部表

create external table psn4 ( 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 ':' location '/data/hive/input/';

区别*

内部表 MANAGED外部表 EXTERNAL创建表时直接存储在默认的hdfs路径需要自己指定路径删除表时将数据和元数据全部删除只删除元数据，数据不删除

先有表，后有数据，使用内部表。先有数据，后有表，使用外部表。

注意：

删除外部表中不会删除 HDFS 中的数据Hive 读时检查（解耦，便于数据读取）; 关系数据库写时检查

Hive 分区

分区表的意义在于优化查询。查询时尽量利用分区字段。如果不使用分区字段，就会全部扫描。

注意：分区属于元数据，不能通过外部表直接从 HDFS 加载 Hive 中，必须在表定义时指定对应的partition字段

分区建表

a. 单分区建表语句：

create table day_table (id int, content string) partitioned by (dt string);

单分区表，按天分区，在表结构中存在 id，content，dt 三列。以 dt 为文件夹区分

b. 双分区建表语句：

create table day_hour_table (id int, content string) partitioned by (dt string, hour string);

双分区表，按天和小时分区，在表结构中新增加了 dt 和 hour 两列。先以 dt 为文件夹，再以 hour 子文件夹区分

添加分区表语法

（表已创建，在此基础上添加分区）： ALTER TABLE table_name ADD [IF NOT EXISTS] PARTITION partition_spec [LOCATION ‘location1’] partition_spec [LOCATION ‘location2’] …;

partition_spec: (partition_column = partition_col_value, partition_column = partition_col_value, …) 例： ALTER TABLE day_table ADD PARTITION (dt='2008-08-08', hour='08')

删除分区

LTER TABLE table_name DROP partition_spec, partition_spec,… partition_spec: : (partition_column = partition_col_value, partition_column = partition_col_value, …)

用户可以用 ALTER TABLE DROP PARTITION 来删除分区。内部表中、对应分区的元数据和数据将被一并删除。

例：

ALTER TABLE day_hour_table DROP PARTITION (dt='2008-08-08', hour='09');

向指定分区添加数据语法

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]

例：

-- 从 HDFS 中加载数据 LOAD DATA INPATH '/user/pv.txt' INTO TABLE day_hour_table PARTITION(dt='2008-08- 08', hour='08'); -- 从本地文件系统中加载数据 LOAD DATA local INPATH '/user/hua/*' INTO TABLE day_hour partition(dt='2010-07- 07');

当数据被加载至表中时，不会对数据进行任何转换。

Load 操作只是将数据复制至 Hive 表对应的位置。数据加载时在表下自动创建一个目录。

查询执行分区语法

SELECT day_table.* FROM day_table WHERE day_table.dt>= '2008-08-08';

分区表的意义在于优化查询。查询时尽量利用分区字段。如果不使用分区字段，就会全部扫描。

Hive查询表的分区信息语法

SHOW PARTITIONS day_hour_table;

预先导入分区数据，但是无法识别怎么办？

Msck repair table tablename

直接添加分区

动态分区

开启支持动态分区

set hive.exec.dynamic.partition=true;

默认：true

set hive.exec.dynamic.partition.mode=nostrict;

默认：strict（至少有一个分区列是静态分区）相关参数

set hive.exec.max.dynamic.partitions.pernode;

每一个执行mr节点上，允许创建的动态分区的最大数量(100)

set hive.exec.max.dynamic.partitions;

所有执行mr节点上，允许创建的所有动态分区的最大数量(1000)

set hive.exec.max.created.files;

所有的mr job允许创建的文件的最大数量(100000)

加载数据

create external table psn21( id int, name string, sex string, age int, likes array<string>, address map<string,string> ) row format delimited fields terminated by ',' collection items terminated by '-' map keys terminated by ':' location '/data/bucket/input'; create table psn22( 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 ':'; # hive 命令行中设置动态分区为非严格模式 set hive.exec.dynamic.partition.mode=nonstrict # 注意：参数的位置要对应 from psn21 insert overwrite table psn22 partition(age, sex) select id, name, likes, address, age,sex distribute by age, sex;

Hive 之 DML*

官方文档地址：

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DML#LanguageManualDML-HiveDataManipulationLanguage

加载数据的方式

Loading files into tables 从文件中加载数据

Hive does not do any transformation while loading data into tables. Load operations are currently pure copy/move operations that move datafiles into locations corresponding to Hive tables.

语法：

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)] LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)] [INPUTFORMAT 'inputformat' SERDE 'serde'] (3.0 or later)

注意：从 HDFS 中加载数据，数据发生移动，而从本地加载数据，数据发生拷贝。

Inserting data into Hive Tables from queries 从查询结果集中加载数据

Query Results can be inserted into tables by using the insert clause.

语法：

Standard syntax: INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] select_statement1 FROM from_statement; INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement; Hive extension (multiple inserts): FROM from_statement INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] select_statement1 [INSERT OVERWRITE TABLE tablename2 [PARTITION ... [IF NOT EXISTS]] select_statement2] [INSERT INTO TABLE tablename2 [PARTITION ...] select_statement2] ...; FROM from_statement INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 [INSERT INTO TABLE tablename2 [PARTITION ...] select_statement2] [INSERT OVERWRITE TABLE tablename2 [PARTITION ... [IF NOT EXISTS]] select_statement2] ...; Hive extension (dynamic partition inserts): INSERT OVERWRITE TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] ...) select_statement FROM from_statement; INSERT INTO TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] ...) select_statement FROM from_statement;

例子：

FROM psn INSERT OVERWRITE TABLE psn10 SELECT id,name insert into psn11 select id,likes insert overwrite local directory '/root/result' select * from psn;

更新操作

ACID 事务的特性三大范式

Hive SerDe

SerDe 用于做序列化和反序列化。

构建在数据存储和执行引擎之间，对两者实现解耦。 Hive通过 ROW FORMAT DELIMITED 以及 SERDE 进行内容的读写。

row_format : DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char] [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char] : SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]

Hive 正则匹配

CREATE TABLE logtbl ( 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;

Hive Beeline

提供了 JDBC 的访问方式

beenline 不能用于 DML 操作，只能执行一些查询操作

-- 第一种方式 beeline !connect jdbc:hive2://node04:10000/default root 123 -- 第二种方式 beeline -u connect jdbc:hive2://node04:10000/default -n root

Hive JDBC

public class HiveDemo { // private final static String driver = "org.apache.hive.jdbc.HiveDriver"; // private final static String url = "jdbc:hive2://node04:10000/default"; // private final static String username = "root"; // private final static String password = "123"; public static void main(String[] args) { try { Properties prop = new Properties(); prop.load(new FileInputStream(new File("jdbc.properties"))); String driver = prop.getProperty("driver"); String url = prop.getProperty("url"); String username = prop.getProperty("username"); String password = prop.getProperty("password"); Class.forName(driver); Connection conn = DriverManager.getConnection(url,username,password); Statement st = conn.createStatement(); String sql = "select * from psn"; ResultSet rs = st.executeQuery(sql); while(rs.next()){ int id = rs.getInt("id"); String name = rs.getString("name"); System.out.println(id+"\t"+name); } } catch (Exception e) { e.printStackTrace(); } } }

Hive 函数

官方文档地址：

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF

自定义 UDF

官方文档地址：

https://cwiki.apache.org/confluence/display/Hive/HivePlugins

java 代码 public class TuoMing extends UDF{ public Text evaluate(final Text s) { if (s == null) { return null; } String str = s.toString().substring(0, 3) + "***"; return new Text(str); } }

将 java 代码文件打包成 jar ，上传 Linux 上的 HDFS 中

创建临时函数

-- 本地文件系统加载 add jar /root/tm/tm.jar; create temporary function tm as 'com.szxy.hive.TuoMing'; -- 从 HDFS 中加载 create temporary function tms as 'com.szxy.hive.TuoMing' using jar 'hdfs://node01:8020/data/jar/tm/tm.jar';

使用临时函数

select tms(name) from psn;

结果

Hive 案例

struct 结构体

测试数据

1001,zhangsan:24 1002,lisi:25 1003,wangwu:26 1004,zhaoliu:27

创建表

create table student( id int, info struct<name:string,age:int>) row format delimited fields terminated by "," collection items terminated by ":"; load data inpath '/data/struct/input' into table student;

WordCount

create external table hello( line string ) location '/data/wc/input' create table hello_wc( word string , num int ); from (select explode(split(line,' ')) word from hello ) t insert into hello_wc select word,count(word) group by word;

基站掉话率统计

需求：

找出掉线率最高的前10基站

sql 语句

create table tb_cell_result( imei string, drop_num int, duration int, drop_rate double ); create external table tb_cell( record_time string, imei string, cell string, ph_num int, call_num string, drop_num int, duration int, drop_rate int, net_type string, erl int ) row format delimited fields terminated by ',' location '/data/cell/input'; from tb_cell insert into tb_cell_result select imei,sum(drop_num) sdrop,sum(duration) sdura, sum(drop_num)/sum(duration) srate group by imei sorted by srate desc; select * from tb_cell_result limit 10;

Hive 参数

hive 参数、变量

hive当中的参数、变量，都是以命名空间开头

命名空间读写权限含义hiveconf可读写hive-site.xml 中配置各种变量例：hive --hiveconf hive.cli.print.header=trueSystem可读写系统变量，包括 JVM 运行参数等例：system:user.name=rootenv只读环境变量“ 例：env:JAVA_HOMEhivevar可读写例：hive -d val=key

通过 ${} 方式进行引用，其中 system、env 下的变量必须以前缀开头

hive 参数设置方式

修改配置文件 ${HIVE_HOME}/conf/hive-site.xml

<property> <name>hive.cli.print.header</name> <value>true</value> </property>

启动 hive cli 时，通过 --hiveconf key=value的方式进行设置

例：hive --hiveconf hive.cli.print.header=true

进入cli之后，通过使用set命令设置

set hive.cli.print.header=true;

Hive 分桶

分桶概念

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

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

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

适用场景

数据抽样（ sampling ）

分桶操作

开启支持分桶

set hive.enforce.bucketing=true;

默认：false；设置为 true之后，mr运行时会根据 bucket 的个数自动分配 reduce task 个数。

（用户也可以通过mapred.reduce.tasks自己设置reduce任务个数，但分桶时不推荐使用）注意：一次作业产生的桶（文件数量）和reduce task个数一致。

往分桶表中加载数据

insert into table bucket_table select columns from tbl; insert overwrite table bucket_table select columns from tbl;

桶表抽样查询

select * from bucket_table tablesample(bucket 1 out of 4 on columns);

TABLESAMPLE 语法

TABLESAMPLE(BUCKET x OUT OF y)

x：表示从哪个bucket开始抽取数据 y：必须为该表总bucket数的倍数或因子

栗子当表总 bucket 数为32时

TABLESAMPLE(BUCKET 3 OUT OF 8)，抽取哪些数据？

答：共抽取2（32/16）个bucket的数据，抽取第2、第18（16+2）个bucket的数据

TABLESAMPLE(BUCKET 3 OUT OF 256)，抽取哪些数据？

分桶案例

测试数据

1,tom,11 2,cat,22 3,dog,33 4,hive,44 5,hbase,55 6,mr,66 7,alice,77 8,scala,88

创建 hive 表

create external table tb_bucket( id int, name string, score int ) row format delimited fields terminated by ',' location '/data/bucket/input';

创建分桶表

create table psn_bucket( id int, name string, score int ) clustered by(score) into 4 buckets row format delimited fields terminated by ',';

向分桶表中添加数据

insert into psn_bucket select id,name,score from tb_bucket;

注意：Hive 分桶默认是关闭的,通过 set hive.enforce.bucketing=true;开启分桶

抽样

select id,name,score from psn_bucket tablesample(bucket 2 out of 4);

Hive Laternal View

在 UDTF 函数中使用

Lateral View用于和UDTF函数（explode、split）结合来使用。首先通过UDTF函数拆分成多行，再将多行结果组合成一个支持别名的虚拟表。主要解决在select使用UDTF做查询过程中，查询只能包含单个UDTF，不能包含其他字段、以及多个UDTF的问题

语法：

LATERAL VIEW udtf(expression) tableAlias AS columnAlias (',' columnAlias)

注意：列别名有多个，并且可以重复

栗子

统计人员表中共有多少种爱好、多少个城市?

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

Hive 视图

视图本质上就是一个虚拟表 Virtual Table,和关系型数据库中的普通视图一样，hive也支持视图

特点：

不支持物化视图

只能查询，不能做加载数据操作

视图的创建，只是保存一份元数据，查询视图时才执行对应的子查询

view定义中若包含了ORDER BY/LIMIT语句，当查询视图时也进行ORDER BY/LIMIT语句操作，view当中定义的优先级更高

view支持迭代视图

view语法

创建视图：

CREATE VIEW [IF NOT EXISTS] [db_name.]view_name [(column_name [COMMENT column_comment], ...) ] [COMMENT view_comment] [TBLPROPERTIES (property_name = property_value, ...)] AS SELECT ... ;

查询视图：

select colums from view;

删除视图：

DROP VIEW [IF EXISTS] [db_name.]view_name

Hive 索引

目的

优化查询以及检索性能

创建索引：

create index t1_index 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__psn2_t1_index__表中 create index t1_index on table psn2(name) as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' with deferred rebuild;

查询索引

show index on psn2;

重建索引（建立索引之后必须重建索引才能生效）

ALTER INDEX t1_index ON psn REBUILD;

删除索引

DROP INDEX IF EXISTS t1_index ON psn;

Hive 运行方式

命令行方式cli：控制台模式

与hdfs交互执行执行dfs命令例：dfs –ls /与Linux交互！开头例：!pwd

脚本运行方式（实际生产环境中用最多）

hive -e "" hive -e "">aaa hive -S -e "">aaa hive -f file hive -i /home/my/hive-init.sql hive> source file (在hive cli中运行)

JDBC方式：hiveserver2

web GUI接口（hwi、hue等）

Hive 权限管理

官方文档地址：

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Authorization

三种授权模型：

Storage Based Authorization in the Metastore Server

基于存储的授权 - 可以对Metastore中的元数据进行保护，但是没有提供更加细粒度的访问控制（例如：列级别、行级别）。

SQL Standards Based Authorization in HiveServer2

基于SQL标准的Hive授权 - 完全兼容SQL的授权模型，推荐使用该模式。 https://cwiki.apache.org/confluence/display/Hive/SQL+Standard+Based+Hive+Authorization

Default Hive Authorization (Legacy Mode) hive默认授权 - 设计目的仅仅只是为了防止用户产生误操作，而不是防止恶意用户访问未经授权的数据。

SQL Standards Based Authorization in HiveServer2

完全兼容SQL的授权模型

除支持对于用户的授权认证，还支持角色 role 的授权认证

role可理解为是一组权限的集合，通过role为用户授权

一个用户可以具有一个或多个角色

默认包含另种角色：public、admin

限制： 1、启用当前认证方式之后，dfs, add, delete, compile, and reset等命令被禁用。

2、通过set命令设置hive configuration的方式被限制某些用户使用。

（可通过修改配置文件hive-site.xml中hive.security.authorization.sqlstd.confwhitelist进行配置）

3、添加、删除函数以及宏的操作，仅为具有admin的用户开放。

4、用户自定义函数（开放支持永久的自定义函数），可通过具有admin角色的用户创建，其他用户都可以使用。

5、Transform功能被禁用。

在hive服务端修改配置文件hive-site.xml添加以下配置内容：

<property> <name>hive.security.authorization.enabled</name> <value>true</value> </property> <property> <name>hive.server2.enable.doAs</name> <value>false</value> </property> <property> <name>hive.users.in.admin.role</name> <value>root</value> </property> <property> <name>hive.security.authorization.manager</name> <value>org.apache.hadoop.hive.ql.security.authorization.plugin.sqlstd.SQLStdHiveAuthorizerFactory</value> </property> <property> <name>hive.security.authenticator.manager</name> <value>org.apache.hadoop.hive.ql.security.SessionStateUserAuthenticator</value> </property>

服务端启动hiveserver2；客户端通过beeline进行连接

Hive 优化*

核心思想：把 Hive SQL 当做Mapreduce程序去优化

以下 SQL 不会转为Mapreduce来执行：

select 仅查询本表字段

where 仅对本表字段做条件过滤

Explain 显示执行计划

EXPLAIN [EXTENDED] query

Hive抓取策略

Hive中对某些情况的查询不需要使用 MapReduce 计算

抓取策略

Set hive.fetch.task.conversion=none/more;

Hive运行方式

本地模式

集群模式

开启本地模式

set hive.exec.mode.local.auto=true;

注意： hive.exec.mode.local.auto.inputbytes.max 默认值为128M 表示加载文件的最大值，若大于该配置仍会以集群方式来运行

并行计算

通过设置以下参数开启并行模式：

set hive.exec.parallel=true;

注意：hive.exec.parallel.thread.number （一次SQL计算中允许并行执行的job个数的最大值）

严格模式

通过设置以下参数开启严格模式： set hive.mapred.mode=strict;

（默认为：nonstrict非严格模式）

查询限制

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

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

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

Hive排序

Order By - 对于查询结果做全排序，只允许有一个reduce处理（当数据量较大时，应慎用。严格模式下，必须结合limit来使用）Sort By - 对于单个reduce的数据进行排序Distribute By - 分区排序，经常和Sort By结合使用Cluster By - 相当于 Sort By + Distribute By （Cluster By不能通过asc、desc的方式指定排序规则；可通过 distribute by column sort by column asc|desc 的方式）

Hive Join

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

Map Join：在Map端完成Join

两种实现方式：

SQL方式，在SQL语句中添加 MapJoin 标记（mapjoin hint）语法： SELECT /*+ MAPJOIN(smallTable) */ smallTable.key, bigTable.value FROM smallTable JOIN bigTable ON smallTable.key = bigTable.key;

开启自动的MapJoin

自动的mapjoin 通过修改以下配置启用自动的 mapjoin：

set hive.auto.convert.join = true;

（该参数为true时，Hive 自动对左边的表统计量，如果是小表就加入内存，即对小表使用 Map join）

Map-Side聚合

通过设置以下参数开启在Map端的聚合：

set hive.map.aggr=true;

合并小文件

文件数目小，容易在文件存储端造成压力，给hdfs造成压力，影响效率

设置合并属性

是否合并map输出文件：hive.merge.mapfiles=true 是否合并reduce输出文件：hive.merge.mapredfiles=true; 合并文件的大小：hive.merge.size.per.task=25610001000

去重统计

数据量小的时候无所谓，数据量大的情况下，由于 COUNT DISTINCT 操作需要用一个 Reduce Task来完成，这一个Reduce需要处理的数据量太大，就会导致整个Job很难完成，一般COUNT DISTINCT使用先GROUP BY再COUNT的方式替换

控制Hive中Map以及Reduce的数量

Map 数量相关的参数

参数设置解释mapred.max.split.size一个split的最大值，即每个map处理文件的最大值mapred.min.split.size.per.node一个节点上split的最小值

| mapred.min.split.size.per.rack | 一个机架上split的最小值 |

Reduce 数量相关的参数参数解释mapred.reduce.tasks强制指定reduce任务的数量hive.exec.reducers.bytes.per.reducer每个reduce任务处理的数据量hive.exec.reducers.max每个任务最大的reduce数

Hive- JVM重用

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

通过 set mapred.job.reuse.jvm.num.tasks=n;来设置（n为task插槽个数）

缺点：

设置开启之后，task插槽会一直占用资源，不论是否有task运行，

直到所有的 task 即整个 job 全部执行完成时，才会释放所有的 task 插槽资源！

最新回复(0)