彰起首册,ODPS重装上阵

原标题:马克斯Compute重装上阵 第五弹 – SELECT TRANSFOR

摘要: 马克斯(Max)Compute(原ODPS)是阿里云自主研发的富有业界超越水平的分布式大数目处理平台,
尤其在集团内部获得广泛应用,支撑了五个BU的主导工作。
马克斯Compute除了不停优化性能外,也从事于升高SQL语言的用户体验和表明能力,进步周边ODPS开发者的生产力。

摘要: 大数据测算服务 马克斯Compute
可以提供强大的分析能力,而分布式 NoSQL
数据库表格存储在行级别上的实时更新和可覆盖性写入等风味,相对于
马克斯Compute 内置表 append-only 批量操作,提供了一个很好的填补。

2017/12/20 巴黎云栖大会上阿里云马克斯Compute公布了新式的功能Python
UDF,万众期待的成效终于匡助啦,我怎么能不一试为快,明日就享受什么通过Studio举办Python
udf开发。

摘要:
马克斯(Max)Compute(原ODPS)是阿里云自主研发的有所业界超过水平的分布式大数目处理平台,
尤其在公司内部获得广泛应用,支撑了五个BU的主导工作。
马克斯Compute除了无休止优化性能外,也从事于提高SQL语言的用户体验和表明能力,升高周边ODPS开发者的生产力。

马克斯Compute(原ODPS)是阿里云自主研发的具备业界超越水平的分布式大数据处理平台,
尤其在公司内部得到广泛应用,支撑了八个BU的主干业务。
马克斯Compute除了无休止优化性能外,也从事于提高SQL语言的用户体验和表达能力,升高大面积ODPS开发者的生产力。

关全面据库已经存在半个世纪,有不行广泛的使用情状,不过在急忙迭代的互联网世界其伸张性和
schema 灵活性被诟病颇多,由此类似 TableStore/BigTable/HBase
等强调增加性和灵活性的NoSQL数据库逐步流行起来,那几个 NoSQL 数据库只提供
API 接口,不提供 SQL 访问,那就招致不可胜道熟知 SQL
不过不爱好写代码的用户没办法很清爽的行使此类NoSQL数据库。基于此,表格存储开发社团共同
马克斯Compute(下文中 ODPS 与 马克斯(Max)Compute 同义)团队发掘了 ODPS-SQL
访问表格存储的门径,那样一个只懂 SQL
的用户也得以掀拳裸袖的造访表格存储其中的大气数码了。

 

马克斯(Max)Compute(原ODPS)是阿里云自主研发的所有业界超过水平的分布式大数据处理平台,
越发在集团内部得到广泛应用,支撑了四个BU的为主业务。
马克斯(Max)Compute除了不停优化性能外,也从事于提高SQL语言的用户体验和表明能力,提升大面积ODPS开发者的生产力。

马克斯(Max)Compute基于ODPS2.0新一代的SQL引擎,显著提高了SQL语言编译进程的易用性与语言的表明能力。大家在此推出马克斯Compute(ODPS2.0)重装上阵连串文章

本篇文章就以一个小白用户的地位体验怎么样行使
马克斯(Max)Compute-SQL 查询表格存储其中的多寡,以及如何支付自定义逻辑(User
Defined Function, UDF)来拍卖用户特定的数量格式。

置于条件

马克斯(Max)Compute基于ODPS2.0新一代的SQL引擎,明显进步了SQL语言编译进度的易用性与语言的表达能力。大家在此推出马克斯Compute(ODPS2.0)重装上阵连串作品

首先弹 –
善用马克斯(Max)Compute编译器的荒唐和警告

什么是表格存储 TableStore?

 

率先弹 – 善用马克斯Compute编译器的错误和警示

其次弹 –
新的着力数据类型与内建函数

分布式NoSQL数据存储服务,无缝援救单表PB级数据及百万级访问出现,弹性资源,按量计费,对数据高频的增、删、改协助的很好,保障单行数据读写的强一致性。

打探到,就算作用公布,然而还在公测阶段,假设想要使用,还得申请开通:。那里自己就不介绍申请开通具体流程了。

第二弹 – 新的基本数据类型与内建函数

