MaxCompute重装上阵 第五弹 – SELECT TRANSFOR

  1. awk 用户会好爱这个意义

中的VALUES (…), (…) t (a, b), 相当给概念了一个名为t,列为a,
b的申,类型为(a string, b
string),其中的花色从VALUES列表中演绎。这样在未准备任何物理表的时光,可以学一个生出自由数据的,多行的表明,并拓展任意运算。

理论及OpenMR的范都得以射到面的计算过程。注意,使用map,reduce,select
transform这几独语法其实语义是一律的,用谁要字,哪种写法,不影响一直过程及结果。

编译此脚本,可以考察执行计划如下

其三弹 – 复杂类型

使mytable2中的有id都不也NULL,则相当效于

其次弹 – 新的主干数据类和内建函数

得视,a对应的子查询只需要写一破,在末端重用,CTE的WITH字句中可指定多个子查询,像下变量一样以合讲话中屡屡用。除了用他,也无须还反复嵌套了。

正文也云栖社区原创内容,未经允许不得转载。返回搜狐,查看更多

setodps.sql.type.system.odps2=true;SELECTexplode(array(1,3))AS(a)UNIONALLSELECTexplode(array(0,2,4))AS(a)ORDERBYaLIMIT3;

或用map,reduce的要紧字会让逻辑显得清楚有

SELECT*fromsales_detailwheredsin(selectdtfromsales_date);

select transform (key, value) using “perl -e ‘while($input =
<STDIN>){print $input;}'” from src;

支持IMPLICIT JOIN

拖欠令兼容Hive的Transform功能,可以参照Hive的文档。一些需要小心的触发如下:

SELECT*FROMtable1, table2WHEREtable1.id = table2.id;

付出作业可看来实行计划(全部展开后的视图):

MaxCompute支持SQL标准的CTE。能够提高SQL语句的可读性和实践效率。

上次朝向您介绍了CTE,VALUES,SEMIJOIN,本篇向而介绍MaxCompute对另外脚本语言的支持

旁改善

图片 1

MaxCompute支持UNION [DISTINCT] – 其中DISTINCT为忽略

Select
transform允许sql用户指定在服务器上实施同一句shell命令,将上游数据列字段用tab分隔,每条记下一行,逐行输入shell命令的stdin,并起stdout读取数据作为出口,送及下游。Shell命令的本质是调动用Unix的有的utility,因此可启动其他的剧本解释器。包括python,java,php,awk,ruby等。

搬一个原本在Oracle上面的ETL系统,发现用了 WHERE EXISTS( SELECT
…) 和 WHERE IN (SELECT
…) 这好像的言语,可是发现ODPS在马上上面支撑非完,还要手工用这些半连的话语转换为平常JOIN,再淋。。。

此文中应用MaxCompute Studio作展示,首先,安装MaxCompute
Studio,导入测试MaxCompute项目,创建工程,建立一个初的MaxCompute脚本文件, 如下

等效于

责任编辑:

尽管如此相当于效于

第四弹 – CTE,VALUES,SEMIJOIN

实践后在,MaxCompute Project
Explorer中得找到新创的阐明,并观看values中之数现已插入到表中,如下:

图片 2

摘要: MaxCompute(原ODPS)是阿里云自主研发的富有业界领先水平的分布式大数额处理平台,
尤其在集团中得到广泛应用,支撑了大多单BU的主干业务。
MaxCompute除了无休止优化性能外,也操为提升SQL语言的用户体验以及表达能力,提高大面积ODPS开发者的生产力。

性能

SELECT*frommytable1 aLEFTSEMIJOINmytable2 bona.id=b.id;

采取场景举例

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

图片 3

SELECT * from mytable1 a LEFT SEMI JOIN mytable2 b on a.id=b.id;

  1. 无中生有前往数据

_急需写一个复现的SQL,
从多独表中读取数据,有些中做Join,有些中举行Union,生成中间数据同时如Join,
最后用输出多张表,最后写成了n层嵌套的子查询,自己尚且看不知情了。而且同的询问,在不同之子查询中发生再。为了保障好,把纷繁的说话拆成多单话,但是发现每个语句都用单独提交,排队,并且使拿中结果写及本不需要的临时表,在后的言语中重复念出来,慢了好多。。。

图片 4

