最近基于 Presto 和 Amazon S3 做了一个有意思的东西, 简单来说就是使用SQL直接查询 S3 上的数据, 自带索引并且不需要额外meta数据.
为何要做
我们有很多数据, 直接存储在 S3 上, 有时候要基于一些特征筛选一些做计算(比如用户上传的图片). 而筛选过程很麻烦, 想到的方案有几个
方案一: 用户上传 S3 数据的同时, 写一条 log 到其他数据库(MySQL或者写文本入库到Hive). 筛选过程基于 log 然后再获取 S3 上的数据
方案二: 每天基于 S3 上的数据做 ETL , 将特征数据入库到数据仓库.
但评估下来感觉方案一系统引入太多组件, 日志收集等需要测试+维护, 成本高. 方案二又有延迟性(T+1), AWS 的 lambda 貌似能解决延迟性问题但中国区又没有上线很是苦恼.
正好由于上线了 Presto 作为主要的 SQL 查询工具, 原有的数据仓库也接入了 Presto, 如果可以直接写 SQL 查询 S3 上的数据, 就意味着整个数据链路的打通, 可以任意条件查询用户和 S3 数据并随便 JOIN, 想想都很激动.
思路
回去看我们 S3 数据存储的 key 的设计, 由于考虑到性能, 使用了 /时间倒序/日期/属性1/属性2/属性3/属性.../文件名 的方式作为 key , 而 S3 又有一个特性: Listing Keys Hierarchically Using a Prefix and Delimiter , 简单来说就是 S3 支持使用 key 前缀 list 对象. 如果将 SQL 语句解析成 S3 的 ObjectListing请求 岂不是就可以直接查询 S3 的数据了? 而且如果查询条件中带有 key 中的一些前缀, 我们的查询还是走索引的, 速度就取决于 S3 List 请求的速度!
实现
想明白了就简单了:
我们需要一个 Presto Connector, 将查询请求转化成 S3 Listing 请求并渲染成 Presto 的数据结构.
具体实现参见之前两篇文章:
Presto Plugin 开发 [1]
Presto Plugin 开发 [2]
关于索引多说一句, 如果 S3 key 的设计是:
s3://bucket_name/时间倒序/日期/user_id/column1/column2/column3/column4/data.suffix
那么我们的 Presto Connector 是实现了基于([时间], 日期, user_id, column1, column2, column3, column4) 的联合索引, 由于时间是个有限集合(86400个值), 因此如果查询中不给定时间这个 column , 可以通过枚举所有时间的方式, 同样走索引.
比如查询:
SELECT data_time,
data_date,
user_id,
column1,
column2,
column3,
column4
FROM s3data.test_db.test_table
WHERE data_time = '12:34:56'
AND data_date = '2015-12-16'
AND column1 = "value1"
AND column2 = 'value2'
LIMIT 10
肯定走索引(data_time,data_date, column1, column2), 仅仅需要构造一个
S3 List 请求到 s3://bucket_name/654321/151226/value1/value2 上就可以.
但是查询:
SELECT data_time,
data_date,
user_id,
column1,
column2,
column3,
column4
FROM s3data.test_db.test_table
WHERE column1 = "value1"
AND column2 = 'value2'
LIMIT 10
会导致全表扫描!
效果
对于 OLAP 来说, 速度足够. 给一个测试数据:
在
c3.2xlarge节点上执行count(*)400万条数据需要40秒(感觉有很大优化空间)
总结
这样就构造了
- 没有额外 ETL
- 没有额外索引数据存储
- 带索引的
- 实时(数据一旦上传到 S3 就可以查询到)
- 直接基于 S3 的数据仓库
- 可以 JOIN 数据仓库中其他表(得益于 Presto 的特性)
续集
如果你再实现一个 Presto 的scalar function 通过 RPC 的方式调用其他服务, 还可以做到直接用 SQL 查询 S3 数据然后直接调用 RPC 服务执行计算. 比如查询到'小明'今天上传的图片后, 直接调用黄色图片鉴定服务看他上传的图片到底有多少张是'带颜色'的, 对吧
SELECT tag,
count(*) AS cnt
FROM
(SELECT rpc_check_pic_porn(s3_key) AS tag
FROM s3data.test_db.test_data
WHERE data_date = '2016-01-23'
AND user_id = '小明' ) t
GROUP BY t.tag
-- EOF --
