MaxCompute SQL

    set odps.sql.type.system.odps2=true;

    CREATE TABLE test_newtype (
    c1 tinyint
    ,c2 smallint
    ,c3 int
    ,c4 BIGINT
    ,c5 float
    ,c6 DOUBLE
    ,c7 decimal
    ,c8 binary
    ,c9 timestamp
    ,c10 ARRAY<map<BIGINT,BIGINT>>
    ,c11 map<STRING,ARRAY<BIGINT>>
    ,c12 STRUCT<s1:STRING,s2:BIGINT>
    ,c13 varchar(20))
    LIFECYCLE 1
    ;
    
    
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        

    select cast('2019-02-16 00:00:01' AS string) > '2019-02-16';

    select cast('2019-02-16 00:00:02' AS datetime) > '2019-02-16 00:00:01';
    
        
              
              

    abs(0.9999999999 - 1.0000000000) < 0.000000001

     -- 0.9999999999和1.0000000000为10位精度，而0.000000001为9位精度。
     -- 此时可以认为0.9999999999和1.0000000000相等。
    
    
        
        
        

* A/B中，如果A、B都是Bigint类型，那么结果会是Double类型。
* 只有参数是STRING、BIGINT或DOUBLE类型才能参与算术运算，日期型和布尔型不允许参与运算。
* STRING类型在参与运算前会进行隐式类型转换，转换为DOUBLE类型。

* 位运算符不支持隐式转换，只允许BIGINT类型。

* 逻辑运算符只允许BOOLEAN类型参与运算，不支持隐式类型转换。
* Null And False = False，Null and True = Null；True or Nnll=True，False or Null = Null;

* 以下将转换失败，没有严格按照格式来。可以先用TO_DATE函数调整格式再转

      cast("2013/12/31 02/34/34" as datetime)  
      cast("20131231023434" as datetime)  
      cast("2013-12-31 2:34:34" as datetime)
    
    
        
        
        

    #完整版
    CREATE [EXTERNAL] TABLE IF NOT EXISTS table_name
    [(col_name 数据类型 [DEFAULT value] [COMMENT 字段注释], ...)]
    [COMMENT 表中文注释]
    [PARTITIONED BY (col_name 数据类型 [COMMENT col_comment], ...)]
    --[CLUSTERED BY (col_name [, col_name, ...]) [SORTED BY (col_name [ASC | DESC] [, col_name --[ASC | DESC] ...])] INTO number_of_buckets BUCKETS] -- 用于创建Hash Clustering表时设置表的----Shuffle和Sort属性。
    --[STORED BY StorageHandler] -- 仅限外部表。
    --[WITH SERDEPROPERTIES (Options)] -- 仅限外部表。
    --[LOCATION OSSLocation]; -- 仅限外部表。
    [LIFECYCLE days]
    
    
    # 第二种建表:
     CREATE TABLE [IF NOT EXISTS] table_name
     LIKE existing_table_name
     
    # 第三种建表
    create table if not exists table_name 
    AS select_statement;
    
    
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
    

>     INTO number_of_buckets BUCKETS

>  
>  
>             
> ```
>
>
>
> 指定了哈希桶的数目。这个数字必须填写，且由数据量大小来决定。此外，缺省条件下MaxCompute只能支持最多1111个Reducer，所以此处最多也只支持1111个哈希桶。您可以使用
>
>

>     CREATE TABLE T1 (a string, b string, c bigint) CLUSTERED BY (c) SORTED by (c) INTO 1024 BUCKETS; --创建Hash Clustering非分区表。
>     CREATE TABLE T1 (a string, b string, c bigint) PARTITIONED BY (dt string) CLUSTERED BY (c) SORTED by (c) INTO 1024 BUCKETS; --创建Hash Clustering分区表。
>  
>  
>       
>       
>  
> ```
>
>

##### 4.1.2 表其他操作

-- 查看表
DESC <table_name>;
DESC extended <table_name>; --查看外部表信息。

-- 查看建表语句
SHOW CREATE TABLE <table_name>;

-- 删除表
-- 删除外部表时，OSS上的数据不会被删除。跟Hive一样意思。
DROP TABLE [IF EXISTS] table_name;

-- 重命名表
ALTER TABLE table_name RENAME TO new_table_name;

-- 修改表Owner
ALTER TABLE table_name CHANGEOWNER TO 'ALIYUN$xxx@aliyun.com';