LEFT SEMI JOIN

图片 5

好望,顶层的union两侧各为一个join,join的左表是一样之询问。通过写子查询的措施,只能再这段代码。

  1. UDTF是发档次,而Transform的子进程基被stdin/stdout传输数据,所有数据还当string处理,因此transform多矣同样步类型转换;
  2. Transform数据传依赖让操作系统的管道,而当前管道的buffer仅发生4KB,且未克设置,
    transform读/写 空/满 的pipe会导致进程被挂于;
  3. UDTF的常量参数可以免用传输,而Transform没办法用这优化。

SELECT*frommytable1 aLEFTANTIJOINmytable2 bona.id=b.id;

原本标题:MaxCompute重装及阵 第五弹 – SELECT TRANSFOR

例如:

性能达到,SELECT TRANSFORM 与UDTF
各发千秋。经过又景象对比测试,数据量较小时,大多数现象下select
transform有优势,而数据量大时UDTF有优势。由于transform的付出尤其便民,所以select
transform非常适合做adhoc的数码解析。

SELECT*FROMsrc1UNIONSELECT*FROMsrc2;

力排众议及select transform能促成的机能udtf都能够实现,但是select
transform比udtf要活得几近。且select
transform不仅支持java和python,还支持shell,perl等其它脚本和工具。
且编写的经过要简明,特别吻合adhoc功能的贯彻。举几个例证:

图片 6

  1. Using
    子句子指定的凡若履行的指令,而休资源列表,这或多或少同大部分之MaxCompute
    SQL语法不同等,这么做是为和hive的语法保持相当。

  2. 输入从stdin传入,输出从stdout传出;

  3. 可安排分隔符,默认使用 \t 分隔列,用换行分隔行;

  4. 得起定义reader/writer,但据此内置的reader/writer会快很多

  5. 行使从定义的资源(脚本文件,数据文件等),可以行使 set
    odps.sql.session.resources=foo.sh,bar.txt;
    来指定。可以指定多个resource文件,用逗号隔开(因此不允许resource名字中隐含逗号和分行)。此外我们还提供了resources子句,可以以using
    子句后指定 resources ‘foo.sh’, ‘bar.txt’
    来指定资源,两种办法是相当价格的(参考“用odps跑测试”的事例);

第四弹 –
CTE,VALUES,SEMIJOIN

或者

图片 7

是事例是为着验证,很多java的utility可以一直用来运行。java和python虽然发生备的udtf框架,但是就此select
transform编写更简单,并且不需格外依赖,也远非格式要求,甚至好兑现离线脚论以来一直就是因此。

当一个完全的查询语句被,例如

面的报告句之出同客起50实践之数据表,值是从1顶50;
测试时的数目就是得方便造出来了。功能相近简单,但以前是odps的一个痛点,没有福利之方法去数据,就无便民测试与初学者的学及探究。当然就也足以透过udtf来落实,但是急需复杂的流水线:进入ide->写udtf->打包->add
jar/python->create function->执行->drop function->drop
resource。

EXISTS SUBQUERY/NOT EXISTS SUBQUERY

6.
资源文件会受下充斥到执行指定命令的劳作目录,可以行使文件接口打开./bar.txt文件。

图片 8

SELECT TRANSFORM 介绍

例如:

图片 9

SELECT*frommytable1 aLEFTSEMIJOINmytable2 bona.id=b.id;

MaxCompute(原ODPS)是阿里云自主研发的具有业界领先水平的分布式大数据处理平台,
尤其在集团内部得到广泛应用,支撑了多只BU的主导业务。
MaxCompute除了无休止优化性能外,也行为提升SQL语言的用户体验和表达能力,提高大面积ODPS开发者的生产力。

小节

图片 10

2

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

SELECT*frommytable1whereidin(selectidfrommytable2wherevalue=
mytable1.value);

作者:隐林

例如:

  • 注一,USING
    后面的字符串,在后台是直接从的子进程来调动起命令,没有起shell,所以shell的少数语法,如输入输出重定向,管道等是免支持的。如果用户要好坐
    shell 作为命令,真正的下令作为数据输入,参考“无中生有过去数据”的例证;
  • 注二,JAVA 和 PYTHON 的实际上路径,可以从JAVA_HOME 和 PYTHON_HOME
    环境变量中取作业;

