HIVE的架构和使用

1.数据仓库

数据仓库，英文名称为Data Warehouse，可简写为DW或DWH。数据仓库的目的是构建面向分析的集成化数据环境，为企业提供决策支持（DecisionSupport）。它出于分析性报告和决策支持目的而创建。

数据仓库本身并不“生产”任何数据，同时自身也不需要“消费”任何的数据，数据来源于外部，并且开放给外部应用，这也是为什么叫“仓库”，而不叫“工厂”的原因。

• 数仓的主要特征

  • 面向主题
  • 集成性
  • 非易失性
  • 时变性

• 数据仓库和数据库的区别

  数据库与数据仓库的区别实际讲的是 OLTP 与 OLAP 的区别：

  • 操作型处理，叫联机事务处理 OLTP（On-Line Transaction Processing，），也可以称面向交易的处理系统，它是针对具体业务在数据库联机的日常操作，通常对少数记录进行查询、修改。用户较为关心操作的响应时间、数据的安全性、完整性和并发支持的用户数等问题。传统的数据库系统作为数据管理的主要手段，主要用于操作型处理。
  • 分析型处理，叫联机分析处理 OLAP（On-Line Analytical Processing）一般针对某些主题的历史数据进行分析，支持管理决策。

  即：一个是实时交互操作，一个是离线数据分析

数据库是面向事务的设计，数据仓库是面向主题设计的。

数据库一般存储业务数据，数据仓库存储的一般是历史数据。

数据库设计是尽量避免冗余，一般针对某一业务应用进行设计。数据仓库在设计是有意引入冗余，依照分析需求，分析维度、分析指标进行设计。

数据库是为捕获数据而设计，数据仓库是为分析数据而设计。

源数据层（ODS）：此层数据无任何更改，直接沿用外围系统数据结构和数据，不对外开放；为临时存储层，是接口数据的临时存储区域，为后一步的数据处理做准备。

数据仓库层（DW）：也称为细节层，DW层的数据应该是一致的、准确的、干净的数据，即对源系统数据进行了清洗（去除了杂质）后的数据。

数据应用层（DA或APP）：前端应用直接读取的数据源；根据报表、专题分析需求而计算生成的数据。

• 为什么要对数据仓库分层

用空间换时间，通过大量的预处理来提升应用系统的用户体验（效率），因此数据仓库会存在大量冗余的数据；不分层的话，如果源业务系统的业务规则发生变化将会影响整个数据清洗过程，工作量巨大。

通过数据分层管理可以简化数据清洗的过程，因为把原来一步的工作分到了多个步骤去完成，相当于把一个复杂的工作拆成了多个简单的工作，把一个大的黑盒变成了一个白盒，每一层的处理逻辑都相对简单和容易理解，这样我们比较容易保证每一个步骤的正确性，当数据发生错误的时候，往往我们只需要局部调整某个步骤即可。

• 元数据

元数据（Meta Date），主要记录数据仓库中模型的定义、各层级间的映射关系、监控数据仓库的数据状态及ETL的任务运行状态。即，描述数据的数据

2. hive的基本概念

2.1 hive简介

Hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张数据库表，并提供类SQL查询功能。其本质是将SQL转换为MapReduce的任务进行运算，底层由HDFS来提供数据的存储，说白了hive可以理解为一个将SQL转换为MapReduce的任务的工具，甚至更进一步可以说hive就是一个MapReduce的客户端。

• 作用
  • 降低hadoop人员学习成本
  • 缩短项目周期
  • mapreduce实现逻辑太复杂，类sql语法降低了开发难度和学习成本
• 特点
  • 可扩展性：Hive可以自由的扩展集群的规模，一般情况下不需要重启服务。
  • 延展性：Hive支持用户自定义函数，用户可以根据自己的需求来实现自己的函数。
  • 容错性：良好的容错性，节点出现问题SQL仍可完成执行。

• 基本组成

  • 用户接口
    • 包括CLI、JDBC/ODBC、WebGUI。其中，CLI(command line interface)为shell命令行；JDBC/ODBC是Hive的JAVA实现，与传统数据库JDBC类似；WebGUI是通过浏览器访问Hive。
  • 元数据存储
    • 通常是存储在关系数据库如mysql/derby中。Hive 将元数据存储在数据库中。Hive 中的元数据包括表的名字，表的列和分区及其属性，表的属性（是否为外部表等），表的数据所在目录等。
  • 解释器、编译器、优化器、执行器
    • 完成HQL 查询语句从词法分析、语法分析、编译、优化以及查询计划的生成。生成的查询计划存储在HDFS 中，并在随后有MapReduce 调用执行。

• 与hadoop的关系

  Hive利用HDFS存储数据，利用MapReduce查询分析数据

2.2 hive的安装

由于hive的本质就是用sql的形式开发mapreduce程序，所以在一台机器上安装就好。

• derby版

  直接解压启动就好，元数组保存在derby数据库，而derby数据库是一个内存数据库。缺点：多个地方安装hive后，每一个hive是拥有一套自己的元数据，大家的库、表就不统一。元数据又特别重要，所了以这种方法我们一般不

• mysql共享hive元数据

1）安装MySQL

yum  install  mysql mysql-server  mysql-devel
/etc/init.d/mysqld start
/usr/bin/mysql_secure_installation
//登陆MySQL
 grant all privileges on *.* to 'root'@'%' identified by '111111' with grant option;
 flush privileges;

2）安装hive

• 下载
• 解压
• 修改配置文件

cd  /export/servers/hive-1.1.0-cdh5.14.0/conf
cp hive-env.sh.template hive-env.sh
vim hive-env.sh

HADOOP_HOME=/export/servers/hadoop-2.6.0-cdh5.14.0
# Hive Configuration Directory can be controlled by:
export HIVE_CONF_DIR=/export/servers/hive-1.1.0-cdh5.14.0/conf

cd /export/servers/hive-1.1.0-cdh5.14.0/conf
vim hive-site.xm

