当前位置： 首页 > article >正文

MaxCompute—阿里云原生大数据计算机服务——SQL概述与服务支持

article 2025/1/27 8:19:11

MaxCompute（原名ODPS，Oriented Data Processing Service）是阿里云提供的一款云原生大数据计算服务。它是一种基于SQL的全托管式大数据处理平台，允许用户在云端快速、简便地处理和分析海量数据。

什么是MaxCompute

MaxCompute是适用于数据分析场景的企业级SaaS（Software as a Service）模式云数据仓库，以Serverless架构提供快速、全托管的在线数据仓库服务，消除了传统数据平台在资源扩展性和弹性方面的限制，最小化用户运维投入，使您可以经济并高效地分析处理海量数据。

随着数据收集手段不断丰富，行业数据大量积累，数据规模已增长到了传统软件行业无法承载的海量数据（TB、PB、EB）级别。MaxCompute提供离线和实时的数据接入，支持大规模数据计算及查询加速能力，为您提供面向多种计算场景的数据仓库解决方案及分析建模服务。MaxCompute还为您提供完善的数据导入方案以及多种经典的分布式计算模型，您可以不必关心分布式计算和维护细节，便可轻松完成大数据分析。

MaxCompute适用于100 GB以上规模的存储及计算需求，最大可达EB级别，并且MaxCompute已经在阿里巴巴集团内部得到大规模应用。MaxCompute适用于大型互联网企业的数据仓库和BI分析、网站的日志分析、电子商务网站的交易分析、用户特征和兴趣挖掘等。详细发展历程、产品荣誉及客户案例请参见发展历程和客户案例。

以上内容来自 MaxCompute（MaxCompute）——阿里云帮助中心 [help.aliyun.com/]
开发参考：SQL
SQL概述

MaxCompute SQL是MaxCompute中用于数据查询和分析的SQL语言，其语法类似于标准SQL，但在标准语法ANSI SQL92的基础上进行了一些扩展和限制以更好地服务于大规模数据仓库的场景。本文为您介绍MaxCompute SQL使用场景、使用向导及支持的工具信息，为后续使用MaxCompute SQL提供帮助。

应用场景

MaxCompute SQL 是一种非常强大的工具，专门设计用于处理极大量的数据（从几十GB到数EB）。它非常适合在后台运行大规模的数据分析和处理任务，例如每天或每周一次的报告生成、数据挖掘、或者机器学习模型的训练。

当你提交一个 MaxCompute 作业时，它不会立即开始执行，而是需要等待一段时间（通常是几十秒到数分钟之间），这段时间被称为排队调度。这是因为 MaxCompute 需要安排资源和调度任务，以确保所有的作业都能公平地获得计算资源。

由于存在这种延迟，MaxCompute SQL 不太适合直接连接到需要实时响应的前台业务系统。例如，如果你的网站需要每秒处理几千到数万笔事务，使用 MaxCompute SQL 可能会导致不良的用户体验。

一、DDL语句

1.1 表操作

表是MaxCompute的数据存储单元。数据仓库的开发、分析及运维都需要对表的数据进行处理。

1.1.1 创建表

创建非分区表、分区表、外部表或聚簇表。

  • 非分区表（普通表）：最常见的表类型，数据存储在一个单一的逻辑结构中
  • 分区表：将数据分成多个独立的分区，每个分区可以独立管理
  • 外部表：用于访问存储在数据库外部的数据文件，而不是再数据库内内部存储数据
  • 聚簇表：将多张表的相关数据存储在同一个物理块中，基于某个共同的列
--创建新表。
 create [external] table [if not exists] <table_name>
 [primary key (<pk_col_name>, <pk_col_name2>),(<col_name> <data_type> [not null] [default <default_value>] [comment <col_comment>], ...)]
 [comment <table_comment>]
 [partitioned by (<col_name> <data_type> [comment <col_comment>], ...)]
 
--用于创建聚簇表时设置表的Shuffle和Sort属性。
 [clustered by | range clustered by (<col_name> [, <col_name>, ...]) [sorted by (<col_name> [asc | desc] [, <col_name> [asc | desc] ...])] into <number_of_buckets> buckets] 

--仅限外部表。
 [stored by StorageHandler] 
 --仅限外部表。
 [with serdeproperties (options)] 
 --仅限外部表。
 [location <osslocation>] 

--指定表为Transactional表，后续可以对该表执行更新或删除表数据操作，但是Transactional表有部分使用限制，请根据需求创建。
 [tblproperties("transactional"="true")]

--指定表为Delta Table表，结合primary key，后续可以做upsert，增量查询，time-travel等操作
 [tblproperties ("transactional"="true" [, "write.bucket.num" = "N", "acid.data.retain.hours"="hours"...])] [lifecycle <days>]
;

--基于已存在的表创建新表并复制数据，但不复制分区属性。支持外部表和湖仓一体外部项目中的表。
create table [if not exists] <table_name> [lifecycle <days>] as <select_statement>;

--基于已存在的表创建具备相同结构的新表但不复制数据，支持外部表和湖仓一体外部项目中的表。
create table [if not exists] <table_name> like <existing_table_name> [lifecycle <days>];
1.1.1.1 创建新表：
  • 你可以创建一个新的数据表，像在Excel中创建新的工作表
  • 例如，你想创建一个存储员工信息的表，包含员工ID、姓名和部门
create table if not exists employees (
    employee_id int primary key,
    name varchar(int) not null,
    department varchar(50)
) comment '员工信息表';
1.1.1.2 分区和聚簇表：
  • 分区表就像把一个大文件夹里的文件按日期分成多个小文件夹，方便查找
  • 聚簇表是把数据按某个顺序排列，想图书馆按字母排列书籍
create table sales (
    sale_id int,
    sale_date date,
    amount decimal(10,2)
)partitioned by (sale_date)
clustered by (sale_id) into 4 buckets;
1.1.1.3 外部表：
  • 外部表就像是一个链接，指向存储在别处的数据，而不是把数据直接放在数据库里
  • 例如，你有一些CSV文件存储在云端，现在想再数据库中访问他们
create external table external_data(
    id int,
    value string
)
location 'oss://my-bucket/data/';
1.1.1.4 事务性和Delta Table：
  • 事务性表允许对数据进行更新和删除，确保数据的一致性
  • Delta Table支持增量更新和时间旅行功能，方便追踪数据变化
create table financial_records(
    record_id int primary key,
    amount decimal(10,2),
    record_date date
)tblproperties("transactional"="true");
1.1.1.5 基于已有表创建新表
  • 你可以复制一个表结构或数据，就像复制一个文件
  • 例如，你想创建一个和现有员工表结构相同的新表，不包含数据
create table if not exists employee_backup like employees;

通用参数：

  • external：可选，表示创建的表为外部表
  • if not exists：可选，如果不指定if not exists选项而存在同名表会报错。如果指定if not exists，只要存在同名表，即使原表结构与要创建的目标结构表结构不一致，均返回成功，已存在的同名表的元数据信息不会被改动。
  • table_name:必填，表名，表名大小写不敏感，不能有特殊字符，只能包含a～z、A～Z、数字和下划线。建议以字母开头，名称的长度不超过128字节，否则报错
  • primary key（pk）：可选。表的主键，可以定义一个或多个列作为主键，表示这些列的组合在表中必须唯一，语法遵循标准SQL primary key语法，pk列必须设置not null，不允许修改
  • col_name：可选。表的列名。列名大小写不敏感，不能有特殊字符，只能包含a~z、A~Z、数字、下划线（_）或中文。建议以字母开头，名称的长度不超过128字节，否则报错
  • col_comment：可选。列的注释内容。注释内容为长度不超过1024字节的有效字符串，否则报错
  • data_type：可选。列的数据类型，包含BIGINT、DOUBLE、BOOLEAN、DTETIME、DECIMAL和STRING等多种数据类型
  • default_value：可选。指定列的默认值，当insert操作不指定该列时，该列写入默认值
  • table_comment：可选。表的注释内容。注视内容为长度不超过1024字节的有效字符串，否则报错
  • lifecycle：可选。表的生命周期，仅支持正整数，单位：天

分区表参数：

  • col_name：表的分区列名。列名大小写不敏感，不能有特殊字符，只能包含a~z、A~Z、数字、下划线（_）或中文。建议以字母开头，名称的长度不超过128字节，否则报错
  • data_type：分区列的数据类型
  • col_comment：分区列的注释内容，注释内容为长度不超过1024字节的有效字符串，否则报错

注意：分区值不能包含双字节字符（如中文），必须以字母开头，包含字母、数字和允许的字符，长度不超过255字节。允许的字符包括空格、冒号（:）、下划线（_）、美元符号（$）、井号（#）、英文句点（.）、感叹号（!）和at（@），其他字符的行为未定义，例如转义字符\t、\n和/

聚簇表参数：

  • clustered by | range clustered by (<col_name> [, <col_name>, ...]) [sorted by (<col_name> [asc | desc] [, <col_name> [asc | desc] ...])] into <number_of_buckets> buckets：可选。用于创建聚簇表时设置表的Shuffle和Sort属性

外部表参数：

  • stored by StorageHandler：可选。按照外部表数据格式指定StorageHandler。
  • with serdeproperties (options)：可选。外部表的授权、压缩、字符解析等相关参数。
  • osslocation：可选。外部表数据OSS存储位置
实例：基于已有数据/表新建表

通过create table [if not exists] <table_name> [lifecycle <days>] as <select_statement>;语句可以再创建一个表，并在建表的同时将数据复制到新表中

  • 注意，通过该语句创建的表不会复制分区属性，只会把源表的分区列作为目标表的一般列处理，也不会复制源表本身的生命周期属性
  • 可以通过lifecycle参数回收表，同时也支持创建内部表复制外部表的数据

通过create table [if not exists] <table_name> like <existing_table_name> [lifecycle <days>];语句可以再创建一个表，使目标表和源表具有相同的表结构。

  • 通过该语句创建的表不复制数据，也不会复制源表的生命周期属性
  • 可以通过lifecycle参数回收表，同时也支持创建内部表复制外部表的结构

例1:创建非分区表test1

create table test1 (key STRING);

例2:创建分区表sale_detail

create table if not exists sale_detail(
 shop_name     STRING,
 customer_id   STRING,
 total_price   DOUBLE)
partitioned by (sale_date STRING, region STRING);

例3:创建一个新表，将sale_detail的数据复制到新表中，并设置生命周期

SET odps.sql.allow.fullscan=true;
create table sale_detail_ctas1 lifecycle 10 as select * from sale_detail;
1.1.2 删除表

命令格式：

DROP TABLE [IF EXISITS] <table_name>;
  • IF EXISTS：非必填项，如果不指定IF EXISTS且表不存在则返回异常，如果指定IF EXISTS，无论表是否存在均返回成功
  • table_name：待删除的表名
--删除表sale_detail。无论sale_detail表是否存在，均返回成功。
DROP TABLE IF EXISTS sale_detail;
1.1.3 修改表
1.1.3.1 修改表的所有人

修改表的所有者，即Owner

注意：仅项目所有者（Project Owner）或具备Super_Administrator角色的用户可执行修改表Owner的命令

命令格式：

ALTER TABLE <table_name> CHANGEOWNER TO <new_owner>;
  • table_name：必填，待修改Owner的表名
  • new_owner：必填，修改后的Owner账号。

例如：

--将表sale_detail的所有人修改为ALIYUN$xxx@aliyun.com
ALTER TABLE sale_detail CHANGEOWNER TO 'ALIYUN$xxx@aliyun.com';
--将表sale_detail的所有人修改为名称为ram_test的RAM用户
ALTER TABLE sale_detail CHANGEOWNER TO 'RAM$13xxxxxxxxxxx:ram_test';
1.1.3.2 修改表的注释

