hive-06 查询

查询

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

准备数据

10	ACCOUNTING	1700
20	RESEARCH	1800
30	SALES	1900
40	OPERATIONS	1700

7369	SMITH	CLEARK	7902	1980-12-17	800.00		20
7499	ALLEN	SALESMAN	7698	1981-2-20	1600.00	300.00	30
7521	WARD	SALESMAN	7698	1981-2-22	1250.00	500.00	30
7566	JONES	MANAGER	7839	1981-4-2		2975.00	20
7654	MARTIN	SALESMAN	7698	1987-9-28	1250.00	1400.00	30
7698	BLAKE	MANAGER	7839	1987-5-1		2850.00	30
7782	CLARK	MANAGER	7839	1987-6-9		2450.00	10
7788	SCOTT	ANALYST	7566	1987-4-19	3000.00		20
7839	KING	PRESIDENT		1981-11-17	5000.00	10
7844	TURNER	SALESMAN	7698	1981-9-8	1500.00	0.00	30
7876	ADAMS	CLERK	7788	1987-5-23	1100.00		20
7900	JAMES	CLERK	7698	1987-12-3	950.00		30
7902	FORD	ANALYST	7566	1981-12-3	3000.00		20
7934	MILLER	CLERK	7782	1982-1-23	1300.00		10

注意：

• SQL语言大小写不敏感
• SQL可以写在一行或者多行
• 关键字不能被缩写也不能分行
• 各子语句一般要分行写
• 使用缩进提高语句的可读性

列别名：

• 紧跟列名 也可以使用 AS关键字 后跟别名

常用函数

• 行数 count()
• 最大值max()
• 最小值min()
• 总和sum()
• 平均值avg()

比较预算符

• A <=> B
  • 如果 A 和 B 都为NULL，则返回TRUE，如果一边为NULL则返回FALSE
• A <> B A != B
• BETWEEN A AND B
  • 左闭右闭
  • A < B
• LIKE
  • % 代表零个或多个只读（任意个字符）
  • _ 代表一个字符

# 以A开头
LIKE 'A%'
# 第二个字母为A的
LIKE '_A%'
# 查找名字中带有A的员工信息
RLIKE '[A]'

分组

• Group By
  • 常和聚合函数一起使用
  • where后面不能使用聚合函数 应该使用HAVING 进行过滤

Join语句

• 内连接
  • 只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来
• 左外连接
  • JOIN 操作符左边中符合WHERE子句的所有记录将会被返回
• 右外连接
  • JOIN 操作符右边中符合WHERE子句的所有记录将会被返回
• 满外连接
  • 将会返回所有表中符合WHERE语句条件的所有记录，如果任一表的指定字段没有符合条件的值，将会使用NULL
• 获取左边特有数据：LEFT JOIN添加WHERE筛选右边为NULL的数据
  • 也可以不使用JOIN 使用not in 使用子查询获取右边关联字段所有数据

NVL函数

• 如果第一个参数为NULL那么就使用第二个参数

union、union all

• 都是上下拼接表结果，字段数量必须一致
• union 去重
• 如果没有重复数据 union all 效率更高

笛卡尔积

产生条件：

• 省略连接条件
• 连接条件无效
• 所有表中的所有行互相连接

排序

• order by
  • 全局排序 只有一个reducer
  • 分区1 负无穷 – 2000
  • 分区2 2000 – 4000
  • 分区3 4000 – 正无穷
  • 每个分区内有序 全局就是有序的
• sort by
  • 对于大规模的数据集order by 的效率很低，很多情况下并不需要全局排序，可以使用sort by
  • 为每个reducer产生一个排序文件，每个reducer内部进行排序，对全局结果集来说并不是排序
    • 区内有序
• distribute by
  • 在有些情况我们需要控制某个特定行应该到哪个reducer，通常是为了后续的聚集操作
  • 类似MR中partition 自定义分区 进行分区 结合sort by 使用
  • 比如：先按照部门排序再按照薪水排序
• cluster by
  • 当distribute by和sort by字段相同时可以使用cluster by方式
  • 只能是升序排序

SELECT * FROM emp cluster by deptno;
# 等价于
SELECT * FROM emp distribute by deptno sort by deptno;