MaxCompute（原ODPS）通过引入更多基本数据类型如TINYINT、VARCHAR及TIMESTAMP等，增强了SQL语言的表达能力。同时，新增了大量内建函数，包括数学、日期和字符串函数等，以更好地适应复杂的应用场景。
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。
| 类型 | 是否新增 | 常量定义 | 描述 |
|---|---|---|---|
| TINYINT | 是 | 1Y, -127Y | 8位有符号整形, 范围-128到127 |
| SMALLINT | 是 | 32767S, -100S | 16位有符号整形, 范围-32768到32767 |
| INT | 是 | 1000, -15645787 ( 注1 ) | 32位有符号整形, 范围-2^31到2^31 - 1 |
| BIGINT | 否 | 100000000000L, -1L | 64位有符号整形, 范围-2^63 + 1到2^63 - 1 |
| FLOAT | 是 | 无 | 32位二进制浮点型 |
| DOUBLE | 否 | 3.1415926 1E+7 | 64位二进制浮点型 |
| DECIMAL | 否 | 3.5BD, 99999999999.9999999BD | 10进制精确数字类型，整形部分范围-10^36+1到10^36-1, 小数部分精确到10^-18 |
| VARCHAR | 是 | 无 ( 注2 ) | 变长字符类型，n为长度，取值范围1到65535 |
| STRING | 否 | "abc", 'bcd', "alibaba" 'inc' ( 注3 ) | 字符串类型，目前长度限制为8M |
| BINARY | 是 | 无 | 二进制数据类型，目前长度限制为8M |
| DATETIME | 否 | DATETIME '2017-11-11 00:00:00' | 日期时间类型，范围从0001年1月1日到9999年12月31日, 精确到毫秒 |
| TIMESTAMP | 是 | TIMESTAMP '2017-11-11 00:00:00.123456789' | 与时区无关的时间戳类型，范围从0001年1月1日到9999年12月31日 23.59:59.999999999, 精确到纳秒 ( 注4 ) |
| BOOLEAN | 否 | TRUE,FALSE | boolean类型, 取值TRUE或FALSE |
新的隐式转换规则表如下表 ( 注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 | FALSE |
| tinyint to | FALSE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | FALSE | FALSE |
| smallint to | FALSE | FALSE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | FALSE | FALSE |
| int to | FALSE | FALSE | FALSE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | FALSE | FALSE |
| bigint to | FALSE | FALSE | FALSE | FALSE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | FALSE | FALSE |
| float to | FALSE | FALSE | FALSE | FALSE | FALSE | TRUE | TRUE | TRUE | TRUE | TRUE | FALSE | FALSE |
| double to | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | TRUE | TRUE | TRUE | TRUE | FALSE | FALSE |
| decimal to | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | TRUE | TRUE | TRUE | FALSE | FALSE |
| string to | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | TRUE | TRUE | TRUE | TRUE | FALSE | FALSE |
| varchar to | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | TRUE | TRUE | TRUE | TRUE | FALSE | FALSE |
| timestamp to | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | TRUE | TRUE | TRUE | FALSE |
| binary to | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | TRUE |
此外，还新增了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;返回
| a | b | p |
|---|---|---|
| 0 | NULL | 2017-01-23 22:30:47.130406621 |
| 0 | NULL | 2017-01-23 22:30:47.130406621 |
可以看到分区列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类型符合一一映射关系
| max compute type | java type |
|---|---|
| tinyint | java.lang.Byte |
| smallint | java.lang.Short |
| int | java.lang.Integer |
| bigint | java.lang.Long |
| float | java.lang.Float |
| double | java.lang.Double |
| decimal | java.math.BigDecimal |
| boolean | java.lang.Boolean |
| string | java.lang.String |
| varchar | com.aliyun.odps.data.Varchar |
| binary | com.aliyun.odps.data.Binary |
| datetime | java.util.Date |
| timestamp | java.sql.Timestamp |
| array | java.util.List |
| map | java.util.Map |
| struct | com.aliyun.odps.data.Struct |
需要注意的是这里，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类型处理。
扫一扫
1301
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
