hive-DML-查询

查询语法：

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 [offset,] rows]

SELECT 语句可以是联合查询的一部分，也可以是另一个查询的子查询。
table_reference表示查询的输入。它可以是常规表、视图、连接构造或子查询。

表名和列名不区分大小写。

简单查询

全表查询

select * from table_name;

指定列查询

select select_expr1, select_expr2 from table_name;

算术运算符

运算符	描述
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按位取反

limit子句

LIMIT 子句可用于限制 SELECT 语句返回的行数。

LIMIT 需要一个或两个数字参数，它们都必须是非负整数常量。

第一个参数指定要返回的第一行的偏移量，第二个参数指定要返回的最大行数。

当给出单个参数时，它代表最大行数，偏移量默认为 0。

以下查询返回 5 个任意客户

SELECT * FROM customers LIMIT 5;

以下查询返回要创建的前 5 个客户

SELECT * FROM customers ORDER BY create_date LIMIT 5;
 

以下查询返回要创建的第 3 个到第 7 个客户

SELECT * FROM customers ORDER BY create_date LIMIT 2,5;

这些例子来自hive官网

where子句

WHERE 条件是一个布尔表达式。也支持某些类型的子查询。

比较运算符

操作符	支持的数据类型	描述
A=B	基本数据类型	如果A等于B则返回TRUE，否则返回FALSE
A<=>B	基本数据类型	如果A和B都为NULL，则返回TRUE，否则返回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相匹配，而不是只需与其字符串匹配。

like

通配符：

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

逻辑运算符

操作符	含义
AND	逻辑并
OR	逻辑或
NOT	逻辑否

group by语句

按性别计算不同用户的数量，可以编写以下查询：

INSERT OVERWRITE TABLE pv_gender_sum
SELECT pv_users.gender, count (DISTINCT pv_users.userid)
FROM pv_users
GROUP BY pv_users.gender;

可以同时进行多个聚合，但是，任何两个聚合都不能具有不同的 DISTINCT 列。例如，以下是可能的，因为 count(DISTINCT) 和 sum(DISTINCT) 指定了相同的列：

INSERT OVERWRITE TABLE pv_gender_agg
SELECT pv_users.gender, count(DISTINCT pv_users.userid), count(*), sum(DISTINCT pv_users.userid)
FROM pv_users
GROUP BY pv_users.gender;

但是，不允许以下查询。不允许在同一个查询中使用多个 DISTINCT 表达式。

INSERT OVERWRITE TABLE pv_gender_agg
SELECT pv_users.gender, count(DISTINCT pv_users.userid), count(DISTINCT pv_users.ip)
FROM pv_users
GROUP BY pv_users.gender;

使用 group by 子句时，select 语句只能包含 group by 子句中包含的列

SELECT
   a,
   sum(b)
FROM
   t1
GROUP BY
   a;

以下查询不工作，这是因为 select 子句有一个额外的列 ( b)，它没有包含在 group by 子句中（它也不是聚合函数）

SELECT
   a,
   b
FROM
   t1
GROUP BY
   a;

group by 语句后使用having语句进行过滤。