-- 修改表的注释
ALTER TABLE table_name SET COMMENT 'new coments for table sale_detail';

-- 修改表的修改时间
-- 此时，MaxCompute会认为表的数据有变动，生命周期的计算会重新开始。
ALTER TABLE table_name TOUCH;

-- 修改表的Hash Clustering属性
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 Clustering属性的语法格式如下
ALTER TABLE table_name NOT CLUSTERED;

-- 清空非分区表里的数据
TRUNCATE TABLE table_name;

-- 清空分区表里的数据
ALTER TABLE table_name DROP PARTITION(dt = '??????');

-- 强制删除表数据（分区数据），可以加purge
DROP TABLE table_name PURGE;
ALTER TABLE table_name DROP PARTITION(dt = '??????') PURGE;

-- 备份表

##### 4.1.3 备份表的数据

​ 如果project 里的空间比较紧张，在想办法进行删除数据或者压缩数据，那么可以考虑 MaxCompute 里对表 的 archive 功能，效果是可以将存储空间压缩 50%左右，而且在这个过程中会将多个小文件自动的合并掉

​ 代价是如果某个数据块 损坏或某台机器损坏，恢复数据块的时间要比原来的方式长了，读的性能会有一定损失。

​ 所以现在这种功能可以用在一些冷数据的压缩存储上，比如一些非常大的日志数据，超过一定时间期限后使用 的频率非常低，但是又需要长期保存，则可以考虑用 raid file 来存储。

​ 语法：

ALTER TABLE [table_name] <PARTITION(partition_name='partition_value')> ARCHIVE;

-- 案例
alter table my_log partition(ds='20140101') archive;

​

#### 4.2 生命周期操作

​ MaxCompute提供了数据生命周期管理功能，**方便释放存储空间，简化回收数据的流程** 。

##### 4.2.1 注意事项

  * 在MaxCompute中，每当表的数据被修改后，表的`LastDataModifiedTime`将会被更新，非分区表和分区表的数据是否要回收都是根据每张表的LastDataModifiedTime和设置的生命周期天数来判断的，**从最后一次数据被修改开始计算，如果经过days后仍没有被改动，就会被系统自动回收** （类似drop table操作）
  * 不同的是非分区表不支持取消生命周期的设置，只能修改生命周期；而分区表可以取消某个具体分区的生命周期设置，并且**就算最后一个分区被回收了，这张分区表也不会被删除** 。
  * 生命周期只能在表级别设置，不能在分区级别设置，但分区表设置生命周期后，生命周期是在分区级别生效。
  * create table …like …的方式来建表，会复制列名、列注释以及表注释等，**但生命周期不会** 。

##### 4.2 2 禁止生命周期

​ 在某些情况下有些特定的分区可能不希望被生命周期功能自动回收掉，比如一个月的月初，或者双 11 期间的数 据，在这种情况下可以禁止该分区被生命周期功能回收。

​ 语法：

-- 禁止生命周期
ALTER TABLE table_name [partition_spec] ENABLE|DISABLE LIFECYCLE;

-- 示例
ALTER TABLE trans PARTITION(dt='20141111') DISABLE LIFECYCLE;

##### 4.2.3 修改表的生命周期属性

​ MaxCompute 提供数据生命周期管理功能，方便用户释放存储空间。

​ 语法：

-- 修改表的生命周期属性
ALTER TABLE table_name SET LIFECYCLE DAYS;

-- 案例
ALTER TABLE test_lifecycle SET lifecycle 50;

* 第一种情况，如果表是非分区表，自最后一次数据被修改开始计算，经过 days 天后数据仍未被改动，则此表无需 用户干预， 将会被 MaxCompute 自动回收(类似 drop table 操作)。在 MaxCompute 中，每当表的数据被修改后，表的LastDataModifiedTime 将会被更新，因此， MaxCompute 会根据每张表的LastDataModifiedTime 以及 lifecycle 的设 置来判断是否要回收此表。
  * 第二中情况，如果是分区表，则根据各分区的 LastDataModifiedTime 判断该分区是否该被回收。不同于非分区表，分区表的最后一个分区被回收后，该表不会被删除。

#### 4.3 分区和列操作

  * 注意： 
    * MaxCompute SQL仅支持新增分区，不支持新增分区字段；
    * 不能删除列；
    * 添加的新列不支持指定顺序，默认在最后一列。