修改表的注释内容

ALTER TABLE <table_name> SET COMMENT '<new_comment>';
  • table_name：必填，待修改注释的表的名称
  • new_comment：必填，修改后的注释名称
ALTER TABLE sale_detail SET COMMENT 'new comment for table sale_detail';

可以通过MaxCompute的DESC<tabel_name>命令查看表中的comment的修改结果

+------------------------------------------------------------------------------------+
| Owner:                    ALIYUN$sant****.aliyunid.com                  |
| Project:                  ani****                                               |
| Schema:                   default                                                  |
| TableComment: new comment for table sale_detail                                    |
+------------------------------------------------------------------------------------+
1.1.3.3 修改表的最后更新时间

MaxCompute SQL提供TOUCH操作用来修改表的LastModifiedTime，可将表的LastModifiedTime修改为当前时间。此操作会改变表的LastModifiedTime的值，MaxCompute会认为表的数据有变动，生命周期的计算会重新开始

ALTER TABLE <table_name> TOUCH;
  • table_name：必填，待修改时间的表的名称
ALTER TABLE sale_detail TOUCH;
1.1.3.4 修改表的聚簇属性

对于分区表，MaxCompute支持通过ALTER TABLE语句增加或去除聚簇属性

  • 增加表的Hash聚簇属性：
ALTER TABLE <table_name> 
[CLUSTERED BY (<col_name> [, <col_name>, ...]) 
[SORTED BY (<col_name> [ASC | DESC] [, <col_name> [ASC | DESC] ...])] 
INTO <number_of_buckets> BUCKETS];
  • 去除表的Hash聚簇属性：
ALTER TABLE <table_name> NOT CLUSTERED;
  • 增加表的Range聚簇属性，Bucket数是不必需的，可以省略，这时系统会根据数据量自动决定最佳的Bucket数目：
ALTER TABLE <table_name> 
[RANGE CLUSTERED BY (<col_name> [, <col_name>, ...]) 
[SORTED BY (<col_name> [ASC | DESC] [, <col_name> [ASC | DESC] ...])] 
INTO <number_of_buckets> BUCKETS];
  • 去除表或分区的Range聚簇属性：
ALTER TABLE <table_name> NOT CLUSTERED;
ALTER TABLE <table_name> <pt_spec> NOT CLUSTERED;

通过ALTER TABLE改变聚簇属性只对分区表有效，非分区表一旦建立聚簇属性就无法改变；ALTER TABLE语句适用于存量表，再增加了新的聚簇属性后，新的分区将按设置的聚簇属性存储。

ALTER TABLE只会影响分区表的新建分区（包括INSERT OVERWRITE生成的），新分区将按新的聚簇属性存储，老数据分区的聚簇属性和存储保持不变。即在一张曾经做过聚簇属性设置的表上，关闭了聚簇属性，再增加聚簇设置，可以在新分区设置不同于之前的聚簇列、排序列及分桶数。

由于ALTER TABLE只影响新分区，所以该语句不可以再指定分区。

1.1.3.5 修改表名

仅修改表的名字，不改动表中的数据

ALTER TABLE <table_name> RENAME TO <new_table_name>;
  • table_name：必填，待修改名称的表
  • new_table_name：必填，修改后的表的名称，如果已存在与new_table_name同名的表，会返回报错
ALTER TABLE sale_detail RENAME TO sale_detail_rename;
1.1.3.6 清空非分区表里的数据

将指定的非分区表中的数据清空。

TRUNCATE TABLE <table_name>;
1.1.3.7 清空列数据

使用clear column命令清空普通表的列，将不再使用的列数据从磁盘删除并重置NULL，从而达到降低存储成本的目的。

ALTER TABLE <table_name> [partition ( <pt_spec>[, <pt_spec>....] )] 
  CLEAR COLUMN column1[, column2, column3, ...] 
  [without touch];
  • table_name：必填，将要执行清空列数据的表名称
  • column：将要被清空数据的列名称
  • partition：非必填，指定分区，若未指定，则表示操作所有分区
  • pt_spec：非必填，分区描述，格式为(partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...)
  • without touch：非必填，表示不更新LastDataModifiedTime；若未指定，则会更新
1.1.4 查看表
1.1.4.1 查看表信息

查看内部表、外部表、聚簇表或Transactional表的信息

--查看表信息
DESC <table_name> [PARTITION (<pt_spec>)]; 
--查看外部表、聚簇表或Transactional表信息。也可以查看内部表的扩展信息。
DESC EXTENDED <table_name>;
1.1.4.2 查看建表语句

生成创建表的SQL DDL语句，方便通过SQL重建Schema

SHOW CREATE TABLE <table_name>;

使用示例：

--查看表sale_detail的建表语句。
SHOW CREATE TABLE sale_detail;
--返回结果如下
CREATE TABLE IF NOT EXISTS max****.`default`.sale_detail(shop_name STRING, customer_id STRING, total_price DOUBLE) 
PARTITIONED BY (sale_date STRING, region STRING) STORED AS ALIORC TBLPROPERTIES ('columnar.nested.type'='true');
1.1.4.3 列出项目下的表和视图

列出项目下所有的表、外部表、视图和物化视图，或符合某些规则的表、外部表、视图和物化视图

--列出项目下所有的表和视图。
SHOW TABLES;
--列出项目下表名或视图名与chart匹配的表。
SHOW TABLES LIKE '<chart>';

使用示例

--列出项目下表名与sale*匹配的表。*表示任意字段。
SHOW TABLES LIKE 'sale*';              
--返回结果类似
ALIYUN$account_name:sale_detail
......
--ALIYUN是系统提示符，表示您是阿里云主账号用户。如果您是阿里云RAM用户，系统提示符为RAM。
1.1.4.4 列出项目下外部表

列出项目下所有的外部表，或符合某些规则的外部表

--列出项目下所有的外部表。
SHOW EXTERNAL TABLES;
--列出项目下名称与external_chart匹配的外部表。
SHOW EXTERNAL TABLES LIKE '<external_chart>';

使用示例：

--列出项目下名称与a*匹配的外部表。*表示任意字段。
SHOW EXTERNAL TABLES LIKE 'a*';   
--返回结果类似于
ALIYUN$account_name:a_et
......
--ALIYUN是系统提示符，表示您是阿里云主账号用户。如果您是阿里云RAM用户，系统提示符为RAM。
1.2 CLONE TABLE

CLONE TABLE支持高效的将源表数据复制到目标表中，适用于表数据迁移场景。

CLONE TABLE <[<src_project_name>.]<src_table_name>> [PARTITION(<pt_spec>), ...]
 TO <[<dest_project_name>.]<dest_table_name>> [IF EXISTS [OVERWRITE | IGNORE]] ;
  • src_project_name：非必填，源表所属的MaxCompute项目名称。不指定时，默认为当前项目。当源表与目标不属于同一项目时，需要携带此参数
  • src_table_name：必填，源表名称
  • pt_spec：非必填，源表的分区信息。格式为：(partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...)。其中partition_col是分区字段，partition_col_value是分区值。
  • dest_project_name：非必填，目标表所属项目名称。不指定时默认为当前项目。
  • dest_table_name：必填，目标表名称。

  • 当目标表不存在时，CLONE TABLE命令会创建目标表，创建目标表使用的是CREATE TABLE LIKE语义。

  • 当目标表已存在并指定IF EXISTS OVERWRITE时，CLONE TABLE命令会覆盖目标表或对应分区的数据。

  • 当目标表已存在并指定IF EXISTS IGNORE时，CLONE TABLE命令会跳过已存在分区，不会覆盖目标表已有分区的数据

1.2.1 分区表
--创建一张分区表sale_detail。
CREATE TABLE IF NOT EXISTS sale_detail
(
shop_name     string,
customer_id   string,
total_price   double
)
PARTITIONED BY (sale_date string, region string);

--向源表增加分区。
ALTER TABLE sale_detail ADD PARTITION (sale_date='2013', region='china') PARTITION (sale_date='2014', region='shanghai');

--向源表追加数据。
INSERT INTO sale_detail PARTITION (sale_date='2013', region='china') VALUES ('s1','c1',100.1),('s2','c2',100.2),('s3','c3',100.3);
INSERT INTO sale_detail PARTITION (sale_date='2014', region='shanghai') VALUES ('null','c5',null),('s6','c6',100.4),('s7','c7',100.5);
--开启全表扫描，仅此Session有效。执行select语句查看表sale_detail中的数据。
SET odps.sql.allow.fullscan=true; 
SELECT * FROM sale_detail;
--返回结果。
+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s3         | c3          | 100.3       | 2013       | china      |
| null       | c5          | NULL        | 2014       | shanghai   |
| s6         | c6          | 100.4       | 2014       | shanghai   |
| s7         | c7          | 100.5       | 2014       | shanghai   |
+------------+-------------+-------------+------------+------------+
1.2.2 非分区表
--创建一张非分区表sale_detail_np。
CREATE TABLE IF NOT EXISTS sale_detail_np
(
shop_name     string,
customer_id   string,
total_price   double
);

--向源表追加数据。
INSERT INTO sale_detail_np VALUES ('s4','c4',100.4);
SELECT * FROM sale_detail_np;
--返回结果。
+------------+-------------+-------------+
| shop_name  | customer_id | total_price |
+------------+-------------+-------------+
| s4         | c4          | 100.4       |
+------------+-------------+-------------+
1.3 分区操作
1.3.1 添加分区

为已存在的分区表新增分区

对于有多级分区的表，如果需要添加新的分区值，必需指名全部的分区

仅支持新增分区值，不支持新增分区字段

ALTER TABLE <table_name> ADD [IF NOT EXISTS] PARTITION <pt_spec> [PARTITION <pt_spec> PARTITION <pt_spec>...];

使用示例：

ALTER TABLE sale_detail ADD IF NOT EXISTS PARTITION (sale_date='20111011');
--创建delta table表
CREATE TABLE mf_tt (pk BIGINT NOT NULL PRIMARY KEY, 
                    val BIGINT NOT NULL) 
             PARTITIONED BY (dd STRING, hh STRING) 
            TBLPROPERTIES ("transactional"="true"); 
             
--添加分区           
ALTER TABLE mf_tt ADD PARTITION (dd='01', hh='01');
1.3.2 修改分区的更新时间

MaxCompute SQL提供touch操作，用于修改分区表中分区的LastModifiedTime。此操作会将LastModiifiedTime修改为当前时间。此时MaxCompute会认为数据有变动，重新计算生命周期

ALTER TABLE <table_name> touch PARTITION (<pt_spec>);

使用示例：

--修改表sale_detail的分区sale_date='201312', region='shanghai'的LastModifiedTime。
ALTER TABLE sale_detail touch PARTITION (sale_date='201312', region='shanghai');
1.3.3 修改分区值

MaxCompute SQL支持通过rename操作更改分区表的分区值

ALTER TABLE <table_name> PARTITION (<pt_spec>) rename TO PARTITION (<new_pt_spec>);

使用示例：

--修改表sale_detail的分区值。
ALTER TABLE sale_detail PARTITION (sale_date = '201312', region = 'hangzhou') rename TO PARTITION (sale_date = '201310', region = 'beijing');
1.3.4 合并分区

MaxCompute SQL提供merge partition对分区表的分区进行合并，即将同一个分区表下的多个分区合并成一个分区，同时删除被合并的分区维度的信息，把数据移动到指定分区

