1

云端的数据仓库Palo

数据库(Database)是按照数据结构来组织、存储和管理数据的系统,由于支持事务处理,被广泛运用在业务系统中,也被称为OLTP(Online Transaction Processing)系统。

数据仓库(Data Warehouse)是用来做报表和分析的系统,简称OLAP(Online Analytical Processing)。数据从各种数据源萃取(Extract)、变形(Transform)、导入(Load)到数据仓库之后,数据科学家便可以做出各种报表、观察趋势以及分析每天的销售数据。

下面是OLTP和OLAP的对比:

OLTP OLAP
面向应用 日常交易处理 统计分析决策
访问模式 简单小事务、操作少量数据 复杂聚合查询、操作大量数据
数据类型 最新数据 历史数据
数据规模 GB TB至PB
数据更新 实时更新 批量更新
数据组织 第三范式 星型模型

相对数据库,数据仓库的目的是辅助企业做出更明智的决策,是商务智能的核心组成部分,具有以下特点:

  • 把异构数据源中的数据转换成统一格式,方便用SQL语句查询。
  • 专门为在线分析(OLAP)优化,不影响在线事务(OLTP)的业务。
  • 维护历史数据,而不仅仅是最新数据。
  • 提供唯一真实版本(Single Version of the Truth),帮助企业不同部门之间协作。

说明一点,这里特指传统意义上的关系型数据库,Hadoop平台演化出来的数据湖(Data Lake)也可以作为新一代的数据仓储技术,以后有机会再介绍。

数据仓库虽有万般好处,代价呢就是昂贵,有时候必须搭配特制的一体机才能正常工作。随着云计算的发展,云端的数据仓库产品,如亚马逊的RedShift和百度的Palo,也有长足的发展。由于技术突破,数据仓库系统可以在廉价硬件(Commodity Hardware)上运行,价格降到了原先的十分之一;此外还能享有云计算的好处,比如自由伸缩集群、高可用、免运维,等等。

这里我们以Palo为例介绍云端数据仓库的体验。Palo是百度自研的数据仓库云服务,名字正好是OLAP倒过来写,意思是“玩转OLAP”,在百度内部有着广泛应用。比如站长们熟悉的百度统计就是使用Palo产生交互式报表。如今,Palo已经通过百度开放云这个窗口把技术输出给国内企业,助力企业向互联网+转型。

准备数据

为了更好衡量用于产生报表的分析型数据库,Star Schema Benchmark设计了星型拓扑的元数据以及查询语句,适合分析型数据库应用场景。同时还提供了dbgen工具,可以生成各种规模的测试数据。

以下是默认生成的结果,LINEORDER是事实表(6M条记录),其他都是维度表,结构如下:

big-data-palo-01

数据生成以后请上传到对象存储BOS中,方便高速载入Palo。

建设仓库

可以通过管理控制台来创建集群。集群有两种节点:

  • Lead Node:CPU 2核、内存10G、SSD 50G
  • Compute Node:CPU 2核、内存10G、SSD 50G、HDD 500G

由于Palo采用了MPP加Shared Nothing架构,集群的容量和性能与Compute Node成正比。为了保证性能和数据高可用,推荐至少使用三个Compute Node。集群创建以后Compute Node的个数是可以调整的,以便随时伸缩集群保证业务。

因为Palo跟MySQL在语法上是兼容的,集群创建完毕后,可以按照管理控制台给出的连接信息通过MySQL Workbench连接:

big-data-palo-02

导入数据之前,首先需要创建数据库:

CREATE DATABASE ssb;
USE ssb;

然后便可以创建表格:

CREATE TABLE CUSTOMER
(
    C_CUSTKEY     INTEGER,
    C_NAME        VARCHAR(25),
    C_ADDRESS     VARCHAR(40),
    C_CITY        VARCHAR(10),
    C_NATION      VARCHAR(15),
    C_REGION      VARCHAR(12),
    C_PHONE       VARCHAR(15),
    C_MKTSEGMENT  VARCHAR(10)
)
DISTRIBUTED BY RANDOM BUCKETS 8 PROPERTIES ("replication_num"="3");