##### 4.3.1 分区操作

-- 添加分区
-- 对于多级分区的表，如果需要添加新的分区，必须指明全部的分区值。
ALTER TABLE table_name ADD [IF NOT EXISTS] PARTITION(par1 = '',par2 = ''...);

-- 删除分区操作
ALTER TABLE table_name DROP [IF EXISTS] PARTITION(par1 = '',par2 = ''...);

-- 修改分区值
ALTER TABLE table_name PARTITION (par1 = '旧值',par2 = '旧值', ...) RENAME TO PARTITION (par1 = '新值',par2 = '新值', ...);

-- 修改表、分区的更新时间
ALTER TABLE table_name TOUCH PARTITION(par1 = '',...);

##### 4.3.2 列操作

-- 添加列
ALTER TABLE table_name ADD COLUMNS (col_name1 数据类型,col_name2 数据类型...);

-- 修改列名操作
ALTER TABLE table_name CHANGE COLUMN old_col_name RENAME TO new_col_name;

-- 修改列的注释
ALTER TABLE table_name CHANGE COLUMN col_name COMMENT comment_string;

-- 同时修改列名及列注释
ALTER TABLE table_name CHANGE COLUMN old_col_name new_col_name column_type COMMENT column_comment;

##### 4.3.3 合并分区

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

  * 注意： 
    * 如果运行过程中出现源数据被并发修改（包括`INSERT`、`RENAME`或`DROP`）时，即使指定`IF EXISTS`也会报错。
    * 不支持外部表和SHARD表，对于CLUSTERED表合并后的分区文件会消除CLUSTERED属性。
    * 一次性合并分区数量限制为4000个。

​ 语法：

ALTER TABLE <tableName> MERGE [IF EXISTS] PARTITION(<predicate>) [, PARTITION(<predicate2>) ...] OVERWRITE PARTITION(<fullPartitionSpec>) [PURGE];

-- 示例：
+------------+------------+------------+------------+

+------------+------------+------------+------------+

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

-- MERGE PARTITIONS允许指定多个谓词条件，示例如下，指定到具体分区下，合并剩余分区。
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;

#### 4.4 视图操作

##### 4.4.1 创建视图

  * 注意： 
    * 不允许向视图写入数据，例如使用`insert into`或者`insert overwrite`操作视图。
    * 创建视图时，您必须有对视图所引用表的读权限。

​ 语法：

CREATE [OR REPLACE] VIEW [IF NOT EXISTS] view_name
[(col_name [COMMENT col_comment], ...)]		-- 没有数据类型
[COMMENT view_comment]
[AS select_statement]

-- 创建视图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;

##### 4.4.2 视图其他操作

-- 重命名视图
ALTER VIEW view_name RENAME TO new_view_name;

-- 删除视图
DROP VIEW [IF EXISTS] view_name;

#### 4.5 更新表数据（Insert OverWrite 和 Insert Into）

语法：

INSERT OVERWRITE|INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)] [(col1,col2 ...)]

select_statement
FROM from_statement;

* 

注意

    * MaxCompute的`INSERT`语法与通常使用的MySQL或Oracle的`INSERT`语法有差别，在`INSERT OVERWRITE/INTO`后需要加入`TABLE`关键字，而非直接使用`TABLENAME`。
    * 当遇到并发写入时，MaxCompute会保证在冲突时只会一个作业成功，其它冲突作业失败。
    * OVERWRITE类作业可保证一致性，但INTO类作业在冲突失败后可能存在失败作业的数据残留。
    * Insert后面的partition参数不允许使用函数等表达式，只能是常量；
    * 源表与目标表的对应关系依赖于在`select`子句中列的顺序，而不是表与表之间列名的对应关系。
    * 向某个分区插入数据时，分区列只能出现在select的条件中。

