Hive的详细使用

Hive

1、Hive安装部署

安装Hive

• 第一步：把 apache-hive-3.1.2-bin.tar.gz 上传到 linux 下，并解压

• 第二步：添加到环境变量中（按照自己的路径配置）

  # HIVE_HOME
  export HIVE_HOME=/opt/software/hive/apache-hive-3.1.2-bin
  export PATH=$PATH:$HIVE_HOME/bin
  

• 第三步：初始化元数据

  • bin/schematool -dbType derby -initSchema

• 第四步：启动hdfs、yarn

  • start-all.sh

• 第五步：启动hive

  • bin/hive

安装MySQL

• 第一步：检查系统是否安装过MySQL

  • rpm -qa | grep mariadb
  • 若安装了，使用下面命令将其卸载
    • rpm -e --nodeps mariadb-libs

• 第二步：将mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar上传到linux上，并解压

  • tar -xvf mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar

• 第三步：在安装目录下执行 rpm 安装（按照顺序依次执行）

  rpm -ivh mysql-community-common-5.7.28-1.el7.x86_64.rpm
  rpm -ivh mysql-community-libs-5.7.28-1.el7.x86_64.rpm
  rpm -ivh mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm
  rpm -ivh mysql-community-client-5.7.28-1.el7.x86_64.rpm
  rpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm
  

• 第四步：初始化数据库

  • mysqld --initialize --user=mysql

• 第五步：查看临时生成的 root 用户的密码，方便后续修改密码

  • cat /var/log/mysqld.log

• 第六步：启动MySQL

  • systemctl start mysqld

• 第七步：登录数据库

  • mysql -uroot -p

• 第八步：修改 root 用户的密码

  • set password = password("123456");

• 第九步：修改 mysql 库下的 user 表中的 root 用户允许任意 ip 连接

  • update mysql.user set host='%' where user='root';
  • flush privileges;

Hive元数据配置到MySQL

• 第一步：将 MySQL 的 JDBC 驱动（mysql-connector-java-5.1.37.jar）拷贝到 Hive 的 lib 目录下

• 第二步：在$HIVE_HOME/conf 目录下新建 hive-site.xml 文件，并添加如下内容（按照自己的环境配置）

  <?xml version="1.0"?>
  <?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
  <configuration>
      <!-- jdbc 连接的 URL -->
      <property>
          <name>javax.jdo.option.ConnectionURL</name>
          <value>jdbc:mysql://hadoop151:3306/metastore?useSSL=false</value>
      </property>
      <!-- jdbc 连接的 Driver-->
      <property>
          <name>javax.jdo.option.ConnectionDriverName</name>
          <value>com.mysql.jdbc.Driver</value>
      </property>
      <!-- jdbc 连接的 username-->
      <property>
          <name>javax.jdo.option.ConnectionUserName</name>
          <value>root</value>
      </property>
      <!-- jdbc 连接的 password -->
      <property>
          <name>javax.jdo.option.ConnectionPassword</name>
          <value>123456</value>
      </property>
      <!-- Hive 元数据存储版本的验证 -->
      <property>
          <name>hive.metastore.schema.verification</name>
          <value>false</value>
      </property>
      <!--元数据存储授权-->
      <property>
          <name>hive.metastore.event.db.notification.api.auth</name>
          <value>false</value>
      </property>
      <!-- Hive 默认在 HDFS 的工作目录 -->
      <property>
          <name>hive.metastore.warehouse.dir</name>
          <value>/user/hive/warehouse</value>
      </property>
  </configuration>
  

• 第三步：登录MySQL并创建元数据库(metastore)

  • mysql -uroot -p
  • create database metastore;
  • quit，退出MySQL客户端

• 第四步：初始化Hive元数据库

  • bin/schematool -initSchema -dbType mysql -verbase

• 启动Hive

  • /bin/hive

访问Hive的方式

使用元数据服务的方式访问 Hive

• 第一步：在 hive-site.xml 文件中添加配置

      <!-- 指定存储元数据要连接的地址 -->
      <property>
          <name>hive.metastore.uris</name>
          <value>thrift://hadoop151:9083</value>
      </property>
  

• 第二步：启动metastore

  # 前台启动，打印日志
  bin/hive --service metastore
  
  # 后台启动
  nohup bin/hive --service metastore >/dev/null 2>&1 &
  

• 第三步：进入hive

  • bin/hive

使用 JDBC 方式访问 Hive

• 第一步：在 hive-site.xml 文件中添加配置

      <!-- 指定存储元数据要连接的地址 -->
      <property>
          <name>hive.metastore.uris</name>
          <value>thrift://hadoop151:9083</value>
      </property>
      <!-- 指定 hiveserver2 连接的 host -->
      <property>
          <name>hive.server2.thrift.bind.host</name>
          <value>hadoop151</value>
      </property>
      <!-- 指定 hiveserver2 连接的端口号 -->
      <property>
          <name>hive.server2.thrift.port</name>
          <value>10000</value>
      </property>
  

