时间溢出的地方
0%

ODPS 中的 SQL 学习笔记

发表于 分类于 阅读次数： Valine：
本文字数： 2.5k 阅读时长 ≈ 2 分钟

ODPS 中的 SQL，与大部分平台的 SQL 基本是一样的，但可能已有一些差异。这里记录的一些内容，可能有的是通用的，有的是 ODPS 特有的，我也不是很清楚。自己在 ODPS 中使用 SQL 时，可能因为用法不熟悉而写一些很低效的语句，这个地方记录一些用法和技巧，以使应用更高效。

主要内容来自于《ODPS权威指南 阿里大数据平台应用开发实践》。

1. 表和分区

创建表的时候指定分区列：

1
2
3
4
5
6
CREATE TABLE IF NOT EXISTS table_name (
	user_id BIGINT, 
    url STRING
)
PARTITION BY (ds STRING, country STRING)
;

分区列通常称为分区键，可以有多级。

两种创建表的方式：

  • CREATE TABLE tabel_name_like LIKE table_tame: 会创建一个和 table_name 有同样 schema 的表，但不会复制任何数据
  • CREATE TABLE table_name_as AS SELECT ... FROM table_name 不支持生成带分区的表

1.1. 添加分区

1
ALTER TABLE tabel_name IF NOT EXISTS PARTITION (ds='20220529', country='China');

注意添加分区不是添加分区键，是指给已有的分区键增加新的分区值

ODPS 不支持添加新的分区键

1.2. 修改表

  1. 添加列

    1
    ALTER TABLE tabel_name ADD COLUMNS (info STRING);

    对于已有记录，新增的列的值都为空

  2. 添加列的注释

    1
    ALTER TABLE tabel_name CHANGE COLUMN info COMMENT 'user information';

  3. ODPS 不支持删除列

1.3. 删除分区

删除分区实际是修改表：

1
ALTER TBLE table_name DROP IF EXISTS PARTITION (ds='20220529', country='China');

2. 多表连接 JOIN

2.1. 普通 JOIN

如果想求只在 A 中不在 B 中的记录：

1
SELECT * FROM A LFET JOIN B ON A.id = B.id WHERE B.id IS NULL;

一般这种场景只查询表 A 的字段，修改如下：

1
SELECT A.* FROM A LFET JOIN B ON A.id = B.id WHERE B.id IS NULL;

FULL JOIN 包含左表和右表的并集，未匹配的字段用 NULL 表示

如果想求两张表的差集，即只在 A 中的记录或只在 B 中的记录，则使用：

1
SELECT * FROM A FULL JOIN B ON A.id = B.id WHERE B.id IS NULL OR B.id IS NULL;

ON 和 WHERE

  1. 对于内连接，ON 和 WHERE 在语义是一致的，执行结果等价，如：

    1
    2
    3
    4
    5
    6
    SELECT A.id, B.score FROM A JOIN B
    ON (A.id = B.id AND B.score > 80);

    SELECT A.id, B.score FROM A JOIN B
    ON A.id = B.id 
    WHERE B.score > 80;

    上面两句是等价的

  2. 对于外连接，ON 和 WHERE 是不等价的。以左连接为例：

    • 对于 ON 查询，先对表 B 执行过滤，得到中间结果集，再和表 A 执行左连接，得到最终结果
    • 对于 WHERE 查询，先对表 A 和表 B 执行左连接，得到中间结果，再根据 WHERE 条件进行过滤

2.2. MAP JOIN

当一张大表和一张或多张小表 JOIN 时，使用 MAPJOIN 会比普通 JOIN 快很多

MAPJOIN 对小表的大小限制为 512 MB

举例：

1
2
3
4
5
6
7
8
SELECT /*+ MAPJOIN(u) */
	u.user_id, 
	u.gender
	pv.view_time
FROM user u
JOIN page_view pv
ON u.user_id = pv.user_id
;

这里的 /*+ MAPJOIN(u) */ 是必须的，通常称为 MAPJOIN HINT，标识该 JOIN 是 MAPJOIN，而且小表是 u。

对于 INNER JOIN 操作，大小表的位置不敏感，在上面的查询中，写成 FROM user u JOIN page_view pv 和 FROM page_view pv JOIN user u 都可以，只要指明哪个是小表即可。

但对于 a LEFT JOIN b，只能 b 是小表，也就是说 MAPJOIN HINT 只能写成 /*+ MAPJOIN(b) */；

同样，对于 a RIGHT JOIN b，只能 a 是小表

左连接左表不能是小表，右连接右表不能是小表

3. 高级查询

3.1. 聚合操作

聚合函数常见的有 COUNT, SUM, MAX, MIN 等，聚合函数和关键字 GROUP BY 一起使用。

注意： SELECT 中的所有字段，除了聚合函数中的字段以及和列无关的函数计算外，都必须包含在 GROUP BY 中

1
2
3
4
5
6
SELECT 
	gender, 
	COUNT(DISTINCT user_id) AS user_cnt, 
	COUNT(DISTINCT ip) AS ip_cnt
FROM pv_users
GROUP BY gender;

ODPS 支持 DISTINCT 操作作用于不同的列。

3.2. 窗口函数

常见的如 row_number(), rank() 等，例如：

1
2
3
4
5
6
SELECT 
	id, 
	course, 
	score, 
	rank() OVER (PARTITION BY course ORDER BY score DESC) AS rank
FROM student_score;

PARTITION BY key 会把记录按照 key 进行分组（一个分组就是一个「窗口」），每个窗口内的记录执行这个窗口函数。如果想把表中的所有记录当作一个窗口，就写成 PARTITION BY 1

3.3. 多路输出

扫描一次原始表，输出结果到不同的表中：

1
2
3
4
5
6
7
8
FROM pv_users
INSERT OVERWRITE TABLE pv_gender_sum
	SELECT gender, COUNT(DISTINCT user_id)
	GROUP BY gender
INSERT OVERWRITE TABLE pv_age_sum
	SELECT age, COUNT(DISTINCT user_id)
	GROUP BY age
;

多路输出不仅支持输出到多张表，还支持输出到同一张表的不同分区，或者混合模式