其三弹 –
复杂类型

怎么样是大数量统计服务 马克斯Compute?

环境准备

马克斯Compute Studio接济Python UDF开发,前提须求设置python,
pyodps和idea的python插件。

  1. 设置Python:可以谷歌或者百度查寻下如何设置。
  2. 安装pyodps:可以参照python
    sdk文档的安装步骤。即,在
    Python 2.6 以上(包罗 Python 3),系统装置 pip 后,只需运行下 pip
    install pyodps,PyODPS 的相关信赖便会自动安装。
  3. AMDlij IDEA中设置Python插件。搜索Python Community
    Edition插件并设置
  4. 美高梅4858官方网站 1
  5. 配置studio module对python的依赖。

  6.  

    • File -> Project structure,添加python sdk:
    • 美高梅4858官方网站 2
    • File -> Project structure,添加python facets:
      美高梅4858官方网站 3
    • File -> Project structure,配置module依赖python facets:
      美高梅4858官方网站 4

其三弹 – 复杂类型

第四弹 –
CTE,VALUES,SEMIJOIN

一种高效、完全托管的TB/PB级数据仓库解决方案,提供多种经典的分布式总结模型,可以更神速的化解用户海量数据测算问题。

开发Python UDF

环境都准备好后,既可在对应看重的module里创制进行python udf开发。

第四弹 – CTE,VALUES,SEMIJOIN

上次向你介绍了复杂类型,从本篇初始,向你介绍马克斯(Max)Compute在SQL语言DML方面的改正

上面首先大家将介绍环境准备,那是怀有前面的操作的底蕴。然后会介绍使用
OdpsCmd
访问表格存储。在首节大家介绍使用 OdpsStudio
访问表格存储。最终介绍怎么样写 UDF、计划 UDF 以及在查询中利用 UDF。

新建python脚本。

右键 new | 马克斯(Max)Compute Python,弹框里输入脚本名称,选拔连串为python udf:

美高梅4858官方网站 5

转移的模板已自行填充框架代码,只须要编制UDF的入参出参,以及函数逻辑:
美高梅4858官方网站 6

上次向你介绍了CTE,VALUES,SEMIJOIN,本篇向您介绍马克斯Compute对此外脚本语言的接济

场景1 

环境准备

当地调试

代码开发好后,可以在Studio中举行地面调试。Studio帮忙下载表的一部分sample数据到地点运行,举行debug,步骤如下:

  1. 右键python udf类,点击”运行”菜单,弹出run
    configuration对话框。UDF|UDAF|UDTF一般意义于select子句中表的一点列,此处需配备马克斯(Max)Compute
    project,table和column(元数据出自project
    explorer窗口和warehouse下的example项目):
    美高梅4858官方网站 7
  2. 点击OK后,通过tunnel自动下载指定表的sample数据到地头warehouse目录(若以前已下载过,则不会重新重新下载,否则利用tunnel服务下载数据。默许下载100条,如需越来越多数据测试,可活动行使console的tunnel命令或者studio的表下载功效)。下载完毕后,可以在warehouse目录看到下载的sample数据。那里用户也得以行使warehouse里的数目进行调试,具体可参考java
    udf开发中的有关地点运行的warehouse目录”部分)。
  3. 美高梅4858官方网站 8
  4. 接下来本地运行框架会基于指定的列,获取data文件里指定列的多寡,调用UDF本地运行。
    美高梅4858官方网站 9
  • SELECT TRANSFORM。

  • 场景1

  • 自我的系统要搬迁到马克斯(Max)Compute平台上,系统中原来有不少功力是选用脚本来已毕的,包蕴python,shell,ruby等剧本。
    要迁移到马克斯(Max)Compute上,我索要把这几个本子全体都改造成UDF/UDAF/UDTF。改造进度不仅必要费用时间人力,还索要做四次又几回的测试,从而有限扶助改造成的udf和原先的本子在逻辑上是等价的。我梦想能有更简明的迁徙情势。
  • 场景2
  • SQL比较善于的是汇集操作,而我急需做的事情要对一条数据做更多的精密的持筹握算,现有的放权函数不可以有利于的兑现自身想要的功能,而UDF的框架不够灵活,并且Java/Python我都不太熟识。比较之下我更擅长写剧本。我就希望可以写一个本子,数据全都输入到自我的脚本里来,我要好来做各个计算,然后把结果输出。而MaxCompute平台就背负帮自己把数量做好切分,让自己的台本可以分布式执行,负责数据的输入表和输出表的管理,负责JOIN,UNION等关系操作就好了。

