OLAP on TableStore:基于Data Lake Analytics的Serverless SQL大数据分析

本文涉及的产品
简介: TableStore(简称OTS)是阿里云的一款分布式表格系统,为用户提供schema-free的分布式表格服务。随着越来越多用户对OLAP有强烈的需求,我们提供在表格存储上接入Data Lake Analytics(简称DLA)服务的方式,提供一种快速的OLAP解决方案。

背景介绍

TableStore(简称OTS)是阿里云的一款分布式表格系统,为用户提供schema-free的分布式表格服务。随着越来越多用户对OLAP有强烈的需求,我们提供在表格存储上接入Data Lake Analytics(简称DLA)服务的方式,提供一种快速的OLAP解决方案。DLA是阿里云上的一款的通用SQL查询引擎,通过在OTS连通DLA服务,使用通用的SQL语言(兼容mysql5.7绝大部分查询语法),在表格存储上做灵活的数据分析任务。

架构视图

arch.png

如上图所示,整体OLAP查询架构涉及3款阿里云产品:DLA,OTS,OSS。其中DLA负责分布式SQL查询计算,在实际运行过程中,会将用户sql查询请求进行任务拆解,产生若干可并行化的子任务,提升数据计算和查询能力。OTS为数据存储层,用于接收DLA的各类子查询任务。如果用户已经有存量的数据在OTS上,可以直接在DLA上建立映射视图,实现快速体验SQL计算带来的便捷。OSS为分布式对象存储系统,主要用于用户查询结果数据的保存。

因此用户要想快速体验SQL on OTS,必须在开通OTS的前提下,完成DLA和OSS服务的开通。通过上述3个云产品的配合,用户就能在OTS上快速执行SQL计算。目前开通OSS服务的主要原因是DLA默认回查询结果集数据写回到OSS存储,因此需要引入一个额外的存储依赖,但仅依赖用户开通OSS服务,不需要用户预先创建OSS存储实例。

目前开服公测的区域是上海区,对应的实例是该region内所有的容量型实例。在开通DLA服务时,需要先填写公测申请,通过之后按照“接入方式”小节的步骤,能快速完成接入体验。

接入方式

整个主要包含OTS、OSS、DLA的服务接入。需要注意的一点是,完成接入之后,就会按照实际查询产生相应的费用。如在这个过程中,用户账号是欠费的,将会发生查询失败。

OTS服务开通

如果用户已经开通的OTS服务,并且上面已经包含存量的实例,表格数据,则忽略该步骤。

对于首次使用OTS的用户,可按照下述方式开通OTS:

  1. 登录https://www.aliyun.com
  2. 进入“产品”->"云计算基础"->"数据库"->“表格存储 TableStore”;
  3. 按照上面的文档说明,快速建立实例和表格,进行体验;

    
    1)使用控制台,快速创建测试表格:

    ots_create_table.jpg

    
    2)使用控制台,快速插入测试数据:

    insert_data.jpg

OSS服务开通

  1. 登录https://www.aliyun.com
  2. 进入“产品”->"云计算基础"->"存储服务"->“对象存储 OSS”;
  3. 直接点击服务开通即可。

OSS服务开通后,不需要创景对象实例,DLA接入时,会自动为用户在OSS服务中,创建用于存储查询结果数据的对象存储实例,用户不需要关心。

DLA服务开通

  1. 登录https://www.aliyun.com
  2. 进入“产品”->"大数据"->"大数据计算"->“Data Lake Analytics”;
  3. 直接点击服务开通;

注意:处于公测阶段时,开通服务需要做公测申请,填写好相关信息即可。

DLA on OTS接入

按照下列步骤,在DLA上建立OTS的映射:

  1. 开通DLA服务之后,可以选择不同region,选择开通对应region的DLA服务实例(如现在华东2的上海区域)。不同的region,对应不同的账号,不同region的DLA账号,不能混用,如下图所示:

service_open.jpg

service_open2.jpg

注意:账号创建完成之后,会收到相关邮件(邮箱为阿里云的注册邮箱),内含该region的DLA账号和密码,注意查收。
  1. 选择region,授权DLA访问OTS上的用户实例数据,如下图所示:
    assumeRole.jpg
  2. 服务开通之后,有3中SQL访问方式:控制台、mysql client,JDBC。

控制台访问

点击数据库连接,使用邮件中的该region的用户名和密码,连接进入控制台。

console_access.jpg

进入控制台后,需要为OTS上的实例表格数据建立映射信息。场景举例:假设用户在上海region已经有一个名为sh_tpch的实例,该实例包含表格test001,里面包含2行测试数据。对该实例建立映射的步骤包括:

