MaxCompute - ODPS重装上阵 第二弹 - 新的基本数据类型与内建函数

摘要： MaxCompute（原ODPS）是阿里云自主研发的具有业界领先水平的分布式大数据处理平台, 尤其在集团内部得到广泛应用，支撑了多个BU的核心业务。 MaxCompute除了持续优化性能外，也致力于提升SQL语言的用户体验和表达能力，提高广大ODPS开发者的生产力。

点此查看原文

MaxCompute（原ODPS）是阿里云自主研发的具有业界领先水平的分布式大数据处理平台, 尤其在集团内部得到广泛应用，支撑了多个BU的核心业务。 MaxCompute除了持续优化性能外，也致力于提升SQL语言的用户体验和表达能力，提高广大ODPS开发者的生产力。

MaxCompute基于ODPS2.0新一代的SQL引擎，显著提升了SQL语言编译过程的易用性与语言的表达能力。我们在此推出MaxCompute(ODPS2.0)重装上阵系列文章

第一弹 - 善用MaxCompute编译器的错误和警告 
第二弹 - 新的基本数据类型与内建函数 
第三弹 - 复杂类型 
第四弹 - CTE，VALUES，SEMIJOIN

上次向您介绍了 [编译器的易用性改进] https://yq.aliyun.com/articles/225028)，这次向您介绍新的基本数据类型与内建函数

原ODPS只有六种基本数据类型, bigint, double, decimal, string, datetime, boolean。一般用起来也还够用，但是在某些场景下就不够了

场景1 
一个项目需要将原来在SQL SERVER上面运行的ETL系统，最近因为数据量暴涨，需要迁移到MaxCompute。发现某些表用了VARCHAR，有的用了INT。这些类型也被系统的多处SQL脚本用到还参与了运算。迁移到ODPS上时候，用STRING代替VARCHAR，用BIGINT代替INT ( 注1 )。 
迁移完成后发现数据和原有系统对不上，是不是VARCHAR的截断，INT的溢出行为导致数据不同呢？还是什么其他原因，面对着现存系统，没办法，只好一点点看代码，跑数据，做分析。本来以为挺轻松的项目，花了几周时间才搞定。。。

场景2 
我的项目需要存放二进制数据到表中，因为是语音识别项目，每小段采集的音频如果作为一个字段存下去，然后用个UDF处理起来很方便。可是，ODPS没有BINARY数据类型，好吧，就存成STRING好了。可是编写写UDF时候好麻烦，为了存进去，必须将byte[]编码成string, 读的时候又必须解码，代码写了一大堆，运行速度也慢了好多。。。 
MaxCompute采用基于ODPS2.0的SQL引擎，大幅度扩充了基本类型并提供了配套的内建函数，基本解决了上述问题。

基本类型的扩充 
此文中采用MaxCompute Studio作展示，首先，安装MaxCompute Studio，导入测试MaxCompute项目，创建工程，建立一个新的MaxCompute脚本文件,　如下

运行后，建立另一个文件插入数据，如下： 

运行后，可以在MaxCompute Studio的Project Explorer中找到新创建的表，察看表的详细信息，并预览数据，如下图 

可以看到

创建表的时候，首先指定使用MaxCompute新类型系统，因为兼容性的考虑，需要您主动打开这个设定。也可以在MaxCompute Studio中缺省指定，如下图 

MaxCompute Studio支持含新类型表数据的导入导出，可参考此ATA文章

如果不使用MaxCompute Studio，可以在脚本中指定，set odps.sql.type.system.odps2=true;。Studio实际上在后台也是使用这个开关来控制是否启用新类型。odps.sql.type.system.odps2设定为true的时候，除了可以使用新类型，也控制其它方面的一些行为改变。将在相关部分说明。

如果需要在MaxCompute 项目中缺省打开，可以联系您的项目管理员，在项目模板中设定。

扩充后MaxCompute支持的基本数据类型如下表，新增类型有TINYINT, SMALLINT, INT, FLOAT, VARCHAR, TIMESTAMP, BINARY。 


新的隐式转换规则表如下表 ( 注5 )

boolean	tinyint	smallint	int	bigint	float	double	decimal	string	varchar	timestamp	binary
boolean to	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
tinyint to	FALSE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	FALSE
smallint to	FALSE	FALSE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	FALSE
int to	FALSE	FALSE	FALSE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	FALSE
bigint to	FALSE	FALSE	FALSE	FALSE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	FALSE
float to	FALSE	FALSE	FALSE	FALSE	FALSE	TRUE	TRUE	TRUE	TRUE	TRUE	FALSE
double to	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	TRUE	TRUE	TRUE	TRUE	FALSE
decimal to	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	TRUE	TRUE	TRUE	FALSE
string to	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	TRUE	TRUE	TRUE	TRUE	FALSE
varchar to	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	TRUE	TRUE	TRUE	TRUE	FALSE
timestamp to	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	TRUE	TRUE	TRUE
binary to	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE

此外，还新增了DECIMAL类型与DATETIME的常量定义方式, 100BD就是数值为100的DECIMAL，datetime ‘2017-11-11 00:00:00’就是个datetime类型的常量。常量定义的方便之处在于可以直接用到values子句和values表中，以后会单独介绍。

