MaxCompute SQL

MaxCompute SQL

• 知识点结构图
  

• 本文初衷是为了学习归纳，若有错误，请指出。

修改记录

时间	内容
2020年9月13日	第一次发布

一、概述

1.1 定义

​ MaxCompute（原 ODPS） SQL 适用于海量数据(TB 级别)，实时性要求不高的场合，比如离线批量计算，它的每个作业的准备，提交等阶段要花费较长时间。

​ 采用的是类似与SQL的语法，可以看作是标准SQL的子集，但和数据库也有很多不同，比如没有事务，主键约束，索引等。

1.2 优缺点

• 优点：
  • MaxCompute SQL的优点是学习成本低，不需要了解复杂的分布式计算概念，只需要会sql便可以操作；
  • MaxCompute主要用于大规模的数据计算，最快支持在分钟或秒钟级别完成查询返回结果，但无法在毫秒级别返回结果。
• 缺点：
  • 一次作业批量处理海量数据，不适合直接对接需要每秒处理几千至数万笔事务的业务系统。
  • MaxCompute的SQL语法与Oracle、MySQL有一定差别，无法将其他数据库中的SQL语句无缝迁移至MaxCompute中。
  • MaxCompute SQL不支持事务、索引，也不支持Update或Delete操作。

​

1.3 支持的数据类型

​ 支持的数据类型有：Bigint、Double、String、Datetime、Boolean、Decimal，Float。

​ 目前MaxCompute SQL中使用到新数据类型（TINYINT、SMALLINT、INT、FLOAT、VARCHAR、TIMESTAMP、BINARY）时，需要用set命令开启：

• Session级别：要使用新数据类型，需在SQL语句前加上set语句set odps.sql.type.system.odps2=true;，并与SQL语句一起提交执行。

• Project级别：支持对Project级别进行新类型打开。Project Owner可根据需要对Project进行设置，命令为：

  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
  ;
  

1.4 运算符

• 关系运算符注意点：

  • 在进行部分关系运算之前，需要首先进行类型转换，否则可能返回NULL。如下举例，'2019-02-16 00:00:01'为DATETIME类型，而'2019-02-16'为STRING类型，在进行比较关系运算前需首先完成显示类型转换：

    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';
    

  • 由于DOUBLE值存在一定的精度差，因此建议两个DOUBLE类型相减，取绝对值的方式进行判断。当绝对值足够小时，认为两个DOUBLE数值相等，比如：

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

  • 在将String类型和Bigint类型数据进行比较时，这两个数据都将转换成Double类型，比较过程中可能丢失精度，所以需要将String用cast显示转换成Bigint类型运算。

• 算术运算符注意点：

  • 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;

1.5 MaxCompute的ACID特性

• 原子性（Atomicity）
  • 任何时候MaxCompute会保证在冲突时只会一个作业成功，其它冲突作业失败。
  • 对于单个表或分区的CREATE、OVERWRITE、DROP操作，可以保证其原子性。
  • 跨表操作时不支持原子性（例如MULTI-INSERT）。
  • 在极端情况下，以下操作可能不保证原子性：
    • DYNAMIC INSERT OVERWRITE多于一万个分区，不支持原子性。
    • INTO类操作：这类操作失败的原因是事务回滚时数据清理失败，但不会造成原始数据丢失。
• 一致性（Consistency）
  • OVERWRITE类作业可保证一致性。
  • INTO类作业在冲突失败后可能存在失败作业的数据残留。
• 隔离性（Isolation）
  • 非INTO类操作保证读已提交。
  • INTO类操作存在读未提交的场景。
• 持久性（Durability）
  • MaxCompute保证数据的持久性。

二、类型转换

​ MaxCompute SQL允许数据类型之间的转换，类型转换方式包括显式类型转换和隐式类型转换。

2.1 显式转换

​ 显式类型转换是通过cast()函数将一种数据类型的值转换为另一种类型的值，在MaxCompute SQL中支持的显式类型转换，如下表所示。

• 注意点：
  • 将DOUBLE类型转为BIGINT类型时，小数部分会被截断，例如cast(1.6 as BIGINT) = 1。
  • 同样，满足DOUBLE格式的STRING类型转换为BIGINT时，会先将STRING转换为DOUBLE，再将DOUBLE转换为BIGINT，因此，小数部分会被截断。
  • 满足BIGINT格式的STRING类型可以被转换为DOUBLE类型，小数点后保留一位，例如cast(“1” as DOUBLE) = 1.0。
  • 日期类型转换时采用默认格式yyyy-mm-dd hh:mi:ss。
  • MaxCompute支持复杂类型的类型转换功能，规则同上。