_亟待写一个复现的SQL,
从五个表中读取数据,有些之间做Join,有些之间做Union,生成中间数据又要Join,
最后索要输出多张表,最终写成了n层嵌套的子查询,自己都看不懂了。而且同样的查询,在差距的子查询中有双重。为了掩护方便,把复杂的讲话拆成多个语句,可是发现每个语句都亟待独自提交,排队,并且要将中等结果写到本来不要求的临时表,在末端的言语中再读出来,慢了很多。。。

第一,准备好一个 马克斯(Max)Compute 的工程,工程创立率领文档,准备好AccessId和AccessKey备用,为了分歧其他产品的AccessId和AccessKey,前边大家称之为ODPS-AccessId,ODPS-AccessKey。并在RAM中授权
马克斯(Max)Compute 访问 TableStore 的权力,授权方式请参见MaxCompute访问TableStore数据——授权

挂号公布Python UDF

  1. 代码调试好后,将python脚本添加为马克斯(Max)Compute的Resource:
    美高梅4858官方网站 10

只顾此处选用的马克斯(Max)Compute project必须是早已提请开展python
udf的project。

  1. 注册python 函数:
    美高梅4858官方网站 11
  2. 在sql脚本中编辑马克斯(Max)Compute sql试用python udf:
    美高梅4858官方网站 12

原稿链接:

上述意义能够选择SELECT TRANSFORM来落到实处

场景2

小提示:由于 马克斯Compute 在 2.0
版本的乘除框架才能支撑直接访问 TableStore
数据,该版本还在灰度上线中,近年来还必要 申请MaxCompute
2.0试用
,具体开通使用方式请参见 怎么申请试用马克斯(Max)Compute
2.0。

SELECT TRANSFORM 介绍

正在开发新项目,须要给一个小数码表准备些基本数据,不过尚未INSERT …
VALUES
语句,不可能把数量和成立表的DDL放在一块儿爱戴,只可以另用一些剧本,调用ODPS命令行准备数据。。。

接下来,准备好一个表格存储的实例以及一张数据表,报表存储实例管理,准备好实例名、EndPoint,为了不相同别的产品的AccessId和AccessKey,后边大家称之为TableStore-InstanceName,TableStore-EndPoint。

此文中应用马克斯Compute Studio作体现,首先,安装马克斯(Max)Compute
Studio,导入测试马克斯Compute项目,创制工程,建立一个新的马克斯(Max)Compute脚本文件, 如下

场景3

网络连通性

美高梅4858官方网站 13

想测试一个新写的UDF,只写SELECT
myudf(‘123’);会报错,还非得成立一个dual表,里面加一行数据,好劳累。假诺测试UDAF,还要在测试表里面准备多行数据,每趟测试区其余输入都要修改表内容依旧创建新表,若是有个办法不用创立表也能例外的数目整合测试自己的UDF就好了。。。

马克斯Compute 与 TableStore
是多少个单身的大数额计算以及大数额存储服务,所以两者之间的网络必须确保连通性。
对于 马克斯(Max)Compute 公共云服务走访 TableStore 存储,推荐应用 TableStore
私网
地址,例如

付给作业可以见见举办安排(全部举行后的视图):

场景4

倘使网络不通,可以使用公网地址,TableStore原生帮忙 VPC
网络决定,也急需将网络项目设置为 “允许擅自网络访问”

美高梅4858官方网站 14

搬迁一个原先在Oracle上边的ETL系统,发现用了 WHERE EXISTS( SELECT
…) 和 WHERE IN (SELECT
…) 那类的语句,可是发现ODPS在那上面扶助不完整,还要手工将这个半两次三番的言语转换为常见JOIN,再过滤。。。

 