ALTER TABLE <table_name> MERGE [IF EXISTS] PARTITION (<predicate>) [, PARTITION(<predicate2>) ...] overwrite PARTITION (<fullpartitionSpec>) [purge];

使用示例：

--查看分区表的分区。
SHOW partitions intpstringstringstring;

ds=20181101/hh=00/mm=00
ds=20181101/hh=00/mm=10
ds=20181101/hh=10/mm=00
ds=20181101/hh=10/mm=10

--合并所有满足hh='00'的分区到hh='00'，mm='00'中。
ALTER TABLE intpstringstringstring MERGE PARTITION(hh='00') overwrite PARTITION(ds='20181101', hh='00', mm='00');

--查看合并后的分区。
SHOW partitions intpstringstringstring;

ds=20181101/hh=00/mm=00
ds=20181101/hh=10/mm=00
ds=20181101/hh=10/mm=10                    
--合并多个指定分区。
ALTER TABLE intpstringstringstring MERGE IF EXISTS PARTITION(ds='20181101', hh='00', mm='00'), PARTITION(ds='20181101', hh='10', mm='00'),  partition(ds='20181101', hh='10', mm='10') overwrite partition(ds='20181101', hh='00', mm='00') purge;
--查看分区表的分区。
SHOW partitions intpstringstringstring;

ds=20181101/hh=00/mm=00
1.3.5 列出所有分区

列出一张表中的所有分区。当表不存在或为非分区表时，返回报错。

SHOW PARTITIONS <table_name>;

使用示例：

-- 列出sale_detail中的所有分区。
SHOW PARTITIONS sale_detail;
--返回结果
sale_date=2023/region=china
sale_date=2024/region=beijing
1.3.6 查看分区信息
DESC <table_name> PARTITION (<pt_spec>);

使用示例：

--查询分区表sale_detail的分区信息。
DESC sale_detail PARTITION (sale_date='201312',region='hangzhou');
--返回结果
+------------------------------------------------------------------------------------+
| PartitionSize: 1234                                                                |
+------------------------------------------------------------------------------------+
| CreateTime:               2024-11-14 16:43:22                                      |
| LastDDLTime:              2024-11-14 16:45:37                                      |
| LastModifiedTime:         2024-11-14 16:45:37                                      |
+------------------------------------------------------------------------------------+
OK
1.3.7 删除分区

MaxCompute支持通过条件筛选方式删除分区。如果您希望一次性删除符合某个规则条件的多个分区，可以使用表达式指定筛选条件，通过筛选条件匹配分区并批量删除分区。

  • 限制条件

    • 每个分区过滤子句只能访问一个分区列。

    • 表达式用到的函数必须是内建的Scalar函数。

  • 注意事项

    • 删除分区之后，MaxCompute项目的存储量会降低

  • 未指定筛选条件
--一次删除一个分区。
ALTER TABLE <table_name> DROP [IF EXISTS] PARTITION <pt_spec>;
--一次删除多个分区。
ALTER TABLE <table_name> DROP [IF EXISTS] PARTITION <pt_spec>,PARTITION <pt_spec>[,PARTITION <pt_spec>....];
  • 指定筛选条件
ALTER TABLE <table_name> DROP [IF EXISTS] PARTITION <partition_filtercondition>;

partition_filtercondition说明：

指定筛选条件时必填。分区筛选条件，不区分大小写。格式如下：

partition_filtercondition
    : PARTITION (<partition_col> <relational_operators> <partition_col_value>)
    | PARTITION (scalar(<partition_col>) <relational_operators> <partition_col_value>)
    | PARTITION (<partition_filtercondition1> AND|OR <partition_filtercondition2>)
    | PARTITION (NOT <partition_filtercondition>)
    | PARTITION (<partition_filtercondition1>)[,PARTITION (<partition_filtercondition2>), ...]

介绍如下：

  • partition_col：分区名称。

  • relational_operators：关系运算符

  • partition_col_value：分区列比较值或正则表达式，与分区列数据类型保持一致。

  • scalar()：Scalar函数。Scalar函数基于输入值生成对应的标量，对分区列的值（partition_col）进行处理后再按照指定的关系运算符relational_operators与partition_col_value做比较。

  • 分区过滤条件支持逻辑运算符NOT、AND和OR。支持通过NOT过滤条件子句，取过滤规则的补集。支持多个过滤条件子句以AND或OR的关系组成整体分区匹配规则。

  • 支持多个分区过滤子句，当多个分区过滤子句以英文逗号（,）分隔时，每个过滤子句的逻辑以OR的关系组成整体分区匹配规则

1.4 列操作
1.4.1 添加列或注释
ALTER TABLE <table_name> 
      ADD columns [if NOT EXISTS]
          (<col_name1> <type1> comment ['<col_comment>']
           [, <col_name2> <type2> comment '<col_comment>'...]
          );
  • table_name：必填，待新增列的表名称，添加的新列不支持指定顺序，默认在最后一列
  • col_name：必填，新增列的名称
  • type：必填，新增列的数据名称
  • col_comment：非必填，新增列的注释

使用示例：

--为表sale_detail新增两列
ALTER TABLE sale_detail ADD columns if NOT EXISTS(customer_name STRING, education BIGINT);
--为表添加一个复杂数据类型列
ALTER TABLE sale_detail ADD columns (region struct<province:string, area:string>);
1.4.2 删除列
ALTER TABLE <table_name> DROP columns <col_name1>[, <col_name2>...];

使用示例：

--删除表sale_detail的列customer_id。输入yes确认后，即可删除列。
ALTER TABLE sale_detail DROP columns customer_id;

--删除表sale_detail的列shop_name和customer_id。输入yes确认后，即可删除列。
ALTER TABLE sale_detail DROP columns shop_name, customer_id;
1.4.3 更改列数据类型
ALTER TABLE <table_name> change [COLUMN] <old_column_name> <new_column_name> <new_data_type>;

使用示例：

--将mf_evol_t3表的id字段由int转化为bigint
ALTER TABLE mf_evol_t3 change id id bigint;
--将mf_evol_t3表的id字段类型由bigint转化为string
ALTER TABLE mf_evol_t3 change COLUMN id id string;
1.4.4 修改列的顺序
ALTER TABLE <table_name> change <old_column_name> <new_column_name> <column_type> after <column_name>;

使用示例：

--修改表sale_detail的列customer_id为customer并位于total_price之后。
ALTER TABLE sale_detail change customer_id customer string after total_price;
--修改表sale_detail的列customer_id位于total_price之后，不修改列名称。
ALTER TABLE sale_detail change customer_id customer_id string after total_price;
1.4.5 修改列名
ALTER TABLE <table_name> change COLUMN <old_col_name> rename TO <new_col_name>;
  • table_name：必填，待修改列名的表名称
  • old_col_name：待修改的列名称，必须是已存在的列
  • new_col_name：修改后的列名称，列名称不能重复
--修改表sale_detail的列名customer_name为customer。
ALTER TABLE sale_detail change COLUMN customer_name rename TO customer;
1.4.6 修改表的列非空属性

修改表的非分区列的非空属性。即如果表的非分区列禁止NULL，可以通过此命令修改分区列值允许为NULL

可以通过desc extended table_name;命令查看Nullable属性值，判断列的非空属性。如果Nullable为true，表示允许为NULL；如果Nullable为false，表示禁止为NULL

ALTER TABLE <table_name> change COLUMN <old_col_name> NULL;

使用示例：

--创建一张分区表，id列禁止为NULL。
CREATE TABLE null_test(id int NOT NULL, name string) partitioned BY (ds string);
--修改id列允许为NULL。
ALTER TABLE null_test change COLUMN id NULL;
1.5 生命周期操作

生命周期（Lifecycle），指表（分区）数据从最后一次更新的时间算起，在经过指定的时间后没有变动，则此表将被自动回收，这个指定的时间就是生命周期。生命周期回收为每天定时启动，扫描全量分区。

  • 对于非分区表，当LastModifiedTime超过设定的生命周期时长时，MaxConpute回回收这些数据并删除对应的表
  • 对于分区表，根据各分区的LastModifiedTime判断该分区数据是否该被回收，当最后一个分区被回收后，该表不会被删除。如果需要在最后一个分区被回收后自动删除该表，可以通过两种方法设置：

项目级别：

--最后一个分区被回收后自动删除该表
setproject odps.table.lifecycle.deletemeta.on.expiration=true;

--最后一个分区被回收后默认保留该表
setproject odps.table.lifecycle.deletemeta.on.expiration=false;

表级别：

---创建新表：设置最后一个分区被回收后自动删除该表
CREATE TABLE <Table_Name>(id int, name string)
     partitioned BY (ds string)
     tblproperties ('lifecycle.deletemeta'='true')
     lifecycle  1;
---已有表：设置最后一个分区被回收后自动删除该表
ALTER TABLE <table_name> SET tblproperties('lifecycle.deletemeta'='true');

--最后一个分区被回收后默认保留该表
ALTER TABLE <table_name> SET tblproperties('lifecycle.deletemeta'='false');

注意：

任务调度依赖不会影响表数据生命周期的到期回收。

为分区表设置生命周期时，该生命周期设置将应用于整个表，包括所有的分区，不支持在分区级别单独设置或修改生命周期时长。

数据被回收后，默认在回收站保留一天，超过一天，数据将被永久清理，清理后将无法恢复。

生命周期主要提供定期回收表或分区的功能，每天根据服务的繁忙程度，不定时回收。不能确保表或分区的生命周期到期后，立刻被回收。

在进行数据写入、更新和删除操作时，会更新LastModifiedTime；而修改生命周期时，不会影响LastModifiedTime。

1.5.1 设置生命周期
1.5.1.1 创建表
--创建新表。
 CREATE [external] TABLE [if not exists] <table_name>
 [(<col_name> <data_type> [default <default_value>] [comment <col_comment>], ...)]
 [comment <table_comment>]
 [partitioned BY (<col_name> <data_type> [comment <col_comment>], ...)]
 --用于创建聚簇表时设置表的Shuffle和Sort属性。
 [clustered BY | range clustered BY (<col_name> [, <col_name>, ...]) [sorted BY (<col_name> [ASC | DESC] [, <col_name> [ASC | DESC] ...])] INTO <number_of_buckets> buckets] 
 --仅限外部表。
 [stored by StorageHandler] 
 --仅限外部表。
 [with serdeproperties (options)] 
 --仅限外部表。
 [location <osslocation>] 
 --生命周期
 lifecycle <days>;

--基于已存在的表创建具备相同结构的新表但不复制数据，支持外部表和湖仓一体外部项目中的表。
create table [if not exists] <table_name> like <existing_table_name> [lifecycle <days>];
  • table_name：必填，需要设置生命周期的表名称
  • days：必填修改后的生命周期时间，只能为正整数，单位为天

使用示例：

--新建test_lifecycle表，生命周期为100天。
CREATE TABLE test_lifecycle (key string) lifecycle 100;
1.5.1.2 已有表
ALTER TABLE <Table_Name> SET lifecycle <days>;

使用示例：

--修改test_lifecycle表，将生命周期设为50天。
ALTER TABLE test_lifecycle set lifecycle 50;
1.5.2 禁止或恢复生命周期
ALTER TABLE <table_name> [<pt_spec>] {enable|disable} lifecycle;

使用示例：

--禁止表trans生命周期功能
ALTER TABLE trans disable lifecycle;
--禁止表trans中时间为20121111分区的生命周期功能
ALTER TABLE trans partition (dt='20141111') disable lifecycle;
1.6 视图操作

视图（View）是一种在表层级上创建的虚拟表，他甚至可以基于一个或多个表而存在。通过使用视图，可以保留查询逻辑（SQL语句）而无需创建市级的表占用存储空间。

