数据仓库之Hive

最新推荐文章于 2024-03-14 12:09:33 发布
最新推荐文章于 2024-03-14 12:09:33 发布
阅读量442 收藏 2
点赞数 1
分类专栏： 大数据 文章标签： Hive
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Sunshine_wz/article/details/102870076
版权

文章目录

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/SoftMySQLHive
node01*
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-client
node01*
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

配置环境变量的目的：

  1. 找可执行性文件
  2. 方便其他框架或者服务使用。 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路径需要自己指定路径
删除表时将数据和元数据全部删除只删除元数据，数据不删除

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

注意：

  1. 删除外部表中不会删除 HDFS 中的数据
  2. 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=true
    System可读写系统变量，包括 JVM 运行参数等
    例：system:user.name=root
    env只读环境变量“
    例：env:JAVA_HOME
    hivevar可读写例：hive -d val=key

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

  • hive 参数设置方式

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

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

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

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

    3. 进入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 运行方式

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

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

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

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

  3. JDBC方式：hiveserver2

  4. web GUI接口 （hwi、hue等）

Hive 权限管理

官方文档地址：

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

三种授权模型：

  1. Storage Based Authorization in the Metastore Server

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

  2. SQL Standards Based Authorization in HiveServer2

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

  3. 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来执行：

  1. select 仅查询本表字段

  2. 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

两种实现方式：

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

  1. 开启自动的MapJoin

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

    set hive.auto.convert.join = true;

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

    相关配置参数：

    hive.mapjoin.smalltable.filesize;

    （大表小表判断的阈值，如果表的大小小于该值则会被加载到内存中运行）

    hive.ignore.mapjoin.hint；

    （默认值：true；是否忽略mapjoin hint 即mapjoin标记）

    hive.auto.convert.join.noconditionaltask;

    （默认值：true；将普通的join转化为普通的mapjoin时，是否将多个mapjoin转化为一个mapjoin）

    hive.auto.convert.join.noconditionaltask.size;

    （将多个mapjoin转化为一个mapjoin时，其表的最大值）

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

    • 尽可能使用相同的连接键（会转化为一个MapReduce作业）

  • 大表join大表

    空key过滤：有时join超时是因为某些key对应的数据太多，而相同key对应的数据都会发送到相同的reducer上，从而导致内存不够。此时我们应该仔细分析这些异常的key，很多情况下，这些key对应的数据是异常数据，我们需要在SQL语句中进行过滤。
    空key转换：有时虽然某个key为空对应的数据很多，但是相应的数据不是异常数据，必须要包含在join的结果中，此时我们可以表a中key为空的字段赋一个随机的值，使得数据随机均匀地分不到不同的reducer上

Map-Side聚合

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

set hive.map.aggr=true;

相关配置参数：

hive.groupby.mapaggr.checkinterval：

map 端 group by 执行聚合时处理的多少行数据（默认：100000）

hive.map.aggr.hash.min.reduction：

进行聚合的最小比例（预先对100000条数据做聚合，若聚合之后的数据量/100000的值大于该配置0.5，则不会聚合）

hive.map.aggr.hash.percentmemory：

map端聚合使用的内存的最大值

hive.map.aggr.hash.force.flush.memory.threshold：

map端做聚合操作是hash表的最大可用内容，大于该值则会触发flush

hive.groupby.skewindata

是否对GroupBy产生的数据倾斜做优化，默认为false

相关配置参数：

hive.groupby.mapaggr.checkinterval：

map端group by执行聚合时处理的多少行数据（默认：100000）

hive.map.aggr.hash.min.reduction：

进行聚合的最小比例（预先对100000条数据做聚合，若聚合之后的数据量/100000的值大于该配置0.5，则不会聚合）

hive.map.aggr.hash.percentmemory：

map端聚合使用的内存的最大值

hive.map.aggr.hash.force.flush.memory.threshold：

map端做聚合操作是hash表的最大可用内容，大于该值则会触发flush

hive.groupby.skewindata

是否对GroupBy产生的数据倾斜做优化，默认为false