##### 4.5.1 功能说明

  * **insert into** ：直接向表或表的分区中追加数据。不支持`INSERT INTO`到Hash Clustering表。如果您需要插入少量测试数据，可以配合[VALUES](https://help.aliyun.com/document_detail/73778.html#concept-uhn-rdb-wdb)使用。
  * **insert overwrite** ：先清空表中的原有数据，再向表或分区中插入数据。目前`INSERT OVERWRITE`不支持指定插入列的功能，暂时只能用`INSERT INTO`

##### 4.5.2 Insert 动态分区注意事项

  * `insert into partition`时，如果分区不存在，会自动创建分区。
  * 多个`insert into partition`作业并发时，如果分区不存在，会自动创建分区，但只会成功创建一个分区。（这点和并发写入保证只有一个作业成功一样）
  * 如果不能控制`insert into partition`作业并发，则只能通过预创建分区避免问题。

#### 4.6 多路输出-Multi Insert

​ MaxCompute SQL支持在一个语句中将数据插入不同的目标表或者分区中实现多路输出。

​ 语法：就是一个From开头，后面多个insert语句。

FROM from_statement
INSERT OVERWRITE | INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)]
select_statement1 [FROM from_statement]
[INSERT OVERWRITE | INTO TABLE tablename2 [PARTITION (partcol1=val3, partcol2=val4 ...)]
select_statement2 [FROM from_statement]]

* 注意： 
    * 通常，单个SQL中最多可以写255路输出。超过255路，则报语法错误。
    * 在一个`multi insert`中，对于分区表，同一个目标分区不允许出现多次。
    * 在一个`multi insert`中，对于未分区表，该表不能出现多次。
    * 对于同一张分区表的不同分区，不能同时有`insert overwrite`和`insert into`操作，否则报错返回

  * 示例：

--创建表sale_detail_multi。
create table sale_detail_multi like sale_detail;

--将表sale_detail中的数据插入到表sale_detail_multi。
set odps.sql.allow.fullscan=true; //开启全表扫描，仅此session有效。
from sale_detail
insert overwrite table sale_detail_multi partition (sale_date='2010', region='china' ) 
select shop_name, customer_id, total_price 
insert overwrite table sale_detail_multi partition (sale_date='2011', region='china' )
select shop_name, customer_id, total_price ;

#### 4.7 输出到动态分区

##### 4.7.1 说明

​ 在使用INSERT OVERWRITE语句将数据插入到分区表时，MaxCompute提供了如下两种方式：

  * 输出到静态分区：在INSERT语句中**直接指定分区值** ，将数据插入指定的分区。
  * 输出到动态分区：在INSERT语句中**不直接指定分区值** ，只指定分区列名。分区列的值在SELECT子句中提供，系统自动根据分区字段的值将数据插入到相应分区。（在SQL运行之前，您无法得知会产生哪些分区。只有在语句运行结束后，才能通过分区字段产生的值确定产生的分区）

##### 4.7.2 动态分区语法

INSERT OVERWRITE|INTO TABLE tablename PARTITION (partcol1, partcol2 ...) --也就是这里不指定具体值
select_statement FROM from_statement;

##### 4.7.3 动态分区注意事项

  * 使用INSERT INTO最多可以生成10000个动态分区，使用INSERT OVERWRITE最多可以生成60000个动态分区。
  * 分布式环境下，使用动态分区功能的SQL中，单个进程最多只能输出512个动态分区，否则会引发运行时异常。
  * 动态生成的分区值不允许为NULL，也不支持含特殊字符和中文，否则会引发运行时异常`FAILED: ODPS-0123031:Partition exception - invalid dynamic partition value: province=xxx`。
  * 如果目标表有多级分区，在运行INSERT语句时允许指定部分分区为静态，但是静态分区必须是高级分区。
  * 如果目标表为Hash Clustering Table，则不支持动态分区。
  * 动态分区中，**select_statement** 字段和目标表动态分区的对应是按字段顺序决定，并不是按照列名称决定的。 
    * 动态分区插入时， 。这点和普通插入分区表不一样。

  * 在动态分区进行时，如果分区列的数据类型和select列表中类型不一致，MaxCompute能支持隐式类型转换。

### 五、SELECT语句

#### 5.1 Select语法注意事项

SELECT语句查询分区表时禁止全表扫描，屏显目前最多只能显示10000行结果。

    * 如果想要执行全表扫描，需要执行以下开关：set odps.sql.allow.fullscan=true;

`select_expr`支持这样的正则表达式：

SELECT `abc.*` FROM t;选出t表中所有列名以abc开头的列。

SELECT `(ds)?+.+` FROM t;选出t表中列名不为ds的所有列。
SELECT `(ds|pt)?+.+` FROM t;选出t表中排除ds和pt两列的其它列。
SELECT `(d.*)?+.+` FROM t;选出t表中排除列名以d开头的其它列。

* 