Select
transform允许sql用户指定在服务器上执行一句shell命令,将上游数据各字段用tab分隔,每条记下一行,逐行输入shell命令的stdin,并从stdout读取数据作为出口,送到下游。Shell命令的原形是调用Unix的一部分utility,因而可以启动其余的脚本解释器。包蕴python,java,php,awk,ruby等。

马克斯(Max)Compute接纳基于ODPS2.0的SQL引擎,对DML进行了大幅伸张,提升了易用性和包容性,基本化解了上述问题。

使用客户端 ODPS-CMD

该命令包容Hive的Transform功效,可以参见Hive的文档。一些亟待注意的点如下:

Common Table Expression (CTE)

1.下载并安装大数据测算服务客户端

  1. Using
    子句指定的是要实践的授命,而非资源列表,这点和多数的马克斯(Max)Compute
    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跑测试”的事例);

马克斯(Max)Compute协理SQL标准的CTE。可以加强SQL语句的可读性与执行功效。

彰起首册,ODPS重装上阵。2.下载解压,将conf/odps_config.ini
的始末改动为:

6.
资源文件会被下载到执行指定命令的行事目录,可以运用文件接口打开./bar.txt文件。

此文中拔取马克斯Compute Studio作体现,首先,安装MaxCompute
Studio,导入测试马克斯Compute项目,创造工程,建立一个新的马克斯(Max)Compute脚本文件, 如下

project_name=上面申请的ODPS工程名

眼前odps select transform完全同盟了hive的语法、功效和表现,包含input/output row format 以及
reader/writer。Hive上的剧本,半数以上得以从来拿来运行,部分脚本只需要通过不难改动即可运行。此外咱们不少成效都用比hive更高执行功用的语言
(C++) 重构,用以优化性能。

美高梅4858官方网站 15

access_id=ODPS-AccessId

选取场景举例

可以观察,顶层的union两侧各为一个join,join的左表是均等的查询。通过写子查询的法子,只能够重复那段代码。

access_key= ODPS-AccessKey

辩论上select transform能兑现的效率udtf都能落实,但是select
transform比udtf要灵活得多。且select
transform不仅襄助java和python,还辅助shell,perl等其余脚本和工具。
且编写的经过要简单,尤其适合adhoc功用的落到实处。举多少个例证:

行使CTE的主意重写以上语句

end_point=

  1. 兴妖作怪造数据

美高梅4858官方网站 16

https_check=true

美高梅4858官方网站 17

可以观察,a对应的子查询只需求写五次,在背后重用,CTE的WITH字句中得以指定三个子查询,像使用变量一样在方方面面讲话中频仍重用。除了重用外,也不必再频仍嵌套了。

# confirm threshold for query input size(unit:
GB)

照旧选用python

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

data_size_confirm=100.0

美高梅4858官方网站 18

美高梅4858官方网站 19

# this url is for odpscmd update

下边的语句造出一份有50行的数据表,值是从1到50;
测试时候的数码就足以方便造出来了。成效相近简单,但原先是odps的一个痛点,没有福利的章程造数据,就不便民测试以及初学者的上学和追究。当然那也得以通过udtf来落实,不过急需复杂的流程:进入ide->写udtf->打包->add
jar/python->create function->执行->drop function->drop
resource。

其中M1, M2,
M4多少个分布式职责分别对应相应多少个输入表,双击M2可以看出中切实实施的DAG(在DAG中重复双击可以回到),如下

update_url=

  1. awk 用户会很欢跃这些作用

美高梅4858官方网站 20

3.行bin/odpscmd,输入show
tables,正常实施则意味着上边配置不错。

美高梅4858官方网站 21

能够看看对src读后举行过滤的DAG。对src的读取与过滤在全体实施安顿中只必要一遍( 注1 )。

 

下面的讲话仅仅是把value原样输出,不过熟谙awk的用户,从此过上了写awk脚本不写sql的小日子

VALUES

4.在bin/odpscmd 下输入环境变量,显式开启 ODPS 2.0
的非结构化成效( 仅在 ODPS 2.0 总结框架完全上线为必须),单独实施
xx.sql 文件时也须求将下面设置写在 SQL 文件的开首处。

  1. 用odps跑测试

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

set odps.task.major.version=2dot0_demo_flighting;

美高梅4858官方网站 22