1.6.1 创建或更新视图
CREATE [OR REPLACE] VIEW [IF NOT EXISTS] <view_name>
    [(<col_name> [comment <col_comment>], ...)]
    [comment <view_comment>]
    AS <select_statement>;
  • OR REPLACE：非必填，更新视图需要携带该字段
  • IFNOT EXISTS：非必填，忽略视图重复时报错
  • view_name：必填，待创建或更新的视图的名称
  • col_name：必填，待创建视图包含的列名称
  • col_comment：非必填，待创建视图的列的注释
  • view_comment：非必填，待创建视图的注释
  • select_statement：必填，select查询语句，是视图的数据来源，要求必须拥有视图所引用表的读取权限。视图只能包含一个有效的select语句

使用示例：

--基于sale_detail创建视图sale_detail_view
CREATE VIEW IF NOT EXISTS sale_detail_view 
(store_name, customer_id, price, sale_date, region) 
comment 'a view for table sale_detail' 
AS SELECT * FROM sale_detail;
--基于表sale_detail更新视图sale_detail_view
CREATE OR REPLACE VIEW IF NOT EXISTS sale_detail_view 
(store_name, customer_id, price)
comment 'a view for table sale_detail'
AS SELECT shop_name, customer_id, total_price FROM sale_detail;
1.6.2 重命名视图
ALTER VIEW <view_name> RENAME TO <new_view_name>;

使用示例：

--将视图view sale_detail_view重命名为market。
ALTER VIEW sale_detail_view RENAME TO market;
1.6.3 查看视图
DESC <view_name>; 
--使用示例
DESC sale_detail_view;
1.6.4 删除视图
DROP VIEW [IF EXISTS] <view_name>;
--删除视图sale_detail_view。
DROP VIEW IF EXISTS sale_detail_view;

二、DML语句

2.1 插入或覆写数据（INSERT INTO｜INSERT OVERWRITE）

前提条件：执行insert into和insert overwrite操作前需要具备目标表的更新权限（Update）及源表的元信息读取权限（Select）

在使用MaxCompute SQL处理数据时，insert into或insert overwrite操作可以将select查询的结果保存至目标表中。二者的区别是：

  • insert into：直接向表或静态分区中插入数据。您可以在insert语句中直接指定分区值，将数据插入指定的分区。如果您需要插入少量测试数据，可以配合VALUES
    ​​​​​​​使用。
  • insert overwrite：先清空表或静态分区中的原有数据，再向表或静态分区中插入数据
2.1.1 使用限制

执行insert into和insert overwrite操作更新表或静态分区数据的使用限制如下：

  • insert into：不支持向聚簇表中追加数据。
  • insert overwrite：不支持指定插入列，只能使用insert into。例如create table t(a string, b string); insert into t(a) values ('1');，a列插入1，b列为NULL或默认值。
  • MaxCompute对正在操作的表没有锁机制，不要同时对一个表执行insert into或insert overwrite操作。

对于Delta Table类型的表有如下限制。

  • Delta Table表用Insert Overwrite写入数据时，相同PK值的多行记录在写入表之前会先去重，只选择第一行写入，最终写入的结果依赖于计算过程的记录顺序，无法手动指定。由于该操作写入的是全量数据，因此默认去重也是尽可能保证PK唯一性的属性。
  • Delta Table表用Insert Into写入数据时，相同PK值的多行默认不去重，都会写入表中，但如果设置Flag( odps.sql.insert.acidtable.deduplicate.enable)的值为true，则会去重后再写入表中。
2.1.2 命令格式
insert {into|overwrite} table <table_name> [partition (<pt_spec>)] [(<col_name> [,<col_name> ...)]]
<select_statement>
from <from_statement>
[zorder by <zcol_name> [, <zcol_name> ...]];
  • from_statement：必填，from子句，表示数据来源，例如源表名称
  • zorder by <zcol_name> [, <zcol_name> ...]：可选。向表或分区写入数据时，支持根据指定的一列或多列（select_statement对应表中的列），把排序列数据相近的行排列在一起，提升查询时的过滤性能，在一定程度上降低存储成本。需要注意的是，order by x, y会严格地按照先x后y的顺序对数据进行排序，zorder by x, y会把相近的<x, y>尽量排列在一起。当SQL查询语句的过滤条件中包含排序列时，order by后的数据仅对包含x的表达式有较好的过滤效果，zorder by后的数据对包含x或同时包含x、y的表达式均有较好的过滤效果，列压缩比例更高
  • zorder by有两种模式，默认模式为local zorder。local模式只是单个文件内部按照zorder排序，并不是对全局数据做一个重分布，所以如果数据分散在各个文件，那么数据的聚集程度可能也不高，无法做到最有效的Data Skipping。鉴于该问题，在新版本中支持了global zorder
  • sort by语句用于指定单个文件内部排序的方式，如果不写sort by，则单个文件内部按照local zorder排序

zorder by的使用限制如下：

对于分区表，一次只允许对1个分区进行zorder by排序。

zorder by字段数目只能在2~4之间。

目标表为聚簇表时，不支持zorder by子句。

zorder by可以与distribute by一起使用，不能与order by、cluster by或sort by一起使用。

2.1.3 示例：普通表
--执行insert into命令向非分区表websistes中追加数据
--创建一张非分区表websites。
create table if not exists websites
(id int,
 name string,
 url string
);
--创建一张非分区表apps
create table if not exists apps
(id int,
 app_name string,
 url string
);
--向表apps追加数据。其中：insert into table table_name可以简写为insert into table_name
insert into apps (id,app_name,url) values 
(1,'Aliyun','https://www.aliyun.com');
--复制apps的表数据追加至websites表
insert into websites (id,name,url) select id,app_name,url
from  apps;
--执行select语句查看表websites中的数据。
select * from websites;
--返回结果。
+------------+------------+------------+
| id         | name       | url        |
+------------+------------+------------+
| 1          | Aliyun     | https://www.aliyun.com |
+------------+------------+------------+
--执行insert overwrite命令向sale_detail_insert中覆写数据
--创建目标表sale_detail_insert，与sale_detail有相同的结构。
create table sale_detail_insert like sale_detail;

--给目标表增加分区。非必需操作，如果不提前创建，写入时会自动创建该分区。
alter table sale_detail_insert add partition (sale_date='2013', region='china');

--从源表sale_detail中取出数据插入目标表sale_detail_insert。注意不需要声明目标表字段，也不支持重排目标表字段顺序。
--对于静态分区目标表，分区字段赋值已经在partition()部分声明，不需要在select_statement中包含，只要按照目标表普通列顺序查出对应字段，按顺序映射到目标表即可。动态分区表则需要在select中包含分区字段，详情请参见插入或覆写动态分区数据（DYNAMIC PARTITION）。
insert overwrite table sale_detail_insert partition (sale_date='2013', region='china')
  select 
  shop_name, 
  customer_id,
  total_price 
  from sale_detail
  zorder by customer_id, total_price;

--开启全表扫描，仅此Session有效。执行select语句查看表sale_detail_insert中的数据。
set odps.sql.allow.fullscan=true;
select * from sale_detail_insert;

--返回结果。
+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s3         | c3          | 100.3       | 2013       | china      |
+------------+-------------+-------------+------------+------------+
2.1.4 示例：Delta Table类型表
--创建Delta Table表mf_dt，并执行insert命令插入并复写数据
--创建Delta Table表mf_dt。
create table if not exists mf_dt (pk bigint not null primary key, 
                  val bigint not null) 
                  partitioned by (dd string, hh string) 
                  tblproperties ("transactional"="true");
                  
--向mf_dt表dd='01'和hh='01'的分区中插入测试数据。
insert overwrite table mf_dt partition (dd='01', hh='01') 
                 values (1, 1), (2, 2), (3, 3);

--查询mf_dt表目标分区中的数据                
select * from mf_dt where dd='01' and hh='01';
--返回结果
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 01 |
| 3          | 3          | 01 | 01 |
| 2          | 2          | 01 | 01 |
+------------+------------+----+----+

--使用insert into向mf_dt表目标分区中追加数据。
insert into table mf_dt partition(dd='01', hh='01') 
            values (3, 30), (4, 4), (5, 5);
            
select * from mf_dt where dd='01' and hh='01';
--返回结果
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 01 |
| 3          | 30         | 01 | 01 |
| 4          | 4          | 01 | 01 |
| 5          | 5          | 01 | 01 |
| 2          | 2          | 01 | 01 |
+------------+------------+----+----+

--使用insert overwrite向mf_dt表目标分区的覆盖写入数据。
insert overwrite table mf_dt partition (dd='01', hh='01') 
                 values (1, 1), (2, 2), (3, 3);
select * from mf_dt where dd='01' and hh='02';
--返回结果。
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 01 |
| 3          | 3          | 01 | 01 |
| 2          | 2          | 01 | 01 |
+------------+------------+----+----+

--使用insert into向mf_dt表dd='01'和hh='02'的分区写入数据。
insert overwrite table mf_dt partition (dd='01', hh='02') 
                 values (1, 11), (2, 22), (3, 32);
select * from mf_dt where dd='01' and hh='02';
--返回结果。
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 11         | 01 | 02 |
| 3          | 32         | 01 | 02 |
| 2          | 22         | 01 | 02 |
+------------+------------+----+----+

--开启全表扫描，仅此Session有效。执行select语句查看表mf_dt中的数据。
set odps.sql.allow.fullscan=true;
select * from mf_dt;
--返回结果。
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 11         | 01 | 02 |
| 3          | 32         | 01 | 02 |
| 2          | 22         | 01 | 02 |
| 1          | 1          | 01 | 01 |
| 3          | 3          | 01 | 01 |
| 2          | 2          | 01 | 01 |
+------------+------------+----+----+
2.2 插入或覆写动态分区数据（DYNAMIC PARTITION）
2.2.1 使用限制

如果您需要更新表数据到动态分区，需要注意：

  • INSERT INTO PARTITION时，如果分区不存在，会自动创建分区。
  • 多个INSERT INTO PARTITION作业并发时，如果分区不存在，优先执行成功的作业会自动创建分区，但只会成功创建一个分区。
  • 如果不能控制INSERT INTO PARTITION作业并发，建议您通过ALTER TABLE命令提前创建分区，详情请参见分区操作。
  • 如果目标表有多级分区，在执行INSERT操作时，允许指定部分分区为静态分区，但是静态分区必须是高级分区。
  • 向动态分区插入数据时，动态分区列必须在SELECT列表中，否则会执行失败。
2.2.2 命令格式
INSERT {INTO|OVERWRITE} TABLE <table_name> PARTITION (<ptcol_name>[, <ptcol_name> ...]) 
<select_statement> FROM <from_statement>;
  • table_name：必填，需要插入数据的目标表名
  • ptocol_name：必填，目标表分区列的名称
  • select_statement：必填，select子句，从源表中查询需要插入目标表的数据
  • from_statement：必填，FROM子句，表示数据来源
2.2.3 示例数据
--创建一张分区表sale_detail。
CREATE TABLE IF NOT EXISTS sale_detail
(
shop_name     STRING,
customer_id   STRING,
total_price   DOUBLE
)
PARTITIONED BY (sale_date STRING, region STRING);

--向源表增加分区。非必需操作，如果不提前创建，写入时会自动创建该分区。
ALTER TABLE sale_detail ADD PARTITION (sale_date='2013', region='china');

--向源表追加数据。其中：insert into table table_name可以简写为insert into table_name，但insert overwrite table table_name不可以省略table关键字。
INSERT INTO sale_detail PARTITION (sale_date='2013', region='china') VALUES ('s1','c1',100.1),('s2','c2',100.2),('s3','c3',100.3);