回来左表中之数据,当join条件不成立,也不怕是mytable1中某行的id在mytable2的装有id中无出现过,此行就保留在结果集中

  1. 用odps跑测试

对NOT IN SUBQUERY,类似于LEFT ANTI JOIN,但是生一些明明不同

  1. 好串联在用,使用 distribute by和 sort by对输入数据做预处理

SELECT*frommytable1whereidnotin(selectidfrommytable2);

图片 11

VALUES

MaxCompute基于ODPS2.0底SQL引擎,提供了SELECT
TRANSFORM功能,可以明确简化对台本代码的援,与此同时,也增长了性!我们引进你尽可能使SELECT
TRANSFORM。

中M1, M2,
M4老三个分布式任务分别对应相应三单输入表,双击M2可以视未遭现实执行之DAG(在DAG中再双击可以回),如下

小结

亟需先勾勒好FROM,再回头写SELECT列表,才能够唤起。如下

UDTF的优势:

SELECT*frommytable1wherenotexists(select*frommytable2whereid=
mytable1.id);`

要以python

一部分时候表的排好多,准备数据的时候想只插入部分列的数量,此时可用插队列表功能

率先弹 – 善用MaxCompute编译器的不当与警戒

里的ds如果是劈区列,则select dt from
sales_date 会单独启动作业执行子查询,而休会见转化为SEMIJOIN,执行后底结果碰头挨个与ds比较,sales_detail中ds值未在回结果被的分区不会见读取,保证分区裁剪仍然有效。

标注

等效于

  1. 支撑外脚本语言

SELECTkey,max(value)FROMsrc tWHEREvalue>0GROUPBYkeyHAVINGsum(value)
>100ORDERBYkeyLIMIT100;

图片 12

例如:

  • SELECT TRANSFORM。

  • 场景1

  • 自我的系统设迁移至MaxCompute平台上,系统受原本有很多功能是利用下本来好的,包括python,shell,ruby等剧本。
    要迁移至MaxCompute上,我欲把这些本子全部都改造成UDF/UDAF/UDTF。改造过程不仅用吃时间人力,还待举行同整个又同样整个的测试,从而确保改造成的udf和原来的剧本在逻辑上是相等价格的。我望会产生再简便的迁方式。
  • 场景2
  • SQL比较善于的是集聚操作,而自己得举行的业务如果针对同样漫长数据做还多的精致的精打细算,现有的置函数不克利的实现自己思要之效能,而UDF的框架不够灵活,并且Java/Python我都未极端熟悉。相比之下我还善于写剧本。我就冀能够写一个剧本,数据全输入到自之本子里来,我要好来举行各种计算,然后将结果输出。而MaxCompute平台就担帮助我拿数量做好切分,让自己的剧本能够分布式执行,负责数据的输入表和输出表的军事管制,负责JOIN,UNION等涉嫌操作就吓了。

实践的功能相当给

上述功能可以下SELECT TRANSFORM来落实

事实上的逻辑执行顺序是 FROM->WHERE->GROUY
BY->HAVING->SELECT->ORDER
BY->LIMIT,前一个凡是继一个的输入,与专业的写语序实际并不相同。很多便于混淆视听的问题,都是由此滋生的。例如order
by中只能引用select列表中生成的排,而非是访问FROM的源表中的排。HAVING可以看的是
group by key和聚合函数。SELECT的当儿,如果生GROUP BY,就只好看group
key和聚合函数,而非是FROM中源表中的排。

摘要:
MaxCompute(原ODPS)是阿里云自主研发的享有业界领先水平的分布式大数额处理平台,
尤其在集团中得到广泛应用,支撑了差不多单BU的核心工作。
MaxCompute除了连优化性能外,也操为提升SQL语言的用户体验以及表达能力,提高广大ODPS开发者的生产力。

UNION后LIMIT的语义变化。

SELECT TRANSFORM 的优势:

原有ODPS也支持[NOT] IN
SUBQUERY不作JOIN条件,例如出现于非WHERE语句被,或者虽然当WHERE语词被,但无能为力转移为JOIN条件。MaxCompute仍然支撑这种用法,但是此时因无法转换为SEMI
JOIN而要贯彻启动一个独立的作业来运转SUBQUERY,所以无支持correlated条件。

即odps select transform完全匹配了hive的语法、功能和行为,包括
input/output row format 以及
reader/writer。Hive上的脚本,大部分得一直将来运作,部分脚论就需要通过少改动即可运行。另外我们许多功力还为此比hive更强执行效率的言语
(C++) 重构,用以优化性能。

正开新类型,需要被一个略带数目表准备些基本数据,但是从未INSERT …
VALUES
语句子,没道把多少及创建表的DDL放在同保护,只好另用一些本子,调用ODPS命令执行准备数据。。。

方的言辞仅仅是拿value原样输出,但是熟悉awk的用户,从此过上了写awk脚本不写sql的日子

MaxCompute(原ODPS)是阿里云自主研发的装有业界领先水平的分布式大数额处理平台,
尤其在集团中得到广泛应用,支撑了大多单BU的着力业务。
MaxCompute除了连优化性能外,也操为提升SQL语言的用户体验和表达能力,提高周边ODPS开发者的生产力。

  1. 分进程以及父进程是零星个经过,而UDTF是单线程的,如果算占比较比较强,数据吞吐量比较粗,可以下服务器的多核特性
  2. 数据的传导通过重复底层的系调用来读写,效率比java高
  3. SELECT
    TRANSFORM支持之少数工具,如awk,是natvie代码实现之,和java相比理论及恐怕会见生出性能优势。

SELECT*FROMsrcUNIONALLSELECT*FROMsrc;

点用之凡perl。这实在不仅仅是言语支持的扩展,一些简单的作用,awk,
python, perl, shell
都支持直接以命令中写剧本,不待写脚论文件,上传资源相当进程,开发进程又简便易行。另外,由于目前我们计算集群达未曾php和ruby,所以就简单种脚本不支持。

斯功效要是便利从其他数据库系统迁移,对于信贷买,我们或引进你使用JOIN,明确表示意图

场景2

可是,如果mytable2中发生另为NULL的排列,则 not
in表达式会为NULL,导致where条件不立,无多少返回,此时和LEFT ANTI
JOIN不同。

MaxCompute支持以实行顺序书写查询语句,例如地方的言语可以写啊

支持新的SELECT语序

FROMsrc tWHEREvalue >0GROUPBYkeyHAVING sum(value) >100SELECTkey,
max(value)ORDERBYkeyLIMIT100;

若果使用上述以FROM起始之法子开,则好自然而然的基于上下文进行提示。如下

例如:

啊就是足以无写from语句,直接实施SELECT,只要SELECT的表达式列表不用外上游表数据就可。其底层实现啊打一个1行,0列的匿名VALUES表选取。这样,在巴测试一些函数,比如自己之UDF等,就还为无用手工创建DUAL表了。

创办一个新的文件,如下:

MaxCompute支持SEMI JOIN(半老是)。SEMI
JOIN中,右表只所以来过滤左表的数量如果非产生本结果集中。支持的语法包括LEFT
SEMI JOIN,LEFT ANTI JOIN,(NOT) IN SUBQUERY,(NOT) EXISTS

仅仅见面回到mytable1中之多少,只要mytable1的id在mytable2的id没有出现过

履之功用一定给

上次向您介绍了复杂类型,从本篇开始,向您介绍MaxCompute在SQL语言DML方面的改进

图片 13

图片 14

SELECTDISTINCT*FROM(SELECT*FROMsrc1UNIONALLSELECT*FROMsrc2) t;

回左表中的多寡,当join条件建立,也便是mytable1中某行的id在mytable2的有id中冒出了,此行就封存在结果集中

INSERT… VALUES…
有一个限制,values必须是常量,但是一些时候希望当插入的多寡中开展一些大概的演算,这个时刻可以以MaxCompute的VALUES
TABLE功能,如下:

MaxCompute采用基于ODPS2.0底SQL引擎,对DML进行了大幅扩充,提高了易用性和兼容性,基本化解了上述问题。

SELECT*frommytable1whereexists(select*frommytable2whereid=
mytable1.id);`

