大数据处理之Hive
Apache Hive
The Apache Hive ™ data warehouse software facilitates reading, writing, and managing large datasets residing in distributed storage using SQL. Structure can be projected onto data already in storage. A command line tool and JDBC driver are provided to connect users to Hive.
一、Hive 概述
1.1 MapReduce 编程的不便性
- MapReduce is hard to program
- 【八股文】格式编程，三大部分
- No Schema，lack of query lanaguages, eg. SQL
- 数据分析，针对DBA、SQL语句，如何对数据分析
- MapReduce编程成本高
- FaceBook 实现并开源了 Hive
1.2 Hadoop 与 Hive
-
HDFS：存储数据
-
YARN：资源管理
-
MapReduce：处理数据
-
日志
- 日志内容，统一的规范
- 每一行数据就是一条数据（RDBMS）
- 很多列，统一的标识符进行分割
- 日志内容，统一的规范
-
schema
- 模式
- 约束
-
Hive -- 自动将类似于表的语句转化为mapreduce，然后提交到yarn上面运行
-
处理的数据存储在HDFS上
-
分析数据底层的实现就是MapReduce
-
执行程序运行的是YARN
-
RDNMS
- 表的概念
create table `bf_log`{ //具体字段 } -
分析 HQL = HiveQL
select * from bf_log limit 10; //截取 select substring(ip,0,4) ip_prex from bg_log;- ETL 功能
-
1.3 什么是Hive
-
由FaceBook开源用于解决海量结构化日志的数据统计
-
Hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射成一张表，并提供类SQL查询功能
-
构建在Hadoop之上的数据仓库
- 使用HQL作为查询接口
- 使用HDFS存储
- 使用MapReduce计算
-
本质是：将HQL转化成MapReduce程序
-
灵活性和扩展性更好：支持UDF，自定义存储格式等
- UDF函数其实就是一个简单的函数，执行过程就是在Hive转换成mapreduce程序后，执行java方法，类似于像Mapreduce执行过程中加入一个插件，方便扩展
- UDF只能实现一进一出的操作，如果需要实现多进一出，则需要实现UDAF
- 允许自定义UDF函数
-
适合离线处理
-
Hive关键：转化
HQL
| Engine
MapReduce
1.4 Hive 在生态系统的位置 & 架构
1.4.1 在生态系统的位置
1.4.2 Hive 架构
- Client ：用户接口
- CLI：命令行（Hive shell）
- JDBC/ODBC：java访问Hive
- WEBUI：浏览器访问Hive
- Meta sotre 表的元数据
- 默认存储在自带的derby数据库中，推荐使用mysql存储Meta Store
- 表的名称
- 所属是数据库
- 表的拥有者
- 列/分区字段
- 表的数据所在目录，表与文件的映射（表对应的文件在哪里）
- 表的类型（是否是外部表）
- 字段个数
- Driver（Egine）
- SQL Parser 解析器
- 将SQL字符串转化成抽象语法树AST，这一步一般都用第三方工具库完成，比如antlr
- 对AST进行语法分析，比如表是否存在、指端是否存在、SQL语义是否有误（比如select中被判定为聚合的字段在group by中是否有出现）
- Query Optimizer 编译器：将AST编译生成逻辑执行计划
- Physical Plan 优化器：对逻辑执行计划进行优化
- Excution 执行器：把逻辑执行计划转为可以运行的物理运行计划。对于Hive来说，就是MR/TEZ/Spark
- SQL Parser 解析器
- Hadoop
- 使用HDFS进行存储，使用MapReduce 进行计算
1.5 Hive 有点与使用场景
- 操作接口采用类SQL语法，提供快速开发的能力（简单、容易上手）
- 避免了去写MapReduce，减少开发人员的学习成本
- 统一的元数据管理，可与impala/spark等共享元素据
- 易于扩展（HDFS+MapReduce：可以扩展集群规模；支持自定义函数）；
- 数据的离线处理；比如：日志分析，海量结构化数据离线分析...
- Hive的执行延迟比较高，因此Hive长于数据分析的，对实时性要求不高的场合
- Hive优势在于处理大数据，对于处理小数据没有优势，因为Hive的执行延迟比较高
二、Hive 环境搭建
2.1 Hive 相关文档
-
官网
-
文档
https://cwiki.apache.org/confluence/display/Hive/GettingStarted
-
下载
-
github
2.2 环境依赖
- Java 1.7 以上
- Hadoop 2.x（也可以运行在hadoop 1.x上）
- Hive is commonly used in production Linux and Windows environment. mac is a commonly used development environment.
2.3 启动 Hive
-
启动HDFS
-
修改 Hive 的配置
-
重命名 hive-env.sh.template 为 hive-env.sh，并修改配置
# Set HADOOP_HOME to point to a specific hadoop install directory HADOOP_HOME=/opt/models/hadoop-2.5.0 # Hive Configuration Directory can be controlled by: export HIVE_CONF_DIR=/opt/models/hive-0.13.1/conf
-
-
启动 Hive
[root@hadoop00 hive-0.13.1]# bin/hive -
启动后的结果：启动之后直接进入hive 客户端
- 操作命令类似于mysql
- 建表语句
create table test_table( name String; passwd String; sex String; )- 创建表格，并指定字段域字段分割符（tab）
create table student( id int, name String )ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
- 数据准备 students.txt
1001 zhangsan 1002 lisi 1003 wangwu- 将数据加载到表中
hive> load data local inpath '/opt/data/students.txt' into table student;
2.4 配置Hive元数据库（metastore）-- MySQL与hive在同一台机器上
- 将 hive-site.xml.template 复制 并重命名为 hive-site.xml
- 将 jdbc 连接驱动的 jar 包拷贝到 hive 安装目录中的 lib 目录下面
- 修改配置文件如下
<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://hadoop00:3306/metastore?createDatabaseIfNotExist=true</value>
</property>
<property>
<name>javax.jdo.option.ConnectionDriverUserName</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>magiwise@CQ2020</value>
</property>
</configuration>
-
启动 hive
bin/hive -
查看mysql数据库
- 在多个终端中启动多个hive，不会报错了
2.5 Hive 相关的命令以及配置
-
desc table_name：查看表结构
-
desc extened table_name：查看表的详细信息（不包含表中的数据）
-
desc formatted table_name：格式化查看表结构以及详细信息（不包含表中的数据）
-
load data local input 'file_path/file_name' into table db_name.table_name：将本地文件 file_name 加载到数据库 db.name 中的表 table_name 中
-
show functions：查看 Hive 中提供的函数
-
desc function funciton_name：查看函数 function_name 怎么使用
-
desc function exteneded function_name：给出 function_name 的用法以及一个 function_name 案例
-
show partitions table_name : 查看一个表的分区
-
配置客户端展示信息
<property> <!--使用的元数据（使用哪张表）--> <name>hive.cli.print.header</name> <value>true</value> </property> <property> <!--当前的数据库信息--> <name>hive.cli.print.current.db</name> <value>true</value> </property> -
配置hive日志
-
将 hive-log4j.properties.template 重命名为 hive-log4j.properties
-
重启 hive
-
修改 hive-log4j.properties 中相关的配置
三、Hive 常用配置&操作
3.1 Hive 常见属性配置
-
Hive 数据仓库位置配置(在HDFS的位置)
- 每创建一个数据库，据会新建一个名为 数据库名.db 的文件夹
- 在仓库目录下，没有对默认的数据库 default 常见文件夹
- 如果某张表属于 default 数据库，直接在数据仓库目录（/user/hive/warehouse）创建一个文件夹，文件加名字为对应表的名字
<property> <name>hive.metastore.warehouse.dir</nam> <value>/user/hive/warehouse</value> <description>location of default database for the warehouse</description> </property>-
注意事项：在设置目录时，需要注意权限(同组权限)
$HADOOP_HOME/bin/hadoop fs -mkdir /tmp $HADOOP_HOME/bin/haddop fs -mkdir /user/hive/warehouse $HADOOP_HOME/bin/fs -chmod g+w /tmp $HADOOP_HOME/bin/hadoop fs -chmod g+w /user/hive/warehouse
-
Hive 运行日志信息位置
在 $HIVE_HOME/conf/hive-log4j.properties 中进行配置
hive.log.dir=/opt/models/hive-0.13.1/logs hive.log.file=hive.log -
指定 Hive 运行时显示的log日志级别
在$HIVE_HOME/conf/hive-log4j.properties 中进行配置
hive.root.logger=INFO,DRFA hive.root.logger=DEBUG,DRFA -
在cli命令行上显示但概念数据库，以及查询表的行头信息
$HIVE_HOME/conf/hive-site.xml
<property> <name>hive.cli.print.header</name> <value>true</true> <description>Whether to print the nams of the colums in query output</description> </property> <property> <name>hive.cli.print.current.db</name> <value>true</value> <description>Whether to include the current database in the Hive pro </property> -
在启动Hive时设置配置属性信息
bin/hive --hiveconf <property=value> bin/hive --hiveconf hive.root.logger=INFO,console -
查看当前所有的配置信息
hive > set ; # 查看某个属性的信息 hive > set system:user.name ; -
设置某个属性的信息
# 设置某个属性的值，仅仅在当前会话生效 hive > set system:user.name=zhang;
3.2 Hive shell 常见操作(企业中常用)
3.2.1 bin/hive
[root@hadoop00 hive-0.13.1]# bin/hive -help
usage: hive
-d,--define <key=value> Variable subsitution 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 <hostname> connecting to Hive Server on remote host
-H,--help Print help information
--hiveconf <property=value> Use value for given property
--hivevar <key=value> Variable subsitution to apply to hive
commands. e.g. --hivevar A=B
-i <filename> Initialization SQL file
-p <port> connecting to Hive Server on port number
-S,--silent Silent mode in interactive shell
-v,--verbose Verbose mode (echo executed SQL to the
console)
-
**bin/hive -e <quoted-query-string> **
$ bin/hive -e "select * from db_hive.students"; -
bin/hive -f <filename>
$ touch hivef.sql select * from db_hive.studets; # 执行 sql 文件 $ bin/hive -f /opt/data/hivef.sql; # 执行 sql 文件，并将结果写入到文件中 $ bin/hive -f /opt/data/hivef.sql > hivef-res.txt -
bin/hive -i <filename>
里面存储也是 sql 文件，用于初始化操作，通常与用户自定义的 UDF 相互使用
-
bin/hive -S --silent
Silent mode in interactive shellwhere only data is emitted
交互式shell中的静默模式，只发出数据
3.2.2 Hive Cli 交互式命令
-
quit/exit：退出命令行
- quit：
- exit
-
set key=value：设置一个属性的值
-
set
-
set -v
-
!：操作 Linux 的命令（操作和查看本地文件系统）
hive > !ls /opt/models/ -
dfs：操作和查看 HDFS文件系统
hive > dfs -ls / -
query string
-
load data local inpath '/local_file_path/file_name' into table db_name.table_name ;
- 将数据加载到对应的表中
- 其实质就是将对应是数据存放到HDFS中对应表名的文件夹中
- 所以可以事先创建文件夹并将对应的数据存放到文件下，然后再创建对应的表，这样数据也会直接对应到表中
-
hive 命令记录：$HOME/.hivehistory
四、Hive 表操作
4.1 Create Table
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 ]
4.2 三种创建表的方式
- 原表创建
create table IF NOT EXISTS default.bg_log_20150913(
ip string COMMENT'remote up address',
user string COMMENT'用户名',
req_url string)
COMMENT'BeiFeng Web Access Logs'
ROW FORMAT DELIMITED FIELDS TERMINATED BY ' '
# 默认值就是 '\n'
COLLECTION ITEMS TERMINATED BY '\n'
# 默认就是 TEXTFILE
STORED AS TEXTFILE
# 设置创建的表格位置
LOCATION '/user/beifeng/hive/warehouse/bf_log_20150913';
- 子表创建(分表)
create table IF NOT EXISTS default.bf_log_20150913_sa
AS
select ip, user form default.bf_log_20150913;
- 同结构表创建
------------------------------------------------------------
create table IF NOT EXISTS default.bf_log_20150814
like default.bf_log_20150913;
4.3 表的类型
-
管理表（MANAGED_TABLE）
- 在Hive中，所有创建的表默认都是管理表
-
外部表/托管表（EXTERNAL）
- 在Hive中，创建表的时候，加上 EXTERNAL 关键字，所创建的表就是外部表
create EXTERNAL TABLE IF NOT EXISTS db_name.table_name (.....) ;- 与管理表的区别：删除外部表，表中的数据不会被删除
- 应用场景：一份数据，同时需要给不同的业务部门使用
- 在企业当中，80%都是外部外部表
4.4 外部表VS管理表
-
内部表(管理表)
- 也称之为MANAGED_TABLE
- 默认存储在 /user/hive/warehouse 下，也可以通过 location 指定（一般不会去指定目录）
- 删除表时，会删除表数据以及元数据
-
外部表
-
也称之为 EXTERNAL_TABLE
-
在创建表时可以自己指定目录位置（LOCATION）（通常必须指定）
create external table page_view( ..... )comment'' row fomat delimited fields terminated by '\n' store as textfile location '<hdfs_location>' ; -
删除表时，只会删除元数据而不会删除表数据（删除表，只会删除mysql数据库中对应表的元数据，不会删除在HDFS中以该表名创建的文件夹）
-
-
元数据&表数据
- 元数据：存储在mysql中
- 表数据：存储在HDFS中
4.5 分区表
4.5 1 定义
- 分区表实际上就是对应一个HDFS文件系统上的独立的文件夹，该文件夹下是该分区所有的数据文件
- Hive 中的分区就是分目录，把一个大的数据集根据业务需求分割成更小的数据集
- 在查询时通过 WHERE 子句中的表达式选择查询所需要的指定的分区，这样的查询效率会提高很多
4.5.2 创建分区表
create table db_name.table_name(
....
)comment'....'
partitioned by ('分区规则')/(exp:event_month string)
row format delimited fields terminated by '\t' ;
4.5 3 加载数据到分区表中
hive > load data local inpath '/local_file_path/file_name' into table db_name.table_name partition (event_month='201509')
4.5.4 分区表注意事项
-
修复分区表
-
HDFS对应的分区文件夹中有数据，但是hive查询不到，则需要修复分区表
-
产生原因：分区不能识别分区 = metastore 中没有相关分区的信息
-
修复
- 方式一
hive > msck repair table db_name.table_name ;-
方式二(常用)
hive > alter table db_name.table_name add partiton(分区信息) ; ## 案例 # hive > alter table dept_par add parition(month='201509') ;
-
-
企业中对分区表的使用案例(shell 脚本)
dfs -mkdir -p /user/hive/warehouse/dept_par/day=20150914 ; dfs -put /opt/data/deptInfo.txt /user/hive/warehouse/dept_par/day=20150914 ; alter table dept_par add partition(day='20150914') ;
4.5.5 查看分区表的分区
hive > show paritions db_name.table_name ;
五、数据库操作 DML
5.1 创建数据库
CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[MANAGEDLOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];
---------------------------------------------------------
create database db_hive_01 ;
# 标准做法
create database if not exists db_hive_02 ;
# 加上数据库在HDFS中存储位置
create database if not exists db_hive_03
location '/user/root/hive/warehouse/db_hive_03.db' ;
5.2 查看数据库
- show databases ； --查看所有数据库
- show databases like 'db_hive*'；-- 查看相关数据库
5.3 使用数据库
- use db_name
5.4 查看数据库信息
- **desc database db_name ; ** -- 查看数据库基本信息
- desc database extended db_name ; -- 查看数据库的详细信息
5.5 删除数据库
- drop database db_name ; -- 删除空数据库，HDFS中对应的目录也会被删除
- drop database db_name cascade ; -- 删除非空数据库（级联删除），HDFS中对应的目录也会被删除
- drop database if exists db_name ; -- 标准做法
六、数据类型
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Types
6.1 primitive_type 原生数据类型
6.2 Numeric Types
- INT/INTEGER： (4-byte signed integer, from -2,147,483,648 to 2,147,483,647)
- BIGINT：(8-byte signed integer, from
-9,223,372,036,854,775,808to9,223,372,036,854,775,807) - DOUBLE ：PRECISION (alias for DOUBLE, only available starting with Hive 2.2.0)
- DECIMAL
6.3 Date/Time Types(一般都不用)
- TIMESTAMP：(Note: Only available starting with Hive 0.8.0)
- DATE：(Note: Only available starting with Hive 0.12.0)
- INTERVAL：(Note: Only available starting with Hive 1.2.0)
6.4 String Types
- STRING
6.5 Misc Types
- BOOLEAN：实际中很少运用，通常使用0/1去代替
6.6 Complex Types
- arrays: ARRAY<data_type> (Note: negative values and non-constant expressions are allowed as of Hive 0.14.)
- maps: MAP<primitive_type, data_type> (Note: negative values and non-constant expressions are allowed as of Hive 0.14.)企业中用得最多
- structs: STRUCT<col_name : data_type [COMMENT col_comment], ...>
union: UNIONTYPE<data_type, data_type, ...> (Note: Only available starting with Hive 0.7.0.)
6.7 巧用案例--使用脚本语言
-
创建一张表并录入数据，这张表中只有一个字段，一行就是一整条记录，包含多个字段: table, load E
create table log( content string ) -
结合Hive子查询并使用脚本对数据进行预处理: select, python T
python >> content
<< 我所需要的字段
-
将处理得到的数据放大子表当中: sub table L
七、雇员表和部门表 案例
7.1 创建表
-
雇员表
create table IF NOT EXISTS default.emp( empno int, ename string, job string, mgr int, hiredate string, sal double, comm double, deptno int ) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' ; -
部门表
create table IF NOT EXISTS default.dept( deptno int, deptname string, loc string )ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
7.2 准备数据
-
雇员数据(emp.txt)
7369 SMITH CLERK 7902 1980-12-17 800.00 20 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 7566 JONES MANAGER 7839 1981-4-2 2975.00 20 7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30 7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30 7782 CLARK MANAGER 7839 1981-6-9 2450.00 10 7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20 7839 KING PRESIDENT 1981-11-17 5000.00 10 7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30 17876 ADAMS CLERK 7788 1987-5-23 1100.00 20 7900 JAMES CLERK 7698 1981-12-3 950.00 30 7902 FORD ANALYST 7566 1981-12-3 3000.00 20 7934 MILLER CLERK 7782 1982-1-23 1300.00 10 -
部门数据(deptInfo.txt)
10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
7.3 导入数据
-
导入雇员数据
hive > load data local inpath '/file_path/emp.txt' into table default.emp ; 或者 # 覆盖导入 hive > load data local inpath '/file_path/emp.txt' overwrite into table default.emp ; -
导入部门数据
hive > load data local inpath '/file_path/deptInfo.txt' into table default.dept ; 或者 # 覆盖导入 hive > load data local inpath '/file_path/deptInfo.txt' overwrite into table default.dept ;
7.4 表相关操作
-
创建子表（结构+数据）
hive > create table default.dept_cats hive > as hive > select * from default.dept ; -
创建相同表（结构）
hive > create tabel default.dept_like hive > like hive > default.dept ; -
修改表名
hive > alter table dept_like hive > rename hive > dept_like_rename ; -
清空表中的数据
hive > truncate table default.dept_cats ; -
删除表
hive > drop table if exists default.dept_cats_rename ; -
托管表/外部表（external）
create external table if not exists default.emp_ex( empno int, ename string, job string, mgr int, hiredate string, sal double, comm double, deptno int )row format delimited fields terminated by '\t' ;注：查看一张表的类型，使用desc formatted命令
-
分区表
-
创建分区表：需要一个分区字段
分区字段：按照什么方式来拆分文件夹
partitioned by 分区字段
create external table if not exists default.emp_par( empno int, ename string, job string, mgr int, hiredate string, sal double, comm double, deptno int ) # 一级分区 partitioned by (event_month string) # 二级分区 # partitioned by (month string, day string) row format delimited fields terminated by '\t' -
加载数据到分区表中
hive > load data local inpath '/opt/data/empdata/emp.txt' into table default.emp_par # 一级分区 partiton(event_month='201905') ; -
查看HDFS中的文件结构
-
查询分区中的数据
hive > select * from emp_par where event_month='201509' ; -
同时查询多个分区的数据并合并结果集
- 直接在 Cli 中写入HQL语句
hive > select count(distinct emp_par.job) from emp_par where evnent_month = '201509' union select count(distinct emp_par.job) from default.emp_par where event_month = '201508' union select count(distinct emp_par.job) from default.emp_par where event_month = '201507' ;注： 上面的语句会启动5个mapreduce：三个select，两个union
-
将 HQL 写入到文件中，使用 bin/hive -f file_name.sql 执行（em_par_select.sql）
select count(distinct emp_par.job) from emp_par where evnent_month = '201509' union select count(distinct emp_par.job) from default.emp_par where event_month = '201508' union select count(distinct emp_par.job) from default.emp_par where event_month = '201507' ;
-
八、将数据加载到表中的方式
8.1 load data
load data [local] inpath 'filepath' [overwrite] into table tablename [partition (partcol1=val1, partcol2=val2...)]
- filepath 位置
- 在本地：local
- 在hdfs中，不需要 local，但是在数据加载完毕之后，原来的文件会被直接删除
- overwrite 覆盖
- 覆盖：overwrite
- 追加：不需要 voerwrite
- parition 分区
- 是分区表： 需要加上分区 partition(partcol1=val1, ......)
- 不是分区表，不需要
8.2 create & insert(创建表的时候，通过insert进行加载)
create table db_name.table_name(....);
insert into table db_name.table_name select * from db_name1.table_name1 ;
8.3 create & as
create table db_name.table_name
as
select * from db_name1.table_name2 ;
8.4 创建表的时候通过location指定加载
create table db_name.table_name(....)
location 'hdfs文件目录'
注意：采用此种方式，对于普通表（非分区表）可行，但需要提前将数据放到对应的目录之中
九、数据迁移之导出数据
9.1 使用 insert 将数据插入到本地目录
insert overwrite local directory 'file_path'
select * from db_name.table_name;
# 设置导出的数据格式
insert overwrite local directory 'file_path'
row format delimited fields terminated by '\t'
collection items terminated by '\n'
select * from db_name.table_name ;
9.2 将查询结果直接写入到本地
bin/hive -e "select * from db_name.table_name ;" > 'local_path/file_name'
# bin/hive -e "select * from default.dept_par ;" > '/opt/data/dept_par_e.txt'
9.3 将查询结果插入到 hdfs 文件系统中并下载到本地
insert overwrite directory 'hdfs_path'
select * from db_name.table_name ;
# 将hdfs中的文件下载到本地
# bin/hdfs dfs -get 'hdfs_path/file_name'
9.4 采用 sqoop 的方式
- Hdfs/hive --> rdbms
- Dbms --> hdfs/hive/hbase
十、分析数据之常见的查询
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Select
10.1 select syntax
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[ORDER BY col_list]
[CLUSTER BY col_list
| [DISTRIBUTE BY col_list] [SORT BY col_list]
]
[LIMIT [offset,] rows]
10.1.2 全表查询、指定字段查询
# 全表查询
select * from db_name.table.name ;
# 指定字段查询
select t.clo1, t.clo2 ... from db_name.table_name t ;
10.1.2 = / >= / <= / between and / limit
# between and
select t.empno, t.ename, t.deptno from default.emp t where t.sal between 800 and 1500 ;
# limit
select * from db_name.table_name limit 5;
10.1.3 (not) in / is (not) null
select t.empno, t.ename, t.deptno from emp t where comm is null ;
10.1.4 max / min / count / sum / avg (函数)
hive 查看自带的函数 ： show functions ;
hive查看函数的用法：desc function extended function_name ;
# count
select count(*) cnt from default.emp ;
# max
select max(sal) max_sal from default.emp ;
# sum
select sum(sal) from default.emp ;
# avg
select avg(sal) from default.emp ;
10.1.5 group by / having
10.1.5.1 group by
- 统计每个部门的平均工资
select t.deptno, avg(t.sal) avg_sal
from default.emp t
group by t.deptno ;
注意：使用group by 的时候，select 的所有字段，都必须出现在group by的字段中或者聚合函数中
- 每个部门中每个岗位的最高薪水
select
t.deptno,
t.job,
max(t.sal) max_sal
from default.emp t
group by t.deptno, t.job ;
10.1.5.2 having
- having & where
- where 是针对单条记录进行筛选
- having 是针对分组结果进行筛选
- 求每个部门的平均薪水大于2000的部门
select deptno, avg(sal) avg_sal from default.emp t group by t.deptno having t.avg_sal > 200 ;
10.1.6 join 两个表进行连接
- m表中的一条记录和n表中的一个条记录组成一条记录
10.1.6.1 等值连接 (join ... on)
select
e.empno,
e.ename,
e.deptno
from emp e
join dept d
on e.deptno = d.deptno ;
10.1.6.2 左连接 (left join) -- 以左边的表为准
select
e.empno,
e.ename,
d.deptno
from emp e
left join dept d
on e.deptno = d.deptno ;
10.1.6.3 右连接 (right join) -- 以右边的表为准
右表中有的字段，左边中没有，结果就为空
select
e.empno,
e.ename,
d.deptno
from emp e
right join dept d
on e.deptno = d.deptno ;
10.1.6.3 全连接（full join）
select
e.empno,
e.ename,
d.deptno
from emp e
full join dept d
on e.deptno = d.deptno ;
11 数据操作之 Import & Export
-
expert syntax 将hive表中的数据导出外部
export table tablename [partiton(part_column='value'[,...])] to 'export_target_path' ; # export_target_path hdfs中的路径 -
import syntar 将外部数据导入到 hive 表中
import [[external] table new_or_original_tablename [partiton(part_colum='value'[,...])]] from 'source_path' [location] 'import_target_path' ;
12 数据分析之排序
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SortBy
12.1 Order by
- 对全局数据的排序
- 仅仅只有一个 reduce
- 在查询量比较大的时候，谨慎使用
colOrder: ( ASC | DESC )
colNullOrder: (NULLS FIRST | NULLS LAST) -- (Note: Available in Hive 2.1.0 and later)
orderBy: ORDER BY colName colOrder? colNullOrder? (',' colName colOrder? colNullOrder?)*
query: SELECT expression (',' expression)* FROM src orderBy
-
将员工表按照工号的降序排列
select * from default.emp order by empno desc ;
12.2 Sort by
- 对每个reduce内部数据进行排序
- 输出的每一个文件都是经过排序的
- 但是对于全局的结果集来说，是没有排序
colOrder: ( ASC | DESC )
sortBy: SORT BY colName colOrder? (',' colName colOrder?)*
query: SELECT expression (',' expression)* FROM src sortBy
-
查询每个部门的人员的排序
set mapreduce.job.reduce= 3 ; insert overwrite local directory '/opt/data/hive/sortby_res' select * from default.emp sort by empno asc ; -
怎么设置一个job的 reduce 和 map 数目
# 当前会话生效 hive > set mapreduce.job.reduces=<number> hive > set mapreduce.job.maps=<number>
12.3 distribute by
-
分区 partition
-
类似于mapreduce中分区partition的功能
-
对数据进行分区
-
结合sort by 进行使用
-
对部门进行分区（按照部门编号进行分区）并按照工号排序
insert overwrite local directory '/opt/data/hive/distby_res' select * from default.emp distribute by deptno sort by empno asc ; -
注意事项
- distribute by 必须要在 sort by 之前
12.4 cluster by
-
order by 与 distribute by 结合
-
使用场景：当 distribute by 的字段 和 sort by 字段相同时，就可以使用cluster by 代替
-
按照工号进行分区并排序
insert overwrite local directory '/opt/data/hive/distby_res' select * from default.emp cluster by empno ;
12.5 总结
- order by : 全局排序，一个Reduce
- sort by : 每个reduce内部进行排序，全局不是排序的
- distribute by : 类似MR中的partition，进行分区，结合sort by 使用
- distribute by 必须要在 sort by 之前
- cluster by : 当distribute 和 sort 字段相同时，使用
13 UDF 编程（User Definition Function）
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF
https://cwiki.apache.org/confluence/display/Hive/HivePlugins
13.1 概述
- Hive 自带了一些函数，但是数量有限，自己可以通过自定义UDF来方便的扩展
- UDF 用户自定义函数，允许用户扩展HiveQL功能
- UDF
- 一进一出
- UDAF（User-Defined Aggregation Function）
- 聚集函数，多进一出
- 类似于：count/max/min
- UDTF（User-Defined Table-Generating Functions）
- 一进一出
- 如 lateral view explore()
13.2 编程
- 编程步骤
- 继承 org.apache.hadoop.hive.ql.UDF
- 需要实现evaluate函数，evaluate函数支持重载
- 注意事项
- UDF必须要有返回类型，可以返回null，但是返回类型不能为void
- UDF中常用Text/LongWritetable等类型，不推荐使用java类型
13.3 creating custom USFs
-
you need to create a new class that extends UDF, with one or more methods named evaluate
-
引入依赖
<dependency> <groupId>org.apache.hive</groupId> <artifactId>hive-jdbc</artifactId> <version>3.1.2</version> </dependency> <dependency> <groupId>org.apache.hive</groupId> <artifactId>hive-exec</artifactId> <version>3.1.2</version> </dependency> -
编写代码
package com.zhang.udf; import org.apache.hadoop.hive.ql.exec.UDF; import org.apache.hadoop.io.Text; import java.util.Locale; public class LowerUDF extends UDF{ public Text evaluate(Text str){ if(null == str.toString()){ return null; } return new Text(str.toString().toLowerCase()); } public static void main(String[] args) { System.out.println(new LowerUDF().evaluate(new Text("HIVE"))); } }
-
-
Usage old way
-
将代码打包成一个jar包并上传到服务器上
-
将jar包加入到 hive中
hive > add jar jar_path/java -
创建函数
hive > create temporary function UDF_function_name as "类名" ; # 例子 hive > create temporary function my_lower as "com.zhang.udf.LowerUDF" ; -
查看是否成功
# 查看functions 是否已经存在自定义udf hive > show functions ; # 查看用法 hive > desc function extended my_lower ; # 查看引入了多少jar hive > list jars ; -
使用，和其他函数一样
hive > select my_lower(ename) from default.emp ;
-
-
Usage new way (0.13之后的版本才提供)
hive > create function myfunc as 'myclass' using jar 'hdfs:///path/to/jar' ; # exp hive > create function my_lower as 'com.zhang.udf.LowerUDF' using jar 'hdfs://hadoop00/user/hive/warehouse/jar/hdfsapi-1.0-SNAPSHOT.jar' ;-
注意：
-
使用该种方式创建的的udf，起名字会带上数据名（default.my_lower）
-
在所创建的数据库（default）中可以直接使用
hive (default) > select my_lower(ename) from db_name.emp ; 或者 hive (default) > slect default.my_lower(ename) from db_name.emp ; -
在其他的数据中，必须使用全名
hive (db_hive) > select default.my_lower(ename) from db_name.emp ;
-
-
14 HiveServer2 & Beeline
14.1 HiveServer2
-
概述
作为hive的一个服务存在，用于提供类似于jdbc的连接
-
命令位置
在 hive安装目录下的bin目录中，作为一个hive的一个服务
-
启动方式
-
直接启动
$ bin/hiveserver2 -
作为守护进程启动
$ nohup /bin/hiveserver2 &
14.2 Beeline
-
概述
作为一个类似于 cli 的命令行客户端
-
命令位置
在hive安装目录下bin目录中
-
启动
$ bin/beeline -
连接 HiveServer2 --- 方式一
beeline > !connect jdbc:hive2://localhost:10000 scott tiger org.apache.hive.jdbc.HiveDriver ; # 例子 beeline > !connect jdbc:hive2://hadoop00:1000 scott tiger org.apache.hive.jdbc.HiveDriver ; -
连接 hiveserver2 --- 方式二
bin/beeline -u jdbc:hive2://hadoop00:1000/default ; -
操作
具体的使用和在 cli 中是相同的
14.3 HiveServer2 JDBC
- 将分析的结果存储在hive表（结果表，resule），前端通过DAO代码，进行数据的查询。
- 但是HiveServer2 点并发需要进行处理
15 数据压缩
15.1 常见的数据压缩技术
-
处理流程
input --> map --> shffle --> reduce -- > output
-
数据压缩体现在数据处理过程中
-
数据压缩的好处
- 数据量小
- 减少本地磁盘的IO占用
- 减少网络的IO
-
注意：对于map处理的数据的输入，不用配置压缩。map会根据文件的后缀名去判断文件是否被压缩了以及采用的是什么方式进行压缩，然后自动的去解压
- 现在，企业用得更多的是snappy
- snappy是谷歌开源的压缩工具
15.2 Data Compression in Hadoop(好处)
- Hadoop jobs are usually IO bound;（降低了IO流负载）
- Compression reduces the size of data transferred across network;（减少网络数据传输量的大小）
- Overall job performance may be increased by simple enabing compression;（提高了job的性能）
- Splittability must be tabken into account;（压缩格式支持可分割）
- 通常情况下一个 block 采用一个 map 处理
- 压缩后的数据，必须支持可分割（支持每个压缩文件都可以单独的解压）
15.3 Compression in MR Pipeline
15.4 Compression in Hive
15.5 编译hadoop
-
官方提供的版本是不支持数据压缩的，所以需要我们自己去编译hadoop
-
打开 BUILD.txt
- Snappy ：一个支持压缩的可选项
-
编译步骤
-
安装 snappy
-
编译hadoop 2.x 源码
$ mvn package -Pdist,native,docs -DskipTests -Dtar -Drequire.snappy -
编译完毕之后，会有一个 hadoop-2.5.0/target/haddoop-2.5.0/lib/native，我们只需要替换一下 native 下的文件就可以了
-
检查
[root@hadoop00 hadoop-2.5.0]# bin/hadoop checknative 21/04/06 14:40:18 WARN bzip2.Bzip2Factory: Failed to load/initialize native-bzip2 library system-native, will use pure-Java version 21/04/06 14:40:18 INFO zlib.ZlibFactory: Successfully loaded & initialized native-zlib library Native library checking: hadoop: true /opt/models/hadoop-2.5.0/lib/native/libhadoop.so zlib: true /lib64/libz.so.1 snappy: true /lib64/libsnappy.so.1 lz4: true revision:99 bzip2: false
15.6 压缩测试
-
运行程序在yarn上
[root@hadoop00 hadoop-2.5.0]# bin/yarn jar share/hadoop/mapreduce/hadoop-mapreduce-examples-2.5.0.jar wordcount /user/zhang/mapreduce/wordcount/input/wc.input /user/zhang/mapreduce/wordcount/output/ -
设置mapreduce参数压缩参数，并运行
[root@hadoop00 hadoop-2.5.0]# bin/yarn jar share/hadoop/mapreduce/hadoop-mapreduce-examples-2.5.0.jar wordcount -Dmapreduce.map.output.compress=true -Dmapreduce.map.output.compress.codec=org.apache.hadoop.io.compress.SnappyCodec /user/zhang/mapreduce/wordcount/input/wc.input /user/zhang/mapreduce/wordcount/output2/
-
15.7 在 Hive 中设置压缩
hive (default) > set mapreduce.map.output.compress =true ;
hive (default) > set mapreduce.map.output.commpress.codec=org.apache.hadoop.io.compress.SnappyCodec ;
16 Hive Storage 数据存储的格式
16.1 文件格式
file_format:
``| SEQUENCEFILE -- (序列化文件，hadoop支持)
``| 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
-
数据存储方式
-
按行存储数据
- SEQUENCEFILE
- TEXTFILE
-
按列存储数据
-
RCFILE
-
ORC（Optimized Row Columnar File）: hive/shark/spar支持
-
使用ORCFIle格式存储列数较多的表
-
-
PARQUET
-
PARQUET
-
-
-
我们在进行数据处理的时候，实质是在对表中的字段进行处理，更多的时候是在对列进行处理，所以才会有按列存储的存储方式，提升数据读取的速度
-
在企业中的使用
- TEXTFILE 最基本的数据，元表数据的格式一般是TEXTFILE
- 生成的子表中，一般采用 ORC 和 PARQUET 两种格式
16.2 Columnar VS Row_based
16.3 测试
-
创建不同文件格式的表
-
textfile格式
create table page_views_text( track_time string, url string, session_id string, referenct string, ip string ) row format delimited fields terminated by ',' stored as textfile ; -
OCR 文件格式
create table page_views_orc( track_time string, url string, session_id string, referenct string, ip string ) row format delimited fields terminated by ',' stored as ORC ; -
PARQUET 文件格式
create table page_views_par( track_time string, url string, session_id string, referenct string, ip string ) row format delimited fields terminated by ',' stored as PARQUET ;
-
-
向表中插入相同的数据
load data local inpath '/opt/data/UserBehavior/UserBehavior.csv' into table default.page_views_text ; insert into table default.page_views_orc select * from page_views_text ; insert into table default.page_views_par select * from page_views_text ; -
查看文件大小
hive (default) > dfs -du -h /user/hive/warehouse/page_views_text ; hive (default) > dfs -du -h /user/hive/warehouse/page_views_orc ; hive (defualt) > dfs -du -h /user/hive/warehouse/page_views_par ; -
查询语句，进行时间对比
select session_id, count(*) cnt from page_views_text group by session_id order by cnt ; select session_id, count(*) cnt from page_views_orc group by session_id order by cnt ; select session_id, count(*) cnt from page_views_par group by session_id order by cnt ;
16.4 存储格式 & 压缩 结合使用（实际运用）
create table page_views_orc_snappy(
track_time string,
url string,
session_id string,
referenct string,
ip string
)
row format delimited fields terminated by ','
stored as ORC tblproperties("orc.compress"="SNAPPY") ;
16.5 总结
- 在实际的开发中，hive表数据
- 存储格式：orcfile / parquet
- 数据压缩：snappy
17 企业优化 1
17.1 FetchTask 抓取数据
-
为什么有的sql执行mapreduce，而有的却没有？
-
配置属性 hive.fetch.task.conversion
在企业中，通常使用 more 选项
<property> <name>hive.fetch.task.conversion</name> <value>more</value> <description> Some select queries can be converted to single FETCH task minimizing latency. Currently the query should be single sourced not having any subquery and should not have any aggregations or distincts (which incurs RS), lateral views and joins. 1. minimal : SELECT STAR, FILTER on partition columns, LIMIT only 2. more : SELECT, FILTER, LIMIT only (TABLESAMPLE, virtual columns) </description> </property> -
17.2 Hive 高级优化
17.2.1 大表【拆分】
- 子表
17.2.2 外部表、分区表
- 结合使用
- 多级分区
17.2.3 数据
- 存储格式（textfile、orcfile、parquet）
- 数据压缩（snappy）
17.2.4 SQL
-
优化SQL语句(例如 predict pushdown)
select e.a, e.b, d.h, d.f from (select ... from emp where ....) e join (select ... from emp where ....) d on (e.deptno = d.deptno)join，filter
17.2.5 join (优化是重点)
-
common/shuffle/reduce join
- 连接发生在 reduce 任务阶段
- 特点：大表对大表
- 每个表的数据都是从文件中读取的
-
map join
- 连接发生在 map 任务阶段
- 特点：小表对大表
- 大表的数据从文件中读取
- 小表的数据在内存中，放到内存中的实现类是：DistributedCache
-
SMB join Sort-Merge-Bucket join(大公司)
- sort ： 排序
- merge：合并，将相同的数据放在同一个分区里面
- bucket：桶，在创建表的时候，有一个桶的概念。指定数据平均分配到多少个地方
- 是对大表对大表的优化
- clustered by : distribute 和 sort 字段相同时，使用方式，是 distribute by 和 sort by 的结合
- distribute by：类似 MR 中 parition，进行分区，结合sort by 使用
- sort by：每个 reduce 内部进行排序，全局不是排序
- order by：全局排序，一个 reduce
- 两个表的桶数目要一样，分区字段要相同
-
join 相关属性设置
-
让sql语句自动识别是表的大小以及是大表对大表还是大表对小表，然后自动的去转为对应的 join 方式
hive > set hive.auto.convert.join = true ;
-
-
smb join 相关属性设置
# 当我们提前知道是大表对大表的时候，可以提前设置这三个属性 set hive.auto.convert.sortmerge.join=true ; set hive.optimize.bucketmapjoin=true ; set hive.optimize.bucketmapjoin=true ;
17.2.6 like : 慎用，并且不要同时使用两个占位符
17.2.7 并行执行
# 是否启用并行执行
hive.exec.parallel
hive.exec.parallel.thread.number
17.2.8 JVM重用
基本上能够节约三分之一到三分之二的时间
通常情况下，一个jvm（容器）中只运行一个mapreduce任务，这样会浪费很多容器启动的时间，通过这只，可以让一个jvm（容器）运行多个mapreduce
# 该值不能最好不要超过 9
mpareduce.job.jvm.numtasks = 1
17.2.9 Reduce 数目
-
map的任务数目默认情况下是由块的大小决定
-
reduce的任务数目是由任务决定的：mapreduce.job.reduces
-
在实际运用中，最好的设置方法是进行测试
-
调试的标准：基本上所有的reduce任务完成的时间都在一个时间段之内
17.2.10 推测执行 -- 属于mapreduce的调优
默认是启用的。当启用一个任务的时候，ApplicationMaster 会根据一些判断的标准，在另外一台机器上新启动一个相同的任务，然后判断两个任务哪个先完成，就使用哪个的结果，然后将另外一个任务关掉。
hive.mapred.reduce.tasks.speculative.execution
mapreduce.map.speculative
mapreduce.reduce.speculative
我们在写hive语句的时候，应该将这写相关的参数给关掉
17.2.7 Map数目
主要还是根据块的大小来设置
hive.merge.size.per.task 256000000
17.2.8 动态分区调整
- hive.exec.dynamic.partiton：
- 设置为 true 表示开启动态分区功能（默认为false）： hive.exec.dynamic.partiton=true
- hive.exec.dynamic.partition.mode：
- 设置为 nonstrict，表示允许所有分区都是动态的（默认为 strict）
- 设置为 strict ，表示必须保证至少有一个分区是静态的
- hive.exec.dynamic.partition.mode=strict
- hive.exec.max.dynamic.patitions.pernode = 100
- 每个mapper或reducer可以创建的最大动态分区个数
- hive.exec.max.dynamic.partitons = 1000
- 一个动态分区创建语句可以创建的最多的动态分区数
- hive.exec.max.created.files =100000
- 全局可以创建的最大文件个数
- 场景
- 自动创建分区
17.2.9 strict mode （hive.mapred.mode）
-
对分区表进行查询，在where子句中没有加分区过滤的话，将禁止提交任务（默认：nonstrict）
-
使用严格模式可以禁止三种类型的查询
-
对于分区表，不加分区字段过滤条件，不能执行
# 未加分区过滤，会出错 select * from default.dept_par where dept_par.deptno = 10 ; # 正确做法 select * from default.dept_par where dept_par.deptno = 10 and month='202109' ; -
对于order by语句，必须使用 limit 语句
-
限制笛卡尔积查询（join 的时候不使用 on，而使用 where 的）
-
严格模式的好处
- 会去检查我们的sql
-
18 Explain Execution Plan
查看一个任务需要执行几个map和几个reduce
explain [extended|dependency] query
- explain select ename from default.emp ;
- explain select deptno, avg(sal) avg_sal from emp group by deptno ;
-
查看更加详细的执行计划 extended
hive > explain extended select deptno, avg(sal) avg_sal from emp group by deptno ;-
可以查看到些什么
-
The Abstract Syntax Tree for the query
-
The dependencies between the differenct stages of the plan
-
The description of each of the stages
-
-
19 实战
19.1 思路
- 原表
- 针对不同的业务创建不同的子表
- 数据存储格式 orcfile/parquet
- 数据压缩 snappy
- map output 数据压缩 snappy
- 外部表
- 创建分区表
19.2 正则表达式
https://issues.apache.org/jira/browse/HIVE-662
http
-
Apache Weblog Data
- 对于数据导入的处理
- 对用户自定义的数据进行序列化处理
CREATE TABLE serde_regex( host STRING, identity STRING, user STRING, time STRING, request STRING, status STRING, size STRING, referer STRING, agent STRING) ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.RegexSerDe' WITH SERDEPROPERTIES ( "input.regex" = "([^ ]*) ([^ ]*) ([^ ]*) (-|\\[[^\\]]*\\]) ([^ \"]*|\"[^\"]*\") (-|[0-9]*) (-|[0-9]*)(?: ([^ \"]*|\"[^\"]*\") ([^ \"]*|\"[^\"]*\"))?", "output.format.string" = "%1$s %2$ %3$s %4$s %5$s %6$s %7$s %8$s %9$s" ) STORED AS TEXTFILE; LOAD DATA LOCAL INPATH "../data/files/apache.access.log" INTO TABLE serde_regex; LOAD DATA LOCAL INPATH "../data/files/apache.access.2.log" INTO TABLE serde_regex; SELECT * FROM serde_regex ORDER BY time;- 正则表达式
- 一个 （） 括起来的就是一个字段
- [] 代表可有可无（）
19.3 get started
- 依据业务数据表
- 方式一：原始表 bf_log_src， 加载数据（预先处理）
- 方式二：创建正则表达 RegexSerDe
- 数据ETL
- 拆分表（子表）、数据存储格式
- 数据预处理ETL（udf、python）
- 数据分析HQL
19.4 数据清洗
-
定义UDF，对原表数据进行清洗
-
第一个UDF，去除引号
package com.zhang.udf; import org.apache.hadoop.io.Text; public class RemoveQuotesUDF { public Text evaluate(Text str){ if(null == str){ return new Text(); } if(null == str.toString()){ return new Text(); } return new Text(str.toString().replaceAll("\"","")); } public static void main(String[] args) { System.out.println(new RemoveQuotesUDF().evaluate(new Text("\"HIVE\""))); } } -
第二个UDF：格式化时间字段
package com.zhang.udf; import org.apache.hadoop.io.Text; import java.text.SimpleDateFormat; import java.util.Date; import java.util.Locale; public class DateTransformUDF { private final SimpleDateFormat inputFormat = new SimpleDateFormat("dd/MMM/yyyy:HH:mm:ss", Locale.ENGLISH) ; private final SimpleDateFormat outputFormat = new SimpleDateFormat("yyyyMMddHHmmss") ; public Text evaluate(Text input){ Text output = new Text(); if(null == input){ return new Text(); } String inputDate = input.toString().trim() ; if(null == inputDate) { return null ; } try{ // parse Date parseDate = inputFormat.parse(inputDate) ; // tranform String outputDate = outputFormat.format(parseDate); //set output.set(outputDate) ; }catch(Exception e){ e.printStackTrace(); return output ; } return output ; } public static void main(String[] args) { System.out.println(new DateTransformUDF().evaluate(new Text("31/Aug/2015:00:04:37 +0800"))); } }
-
-
截取字段
-
substring
- 下标：从1开始计数
select t.prex_ip, count(*) cnt from ( select substring(remote_ip,1,7) prex_ip from bf_log_comm ) t group by t.remote_ip order by cnt desc limit 20 ;
-
19.5 数据清洗之python
-
创建原表并导入数据
CREATE TABLE u_data ( userid INT, movieid INT, rating INT, unixtime STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS TEXTFILE; LOAD DATA LOCAL INPATH '/opt/data/ml-100k/u.data' OVERWRITE INTO TABLE u_data; -
weekday_mapper.py
import sys
import datetime
for line in sys.stdin:
line = line.strip()
userid, movieid, rating, unixtime = line.split('\t')
weekday = datetime.datetime.fromtimestamp(float(unixtime)).isoweekday()
print '\t'.join([userid, movieid, rating, str(weekday)])
- 具体使用
drop table if exists u_data_new ;
CREATE TABLE u_data_new (
userid INT,
movieid INT,
rating INT,
weekday INT)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t';
add FILE /opt/data/py/weekday_mapper.py;
INSERT OVERWRITE TABLE u_data_new
SELECT
TRANSFORM (userid, movieid, rating, unixtime)
USING 'python weekday_mapper.py'
AS (userid, movieid, rating, weekday)
FROM u_data;
SELECT weekday, COUNT(*) cnt FROM u_data_new GROUP BY weekday order by cnt; -- Time taken: 36.461 seconds, Fetched: 7 row(s)
SELECT weekday, COUNT(1) cnt FROM u_data_new GROUP BY weekday order by cnt; -- Time taken: 36.231 seconds, Fetched: 7 row(s)
SELECT weekday, COUNT(*) cnt FROM u_data_new GROUP BY weekday order by cnt desc ; -- Time taken: 37.422 seconds, Fetched: 7 row(s)
SELECT weekday, COUNT(1) cnt FROM u_data_new GROUP BY weekday order by cnt desc ; -- Time taken: 35.661 seconds, Fetched: 7 row(s)
- 在select中使用python脚本
- TRANSFORM -- 输入原表的数据字段
- USING -- 处理的脚本，使用哪个脚本
- AS -- 根据python脚本处理后的输出
浙公网安备 33010602011771号