--开启全表扫描，仅此Session有效。执行select语句查看表sale_detail中的数据。
SET odps.sql.allow.fullscan=true; 
SELECT * FROM sale_detail;

--返回结果。
+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s3         | c3          | 100.3       | 2013       | china      |
+------------+-------------+-------------+------------+------------+
2.2.4 使用示例

表数据来源于上面的示例数据

--将原表数据插入到目标表中。在运行SQL语句之前，无法得知会产生哪些分区。只有在语句运行结束后，才能通过region字段产生的值确定产生的分区
--创建目标表total_revenues。
CREATE TABLE total_revenues (revenue DOUBLE) PARTITIONED BY (region string);

--将源表sale_detail中的数据插入到目标表total_revenues。
SET odps.sql.allow.fullscan=true; 
INSERT OVERWRITE TABLE total_revenues PARTITION(region) SELECT total_price AS revenue, region FROM sale_detail;

--执行SHOW PARTITIONS语句查看表total_revenues的分区。
SHOW PARTITIONS total_revenues;
   
--返回结果。
region=china  

--开启全表扫描，仅此Session有效。执行SELECT语句查看表total_revenues中的数据。  
SET odps.sql.allow.fullscan=true; 
SELECT * FROM total_revenues;    

--返回结果。
+------------+------------+
| revenue    | region     |
+------------+------------+
| 100.1      | china      |
| 100.2      | china      |
| 100.3      | china      |
+------------+------------+        
--在动态分区表中，select_statement字段和目标表动态分区的对应关系是由字段顺序决定，并不是由列名称决定的
--将源表sale_detail中的数据插入到目标表sale_detail_dypart。
SET odps.sql.allow.fullscan=true; 
INSERT OVERWRITE TABLE sale_detail_dypart PARTITION (sale_date, region)
SELECT shop_name,customer_id,total_price,sale_date,region FROM sale_detail;

--开启全表扫描，仅此Session有效。执行select语句查看表sale_detail_dypart中的数据。
SET odps.sql.allow.fullscan=true; 
SELECT * FROM sale_detail_dypart;

--返回结果。决定目标表sale_detail_dypart动态分区的字段sale_date为源表sale_detail的字段sale_date；决定目标表sale_detail_dypart动态分区的字段region为源表sale_detail的字段region。
+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s3         | c3          | 100.3       | 2013       | china      |
+------------+-------------+-------------+------------+------------+


--将源表sale_detail中的数据插入到目标表sale_detail_dypart，调整select字段顺序。
SET odps.sql.allow.fullscan=true; 
INSERT OVERWRITE TABLE sale_detail_dypart PARTITION (sale_date, region)
SELECT shop_name,customer_id,total_price,region,sale_date FROM sale_detail;

--开启全表扫描，仅此Session有效。执行select语句查看表sale_detail_dypart中的数据。
SET odps.sql.allow.fullscan=true; 
SELECT * FROM sale_detail_dypart;

--返回结果。决定目标表sale_detail_dypart动态分区的字段sale_date为源表sale_detail的字段region；决定目标表sale_detail_dypart动态分区的字段region为源表sale_detail的字段sale_date。
+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | china      | 2013       |
| s2         | c2          | 100.2       | china      | 2013       |
| s3         | c3          | 100.3       | china      | 2013       |
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s3         | c3          | 100.3       | 2013       | china      |
+------------+-------------+-------------+------------+------------+
2.3 更新或删除数据（UPDATE｜DELETE）

MaxCompute的DELETE、UPDATE功能具备与传统数据库用法类似的删除或更新表中指定行的能力。

实际使用DELETE、UPDATE功能时，系统会针对每一次删除或更新操作自动生成用户不可见的Delta文件，用于记录删除或更新的数据信息：

  • DELETE：Delta文件中使用txnid(bigint)和rowid(bigint)字段标识Transactional表的Base文件（表在系统中的存储形式）中的记录在哪次删除操作中被删除。
  • 例如，表t1的Base文件为f1，且内容为a, b, c, a, b，当执行DELETE FROM t1 WHERE c1='a';命令后，系统会生成一个单独的f1.delta文件。假设txnid是t0，则f1.delta的内容是((0, t0), (3, t0))，标识行0和行3，在txnt0中被删除了。如果再执行一次DELETE操作，系统会又生成一个f2.delta文件，该文件仍然是根据Base文件f1编号，读取文件时，基于Base文件f1和当前所有Delta文件的共同表示结果，读取没有被删除的数据。
  • UPDATE：UPDATE操作会转换为DELETE+INSERT INTO的实现逻辑。
2.3.1 DELETE

DELETE操作用于删除Transactional或Delta Table表中满足指定条件的单行或多行数据

DELETE FROM <table_name> [WHERE <where_condition>];

使用示例：

--创建非分区表acid_delete并导入数据，执行DELETE操作删除满足指定条件的行数据
--创建Transactional表acid_delete。
CREATE TABLE IF  NOT EXISTS acid_delete(id BIGINT) tblproperties ("transactional"="true"); 

--插入数据。
INSERT OVERWRITE TABLE acid_delete VALUES(1),(2),(3),(2); 

--查看插入结果。
SELECT * FROM acid_delete; 
--返回结果
+------------+
| id         |
+------------+
| 1          |
| 2          |
| 3          |
| 2          |
+------------+

--删除id为2的行，如果在MaxCompute客户端（odpscmd）执行，需要输入yes|no确认。
DELETE FROM acid_delete WHERE id = 2; 

--查看结果表中数据只有1、3。
SELECT * FROM acid_delete; 
--返回结果
+------------+
| id         |
+------------+
| 1          |
| 3          |
+------------+
--创建Delta Table表mf_dt并导入数据，执行DELETE操作删除满足指定条件的行
--创建目标Delta Table表mf_dt。
CREATE TABLE IF  NOT EXISTS mf_dt (pk BIGINT  NOT NULL PRIMARY KEY, 
                  val  BIGINT NOT NULL) 
                  PARTITIONED BY(dd STRING, hh STRING) 
                  tblproperties ("transactional"="true");

--插入数据
INSERT OVERWRITE TABLE mf_dt PARTITION (dd='01', hh='02') VALUES (1, 1), (2, 2), (3, 3);

--查看插入结果                 
SELECT * FROM mf_dt WHERE dd='01' AND hh='02';

--返回结果
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 02 |
| 3          | 3          | 01 | 02 |
| 2          | 2          | 01 | 02 |
+------------+------------+----+----+

--删除分区为01和02，且val为2的数据。
DELETE FROM mf_dt WHERE val = 2  AND dd='01' AND hh='02';

--查看结果表中只有val为1、3的数据
SELECT * FROM mf_dt WHERE dd='01' AND hh='02';

--返回结果
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 02 |
| 3          | 3          | 01 | 02 |
+------------+------------+----+----+
2.3.1.1 清空列数据

使用CLEAR COLUMN命令清空普通表的列，将不再使用的列数据从磁盘删除并置NULL，从而达到降低存储成本的目的

ALTER TABLE <table_name> 
           [PARTITION ( <pt_spec>[, <pt_spec>....] )] 
					 CLEAR COLUMN column1[, column2, column3, ...]
			                         [WITHOUT TOUCH];
  • column：将要被清空数据的列名称
  • PARTITION：指定分区，若未指定，则表示操作所有分区
  • pt_spec：分区描述
  • WITHOUT TOUCH：表示不更新LastDataModifiedTime，若未指定，则会更新LastDataModifiedTime
--不支持对具有非空属性的列进行clear column操作，可以手动取消not nullable属性
ALTER TABLE <table_name> change COLUMN <old_col_name> NULL;

使用示例：

--创建表
CREATE TABLE IF  NOT EXISTS mf_cc(key STRING, 
     value STRING, 
     a1 BIGINT , 
     a2 BIGINT , 
     a3 BIGINT , 
     a4 BIGINT)  
     PARTITIONED BY(ds STRING, hr STRING);
                 
--添加分区
ALTER TABLE mf_cc ADD IF  NOT EXISTS PARTITION (ds='20230509', hr='1641');

--插入数据
INSERT INTO mf_cc PARTITION (ds='20230509', hr='1641') 
     VALUES("key","value",1,22,3,4);
     
--查询数据     
SELECT * FROM mf_cc WHERE ds='20230509' AND  hr='1641';
--返回结果
+-----+-------+------------+------------+--------+------+---------+-----+
| key | value | a1         | a2         | a3     | a4   | ds      | hr  |
+-----+-------+------------+------------+--------+------+---------+-----+
| key | value | 1          | 22         | 3      | 4    | 20230509| 1641|
+-----+-------+------------+------------+--------+------+---------+-----+
--清空列数据
ALTER TABLE mf_cc PARTITION(ds='20230509', hr='1641') 
                  CLEAR COLUMN key,a1 
    WITHOUT TOUCH;
--查询数据     
SELECT * FROM mf_cc WHERE ds='20230509' AND  hr='1641';
--返回结果，key和a1的数据已经变成null
+-----+-------+------------+------------+--------+------+---------+-----+
| key | value | a1         | a2         | a3     | a4   | ds      | hr  |
+-----+-------+------------+------------+--------+------+---------+-----+
| null| value | null       | 22         | 3      | 4    | 20230509| 1641|
+-----+-------+------------+------------+--------+------+---------+-----+
2.3.2 更新数据（UPDATE）

UPDATE操作用于将Transactional表或Delta Table表中行对应的单列或多列数据更新为新值

--方式1
UPDATE <table_name> SET <col1_name> = <value1> [, <col2_name> = <value2> ...] [WHERE <where_condition>];

--方式2
UPDATE <table_name> SET (<col1_name> [, <col2_name> ...]) = (<value1> [, <value2> ...])[WHERE <where_condition>];

--方式3
UPDATE <table_name>
       SET <col1_name> = <value1> [ , <col2_name> = <value2> , ... ]
        [ FROM <additional_tables> ]
        [ WHERE <where_condition> ]

使用示例：

--创建非分区表scid_update并导入数据，执行UPDATE操作更新满足指定条件的行对应的列数据
--创建Transactional表acid_update。  
CREATE TABLE IF  NOT EXISTS acid_update(id BIGINT) tblproperties ("transactional"="true");

--插入数据。
INSERT OVERWRITE TABLE acid_update VALUES(1),(2),(3),(2);

--查看插入结果。
SELECT * FROM acid_update; 

--返回结果
+------------+
| id         |
+------------+
| 1          |
| 2          |
| 3          |
| 2          |
+------------+

--将所有id为2的行，id值更新为4。
UPDATE acid_update SET id = 4 WHERE id = 2; 

--查看更新结果，2被更新为4。
SELECT * FROM acid_update; 

--返回结果
+------------+
| id         |
+------------+
| 1          |
| 3          |
| 4          |
| 4          |
+------------+
--创建Delta Table表mf_dt并导入数据，执行UPDATE操作删除满足指定条件的行
--创建目标Delta Table表mf_dt。
CREATE TABLE IF  NOT EXISTS mf_dt (pk BIGINT  NOT NULL PRIMARY KEY, 
                  val  BIGINT NOT NULL) 
                  PARTITIONED BY(dd STRING, hh STRING) 
                  tblproperties ("transactional"="true");

--插入数据
INSERT OVERWRITE TABLE mf_dt PARTITION (dd='01', hh='02') 
                 VALUES (1, 1), (2, 2), (3, 3);