<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
        <property>
                <name>javax.jdo.option.ConnectionURL</name>
                <value>jdbc:mysql://188.131.192.45:3306/hive?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>123456</value>
        </property>
        <property>
                <name>hive.cli.print.current.db</name>
                <value>true</value>
        </property>
        <property>
                <name>hive.cli.print.header</name>
                <value>true</value>
        </property>
        <property>
                <name>hive.server2.thrift.bind.host</name>
                <value>node01.hadoop.com</value>
        </property>
<!--
        <property>
                <name>hive.metastore.uris</name>
                <value>thrift://node03.hadoop.com:9083</value>
        </property>
-->
</configuration>

3）上传mysql的驱动包到hive的lib目录

3. hive基本操作

hive仓库设计的雪花型和星型。

3.1 交互方式

3.1.1 方式一

cd /export/servers/hive-1.1.0-cdh5.14.0
bin/hive

3.1.2 方式二

启动服务端

cd  /export/servers/hive-1.1.0-cdh5.14.0
nohup bin/hive --service hiveserver2  &

启动客户端

bin/beeline
beeline> !connect jdbc:hive2://node01.hadoop.com:10000

3.1.3 方式三

使用 –e 参数来直接执行hql的语句

bin/hive -e "use myhive;select * from test;"

使用 –f 参数通过指定文本文件来执行hql的语句

vim hive.sql
use myhive;select * from test;

bin/hive -f hive.sql

3.2 操作数据库

3.2.1 创建数据库

方法一：默认存放位置

create database if not exists myhive;
use  myhive;

这种方式hive的表存放位置模式是由hive-site.xml当中的一个属性指定的

<name>hive.metastore.warehouse.dir</name>
<value>/user/hive/warehouse</value>

方法二：指定hdfd存储位置

create database myhive2 location '/myhive2';

3.2.2 修改数据库

可以使用alter database 命令来修改数据库的一些属性。但是数据库的元数据信息是不可更改的，包括数据库的名称以及数据库所在的位置。

alter database  myhive2  set dbproperties('createtime'='20180611');

3.2.3 查看库详细信息

desc  database  myhive2;
desc database extended  myhive2;

3.2.4 删除数据库

删除空数据库

drop  database  myhive2;

强制删除有数据的数据库

drop  database  myhive  cascade; 

3.3 操作数据表

3.3.1创建数据表

（1）内部表

create  table if not exists stu2(id int ,name string) 
row format delimited fields terminated by '/t' 
stored as textfile 
location '/user/stu2';

CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常，用户可以用 IF NOT EXISTS 选项来忽略这个异常。

hive的默认分割符：‘/001’

根据查询结果创建表（查询结果也会放到表中）

create table stu3 as select * from stu2;

根据已经存在的表结构创建表

create table stu4 like stu2;

常见字段类型

分类	类型	描述	字面量示例
原始类型	BOOLEAN	true/false	TRUE
	TINYINT	1字节的有符号整数 -128~127	1Y
	SMALLINT	2个字节的有符号整数，-32768~32767	1S
	INT	4个字节的带符号整数	1
	BIGINT	8字节带符号整数	1L
	FLOAT	4字节单精度浮点数1.0
	DOUBLE	8字节双精度浮点数	1.0
	DEICIMAL	任意精度的带符号小数	1.0
	STRING	字符串，变长	“a”,’b’
	VARCHAR	变长字符串	“a”,’b’
	CHAR	固定长度字符串	“a”,’b’
	BINARY	字节数组	无法表示
	TIMESTAMP	时间戳，毫秒值精度	122327493795
	DATE	日期	‘2016-03-29’
	INTERVAL	时间频率间隔
复杂类型	ARRAY	有序的的同类型的集合	array(1,2)
	MAP	key-value,key必须为原始类型，value可以任意类型	map(‘a’,1,’b’,2)
	STRUCT	字段集合,类型可以不同	struct(‘1’,1,1.0), named_stract(‘col1’,’1’,’col2’,1,’clo3’,1.0)
	UNION	在有限取值范围内的一个值	create_union(1,’a’,63)

（2）外部表

外部表因为是指定其他的hdfs路径的数据加载到表当中来，所以hive表会认为自己不完全独占这份数据，所以删除hive表的时候，数据仍然存放在hdfs当中，不会删掉。

create external table teacher (t_id string,t_name string) 
row format delimited fields terminated by '/t';

（3）分区表

在大数据中，最常用的一种思想就是分治，我们可以把大的文件切割划分成一个个的小的文件，这样每次操作一个小的文件就会很容易了，同样的道理，在hive当中也是支持这种思想的，就是我们可以把大的数据，按照每天，或者每小时进行切分成一个个的小的文件，这样去操作小的文件就会容易得多了。

创建分区表：

create table score(s_id string,c_id string, s_score int) 
partitioned by (month string) 
row format delimited fields terminated by '/t';

创建多分区表：

create table score2 (s_id string,c_id string, s_score int) 
partitioned by (year string,month string,day string) 
row format delimited fields terminated by '/t';

查看分区：

show  partitions  score;

添加分区：

alter table score add partition(month='201805');

删除分区：

alter table score drop partition(month = '201806');

多分区联合查询使用union all来实现

select * from score where month = '201806'
union all 
select * from score where month = '201806';

（4）分桶表

将数据按照指定的字段进行分成多个桶中去，说白了就是将数据按照字段进行划分，可以将数据按照字段划分到多个文件当中去

• 开启hive的分桶功能

set hive.enforce.bucketing=true;

• 设置reduce的个数

set mapreduce.job.reduces=3;

• 创建桶表

create table course (c_id string,c_name string,t_id string) clustered by(c_id) 
into 3 buckets 
row format delimited fields terminated by '/t';

桶表的数据加载，由于通标的数据加载通过hdfs dfs -put文件或者通过load data均不好使，只能通过insert overwrite

创建普通表，并通过insert overwrite的方式将普通表的数据通过查询的方式加载到桶表当中去

3.3.2 查询表结构

desc formatted  stu2;

3.3.3 修改表

• 表重新命名

alter  table  old_table_name  rename  to  new_table_name;

• 增加修改列信息

(1)查询表结构
desc score5;
(2)添加列
alter table 表名 add columns (mycol string, mysco string);
(3)更新列
alter table score5 change column mysco mysconew int;

