数据仓库Hive入门

Hive

Hive概述

什么是Hive

Hive：由 Facebook 开源用于解决海量结构化日志的数据统计。

Hive 是基于 Hadoop 的一个数据仓库工具，可以将结构化的数据文件映射为一张表，并

提供类 SQL 查询功能。本质是：将 HQL 转化成 MapReduce 程序。

• 1）Hive 处理的数据存储在 HDFS

  2）Hive 分析数据底层的实现是 MapReduce

  3）执行程序运行在 Yarn 上

Hive优缺点

优：简单，类sql语法，执行延迟较高，常用于数据分析

缺：不擅长数据挖掘，HQL表达能力有限，效率较低

Hive架构原理

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-IC2S3NRK-1614823586800)(C:\Users\石弘利\AppData\Roaming\Typora\typora-user-images\image-20200818223842706.png)]

• 1．用户接口：Client

  CLI（command-line interface）、JDBC/ODBC(jdbc 访问 hive)、WEBUI（浏览器访问 hive）

• 2．元数据：Metastore

  元数据包括：表名、表所属的数据库（默认是 default）、表的拥有者、列/分区字段、表的类型（是否是外部表）、表的数据所在目录等；

• 3．Hadoop

  使用 HDFS 进行存储，使用 MapReduce 进行计算。

• 4．驱动器：Driver

  （1）解析器（SQL Parser）：将 SQL 字符串转换成抽象语法树 AST，这一步一般都用

  第三方工具库完成，比如 antlr；对 AST 进行语法分析，比如表是否存在、字段是否存

  在、SQL 语义是否有误。

  （2）编译器（Physical Plan）：将 AST 编译生成逻辑执行计划。

  （3）优化器（Query Optimizer）：对逻辑执行计划进行优化。

  （4）执行器（Execution）：把逻辑执行计划转换成可以运行的物理计划。对于 Hive 来说，就是 MR/Spark

Hive 通过给用户提供的一系列交互接口，接收到用户的指令(SQL)，使用自己的 Driver，结合元数据(MetaStore)，将这些指令翻译成 MapReduce，提交到 Hadoop 中执行，最后，将执行返回的结果输出到用户交互接口。

Hive和传统数据库比较

Hive 采用了类似 SQL 的查询语言 HQL(Hive Query Language)，因此很容易将 Hive 理解为数据库。其实从结构上来看，Hive 和数据库除了拥有类似的查询语言，再无类似之处。

数据库可以用在 Online 的应用中，但是 Hive 是为数据仓库而设计的。

常见的不同方面比较：

• 查询语言：

  HQL是专门针对Hive的特性，设计的一种类SQL的查询语言

• 数据存储位置：

  Hive 是建立在 Hadoop 之上的，所有 Hive 的数据都是存储在 HDFS 中的。而数据库则可以将数据保存在块设备或者本地文件系统中。

• 数据更新：

  由于 Hive 是针对数据仓库应用设计的，而数据仓库的内容是读多写少的。因此，Hive中不建议对数据的改写，所有的数据都是在加载的时候确定好的。而数据库中的数据通常是需要经常进行修改的，因此可以使用 insert添加数据 ， 使 用 update修改数据。

• 执行方式：

  Hive 中大多数查询的执行是通过 Hadoop 提供的MapReduce来实现的。而数据库通常有自己的执行引擎。

• 执行延迟：

  Hive 在查询数据的时候，由于没有索引，需要扫描整个表，因此延迟较高。另外一个导致 Hive 执行延迟高的因素是 MapReduce 框架。由于 MapReduce 本身具有较高的延迟，因此在利用 MapReduce 执行 Hive 查询时，也会有较高的延迟。相对的，数据库的执行延迟较低。当然，这个低是有条件的，即数据规模较小，当数据规模大到超过数据库的处理能力的时候，Hive 的并行计算显然能体现出优势。

• 可扩展性：

  Hive的可扩展性和Hadoop可扩展性一致，远超过传统数据库

• 数据规模：

  Hive建立在集群上，使用MapReduce并行计算，支持大量的数据。传统数据库支持的数据规模相比较而言，较小。

Hive安装和注意点

安装与部署

我安装的是1.2.1版本：

官网地址：http://hive.apache.org/