--查看插入结果                 
SELECT * FROM mf_dt WHERE dd='01' AND hh='02';
--返回结果
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 02 |
| 3          | 3          | 01 | 02 |
| 2          | 2          | 01 | 02 |
+------------+------------+----+----+

--更新指定行的一列数据，将分区为01和02的所有pk=3的行，val值更新为30。
--方法一
UPDATE mf_dt SET val = 30 WHERE pk = 3 AND dd='01' AND hh='02';

--方法二
UPDATE mf_dt SET val = delta.val 
              FROM (SELECT pk, val FROM VALUES (3, 30) t (pk, val)) delta 
              WHERE delta.pk = mf_dt.pk AND mf_dt.dd='01' AND mf_dt.hh='02';

--查看更新结果。
SELECT * FROM mf_dt WHERE dd='01' AND hh='02';
--返回结果，pk=3的行val值被更新为30。
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 02 |
| 3          | 30         | 01 | 02 |
| 2          | 2          | 01 | 02 |
+------------+------------+----+----+
2.4 VALUES

如果需要向表中插入少量数据，可以通过INSERT…VALUES或VALUES TABLE操作向数据量小的表中插入数据

执行INSERT INTO操作前需要具备目标表的修改权限（Alter）及源表的元信息读取权限（Describe）

功能说明
INSERT…VALUES

在业务测试阶段，您可以通过INSERT … VALUES操作向表中插入数据执行简单测试：

  • 如果插入几条或十几条数据，您可以通过INSERT … VALUES语句快速向测试表中写入数据。
  • 如果插入几十条数据，您可以通过Tunnel上传一个TXT或CSV格式的数据文件导入数据。还可以通过DataWorks的导入功能快速导入一个数据文件
VALUES TABLE

如果您需要对插入的数据进行简单的运算，推荐使用MaxCompute的VALUES TABLE。VALUES TABLE可以在INSERT语句和任何DML语句中使用。功能如下：

  • 在没有任何物理表时，您可以模拟一个有任意数据的、多行的表，并进行任意运算。
  • 取代SELECT * FROM与UNION ALL组合的方式，构造常量表。
  • VALUES TABLE支持特殊形式。您可以不通过FROM子句，直接执行SELECT，SELECT表达式列表中不可以出现其它表中的数据。其底层实现为从一个1行0列的匿名VALUES表中进行选取操作。在测试UDF或其它函数时，您可以通过该方式免去手工创建DUAL表的过程。
2.4.1 使用限制

通过INSERT … VALUES或VALUES table操作向表中插入数据时，不支持通过INSERT OVERWRITE操作指定插入列，只能通过INSERT INTO操作指定插入列。

--INSERT … VALUES
INSERT INTO TABLE <table_name>
[PARTITION (<pt_spec>)][(<col1_name> ,<col2_name>,...)] 
VALUES (<col1_value>,<col2_value>,...),(<col1_value>,<col2_value>,...),...

--VALUES table
VALUES (<col1_value>,<col2_value>,...),(<col1_value>,<col2_value>,...),<table_name> (<col1_name> ,<col2_name>,...)...
2.4.2 使用示例
--通过INSERT…VALUES操作向特定分区内插入数据
--创建分区表srcp。
CREATE TABLE IF NOT EXISTS srcp (key STRING,value BIGINT) PARTITIONED BY (p STRING);

--向分区表srcp添加分区。
ALTER TABLE srcp ADD IF NOT EXISTS PARTITION (p='abc');

--向表srcp的指定分区abc中插入数据。
INSERT INTO TABLE srcp PARTITION (p='abc') VALUES ('a',1),('b',2),('c',3);

--查询表srcp。
SELECT * FROM srcp WHERE p='abc';

--返回结果。
+------------+------------+------------+
| key        | value      | p          |
+------------+------------+------------+
| a          | 1          | abc        |
| b          | 2          | abc        |
| c          | 3          | abc        |
+------------+------------+------------+
--通过VALUES table操作插入数据
--创建分区表srcp。
CREATE TABLE IF NOT EXISTS srcp (key STRING,value BIGINT) PARTITIONED BY (p STRING);

--向表srcp中插入数据。
INSERT INTO TABLE srcp PARTITION (p) SELECT concat(a,b), length(a)+length(b),'20170102' FROM VALUES ('d',4),('e',5),('f',6) t(a,b);

--查询表srcp。
SELECT * FROM srcp WHERE p='20170102';

--返回结果。
+------------+------------+------------+
| key        | value      | p          |
+------------+------------+------------+
| d4         | 2          | 20170102   |
| e5         | 2          | 20170102   |
| f6         | 2          | 20170102   |
+------------+------------+------------+

三、DQL语法

3.1 SELECT语法

MaxCompute支持通过SELECT语句查询数据

使用限制：

  • 当使用SELECT语句时，屏幕最多只能显示10000行结果，同时返回结果要小于10MB。当SELECT语句作为子句时则无此限制，SEECT子句会将全部结果返回给上层查询
  • SELECT语句查询分区表时默认禁止全表扫描
[with <cte>[, ...] ]
SELECT [all | distinct] <SELECT_expr>[, <except_expr>][, <replace_expr>] ...
       from <table_reference>
       [where <where_condition>]
       [group by {<col_list>|rollup(<col_list>)}]
       [having <having_condition>]
       [window <window_clause>]
       [order by <order_condition>]
       [distribute by <distribute_condition> [sort by <sort_condition>]|[ cluster by <cluster_condition>] ]
       [limit <number>]

在本章节中提供如下源数据，基于源数据提供相关示例：

--创建一张分区表sale_detail。
create table if not exists sale_detail
(
shop_name     string,
customer_id   string,
total_price   double
)
partitioned by (sale_date string, region string);

--向源表增加分区。
alter table sale_detail add partition (sale_date='2013', region='china');

--向源表追加数据。
insert into sale_detail partition (sale_date='2013', region='china') values ('s1','c1',100.1),('s2','c2',100.2),('s3','c3',100.3);

SELECT * from sale_detail;
--返回结果。
+------------+------------+------------+------------+------------+
| shop_name  | price      | customer   | sale_date  | region     |
+------------+------------+------------+------------+------------+
| s1         | 100.1      | c1         | 2013       | china      |
| s2         | 100.2      | c2         | 2013       | china      |
| s3         | 100.3      | c3         | 2013       | china      |
+------------+------------+------------+------------+------------+
3.1.1 WITH子句（cte）

可选。WITH子句包含一个或多个常用的表达式CTE。CTE充当当前运行环境中的临时表，可以在之后的查询中引用该表。CTE使用规则如下：

  • 在同一WITH子句中的CTE必须具有唯一的名字
  • 在WITH子句中定义的CTE仅对在同一WITH子句中的其他CTE可以使用
--假设A是子句中第一个CTE，B是子句中的第二个CTE
-- A引用A：无效，错误命令示例如下
with 
A as (SELECT 1 from A) 
SELECT * from A;

-- A引用B，B引用A：无效，不允许循环引用：
with 
A as (SELECT * from B ), 
B as (SELECT * from A ) 
SELECT * from B;

正确命令格式如下：

with 
A as (SELECT 1 as C),
B as (SELECT * from A) 
SELECT * from B;
3.1.2 列表达式（SELECT_expr）

必填。SELECT_expr格式为col1_name, col2_name, 列表达式,...，表示待查询的普通列、分区列或正则表达式。列表达式使用规则如下：

  • 用列名指定要读取的列。读取表sale_detail的列shop_name
SELECT shop_name from sale_detail;
  • 用星号（*）代表查询所有的列。可配合where子句指定过滤条件
--开启全表扫描，仅此Session有效。
set odps.sql.allow.fullscan=true;
SELECT * from sale_detail;
  • 在where子句中指定过滤条件
SELECT * from sale_detail where shop_name='s1';
  • 可以使用正则表达式
SELECT `sh.*` from sale_detail;
  • 选择sale_detail表中排出shop_name和customer_id两列的其他列
SELECT `(shop_name|customer_id)?+.+` from sale_detail;
  • 查询表中region列数据，如果有重复值时只显示一条
SELECT distinct region from sale_detail;
3.1.3 排除列（except_expr）

可选。except_expr格式为except(col1_name, col2_name, ...)。当您希望读取表内大多数列的数据，同时要排除表中少数列的数据时，可以通过SELECT * except(col1_name, col2_name, ...) from ...;语句实现，表示读取表数据时会排除指定列（col1、col2）的数据。

--读取sale_detail表的数据，并排除region列的数据。
SELECT * except(region) from sale_detail;
3.1.4 修改列（replace_expr）

可选。replace_expr格式为replace(exp1 [as] col1_name, exp2 [as] col2_name, ...)。当您希望读取表内大多数列的数据，同时要对表中少数列的数据进行修改时，可以通过SELECT * replace(exp1 as col1_name, exp2 as col2_name, ...) from ...;实现，表示读取表数据时会将col1的数据修改为exp1，将col2的数据修改为exp2。

--读取sale_detail表的数据，并修改total_price、region两列的数据。
SELECT * replace(total_price+100 as total_price, 'shanghai' as region) from sale_detail;
3.1.5 目标表信息（table_reference）

必填。table_reference表示查询的目标表信息。目标表使用规则如下：

  • 直接指定目标表名
SELECT customer_id from sale_detail;
  • 嵌套子查询
SELECT * from (SELECT region,sale_date from sale_detail) t where region = 'china';
3.1.6 WHERE子句（where_condition）

可选。where子句为过滤条件。如果表是分区表，可以实现列裁剪

  • 配合关系运算符，筛选满足指定条件的数据
SELECT * 
from sale_detail
where sale_date >= '2008' and sale_date <= '2014';
--等价于如下语句。
SELECT * 
from sale_detail 
where sale_date between '2008' and '2014';
  • 在列表达式（SELECT_expr）中，如果被重命名的列字段（赋予了列别名）使用了函数，则不能在where子句中引用列别名。错误命令列示例如下：
SELECT  task_name
        ,inst_id
        ,settings
        ,GET_JSON_OBJECT(settings, '$.SKYNET_ID') as skynet_id
        ,GET_JSON_OBJECT(settings, '$.SKYNET_NODENAME') as user_agent
from    Information_Schema.TASKS_HISTORY
where   ds = '20211215' and skynet_id is not null
limit 10;
3.1.7 GROUP BY分组查询（col_list）

可选。通常，group by和聚合函数配合使用，根据指定的普通列、分区列或正则表达式进行分组。group by使用规则如下：

group by操作优先级高于SELECT操作，因此group by的取值是SELECT输入表的列名或由输入表的列构成的表达式。需要注意的是：

  • group by取值为正则表达式时，必须使用列的完整表达式。

  • SELECT语句中没有使用聚合函数的列必须出现在GROUP BY中。

  • 直接使用输入表列名region作为group by的列，即以region值分组
SELECT region from sale_detail group by region;
  • 以region值分组返回每一组的销售额总量
SELECT sum(total_price) from sale_detail group by region;
  • 以region值分组，返回每一组的region值（组内唯一）及销售额总量
SELECT region, sum (total_price) from sale_detail group by region;
  • 以SELECT列的别名分组
SELECT region as r from sale_detail group by r;
--等效于如下语句。
SELECT region as r from sale_detail group by region;
  • 以列表达式分组
SELECT 2 + total_price as r from sale_detail group by 2 + total_price;
  • 当SQL语句设置了属性，即set odps.sql.groupby.position.alias=true;，group by中的整型常量会被当作SELECT的列序号处理
--与下一条SQL语句一起执行。
set odps.sql.groupby.position.alias=true;
--1代表SELECT的列中第一列即region，以region值分组，返回每一组的region值（组内唯一）及销售额总量。
SELECT region, sum(total_price) from sale_detail group by 1;
3.1.8 HAVING子句（having_condition）