3.3.4 删除表

drop table 表名;

清空表只能清空内部表

truncate table 表名;

3.3.5 向表中加载数据

（1）通过load方式加载数据

load data local inpath '/export/servers/hivedatas/score.csv' 
overwrite into table score partition(month='201806');
-- 注意这里的overwrite表示重新添加，如果没有，则表示追加

（2）通过查询方式加载数据

insert overwrite  table score4 partition(month = '201806')    -- 这里的overwrite必须有
select s_id,c_id,s_score from score;

（3）多插入模式

from score 
insert overwrite table score_first partition(month='201806') select s_id,c_id 
insert overwrite table score_second partition(month = '201806') select c_id,s_score;	

（4）直接将数据放到表对应的路径下

对于分区表我们需要修复表和数据文件件的关系映射

msck repair table score4;

或者手动添加分区

hdfs dfs -mkdir -p /scoredatas/month=201805
hdfs dfs -put score.csv /scoredatas/month=201805

alter table score4 add partition(month='201805');

（5）import导入hive表（内部表）

import table teacher2 from '/export/teacher';

3.3.6 导出数据

（1）insert导出

• 将查询结果导出到本地

  insert overwrite local directory '/export/servers/exporthive' 
  select * from score;
  

• 查询的结果格式化导出到本地

  insert overwrite local directory '/export/servers/exporthive' 
  row format delimited fields terminated by '/t' 
  collection items terminated by '#' 
  select * from student;
  

• 查询的结果导出到HDFS上(没有local)

  insert overwrite directory '/export/servers/exporthive'
  row format delimited fields terminated by '/t' 
  collection items terminated by  '#'     
  select * from score;
   -- 对于集合类型我们使用#来进行分割，因为这个表里面没有集合类型，所以加不加这个结果都一样
  

（2）hadoop命令导出到本地

hdfs dfs -get /export/servers/exporthive/000000_0 /export/servers/exporthive/local.txt;

（3）hive的shell命令导出到本地

bin/hive -e "select * from myhive.score;" > /export/servers/exporthive/score.txt

（4）export导出到HDFS

export table score to '/export/exporthive/score';

4. hive的查询语句

基本查询语句和MySQL是基本相同的。大多数情况下，Hive会对每对JOIN连接对象启动一个MapReduce任务。

4.1 分组

group by字段

注意一个问题：select后的字段必须是group by 后的字段（但是可以是聚合函数）

4.2 join语句

hive的join语句只支持等值连接，不支持非等值连接

相比MySQL，hive多了个满连接

**满外连接：**将会返回所有表中符合WHERE语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用NULL值替代。

SELECT * FROM teacher t FULL JOIN course c ON t.t_id = c.t_id ;

4.3 排序

（1）order by

​ 全局排序，只能有一个reduce，可能造成单个reduce处理压力过大。

（2）sort by

​ 每个MapReduce内部进行排序，对全局结果集来说不是排序。

1）设置reduce个数

set mapreduce.job.reduces=3;

2）查看设置reduce个数

set mapreduce.job.reduces;

3）查询成绩按照成绩升序排列

select* from score sort by s_score;

（3）distribute by

​ 类似MR中partition，进行分区，结合sort by使用。

​ **注意：**Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。

（4）cluster by

当distribute by和sort by字段相同时，可以使用clusterby方式。

cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是倒序排序，不能指定排序规则为ASC或者DESC。

（5）补充内容

• 分组求topN

5.hive shell参数设定

常用shell命令

./hive -e "hive命令"       从命令行执行命令
./hive -f hive.sql        从脚本文件执行hive命令

1、	-i 从文件初始化HQL。
2、	-e从命令行执行指定的HQL 
3、	-f 执行HQL脚本 
4、	-v 输出执行的HQL语句到控制台 
5、	-p <port> connect to Hive Server on port number 
6、	-hiveconf x=y Use this to set hive/hadoop configuration variables.  

hive参数配置方式三种：

• 配置文件 hive-site.xml
• 命令行参数 启动hive客户端的时候可以设置参数
• 参数命令 客户端通过set 方式设定

（1）配置文件

用户自定义配置文件：$HIVE_CONF_DIR/hive-site.xml

默认配置文件：$HIVE_CONF_DIR/hive-default.xml

用户自定义配置会覆盖默认配置。另外，Hive也会读入Hadoop的配置，因为Hive是作为Hadoop的客户端启动的，Hive的配置会覆盖Hadoop的配置。配置文件的设定对本机启动的所有Hive进程都有效。

（2）命令行参数

启动Hive（客户端或Server方式）时，可以在命令行添加-hiveconf param=value来设定参数，这一设定对本次启动的Session（对于Server方式启动，则是所有请求的Sessions）有效。例如：

bin/hive -hiveconf hive.root.logger=INFO,console

（3）set参数设定

可以在HQL中使用SET关键字设定参数，这一设定的作用域也是session级的。例如：

set mapred.reduce.tasks=100;

注意某些系统级的参数，例如log4j相关的设定，必须用前两种方式设定，因为那些参数的读取在Session建立以前已经完成了。

6. hive函数

6.1 查看hive函数的基本命令

查看系统自带函数

hive> show functions;

显示自带函数

hive> desc function 函数名;

详细显示自带函数

hive> desc function extended 函数名;

6.2 自定义函数

（1）三种自定义函数

• UDF（User-Defined-Function）： 一进一出
• UDAF（User-Defined Aggregation Function）：聚集函数，多进一出，类似于：count/max/min
• UDTF（User-Defined Table-Generating Functions）: 一进多出， 如lateral view explore()

（2）UDF开发示例

• 编程步骤：

​ 1）继承org.apache.hadoop.hive.ql.UDF

​ 2）需要实现evaluate函数；evaluate函数支持重载；

• 注意事项
  • UDF必须要有返回类型，可以返回null，但是返回类型不能为void；
  • UDF中常用Text/LongWritable等类型，不推荐使用java类型；
• 创建maven工程

<repositories>
    <repository>
        <id>cloudera</id>
 <url>https://repository.cloudera.com/artifactory/cloudera-repos/</url>
    </repository>
