阿里云_数加平台类HQL的那些坑

 

简介

    近期在练习机器学习算法，部分数据本机跑太慢了。恰好阿里云有在线的机器学习平台，所以不妨用阿里云来实现了。

    将之前MySQL数据迁移到阿里云上，这个过程主要比较顺利的。但后面修改特征提取sql时就遇到各种问题。问题根源是阿里云_数加平台基于的maxComputer组件的sql不是常规的sql，不支持update操作。之前sql大多使用了update（主要为了逻辑清晰，创建表后修改，减少中间表）。所以特征提取的sql需要重写。

 

官方文档

地址：https://help.aliyun.com/document_detail/27860.html?spm=5176.doc48950.6.601.yR58qr

和常用sql区别

官方文档给出的差异：https://help.aliyun.com/document_detail/50985.html?spm=5176.doc27860.6.623.0QzCDZ

 

简单整理下

01.表结构

不能修改分区列列名，只能修改分区列对应的值。

支持增加列，但是不支持删除列以及修改列的数据类型

 

02.INSERT

语法上最直观的区别是Insert into/overwrite后面有个关键字’Table’。

目前只支持Insert Into/Overwrite Table TableName Select的语法批量插入数据，还不支持Insert Into/Overwrite Table TableName Values(xxx)的语法。

注：常用的create table aaa select * from bbb;不行.

insert into aaa select * from bbb也不行。

insert into table aaa (select * from a union all select * from b)也不行（大坑），必须使用select * from (select * from a union all select * from b) t.外层嵌套的select*以及后面的表别名t都不能少。

 

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

这个和一般习惯一致，所以没大问题

 

03.UPDATE/DELETE

目前不支持Update/Delete语句，

注意：大坑，其他常规关系数据库可以跑的sql，迁移阿里云-数加后基本都要重写

 

04.SELECT

输入表的数量不能超过16张。

注：小坑，一般也用不大那么多表

Group by查询里的Select字段，要么是Group By的分组字段，要么需要使用聚合函数。从逻辑角度理解，如发现一个非分组列同一个Group By Key里的数据有多条，不使用聚合函数的话就没办法展示。

这个也符合日常习惯，没大问题

  

05.子查询

子查询必须要有别名。建议查询都带别名。

  

06.IN/NOT IN

关于In/Not In,Exist/Not Exist，后面的子查询数据量不能超过1000条，解决办法参考此文档。如果业务上已经保证了子查询返回结果的唯一性，可以考虑去掉Distinct增加查询性能。

 

注：not in和not exist这个需要留意下，自己写时避开了这种写法，可采用表join后判断字段为null的方式绕开in和exist，比较担心的万一超过1000但是没报错，程序逻辑不对就麻烦了。

SQL返回10000条

MaxCompute限制了单独执行select语句时返回的数据条数，用户可以参考此文档进行配置，设置上限为1万。

 

07.MAPJOIN

Join不支持笛卡尔积，也就是Join必须要用On设置关联条件。如果有一些小表需要做广播表，需要用Mapjoin Hint。

注意：大坑，很多where都要修改掉，一般修改为left outer join，注意outer不能少，否则也报错，这个和常规习惯不符合，一般都是left join的

 

08.ORDER BY

Order By 后面需要配合Limit n使用。如果希望做很大的数据量的排序，甚至需要做全表排序，可以把这个N设置的很大。不过请谨慎使用，因为无法使用到分布式系统的优势，可能会有性能问题。

 

09.UNION ALL

参与UNION ALL运算的所有列的数据类型、列个数、列名称必须完全一致，否则抛异常。

UNION ALL查询外面需要再嵌套一层子查询。

注意：大坑，union all 外面不止子查询，还要在后加别名，否则也报错，

 

10.其他

除此之外，还有部分问题，目前自己也不是很明白它具体怎么执行的，一个写法不行换个写法就ok了，比如

这个报错：

SELECT sum(if(behavior_type is NOT null,

        1,

        0) )

FROM

    (SELECT t1.user_id AS user_id,

        t1.item_id AS item_id

    FROM tianchi_mr_train_user_bt t1

    WHERE t1.bt_04='1'

            AND t1.datestr='2014-12-16') tt1（加left outer一样的问题）

JOIN

    (SELECT t2.behavior_type AS behavior_type

    FROM tianchi_mr_train_user_bt t2

    WHERE t2.datestr='2014-12-15' ) tt2

    ON tt1.user_id=tt2.user_id

        AND tt1.item_id=tt2.item_id

报错：Invalid column reference : line 11:35 'user_id'

 

这个ok：

SELECT sum(if(behavior_type is NOT null,

         1,

         0) )

FROM

    (SELECT t1.user_id AS user_id,

         t1.item_id AS item_id

    FROM tianchi_mr_train_user_bt t1

    WHERE t1.bt_04='1'

            AND t1.datestr='2014-12-16') tt1（加leftouter也ok，结果一样）

JOIN tianchi_mr_train_user_bt t2

    ON t2.datestr='2014-12-15'

        AND tt1.user_id=t2.user_id

        AND tt1.item_id=t2.item_id

 

两个没觉得有什么不同，

 

 

附录：

mysql迁移到数加平台：mysql导出为CSV，再导入天池

导出mysql

select * from train_user_bt  

into outfile 'd:/test.csv'  

fields terminated by ','  

lines terminated by '\r\n';

导入天池

tunnel upload d:\test.csv tianchi_mr_train_user_bt;

 

表建表

Create like+Insert into table xxx

或Create xx as select 也ok，比之前多各as