在SQL解析中，`GROUP BY`操作先于`SELECT`操作，因此`GROUP BY`的取值是`SELECT`输入表的列名或者由输入表的列构成的表达式，不允许是`SELECT`语句的输出列的别名。

OFFSET 和 ORDER BY LIMIT语句配合，可以指定跳过OFFSET数目的行。

--将src按照key从小到大排序后，输出第11到第30行（OFFSET 10指定跳过前10行，LIMIT 20指定最多输出20行）。

SELECT * FROM src ORDER BY key LIMIT 20 OFFSET 10;

* 

子查询必须要有别名

#### 5.2 select语序

案例一：

SELECT  key
    ,MAX(value)
FROM    src t
WHERE   value > 0
GROUP BY key
HAVING  SUM(value) > 100
ORDER BY key
LIMIT   100
;

-- 以上语句的逻辑执行顺序是FROM->WHERE->GROUY BY->HAVING->SELECT->ORDER BY->LIMIT

-- 案例二
SELECT  shop_name
    ,total_price
    ,region
FROM    sale_detail
WHERE   total_price > 150
DISTRIBUTE BY region
SORT BY region
;

-- 以上语句的逻辑执行顺序是FROM->WHERE->SELECT->DISTRIBUTE BY->SORT BY。

* `ORDER BY`中只能引用`SELECT`列表中生成的列，而非访问`FROM`的源表中的列。
  * `HAVING`可以访问`GROUP BY key`和聚合函数。
  * `SELECT`时，如果语句中有`GROUP BY`，便只能访问`GROUP BY key`和聚合函数，而不是`FROM`中源表中的列。

#### 5.3 交集、并集、补集

交集：Intersect & Intersect distinct（交集后去重）

并集：Union All & Union

补集：Except All & Except distinct （即第一个数据集减去第二个数据集的结果，默认是EXCEPT DISTINCT）

​ 这里EXCEPT等同于MINUS

-- EXCEPT ALL 如果第一个数据集有重复的，有多少个减多少个
SELECT * FROM VALUES (1, 2), (1, 2), (3, 4), (3, 4), (5, 6), (7, 8) t(a, b) 
EXCEPT ALL 
SELECT * FROM VALUES (3, 4), (5, 6), (5, 6), (9, 10) t(a, b);

-- EXCEPT DISTINCT 如果第一个数据集有重复的，那么只当做去重后的一个来减
SELECT * FROM VALUES (1, 2), (1, 2), (3, 4), (3, 4), (5, 6), (7, 8) t(a, b) 
EXCEPT
SELECT * FROM VALUES (3, 4), (5, 6), (5, 6), (9, 10) t(a, b);

​ 注意，集合操作左右两个分支要求列个数必须一致。如果数据类型不一致，可能会进行隐式类型转换。

​ MaxCompute最多允许256个分支的集合操作，超出256个将报错。

#### 5.4 Join操作

  * `LEFT OUTER JOIN`：左连接，可简写为`LEFT JOIN`。返回左表中的所有记录，右表中没有与之匹配的记录显示为空。 
    * 如果右表值不唯一，建议不要连续使用过多`LEFT JOIN`，会造成数据重复，数据膨胀导致作业停止。

  * `RIGHT OUTER JOIN`：右连接，可简写为`RIGHT JOIN`。返回右表中的所有记录，左表中没有与之匹配的记录显示为空
  * `FULL OUTER JOIN`：全连接，可简写为`FULL JOIN`。返回左右表中的所有记录。
  * `INNER JOIN`：内连接。关键字`INNER`可以省略。返回表之间相互都匹配的数据行
  * 隐式连接，即不指定`JOIN`关键字执行连接。就普通的写法
  * 自然连接，natural join

#### 5.5 Semi Join

参考：https://help.aliyun.com/document_detail/73784.html?spm=a2c4g.11186623.6.700.2f0d3dd6JPv77w

#### 5.6 MapJoin Hint

​ 当一个大表和一个或多个小表JOIN时，可以在SELECT语句中显式指定MAPJOIN以提升查询性能。

##### 5.6.1 原理

​ 在大表和一个或多个小表JOIN的场景下，**MAPJOIN会将指定的小表全部先加载到的内存中，然后在Map阶段完成大表和小表的连接从而加快JOIN的执行速度** 。