• 第二步：启动metastore

  bin/hive --service metastore

• 第三步：启动 hiveserver2

  bin/hive --service hiveserver2

• 第四步：启动 beeline 客户端

  • bin/beeline -u jdbc:hive2://hadoop151:10000 -n root

Hive 常见属性配置

运行日志信息配置

• 将 /conf/hive-log4j2.properties.template 文件名改为 hive-log4j2.properties，并修改日志目录
  • property.hive.log.dir = /opt/software/hive/apache-hive-3.1.2-bin/logs

打印 当前库 和 表头

• 在 hive-site.xml 中加入配置

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

2、Hive 数据类型

基本数据类型

Hive 数据类型	Java 数据类型	长度	例子
TINYINT	byte	1byte 有符号整数	20
SMALINT	short	2byte 有符号整数	20
INT	int	4byte 有符号整数	20
BIGINT	long	8byte 有符号整数	20
BOOLEAN	boolean	布尔类型，true 或者false	TRUE FALSE
FLOAT	float	单精度浮点数	3.14159
DOUBLE	double	双精度浮点数	3.14159
STRING	string	字符系列。可以指定字符集	‘now is the time’ “for all good men”
TIMESTAMP		时间类型
BINARY		字节数组

集合数据类型

数据类型	描述	语法示例
STRUCT	和c 语言中的 struct 类似，可通过“点”符号访问元素内容	struct()例如struct<street:string, city:string>
MAP	MAP是键-值对元组集合，使用数组表示法可以访问数据	map()例如 map<string, int>
ARRAY	数组是一组具有相同类型和名称的变量的集合	Array()例如 array<string>

类型转化

• 任何整数类型都可以隐式地转换为一个范围更广的类型，如 TINYINT 可以转换成 INT，INT 可以转换成 BIGINT
• 所有整数类型、FLOAT 和 STRING 类型都可以隐式地转换成 DOUBLE
• TINYINT、SMALLINT、INT 都可以转换为 FLOAT
• BOOLEAN 类型不可以转换为任何其它的类型

3、DDL 数据定义

数据库

• 显示数据库
  • show databases;
• 过滤显示查询的数据库（查询数据库名为 hive 开头的）
  • show databases like 'hive*'
• 显示数据库信息
  • desc database 数据库名称;
• 显示数据库详细信息，extende
  • desc database extended 数据库名称;
• 切换当前数据库
  • use 数据库名称;
• 修改数据库（来设置这个数据库的属性信息）
  • alter database 数据库名称 set dbproperties('属性名'='属性内容');
• 删除空数据库
  • drop database 数据库名称;
• 删除非空数据库
  • drop database 数据库名称 cascade;

数据表

