什么是分析型数据库？

数据处理可以分为 2 种，OLTP （Online Transaction Proccessing，联机事务处理）和 OLAP（Online Analytical Proccessing，联机分析处理）。

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

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

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

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

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

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

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

支出列存储。在用户更新行为远少于插入行为时，列存储更快且支持较高的压缩率。
支持压缩。当数据量较大时，压缩可以减小存储体积，节省存储成本。
支持 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 外表导入的方式来完成该功能。

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

写入数据到 CSV

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

1
2

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），会返回文件过大的错误。这时候你可以选择分割文件或者压缩文件。我采用的是压缩文件。

1	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 的情况下，压缩等级也要保持一致。

从外部表导入数据

1	insert into app_log select * from external_log_20200106；

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

查询数据

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

Castle，专属于自己的领地

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