美高梅4858官方网站 23

set
odps.sql.planner.mode=lot;

或者

实践后在,马克斯Compute Project
Explorer中得以找到新创立的表,并探望values中的数据已经插入到表中,如下:

set odps.sql.ddl.odps2=true;

美高梅4858官方网站 24

美高梅4858官方网站 25

set
odps.sql.preparse.odps2=lot;

这一个事例是为着表达,很多java的utility可以一向拿来运作。java和python即使有现成的udtf框架,不过用select
transform编写更简约,并且不必要相当依赖,也不曾格式须求,甚至足以兑现离线脚本拿来平昔就用。

有些时候表的列很多,准备数据的时候希望只插入部分列的数码,此时得以用插队列表功能

set odps.sql.type.system.odps2=true; –是永葆表格存储的binary类型

  1. 支撑任何脚本语言

美高梅4858官方网站 26

5.创立一张 马克斯(Max)Compute 的数据表关联到 TableStore
的某一张表。

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

实施后,马克斯Compute Project
Explorer中找到目标表,并察看values中的数据已经插入,如下:

涉及的数据表音讯如下:

上边用的是perl。那其实不仅是言语协助的伸张,一些概括的功能,awk,
python, perl, shell
都协理直接在指令里面写剧本,不须要写脚本文件,上传资源等进度,开发进度更简短。此外,由于近年来我们总计集群上从未有过php和ruby,所以这三种脚本不接济。

美高梅4858官方网站 27

  • 实例名称:vehicle-test
  • 数码表名称:vehicle_track
  • 主键音信:vid(int); gt (int)
  • 访问域名:https://vehicle-test.cn-shanghai.ots-internal.aliyuncs.com
  1. 可以串联着用,使用 distribute by和 sort by对输入数据做预处理

对于在values中从未制定的列,可以看到取缺省值为NULL。插入列表成效不自然和VALUES一起用,对于INSERT
INTO … SELECT…, 同样可以选拔。

DROP TABLE IF EXISTS
ots_vehicle_track;

美高梅4858官方网站 28

INSERT… VALUES…
有一个限制,values必须是常量,然而部分时候希望在插入的数额中开展一些大约的演算,这几个时候可以动用马克斯(Max)Compute的VALUES
TABLE功用,如下:

 

抑或用map,reduce的最首要字会让逻辑显得清楚一些

美高梅4858官方网站 29

CREATE EXTERNAL TABLE IF NOT
EXISTS
ots_vehicle_track

美高梅4858官方网站 30

其中的VALUES (…), (…) t (a, b), 相当于概念了一个名为t,列为a,
b的表,类型为(a string, b
string),其中的品种从VALUES列表中演绎。那样在不准备任何物理表的时候,可以效仿一个有自由数据的,多行的表,并开展任意运算。

(

辩护上OpenMR的模子都能够映射到下面的测算进程。注意,使用map,reduce,select
transform这多少个语法其实语义是一模一样的,用哪个关键字,哪一类写法,不影响一向进程和结果。

骨子里,VALUES表并不限于在INSERT语句中动用,任何DML语句都可以选用。

vid bigint,

性能

再有一种VALUES表的特殊方式

gt bigint,

特性上,SELECT TRANSFORM 与UDTF
各有千秋。经过多种场馆相比测试,数据量较小时,大部分场景下select
transform有优势,而数据量大时UDTF有优势。由于transform的支出越发方便,所以select
transform非凡适合做adhoc的数量解析。

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

longitude double,

UDTF的优势:

也就是足以不写from语句,直接执行SELECT,只要SELECT的表明式列表不用其他上游表数据就足以。其底层达成为从一个1行,0列的匿名VALUES表选用。那样,在盼望测试一些函数,比如自己的UDF等,就再也不用手工成立DUAL表了。

latitude double,

  1. UDTF是有品种,而Transform的子进度基于stdin/stdout传输数据,所有数据都看成string处理,由此transform多了一步类型转换;
  2. Transform数据传输看重于操作系统的管道,而眼前管道的buffer仅有4KB,且无法安装,
    transform读/写 空/满 的pipe会导致进程被挂起;
  3. UDTF的常量参数可以不用传输,而Transform不能利用那么些优化。

SEMI JOIN

distance double
,

SELECT TRANSFORM 的优势:

马克斯Compute支持SEMI JOIN(半一而再)。SEMI
JOIN中,右表只用来过滤左表的数目而不出现在结果集中。接济的语法包罗LEFT
SEMI JOIN,LEFT ANTI JOIN,(NOT) IN SUBQUERY,(NOT) EXISTS

speed double,

  1. 子进程和父进度是八个进程,而UDTF是单线程的,若是总计占比相比高,数据吞吐量相比较小,可以动用服务器的多核特性
  2. 数量的传导通过更底层的体系调用来读写,效用比java高
  3. SELECT
    TRANSFORM协理的少数工具,如awk,是natvie代码完成的,和java相比较理论上也许会有性能优势。

LEFT SEMI JOIN

oil_consumption double

小结

回去左表中的数据,当join条件建立,也就是mytable1中某行的id在mytable2的所有id中出现过,此行就保存在结果集中

)