• 建表语法

  CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name 
  [(col_name data_type [COMMENT col_comment], ...)] 
  [COMMENT table_comment]
  [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] 
  [CLUSTERED BY (col_name, col_name, ...)
  [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] 
  [ROW FORMAT row_format]
  [STORED AS file_format] 
  [LOCATION hdfs_path]
  [TBLPROPERTIES (property_name=property_value, ...)] 
  [AS select_statement]
  
  
  CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name  --EXTERNAL：创建外部表，IF NOT EXISTS：是否存在
  [(col_name data_type [COMMENT col_comment], ...)]  --列名  列类型  列注释
  [COMMENT table_comment]  --表注释
  [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]  --PARTITIONED BY：创建分区表
  [CLUSTERED BY (col_name, col_name, ...)]  --CLUSTERED BY：创建分桶表
  [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]  --SORTED BY：对桶中的字段进行排序
  [ROW FORMAT DELIMITED row_format]  --ROW FORMAT DELIMITED：一行数据分割的符号
  [STORED AS file_format]  --STORED AS：指定数据类型
  [LOCATION hdfs_path]  --LOCATION：指定表在 HDFS 上的存储位置
  [TBLPROPERTIES (property_name=property_value, ...)]  --TBLPROPERTIES：表的一些配置
  [AS select_statement]  --AS：后跟查询语句，根据查询结果创建表
  
  
  --创建数据表
  create external table if not exists psq(id int, name string)
  row format delimited fields terminated by ','
  stored as textfile
  location '/fzk';
  

• 查询表的类型

  • desc formatted 数据表;

• 将表修改成外部表（‘EXTERNAL’=‘TRUE’ ： 区分大小写）

  • alter table 数据表 set tblproperties('EXTERNAL'='TRUE')

• 将表修改成内部表（‘EXTERNAL’=‘FALSE’ ： 区分大小写）

  • alter table 数据表 set tblproperties('EXTERNAL'='FALSE');

• 重命名表

  • alter table 旧数据表名 rename to 新数据表名

• 更新列

  • alter table 表名 change 旧列名 新列名 新列数据类型;

• 增加和替换列（add：增加， replace：替换）

  • alter table jzx add|replace columns (列名 列数据类型);

• 删除表

  • drop table 表名;

  
  
  
  

4、DML 数据操作

数据导入（五种）

• 第一种：向表中装载数据（Load）

  • load data [local] inpath '数据的路径' [overwrite] into table 表名 [partition (partcoll=val,...)]
    • local：表示从本地加载数据到 hive 表；否则从HDFS 加载数据到 hive 表
    • inpath：表示加载数据的路径
    • overwrite：表示覆盖表中已有数据，否则表示追加
    • into table：表示加载到哪张表
    • partition：表示上传到指定分区

• 第二种：通过查询语句向表中插入数据（insert）

  • insert [into|overwrite] table 表名 values(数据,...)
    • into：追加数据
    • overwrite：覆盖数据并添加

• 第三种：查询语句中创建表并加载数据（As Select）（将table_2的数据加载到table_1中）

  • create table if not exists table_1 as select id, name from table_2

• 第四种：将数据上传到 HDFS 的表的路径中

  • dfs -put /文件路径/文件名 /上传的hdfs路径

• 第五种： Import 数据到指定 Hive 表中

  • 先用 export 导出后，再将数据导入

  • 将hdfs路径的数据导入到表中

    • import table 表名 from '/hdfs的路径'

  
  
  
  

数据导出

• 第一种：insert 导出

  insert overwrite local directory '/路径'
  row format delimited fields terminated by '\t'
  select * from 表名;
  
  --解释
  insert overwrite local directory '/路径'  --local：导出到本地，若没有local则导出到HDFS
  row format delimited fields terminated by '\t'  --将查询的结构按照 '\t' 分隔符格式化
  select * from 表名;  --查询语句
  

• 第二种：Export 导出到 HDFS 上

  • export table 表名 to /hdfs路径;

清除表中的数据（truncate）

• truncate table 表名;

5、查询

普通查询

• **基本查询、算术运算符、比较运算符、逻辑运算符、分组(group by、hiving)、连接(join)**查询语句和SQL一样

排序

全局排序（order by）

• Order By：全局排序，只有一个 Reducer
• ASC ：升序（默认），DESC ：降序
• 用法和SQl一样

每个Reduce内部排序（Sort By）

• Sort By：对于大规模的数据集 order by 的效率非常低。在很多情况下，并不需要全局排序，此时可以使用 sort by。
• Sort by 为每个 reducer 产生一个排序文件。每个 Reducer 内部进行排序，对全局结果集来说不是排序
• select * from 表名 sort by 字段名 [asc|desc];

分区（Distribute By）

• Distribute By： 在有些情况下，我们需要控制某个特定行应该到哪个 reducer，通常是为了进行后续的聚集操作。distribute by 子句可以做这件事。distribute by 类似 MR 中 partition （自定义分区），进行分区，结合 sort by 使用。
• select * from 表名 distribute by 字段名1 sort by 字段名2 [asc|desc];

Cluster By

• 当 distribute by 和 sorts by 字段相同时，可以使用 cluster by 方式。
• cluster by 除了具有 distribute by 的功能外还兼具 sort by 的功能。但是排序只能是升序排序，不能指定排序规则为 ASC 或者 DESC
• select * from 表名 cluster by 字段名;

6、分区表和分桶表

分区表

分区基本操作

• 创建分区

  create table 表名(字段名 字段类型, ...)
  partitioned by (分区名称 分区类型)
  row format delimited fields terminated by '\t';
  
  --解释
  create table 表名(字段名 字段类型, ...)  --建表语句
  partitioned by (分区名称 分区类型)  --分区
  row format delimited fields terminated by '\t';  --行初始化
  
  --事例
  create table dept_partitioned(id int, name string, sal string)
  partitioned by (day string)
  row format delimited fields terminated by '\t';
  

• 加载数据到表中

  load data local inpath '/本地文件' into table 表名 partition(分区名称=分区内容);
  
  --事例（将本地的dept001文件加载到dept_partitioned表的 'day=2021-04-22' 分区）
  load data local inpath '/opt/software/hive/data/partitioned/dept001' into table dept_partitioned partition(day='2021-04-22');
  

• 查询分区表中数据

  select * from 表名 where 分区字段名 = 条件;
  
  --事例（查询分区为 day = '2021-04-22' 的数据）
  select * from dept_partitioned where day = '2021-04-22';
  

• 增加分区

  alter table 表名 add partition(分区字段名=分区内容);
  
  --事例（添加分区为 day='2021-04-23'）
  alter table dept_partition add partition(day='2021-04-23');
  

• 删除分区

  alter table 表名 drop partition(分区字段名=条件);
  
  --事例（删除分区为 day='2021-04-23'）
  alter table dept_partitioned drop partition(day='2021-04-23');
  

• 列出分区表中的分区

  • show partitions 表名;

二级分区

• 创建二级分区

  create table 表名(字段名 字段类型, ...)
  partitioned by (一级分区名称 一级分区类型, 二级分区名称 二级分区类型)
  row format delimited fields terminated by '\t';
  
  --解释
  create table 表名(字段名 字段类型, ...)  --建表语句
  partitioned by (一级分区名称 一级分区类型, 二级分区名称 二级分区类型)  --分区
  row format delimited fields terminated by '\t';  --行初始化
  
  --事例
  create table dept_partitioned(id int, name string, sal string)
  partitioned by (day string, hour string)
  row format delimited fields terminated by '\t';
  

• 加载数据到表中

  load data local inpath '/本地文件' into table 表名 partition(一级分区名称=一级分区内容, 二级分区名称=二级分区内容);
  
  --事例（将本地的dept001文件加载到dept_partitioned表的 'day=2021-04-22, hour=10' 分区）
  load data local inpath '/opt/software/hive/data/partitioned/dept001' into table dept_partitioned partition(day='2021-04-22', hour='10');
  

动态分区调整

• 关系型数据库中，对分区表 Insert 数据时候，数据库自动会根据分区字段的值，将数据插入到相应的分区中，Hive 中也提供了类似的机制，即动态分区(Dynamic Partition)，只不过，使用 Hive 的动态分区，需要进行相应的配置

• 开启动态分区参数设置

  • 开启动态分区功能（默认 true，开启）
    • set hive.exec.dynamic.partition=true;
  • 设置为非严格模式（动态分区的模式，默认 strict，表示必须指定至少一个分区为静态分区，nonstrict 模式表示允许所有的分区字段都可以使用动态分区）
    • set hive.exec.dynamic.partition.mode=nonstrict;
  • 在所有执行 MR 的节点上，最大一共可以创建多少个动态分区。默认 1000
    • set hive.exec.max.dynamic.partitions=1000;
  • 在每个执行 MR 的节点上，最大可以创建多少个动态分区。该参数需要根据实际的数据来设定。比如：源数据中包含了一年的数据，即 day 字段有 365 个值，那么该参数就需要设置成大于 365，如果使用默认值 100，则会报错
    • set hive.exec.max.dynamic.partitions.pernode=100;
  • 整个 MR Job 中，最大可以创建多少个 HDFS 文件。默认 100000
    • set hive.exec.max.created.files=100000;
  • 当有空分区生成时，是否抛出异常。一般不需要设置。默认 false
    • set hive.error.on.empty.partition=false;

分桶表

• 创建分桶

  create table 表名(字段名 字段类型, ...)
  clustered by(字段名)
  into 4 buckets
  row format delimited fields terminated by '\t';
  
  --解释
  create table 表名(字段名 字段类型, ...)  --建表语句
  clustered by(字段名)  --分桶
  into 4 buckets  --分四个分桶
  row format delimited fields terminated by '\t';  --行格式化
  
  --事例
  create table bucket(id int, name string)
  clustered by(id)
  into 4 buckets
  row format delimited fields terminated by '\t';
  

• 导入数据到分桶表

  load data local inpath '/本地文件' into table 表名;
  
  --事例（将本地bucket.txt文件的数据导入到分桶表中）
  load data local inpath '/opt/software/hive/data/bucket/bucket.txt' into table bucket;
  

• 查看分桶表数据

  • select * from 表名;

• 抽样查询

  • 对于非常大的数据集，有时用户需要使用的是一个具有代表性的查询结果而不是全部结果。Hive 可以通过对表进行抽样来满足这个需求

  • 语法: TABLESAMPLE(BUCKET x OUT OF y)（x 的值必须小于等于 y 的值）

    select * from 表名 tablesample(bucket x out of y on id);
    
    --事例
    select * from bucket tablesample(bucket 1 out of 4 on id);
    

7、函数

系统内置函数

• 查看系统自带的函数
  • show functions
• 显示自带的函数的用法
  • desc function 函数名称
• 详细显示自带的函数的用法
  • desc function extended 函数名称

常用内置函数

• NVL：给值为 NULL 的数据赋值，它的格式是 NVL( value，default_value)。它的功能是如果 value 为 NULL，则 NVL 函数返回 default_value 的值，否则返回 value 的值，如果两个参数都为 NULL ，则返回 NULL

  select nvl(字段名, 数据) from 表名;
  
  --例子：当comm字段的值为null，则用 -1 代替
  select comm,nvl(comm, -1) from emp;
  

• CASE WHEN THEN ELSE END

  --例子
  select
      dept_id,
      sum(case sex when '男' then 1 else 0 end) male_count,    --sex字段的值为男值为 1，否则 0
      sum(case sex when '女' then 1 else 0 end) female_count   --sex字段的值为女值为 1，否则 0
  from emp_sex
  group by dept_id;
  

行转列

• CONCAT（concat）。concat(str1, str2, ... strN)

  • 将多个字符串拼接后输出

    select concat('a', '-', 'b')  --输出： a-b
    

• CONCAT_WS（concat_ws），concat_ws(separator, [string | array(string)]+)

  • 它是一个特殊形式的 CONCAT()。第一个参数是其他参数间的分隔符

    select concat_ws('-', 'a', 'b', 'c', 'd')  --输出： a-b-c-d
    

• COLLECT_SET（collect_set），collect_set(x)

  • 函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生 Array 类型字段

    select collect_set(deptno) from dept;  -- 将 deptno 字段去重，并产生 Array 类型字段
    

列转行

• EXPLODE（explode），explode(a)

  • 将 hive 一列中复杂的 Array 或者 Map 结构拆分成多行

    -- deptno 字段为 Array 或者 Map 类型，将其拆分成多行
    select explode(deptno) from dept;
    

• LATERAL VIEW（lateral view），lateral view udtf(expression) tableAlias AS columnAlias

  • 用于和 split, explode 等 UDTF 一起使用，能够将一列数据拆成多行数据，在此基础上可对拆分后的数据进行聚合

    SELECT
        movie,
        category_name
    FROM
    	movie_info lateral view explode(split(category,",")) movie_info_tmp AS category_name;  -- 将 category 字段的数据按照 ',' 切割，并显示 
    

窗口函数

相关函数说明

• OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化
  • CURRENT ROW（current row）：当前行
  • n PRECEDING（n preceding）：往前 n 行数据
  • n FOLLOWING（n following）：往后 n 行数据
  • UNBOUNDED（unbounded）：起点/终点
    • UNBOUNDED PRECEDING（unbounded preceding）：表示从前面的起点，
    • UNBOUNDED FOLLOWING（unbounded following）：表示到后面的终点
• LAG（lag） ：往前第 n 行数据
  • lag (字段名, n, 默认数据) over (...)
• LEAD（lead）：往后第 n 行数据
  • lead (字段名, n, 默认数据) over (...)
• NTILE（ntile）：把有序窗口的行分发到指定数据的组中，各个组有编号，编号从 1 开始，对于每一行，NTILE 返回此行所属的组的编号。注意：n 必须为 int 类型
  • ntile(n)
• Rank
  • rank() ：排序相同时会重复，总数不会变（例如第二名重复：1 2 2 4）
  • dense_rank() ：排序相同时会重复，总数会减少（例如第二名重复：1 2 2 3）
  • row_number() ：会根据顺序计算（例如第二名重复：1 2 3 4）

使用

-- over() 的使用
select name, orderdate, cost,
    sum(cost) over() as sample1,  --所有行相加
    sum(cost) over(partition by name) as sample2,  --按 name 分组，组内数据相加
    --按 name 分组，按 orderdate 升序，组内数据累加
    sum(cost) over(partition by name order by orderdate) as sample3,  
    --按 name 分组，按 orderdate 升序，由起点到当前行的聚合 相加
    sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and current row ) as sample4 ,  
    sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING and current row) as sample5,  --当前行和前面一行做聚合
    sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING AND 1 FOLLOWING ) as sample6,  --当前行和前边一行及后面一行做聚合
    sum(cost) over(partition by name order by orderdate rows between current row and UNBOUNDED FOLLOWING ) as sample7  --当前行及后面所有行做聚合