除此以外当处理分区表底时节,也会见出奇处理

率先弹 –
善用MaxCompute编译器的不当和警告

例如:

对于以values中并未制定的排,可以看来取缺省值为NULL。插入列表功能不肯定同VALUES一起就此,对于INSERT
INTO … SELECT…, 同样可以使用。

LEFT ANTI JOIN

SELECT*frommytable1 aLEFTANTIJOINmytable2 bona.id=b.id;

实则,VALUES表并无压在INSERT语句被利用,任何DML语句都得采用。

场景3

当SUBQUERY被生出至少一行数上,返回TRUE,否则FALSE。NOT
EXISTS的上虽然相反。目前特支持含有correlated WHERE条件的子查询。EXISTS
SUBQUERY/NOT EXISTS SUBQUERY实现之计是更换为LEFT SEMI JOIN或者LEFT
ANTI JOIN

想念测试一个初写的UDF,只写SELECT
myudf(‘123’);会报错,还须创造一个dual表,里面加一行数,好烦。如果测试UDAF,还要当测试表里面准备多行数据,每次测试不同的输入还要修改表内容或创造新表,如果出只办法不用创建表也克例外之数据整合测试自己的UDF就哼了。。。

支撑顶层UNION

独见面回去mytable1中之数码,只要mytable1的id在mytable2的id中冒出了