MaxCompute基于ODPS2.0的SQL引擎,提供了SELECT
TRANSFORM作用,可以显然简化对台本代码的引用,与此同时,也增强了性能!大家引进您尽可能利用SELECT
TRANSFORM。

例如:

STORED BY ‘com.aliyun.odps.TableStoreStorageHandler’
— (1)

标注

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

WITH SERDEPROPERTIES ( — (2)

  • 注一,USING
    前边的字符串,在后台是间接起的子进度来调起命令,没有起shell,所以shell的某些语法,如输入输出重定向,管道等是不帮助的。即便用户须要可以以
    shell 作为命令,真正的命令作为数据输入,参考“惹是生非造数据”的例子;
  • 注二,JAVA 和 PYTHON 的骨子里路径,可以从JAVA_HOME 和 PYTHON_HOME
    环境变量中取得作业;

只会回去mytable1中的数据,只要mytable1的id在mytable2的id中冒出过

‘tablestore.columns.mapping’=’:vid, :gt, longitude, latitude, distance, speed,
oil_consumption’, —
(3)

作者:隐林

LEFT ANTI JOIN

‘tablestore.table.name’=’vehicle_track’ —
(4)

本文为云栖社区原创内容,未经同意不得转发。回到微博,查看越多

归来左表中的数据,当join条件不创立,也就是mytable1中某行的id在mytable2的兼具id中尚无出现过,此行就保存在结果集中

)

义务编辑:

例如:

LOCATION ‘tablestore://vehicle-test.cn-shanghai.ots-internal.aliyuncs.com’; — (5)

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

  • com.aliyun.odps.TableStoreStorageHandler 是 马克斯Compute
    内置的拍卖 TableStore 数据的 StorageHandler, 定义了 马克斯(Max)Compute 和
    TableStore 的互相,相关逻辑由 马克斯Compute 已毕。
  • SERDEPROPERITES
    可以了解成提供参数选项的接口,在动用 TableStoreStorageHandler
    时,有五个必须指定的选项,分别是上边介绍的
    tablestore.columns.mapping 和 tablestore.table.name。
    更加多的可选选项将在后头其他例子中提及。
  • tablestore.columns.mapping
    选项:必需选项,用来描述对亟待 马克斯(Max)Compute 将造访的 TableStore
    表的列,包涵主键和属性列。 那中间以 : 打头的用来代表 TableStore
    主键,例如这一个事例中的 :vid:gt。 其余的均为属性列。
    TableStore协理最少1个,最多4个主键,主键类型为 bigint 或
    string,其中第二个主键为分区键。 在指定映射的时候,用户必须提供指定
    TableStore 表的
    有着主键,对于属性列则没有必要全体提供,可以只提供必要经过
    马克斯(Max)Compute 来访问的属性列。
  • tablestore.table.name:需求拜访的 TableStore 表名。
    若是指定的 TableStore 表名错误(不存在),则会报错,马克斯(Max)Compute
    不会百尺竿头更进一步去创设 TableStore 表。
  • LOCATION 用来指定访问的 TableStore 的实例新闻,包罗instance 名字,endpoint 等。 
  • 数量格式对应,马克斯Compute 与 TableStore
    的数目格式对应如下:

