ODPS重装上阵,简明手册

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

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

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

2017/12/20 新加坡云栖大会上阿里云马克斯(Max)Compute发布了最新的成效Python
UDF,万众期待的效能终于扶助啦,我怎么能不一试为快,明日就享受什么通过Studio举行Python
udf开发。

摘要:
马克斯(Max)Compute(原ODPS)是阿里云自主研发的所有业界当先水平的分布式大数额处理平台,
尤其在集团内部得到广泛应用,支撑了多少个BU的中坚工作。
马克斯(Max)Compute除了无休止优化性能外,也从事于进步SQL语言的用户体验和表明能力,升高广大ODPS开发者的生产力。

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

关周密据库已经存在半个世纪,有分外广泛的使用情状,然则在高速迭代的互联网世界其增加性和
schema 灵活性被斥责颇多,由此类似 TableStore/BigTable/HBase
等强调增加性和灵活性的NoSQL数据库逐步流行起来,这么些 NoSQL 数据库只提供
API 接口,不提供 SQL 访问,那就造成多如牛毛耳熟能详 SQL
可是不欣赏写代码的用户没办法很欣然自得的采纳此类NoSQL数据库。基于此,表格存储开发公司共同
马克斯Compute(下文中 ODPS 与 马克斯Compute 同义)团队发掘了 ODPS-SQL
访问表格存储的门径,那样一个只懂 SQL
的用户也得以愉悦的走访表格存储其中的雅量数量了。

 

马克斯Compute(原ODPS)是阿里云自主研发的具备业界领先水平的分布式大数额处理平台,
尤其在集团内部得到广泛应用,支撑了八个BU的为主工作。
马克斯(Max)Compute除了无休止优化性能外,也从事于升高SQL语言的用户体验和表明能力,进步大面积ODPS开发者的生产力。

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

亚洲必赢登录 ,本篇小说就以一个小白用户的身价体验怎么样行使
马克斯(Max)Compute-SQL 查询表格存储其中的数码,以及怎么样支付自定义逻辑(User
Defined Function, UDF)来处理用户特定的多少格式。

嵌入条件

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

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

怎么是表格存储 TableStore?

 

先是弹 – 善用马克斯Compute编译器的谬误和警戒

其次弹 –
新的基本数据类型与内建函数

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

了解到,固然效果发表,然则还在公测阶段,借使想要使用,还得申请开通:。这里自己就不介绍申请开展具体流程了。

其次弹 – 新的要旨数据类型与内建函数

其三弹 –
复杂类型

ODPS重装上阵,简明手册。怎么样是大数量测算服务 马克斯Compute?

环境准备

MaxCompute 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. 亚洲必赢登录 1
  5. 配置studio module对python的依赖。

  6.  

    • File -> Project structure,添加python sdk:
    • 亚洲必赢登录 2
    • File -> Project structure,添加python facets:
      亚洲必赢登录 3
    • File -> Project structure,配置module依赖python facets:
      亚洲必赢登录 4

其三弹 – 复杂类型

第四弹 –
CTE,VALUES,SEMIJOIN

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

开发Python UDF

条件都准备好后,既可在对应信赖的module里创造举行python udf开发。

第四弹 – CTE,VALUES,SEMIJOIN

上次向你介绍了复杂类型,从本篇开头,向您介绍马克斯Compute在SQL语言DML方面的创新

上边首先我们将介绍环境准备,那是持有后边的操作的根底。然后会介绍使用
OdpsCmd
访问表格存储。在第一节大家介绍使用 OdpsStudio
访问表格存储。最终介绍怎么样写 UDF、布置 UDF 以及在询问中选用 UDF。

新建python脚本。

右键 new | 马克斯Compute Python,弹框里输入脚本名称,选拔品种为python udf:

亚洲必赢登录 5