可选。通常HAVING子句与聚合函数一起使用，实现过滤

--为直观展示数据呈现效果，向sale_detail表中追加数据。
insert into sale_detail partition (sale_date='2014', region='shanghai') values ('null','c5',null),('s6','c6',100.4),('s7','c7',100.5);
--使用having子句配合聚合函数实现过滤。
SELECT region,sum(total_price) from sale_detail 
group by region 
having sum(total_price)<305;
3.1.9 ORDER BY全局排序（order_condition）

可选。order by用于对所有数据按照指定普通列、分区列或指定常量进行全局排序。order by使用规则如下：

  • 默认对数据进行升序排序，如果降序排序，需要使用desc关键字。
  • order by默认要求带limit数据行数限制，没有limit会返回报错。如您需要解除order by必须带limit的限制

使用示例：

  • 查询表sale_detail的信息，按照total_price升序排列前2条
SELECT * from sale_detail order by total_price limit 2;
  • 在使用order by排序时，NULL会被认为比任何值都小，这个行为与MySQL一致，但是与Oracle不一致。查询表sale_detail的信息，并按照total_price升序排列前2条
SELECT * from sale_detail order by total_price limit 2;
  • order by后面需要加上SELECT列的别名。当SELECT某列时，如果没有指定列的别名，则列名会被作为列的别名。order by加列的别名。命令示例如下
SELECT total_price as t from sale_detail order by total_price limit 3;
--等效于如下语句。
SELECT total_price as t from sale_detail order by t limit 3;
  • 当SQL语句设置了属性，即set odps.sql.orderby.position.alias=true;，order by中的整型常量会被当作SELECT的列序号处理。命令示例如下
--与下一条SQL语句一起执行。
set odps.sql.orderby.position.alias=true;
SELECT * from sale_detail order by 3 limit 3;
3.1.10 SORT BY局部排序（sort_condition）

可选，通常配合distribute by使用

  • sort by默认对数据进行升序排序，如果降序排序，需要使用desc关键字。
  • 如果sort by语句前有distribute by，sort by会对distribute by的结果按照指定的列进行排序

使用示例：

  • 查询表sale_detail中的列region和total_price的值并按照region值进行哈希分片，然后按照total_price对哈希分片结果进行局部升序排序。命令示例如下。
--为直观展示数据呈现效果，向sale_detail表中追加数据。
insert into sale_detail partition (sale_date='2014', region='shanghai') values ('null','c5',null),('s6','c6',100.4),('s7','c7',100.5);
SELECT region,total_price from sale_detail distribute by region sort by total_price;
  • 查询表sale_detail中的列region和total_price的值并按照region值进行哈希分片，然后按照total_price对哈希分片结果进行局部降序排序。命令示例如下。
SELECT region,total_price from sale_detail distribute by region sort by total_price desc;
  • 如果sort by语句前没有distribute by，sort by会对每个Reduce中的数据进行局部排序。保证每个Reduce的输出数据都是有序的，从而增加存储压缩率，同时读取时如果有过滤，能够减少真正从磁盘读取的数据量，提高后续全局排序的效率。命令示例如下
SELECT region,total_price from sale_detail sort by total_price desc;
3.1.11 LIMIT限制输出行数（number）

可选。limit <number>中的number是常数，用于限制输出行数，取值范围为int32位取值范围，即最大值不可超过2,147,483,647

3.2 SELECT语序

在SELECT语法中，涉及的主要操作主要包括：

select、from、where、group by、having、windows、qualify、order by、distribute by、sort by、limit

基于order by不和distribute by、sort by同时使用，group by也不和distribute by、sort by同时使用的限制，常见select语句的执行顺序如下：

  • 场景1：from->where->group by->having->select->order by->limit
  • 场景2：from->where->select->distribute by->sort by

为了避免混淆，MaxCompute支持以执行顺序书写查询语句，语法结构可改为如下形式：

from <table_reference>
[where <where_condition>]
[group by <col_list>]
[having <having_condition>]
[window <window_name> AS (<window_definition>)]
[qualify <expression>]
select [all | distinct] <select_expr>, <select_expr>, ...
[order by <order_condition>]
[distribute by <distribute_condition> [sort by <sort_condition>] ]
[limit <number>]

示例1，符合场景1的命令如下：

--按照select语法书写。
set odps.sql.allow.fullscan=true;
select region,max(total_price) 
from sale_detail 
where total_price > 100
group by region 
having sum(total_price)>300.5 
order by region 
limit 5;
--按照执行顺序书写。与上一写法等效。
from sale_detail 
where total_price > 100 
group by region 
having sum(total_price)>300.5 
select region,max(total_price) 
order by region 
limit 5;

该命令的执行逻辑如下：

  • a.从sale_detail表（from sale_detail）中取出满足where total_price > 100条件的数据。
  • b.对于a中得到的结果按照region进行分组（group by region）。
  • c.对于b中得到的结果筛选分组中满足total_price之和大于305的数据（having sum(total_price)>305）。
  • d.对于c中得到的结果select region,max(total_price)。
  • e.对于d中得到的结果按照region进行排序（order by region）。
  • f.对于e中得到的结果仅显示前5条数据（limit 5）。

示例2，符合场景2的命令提示如下：

--按照select语法书写。
set odps.sql.allow.fullscan=true;
select shop_name
       ,total_price
       ,region
from   sale_detail
where  total_price > 100.2
distribute by region
sort by total_price;
--按照执行顺序书写。与上一写法等效。
from   sale_detail 
where  total_price > 100.2 
select shop_name
       ,total_price
       ,region 
distribute by region 
sort by total_price;

该命令的执行逻辑如下：

  • a.从sale_detail表（from sale_detail）中取出满足where total_price > 100.2条件的数据。
  • b.对于a中得到的结果select shop_name, total_price, region。
  • c.对于b中得到的结果按照region进行哈希分片（distribute by region）。
  • d.对于c中得到的结果按照total_price进行升序排列（sort by total_price）。
​​​​​​​3.3 子查询（SUBQUERY）

当需要在某个查询的执行结果基础上进一步执行查询操作时，例如单独计算聚合值、检查某条记录的存在性、筛选基于另一查询结果的数据、关联更新或删除操作、简化JOIN操作以获取单个值、作为派生表给主查询使用、提供排序或分组的依据以及进行逐行比较，可以通过子查询操作实现

子查询指在一个完整的查询语句之中，嵌套若干个不同功能的小查询，从而一起完成复杂查询的一种编写形式：

  • 基础子查询：在查询的FROM子句中使用子查询，作为一个临时表使用，可用于复杂计算或者在查询中进行数据转换
  • IN SUBQUERY：当需要匹配一组由子查询返回的值时，可以在WHERE子句中使用IN子查询，适用于从主查询中选择匹配子查询条件的行
  • NOT IN SUBQUERY：理解为IN SUBQUERY的反向选择
  • EXISTS SUBQUERY：EXISTS子查询在主查询中用来判断子查询是否返回了任何结果。它适用于检查一个记录是否存在于子查询中，而不关心子查询具体返回了什么
  • NOT EXISTS SUBQUERY：理解为EXISTS SUBQUERY的反向选择
  • SCALAR SUBQUERY：标量子查询返回单个值的子查询，通常用在SELECT列表中，或作为WHERE或HAVING子句中的一个条件值，适用于计算特定的聚合值或者当需要从子查询中提取单个数据点
3.3.1 基础子查询

普通查询操作的对象是目标表，但是查询的对象也可以另一个select语句，这种查询为子查询。在from子句中，子查询可以被当作一张表，于其他表或者子查询进行join操作。

select <select_expr> from (<select_statement>) [<sq_alias_name>];

使用示例：

set odps.sql.allow.fullscan=true;
select * from (select shop_name from sale_detail) a;
3.3.2 IN SUBQUERY

in subquery与left semi join用法类似

select<select_expr1>from<table_name1>where<select_expr2>in(select<select_expr3>from<table_name2>);
--等效于leftsemijoin如下语句。
select<select_expr1>from<table_name1><alias_name1>leftsemijoin<table_name2><alias_name2>on<alias_name1>.<select_expr2>=<alias_name2>.<select_expr3>;

注意：使用IN的子查询时，在子查询的返回结果中会自动去除NULL值的记录

使用示例：

set odps.sql.allow.fullscan=true;
select * from sale_detail where total_price in (select total_price from shop);
3.3.3 EXISTS SUBQUERY

使用exists subquery时，当子查询中有至少一行数据时，返回True，否则返回False

select <select_expr> from <table_name1> where exists (select <select_expr> from <table_name2> where <table_name2_colname> = <table_name1>.<colname>);

注意：使用exists的子查询时，在子查询的返回结果中会自动去除NULL值的记录

使用示例：

set odps.sql.allow.fullscan=true;
select * from sale_detail where exists (select * from shop where customer_id = sale_detail.customer_id);
--等效于以下语句。
select * from sale_detail a left semi join shop b on a.customer_id = b.customer_id;
3.3.4 SCALAR SUBQUERY

若子查询的输出结果为单行单列时，可以作为标量使用，即可以参与标量运算。如果查询的结果只有一行，在外面嵌套一层max或min操作，其结果不变。

select <select_expr> from <table_name1> where (<select count(*) from <table_name2> where <table_name2_colname> = <table_name1>.<colname>) <标量运算符> <scalar_value>;
--等效于以下语句。
select <table_name1>.<select_expr> from <table_name1> left semi join (select <colname>, count(*) from <table_name2> group by <colname> having count(*) <标量运算符> <scalar_value>) <table_name2> on <table_name1>.<colname> = <table_name2>.<colname>;

使用示例：

set odps.sql.allow.fullscan=true;
select * from shop where (select count(*) from sale_detail where sale_detail.shop_name = shop.shop_name) >= 1;
3.4 交集（INTERSECT）、并集（UNION）、补集（EXCEPT）

本段介绍交集（intersect、intersect all、intersect distinct）、并集（union、union all、union distinct）和补集（except、except all、except distinct、minus、minus all、minus distinct）的使用方法

  • 交集：求两个数据集的交集，即输出两个数据集均包含的记录
  • 并集：求两个数据集的并集，即将两个数据集合并成一个数据集
  • 补集：求第二个数据集在第一个数据集中的补集，即输出第一个数据集包含而第二个数据集不包含的记录

注意：对数据集进行操作的结果不一定会按序排列；如果数据集的数据类型不一致，系统会进行隐式转换。

3.4.1 交集
--取交集不去重。
<select_statement1> intersect all <select_statement2>;
--取交集并去重。intersect效果等同于intersect distinct。
<select_statement1> intersect [distinct] <select_statement2>;

使用示例：

--对两个数据集取交集，不去重
select * from values (1, 2), (1, 2), (3, 4), (5, 6) t(a, b) 
intersect all 
select * from values (1, 2), (1, 2), (3, 4), (5, 7) t(a, b);
--对两个查询结果取交集并去重
select * from values (1, 2), (1, 2), (3, 4), (5, 6) t(a, b) 
intersect distinct 
select * from values (1, 2), (1, 2), (3, 4), (5, 7) t(a, b);
--等效于如下语句。
select distinct * from 
(select * from values (1, 2), (1, 2), (3, 4), (5, 6) t(a, b) 
intersect all 
select * from values (1, 2), (1, 2), (3, 4), (5, 7) t(a, b)) t;
3.4.2 并集
--取并集不去重。
<select_statement1> union all <select_statement2>;
--取并集并去重。
<select_statement1> union [distinct] <select_statement2>;

