阿里云分析型数据库的使用

技术

什么是分析型数据库?

数据处理可以分为 2 种,OLTP (Online Transaction Proccessing,联机事务处理) 和 OLAP(Online Analytical Proccessing,联机分析处理)。

OLTP 一般聚焦于日常的业务,例如支付系统里的一笔交易、图书馆里的借阅记录、博客系统里边的一篇日志。

OLAP 则着重强调数据分析,例如 9 月份的业务和 8 月份业务的对比、用户近半年的留存、用户的兴趣的分布,一般的数据仓库都是 OLAP 的存储形式。

OLTP 需要即时、快速、无差错的保证每一个业务的完成,因此要尽量简单。OLTP 的数据变化非常频繁,而且用户会经常基于行进行查询,因此数据要做好热备和缓存,在用户更新和获取时尽快返回,因此数据存在内存中最好不过。

OLAP 则一般用于决策,每次查询用的数据量都是百万以上的级别,这时候存储在内存显然不合适。OLAP 的数据一般不会经常变化,插入的需求远远多于更新。OLAP 对于查询的需求不要求像 OLTP 那样 ms 级别的返回,一般秒级别甚至分钟级别都可以接受。

适用于 OLAP 的数据库就是分析型数据库了,他们天生就是为了分析而生。

为什么使用了阿里云分析型数据库?

我们最终选择了阿里云分析型数据库 Postgres 版作为我们 OLAP 的数据存储方式,主要原因有以下几点:

  1. 支出列存储。在用户更新行为远少于插入行为时,列存储更快且支持较高的压缩率。
  2. 支持压缩。当数据量较大时,压缩可以减小存储体积,节省存储成本。
  3. 支持 JSON 操作。因为业务上的原因,有些数据比较复杂,难以直接作为列进行存储,这时候使用 JSON 可以大幅简化查询行为。

分析型数据库的具体使用

创建数据表

create table app_log (
    log_id         varchar(64),
    session_id     varchar(64),
    event          varchar(64),
    event_time     timestamp
)
  WITH (appendonly = true, orientation = column, compresstype = zlib, compresslevel = 5) distributed by (log_id)
    partition by range (event_time)
(
    START (date '2020-2-4') INCLUSIVE
    END (date '2024-2-4') EXCLUSIVE
    EVERY (INTERVAL '1 month')
); 

执行该语句,这样我们就成功地创建了一张表。

导入数据

开始时我们是直接向数据表中使用 insert 语句插入数据的,随着 QPS 的增加,insert 语句占用了数据库的大部分 CPU,导致我们平常进行查询时非常缓慢,因此我们改为了每日凌晨导入前一天数据的方式。这样我们既能在第二日正常地对既有数据进行分析,又不会使 CPU 一直占用很高。

我们采用了从 OSS 外表导入的方式来完成该功能。

  1. 写入数据到 CSV
  2. 将 CSV 数据上传到 OSS
  3. 从 OSS 上的 CSV 文件创建外部表
  4. 将外部表的数据导入到数据表

写入数据到 CSV

这一步的功能应该由业务实现,非常简单,我们来看下我们的数据文件。

ba386925fc2b266ab9ee859322a120d7,5086c4eb5d584621abe23492dead64cd,use_time,2020-01-06T11:47:16.380+08
378c880b114002f729a330e3c61bcc4b,5086c4eb5d584621abe23492dead64cd,use_time,2020-01-06T11:47:16.378+08

将 CSV 上传到 OSS

这一步的功能也非常简单,直接使用阿里云的 OSS 命令行工具或者 SDK 将 CSV 文件上传到 OSS 即可。不过要注意的是,当文件较大时(阿里云 SDK 提示的是 5G,实际中大概是 20G),会返回文件过大的错误。这时候你可以选择分割文件或者压缩文件。我采用的是压缩文件。

gzip 2020-01-06.csv

执行该命令即可得到名字为 2020-01-06.csv.gzip 的压缩文件。

从 OSS 创建只读外表

create readable external table external_log_20200106 (
    log_id         varchar(64),
    session_id     varchar(64),
    event          varchar(64),
    event_time     timestamp
)
location('oss://$oss
        filepath=$filepath id=$oss_access_key_id 
        key=$oss_access_key_secret bucket=$bucket compressiontype=gzip compressionlevel=6')
        FORMAT 'csv'
        ENCODING 'utf8'
        LOG ERRORS INTO my_error_rows SEGMENT REJECT LIMIT 1000;

这样便创建了名字为 external_log_20200106 的外部表。

需要注意的是,外部表的列名顺序和数据表要保持完全一致。

使用 Gzip 的情况下,压缩等级也要保持一致。

从外部表导入数据

insert into app_log select * from external_log_20200106;

执行该命令即可从外部表导入数据到数据表中,这样便完成了数据的写入。

查询数据

阿里云分析型数据库支持 BitMap,Hash,Btree 等类型的索引,在查询之前一定要根据自己的需求加好索引方便快速查询,这个由于大家的业务都不一样我就不举例了。

创建于2020年02月10日 09:49
阅读量 380
留言列表

暂时没有留言

添加留言