1)将ots的实例映射成DLA的一个DataBase实例:
在建立DLA的Database映射前,首先需要在OTS上创建一个表格存储的实例instance,如:

创建一个实例,名为sh-tpch,对应的endpoint为https://sh-tpch.cn-shanghai.ots.aliyuncs.com。

完成测试实例创建后,执行下列语句建立Database映射:

CREATE SCHEMA sh_tpch001 with DBPROPERTIES(LOCATION ='https://sh-tpch.cn-shanghai.ots.aliyuncs.com', catalog='ots', instance ='sh-tpch');

注意:使用mysql client时,可以使用create database或create schema语句进行创建db映射;但是在控制台,目前只支持create schema语句创建db映射。

create_db.jpg

上述语句,将在DLA上创建一个名为sh_tpch001的database,对应的实例是ots的sh-tpch.cn-shanghai.ots.aliyuncs.com集群下名为sh-tpch的实例。通过上面的语句,就能产生一个ots的实例映射。

2)在tp_tpch001的DB下,建立表格的映射:
在建立DLA的表格映射前,首先需要在OTS创建测试表,流程参考"OTS服务开通"小节。

测试表格创建完成后,执行下列语句建立表格映射:

CREATE TABLE test001 (pk0 int , primary key(pk0));

注意:主要建立DLA映射表时,指定的Primary Key必须跟OTS表格定义Primary Key列表一致。因为Primary Key必须能是唯一的定位一行,一旦映射表的Primary Key列表与OTS表格的PK不一致,则可能会导致SQL查询结果出现非预期的错误。

create_table.jpg

例如:用户的OTS实例sh_tpch上包含test001表格,其中只有一列pk0。上面的命令就完成了在DLA的实例sh_tpch001上,创建映射表test001。使用show命令能查看该表创建成功:

show_table.jpg

3)使用select语句执行sql查询:

1. 查出所有数据:
select * from test001;

select_0.jpg

2. 执行count统计:
select count(*) from test001;

select_1.jpg

3. 执行sum统计:
select sum(pk0) from test001;

select_2.jpg

4)更为丰富执行语句,请查看如下的帮助说明文档:

create schema语句:https://help.aliyun.com/document_detail/72005.html
create table语句:https://help.aliyun.com/document_detail/72006.html
select语句:https://help.aliyun.com/document_detail/71044.html
show语句:https://help.aliyun.com/document_detail/72011.html
drop table语句:https://help.aliyun.com/document_detail/72008.html
drop schema语句:https://help.aliyun.com/document_detail/72007.html

5)在做SQL执行时,可以选择同步执行结果,返回满足条件的前10000条记录;如果要获大结果集数据,需要选择异步执行,并使用show query_id的方式异步获取结果:

show query_task where id = '59a05af7_1531893489231';

async_exec.jpg
show_async_result.jpg

mysql访问

使用标准的mysql client也能快速连通DLA的数据实例。其中连接语句为:

mysql -h service.cn-shanghai.datalakeanalytics.aliyuncs.com -P 10000 -u <username> -p <password> -c -A

其他操作语句跟“控制台访问”小节介绍一致。

JDBC访问

也可以使用标准的java api实现访问,连接串为:

jdbc:mysql://service.cn-shanghai.datalakeanalytics.aliyuncs.com:10000/

其他操作语句跟“控制台访问”小节介绍一致。

总结

通过DLA+OTS,我们能让用户快速在表格存储上体验极致的分布式SQL计算。

Reference

表格存储(Table Store): https://www.aliyun.com/product/ots
Data Lake Analytics: https://www.aliyun.com/product/datalakeanalytics

联系我们

如接入过程或sql查询出现任何问题,可以钉钉联系:蔡杰明(钟牙)
OTS外部用户钉钉交流群:表格存储公开交流群, 群号:11789671