注意：存在多个union all时，支持通过括号指定union all的优先级

--对两个数据集取并集，不去重
select * from values (1, 2), (1, 2), (3, 4) t(a, b) 
union all 
select * from values (1, 2), (1, 4) t(a, b);
对两个数据集取并集并去重
select * from values (1, 2), (1, 2), (3, 4) t(a, b)
union distinct 
select * from values (1, 2), (1, 4) t(a, b);
--等效于如下语句。
select distinct * from (
select * from values (1, 2), (1, 2), (3, 4) t(a, b) 
union all 
select * from values (1, 2), (1, 4) t(a, b));
--通过括号指定union all的优先级
select * from values (1, 2), (1, 2), (5, 6) t(a, b)
union all 
(select * from values (1, 2), (1, 2), (3, 4) t(a, b)
union all 
select * from values (1, 2), (1, 4) t(a, b));
3.4.3 补集
--取补集不去重。
<select_statement1> except all <select_statement2>;
<select_statement1> minus all <select_statement2>;
--取补集并去重。
<select_statement1> except [distinct] <select_statement2>;
<select_statement1> minus [distinct] <select_statement2>;
3.5 JOIN
  • 左连接（LEFT OUTER JOIN）：可简写为LEFT JOIN。返回左表中的所有记录，即使右表中没有与之匹配的记录

通常，JOIN操作左边为大表，右表为小表，如果右表值不唯一，建议不要连续使用过多LEFT JOIN，以免在JOIN过程中产生数据膨胀，导致作业停滞

  • 右连接（RIGHT OUTER JOIN）：可简写为RIGHT JOIN。返回右表中的所有记录，即使在左表中没有与之匹配的记录
  • 全连接（FULL OUTER JOIN）：可简写为FULL JOIN。返回左右表中的所有记录
  • 内连接（INNER JOIN）：关键字INNER可以省略。左右表中至少存在一个匹配行时，INNER JOIN返回数据行

注意：使用JOIN时，会在计算中自动加入JOIN的key is not null的过滤条件，去除关联键为NULL的值所在行

<table_reference> JOIN <table_factor> [<join_condition>]
| <table_reference> {LEFT OUTER|RIGHT OUTER|FULL OUTER|INNER|NATURAL} JOIN <table_reference> <join_condition>
  • table_reference：必填。待执行JOIN操作的左表查询语句。格式为table_name [alias] | table_query [alias] |... 。
  • table_factor：必填。待执行JOIN操作的右表或表查询语句。格式为table_name [alias] | table_subquery [alias] |... 。
  • join_condition：可选。JOIN连接条件，是一个或多个等式表达式组合。格式为on equality_expression [and equality_expression]...，equality_expression为等式表达式。
示例数据：
--创建分区表sale_detail和sale_detail_jt。
CREATE TABLE if NOT EXISTS sale_detail
(
shop_name     STRING,
customer_id   STRING,
total_price   DOUBLE
)
PARTITIONED BY (sale_date STRING, region STRING);

CREATE TABLE if NOT EXISTS sale_detail_jt
(
shop_name     STRING,
customer_id   STRING,
total_price   DOUBLE
)
PARTITIONED BY (sale_date STRING, region STRING);

--向源表增加分区。
ALTER TABLE sale_detail ADD PARTITION (sale_date='2013', region='china') PARTITION (sale_date='2014', region='shanghai');
ALTER TABLE sale_detail_jt ADD PARTITION (sale_date='2013', region='china');

--向源表追加数据。
INSERT INTO sale_detail PARTITION (sale_date='2013', region='china') VALUES ('s1','c1',100.1),('s2','c2',100.2),('s3','c3',100.3);
INSERT INTO sale_detail PARTITION (sale_date='2014', region='shanghai') VALUES ('null','c5',null),('s6','c6',100.4),('s7','c7',100.5);
INSERT INTO sale_detail_jt PARTITION (sale_date='2013', region='china') VALUES ('s1','c1',100.1),('s2','c2',100.2),('s5','c2',100.2);

--查询表sale_detail和sale_detail_jt中的数据，命令示例如下：
SET odps.sql.allow.fullscan=true;
SELECT * FROM sale_detail;
--返回结果
+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s3         | c3          | 100.3       | 2013       | china      |
| null       | c5          | NULL        | 2014       | shanghai   |
| s6         | c6          | 100.4       | 2014       | shanghai   |
| s7         | c7          | 100.5       | 2014       | shanghai   |
+------------+-------------+-------------+------------+------------+

SET odps.sql.allow.fullscan=true;
SELECT * FROM sale_detail_jt;
-- 返回结果
+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s5         | c2          | 100.2       | 2013       | china      |
+------------+-------------+-------------+------------+------------+

--创建做关联的表。
SET odps.sql.allow.fullscan=true;
CREATE TABLE shop AS SELECT shop_name, customer_id, total_price FROM sale_detail;
使用示例：

1.左连接：

--分区表需要开启全表扫描功能，否则join操作会执行失败。
SET odps.sql.allow.fullscan=true;
--由于表sale_detail_jt及sale_detail中都有shop_name列，因此需要在select子句中使用别名进行区分。
SELECT a.shop_name AS ashop, b.shop_name AS bshop FROM sale_detail_jt a 
       LEFT OUTER JOIN sale_detail b ON a.shop_name=b.shop_name;

--返回结果如下
+------------+------------+
| ashop      | bshop      |
+------------+------------+
| s2         | s2         |
| s1         | s1         |
| s5         | NULL       |
+------------+------------+

2.右连接：

--分区表需要开启全表扫描功能，否则join操作会执行失败。
SET odps.sql.allow.fullscan=true;
--由于表sale_detail_jt及sale_detail中都有shop_name列，因此需要在select子句中使用别名进行区分。
SELECT a.shop_name AS ashop, b.shop_name AS bshop FROM sale_detail_jt a 
       RIGHT OUTER JOIN sale_detail b ON a.shop_name=b.shop_name;

--返回结果如下
+------------+------------+
| ashop      | bshop      |
+------------+------------+
| s1         | s1         |
| s2         | s2         |
| NULL       | s3         |
| NULL       | null       |
| NULL       | s6         |
| NULL       | s7         |
+------------+------------+

3.全连接：

--分区表需要开启全表扫描功能，否则join操作会执行失败。
SET odps.sql.allow.fullscan=true;
--由于表sale_detail_jt及sale_detail中都有shop_name列，因此需要在select子句中使用别名进行区分。
SELECT a.shop_name AS ashop, b.shop_name AS bshop FROM sale_detail_jt a 
       FULL OUTER JOIN sale_detail b ON a.shop_name=b.shop_name;

--返回结果如下
+------------+------------+
| ashop      | bshop      |
+------------+------------+
| NULL       | s3         |
| NULL       | s6         |
| s2         | s2         |
| NULL       | null       |
| NULL       | s7         |
| s1         | s1         |
| s5         | NULL       |
+------------+------------+

4.内连接：

--分区表需要开启全表扫描功能，否则join操作会执行失败。
SET odps.sql.allow.fullscan=true;
--由于表sale_detail_jt及sale_detail中都有shop_name列，因此需要在select子句中使用别名进行区分。
SELECT a.shop_name AS ashop, b.shop_name AS bshop FROM sale_detail_jt a 
       INNER JOIN sale_detail b ON a.shop_name=b.shop_name;

--返回结果如下
+------------+------------+
| ashop      | bshop      |
+------------+------------+
| s2         | s2         |
| s1         | s1         |
+------------+------------+

5.自然连接：

--分区表需要开启全表扫描功能，否则join操作会执行失败。
SET odps.sql.allow.fullscan=true;
--自然连接。
SELECT * FROM sale_detail_jt NATURAL JOIN sale_detail;
--等效于如下语句。
SELECT sale_detail_jt.shop_name AS shop_name, sale_detail_jt.customer_id AS customer_id, sale_detail_jt.total_price AS total_price, sale_detail_jt.sale_date as sale_date, sale_detail_jt.region as region from sale_detail_jt 
INNER JOIN sale_detail 
ON sale_detail_jt.shop_name=sale_detail.shop_name AND sale_detail_jt.customer_id=sale_detail.customer_id and sale_detail_jt.total_price=sale_detail.total_price AND sale_detail_jt.sale_date=sale_detail.sale_date AND sale_detail_jt.region=sale_detail.region;

--返回结果如下
+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
+------------+-------------+-------------+------------+------------+

6.join与where相结合，查询两表中region为china且shop_name一致的记录数，保留sale_detail表的全部记录：

--分区表需要开启全表扫描功能，否则join操作会执行失败。
SET odps.sql.allow.fullscan=true;
--执行SQL语句。
SELECT a.shop_name
    ,a.customer_id
    ,a.total_price
        ,b.total_price 
FROM  (SELECT * FROM sale_detail WHERE region = "china") a 
LEFT JOIN (SELECT * FROM sale_detail_jt WHERE region = "china") b 
ON   a.shop_name = b.shop_name;

--返回结果如下
+------------+-------------+-------------+--------------+
| shop_name  | customer_id | total_price | total_price2 |
+------------+-------------+-------------+--------------+
| s1         | c1          | 100.1       | 100.1        |
| s2         | c2          | 100.2       | 100.2        |
| s3         | c3          | 100.3       | NULL         |
+------------+-------------+-------------+--------------+

错误命令示例如下：

SELECT a.shop_name
    ,a.customer_id
    ,a.total_price
        ,b.total_price 
FROM  sale_detail a 
LEFT JOIN sale_detail_jt b 
ON   a.shop_name = b.shop_name 
WHERE  a.region = "china" AND b.region = "china";

--返回结果如下
+------------+-------------+-------------+--------------+
| shop_name  | customer_id | total_price | total_price2 |
+------------+-------------+-------------+--------------+
| s1         | c1          | 100.1       | 100.1        |
| s2         | c2          | 100.2       | 100.2        |
+------------+-------------+-------------+--------------+
--获取到两个表的交集，非sale_detail表的全部记录

更多相关内容参考官方详细文档 help.aliyun.com/


http://www.kler.cn/a/518816.html

相关文章：

  • Vue 引入及简单示例
  • 【C】memory 详解
  • vue3+elementPlus之后台管理系统（从0到1）(day4-完结)
  • Linux的常用指令的用法
  • 深度学习项目--基于LSTM的糖尿病预测探究(pytorch实现)
  • 【Project】CupFox电影网站数据爬取分析与可视化
  • linux-execve的使用
  • C# 利用ssh 开启一个命令交互窗口
  • 微信小程序部分用户报错ERR_CONNECTION_REFUSED连接被拒绝
  • 海外问卷调查如何影响企业的经营？在品牌建设中有何指导意义？
  • 【PyTorch】3.张量类型转换
  • 使用JavaScript实现猜数字小功能
  • SWPU 2022 新生赛--web题
  • 洛谷 P1687 机器人小Q（DP）
  • AI视频生成技术：Sora的突破与挑战
  • 第五部分：Linux中的gcc/g++以及gdb和makefile
  • React第二十五章(受控组件/非受控组件)
  • AI对齐服务：从7.5亿美元市场到创新转型
  • 罗氏线圈的学习【一】
  • 多线程详解——Kotlin多线程几种实现方式
  • 2024年CSDN年度回顾：个人成长、创作历程与生活的融合与平衡
  • 在Ubuntu上使用Apache+MariaDB安装部署Nextcloud并修改默认存储路径
  • 编码器和扩散模型
  • centos搭建docker registry镜像仓库
  • Alibaba Spring Cloud 十六 Sentinel 流量控制
  • Qt Designer and Python: Build Your GUI