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

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

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

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

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

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

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

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

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

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

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

什么是表格存储 TableStore?

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

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

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

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

第三弹 –
复杂类型

什么是大数据计算服务 MaxCompute?

第三弹 – 复杂类型

第四弹 –
CTE,VALUES,SEMIJOIN

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

第四弹 – CTE,VALUES,SEMIJOIN

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

下面首先我们将介绍环境准备,这是所有后面的操作的基础。然后会介绍使用
OdpsCmd
访问表格存储。在第三节我们介绍使用 OdpsStudio
访问表格存储。最后介绍如何写 UDF、部署 UDF 以及在查询中使用 UDF。

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

场景1 

环境准备

  • SELECT TRANSFORM。

  • 场景1

  • 我的系统要迁移到MaxCompute平台上,系统中原来有很多功能是使用脚本来完成的,包括python,shell,ruby等脚本。
    要迁移到MaxCompute上,我需要把这些脚本全部都改造成UDF/UDAF/UDTF。改造过程不仅需要耗费时间人力,还需要做一遍又一遍的测试,从而保证改造成的udf和原来的脚本在逻辑上是等价的。我希望能有更简单的迁移方式。
  • 场景2
  • SQL比较擅长的是集合操作,而我需要做的事情要对一条数据做更多的精细的计算,现有的内置函数不能方便的实现我想要的功能,而UDF的框架不够灵活,并且Java/Python我都不太熟悉。相比之下我更擅长写脚本。我就希望能够写一个脚本,数据全都输入到我的脚本里来,我自己来做各种计算,然后把结果输出。而MaxCompute平台就负责帮我把数据做好切分,让我的脚本能够分布式执行,负责数据的输入表和输出表的管理,负责JOIN,UNION等关系操作就好了。

_需要写一个复现的SQL,
从多个表中读取数据,有些之间做Join,有些之间做Union,生成中间数据又要Join,
最后需要输出多张表,最后写成了n层嵌套的子查询,自己都看不懂了。而且同样的查询,在不同的子查询中有重复。为了维护方便,把复杂的语句拆成多个语句,但是发现每个语句都需要单独提交,排队,并且要将中间结果写到本来不需要的临时表,在后面的语句中再读出来,慢了好多。。。

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

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

场景2

小提示:由于 MaxCompute 在 2.0
版本的计算框架才能支持直接访问 TableStore
数据,该版本还在灰度上线中,目前还需要 申请MaxCompute
2.0试用
,具体开通使用方法请参见 如何申请试用MaxCompute
2.0。

SELECT TRANSFORM 介绍

正在开发新项目,需要给一个小数据表准备些基本数据,但是没有INSERT …
VALUES
语句,没办法把数据和创建表的DDL放在一起维护,只好另用一些脚本,调用ODPS命令行准备数据。。。

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

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

场景3

网络连通性

永利游戏网址 1

想测试一个新写的UDF,只写SELECT
myudf(‘123’);会报错,还必须创建一个dual表,里面加一行数据,好麻烦。如果测试UDAF,还要在测试表里面准备多行数据,每次测试不同的输入都要修改表内容或者创建新表,如果有个办法不用创建表也能不同的数据组合测试我的UDF就好了。。。

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

提交作业可以看到执行计划(全部展开后的视图):

场景4

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

永利游戏网址 2

迁移一个原来在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等。

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

使用客户端 ODPS-CMD

该命令兼容Hive的Transform功能,可以参考Hive的文档。一些需要注意的点如下:

Common Table Expression (CTE)

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

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

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

2.下载解压,将conf/odps_config.ini
的内容修改为:

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

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

project_name=上面申请的ODPS工程名

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

永利游戏网址 3

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. 无中生有造数据

永利游戏网址 4

https_check=true

永利游戏网址 5

可以看到,a对应的子查询只需要写一次,在后面重用,CTE的WITH字句中可以指定多个子查询,像使用变量一样在整个语句中反复重用。除了重用外,也不必再反复嵌套了。

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

或者使用python

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

data_size_confirm=100.0

永利游戏网址 6

永利游戏网址 7

# 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 用户会很喜欢这个功能

永利游戏网址 8

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

永利游戏网址 9

可以看到对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;

永利游戏网址 10

永利游戏网址 11

set
odps.sql.planner.mode=lot;

或者

执行后在,MaxCompute Project
Explorer中可以找到新创建的表,并看到values中的数据已经插入到表中,如下:

set odps.sql.ddl.odps2=true;

永利游戏网址 12

永利游戏网址 13

set
odps.sql.preparse.odps2=lot;

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

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

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

  1. 支持其他脚本语言

永利游戏网址 14

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

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

执行后,MaxCompute Project
Explorer中找到目标表,并看到values中的数据已经插入,如下:

关联的数据表信息如下:

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

永利游戏网址 15

  • 实例名称: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;

永利游戏网址 16

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

 

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

永利游戏网址 17

CREATE EXTERNAL TABLE IF NOT
EXISTS
ots_vehicle_track

永利游戏网址 18

其中的VALUES (…), (…) t (a, b), 相当于定义了一个名为t,列为a,
b的表,类型为(a string, b
string),其中的类型从VALUES列表中推导。这样在不准备任何物理表的时候,可以模拟一个有任意数据的,多行的表,并进行任意运算。

(

理论上OpenMR的模型都可以映射到上面的计算过程。注意,使用map,reduce,select
transform这几个语法其实语义是一样的,用哪个关键字,哪种写法,不影响直接过程和结果。

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

vid bigint,

网站地图xml地图