马克斯Compute – ODPS重装上阵 第6弹 – CTE,VALUES,SEMIJOIN

  1. 无中生有造数据

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

MaxCompute 与 TableStore
是两个独立的大数据计算以及大数据存储服务,所以两者之间的网络必须保证连通性。
对于 MaxCompute 公共云服务访问 TableStore 存储,推荐使用 TableStore
私网
地址,例如

或者

实际上,VALUES表并不限于在INSERT语句中使用,任何DML语句都可以使用。

首先,准备好一个 MaxCompute 的工程,工程创建指导文档,准备好AccessId和AccessKey备用,为了区别其他产品的AccessId和AccessKey,后面我们称之为ODPS-AccessId,ODPS-AccessKey。并在RAM中授权
MaxCompute 访问 TableStore 的权限,授权方式请参考MaxCompute访问TableStore数据——授权

目前odps select transform完全兼容了hive的语法、功能和行为,包括
input/output row format 以及
reader/writer。Hive上的脚本,大部分可以直接拿来运行,部分脚本只需要经过少许改动即可运行。另外我们很多功能都用比hive更高执行效率的语言
(C++) 重构,用以优化性能。

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

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

作者:隐林

LEFT SEMI JOIN

 

图片 1

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

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

理论上select transform能实现的功能udtf都能实现,但是select
transform比udtf要灵活得多。且select
transform不仅支持java和python,还支持shell,perl等其它脚本和工具。
且编写的过程要简单,特别适合adhoc功能的实现。举几个例子:

也就是可以不写from语句,直接执行SELECT,只要SELECT的表达式列表不用任何上游表数据就可以。其底层实现为从一个1行,0列的匿名VALUES表选取。这样,在希望测试一些函数,比如自己的UDF等,就再也不用手工创建DUAL表了。

2.打包之后可以上传到
MaxCompute,其中打包这里有需要注意的地方,File->Project
Structure->Artifacts, 填写好 Name 和 Output Directory 后,要点击
+ 选择输出模块,打包后通过 ODPS Project Explorer
来上传资源、创建函数,然后就可以在SQL中调用。

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

因为WHERE中包含了OR,导致无法转换为SEMI JOIN,会单独启动作业执行子查询

如果网络不通,可以使用公网地址,TableStore原生支持 VPC
网络控制,也需要将网络类型设置为 “允许任意网络访问”

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

迁移一个原来在Oracle上面的ETL系统,发现用了 WHERE EXISTS( SELECT
…) 和 WHERE IN (SELECT
…) 这类的语句,可是发现ODPS在这方面支持不完整,还要手工将这些半连接的语句转换为普通JOIN,再过滤。。。

data_size_confirm=100.0

  1. UDTF是有类型,而Transform的子进程基于stdin/stdout传输数据,所有数据都当做string处理,因此transform多了一步类型转换;
  2. Transform数据传输依赖于操作系统的管道,而目前管道的buffer仅有4KB,且不能设置,
    transform读/写 空/满 的pipe会导致进程被挂起;
  3. UDTF的常量参数可以不用传输,而Transform没办法利用这个优化。

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

一种快速、完全托管的TB/PB级数据仓库解决方案,提供多种经典的分布式计算模型,能够更快速的解决用户海量数据计算问题。

  1. 用odps跑测试

还有一种VALUES表的特殊形式

# this url is for odpscmd update

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

可以看到对src读后进行过滤的DAG。对src的读取与过滤在整个执行计划中只需要一次
注1 )。

// 统计编号 4 以下的车辆在时间戳 1469171387
以前的平均速度和平均油耗

SELECT TRANSFORM 的优势:

Common Table Expression (CTE)

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

图片 2

支持新的SELECT语序

speed double,

图片 3

除此之外,针对MaxCompute用户的特点,也就是需要在非常复杂的业务场景下,支持对己大量数据的处理,MaxCompute提供了特有的脚本模式和参数化视图,将在下一次为您介绍。

本篇文章就以一个小白用户的身份体验如何使用
MaxCompute-SQL 查询表格存储里面的数据,以及如何开发自定义逻辑(User
Defined Function, UDF)来处理用户特定的数据格式。

第三弹 – 复杂类型

图片 4

然后,准备好一个表格存储的实例以及一张数据表,表格存储实例管理,准备好实例名、EndPoint,为了区别其他产品的AccessId和AccessKey,后面我们称之为TableStore-InstanceName,TableStore-EndPoint。

图片 5

使用CTE的方式重写以上语句

//
我们选出来1行数据,并将name/name传入UDF,返回两个string的累加

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

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

set
odps.sql.planner.mode=lot;

UDTF的优势:

对于NOT IN SUBQUERY,类似于LEFT ANTI JOIN,但是有一点显著不同

3.行bin/odpscmd,输入show
tables,正常执行则表示上面配置正确。

应用场景举例

注1