</repositories>
<dependencies>
    <dependency>
        <groupId>org.apache.hadoop</groupId>
        <artifactId>hadoop-common</artifactId>
        <version>2.6.0-cdh5.14.0</version>
    </dependency>
    <dependency>
        <groupId>org.apache.hive</groupId>
        <artifactId>hive-exec</artifactId>
        <version>1.1.0-cdh5.14.0</version>
    </dependency>
</dependencies>
<build>
<plugins>
    <plugin>
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-compiler-plugin</artifactId>
        <version>3.0</version>
        <configuration>
            <source>1.8</source>
            <target>1.8</target>
            <encoding>UTF-8</encoding>
        </configuration>
    </plugin>
     <plugin>
         <groupId>org.apache.maven.plugins</groupId>
         <artifactId>maven-shade-plugin</artifactId>
         <version>2.2</version>
         <executions>
             <execution>
                 <phase>package</phase>
                 <goals>
                     <goal>shade</goal>
                 </goals>
                 <configuration>
                     <filters>
                         <filter>
                             <artifact>*:*</artifact>
                             <excludes>
                                 <exclude>META-INF/*.SF</exclude>
                                 <exclude>META-INF/*.DSA</exclude>
                                 <exclude>META-INF/*/RSA</exclude>
                             </excludes>
                         </filter>
                     </filters>
                 </configuration>
             </execution>
         </executions>
     </plugin>
</plugins>
</build>

• 继承UDF

public class ItcastUDF extends UDF {
    public Text evaluate(final Text s) {
        if (null == s) {
            return null;
        }
        //返回大写字母
        return new Text(s.toString().toUpperCase());
    }
}

• 将项目打包，上传到hive的lib目录
• hive客户端添加jar包

add jar /export/servers/hive-1.1.0-cdh5.14.0/lib/自定义jar包名字.jar;

• 客户端内创建函数

  • 临时函数

    create temporary function tolowercase as 'cn.itcast.udf.ItcastUDF';
    

  • 永久函数

    create function tolowercase as 'cn.itcast.udf.ItcastUDF';
    

7.数据的压缩

7.1 MR支持的压缩编码

压缩格式	工具	算法	文件扩展名	是否可切分
DEFAULT	无	DEFAULT	.deflate	否
Gzip	gzip	DEFAULT	.gz	否
bzip2	bzip2	bzip2	.bz2	是
LZO	lzop	LZO	.lzo	否
LZ4	无	LZ4	.lz4	否
Snappy	无	Snappy	.snappy	否

Hadoop引入编码/解码器

压缩格式	对应的编码/解码器
DEFLATE	org.apache.hadoop.io.compress.DefaultCodec
gzip	org.apache.hadoop.io.compress.GzipCodec
bzip2	org.apache.hadoop.io.compress.BZip2Codec
LZO	com.hadoop.compression.lzo.LzopCodec
LZ4	org.apache.hadoop.io.compress.Lz4Codec
Snappy	org.apache.hadoop.io.compress.SnappyCodec

7.2 设置压缩方式

（1）在hadoop中启动压缩，配置core-site.xml、mapred-site.xml

（2）通过set 方式在hive客户端设定（这是常用方式）

• map端压缩

1）开启hive中间传输数据压缩功能
hive (default)>set hive.exec.compress.intermediate=true;
2）开启mapreduce中map输出压缩功能
hive (default)>set mapreduce.map.output.compress=true;
3）设置mapreduce中map输出数据的压缩方式
hive (default)>set mapreduce.map.output.compress.codec= org.apache.hadoop.io.compress.SnappyCodec;

• reduce端压缩

1）开启hive最终输出数据压缩功能
hive (default)>set hive.exec.compress.output=true;
2）开启mapreduce最终输出数据压缩
hive (default)>set mapreduce.output.fileoutputformat.compress=true;
3）设置mapreduce最终数据输出压缩方式
hive (default)> set mapreduce.output.fileoutputformat.compress.codec = org.apache.hadoop.io.compress.SnappyCodec;
4）设置mapreduce最终数据输出压缩为块压缩
hive (default)>set mapreduce.output.fileoutputformat.compress.type=BLOCK;

8. hive的存储格式

8.1 存储格式

hive支持的存储方式主要是：

• 行式存在

  • textfile
  • sequencefile

• 列式存储

  • orc
  • parquet

行存储的特点： 查询满足条件的一整行数据的时候，列存储则需要去每个聚集的字段找到对应的每个列的值，行存储只需要找到其中一个值，其余的值都在相邻地方，所以此时行存储查询的速度更快。

列存储的特点： 因为每个字段的数据聚集存储，在查询只需要少数几个字段的时候，能大大减少读取的数据量；每个字段的数据类型一定是相同的，列式存储可以针对性的设计更好的设计压缩算法。

8.2 存储和压缩结合

创建一个SNAPPY压缩的ORC存储方式