变更的沙盘已自行填充框架代码,只必要编制UDF的入参出参,以及函数逻辑:
亚洲必赢登录 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项目):
    亚洲必赢登录 7
  2. 点击OK后,通过tunnel自动下载指定表的sample数据到地面warehouse目录(若往日已下载过,则不会再次重新下载,否则利用tunnel服务下载数据。默许下载100条,如需越多数据测试,可机关行使console的tunnel命令或者studio的表下载功效)。下载完结后,可以在warehouse目录看到下载的sample数据。那里用户也可以行使warehouse里的多寡开展调节,具体可参考java
    udf开发中的关于地点运行的warehouse目录”部分)。
  3. 亚洲必赢登录 8
  4. 然后本地运行框架会根据指定的列,获取data文件里指定列的数量,调用UDF本地运行。
    亚洲必赢登录 9
  • SELECT TRANSFORM。

  • 场景1

  • 我的连串要动迁到马克斯Compute平台上,系统中原本有广大效用是应用脚本来完结的,包括python,shell,ruby等剧本。
    要迁移到马克斯(Max)Compute上,我急需把这个本子全体都改造成UDF/UDAF/UDTF。改造进度不仅必要消耗时间人力,还索要做四回又一回的测试,从而确保改造成的udf和原先的本子在逻辑上是等价的。我希望能有更简约的迁徙方式。
  • 场景2
  • SQL比较擅长的是汇集操作,而自我索要做的事务要对一条数据做越多的精工细作的乘除,现有的内置函数不能够便于的完毕自己想要的效果,而UDF的框架不够利索,并且Java/Python我都不太熟习。相比较之下我更善于写剧本。我就期待可以写一个剧本,数据全都输入到本人的剧本里来,我自己来做各类计算,然后把结果输出。而马克斯Compute平台就担负帮自己把多少做好切分,让我的本子可以分布式执行,负责数据的输入表和输出表的军事管制,负责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脚本添加为马克斯Compute的Resource:
    亚洲必赢登录 10

留神此处拔取的马克斯(Max)Compute project必须是早就申请开展python
udf的project。

  1. 注册python 函数:
    亚洲必赢登录 11
  2. 在sql脚本中编辑马克斯(Max)Compute sql试用python udf:
    亚洲必赢登录 12

原稿链接:

上述意义可以选取SELECT TRANSFORM来兑现

场景2

小提示:由于 马克斯Compute 在 2.0
版本的计量框架才能支撑直接访问 TableStore
数据,该版本还在灰度上线中,近来还亟需 申请MaxCompute
2.0试用
,具体开通使用办法请参见 怎样申请试用马克斯Compute
2.0。

SELECT TRANSFORM 介绍

正值开发新项目,需求给一个小数目表准备些基本数据,但是从未INSERT …
VALUES
语句,无法把数据和创制表的DDL放在一起尊崇,只可以另用一些剧本,调用ODPS命令行准备数据。。。

下一场,准备好一个表格存储的实例以及一张数据表,报表存储实例管理,准备好实例名、EndPoint,为了不一样其余产品的AccessId和AccessKey,前面我们称之为TableStore-InstanceName,TableStore-EndPoint。

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

场景3

网络连通性

亚洲必赢登录 13

想测试一个新写的UDF,只写SELECT
myudf(‘123’);会报错,还非得创立一个dual表,里面加一行数据,好辛劳。倘诺测试UDAF,还要在测试表里面准备多行数据,每一回测试分歧的输入都要修改表内容如故创设新表,假使有个点子不用创造表也能例外的数码整合测试自己的UDF就好了。。。

马克斯Compute 与 TableStore
是八个独立的大数量统计以及大数目存储服务,所以两者之间的网络必须保障连通性。
对于 马克斯Compute 公共云服务走访 TableStore 存储,推荐应用 TableStore
私网
地址,例如

交由作业可以见见举行布署(全体开展后的视图):

场景4

假定网络堵塞,可以选择公网地址,TableStore原生扶助 VPC
网络决定,也亟需将网络项目设置为 “允许擅自网络访问”

亚洲必赢登录 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等。

马克斯Compute采纳基于ODPS2.0的SQL引擎,对DML举办了大幅伸张,提升了易用性和包容性,基本缓解了上述问题。

选用客户端 ODPS-CMD

该命令包容Hive的Transform作用,可以参考Hive的文档。一些亟需小心的点如下:

Common Table Expression (CTE)

1.下载并设置大数额总计服务客户端

  1. Using
    子句指定的是要实施的吩咐,而非资源列表,那点和大部分的马克斯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跑测试”的例证);

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

2.下载解压,将conf/odps_config.ini
的始末改动为:

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

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

project_name=上边申请的ODPS工程名

眼下odps select transform完全合营了hive的语法、功用和行事,蕴涵input/output row format 以及
reader/writer。Hive上的本子,大部分方可一向拿来运作,部分脚本只须要通过简单改动即可运行。其它大家许多意义都用比hive更高执行效能的语言
(C++) 重构,用以优化性能。

亚洲必赢登录 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. 兴妖作怪造数据

亚洲必赢登录 16

https_check=true

亚洲必赢登录 17

可以看来,a对应的子查询只须要写几回,在后面重用,CTE的WITH字句中可以指定几个子查询,像使用变量一样在整整讲话中一再重用。除了重用外,也无需再频仍嵌套了。

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

或者应用python

编译此脚本,可以考察执行陈设如下

data_size_confirm=100.0

亚洲必赢登录 18

亚洲必赢登录 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 用户会很喜爱这么些成效

亚洲必赢登录 20

3.行bin/odpscmd,输入show
tables,正常实施则代表下面配置不错。

亚洲必赢登录 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;