from business;


-- lag() 的使用
select name,orderdate,cost,
	--显示orderdate字段的往前 1 行数据，若为null在显示为 '1900-01-01'
    lag(orderdate, 1, '1900-01-01') over(partition by name order by orderdate ) as time1,
    ----显示orderdate字段的往后 2 行数据
    lead(orderdate, 2) over (partition by name order by orderdate) as time2  
from business;


--ntile() 的使用（查询前20%的数据）
select * from (
    --按照orderdate升序排列，并将数据分为 5 份
	select name, orderdate, cost, ntile(5) over(order by orderdate) sorted
	from business) t
where sorted = 1;


-- rank 的使用
select name, subject, score,
    rank() over(partition by subject order by score desc) rp,  --rank()的使用
    dense_rank() over(partition by subject order by score desc) drp,  --dense_rank()的使用，
    row_number() over(partition by subject order by score desc) rmp  --row_number()的使用，
from score;

其他常用函数

----------------------常用日期函数
--unix_timestamp:返回当前或指定时间的时间戳	
select unix_timestamp();
select unix_timestamp("2020-10-28",'yyyy-MM-dd');

--from_unixtime：将时间戳转为日期格式
select from_unixtime(1603843200);

--current_date：当前日期
select current_date;

--current_timestamp：当前的日期加时间
select current_timestamp;