2.2 隐式转换

​ 隐式类型转换是指在运行时，由MaxCompute依据上下文使用环境及类型转换规则自动进行的类型转换。MaxCompute支持的隐式类型转换规则，如下表所示。

• 注意点：
  • 这里的Y是指两者之间支持隐式转换，而没有说明转换成谁。

2.2.1 关系运算符作用下的隐式转换

​ 关系运算符包括=、<>、<、<=、>、>=、IS NULL、IS NOT NULL、LIKE、RLIKE、IN。由于LIKE、RLIKE、IN的隐式类型转换规则不同于其他关系运算符，将单独对其进行说明。此处的说明不包含这三种特殊的关系运算符。

​ 当不同类型的数据共同参与关系运算时，按照下述原则进行隐式类型转换。

• 总结：
  • Bigint、String、Double，三者Double最大，Bigint和String互转也会变成Double。
  • 有Decimal的情况，Decimal最大。

2.2.2 特殊的关系运算符作用下的隐式转换

​ 特殊的关系运算符包括LIKE、RLIKE、IN

• LIKE和RLIKE的source和pattern参数均仅接受STRING类型。
• 其他类型不允许参与运算，也不能进行到STRING类型的隐式类型转换。
• IN右侧的VALUE值列表中的数据类型必须一致。
• key in (value1, value2, …)中，当KEY与VALUES之间比较时，如果数据类型包含BIGINT、DOUBLE、STRING，建议统一转为DOUBLE类型；如果数据类型包含DATETIME、STRING，建议统一转为DATETIME类型。除此之外不允许其它类型之间的转换。

2.2.3 算术运算符作用下的隐式转换

• 只有STRING、BIGINT、DOUBLE和DECIMAL才能参与算术运算。
• STRING在参与运算前会进行隐式类型转换到DOUBLE。
• BIGINT和DOUBLE共同参与计算时，会将BIGINT隐式转换为DOUBLE。
• 日期型和布尔型不允许参与算数运算。

2.2.4 逻辑运算符作用下的隐式转换

​ 只有BOOLEAN才能参与逻辑运算。

2.3 内建函数涉及到的隐式转换

​ 在调用函数时，如果输入参数的数据类型与函数定义的参数数据类型不一致，把输入参数的数据类型转换为函数定义的数据类型。

2.4 CASE WHEN作用下的隐式转换

​ 针对case when中有不同结果类型的情况。

• 如果返回类型只有BIGINT、DOUBLE，统一转换为DOUBLE。
• 如果返回类型中有STRING类型，统一转换为STRING，如果不能转换（如BOOLEAN类型）则报错。
• 除此之外不允许其它类型之间的转换。

2.5 String和Datetime之间的转换

​ 这两者之间转换要遵循下面的格式：

• 注意

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

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

  • 如果超出对应月份实际拥有的天数，将会导致异常退出。

三、分区表

​ MaxCompute 用户在创建表时，允许指定表的某些列为分区列，从而决定数据的存储流向。指定分区列（在 select 语句的 where 条件过滤中使用分区列作为过滤条件）会给用户带来诸多便利，例如：提高 SQL 运行效率，减少计费等。

​ 一张表最多允许60000个分区，单表的分区层次不能超过6级。。

​ 但在使用 输出到动态分区(DYNAMIC PARTITION)，SQL 的运行效率较低，并且会带来较高的计费。

四、SQL操作

4.1 表操作

4.1.1 创建表

​ 创建表的语法格式，如下所示，但一般有很多用不到，用不到的加“–”号注释了：

#完整版
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;

• 注意点：
  • 1.partitioned by的字段必须不是建表里面的字段。
  • 2.不加EXTERNAL代表内部表，加EXTERNAL代表外部表，外部表、内部表和Hive的差不多，如下：
  • 3.partitioned by指定表的分区字段，目前支持TINYINT、SMALLINT、INT、BIGINT、VARCHAR和STRING类型。
  • 4.LIFECYCLE是表的生命周期，单位：天。注意，create table like语句不会复制源表的生命周期属性。
  • 5.如果是用create table… as select …的方式来建表，那么在建表的同时将数据复制到新表中，但是不会复制分区属性，只会把源表的分区列作为目标表的一般列处理，也就是新表会多一列。
  • 6.如果是用第二种建表create table …like …的方式来建表，那么源表和目标表具有相同的表结构，即列名、列注释以及表注释等均相同(生命周期不会)。但源表中的数据不会被复制到目标表。
  </