create table log_orc_snappy(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '/t'
STORED AS orc tblproperties ("orc.compress"="SNAPPY");

在实际的项目开发当中，hive表的数据存储格式一般选择：orc或parquet。压缩方式一般选择snappy。

9.调优

9.1 Fetch抓取

Hive中对某些情况的查询可以不必使用MapReduce计算。

在hive-default.xml.template文件中hive.fetch.task.conversion默认是more，老版本hive默认是minimal，该属性修改为more以后，在全局查找、字段查找、limit查找等都不走mapreduce。

<property>
    <name>hive.fetch.task.conversion</name>
    <value>more</value>
</property>

通过set方式设定

hive (default)> set hive.fetch.task.conversion=minimal;
//执行查询语句，都会执行mapreduce程序。

hive (default)> set hive.fetch.task.conversion=more;
//在全局查找、字段查找、limit查找等都不走mapreduce

9.2 开启本地模式

大多数的Hadoop Job是需要Hadoop提供的完整的可扩展性来处理大数据集的。不过，有时Hive的输入数据量是非常小的。在这种情况下，为查询触发执行任务时消耗可能会比实际job的执行时间要多的多。对于大多数这种情况，Hive可以通过本地模式在单台机器上处理所有的任务。对于小数据集，执行时间可以明显被缩短。

用户可以通过设置hive.exec.mode.local.auto的值为true，来让Hive在适当的时候自动启动这个优化。

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

9.3 表的优化

9.3.1 join连接

（1）小表join大表

大表JOIN小表和小表JOIN大表的效率 （新的版本当中已经没有区别了，旧的版本当中需要使用小表）

将key相对分散，并且数据量小的表放在join的左边，这样可以有效减少内存溢出错误发生的几率；再进一步，可以使用Group让小的维度表（1000条以下的记录条数）先进内存。在map端完成reduce。

select  count(distinct s_id)  from score;
select count(s_id) from score group by s_id; -- 在map端进行聚合，效率更高

（2）多表关联时，最好拆分成小段，避免大sql（无法控制中间Job）

（3）大表join大表

（1）空KEY过滤

有时join超时是因为某些key对应的数据太多，而相同key对应的数据都会发送到相同的reducer上，从而导致内存不够。此时我们应该仔细分析这些异常的key，很多情况下，这些key对应的数据是异常数据，我们需要在SQL语句中进行过滤。例如key对应的字段为空，操作如下：

环境准备

create table ori(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string) row format delimited fields terminated by '/t';

create table nullidtable(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string) row format delimited fields terminated by '/t';

create table jointable(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string) row format delimited fields terminated by '/t';

load data local inpath '/export/servers/hivedatas/hive_big_table/*' into table ori; 
load data local inpath '/export/servers/hivedatas/hive_have_null_id/*' into table nullidtable;

不过滤：

INSERT OVERWRITE TABLE jointable
SELECT a.* FROM nullidtable a JOIN ori b ON a.id = b.id;
结果：
No rows affected (152.135 seconds)

过滤：

INSERT OVERWRITE TABLE jointable
SELECT a.* FROM (SELECT * FROM nullidtable WHERE id IS NOT NULL ) a JOIN ori b ON a.id = b.id;
结果：
No rows affected (141.585 seconds)

（2）空key转换

有时虽然某个key为空对应的数据很多，但是相应的数据不是异常数据，必须要包含在join的结果中，此时我们可以表a中key为空的字段赋一个随机的值，使得数据随机均匀地分不到不同的reducer上。例如：

不随机分布：

set hive.exec.reducers.bytes.per.reducer=32123456;
set mapreduce.job.reduces=7;
INSERT OVERWRITE TABLE jointable
SELECT a.*
FROM nullidtable a
LEFT JOIN ori b ON CASE WHEN a.id IS NULL THEN 'hive' ELSE a.id END = b.id;
No rows affected (41.668 seconds)   52.477

结果：这样的后果就是所有为null值的id全部都变成了相同的字符串，及其容易造成数据的倾斜（所有的key相同，相同key的数据会到同一个reduce当中去）

为了解决这种情况，我们可以通过hive的rand函数，随记的给每一个为空的id赋上一个随机值，这样就不会造成数据倾斜

set hive.exec.reducers.bytes.per.reducer=32123456;
set mapreduce.job.reduces=7;
INSERT OVERWRITE TABLE jointable
SELECT a.*
FROM nullidtable a
LEFT JOIN ori b ON CASE WHEN a.id IS NULL THEN concat('hive', rand()) ELSE a.id END = b.id;
No rows affected (42.594 seconds)

9.3.2 mapjoin

如果不指定MapJoin或者不符合MapJoin的条件，那么Hive解析器会将Join操作转换成Common Join，即：在Reduce阶段完成join。容易发生数据倾斜。可以用MapJoin把小表全部加载到内存在map端进行join，避免reducer处理。

（1）开启MapJoin参数设置：

• 设置自动选择Mapjoin

set hive.auto.convert.join = true; 默认为true

• 大表小表的阈值设置（默认25M以下认为是小表）：

set hive.mapjoin.smalltable.filesize=25123456;

首先是Task A，它是一个LocalTask（在客户端本地执行的Task），负责扫描小表b的数据，将其转换成一个HashTable的数据结构，并写入本地的文件中，之后将该文件加载到DistributeCache中。

接下来是Task B，该任务是一个没有Reduce的MR，启动MapTasks扫描大表a,在Map阶段，根据a的每一条记录去和DistributeCache中b表对应的HashTable关联，并直接输出结果。

由于MapJoin没有Reduce，所以由Map直接输出结果文件，有多少个MapTask，就有多少个结果文件。

9.2.3 group by

默认情况下，Map阶段同一Key数据分发给一个reduce，当一个key数据过大时就倾斜了。

并不是所有的聚合操作都需要在Reduce端完成，很多聚合操作都可以先在Map端进行部分聚合，最后在Reduce端得出最终结果。

（1）是否在Map端进行聚合，默认为True

set hive.map.aggr = true;

（2）在Map端进行聚合操作的条目数目

set hive.groupby.mapaggr.checkinterval= 100000;

（3）有数据倾斜的时候进行负载均衡（默认是false）

set hive.groupby.skewindata = true;

当选项设定为 true，生成的查询计划会有两个MR Job。第一个MR Job中，Map的输出结果会随机分布到Reduce中，每个Reduce做部分聚合操作，并输出结果，这样处理的结果是相同的Group By Key有可能被分发到不同的Reduce中，从而达到负载均衡的目的；第二个MR Job再根据预处理的数据结果按照Group By Key分布到Reduce中（这个过程可以保证相同的Group By Key被分布到同一个Reduce中），最后完成最终的聚合操作。

9.2.4 Count(distinct)

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

环境准备：

create table bigtable(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string) row format delimited fields terminated by '/t';

load data local inpath '/home/admin/softwares/data/100万条大表数据（id除以10取整）/bigtable' into table bigtable;

转换前：

SELECT count(DISTINCT id) FROM bigtable;

转换后：

SELECT count(id) FROM (SELECT id FROM bigtable GROUP BY id) a;

9.2.5 笛卡尔积

尽量避免笛卡尔积，即避免join的时候不加on条件，或者无效的on条件，Hive只能使用1个reducer来完成笛卡尔积。

9.2.6 使用分区剪裁、列剪裁

在SELECT中，只拿需要的列，如果有，尽量使用分区过滤，少用SELECT *。

在分区剪裁中，当使用外关联时，如果将副表的过滤条件写在Where后面，那么就会先全表关联，之后再过滤。

先关联再where

SELECT a.id
FROM bigtable a
LEFT JOIN ori b ON a.id = b.id
WHERE b.id <= 10;

正确的写法是写在ON后面：先Where再关联

SELECT a.id
FROM ori a
LEFT JOIN bigtable b ON (a.id <= 10 AND a.id = b.id);

或者直接写成子查询：

SELECT a.id
FROM bigtable a
RIGHT JOIN (SELECT id
FROM ori
WHERE id <= 10
) b ON a.id = b.id;

9.2.7 动态分区调整

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

说白了就是以第一个表的分区规则，来对应第二个表的分区规则，将第一个表的所有分区，全部拷贝到第二个表中来，第二个表在加载数据的时候，不需要指定分区了，直接用第一个表的分区即可

（1）开启动态分区功能（默认true，开启）

set hive.exec.dynamic.partition=true;

（2）设置为非严格模式（动态分区的模式，默认strict，表示必须指定至少一个分区为静态分区，nonstrict模式表示允许所有的分区字段都可以使用动态分区。）

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

（3）在所有执行MR的节点上，最大一共可以创建多少个动态分区。

set  hive.exec.max.dynamic.partitions=1000;

（4）在每个执行MR的节点上，最大可以创建多少个动态分区。该参数需要根据实际的数据来设定。比如：源数据中包含了一年的数据，即day字段有365个值，那么该参数就需要设置成大于365，如果使用默认值100，则会报错。

set hive.exec.max.dynamic.partitions.pernode=100

（5）整个MR Job中，最大可以创建多少个HDFS文件。

在linux系统当中，每个linux用户最多可以开启1024个进程，每一个进程最多可以打开2048个文件，即持有2048个文件句柄，下面这个值越大，就可以打开文件句柄越大

set hive.exec.max.created.files=100000;

（6）当有空分区生成时，是否抛出异常。一般不需要设置。

set hive.error.on.empty.partition=false;

9.2.8 分桶表

对于每一个表（table）或者分区， Hive可以进一步组织成桶，也就是说桶是更为细粒度的数据范围划分。Hive也是 针对某一列进行桶的组织。Hive采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。

把表（或者分区）组织成桶（Bucket）有两个理由：

• 获得更高的查询处理效率。桶为表加上了额外的结构，Hive 在处理有些查询时能利用这个结构。具体而言，连接两个在（包含连接列的）相同列上划分了桶的表，可以使用 Map 端连接 （Map-side join）高效的实现。比如JOIN操作。对于JOIN操作两个表有一个相同的列，如果对这两个表都进行了桶操作。那么将保存相同列值的桶进行JOIN操作就可以，可以大大较少JOIN的数据量。
• 使取样（sampling）更高效。在处理大规模数据集时，在开发和修改查询的阶段，如果能在数据集的一小部分数据上试运行查询，会带来很多方便。

（1）创建带桶的table

注意的问题是使用桶表的时候我们要开启桶表：

set hive.enforce.bucketing = true;

create table bucketed_user(id int,name string) /
clustered by (id) sorted by(name) into 4 buckets /
row format delimited fields terminated by '/t' /
stored as textfile; 

在这里，我们使用用户ID来确定如何划分桶(Hive使用对值进行哈希并将结果除 以桶的个数取余数。这样，任何一桶里都会有一个随机的用户集合（PS：其实也能说是随机，不是吗？）

对于map端连接的情况，两个表以相同方式划分桶。处理左边表内某个桶的 mapper知道右边表内相匹配的行在对应的桶内。因此，mapper只需要获取那个桶 (这只是右边表内存储数据的一小部分)即可进行连接。这一优化方法并不一定要求两个表必须桶的个数相同，两个表的桶个数是倍数关系也可以。

桶中的数据可以根据一个或多个列另外进行排序。由于这样对每个桶的连接变成了高效的归并排序(merge-sort), 因此可以进一步提升map端连接的效率。以下语法声明一个表使其使用排序桶：

我们如何保证表中的数据都划分成桶了呢？把在Hive外生成的数据加载到划分成 桶的表中，当然是可以的。其实让Hive来划分桶更容易。这一操作通常针对已有的表。

Hive并不检查数据文件中的桶是否和表定义中的桶一致(无论是对于桶的数量或用于划分桶的列）。如果两者不匹配，在査询时可能会碰到错 误或未定义的结果。因此，建议让Hive来进行划分桶的操作。

（2）向分桶表中插入数据

INSERT OVERWRITE TABLE bucketed_users SELECT * FROM users; 

物理上，每个桶就是表(或分区）目录里的一个文件。它的文件名并不重要，但是桶 n 是按照字典序排列的第 n 个文件。事实上，桶对应于 MapReduce 的输出文件分区：一个作业产生的桶(输出文件)和reduce任务个数相同。

（3）对桶中的数据进行采样

hive> SELECT * FROM bucketed_users 
> TABLESAMPLE(BUCKET 1 OUT OF 4 ON id); 
0 Nat 
4 Ann 

桶的个数从1开始计数。因此，前面的查询从4个桶的第一个中获取所有的用户。 对于一个大规模的、均匀分布的数据集，这会返回表中约四分之一的数据行。我们也可以用其他比例对若干个桶进行取样(因为取样并不是一个精确的操作，因此这个 比例不一定要是桶数的整数倍)。

注：tablesample是抽样语句，语法：TABLESAMPLE(BUCKET x OUT OF y)

y必须是table总bucket数的倍数或者因子。hive根据y的大小，决定抽样的比例。例如，table总共分了64份，当y=32时，抽取(64/32)2个bucket的数据，当y=128时，抽取(64/128)1/2个bucket的数据。x表示从哪个bucket开始抽取。例如，table总bucket数为32，tablesample(bucket 3 out of 16)，表示总共抽取（32/16）2个bucket的数据，分别为第3个bucket和第（3+16）19个bucket的数据。

9.4 数据倾斜

9.4.1 map数

1）通常情况下，作业会通过input的目录产生一个或者多个map任务。

主要的决定因素有：input的文件总个数，input的文件大小，集群设置的文件块大小(目前为128M，可在hive中通过set dfs.block.size;命令查看到，该参数不能自定义修改)；

2）举例：

a) 假设input目录下有1个文件a，大小为780M，那么hadoop会将该文件a分隔成7个块（6个128m的块和1个12m的块），从而产生7个map数。

b) 假设input目录下有3个文件a，b，c大小分别为10m，20m，150m，那么hadoop会分隔成4个块（10m，20m，128m，22m），从而产生4个map数。即，如果文件大于块大小(128m)，那么会拆分，如果小于块大小，则把该文件当成一个块。

3）是不是map数越多越好？

答案是否定的。如果一个任务有很多小文件（远远小于块大小128m），则每个小文件也会被当做一个块，用一个map任务来完成，而一个map任务启动和初始化的时间远远大于逻辑处理的时间，就会造成很大的资源浪费。而且，同时可执行的map数是受限的。

4）是不是保证每个map处理接近128m的文件块，就高枕无忧了？

答案也是不一定。比如有一个127m的文件，正常会用一个map去完成，但这个文件只有一个或者两个小字段，却有几千万的记录，如果map处理的逻辑比较复杂，用一个map任务去做，肯定也比较耗时。

针对上面的问题3和4，我们需要采取两种方式来解决：即减少map数和增加map数；

• 合并小文件减少map数

set mapred.max.split.size=112345600;
set mapred.min.split.size.per.node=112345600;
set mapred.min.split.size.per.rack=112345600;
set hive.input.format= org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;

这个参数表示执行前进行小文件合并，前面三个参数确定合并文件块的大小，大于文件块大小128m的，按照128m来分隔，小于128m，大于100m的，按照100m来分隔，把那些小于100m的（包括小文件和分隔大文件剩下的），进行合并。

• 增加map数

当input的文件都很大，任务逻辑复杂，map执行非常慢的时候，可以考虑增加Map数，来使得每个map处理的数据量减少，从而提高任务的执行效率。

如果表a只有一个文件，大小为120M，但包含几千万的记录，如果用1个map去完成这个任务，肯定是比较耗时的，这种情况下，我们要考虑将这一个文件合理的拆分成多个，这样就可以用多个map任务去完成。

set mapreduce.job.reduces =10;
create table a_1 as
select * from a
distribute by rand(123);

这样会将a表的记录，随机的分散到包含10个文件的a_1表中，再用a_1代替上面sql中的a表，则会用10个map任务去完成。

每个map任务处理大于12M（几百万记录）的数据，效率肯定会好很多。看上去，貌似这两种有些矛盾，一个是要合并小文件，一个是要把大文件拆成小文件，这点正是重点需要关注的地方，根据实际情况，控制map数量需要遵循两个原则：使大数据量利用合适的map数；使单个map任务处理合适的数据量；

9.4.2 reduce数

• 方法一

（1）每个Reduce处理的数据量默认是256MB
hive.exec.reducers.bytes.per.reducer=25612345
（2）每个任务最大的reduce数，默认为1009
hive.exec.reducers.max=1009
（3）计算reducer数的公式
N=min(参数2，总输入数据量/参数1)

• 方法二

在hadoop的mapred-default.xml文件中修改
设置每个job的Reduce个数
set mapreduce.job.reduces = 15;

• reduce个数的确定

1）过多的启动和初始化reduce也会消耗时间和资源；

2）另外，有多少个reduce，就会有多少个输出文件，如果生成了很多个小文件，那么如果这些小文件作为下一个任务的输入，则也会出现小文件过多的问题；

在设置reduce个数的时候也需要考虑这两个原则：处理大数据量利用合适的reduce数；使单个reduce任务处理数据量大小要合适；

9.5 查看执行计划explain

（1）查看下面这条语句的执行计划

hive (default)> explain select * from course;

（2）查看详细执行计划

hive (default)> explain extended select * from course;

9.6 并行执行

Hive会将一个查询转化成一个或者多个阶段。这样的阶段可以是MapReduce阶段、抽样阶段、合并阶段、limit阶段。或者Hive执行过程中可能需要的其他阶段。默认情况下，Hive一次只会执行一个阶段。不过，某个特定的job可能包含众多的阶段，而这些阶段可能并非完全互相依赖的，也就是说有些阶段是可以并行执行的，这样可能使得整个job的执行时间缩短。不过，如果有更多的阶段可以并行执行，那么job可能就越快完成。

通过设置参数hive.exec.parallel值为true，就可以开启并发执行。不过，在共享集群中，需要注意下，如果job中并行阶段增多，那么集群利用率就会增加。

set hive.exec.parallel=true;              //打开任务并行执行
set hive.exec.parallel.thread.number=16;  //同一个sql允许最大并行度，默认为8。

当然，得是在系统资源比较空闲的时候才有优势，否则，没资源，并行也起不来。

9.7 严格模式

Hive提供了一个严格模式，可以防止用户执行那些可能意向不到的不好的影响的查询。

通过设置属性hive.mapred.mode值为默认是非严格模式nonstrict。开启严格模式需要修改hive.mapred.mode值为strict，开启严格模式可以禁止3种类型的查询。

<property>
    <name>hive.mapred.mode</name>
    <value>strict</value>
    <description>
      The mode in which the Hive operations are being performed. 
      In strict mode, some risky queries are not allowed to run. They include:
        Cartesian Product.
        No partition being picked up for a query.
        Comparing bigints and strings.
        Comparing bigints and doubles.
        Orderby without limit.
    </description>
  </property>

1）对于分区表，除非where语句中含有分区字段过滤条件来限制范围，否则不允许执行。换句话说，就是用户不允许扫描所有分区。进行这个限制的原因是，通常分区表都拥有非常大的数据集，而且数据增加迅速。没有进行分区限制的查询可能会消耗令人不可接受的巨大资源来处理这个表。

2）对于使用了order by语句的查询，要求必须使用limit语句。因为order by为了执行排序过程会将所有的结果数据分发到同一个Reducer中进行处理，强制要求用户增加这个LIMIT语句可以防止Reducer额外执行很长一段时间。

3）限制笛卡尔积的查询。对关系型数据库非常了解的用户可能期望在执行JOIN查询的时候不使用ON语句而是使用where语句，这样关系数据库的执行优化器就可以高效地将WHERE语句转化成那个ON语句。不幸的是，Hive并不会执行这种优化，因此，如果表足够大，那么这个查询就会出现不可控的情况。

9.8 jvm重用

JVM重用是Hadoop调优参数的内容，其对Hive的性能具有非常大的影响，特别是对于很难避免小文件的场景或task特别多的场景，这类场景大多数执行时间都很短。

Hadoop的默认配置通常是使用派生JVM来执行map和Reduce任务的。这时JVM的启动过程可能会造成相当大的开销，尤其是执行的job包含有成百上千task任务的情况。JVM重用可以使得JVM实例在同一个job中重新使用N次。N的值可以在Hadoop的mapred-site.xml文件中进行配置。通常在10-20之间，具体多少需要根据具体业务场景测试得出。

<property>
  <name>mapreduce.job.jvm.numtasks</name>
  <value>10</value>
  <description>
  	How many tasks to run per jvm. If set to -1, there is no limit. 
  </description>
</property>

我们也可以在hive当中通过：这个设置来设置我们的jvm重用

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

这个功能的缺点是，开启JVM重用将一直占用使用到的task插槽，以便进行重用，直到任务完成后才能释放。如果某个“不平衡的”job中有某几个reduce task执行的时间要比其他Reduce task消耗的时间多的多的话，那么保留的插槽就会一直空闲着却无法被其他的job使用，直到所有的task都结束了才会释放。

9.9 推测执行

在分布式集群环境下，因为程序Bug（包括Hadoop本身的bug），负载不均衡或者资源分布不均等原因，会造成同一个作业的多个任务之间运行速度不一致，有些任务的运行速度可能明显慢于其他任务（比如一个作业的某个任务进度只有50%，而其他所有任务已经运行完毕），则这些任务会拖慢作业的整体执行进度。为了避免这种情况发生，Hadoop采用了推测执行（Speculative Execution）机制，它根据一定的法则推测出“拖后腿”的任务，并为这样的任务启动一个备份任务，让该任务与原始任务同时处理同一份数据，并最终选用最先成功运行完成任务的计算结果作为最终结果。

设置开启推测执行参数：Hadoop的mapred-site.xml文件中进行配置

<property>
  <name>mapreduce.map.speculative</name>
  <value>true</value>
  <description>If true, then multiple instances of some map tasks 
               may be executed in parallel.</description>
</property>

<property>
  <name>mapreduce.reduce.speculative</name>
  <value>true</value>
  <description>If true, then multiple instances of some reduce tasks 
               may be executed in parallel.</description>
</property>

不过hive本身也提供了配置项来控制reduce-side的推测执行：

  <property>
    <name>hive.mapred.reduce.tasks.speculative.execution</name>
    <value>true</value>
    <description>Whether speculative execution for reducers should be turned on. </description>
  </property>

关于调优这些推测执行变量，还很难给一个具体的建议。如果用户对于运行时的偏差非常敏感的话，那么可以将这些功能关闭掉。如果用户因为输入数据量很大而需要执行长时间的map或者Reduce task的话，那么启动推测执行造成的浪费是非常巨大大。

9.10 压缩

10. hive的常用函数

• 时间的拆分

# 拆分前标准时间格式
yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-dd'
year()#拆分年
month()
day()
hour()
minute()
second()

• url查分

  • parse_url

    Parts: HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, USERINFO
    
    SELECT parse_url('http://facebook.com/path/p1.php?query=1', 'HOST') FROM src LIMIT 1;  'facebook.com'
    SELECT parse_url('http://facebook.com/path/p1.php?query=1', 'QUERY') FROM src LIMIT 1; 'query=1'
    SELECT parse_url('http://facebook.com/path/p1.php?query=1', 'QUERY', 'query') FROM src LIMIT 1;'1'
    

  • parse_url_tuple

    SELECT b.* FROM src LATERAL VIEW parse_url_tuple(fullurl, 'HOST', 'PATH', 'QUERY', 'QUERY:id') b as host, path, query, query_id LIMIT 1;
    
    SELECT parse_url_tuple(a.fullurl, 'HOST', 'PATH', 'QUERY', 'REF', 'PROTOCOL', 'FILE',  'AUTHORITY', 'USERINFO', 'QUERY:k1') as (ho, pa, qu, re, pr, fi, au, us, qk1) from src a;
    

• 窗口函数over

-- 常用参数
(ROWS | RANGE) BETWEEN (UNBOUNDED | [num]) PRECEDING AND ([num] PRECEDING | CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
(ROWS | RANGE) BETWEEN CURRENT ROW AND (CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
(ROWS | RANGE) BETWEEN [num] FOLLOWING AND (UNBOUNDED | [num]) FOLLOWING)
-- 常配合使用的聚合函数
sum()
avg()
count()
-- 求topN的函数
row_number()
dense_rank()
rank()
-- 窗口内前后一行数据
lag(col,n)
lead(col,n)
-- 可用来求前20%
ntile(n)

• collect_set

-- 将一个列转化为一个set集合，多结合group by使用
SELECT school,collect_set(name) FROM stu2 GROUP BY school;--将一个学校的学生放到一个集合里

• explode

-- 将一个array数组转化为列，一般产生一列作为描述表
SELECT name,movie_catagory FROM movie LATERAL VIEW explode(catagory) tempTable AS movie_catagory

• substr

-- 字符串截取
SELECT substring('Facebook', 5) FROM src LIMIT 1; 'book'
SELECT substring('Facebook', -5) FROM src LIMIT 1; 'ebook'
SELECT substring('Facebook', 5, 1) FROM src LIMIT 1; 'b'

• concat

-- 字符串拼接

• case when

-- 及其常用的语法
case when "gender"="male" then 1 else 0 end

• reflect

-- 反射的使用

• cast

-- 类型类型转换    这里是将字符串类型转换为数值类型
SELECT cast(s_weather as int) FROM temp_weather