其中:

  1. DISTRIBUTED BY RANDOM BUCKETS 8表示把记录随机分配到8个桶里。Palo还支持根据字段的哈希值分桶,需要根据数据的实际特征来选择。
  2. replication_num用来指定副本数目,Compute Node个数大于等于3时建议设置为3,以取得优越的性能和冗余。道理很简单,重要的事情说三遍嘛。
  3. Palo还支持PARTITION使用指定的列和数据范围对数据进行分区,分而治之以提高数据使用效率,这里不再赘述。

接着可以从BOS里面导入数据了:

LOAD LABEL LABEL_CUSTOMER
(
    DATA INFILE("bos://datamart/dbgen/customer.tbl")
    INTO TABLE `CUSTOMER`
    COLUMNS TERMINATED BY "|"
    (
        C_CUSTKEY,
        C_NAME,
        C_ADDRESS,
        C_CITY,
        C_NATION,
        C_REGION,
        C_PHONE,
        C_MKTSEGMENT
    )
)
PROPERTIES
(
    "bos_accesskey"="YOURACCESSKEY",
    "bos_secret_accesskey"="YOURSECRETKEY",
    "bos_endpoint"="http://bj.bcebos.com"
);

导入命令是一个异步命令,命令返回之后可以用SHOW LOAD命令查看导入的进度,失败的话也会提供错误信息。

其他数据创建与导入语句列举如下:

CREATE TABLE DATE
(
    D_DATEKEY          INTEGER,
    D_DATE             VARCHAR(18),
    D_DAYOFWEEK        VARCHAR(9),
    D_MONTH            VARCHAR(9),
    D_YEAR             INTEGER,
    D_YEARMONTHNUM     INTEGER,
    D_YEARMONTH        VARCHAR(7),
    D_DAYNUMINWEEK     INTEGER,
    D_DAYNUMINMONTH    INTEGER,
    D_DAYNUMINYEAR     INTEGER,
    D_MONTHNUMINYEAR   INTEGER,
    D_WEEKNUMINYEAR    INTEGER,
    D_SELLINGSEASON    VARCHAR(12),
    D_LASTDAYINWEEKFL  INTEGER,
    D_LASTDAYINMONTHFL INTEGER,
    D_HOLIDAYFL        INTEGER,
    D_WEEKDAYFL        INTEGER
)
DISTRIBUTED BY RANDOM BUCKETS 8 PROPERTIES ("replication_num"="3");

LOAD LABEL LABEL_DATE
(
    DATA INFILE("bos://datamart/dbgen/date.tbl")
    INTO TABLE `DATE`
    COLUMNS TERMINATED BY "|"
    (
        D_DATEKEY,
        D_DATE,
        D_DAYOFWEEK,
        D_MONTH,
        D_YEAR,
        D_YEARMONTHNUM,
        D_YEARMONTH,
        D_DAYNUMINWEEK,
        D_DAYNUMINMONTH,
        D_DAYNUMINYEAR,
        D_MONTHNUMINYEAR,
        D_WEEKNUMINYEAR,
        D_SELLINGSEASON,
        D_LASTDAYINWEEKFL,
        D_LASTDAYINMONTHFL,
        D_HOLIDAYFL,
        D_WEEKDAYFL
    )
)
PROPERTIES
(
    "bos_accesskey"="YOURACCESSKEY",
    "bos_secret_accesskey"="YOURSECRETKEY",
    "bos_endpoint"="http://bj.bcebos.com"
);

