Hive SQL语言

Hive SQL之数据库与建库

create database :创建数据库

CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
 [COMMENT database_comment]
 [LOCATION hdfs_path]
 [WITH DBPROPERTIES (property_name=property_value, ...)];

COMMENT：数据库的注释说明语句

LOCATION：指定数据库在HDFS存储位置，默认/user/hive/warehouse/dbname.db

WITH DBPROPERTIES：用于指定一些数据库的属性配置。

use database :选择特定的数据库

drop database :删除数据库

默认行为是RESTRICT，这意味着仅在数据库为空时才删除它。

要删除带有表的数据库（不为空的数据库），我们可以使用CASCADE。

DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];

Hive SQL之表与建表

create table : 创建表

CREATE TABLE [IF NOT EXISTS] [db_name.]table_name
 (col_name data_type [COMMENT col_comment], ... )
 [COMMENT table_comment]
 [ROW FORMAT DELIMITED …];

注意事项

• 蓝色字体是建表语法的关键字，用于指定某些功能。

• [ ]中括号的语法表示可选。

• 建表语句中的语法顺序要和语法树中顺序保持一致。

• 最低限度必须包括的语法为：

                            CREATE TABLE table_name (col_name data_type);
  

分隔符指定语法

• ROW FORMATDELIMITED语法用于指定字段之间等相关的分隔符，这样Hive才能正确的读取解析数据。
• 或者说只有分隔符指定正确，解析数据成功，我们才能在表中看到数据。

Hive SQL-DML-Load加载数据

Load语法功能

• Load英文单词的含义为：加载、装载；
• 所谓加载是指：将数据文件移动到与Hive表对应的位置，移动时是纯复制、移动操作。
• 纯复制、移动指在数据load加载到表中时，Hive不会对表中的数据内容进行任何转换，任何操作。

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename;

语法规则之filepath

• filepath表示待移动数据的路径。可以指向文件（在这种情况下，Hive将文件移动到表中），也可以指向目录（在 这种情况下，Hive将把该目录中的所有文件移动到表中）。
• filepath文件路径支持下面三种形式，要结合LOCAL关键字一起考虑：

1. 相对路径，例如：project/data1
2. 绝对路径，例如：/user/hive/project/data1
3. 具有schema的完整URI，例如：hdfs://namenode:9000/user/hive/project/data1

语法规则之LOCAL

指定LOCAL，将在本地文件系统中查找文件路径。

• 若指定相对路径，将相对于用户的当前工作目录进行解释；
• 用户也可以为本地文件指定完整的URI-例如：file:///user/hive/project/data1

没有指定LOCAL关键字

• 如果filepath指向的是一个完整的URI，会直接使用这个URI；
• 如果没有指定schema，Hive会使用在hadoop配置文件中参数fs.default.name指定的（不出意外，都是HDFS）。

Hive SQL-DML-Insert插入数据

insert+select：将后面查询返回的结果作为内容插入到指定表中

INSERT INTO TABLE tablename select_statement1 FROM from_statement;

1. 需要保证查询结果列的数目和需要插入数据表格的列数目一致。
2. 如果查询出来的数据类型和插入表格对应的列数据类型不一致，将会进行转换，但是不能保证转换一定成功，转换 失败的数据将会为NULL。

Hive SQL DML语法之查询数据

Select语法树

SELECT [ALL | DISTINCT] select_expr, select_expr, ...
 FROMtable_reference
 [WHERE where_condition]
 [GROUP BY col_list]
 [ORDER BY col_list]
 [LIMIT [offset,] rows];

• 从哪里查询取决于FROM关键字后面的table_reference，这是我们写查询SQL的首先要确定的事即你查询谁？
• 表名和列名不区分大小写。

1.select_expr：select_expr表示检索查询返回的列，必须至少有一个select_expr。

2.ALL 、DISTINCT：用于指定查询返回结果中重复的行如何处理。

3.WHERE

• WHERE后面是一个布尔表达式（结果要么为true，要么为false），用于查询过滤，当布尔表达式为true时，返回 select后面expr表达式的结果，否则返回空。
• 在WHERE表达式中，可以使用Hive支持的任何函数和运算符，但聚合函数除外。

4.聚合操作

• SQL中拥有很多可用于计数和计算的内建函数，其使用的语法是：SELECT function(列) FROM 表。
• 这里我们要介绍的叫做聚合（Aggregate）操作函数，如：Count、Sum、Max、Min、Avg等函数。
• 聚合函数的最大特点是不管原始数据有多少行记录，经过聚合操作只返回一条数据，这一条数据就是聚合的结果。

5.GROUP BY

• GROUP BY语句用于结合聚合函数，根据一个或多个列对结果集进行分组；
• 如果没有group by语法，则表中的所有行数据当成一组。

语法限制

• 出现在GROUP BY中select_expr的字段：要么是GROUP BY分组的字段；要么是被聚合函数应用的字段。
• 原因：避免出现一个字段多个值的歧义。

6.HAVING

• 在SQL中增加HAVING子句原因是，WHERE关键字无法与聚合函数一起使用。
• HAVING子句可以让我们筛选分组后的各组数据,并且可以在Having中使用聚合函数，因为此时where，group by 已经执行结束，结果集已经确定。

HAVING与WHERE区别

• having是在分组后对数据进行过滤
• where是在分组前对数据进行过滤
• having后面可以使用聚合函数
• where后面不可以使用聚合函数

7.ORDER BY

• ORDER BY 语句用于根据指定的列对结果集进行排序。
• ORDER BY 语句默认按照升序（ASC）对记录进行排序。如果您希望按照降序对记录进行排序，可以使用DESC关 键字

8.LIMIT

• LIMIT用于限制SELECT语句返回的行数。
• LIMIT接受一个或两个数字参数，这两个参数都必须是非负整数常量。
• 第一个参数指定要返回的第一行的偏移量（从Hive 2.0.0开始），第二个参数指定要返回的最大行数。当给出单个 参数时，它代表最大行数，并且偏移量默认为0。

执行顺序

在查询过程中执行顺序：from > where > group（含聚合）> having >order > select；

1. 聚合语句(sum,min,max,avg,count)要比having子句优先执行

2. where子句在查询过程中执行优先级别优先于聚合语句(sum,min,max,avg,count)

Hive SQL Join关联查询

Hive Join语法规则

join_table:
 table_reference [INNER] JOIN table_factor [join_condition]
 | table_reference {LEFT} [OUTER] JOIN table_reference join_condition

 join_condition:
 ON expression

• table_reference：是join查询中使用的表名。
• table_factor：与table_reference相同,是联接查询中使用的表名。
• join_condition：join查询关联的条件，如果在两个以上的表上需要连接，则使用AND关键字。

inner join 内连接

• 内连接是最常见的一种连接，它也被称为普通连接，其中inner可以省略：inner join == join ；
• 只有进行连接的两个表中都存在与连接条件相匹配的数据才会被留下来。

left join 左连接

• left join中文叫做是左外连接(Left Outer Join)或者左连接，其中outer可以省略，left outer join是早期的写法。
• left join的核心就在于left左。左指的是join关键字左边的表，简称左表。
• 通俗解释：join时以左表的全部数据为准，右边与之关联；左表数据全部返回，右表关联上的显示返回，关联不上 的显示null返回。