下载地址：http://archive.apache.org/dist/hive/

安装步骤：

• （1）把 apache-hive-1.2.1-bin.tar.gz 上传到对应目录下

• （2）解压 apache-hive-1.2.1-bin.tar.gz

• （3）修改 apache-hive-1.2.1-bin.tar.gz 的名称为 hive

• （4）修改conf 目录下的 hive-env.sh.template 名称为 hive-env.sh

• （5）配置 hive-env.sh 文件

  ​ 配置 HADOOP_HOME 路径：export HADOOP_HOME=hadoop安装路径

  ​ 配置 HIVE_CONF_DIR 路径：export HIVE_CONF_DIR=Hive安装路径/conf

• （6）之后进入bin目录：./hive ，即可启动hive命令行界面

注意：在hive命令行界面

hive命令行之后一定要加分号 “;” ！！！

hive命令行之后一定要加分号 “;” ！！！

hive命令行之后一定要加分号 “;” ！！！

Hive命令行基本操作

（1）启动 hive

. / hive

（2）查看数据库

hive> show databases;

（3）打开默认数据库

hive> use default;

（4）显示 default 数据库中的表

hive> show tables;

（5）创建一张表

hive> create table student(id int, name string);

（6）显示数据库中有几张表

hive> show tables;

（7）查看表的结构

hive> desc student;

（8）向表中插入数据

hive> insert into student values(1,“xx”);

（9）查询表中数据

hive> select * from student;

（10）退出 hive

hive> quit;

hive 在 hdfs 中的结构

hive中的数据库：在 hdfs 中表现为${hive.metastore.warehouse.dir}目录下一个文件夹

hive中的表：在 hdfs 中表现所属数据库目录下一个文件夹，文件夹中存放该表中的具体数据

从本地导入本地文件到hive

使用load命令或者使用hadoop fs -put命令上传

load命令其实本质上也是hadoop fs -put实现的

hive> load data local inpath ‘本地路径’ into table 表名;

实际上就是把本地数据上传到hdfs的文件夹中

注意：

创建 student 表, 并声明文件分隔符’\t’

hive> create table student(id int, name string) ROW FORMAT DELIMITED FIELDS

TERMINATED

BY ‘\t’;

安装MySQL

如果不安装mysql数据库，hive只能启动一个客户端，效率很低，如果再启动一个客户端，会报SQLException的异常，这是由于derby数据库的原因：不能同时启动多个hive命令行客户端。

以centos7为例，安装mysql5.6

1. 查看是否有mariadb

   rpm -qa|grep mariadb 如果有 就删除：rpm -e --nodeps xx（删除的时候，要用root用户权限）

2. 解压mysql-libs.zip

3. 安装mysql服务器（这里之后都要用root用户）

• rpm -ivh MySQL-server-5.6.24-1.el6.x86_64.rpm
• 查看随机生成的密码：cat /root/.mysql_secret
• 查看mysql状态： service mysql status
• 启动mysql：service mysql start

4. 安装mysql客户端

• rpm -ivh MySQL-client-5.6.24-1.el6.x86_64.rpm
• 初始链接mysql：mysql -uroot -p密码（如果这步出现报错，就mysql -uroot -p然后提示输入密码，再输入密码即可）
• 修改密码为12345：mysql>SET PASSWORD=PASSWORD(‘12345’);

5. 退出mysql

• quit

mysql的user表主机配置

这步的目的，就是让在任何主机上，只要是root + 密码正确，都可以登录这个mysql数据库

1. 进入mysql： mysql -uroot -p12345

2. 使用mysql数据库：mysql>use mysql;

3. 查询user表：mysql>select User, Host, Password from user;

4. 修改user表：mysql>update user set host=’%’ where host=‘localhost’;

5. 删除root用户其他的host：

   mysql>delete from user where Host=‘hadoop’; （本机伪分布式为hadoop）

   mysql>delete from user where Host=‘127.0.0.1’;

   mysql>delete from user where Host=’::1’;

6. 刷新mysql，退出

   mysql>flush privileges;

   mysql>quit;

Hive原数据配置到mysql中

1. 拷贝驱动

   把mysql- connector-java-5.1.27-bin.jar （或者其他版本的mysql驱动包拷贝到安装的hive的lib目录下）：cp

2. 在hive的conf目录下创建一个hive-site.xml

3. 配置基本信息：

   <?xml version="1.0"?>
   <?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
   <configuration>
   <property>
   <name>javax.jdo.option.ConnectionURL</name>
   
   <value>jdbc:mysql://hadoop:3306/metastore?createDatabaseIfNotExist=true</value>
   <description>JDBC connect string for a JDBC metastore</description>
   </property>
   
   <property>
   <name>javax.jdo.option.ConnectionDriverName</name>
   <value>com.mysql.jdbc.Driver</value>
   <description>Driver class name for a JDBC metastore</description>
   </property>
   
   <property>
   <name>javax.jdo.option.ConnectionUserName</name>
   <value>root</value>
   <description>username to use against metastore database</description>
   </property>
   
   <property>
   <name>javax.jdo.option.ConnectionPassword</name>
   <value>000000</value>
   <description>password to use against metastore database</description>
   </property>
   </configuration>
   

   metastore就是一个表，里面存放hive用mysql作为驱动引擎的存放的数据

Hive常用交互命令

1. -e不进入 hive 的交互窗口执行 sql 语句： bin/hive -e “select id from student;（sql语句）”

2. -f 执行脚本（理解为自定义文件）中 sql 语句：在文件中写sql语句，让他去执行这个文件

3. 命令行sql语句追加到文件中：>

4. 其他：

   在 hive命令窗口中查看 hdfs 文件系统

   hive>dfs -ls /hdfs路径;

   在 hive命令窗口中查看本地文件系统

   hive>! ls 本地路径（注意有个感叹号）

   查看在 hive 中输入的所有历史命令

   （1） 进入到当前用户的根目录/root 或/home/hadoop

   （2） 查看. hivehistory 文件：cat .hivehistory

Hive常见属性配置

1. hive位置配置

• Default 数据仓库的最原始位置是在 hdfs 上的：/user/hive/warehouse 路径下

• 如果某张表属于 default 数据库，直接在数据仓库目录下创建一个文件夹。

• 如果想要修改：hive-default.xml.template 如下配置信息拷贝到hive-site.xml 文件中

hive.metastore.warehouse.dir /user/hive/warehouse location of default database for the warehouse ```

2. 显示信息配置

分别显示 现在在hive的那个数据库和打印时对应表的字段名

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

3. 日志信息配置

Hive 的 log 默认存放在/tmp/hadoop/hive.log 目录下（当前用户名下）

修改日志地址：hive/conf/hive-log4j.properties.template 文件名称为hive-log4j.properties 在 hive-log4j.properties 文件中修改 log 存放位置：hive.log.dir=/xx/hive/logs

4. 参数配置方式

   set；：查看所有配置信息

• 配置文件方式
• 命令行参数：-hiveconf param = value设定参数，但是只对这次hive启动有效
• 在 HQL 中使用 SET 关键字设定参数：hive (default)> set mapred.reduce.tasks=100;

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 allgood men”
TIMESTAMP		时间类型
BINARY		字节数组

Hive 的 String 类型相当于数据库的varchar 类型，该类型是一个可变的字符串

集合数据类型

数据类型	描述	语法示例
STRUCT	和c 语言中的 struct 类似，都可以通过“点”符号访问元素内容。例如，如果某个列的数据类型是 STRUCT{first STRING, last STRING},那么第 1 个元素可以通过字段.first 来引用。	struct() 例如struct<street:string, city:string>
MAP	MAP 是一组键-值对元组集合，使用数组表示法可以访问数据。例如，如果某个列的数据类型是 MAP，其中键->值对是’first’->’John’和’last’->’Doe’，那么可以通过字段名[‘last’]获取最后一个元素	map() 例如 map<string, int>
ARRAY	数组是一组具有相同类型和名称的变量的集合。这些变量称为数组的元素，每个数组元素都有一个编号，编号从零开始。例如，数组值为[‘John’, ‘Doe’]，那么第 2 个元素可以通过数组名[1]进行引用。	Array() 例如 array

row format delimited --------行格式化

fields terminated by ‘,’--------字段列分隔符

collection items terminated by ‘_’’---------map struct array的分隔符，（数据分割符号）

map keys terminated by ‘:’---------map中key和value分隔符

lines terminated by ‘\n’;---------行分隔符

访问三种集合中的数据方式：

select 数组[角标],Map[‘key’],struct.属性名 from 表名where 条件;

Hive类型转化

1. 隐式类型转换规则如下

（1） 任何整数类型都可以隐式地转换为一个范围更广的类型，如TINYINT 可以转换成 INT，INT 可以转换成 BIGINT。

（2） 所有整数类型、FLOAT 和 STRING 类型都可以隐式地转换成 DOUBLE。

（3） TINYINT、SMALLINT、INT 都可以转换为 FLOAT。

（4） BOOLEAN 类型不可以转换为任何其它的类型。

2. 使用cast强转

DDL数据定义（库、表增删改查）

创建数据库

1. 创建数据库数据库在HDFS 上的默认存储路径是/user/hive/warehouse/*.db。

2. 如果是默认default数据库下创建表，则表就在hdfs路径/user/hive/warehouse下

3. 如果是创建新的数据库，就在/user/hive/warehouse/*.db下（对应一个文件夹）。在这个库下创建的表，就在这个文件夹里。

4. 指定数据库在HDFS 上存放的位置：（因为不指定的话，默认放在数据仓库下，指定了之后就会放在指定的位置）

   create database 表名 location ‘/指定位置’;

查询数据库

1. 显示数据库：show databases; 模糊查询：like
2. 查看数据库：desc database 数据库；
3. 切换数据库：use 库名

修改数据库

1. 数据库的其他元数据信息都是不可更改的，包括数据库名和数据库所在的目录位置。（只能对后加的属性进行修改）
2. 使用 ALTER DATABASE 命令为某个数据库的 DBPROPERTIES 设置键-值对属性值，来描述这个数据库的属性信息

删除数据库

1. 删除空数据库drop database 【if exists】库名；
2. 删除非空数据库：drop database 【if exists】库名 cascade；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]

cretate table 表名（字段名 字段类型，字段名 字段类型…）row format delimited fields by ‘xx’;

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

（2） EXTERNAL 关键字可以让用户创建一个外部表，在建表的同时可以指定一个指向实际数据的路径（LOCATION），在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。

（3） COMMENT：为表和列添加注释。

（4） PARTITIONED BY 创建分区表，分区–》分文件夹

（5） CLUSTERED BY 创建分桶表，分桶–》分文件

（6） SORTED BY 不常用，对桶中的一个或多个列另外排序

（7） ROW FORMAT（行分割）

DELIMITED

[FIELDS TERMINATED BY char]

[COLLECTION ITEMS TERMINATED BY char]

[MAP KEYS TERMINATED BY char]

[LINES TERMINATED BY char]

| SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, …)]

用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，将会使用自带的 SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的 SerDe，Hive 通过 SerDe 确定表的具体的列的数据。

SerDe 是 Serialize/Deserilize 的简称， hive 使用 Serde 进行行对象的序列与反序列化。

（8） STORED AS 指定存储文件类型

常用的存储文件类型：SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、

RCFILE（列式存储格式文件）

如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE 。

（9）LOCATION ：指定表在HDFS 上的存储位置。

（10） AS：后跟查询语句，根据查询结果创建表。

（11） LIKE 允许用户复制现有的表结构，但是不复制数据。

内部表—外部表

默认创建的表都是所谓的管理表，有时也被称为内部表。

Hive 默认情况下会将这些表的数据存储在由配置项hive.metastore.warehouse.dir(例如，/user/hive/warehouse)所定义的目录的子目录下。当删除一个管理表时，Hive 也会删除这个表中数据。

使用external关键字生成的表，即为外部表。

两者区别：内部表在删除的时候，会把元数据和表中数据也一起删除（也就是mysql和hdfs的数据一起删除）。外部表删除的时候，只删除表的元数据，不删除表中数据。（相当于删了mysql中的数据，hdfs中的数据没有删）。

使用场景：每天将收集到的网站日志定期流入HDFS 文本文件。在外部表（原始日志表）的基础上做大量的统计分析，用到的中间表、结果表使用内部表存储，数据通过SELECT+INSERT 进入内部表。

内外部表相互转换：

改为外部表：alter table 表名 set tblproperties(‘EXTERNAL’=‘TRUE’);

改为内部表：alter table 表名 set tblproperties(‘EXTERNAL’=‘FALSE’);

！！！(‘EXTERNAL’=‘TRUE’)和(‘EXTERNAL’=‘FALSE’)为固定写法，区分大小写！

分区表

分区表实际上就是对应一个 HDFS 文件系统上的独立的文件夹，该文件夹下是该分区所有的数据文件。Hive 中的分区就是分目录，把一个大的数据集根据业务需要分割成小的数据集。

分区就是表对应文件夹下的一个文件夹，其中的一个字段作为分区条件，在分区对应文件夹下存放对应的数据。

把数据分开的好处是提高效率，因为hive没有索引，可以避免全表扫描。在查询时通过 WHERE 子句中的表达式选择查询所需要的指定的分区，这样的查询效率会提高很多。

分区的相关操作（CRUD）

• 创建分区表

create table dept_partition （分区表名）( deptno int, dname string, loc string) （字段）

partitioned by (month string) （分区）

row format delimited fields terminated by ‘\t’;

• 加载数据到分区表中

load data local inpath ‘/opt/module/datas/dept.txt’ into table default.dept_partition （表名）

（必须要指定分区字段名信息）partition(month=‘201709’);

• 查询分区表的数据

单分区：select * from dept_partition where month（分区字段信息）=‘201709’;

多分区：各个select语句之间用 union连接

• 增加分区

添加单个分区：alter table dept_partition add partition(month（分区字段字段）=‘201706’) ;

添加多个分区：alter table dept_partition add partition(month=‘201705’) partition(month=‘201704’);中间用空格隔开，实现多个分区添加

• 删除分区

删除单个：alter table dept_partition drop partition(month=‘201704’);

删除多个：中间用==“，”==分开

• 查看分区个数

show partitions dept_partition（表名）;

• 查看分区表结构

desc formatted dept_partition

分区表高级操作

1. 创建二级、多级分区表

   create table dept_partition2( deptno int, dname string, loc string)

   partitioned by (month string, day string)（两个分区字段）

2. 加载数据到二级分区，并查询

   load data local inpath ‘/opt/module/datas/dept.txt’ into table default.dept_partition2 partition(month=‘201709’, day=‘13’);

   select * from dept_partition2 where month=‘201709’ and day=‘13’;

修改表

1. 重命名表

   ALTER TABLE table_name RENAME TO new_table_name

2. 增加修改删除分区：见上

3. 增加修改替换列信息

   修改：ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]

   alter table 表名 change column 旧列名 新列名 列类型

   增加和替换：

   ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], …)

   alter table 表名 add/replace columns (列名 类型)

   如果是修改列的数据类型，要注意表中是否有数据，如果有数据的话，就不能进行修改。

   ADD 是代表新增一字段，字段位置在所有列后面，REPLACE 则是表示替换表中所有字段。

   replace实际上是修改了表的元数据，但是表的真实数据没有被删除，只是被隐藏了。

4. 删除表

   drop table 表名;

DML

数据导入

1. 向表中加载数据

load可以导入任意地址的数据

load data [local] inpath ‘/路径/文件’ [overwrite] into table 表名 [partition (partcol1=val1,…)];

（1） load data:表示加载数据

（2） local:表示从本地加载数据到 hive 表；否则从HDFS 加载数据到 hive 表

（3） inpath:表示加载数据的路径

（4） overwrite:表示覆盖表中已有数据，否则表示追加

（5） into 表名:表示加载到哪张表

（6） partition:表示上传到指定分区

2. 使用insert插入数据

insert into table 表名 [partition(xxx = ‘xxx’)] values (“xx”,“xx”);

insert into：以追加数据的方式插入到表或分区，原有数据不会删除

insert overwrite：会覆盖表或分区中已存在的数据

insert overwrite table student partition(month=‘201708’) select id, name from student where month=‘201709’;

3. 创建表的同时加载数据

create table if not exists 新表 as select原来旧表的数据字段 from 旧表;

4. 创建表的同时使用location加载指定路径的数据

create table if not exists 表名( 数据名称 数据类型…)

row format delimited fields terminated by ‘\t’ location ‘/数据存放路径(本地或者hdfs)’;

5. Import数据到hive表中

先用export导出，再数据导入

数据导出

1. insert导出

• 查询结果保存到本地/HDFS（一般不用 因为数据都连在一起了）

  insert overwrite 【local】directory ‘路径’ select查询语句;

  如果是local就是保存到本地，没有local就是hdfs

• 查询结果格式化并保存

  insert overwrite 【local】 directory 路径

  ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\t’ select 语句;

2. 使用Hadoop命令导出到本地

   hadoop fs -get hdfs路径 本地路径

3. hive shell：hive -f/-e 执行语句或者脚本 > file

4. export导出到hdfs上

   export table 表名 to ‘/hdfs路径文件夹’;

   export 和 import 主要用于两个Hadoop 平台集群之间 Hive 表迁移。

5. Sqoop导出：将mysql和hdfs/hive进行导出

6. 删除表中的数据

   truncate table 表名;truncate 只能删除内部表，不能删除外部表中数据。

查询

###基本查询

基本查询

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 number]

select * from 表名;

select 字段名 … from 表名;

（1） SQL 语言大小写不敏感。

（2） SQL 可以写在一行或者多行

（3） 关键字不能被缩写也不能分行

（4） 各子句一般要分行写。

（5） 使用缩进提高语句的可读性。

列/字段别名

用as或者空格，设置别名，和mysql一致

运算符

运算符	描述
A+B	A 和 B 相加
A-B	A 减去 B
A*B	A 和 B 相乘
A/B	A 除以 B
A%B	A 对 B 取余
A&B	A 和 B 按位取与
A|B	A 和 B 按位取或
A^B	A 和 B 按位取异或
~A	A 按位取反

常用函数

count总行数

max最大值

min最小值

sum总和

avg平均值

limit

limit 子句用于限制返回的行数。

select * from student limit 5;

Where子句

使用where子句，将不满足条件的行过滤掉。where子句紧随 from子句。

where 子句中不能使用字段别名。

比较运算符

操作符	支持的数据类型	描述
A=B	基本数据类型	如果A 等于 B 则返回TRUE，反之返回 FALSE
A<=>B	基本数据类型	如果A 和B 都为 NULL，则返回 TRUE，其他的和等号（=）操作符的结果一致，如果任一为 NULL 则结果为NULL
A<>B, A!=B	基本数据类型	A 或者 B 为 NULL 则返回NULL；如果A 不等于 B，则返回TRUE，反之返回 FALSE
A<B	基本数据类型	A 或者 B 为 NULL，则返回NULL；如果 A 小于 B，则返回 TRUE，反之返回 FALSE
A<=B	基本数据类型	A 或者 B 为 NULL，则返回 NULL；如果 A 小于等于 B，则返回TRUE，反之返回 FALSE
A>B	基本数据类型	A 或者 B 为 NULL，则返回NULL；如果 A 大于 B，则返回 TRUE，反之返回 FALSE
A>=B	基本数据类型	A 或者 B 为 NULL，则返回 NULL；如果 A 大于等于 B，则返回TRUE，反之返回 FALSE
A [NOT] BETWEEN B AND C	基本数据类型	如果 A，B 或者 C 任一为 NULL，则结果为 NULL。如果 A 的值大于等于 B 而且小于或等于C，则结果为 TRUE，反之为 FALSE。如果使用NOT 关键字则可达到相反的效果。
A IS NULL	所有数据类型	如果A 等于 NULL，则返回TRUE，反之返回 FALSE
A IS NOT NULL	所有数据类型	如果A 不等于NULL，则返回 TRUE，反之返回 FALSE
IN(数值 1, 数值 2)	所有数据类型	使用 IN 运算显示列表中的值，用于范围判断
A [NOT] LIKE B	STRING 类型	B 是一个 SQL 下的简单正则表达式，也叫通配符模式，如果 A 与其匹配的话，则返回 TRUE；反之返回 FALSE。B 的表达式说明如下：‘x%’表示 A 必须以字母‘x’开头，‘%x’表示 A 必须以字母’x’结尾，而‘%x%’表示 A 包含有字母’x’,可以位于开头，结尾或者字符串中间。如果使用 NOT 关键字则可达到相反的效果。
A RLIKE B, A REGEXP B	STRING 类型	B 是基于 java 的正则表达式，如果 A 与其匹配，则返回 TRUE；反之返回 FALSE。匹配使用的是 JDK 中的正则表达式接口实现的， 因为正则也依据其中的规则。例如，正则表达式必须和整个字符串 A 相匹配，而不是只需与其字符串匹配。

Like RLike

Like相当于mysql中的模糊查询

% 通配符，代表多个字符

_ 代表一个字符

RLIKE 子句可以通过 Java 的正则表达式，指定匹配条件。

逻辑运算符

and和 or或 not非

分组

group by

group by 按照一个或者多个列对结果进行分组， 然后对每个组执行聚合操作。一般和聚合函数一起使用。

having

having是对之前的查询结果在进行一个筛选的过程

where 后面不能写分组函数，而 having 后面可以使用分组函数。

having 只用于 group by 分组统计语句。

Join

Hive只支持等值连接，不支持非等值连接。

语法：select 字段信息 from 表1 join 表2 on 连接条件（这个条件在hive中只支持相等条件’=‘） …

内连接

只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。

直接用join

select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno= d.deptno;

左外连接

JOIN 操作符左边表中符合WHERE 子句的所有记录将会被返回。

left join

select e.empno, e.ename, d.deptno from emp e left join dept d on e.deptno = d.deptno;

右外连接

JOIN 操作符右边表中符合WHERE 子句的所有记录将会被返回。

right join

select e.empno, e.ename, d.deptno from emp e right join dept d on e.deptno = d.deptno;

全外连接

就相当于两个集合 并

full join

select e.empno, e.ename, d.deptno from emp e full join dept d on e.deptno = d.deptno;

多表连接

连接 n 个表，至少需要 n-1 个连接条件。例如：连接三个表，至少需要两个连接条件。

使用多个 join … on … 进行连接

SELECT e.ename, d.dname, l.loc_name FROM emp e

JOIN dept d ON d.deptno = e.deptno

JOIN location l ON d.loc = l.loc;

join效率不高，优化办法：当对 3 个或者更多表进行 join 连接时，如果每个 on 子句都使用相同的连接键的话，那么只会产生一个 MapReduce job。

排序

order by

按照字段进行排序，默认asc升序，desc降序

order by在select最后使用

order by做的是全局排序，只有一个Reducer，保证全局有序

按照别名、多个列也可以进行排序

每个MR内部排序

Sort By：对于大规模的数据集 order by 的效率非常低。在很多情况下，并不需要全局排序，此时可以使用sort by。

Sort by 为每个 reducer 产生一个排序文件。每个 Reducer 内部进行排序，对全局结果集来说不是排序。

设置、查看reduce个数：set mapreduce.job.reduces = x;

分区排序

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

1. distribute by 的分区规则是根据分区字段的 hash 码与reduce 的个数进行模除后，余数相同的分到一个区。

2. Hive 要求DISTRIBUTE BY 语句要写在 SORT BY 语句之前。

cluster by

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

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

排序方式	解释	注意点
order by	全局排序	reduce个数为1，和设置无关
sort by	区内排序	在分区的各个区内，进行排序，和distribute by连用，reduce个数为多个
distribute by	分区排序	自定义分区，需要事先定义多个reduce，否则没有意义
cluster by	聚合排序	distribute by 和 sort by 字段相同时，可以使用 cluster by 方式，只能升序排序

排序。

设置、查看reduce个数：set mapreduce.job.reduces = x;

分区排序

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

1. distribute by 的分区规则是根据分区字段的 hash 码与reduce 的个数进行模除后，余数相同的分到一个区。

2. Hive 要求DISTRIBUTE BY 语句要写在 SORT BY 语句之前。

cluster by

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

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

排序方式	解释	注意点
order by	全局排序	reduce个数为1，和设置无关
sort by	区内排序	在分区的各个区内，进行排序，和distribute by连用，reduce个数为多个
distribute by	分区排序	自定义分区，需要事先定义多个reduce，否则没有意义
cluster by	聚合排序	distribute by 和 sort by 字段相同时，可以使用 cluster by 方式，只能升序排序