​ MAPJOIN在Map阶段执行表连接，而不用等到Reduce阶段才执行表连接。这样就节省了大量数据传输的时间以及系统资源，从而起到了优化作业的作用。

​ 指定的表仅能为小表，且表被加载到内存后占用的总内存不得超过512 MB。

>
>
> 通常情况下，JOIN操作在Reduce阶段执行表连接。整个JOIN过程包含Map、Shuffle和Reduce三个阶段。
>
>

##### 5.6.2 使用方法

​ 在SELECT语句中使用Hint提示`/*+ MAPJOIN(table) */`，如下：

SELECT /* + MAPJOIN(a) */
    a.shop_name,
    b.customer_id,
    b.total_price
FROM shop a JOIN sale_detail b
ON a.shop_name = b.shop_name;

##### 5.6.3 使用（限制）条件

  * MAPJOIN支持小表为子查询，在引用小表或子查询时，需要引用别名。
  * LEFT OUTER JOIN的左表必须是大表，RIGHT OUTER JOIN的右表必须是大表，INNER JOIN的左表或右表均可以作为大表，FULL OUTER JOIN不能使用MAPJOIN。
  * MaxCompute在MAPJOIN中最多支持指定128张小表，否则报语法错误。MAPJOIN中多个小表用逗号隔开，例如`/*+MAPJOIN(a,b,c)*/`。（实际开发一般是8张）
  * 如果使用MAPJOIN，则小表占用的总内存不得超过512 MB。由于MaxCompute是压缩存储，因此小表在被加载到内存后，数据大小会急剧膨胀。此处的512 MB是指加载到内存后的空间大小。

SELECT /* + MAPJOIN(a) */
    a.total_price,
    b.total_price
FROM shop a JOIN sale_detail b
ON a.total_price < b.total_price OR a.total_price + b.total_price < 500;

#### 5.7 Laterval View

#### 5.8 Grouping Sets

-待补充。

参考：https://help.aliyun.com/document_detail/92807.html?spm=a2c4g.11186623.6.704.625e8030Qazc5p

#### 5.9 SELECT TRANSFORM语法

-待补充。

参考：https://help.aliyun.com/document_detail/73719.html?spm=a2c4g.11186623.6.705.4ba67830goviwc

#### 5.10 With AS语法（CTE）

​ MaxCompute支持SQL标准的CTE，提高SQL语句的可读性与执行效率。

​ 语法格式：

WITH 
 cte_name AS
(
    cte_query
)
[,cte_name2  AS 
 (
 cte_query2
 )
,……]

示例：

-- 这样写看起来复杂
INSERT OVERWRITE TABLE srcp PARTITION (p='abc')
SELECT * FROM (
SELECT a.key, b.value
FROM (
    SELECT * FROM src WHERE key IS NOT NULL    ) a
JOIN (
    SELECT * FROM src2 WHERE value > 0    ) b
ON a.key = b.key
) c
UNION ALL
SELECT * FROM (
SELECT a.key, b.value
FROM (
    SELECT * FROM src WHERE key IS NOT NULL    ) a
LEFT OUTER JOIN (
    SELECT * FROM src3 WHERE value > 0    ) b
ON a.key = b.key AND b.key IS NOT NULL
)d;

-- 可以改成with as的形式比较直观。也不用反复嵌套
with 
  a as (select * from src where key is not null),
  b as (select  * from src2 where value>0),
  c as (select * from src3 where value>0),
  d as (select a.key,b.value from a join b on a.key=b.key),
  e as (select a.key,c.value from a left outer join c on a.key=c.key and c.key is not null)
insert overwrite table srcp partition (p='abc')
select * from d union all select * from e;

#### 5.11 Clone Table 复制表

-待补充。

#### 5.12 Explain执行计划

-待补充。

