函数

数据类型

INYINT：1byte有符号整数

SMALINT：2byte有符号整数

INT：4byte有符号整数

BIGINT：8byte有符号整数

BOOLEAN：布尔型

FLOAT：单精度浮点型

DOUBLE：双精度浮点型

STRING：字符系列

TIMESTAMP：时间类型

BINARY：字节数组

创建数据库

CREATE DATABASE [IF NOT EXISTS] database_name

[COMMENT database_comment]

[LOCATION hdfs_path]

[WITH DBPROPERTIES (property_name=property_value, ...)];

create database 数据库名称 location   指定存放的路径；

（1）避免创建数据库存在错误，添加 if not exists 进行判断

（2）drop：删除空数据库

（3）如果删除的数据库不存在，最好采用 if exists 判断数据库是否存在。

（4）如果数据库不为空，可以采用cascade命令，强制删除

（5）显示数据库详细信息：extended

（6）修改数据库：ALTER DATABASE命令为某个数据库的DBPROPERTIES设置键-值对属性值，来描述这个数据库的属性信息。数据库的其他元数据信息都是不可更改的，包括数据库名和数据库所在的目录位置。

（7）查看表格式化数据：desc 表名；

（8）查看表的类型：desc formatted 表名；

创建表

（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允许用户复制现有的表结构，但是不复制数据。

管理表和外部表

管理表：managed table 默认创建的表都是管理表，Hive会控制着数据的生命，当我们删除一个管理表时，Hive也会删除这个表中的数据。

外部表：external table 不控制数据的整个生命周期，删除该表不会删除这份数据，删除的是当前表的元数据信息

管理表切换为外部表：external=true（EXTERNAL=TRUE)

外部表切换为管理表：external=False（EXTERNAL=FALSE）.

修改内部表为外部表：alter table 表名 set tblproperties（'EXTERNAL'='TRUE'）

修改外部表为内部表：alter table student2 set tblproperties('EXTERNAL'='FALSE');

重命名：ALTER TABLE 表名 RENAME TO 新的表名；

更新列：ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]

增加和替换列：ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...) 

删除表：drop table 表名；

数据导入

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

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

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

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

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

（6）student:表示具体的表

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

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

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

（10）清楚表中数据：Truncate

查询

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

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

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

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

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

（6）AS 起别名

算术运算符

运算符

描述

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按位取反

操作符

支持的数据类型

描述

A=B

基本数据类型

如果A等于B则返回TRUE，反之返回FALSE

A<=>B

基本数据类型

如果A和B都为NULL，则返回TRUE，如果一边为NULL，返回False

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相匹配，而不是只需与其字符串匹配。

常用函数

计数：count（）

最大值：max（）

总和：sum（）

最小值：min（）

平均值：avg（）

限制返回的行数：limit

like：

% 代表零个或多个字符(任意个字符)。

_ 代表一个字符。

AND

逻辑并

OR

逻辑或

NOT

逻辑否


分组：

分组：Group By

having与where不同点

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

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

join：只支持等值连接，不支持非等值连接

表的别名:

（1）使用别名可以简化查询。

（2）使用表名前缀可以提高执行效率。 

排序

全局排序（Order By）

Order By：全局排序，只有一个Reducer

1）使用 ORDER BY 子句排序

ASC（ascend）: 升序（默认）

DESC（descend）: 降序

2）ORDER BY 子句在SELECT语句的结尾 

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

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

分区排序（Distribute By)

函数

（1）查看系统自带的函数：show functions;

（2）查看自带的函数用法：desc functions upper；

（3）详细显示自带的函数的用法：desc functions extended upper；

（4）空字段赋值：nvl【NVL（value，default_value）】为了给NULL赋值

（5）行转列：

  • CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串;
  • CONCAT_WS(separator, str1, str2,...)：它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;
  • COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段。

（6）列转行

  • EXPLODE(col)：将hive一列中复杂的array或者map结构拆分成多行。
  • LATERAL VIEW
  • 用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
  • 解释：用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。

（7）开窗函数：

  • OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化。
  • CURRENT ROW：当前行
  • n PRECEDING：往前n行数据
  • n FOLLOWING：往后n行数据
  • UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING表示到后面的终点
  • LAG(col,n,default_val)：往前第n行数据
  • LEAD(col,n, default_val)：往后第n行数据
  • NTILE(n)：把有序窗口的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。注意：n必须为int类型
  • RANK() 排序相同时会重复，总数不会变
  • DENSE_RANK() 排序相同时会重复，总数会减少
  • ROW_NUMBER() 会根据顺序计算

（9）自定义UDF函数

　　　　1）UDF（User-Defined-Function）

　　　　　　一进一出

　　（2）UDAF（User-Defined Aggregation Function）

　　　　　　聚集函数，多进一出

　　　　　　类似于：count/max/min

　　（3）UDTF（User-Defined Table-Generating Functions）

　　　　　　一进多出

　　　　　　如lateral view explore()

（10）自定义函数

　　　1）Hive 自带了一些函数，比如：max/min等，但是数量有限，自己可以通过自定义UDF来方便的扩展。

　　　　2）当Hive提供的内置函数无法满足你的业务处理需要时，此时就可以考虑使用用户自定义函数（UDF：user-defined function）。

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/liang1013/p/14038186.html