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

本文链接：https://blog.youkuaiyun.com/goldfish8848/article/details/142972975

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      |