合并小文件

文件数目小，容易在文件存储端造成压力，给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 插槽资源！

爱吃酸梨
关注
  • 1
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
hive数据仓库
qq_44676946的博客
03-15 3337
数据仓库-Hive 数据仓库 1.1. 基本概念 英文名称为Data Warehouse，可简写为DW或DWH。数据仓库的目的是构建面向分析的集成化数据环境，为企业提供决策支持（Decision Support）。 数据仓库是存数据的，企业的各种数据往里面存，主要目的是为了分析有效数据，后续会基于它产出供分析挖掘的数据，或者数据应用需要的数据，如企业的分析性报告和各类报表等。 可以理解为：面向分析的存储系统。 1.2. 主要特征 数据仓库是面向主题的（Subject-Oriented ）、集成的（Integ
数据仓库HIVE（1）
falconQI的博客
10-19 131
HIVE概述 1.面向数据分析的存储系统 2.是一个面向主题的、集成的、不可修改的、反映历史变化的数据集合，用于数据分析，辅助管理决策。 *面向主题：指数据仓库中的数据是按照一定的主题域进行组织 *集成：指对原有分散的数据库数据经过系统加工，整理得到的消除原数据中的不一致性。 *不可修改：指一旦某个数据进入数据仓库之后只需要定期的加载、刷新，不会更改。 *反映历史变化：指通过这些信息，对企业的发展历程...
10-Hive数据仓库
01-07
Hive数据仓库 Hive数据仓库思维导图下载链接 Hive简介 Hive的产生 非java编程者对HDFS的数据做Mapreduce操作 Hive : 数据仓库 Hive：解释器，编译器，优化器等 Hive 运行时，元数据存储在关系型数据库里面 Hive架构 （1）用户接口主要有三个：CLI，Client 和 WUI。其中最常用的是CLI，Cli启动的时候，会同时启动一个Hive副本。Client是Hive的客户端，用户连接至Hive Server。在启动 Client模式的时候，需要指出Hive Server所在节点，并且在该节点启动Hive Server。 WUI是通过浏览器访问Hive
Hive数据仓库简介
W_chuanqi的博客
03-21 3817
Hive起源于Facebook，Facebook公司有着大量的日志数据，而Hadoop是实现了MapReduce模式开源的分布式并行计算的框架，可轻松处理大规模数据。然而MapReduce程序对熟悉Java语言的工程师来说容易开发，但对于其他语言使用者则难度较大。因此Facebook开发团队想设计一种使用SQL语言对日志数据查询分析的工具，而Hive就诞生于此，只要懂SQL语言，就能够胜任大数据分析方面的工作，还节省了开发人员的学习成本。
hive数据仓库--Hive介绍
m0_58625397的博客
03-21 4016
hive
Hive SQL中的lateral view explode
热门推荐
数据森麟
03-31 3万+
公众号后台回复“图书“，了解更多号主新书内容 作者：胖里 来源：胖里的日常 标题中直接写lateral view explode是我鲁莽了，毕竟这俩不是法定cp，也并不是不能分开各自生活。...
Hive数据仓库
weixin_46579297的博客
06-15 5534
基本使用 sql语言show databases; 查看数据库 ; 不能省略优点：缺点：HIve架构设计特别说明：hive2.2以后版本变化较大，去掉了HWI模块，HiveCLI模式也直接采用了beeline链接从上图可以看出，Hive的体系结构分为以下几部分:Hive 基础应用1。Hive基础概念1.hive 数据模型2。HiveSqlDDL建表说明1 元数据：描述数据的数据2.表分类：主要分内表和外表 1.内表：元数据和数据本身均被Hive管理。删除表则全部删除 2.外表：元数据被hive管理，数据本身
数据仓库之Hive.zip
10-23
数据仓库之Hive是大数据处理领域的一个重要工具，它是一个基于Hadoop的数据仓库系统，旨在为大规模数据集提供便捷的查询和分析能力。Hive由Facebook最初开发，并且逐渐成为Apache软件基金会的一个顶级项目，被广泛...
Hive数据仓库之垃圾分类数据分析系统
08-14
（2）hive数据仓库分层设计，包含ODS、DWD、ADS层 （3）sqoop数据迁移，完成HIve与MySQL数据库中的数据交互 （4）Echarts搭建动态可视化大屏 （5）SpringBoot搭建可视化后台系统，完成前端与后台的数据传递与交互。 ...
Hive-数据仓库实战
12-22
在大数据处理领域，Hive 是一个非常关键的数据仓库工具，尤其在数据挖掘、数据分析和统计中扮演着核心角色。在电信行业中，Hive 被广泛用于处理和分析用户的各种信息，如流量、话费、套餐消费等，帮助运营商洞察用户...
Hive数据仓库（一）
09-25
Hive是hadoop领域的数据仓库。它提供了一套工具可以让我们非常方便的管理位于HDFS中的大型数据集，它提供了一套类似于SQL的查询语言，可以很容易的查询hive中的数据。Hive也有数据库、表、视图、索引、函数等概念，非常类似于我们常用的关系型数据库，因此入手非常方便。本资源是Hive授课讲义的一部分。
基于Hadoop的数据仓库Hive学习指南.doc
04-06
【标题】：“基于Hadoop的数据仓库Hive学习指南” 【描述】：该文档是一份针对Hive的学习资料，旨在引导读者理解如何在Hadoop平台上利用Hive进行数据仓库操作和编程实践。它涵盖了Hive的基本概念、安装步骤、实验...
数据仓库hive用到的mysql安装包
09-03
mysql安装包，mysql外界数据库作为存储hive元数据的存储介质，它的存在，方便hive用户根据自身的需求对数据进行分析处理。
Hadoop---(5)Hive（数据仓库）
Mr.Cao
01-30 524
  5.1 Hive的系统架构   用户接口，包括CLI，JDBC/ODBC，WebUI。 元数据存储，通常是存储在关系数据库如mysql，derby中。 解释器、编译器、优化器、执行器。 Hadoop：用HDFS进行存储，利用MapReduce进行计算。         1.     用户接口主要有三个：CLI，JDBC/ODBC和WebUI CLI，即Shell命令...
数据仓库Hive
qq_40905284的博客
02-26 455
数据仓库Hive学习笔记
Hive数据库：嵌入、本地、远程全攻略（上）
小伍的博客
01-11 1140
Hive数据库：嵌入、本地、远程全攻略
数据仓库Hive简述
戴着红领巾走世界
02-25 967
    Hive 是一个构建在 Hadoop 之上的数据仓库，它可以将HDFS数据文件映射成表，并提供类 SQL 查询功能，用于查询的 SQL 语句会被转化为 MapReduce 作业提交到 Hadoop 上运行。其实，Hive是用Java编写的一套基于HDFS分布式数据存储，将SQL编译为MapReduce任务进行分布式计算的数据仓库框架，提供了类似 sq...
第 1 章 Hive 数据仓库
最新发布
m0_73577749的博客
03-14 1737
Hive：由 Facebook 开源用于解决海量结构化日志的数据统计工具。Hive 是基于 Hadoop 的一个数据仓库工具，可以将结构化的数据文件映射为一张表，并 提供类SQL查询功能。
大数据之MR
Sunshine.wz的博客
10-24 3470
MapReduce MR 理论 MapTask & ReduceTask 一个切片对应一个 Map，也就是说切片的数量决定了 Map 的数量 split 切片指逻辑上概念，用于指定 Map 处理数据的大小 切片用于将 HDFS 中的块与 Map 之间解耦 Reduce 的数量由人来决定，根据前面的组的推导 MR 原语 输入(格式化k,v)数据集 -> map映射成一个中间数据...
Hadoop数据仓库：Hive详解与应用
“Hadoop数据仓库工具--hive介绍” Hive是Apache Hadoop生态系统中的一个关键组件，它设计用于处理和管理大规模数据集。Hive构建在Hadoop之上，利用HDFS（Hadoop分布式文件系统）作为底层数据存储，并通过MapReduce...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值