### 六、 MaxCompute和其他数据库的区别

    1. MaxCompute适合海量存储和大数据分析，不适合在线服务；

    2. MaxCompute表不支持主键、索引和字段约束，不支持事务操作。

    3. MaxCompute表不⽀持UPDATE操作和DELECT操作，只能DROP 整个表或者某一分区数据，然后用INSERT OVERWRITE/INSERT INTO写入数据。

    4. MaxCompute可以在select中使用mapjoin Hint，将指定的小表全部先加载到的内存中，然后在Map阶段完成大表和小表的连接从而加快JOIN的执行速度，提高查询速度；

    5. SELECT操作输出屏显的数据行数受限制，最大为10000条。不支持通过SQL下载数据。

    6. MaxCompute表可以指定生命周期，生命周期结束后自动清除数据；

    7. 插入语句有insert into ｜ overwrite into，后面需加Table关键字，overwrite into写入前会清空表数据；

    8. 子查询必须要有别名，select输入表的数量不能超过16张。

    9. 数据插入表的字段映射不是根据Select的别名做的，而是根据Select的字段的顺序和表里的字段的顺序。

    10. 不能修改分区列列名，只能修改分区列对应的值，支持增加列，但是不支持删除列以及修改列的数据类型(数据类型应该可以吧)。

    11. 在MaxCompute中需要通过Tunnel、Dship工具或MaxCompute Tunnel SDK导出数据。

    12. select语句的查询结果默认使用了limit，如果希望数据量很大时返回所有记录，需要设置

>
>
> 其他区别参考：https://help.aliyun.com/document_detail/51823.html?spm=a2c4g.11186623.2.19.68516f416InQIc#concept-yjp-crl-vdb --SQL使用限制
>
>

### 七、函数

#### 7.1 内建函数

-待补充。

#### 7.2 自定义函数

-待补充。

### 八、MaxCompute SQL优化

​ 首先优化SQL的过程，实际上就是要尽可能减少IO读取，尽可能减少计算资源的使用，尽可能减少SQL复杂度，尽可能提升运行速度。

  * **(1) 建分区表** ：但建议分区层数不超过3层，后续查询时为了避免全表扫描需要分区裁剪，分区值尽量常量化，避免不可确定值；插入数据时尽量采用写入静态分区的方式，优化数据存储，提高运行效率；如果用动态分区，底层来说会生成较多的小文件和多个mapreduce任务，增加系统负担。
  * **(2) 只select有效列，并用limit限制返回的条数** 。考虑这两种情况：（1）有些事实表的字段很多，多到有四五十个字段，这种表实际是可能存在的，比如企业报税的申报表，那么select * from整张表的所有字段时，数据量一大就会消耗更多查询时间；（2）第二种情况是假设事实表中存有某个复杂格式的字段，比如json格式，那么在select * from时也会耗时严重，因此select有效列是较好的习惯。
  * **(3) mapjoin优化** ：mapjoin的原理简单说就是先将小表加载到内存中，后续大表与内存中的小表直接join连接计算（底层是大表与内存中的小表的计算在map阶段直接完成，避免了reduce阶段的计算耗时）
  * **(4) 避免一些消耗资源的操作** ，比如： 
    * **少用distinct** ，容易触发数据倾斜
    * count（distinct）处理的时间会很长可以转换成**count（）+ group by** 。因为如果原先只用count(distinct)来处理表的数据，只会开启一个reduce任务来完成所有的计算，而改成count/sum + group by后，除了count/sum会开启一个reduce计算之外，group by也会单独开启另一个reduce来计算，因此数据量大时可以明显提升查询速度。
    * **尽量避免Order by** ，order by会触发全局排序，只能单点运行，效率低，如果业务允许，可以改成distribute by + sort by
    * 将full outer join 改为left outer join + union all 并对小表使用上mapjoin。
    * 多个表join时，join顺序很重要，优先选择join结果输出小的表先关联，能有效减少中间数据量，节省IO和计算资源。
    * 读取相同源表时可以合并成一条sql，系统会优化只读取一次。

  * **(5) 尽量使用内置的UDF函数和窗口函数** ：内置UDF在实现时做了很多优化，运行快，省资源，窗口函数本身能处理很多复杂问题。
  * **(6) 尽可能保证表达式两边的数据类型一致** ：如果发生隐式转换容易造成精度问题，比如string和bigint都转成double来相等比较，悲观情况下，可能触发数据倾斜。这时要cast显式转换一下

工作中还用到的：
- 数据量达到千万级别的，尽量固化分区，不在查询sql里面进行动态分区，减少资源使用
- sql行数较长的，尽量拆分成工作流任务，可以优化性能和排查问题的难易度。
- 维度表要放到mapjoin里，加大资源利用率
- 数据倾斜较高的表，先落地成临时表
- 每日全量推送任务的，如果表级数据量较高的，分析是否可以改成增量模式。

### 九、问题案例收集

​ 待补充

参考：https://help.aliyun.com/knowledge_detail/150534.html#section-iok-hsp-b2z