--to_date：抽取日期部分
select to_date('2020-10-28 12:12:12');

--year：获取年
select year('2020-10-28 12:12:12');

--month：获取月
select month('2020-10-28 12:12:12');

--day：获取日
select day('2020-10-28 12:12:12');

--hour：获取时
select hour('2020-10-28 12:12:12');

--minute：获取分
select minute('2020-10-28 12:12:12');

--second：获取秒
select second('2020-10-28 12:12:12');

--weekofyear：当前时间是一年中的第几周
select weekofyear('2020-10-28 12:12:12');

--dayofmonth：当前时间是一个月中的第几天
select dayofmonth('2020-10-28 12:12:12');

--months_between： 两个日期间的月份
select months_between('2020-04-01','2020-10-28');

--add_months：日期加减月
select add_months('2020-10-28',-3);

--datediff：两个日期相差的天数
select datediff('2020-11-04','2020-10-28');

--date_add：日期加天数
select date_add('2020-10-28',4);

--date_sub：日期减天数
select date_sub('2020-10-28',-4);

--last_day：日期的当月的最后一天
select last_day('2020-02-30');

--date_format(): 格式化日期
select date_format('2020-10-28 12:12:12','yyyy/MM/dd HH:mm:ss');

----------------------常用取整函数
--round： 四舍五入
select round(3.14);
select round(3.54);