只会回到mytable1中的数据,只要mytable1的id在mytable2的id没有出现过

TableStore数据类型

MaxCompute数据类型

string

string

binary

blob

int

bigint

double

double

IN SUBQUERY/NOT IN SUBQUERY

6.执行ODPS-SQL

IN SUBQUERY与LEFT SEMI JOIN类似。

// 统计编号 4 以下的车子在岁月戳 1469171387
以前的平分速度和平均油耗

例如:

select vid,count(*),avg(speed),avg(oil_consumption) from
ots_vehicle_track where vid
<4 and gt<1469171387  group by
vid;

SELECT*frommytable1whereidin(selectidfrommytable2);

 

等效于

利用 UDF(User Defined Function)处理数量

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

奇迹用户在报表存储其中的数量有万分的协会,希望自己开销逻辑来处理每一行数据,比如解析特定的json字符串,这一块的支付也已经很有益了。

原始ODPS也支撑IN SUBQUERY,可是不协助correlated条件,马克斯(Max)Compute协理

1.按照MaxCompute
Studio文档的求证在AMDliJ里面安装MaxCompute-Java/马克斯(Max)Compute-Studio插件,一旦插件安装收尾,就可以直接支付。

例如:

譬如说上面就是一个粗略的 UDF
定义,只是不难的将两个字符串连接。 MaxCompute
援助更扑朔迷离的UDF,包含自定义窗口举行逻辑等,详细请参见MaxCompute Studio-开发
UDF。

SELECT*frommytable1whereidin(selectidfrommytable2wherevalue=
mytable1.value);

 

其中子查询中的where value =
mytable1.value就是一个correlated条件,原有ODPS对于那种既引用了子查询中源表,由引用了外围查询源表的表达式时,会告诉错误。马克斯Compute支持那种用法,那样的过滤条件实在构成了SEMI
JOIN中的ON条件的一有些。

2.封装之后可以上传到
马克斯Compute,其中打包这里有亟待专注的地点,File->Project
Structure->Artifacts, 填写好 Name 和 Output Directory 后,要点击
+ 接纳输出模块,打包后经过 ODPS Project Explorer
来上传资源、创立函数,然后就可以在SQL中调用。

对此NOT IN SUBQUERY,类似于LEFT ANTI JOIN,可是有几许令人惊叹不一样

美高梅4858官方网站 , 

例如:

3.打开bin/odpscmd,输入

SELECT*frommytable1whereidnotin(selectidfrommytable2);

//
大家选出来1行数码,并将name/name传入UDF,重临多个string的丰硕

若果mytable2中的所有id都不为NULL,则等效于

select
cloud_metric_extract_md5(name,
name) as udf_test from test_table
limit 1;

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

 

但是,若是mytable2中有其他为NULL的列,则 not
in表明式会为NULL,导致where条件不树立,无多少再次回到,此时与LEFT ANTI
JOIN不一致。

写在结尾

原有ODPS也支持[NOT] IN
SUBQUERY不作为JOIN条件,例如出现在非WHERE语句中,或者纵然在WHERE语句中,但无能为力转移为JOIN条件。马克斯(Max)Compute照旧支撑那种用法,可是此时因为无法变换为SEMI
JOIN而必须兑现启动一个独门的学业来运作SUBQUERY,所以不援助correlated条件。

脚下ODPS-SQL访问 TaleStore
还在对实践逻辑举行深度的优化,若是有需要请联系ots_support,我们将针对工作场景来展开优化。

例如:

FAQ

SELECT*frommytable1whereidin(selectidfrommytable2)ORvalue>0;

广大错误处理:

因为WHERE中蕴藏了OR,导致不可以转移为SEMI JOIN,会独自启动作业执行子查询

  1. FAILED: ODPS-0010000:System internal error – fuxi
    job failed, WorkerPackageNotExist:须求设置set
    odps.task.major.version=unstructured_data
  2. FAILED: ODPS-0010000:System internal error –
    std::exception:Message: a timeout was
    reached:一般情形下是OTS的endpoint填写错误,导致ODPS没办法访问,可以咨询ots_support旺旺账号
  3. logview invalid
    end_point:在履行进度中,会回来一个logview
    URL地址,即使采取浏览器访问该地址重回错误,可能是布署不对,请检查
    马克斯Compute 配置,并咨询 odps_support 旺旺账号