内建函数的扩充 
任何编程语言，包括SQL，不管语言本身多强大，如过没有丰富的函数后者类库支持，在应用的时候还是会很不方便，MaxCompute配合新数据类型，大大丰富了内建函数，如下：

数学函数 
log2, log10, bin, hex, unhex, degrees, radians, sign, e, pi, factorial, cbrt, shiftleft, shiftright, shiftrightunsigned 
日期函数 
unix_timestamp, year, quarter, month, day, dayofmonth, hour, minute, second, millisecond, nanosecond, from_utc_timestamp, current_timestamp, add_months, last_day, next_day, months_between 
字符串函数 
concat_ws, lpad, rpad, replace, soundex, substring_index, base64, unbase64 
聚合函数 
corr 
这些函数大部分与Hive的内建函数兼容，用法可以直接参考Hive的文档。与Hive不同的是MaxCompute提供的这些函数都是用本地代码实现的高效版本。

新增的TIMESTAMP数据类型支持纳秒级别的精度，与之配合，新增了MaxCompute特有的millisecond, nanosecond函数，可以取出TIMESTAMP, DATETIME的毫秒部分与TIMESTAMP的纳秒部分。

如本系列上一篇中提到的，MaxCompute支持新的强制转换写法，例如，要强制bigint变量为转换为string，可以直接写string(a_bigint), 和写成cast(a_bigint as string)是等效的。具体用哪种形式完全取决于您的偏好。

需要注意的是所有用来支持新类型的函数，例如current_timestamp，也需要设定set odps.sql.type.system.odps2=true;，否则会报告编译错误。

分区类型的扩充 
分区类型的支持也进行了扩充，目前分区类型支持TINYINT, SMALLINT, INT, BIGINT, VARCHAR与STRING ( 注6 )

另外原ODPS在动态分区的时候，如果分区列的类型与对应SELECT列表中的列的类型不严格一致，会报错。MaxCompute支持隐式类型转换 
例如:

set odps.sql.type.system.odps2=true;
create table parttable(a int, b double) partitioned by (p string);
insert into parttable partition(p) (p, a) select key, value, current_timestmap() from src;
select * from parttable;

返回 

可以看到分区列p的值为从timestamp类型隐含转换而来。

使用UDF 
目前，MaxCompute2.0的JAVA UDF已经支持了新类型，Python UDF会尽快实现。JAVA UDF使用新类型的方法如下：

UDAF和UDTF通过@Resolve注解来获取signature，MaxCompute2.0支持在注解中使用新类型，如 @Resolve(“smallint->varchar(10)”) 
UDF通过反射分析evaluate来获取signature，此时max compute内置类型与JAVA类型符合一一映射关系 


需要注意的是这里，array类型对应的java类型是List，而不是数组

小结 
MaxCompute大大扩充了基本数据类型与内建函数，可以更好的适应丰富的应用场景。不过，很多比较复杂的场景仅使用基本类型仍然很麻烦，请期待MaxCompute重装上阵下一篇，复杂类型的支持 
！

标注 
注1 
对于INT常量，如果超过INT取值范围，会转为BIGINT，如果超过BIGINT取值范围，会转为DOUBLE 
在原ODPS下，因为历史原因，SQL脚本中的所有int类型都被转换为bigint，例如

create table a_bigint_table(a int); -- 这里的int实际当作bigint处理
select cast(id as int) from mytable; -- 这里的int实际当作bigint处理

为了与ODPS原有模式兼容，MaxCompute在没有设定odps.sql.type.system.odps2为true的情况下，仍然保留此转换，但是会报告一个警告提示int被当作bigint处理了，如果您的脚本有此种情况，建议全部改写为bigint，避免混淆。

注2 VARCHAR类型常量可通过STRING常量的隐式转换表示 
注3 STRING常量支持连接, 例如’abc’ ‘xyz’会解析为’abcxyz’，不同部分可以写在不同行上 
注4 受底层系统限制，目前调用current_timestamp还达不到纳秒精度，例如

meta_dev>set odps.sql.type.system.odps2=true;select nanosecond(current_timestamp());

输出为类似

+------+
| _c0  |
+------+
| 877000000 |
+------+

Timestamp常量与外部数据导入可以支持纳秒精度。

注5 在原ODPS下，因为历史原因，DOUBLE可以隐式的转换为BIGINT，这个转换潜在可能有数据丢失，一般数据库系统都不允许。为了与ODPS原有模式兼容，MaxCompute在没有设定odps.sql.type.system.odps2为true的情况下，仍然允许此转换，但是会报告警告；在设定odps.sql.type.system.odps2为true的情况下，不允许此隐式类型转换。 
注6 在原ODPS下，因为历史原因，虽然可以指定分区类型为BIGINT，但是除了表的schema表示其为BIGINT,　任何其他情况都被处理为STRING。例如:

create table parttest (a bigint) partitioned by (pt bigint);
insert into parttest partition(pt) select 1, 2 from dual;
insert into parttest partition(pt) select 1, 10 from dual;
select * from parttest where pt >= 2;

返回的结果只有一行，因为10被按照字符串和2比，没能返回。为了与ODPS原有模式兼容，MaxCompute在没有设定odps.sql.type.system.odps2为true的情况下，仍然如此处理；在设定odps.sql.type.system.odps2为true的情况下，BIGINT类型的分区严格按照BIGINT类型处理。