--ceil：  向上取整
select ceil(3.14);
select ceil(3.54);

--floor： 向下取整
select floor(3.14);
select floor(3.54);

----------------------常用字符串操作函数
--upper： 转大写
select upper('low');

--lower： 转小写
select lower('low');

--length： 长度
select length("atguigu");

--trim：  前后去空格
select trim(" atguigu ");

--lpad： 向左补齐，到指定长度
select lpad('atguigu',9,'g');

--rpad：  向右补齐，到指定长度
select rpad('atguigu',9,'g');

--regexp_replace：使用正则表达式匹配目标字符串，匹配成功后替换！
SELECT regexp_replace('2020/10/25', '/', '-');

----------------------集合操作
--size： 集合中元素的个数
select size(friends) from test3;

--map_keys： 返回map中的key
select map_keys(children) from test3;

--map_values: 返回map中的value
select map_values(children) from test3;

--array_contains: 判断array中是否包含某个元素
select array_contains(friends,'bingbing') from test3;

--sort_array： 将array中的元素排序
select sort_array(friends) from test3;

自定义函数

• Hive 自带了一些函数，比如：max/min 等，但是数量有限，自己可以通过自定义 UDF 来方便的扩展
• 当 Hive 提供的内置函数无法满足你的业务处理需要时，此时就可以考虑使用用户自定义函数

自定义UDF函数

• 需求：自定义一个 UDF 实现计算给定字符串的长度

  select my_len("fzk")  -- 4
  

• 第一步：创建Maven工程，并导入依赖（pom.xml）

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

• 第二步：编写实现类

  import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
  import org.apache.hadoop.hive.ql.exec.UDFArgumentLengthException;
  import org.apache.hadoop.hive.ql.metadata.HiveException;
  import org.apache.hadoop.hive.ql.udf.generic.GenericUDF;
  import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
  import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;
  
  public class MyUDF extends GenericUDF {
      public ObjectInspector initialize(ObjectInspector[] objectInspectors) throws UDFArgumentException {
          //判断输入参数的个数
          if(objectInspectors.length != 1){
              throw new UDFArgumentLengthException("只能输入一个参数");
          }
  
          //函数本身返回值为 int，需要返回 int 类型的鉴别器对象
          return PrimitiveObjectInspectorFactory.javaIntObjectInspector;
      }
  
      public Object evaluate(DeferredObject[] deferredObjects) throws HiveException {
          String data = deferredObjects[0].get().toString();
          if(data == null){
              return 0;
          }
  
          return data.length();
      }
  
      public String getDisplayString(String[] strings) {
          return null;
      }
  }
  

• 第三步：将程序打包成 jar 包上传到服务器上

• 第四步：创建函数

  • 方法一：创建永久函数与开发好的java class关联（开发环境使用）

    # 第一步：将jar包上传到hdfs上
    hadoop fs -put jar包全名称 /上传到hdfs的路径
    
    # 第二步：创建永久函数
    hive (default)> create function 函数名称 as '全限定类名' using jar 'jar包在hdfs地址';
    #例子
    hive (default)> create function my_len as 'com.itfzk.hive.udf.MyUDF' using jar 'hdfs://hadoop151:8020/user/hive/jars/my_len.jar';
    

  • 方法二：创建临时函数与开发好的 java class 关联（在 hive 客户端中执行）（开发环境不用）

    #第一步：在 hive 客户端中将 jar 包添加到 hive中
    hive (default)> add jar /上传的jar包的位置和名称;
    
    #第二步：创建临时函数
    hive (default)> create temporary function 函数别名 as "java类的全限定类名";
    # 例子
    hive (default)> create temporary function my_len as "com.itfzk.hive.udf.MyUDF";
    