CREATE TABLE SUPPLIER
(
    S_SUPPKEY     INTEGER,
    S_NAME        VARCHAR(25),
    S_ADDRESS     VARCHAR(25),
    S_CITY        VARCHAR(10),
    S_NATION      VARCHAR(15),
    S_REGION      VARCHAR(12),
    S_PHONE       VARCHAR(15)
)
DISTRIBUTED BY RANDOM BUCKETS 8 PROPERTIES ("replication_num"="3");

LOAD LABEL LABEL_SUPPLIER
(
    DATA INFILE("bos://datamart/dbgen/supplier.tbl")
    INTO TABLE `SUPPLIER`
    COLUMNS TERMINATED BY "|"
    (
        S_SUPPKEY,
        S_NAME,
        S_ADDRESS,
        S_CITY,
        S_NATION,
        S_REGION,
        S_PHONE
    )
)
PROPERTIES
(
    "bos_accesskey"="YOURACCESSKEY",
    "bos_secret_accesskey"="YOURSECRETKEY",
    "bos_endpoint"="http://bj.bcebos.com"
);

CREATE TABLE PART
(
    P_PARTKEY     INTEGER,
    P_NAME        VARCHAR(22),
    P_MFGR        VARCHAR(6),
    P_CATEGORY    VARCHAR(7),
    P_BRAND       VARCHAR(9),
    P_COLOR       VARCHAR(11),
    P_TYPE        VARCHAR(25),
    P_SIZE        INTEGER,
    P_CONTAINER   VARCHAR(10)
)
DISTRIBUTED BY RANDOM BUCKETS 8 PROPERTIES ("replication_num"="3");

LOAD LABEL LABEL_PART
(
    DATA INFILE("bos://datamart/dbgen/part.tbl")
    INTO TABLE `PART`
    COLUMNS TERMINATED BY "|"
    (
        P_PARTKEY,
        P_NAME,
        P_MFGR,
        P_CATEGORY,
        P_BRAND,
        P_COLOR,
        P_TYPE,
        P_SIZE,
        P_CONTAINER
    )
)
PROPERTIES
(
    "bos_accesskey"="YOURACCESSKEY",
    "bos_secret_accesskey"="YOURSECRETKEY",
    "bos_endpoint"="http://bj.bcebos.com"
);

CREATE TABLE LINEORDER
(
    LO_ORDERKEY       BIGINT,
    LO_LINENUMBER     BIGINT,
    LO_CUSTKEY        INTEGER,
    LO_PARTKEY        INTEGER,
    LO_SUPPKEY        INTEGER,
    LO_ORDERDATE      INTEGER,
    LO_ORDERPRIOTITY  VARCHAR(15),
    LO_SHIPPRIOTITY   INTEGER,
    LO_QUANTITY       BIGINT,
    LO_EXTENDEDPRICE  BIGINT,
    LO_ORDTOTALPRICE  BIGINT,
    LO_DISCOUNT       BIGINT,
    LO_REVENUE        BIGINT,
    LO_SUPPLYCOST     BIGINT,
    LO_TAX            BIGINT,
    LO_COMMITDATE     INTEGER,
    LO_SHIPMODE       VARCHAR(10)
)
DISTRIBUTED BY RANDOM BUCKETS 8 PROPERTIES ("replication_num"="3");

LOAD LABEL LABEL_LINEORDER
(
    DATA INFILE("bos://datamart/dbgen/lineorder.tbl")
    INTO TABLE `LINEORDER`
    COLUMNS TERMINATED BY "|"
    (
        LO_ORDERKEY,
        LO_LINENUMBER,
        LO_CUSTKEY,
        LO_PARTKEY,
        LO_SUPPKEY,
        LO_ORDERDATE,
        LO_ORDERPRIOTITY,
        LO_SHIPPRIOTITY,
        LO_QUANTITY,
        LO_EXTENDEDPRICE,
        LO_ORDTOTALPRICE,
        LO_DISCOUNT,
        LO_REVENUE,
        LO_SUPPLYCOST,
        LO_TAX,
        LO_COMMITDATE,
        LO_SHIPMODE
    )
)
PROPERTIES
(
    "bos_accesskey"="YOURACCESSKEY",
    "bos_secret_accesskey"="YOURSECRETKEY",
    "bos_endpoint"="http://bj.bcebos.com"
);

查询数据

现在可以随意进行查询了!比如年和国家的利润汇总:

SELECT 
    D_YEAR,
    C_NATION,
    SUM(LO_REVENUE - LO_SUPPLYCOST) AS PROFIT
FROM
    `DATE`,
    `CUSTOMER`,
    `SUPPLIER`,
    `PART`,
    `LINEORDER`
WHERE
    LO_CUSTKEY = C_CUSTKEY
        AND LO_SUPPKEY = S_SUPPKEY
        AND LO_PARTKEY = P_PARTKEY
        AND LO_ORDERDATE = D_DATEKEY
        AND C_REGION = 'AMERICA'
        AND S_REGION = 'AMERICA'
        AND (P_MFGR = 'MFGR#1' OR P_MFGR = 'MFGR#2')
GROUP BY D_YEAR , C_NATION
ORDER BY D_YEAR , C_NATION;

结果如下图所示:

big-data-palo-03

由于1997年和1998年的利润下降非常明显,还可以根据P_CATEGORY继续下钻:

SELECT 
    D_YEAR,
    S_NATION,
    P_CATEGORY,
    SUM(LO_REVENUE - LO_SUPPLYCOST) AS PROFIT
FROM
    `DATE`,
    `CUSTOMER`,
    `SUPPLIER`,
    `PART`,
    `LINEORDER`
WHERE
    LO_CUSTKEY = C_CUSTKEY
        AND LO_SUPPKEY = S_SUPPKEY
        AND LO_PARTKEY = P_PARTKEY
        AND LO_ORDERDATE = D_DATEKEY
        AND C_REGION = 'AMERICA'
        AND S_REGION = 'AMERICA'
        AND (D_YEAR = 1997 OR D_YEAR = 1998)
        AND (P_MFGR = 'MFGR#1' OR P_MFGR = 'MFGR#2')
GROUP BY D_YEAR , S_NATION , P_CATEGORY
ORDER BY D_YEAR , S_NATION , P_CATEGORY;

所有的查询都在毫秒级别完成!

由于Palo跟MySQL在语法上是兼容的,还可以通过MySQL的ODBC/JDBC驱动写代码交互,你懂的。

背后原理

表面上用户体验平淡无奇,实际上Palo的存储和计算都有着精妙的设计以保证在PB基本的数据量时仍然提供秒级的复杂查询性能。

本质上,Palo是列存储数据库(Columnar Database),采用了并行数据库(Massive Parallel Database)的无共享(Shared Nothing)架构。也就是说,除了网络,节点都是独享自己的计算和存储,保证了系统的可扩展性。

Palo的存储引擎有以下特性:

  • 分布式存储引擎,单表容量可以到PB级别。
  • 列存储,通过高效压缩和编码降低存储,并支持智能索引提高查询性能。
  • 基于廉价硬件而不是定制硬件,保证可靠性的同时大大降低成本。
  • 高效的从BOS存储分布式数据导入。
  • 自动副本均衡以及副本修复。

Palo查询引擎有以下特性:

  • 分布式并行执行,支持大表分布式连接(Join)。
  • 基于成本的优化器,通过综合评估选择最低成本的执行途径。
  • 包括谓词下推和分区剪枝等查询优化。
  • 面向列存储的向量执行优化。

最后,Palo也秉承了开放云开源开放的宗旨,与MySQL接口兼容,方便用户迁移也不用担心被供应商锁定。当然,另一个好处就是可以与BI工具无缝兼容,毕竟通过SQL语句与Palo交互不够高大上,请听下回分解。



张 琪

One Comment

发表评论

电子邮件地址不会被公开。 必填项已用*标注