相关实践学习
基于函数计算一键部署掌上游戏机
本场景介绍如何使用阿里云计算服务命令快速搭建一个掌上游戏机。
建立 Serverless 思维
本课程包括: Serverless 应用引擎的概念, 为开发者带来的实际价值, 以及让您了解常见的 Serverless 架构模式
目录
相关文章
|
1月前
|
Cloud Native OLAP OLTP
在业务处理分析一体化的背景下,开发者如何平衡OLTP和OLAP数据库的技术需求与选型?
在业务处理分析一体化的背景下,开发者如何平衡OLTP和OLAP数据库的技术需求与选型?
146 4
|
1月前
|
SQL 索引
19. 一个SQL语句执行很慢, 如何分析
该内容介绍了如何分析执行慢的SQL语句。首先启用慢查询日志或使用命令获取慢查询的SQL。然后利用`EXPLAIN`命令分析,关注其中的`select_type`, `type`, 和 `extra`字段。`select_type`涉及子查询和联合查询的类型,`type`表示查询优化器使用的访问类型,性能从上到下递减,`extra`字段提供额外信息,如是否使用索引等。
22 0
|
1月前
|
SQL
启动mysq异常The server quit without updating PID file [FAILED]sql/data/***.pi根本解决方案
启动mysq异常The server quit without updating PID file [FAILED]sql/data/***.pi根本解决方案
34 0
|
1月前
|
SQL 数据可视化 算法
SQL Server聚类数据挖掘信用卡客户可视化分析
SQL Server聚类数据挖掘信用卡客户可视化分析
|
25天前
|
SQL 测试技术 OLAP
现代化实时数仓 SelectDB 再次登顶 ClickBench 全球数据库分析性能排行榜!
现代化实时数仓 SelectDB 在时隔两年后再次完成登顶,在全部近百款数据库和数十种机型中,性能位居总榜第一!
现代化实时数仓 SelectDB 再次登顶 ClickBench 全球数据库分析性能排行榜!
|
13天前
|
SQL 缓存 关系型数据库
✅分析SQL执行计划,我们需要关注哪些重要信息
SQL执行计划解析:12个关键字段详解,包括id(操作标识)、select_type(操作类型)、table(涉及表)、partitions(分区)、type(索引类型)、possible_keys(可能的索引)、key(使用索引)、key_len(索引长度)、ref(比较对象)、rows(扫描行数)、filtered(过滤比例)和Extra(额外信息)。类型从优至劣:system&gt;const&gt;eq_ref&gt;ref&gt;range&gt;index&gt;ALL。
|
19天前
|
存储 缓存 测试技术
现代化实时数仓 SelectDB 再次登顶 ClickBench 全球数据库分析性能排行榜!
近日,在 ClickHouse 发起的分析型数据库性能测试排行榜 ClickBench(https://benchmark.clickhouse.com/)中,现代化实时数仓 SelectDB 时隔两年后再次登顶,在全部近百款数据库和数十种机型中,性能表现位居总榜第一!
58 1
|
30天前
|
SQL HIVE UED
【Hive SQL 每日一题】分析电商平台的用户行为和订单数据
作为一名数据分析师,你需要分析电商平台的用户行为和订单数据。你有三张表:`users`(用户信息),`orders`(订单信息)和`order_items`(订单商品信息)。任务包括计算用户总订单金额和数量,按月统计订单,找出最常购买的商品,找到平均每月最高订单金额和数量的用户,以及分析高消费用户群体的年龄和性别分布。通过SQL查询,你可以实现这些分析,例如使用`GROUP BY`、`JOIN`和窗口函数来排序和排名。
|
30天前
|
关系型数据库 Serverless 分布式数据库
【PolarDB 开源】PolarDB Serverless 模式:自动扩缩容与成本效益分析
【5月更文挑战第25天】PolarDB Serverless 提供自动扩缩容功能,适应动态工作负载,降低成本。在业务高峰期增加资源保障性能,低谷期减少资源实现成本优化。通过对比传统模式下的成本浪费,示例说明了Serverless如何节省开支。代码演示了连接与查询PolarDB Serverless数据库的基本操作。要充分利用该模式,需合理规划业务、监控性能并结合其他云服务。PolarDB Serverless是弹性、经济的数据库选择,未来将持续创新,助力企业高效发展。
376 1
|
15天前
|
存储 分布式计算 关系型数据库
实时数仓 Hologres产品使用合集之是否提供相应的功能接口和指令,可以将数据从OSS存储同步到Hologres中进行分析
实时数仓Hologres的基本概念和特点:1.一站式实时数仓引擎:Hologres集成了数据仓库、在线分析处理(OLAP)和在线服务(Serving)能力于一体,适合实时数据分析和决策支持场景。2.兼容PostgreSQL协议:Hologres支持标准SQL(兼容PostgreSQL协议和语法),使得迁移和集成变得简单。3.海量数据处理能力:能够处理PB级数据的多维分析和即席查询,支持高并发低延迟查询。4.实时性:支持数据的实时写入、实时更新和实时分析,满足对数据新鲜度要求高的业务场景。5.与大数据生态集成:与MaxCompute、Flink、DataWorks等阿里云产品深度融合,提供离在线

热门文章

最新文章

相关产品

  • 函数计算