• 第五步：使用

  • select my_len("fzk")

自定义UDTF函数

• 需求：自定义一个 UDTF 实现将一个任意分割符的字符串切割成独立的单词

  select my_udtf("fff,zzz,kkk", ",")
  --输出
  fff
  zzz
  kkk
  

• 第一步：创建Maven工程，并导入依赖（pom.xml）

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

• 第二步：编写实现类

  import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
  import org.apache.hadoop.hive.ql.metadata.HiveException;
  import org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;
  import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
  import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspectorFactory;
  import org.apache.hadoop.hive.serde2.objectinspector.StructObjectInspector;
  import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;
  import java.util.ArrayList;
  import java.util.List;
  
  public class MyUDTF extends GenericUDTF {
  
      @Override
      public StructObjectInspector initialize(StructObjectInspector argOIs) throws UDFArgumentException {
          //定义输出数据的列名和类型
          List<String> fieldNames = new ArrayList<String>();
          List<ObjectInspector> fieldOIs = new ArrayList<ObjectInspector>();
          //列名
          fieldNames.add("word");
          //类型
          fieldOIs.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);
          return ObjectInspectorFactory.getStandardStructObjectInspector(fieldNames, fieldOIs);
      }
  
      public void process(Object[] objects) throws HiveException {
          //获取数据和分割条件，并对数据进行分割
          String data = objects[0].toString();
          String regex = objects[1].toString();
          String[] fields = data.split(regex);
          //输出内容
          List<String> outList = new ArrayList<String>();
          //遍历分割好的数据并写出
          for (String field : fields) {
              outList.clear();
              outList.add(field);
              //将集合内容写出
              forward(outList);
          }
      }
  
      public void close() throws HiveException {
  
      }
  }
  

• 第三步：将程序打包成 jar 包上传到服务器上

• 第四步：创建函数

  • 方法一：创建永久函数与开发好的java class关联（开发环境使用）

    # 第一步：将jar包上传到hdfs上
    hadoop fs -put jar包全名称 /上传到hdfs的路径
    
    # 第二步：创建永久函数
    hive (default)> create function 函数名称 as '全限定类名' using jar 'jar包在hdfs地址';
    #例子
    hive (default)> create function my_udtf as 'com.itfzk.hive.udtf.MyUDTF' using jar 'hdfs://hadoop151:8020/user/hive/jars/my_udtf.jar';
    

  • 方法二：创建临时函数与开发好的 java class 关联（在 hive 客户端中执行）（开发环境不用）

    #第一步：在 hive 客户端中将 jar 包添加到 hive中
    hive (default)> add jar /上传的jar包的位置和名称;
    
    #第二步：创建临时函数
    hive (default)> create temporary function 函数别名 as "java类的全限定类名";
    # 例子
    hive (default)> create temporary function my_udtf as "com.itfzk.hive.udtf.MyUDTF";
    

• 第五步：使用

  • select my_udtf("fff,zzz,kkk", ",")

8、压缩和存储

压缩

压缩介绍

压缩算法对比介绍

压缩格式	Hadoop 自带？	算法	文件扩展名	是否可切片	换成压缩格式后，原来的程序是否需要修改
DEFLATE	是，直接使用	DEFLATE	.deflate	否	和文本处理一样，不需要修改
Gzip	是，直接使用	DEFLATE	.gz	否	和文本处理一样，不需要修改
bzip2	是，直接使用	bzip2	.bz2	是	和文本处理一样，不需要修改
LZO	否，需要安装	LZO	.lzo	是	需要建索引，还需要指定输入格式
Snappy	是，直接使用	Snappy	.snappy	否	和文本处理一样，不需要修改

压缩性能的比较

压缩算法	原始文件大小	压缩文件大小	压缩速度	解压速度
gzip	8.3GB	1.8GB	17.5MB/s	58MB/s
bzip2	8.3GB	1.1GB	2.4MB/s	9.5MB/s
LZO	8.3GB	2.9GB	49.3MB/s	74.6MB/s
Snappy			250MB/s	500MB/s

压缩算法的优缺点比较

压缩算法	优点	缺点
Gzip	压缩率比较高	不支持 Split；压缩/解压速度一般
Bzip2	压缩率高；支持 Split	压缩/解压速度慢
Lzo	压缩/解压速度比较快；支持 Split	压缩率一般；想支持切片需要额外创建索引
Snappy	压缩和解压缩速度快	不支持 Split；压缩率一般