老二弹 –
新的中心数据类和内建函数

图片 15

0

图片 16

写顺序与施行各个一致,就未易于混淆视听了。这样发生一个格外的利,在MaxCompute
Studio中描绘SQL语句的下,会时有发生智能提醒的效能,如果是SELECT在前,书写select列表的表达式的时节,因为FROM还没有写,MaxCompute
Studio没办法知道或者拜会那些列,也即未可知举行提示。如下

而外,针对MaxCompute用户之特点,也就是是需要在非常复杂的业务场景下,支持对自家大量数额的拍卖,MaxCompute提供了故意的本子模式和参数化视图,将于产一致次等也你介绍。

IN SUBQUERY/NOT IN SUBQUERY

selectabs(-1),length(‘abc’),getdate();

图片 17

老ODPS也支撑IN SUBQUERY,但是未支持correlated条件,MaxCompute支持

SELECT*frommytable1whereidin(selectidfrommytable2);

返回

大部DBMS系统受到,如MySQL,Hive等,UNION后要来CLUSTER BY, DISTRIBUTE
BY, SORT BY, ORDER
BY或者LIMIT子句,其用意为同眼前所有UNION的结果,而休是UNION的末尾一块。ODPS2.0在set
odps.sql.type.system.odps2=true;的上,也使这作为。例如:

图片 18

老三弹 –
复杂类型

可以视对src读后展开过滤的DAG。对src的读取与过滤在方方面面实施计划遭到只需要平等次于
注1 )。

应用CTE的法子更写以上语句

履后,MaxCompute Project
Explorer中找到目标表,并探望values中之数据现已插入,如下:

是不是统一或者分裂子查询,是出于ODPS2.0的冲代价的优化器
(CBO)做出决定的,SQL本身的书道,不管是CTE还是分段查询,并无能够保证物理执行计划的统一或者分裂。

标注

场景1 

SEMI JOIN

MaxCompute大大扩大了DML语句之支撑,在易用性,兼容性及性方面,可以重好之满足你的需要。对于SQL比较熟悉的家会发现,上述功效大部分是标准的SQL支持之意义。MaxCompute会持续升级以及专业SQL和业界常用产品的兼容性。

注1

IN SUBQUERY与LEFT SEMI JOIN类似。

ODPS1.0免支持顶层UNION。ODPS2.0足支撑,例如

SELECT*FROMtable1JOINtable2ONtable1.id = table2.id;

图片 19

Common Table Expression (CTE)

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

还有平等种植VALUES表的例外形式

a

图片 20

场景4

SELECT*frommytable1 aLEFTANTIJOINmytable2 bona.id=b.id;

坐WHERE中含有了OR,导致无法换为SEMI JOIN,会独自启动作业执行子查询

SELECT*frommytable1whereidin(selectidfrommytable2)ORvalue>0;

图片 21

其中子查询中的where value =
mytable1.value虽是一个correlated条件,原有ODPS对于这种既引用了子查询中源表,由引用了外围查询源表的表达式时,会报告错误。MaxCompute支持这种用法,这样的过滤条件实在构成了SEMI
JOIN中的ON条件的平等片。

例如:

1

发表评论

电子邮件地址不会被公开。 必填项已用*标注