除此以外在处理分区表的时候,也会有独特处理

SELECT*fromsales_detailwheredsin(selectdtfromsales_date);

其中的ds固然是分区列,则select dt from
sales_date 会单独启动作业执行子查询,而不会转化为SEMIJOIN,执行后的结果会挨个与ds相比,sales_detail中ds值不在再次来到结果中的分区不会读取,保障分区裁剪仍旧有效。

EXISTS SUBQUERY/NOT EXISTS SUBQUERY

当SUBQUERY中有最少一行数据时候,重临TRUE,否则FALSE。NOT
EXISTS的时候则相反。方今只援助含有correlated WHERE条件的子查询。EXISTS
SUBQUERY/NOT EXISTS SUBQUERY落成的方法是更换为LEFT SEMI JOIN或者LEFT
ANTI JOIN

例如:

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

等效于

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

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

则等效于

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

其余改进

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

SELECT*FROMsrc1UNIONSELECT*FROMsrc2;

施行的效益相当于

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

支持IMPLICIT JOIN

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

实践的意义一定于

SELECT*FROMtable1JOINtable2ONtable1.id = table2.id;

此意义重如若造福从其余数据库系统迁移,对于信贷买,大家照旧引进您使用JOIN,明确表示意图

支撑新的SELECT语序

在一个完全的查询语句中,例如

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

实际的逻辑执行各类是 FROM->WHERE->GROUY
BY->HAVING->SELECT->ORDER
BY->LIMIT,前一个是后一个的输入,与正式的书写语序实际并差别。很多不难混淆的题目,都是因而滋生的。例如order
by中只可以引用select列表中变化的列,而不是造访FROM的源表中的列。HAVING可以访问的是
group by key和聚合函数。SELECT的时候,如果有GROUP BY,就只好访问group
key和聚合函数,而不是FROM中源表中的列。

马克斯Compute支持以执行种种书写查询语句,例如地点的语句可以写为

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

挥洒顺序和执行各样一致,就不易于混淆了。那样有一个十分的补益,在马克斯(Max)Compute
Studio中写SQL语句的时候,会有智能提示的机能,即使是SELECT在前,书写select列表的表明式的时候,因为FROM还未曾写,马克斯Compute
Studio无法知道或者访问那些列,也就无法做提醒。如下

美高梅4858官方网站 31

亟待先写好FROM,再回头写SELECT列表,才能唤起。如下

美高梅4858官方网站 32

假若选拔上述以FROM初阶的章程书写,则可以任天由命的按照上下文进行提示。如下

美高梅4858官方网站 33

协理顶层UNION

ODPS1.0不协理顶层UNION。ODPS2.0足以帮忙,例如

SELECT*FROMsrcUNIONALLSELECT*FROMsrc;

UNION后LIMIT的语义变化。

绝半数以上DBMS系统中,如MySQL,Hive等,UNION后纵然有CLUSTER BY, DISTRIBUTE
BY, SORT BY, ORDER
BY或者LIMIT子句,其听从于与前方所有UNION的结果,而不是UNION的末段一块。ODPS2.0在set
odps.sql.type.system.odps2=true;的时候,也使用此行为。例如:

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

返回

a

0

1

2

小节

马克斯Compute大大扩张了DML语句的接济,在易用性,包容性和性能方面,可以更好的满意你的急需。对于SQL比较熟练的专家会意识,上述功效大多数是专业的SQL辅助的功用。马克斯Compute会持续进步与专业SQL和业界常用产品的包容性。

除开,针对马克斯(Max)Compute用户的特色,也就是亟需在分外复杂的事务场景下,辅助对己大批量多少的拍卖,马克斯Compute提供了故意的本子方式和参数化视图,将在下一次为您介绍。

标注

注1

是否合并或者分化子查询,是由ODPS2.0的依据代价的优化器
(CBO)做出决定的,SQL本身的书写格局,不管是CTE依旧子查询,并无法有限扶助物理执行安排的联合或者分化。

相关文章

发表评论

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

网站地图xml地图