开启 Map 输出阶段压缩

--开启 hive 中间传输数据压缩功能
set hive.exec.compress.intermediate=true;

--开启 mapreduce 中 map 输出压缩功能
set mapreduce.map.output.compress=true;

--设置 mapreduce 中 map 输出数据的压缩方式
set mapreduce.map.output.compress.codec=org.apache.hadoop.io.compress.SnappyCodec;

开启 Reduce 输出阶段压缩

--开启 hive 最终输出数据压缩功能
set hive.exec.compress.output=true;

--开启 mapreduce 最终输出数据压缩
set mapreduce.output.fileoutputformat.compress=true;

--设置 mapreduce 最终数据输出压缩方式
set mapreduce.output.fileoutputformat.compress.codec=org.apache.hadoop.io.compress.SnappyCodec;

--设置 mapreduce 最终数据输出压缩为块压缩
set mapreduce.output.fileoutputformat.compress.type=BLOCK;

文件存储格式

• Hive 支持的存储数据的格式主要有：TEXTFILE 、SEQUENCEFILE、ORC、PARQUET

TextFile 存储格式

• 默认格式，数据不做压缩，磁盘开销大，数据解析开销大。可结合 Gzip、Bzip2 使用，但使用 Gzip 这种方式，hive 不会对数据进行切分，从而无法对数据进行并行操作

  --创建 TextFile 存储格式
  create table log_text (
      字段名 字段类型,
  	...
  )
  row format delimited fields terminated by '\t'
  stored as textfile;  --TextFile 存储格式
  

Parquet 存储格式

• Parquet 文件是以二进制方式存储的，所以是不可以直接读取的，文件中包括该文件的数据和元数据，因此 Parquet 格式文件是自解析的

  --创建 Parquet 存储格式
  create table log_parquet(
  	字段名 字段类型,
  	...
  )
  row format delimited fields terminated by '\t'
  stored as parquet;  -- Parquet 存储格式
  

Orc 存储格式

• 每个 Orc 文件由 1 个或多个 stripe 组成，每个 stripe 一般为 HDFS的块大小，每一个 stripe 包含多条记录，这些记录按照列进行独立存储

  --创建 Orc 存储格式
  create table log_orc(
  	字段名 字段类型,
  	...
  )
  row format delimited fields terminated by '\t'
  stored as orc  --Orc 存储格式
  tblproperties("orc.compress"="NONE"); -- 设置 orc 存储不使用压缩
  

存储和压缩结合

--创建一个 SNAPPY 压缩的 ORC 存储方式
create table log_orc_snappy(
    字段名 字段类型,
	...
)
row format delimited fields terminated by '\t'
stored as orc  --Orc 存储格式
tblproperties("orc.compress"="SNAPPY");  --SNAPPY 压缩



--创建一个 SNAPPY 压缩的 parquet 存储方式
create table log_parquet_snappy(
    字段名 字段类型,
	...
)
row format delimited fields terminated by '\t'
stored as parquet  --parquet 存储格式
tblproperties("parquet.compression"="SNAPPY");  --SNAPPY 压缩

9、常用参数设置

参数都是默认值

---------------------------- Fetch 抓取
--Fetch 抓取，默认是more，none：查询语句都走reduce
set hive.fetch.task.conversion=more


---------------------------- 本地模式
--开启本地 MapReduce
set hive.exec.mode.local.auto=true; 
--设置 本地MapReduce 的最大输入数据量，当输入数据量小于这个值时采用 本地MapReduce 的方式，默认为 134217728，即 128M
set hive.exec.mode.local.auto.inputbytes.max=134217728;
--设置 本地MapReduce 的最大输入文件个数，当输入文件个数小于这个值时采用 本地MapReduce 的方式，默认为 4
set hive.exec.mode.local.auto.input.files.max=4;


---------------------------- 小表大表 Join（MapJOIN）
--设置自动选择 Mapjoin
set hive.auto.convert.join=true;
--大表小表的阈值设置（默认 25M 以下认为是小表）
set hive.mapjoin.smalltable.filesize=25000000;


---------------------------- 小文件进行合并
--对小文件进行合并的功能
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;
--在 map-only 任务结束时合并小文件
set hive.merge.mapfiles=true;
--在 map-reduce 任务结束时合并小文件
set hive.merge.mapredfiles=true;
--合并文件的大小，默认 256M
set hive.merge.size.per.task=268435456;
--当输出文件的平均大小小于该值时，启动一个独立的 map-reduce 任务进行文件 merge
set hive.merge.smallfiles.avgsize = 16777216;


---------------------------- 合理设置 Reduce 数
--调整 reduce 个数
set mapreduce.job.reduces=-1;