亚洲必赢登录 22

亚洲必赢登录 23

set
odps.sql.planner.mode=lot;

或者

施行后在,马克斯(Max)Compute Project
Explorer中得以找到新成立的表,并察看values中的数据现已插入到表中,如下:

set odps.sql.ddl.odps2=true;

亚洲必赢登录 24

亚洲必赢登录 25

set
odps.sql.preparse.odps2=lot;

本条事例是为着表达,很多java的utility可以平昔拿来运作。java和python即使有现成的udtf框架,可是用select
transform编写更不难,并且不须要卓殊看重,也不曾格式需要,甚至可以落成离线脚本拿来直接就用。

一部分时候表的列很多,准备数据的时候希望只插入部分列的多寡,此时得以用插队列表作用

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

  1. 协理其他脚本语言

亚洲必赢登录 26

5.创建一张 马克斯Compute 的数据表关联到 TableStore
的某一张表。

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

举行后,马克斯(Max)Compute Project
Explorer中找到目的表,并观察values中的数据已经插入,如下:

关联的数据表音信如下:

下面用的是perl。那实则不只是言语协理的增添,一些不难易行的功力,awk,
python, perl, shell
都扶助直接在指令里面写剧本,不须要写脚本文件,上传资源等进度,开发过程更简便易行。别的,由于当下我们总结集群上尚未php和ruby,所以那三种脚本不协助。

亚洲必赢登录 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;

亚洲必赢登录 28

INSERT… VALUES…
有一个限量,values必须是常量,然则一些时候希望在插入的数据中展开一些简便的演算,这些时候可以应用马克斯Compute的VALUES
TABLE功效,如下:

 

或者用map,reduce的重大字会让逻辑显得清楚一些

亚洲必赢登录 29

CREATE EXTERNAL TABLE IF NOT
EXISTS
ots_vehicle_track

亚洲必赢登录 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中出现过,此行就保存在结果集中

)

马克斯(Max)Compute基于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 是 马克斯(Max)Compute
    内置的处理 TableStore 数据的 StorageHandler, 定义了 马克斯Compute 和
    TableStore 的交互,相关逻辑由 马克斯(Max)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 表名错误(不设有),则会报错,马克斯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条件,马克斯Compute辅助

1.按照MaxCompute
Studio文档的求证在英特尔liJ里面安装马克斯Compute-Java/马克斯(Max)Compute-Studio插件,一旦插件安装收尾,就足以一贯开销。

例如:

譬如上面就是一个简约的 UDF
定义,只是简短的将多少个字符串连接。 马克斯Compute
接济更复杂的UDF,蕴涵自定义窗口进行逻辑等,详细请参考MaxCompute Studio-开发
UDF。

SELECT*frommytable1whereidin(selectidfrommytable2wherevalue=
mytable1.value);

 

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

2.装进之后方可上传到
马克斯(Max)Compute,其中打包那里有要求小心的地点,File->Project
Structure->Artifacts, 填写好 Name 和 Output Directory 后,要点击
+ 选取输出模块,打包后经过 ODPS Project Explorer
来上传资源、创造函数,然后就足以在SQL中调用。

对此NOT IN SUBQUERY,类似于LEFT ANTI JOIN,可是有几许斐然差距

 

例如:

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中源表中的列。

马克斯(Max)Compute扶助以实施顺序书写查询语句,例如地点的言语可以写为

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

书写顺序和施行各样一致,就不简单模糊了。那样有一个附加的功利,在马克斯Compute
Studio中写SQL语句的时候,会有智能提示的效益,若是是SELECT在前,书写select列表的表明式的时候,因为FROM还并未写,马克斯(Max)Compute
Studio不可能知道或者访问那些列,也就不可能做提示。如下

亚洲必赢登录 31

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

亚洲必赢登录 32

假使选拔上述以FROM开始的办法书写,则可以大势所趋的依照上下文举行指示。如下

亚洲必赢登录 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

小节

MaxCompute大大增加了DML语句的支撑,在易用性,包容性和性质方面,可以更好的餍足你的急需。对于SQL比较熟知的专家会意识,上述功能半数以上是正规的SQL支持的意义。马克斯Compute会持续升高与业内SQL和业界常用产品的包容性。

除外,针对马克斯(Max)Compute用户的特色,也就是内需在相当复杂的事情场景下,帮衬对己大批量多少的拍卖,马克斯(Max)Compute提供了故意的剧本格局和参数化视图,将在下三次为你介绍。

标注

注1

是还是不是联结或者不同子查询,是由ODPS2.0的按照代价的优化器
(CBO)做出决定的,SQL本身的书写格局,不管是CTE仍旧子查询,并不可能有限支撑物理执行安顿的见面或者分歧。

网站地图xml地图