使用客户端 ODPS-CMD

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

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

access_id=ODPS-AccessId

第一弹 – 善用MaxCompute编译器的错误和警告

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

  • 实例名称:vehicle-test
  • 数据表名称:vehicle_track
  • 主键信息:vid(int); gt (int)
  • 访问域名:https://vehicle-test.cn-shanghai.ots-internal.aliyuncs.com

或者使用python

INSERT… VALUES…
有一个限制,values必须是常量,但是有的时候希望在插入的数据中进行一些简单的运算,这个时候可以使用MaxCompute的VALUES
TABLE功能,如下:

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

  1. awk 用户会很喜欢这个功能

原有ODPS也支持IN SUBQUERY,但是不支持correlated条件,MaxCompute支持

DROP TABLE IF EXISTS
ots_vehicle_track;

第四弹 – CTE,VALUES,SEMIJOIN

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

目前ODPS-SQL访问 TaleStore
还在对执行逻辑进行深度的优化,如果有需求请联系ots_support,我们将针对业务场景来进行优化。

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

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

update_url=

性能上,SELECT TRANSFORM 与UDTF
各有千秋。经过多种场景对比测试,数据量较小时,大多数场景下select
transform有优势,而数据量大时UDTF有优势。由于transform的开发更加简便,所以select
transform非常适合做adhoc的数据分析。

SELECT*FROMtable1JOINtable2ONtable1.id = table2.id;

常见错误处理:

  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跑测试”的例子);

返回

oil_consumption double

这个例子是为了说明,很多java的utility可以直接拿来运行。java和python虽然有现成的udtf框架,但是用select
transform编写更简单,并且不需要额外依赖,也没有格式要求,甚至可以实现离线脚本拿来直接就用。

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

比如下面就是一个简单的 UDF
定义,只是简单的将两个字符串连接。 MaxCompute
支持更复杂的UDF,包括自定义窗口执行逻辑等,详细请参考MaxCompute Studio-开发
UDF。

性能

VALUES

longitude double,

图片 6

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

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

上次向您介绍了CTE,VALUES,SEMIJOIN,本篇向您介绍MaxCompute对其他脚本语言的支持

SELECT*frommytable1whereidin(selectidfrommytable2)ORvalue>0;

5.创建一张 MaxCompute 的数据表关联到 TableStore
的某一张表。

上面用的是perl。这其实不仅仅是语言支持的扩展,一些简单的功能,awk,
python, perl, shell
都支持直接在命令里面写脚本,不需要写脚本文件,上传资源等过程,开发过程更简单。另外,由于目前我们计算集群上没有php和ruby,所以这两种脚本不支持。

SELECT*FROMsrc1UNIONSELECT*FROMsrc2;

TableStore数据类型

MaxCompute数据类型

string

string

binary

blob

int

bigint

double

double

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

1

end_point=

责任编辑:

第四弹 –
CTE,VALUES,SEMIJOIN

 

小结

MaxCompute大大扩充了DML语句的支持,在易用性,兼容性和性能方面,可以更好的满足您的需求。对于SQL比较熟悉的专家会发现,上述功能大部分是标准的SQL支持的功能。MaxCompute会持续提升与标准SQL和业界常用产品的兼容性。

网络连通性

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

例如:

关系数据库已经存在半个世纪,有非常广泛的使用场景,但是在快速迭代的互联网领域其扩展性和
schema 灵活性被诟病颇多,因此类似 TableStore/BigTable/HBase
等强调扩展性和灵活性的NoSQL数据库逐步流行起来,这些 NoSQL 数据库只提供
API 接口,不提供 SQL 访问,这就导致很多熟悉 SQL
但是不喜欢写代码的用户没法很舒服的使用此类NoSQL数据库。基于此,表格存储开发团队联合
MaxCompute(下文中 ODPS 与 MaxCompute 同义)团队打通了 ODPS-SQL
访问表格存储的路径,这样一个只懂 SQL
的用户也可以愉快的访问表格存储里面的大量数据了。

图片 7

MaxCompute支持SQL标准的CTE。能够提高SQL语句的可读性与执行效率。

set
odps.sql.preparse.odps2=lot;

SELECT TRANSFORM 介绍

LEFT ANTI JOIN

1.按照MaxCompute
Studio文档的说明在IntelliJ里面安装MaxCompute-Java/MaxCompute-Studio插件,一旦插件安装完毕,就可以直接开发。

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

图片 8

latitude double,

上面的语句仅仅是把value原样输出,但是熟悉awk的用户,从此过上了写awk脚本不写sql的日子

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

WITH SERDEPROPERTIES ( — (2)

标注

图片 9

access_key= ODPS-AccessKey

图片 10

图片 11

vid bigint,

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

另外在处理分区表的时候,也会有特殊处理

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

上述功能可以使用SELECT TRANSFORM来实现

图片 12

关联的数据表信息如下: