其他
OLAP数仓入门：基础篇
1. 什么是OLAP，其与OLTP有什么区别？
OLTP是Online transaction processing的英文缩写，指在线/联机事务处理，这么说其实还是比抽象的。OLTP典型的应用领域包括银行、证劵等金融行业，电子商务系统等，在此举最经典的银行例子，我们在招商银行APP上查询账户余额、收支信息和转账记录，在ATM机上存钱，取钱，将招行账号的钱转到工行账号上。这些都是典型的OLTP类操作，这些操作都比较简单，主要是对数据库中的数据进行增删改查。操作主体一般是产品的用户。
OLAP是Online analytical processing的英文缩写，指联机分析处理。从字面上我们能看出是做分析类操作。通过分析数据库中的数据来得出一些结论性的东西。比如给老总们看的报表，用于进行市场开拓的用户行为统计，不同维度的汇总分析结果等等。操作主体一般是运营、销售和市场等团队人员而不是用户。
单次OLTP处理的数据量比较小，所涉及的表非常有限，一般仅一两张表。而OLAP是为了从大量的数据中找出某种规律性的东西，经常用到count()、sum()和avg()等聚合方法，用于了解现状并为将来的计划/决策提供数据支撑，所以对多张表的数据进行连接汇总非常普遍。
为了表示跟OLTP的数据库（database）在数据量和复杂度上的不同，一般称OLAP的操作对象为数据仓库（data warehouse），简称数仓。数据库仓库中的数据，往往来源于多个数据库，以及相应的业务日志。
下表是对OLTP和OLAP的简单总结。
2. MySQL等OLTP数据库能处理OLAP业务吗？
MySQL是当前最流行的开源数据库，一般作为OLTP数据库使用。在MySQL上也能执行一些OLAP操作，但这不是MySQL擅长的领域。虽然OLTP和OLAP都是通过SQL来执行，但SQL语句只是描述了我想要什么，而并没有说明应该怎么做（不考虑hint等），即确定最优的执行计划。由于OLTP操作比较简单，所涉及的表也少，因此不需要相应的数据库具有强大的执行优化能力，比如说MySQL在查询优化这块就比较弱，但这其实没有给它的大规模普及使用造成多大伤害。
当然，MySQL也在快速进步，尤其是最新的8.0版本，在查询优化模块添加了很多众望所归的功能特性，包括窗口函数，通用表达式和更强大的Join能力等。
3. OLAP的查询跟OLTP查询具体有那些不一样？
上文简要提及，OLTP查询一般仅涉及单表，点查为主，返回的是记录本身或该记录的多个列。即使是范围查询，基本上也会通过limit来限制返回的记录数。
而OLAP则不同，表中单条记录本身并不是查询所关心的，比较典型的特点包括有聚合类算子、涉及多表Join，查询所用谓语/条件没有索引，玩玩不是返回记录。由于这些操作都非常耗计算资源，而且数据仓库相比数据库在数据量上大很多，因此，OLAP类查询经常表现为cpu-bound而不是io-bound。
这样说可能还不够直观，下面换种形式。OLTP和OLAP发展到现在已经比较成熟，业界也有些公认的benchmark来进行性能评估。我们可以通过这些benchmark中的对应sql来了解两位服务的典型查询语句。对于OLTP来说，有sysbench和tpcc测试套件，对于OLAP来说，有tpch和tpcds两种。这里分别例举sysbench oltp和tpcds的sql作为参考。
sysbench oltp查询
local stmt_defs = {
point_selects = {
"SELECT c FROM sbtest%u WHERE id=?",
t.INT},
simple_ranges = {
"SELECT c FROM sbtest%u WHERE id BETWEEN ? AND ?",
t.INT, t.INT},
sum_ranges = {
"SELECT SUM(k) FROM sbtest%u WHERE id BETWEEN ? AND ?",
t.INT, t.INT},
order_ranges = {
"SELECT c FROM sbtest%u WHERE id BETWEEN ? AND ? ORDER BY c",
t.INT, t.INT},
distinct_ranges = {
"SELECT DISTINCT c FROM sbtest%u WHERE id BETWEEN ? AND ? ORDER BY c",
t.INT, t.INT},SELECT c FROM sbtest10 WHERE id=4352
SELECT c FROM sbtest10 WHERE id BETWEEN 5046 AND 5046+99 ORDER BY c
SELECT c FROM sbtest3 WHERE id BETWEEN 4983 AND 4983+99
SELECT SUM(K) FROM sbtest1 WHERE id BETWEEN 4981 AND 4981+99
SELECT DISTINCT c FROM sbtest3 WHERE id BETWEEN 4989 AND 4989+99 ORDER BY chttps://github.com/akopytov/sysbench/blob/master/src/lua/oltp_common.luahttps://github.com/Percona-Lab/sysbench-tpcc/blob/master/tpcc_run.lua-- SELECT c_id
-- FROM customer
-- WHERE c_w_id = :c_w_id
-- AND c_d_id = :c_d_id
-- AND c_last = :c_last
-- ORDER BY c_first;
-- SELECT c_balance, c_first, c_middle, c_last
-- FROM customer
-- WHERE c_w_id = :c_w_id
-- AND c_d_id = :c_d_id
-- AND c_id = :c_id;
-- SELECT c_discount, c_last, c_credit, w_tax
-- FROM customer, warehouse
-- WHERE w_id = :w_id
-- AND c_w_id = w_id
-- AND c_d_id = :d_id
-- AND c_id = :c_id;tpcds查询
-- query68
SELECT
"c_last_name"
, "c_first_name"
, "ca_city"
, "bought_city"
, "ss_ticket_number"
, "extended_price"
, "extended_tax"
, "list_price"
FROM
(
SELECT
"ss_ticket_number"
, "ss_customer_sk"
, "ca_city" "bought_city"
, "sum"("ss_ext_sales_price") "extended_price"
, "sum"("ss_ext_list_price") "list_price"
, "sum"("ss_ext_tax") "extended_tax"
FROM
${database}.${schema}.store_sales
, ${database}.${schema}.date_dim
, ${database}.${schema}.store
, ${database}.${schema}.household_demographics
, ${database}.${schema}.customer_address
WHERE ("store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk")
AND ("store_sales"."ss_store_sk" = "store"."s_store_sk")
AND ("store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk")
AND ("store_sales"."ss_addr_sk" = "customer_address"."ca_address_sk")
AND ("date_dim"."d_dom" BETWEEN 1 AND 2)
AND (("household_demographics"."hd_dep_count" = 4)
OR ("household_demographics"."hd_vehicle_count" = 3))
AND ("date_dim"."d_year" IN (1999 , (1999 + 1) , (1999 + 2)))
AND ("store"."s_city" IN ('Midway' , 'Fairview'))
GROUP BY "ss_ticket_number", "ss_customer_sk", "ss_addr_sk", "ca_city"
) dn
, ${database}.${schema}.customer
, ${database}.${schema}.customer_address current_addr
WHERE ("ss_customer_sk" = "c_customer_sk")
AND ("customer"."c_current_addr_sk" = "current_addr"."ca_address_sk")
AND ("current_addr"."ca_city" <> "bought_city")
ORDER BY "c_last_name" ASC, "ss_ticket_number" ASC
LIMIT 100
--query53
SELECT *
FROM
(
SELECT
"i_manufact_id"
, "sum"("ss_sales_price") "sum_sales"
, "avg"("sum"("ss_sales_price")) OVER (PARTITION BY "i_manufact_id") "avg_quarterly_sales"
FROM
${database}.${schema}.item
, ${database}.${schema}.store_sales
, ${database}.${schema}.date_dim
, ${database}.${schema}.store
WHERE ("ss_item_sk" = "i_item_sk")
AND ("ss_sold_date_sk" = "d_date_sk")
AND ("ss_store_sk" = "s_store_sk")
AND ("d_month_seq" IN (1200 , (1200 + 1) , (1200 + 2) , (1200 + 3) , (1200 + 4) , (1200 + 5) , (1200 + 6) , (1200 + 7) , (1200 + 8) , (1200 + 9) , (1200 + 10) , (1200 + 11)))
AND ((("i_category" IN ('Books ' , 'Children ' , 'Electronics '))
AND ("i_class" IN ('personal ' , 'portable ' , 'reference ' , 'self-help '))
AND ("i_brand" IN ('scholaramalgamalg #14 ' , 'scholaramalgamalg #7 ' , 'exportiunivamalg #9 ' , 'scholaramalgamalg #9 ')))
OR (("i_category" IN ('Women ' , 'Music ' , 'Men '))
AND ("i_class" IN ('accessories ' , 'classical ' , 'fragrances ' , 'pants '))
AND ("i_brand" IN ('amalgimporto #1 ' , 'edu packscholar #1 ' , 'exportiimporto #1 ' , 'importoamalg #1 '))))
GROUP BY "i_manufact_id", "d_qoy"
) tmp1
WHERE ((CASE WHEN ("avg_quarterly_sales" > 0) THEN ("abs"((CAST("sum_sales" AS DECIMAL(38,4)) - "avg_quarterly_sales")) / "avg_quarterly_sales") ELSE null END) > DECIMAL '0.1')
ORDER BY "avg_quarterly_sales" ASC, "sum_sales" ASC, "i_manufact_id" ASC
LIMIT 100
--query59
WITH
wss AS (
SELECT
"d_week_seq"
, "ss_store_sk"
, "sum"((CASE WHEN ("d_day_name" = 'Sunday ') THEN "ss_sales_price" ELSE null END)) "sun_sales"
, "sum"((CASE WHEN ("d_day_name" = 'Monday ') THEN "ss_sales_price" ELSE null END)) "mon_sales"
, "sum"((CASE WHEN ("d_day_name" = 'Tuesday ') THEN "ss_sales_price" ELSE null END)) "tue_sales"
, "sum"((CASE WHEN ("d_day_name" = 'Wednesday') THEN "ss_sales_price" ELSE null END)) "wed_sales"
, "sum"((CASE WHEN ("d_day_name" = 'Thursday ') THEN "ss_sales_price" ELSE null END)) "thu_sales"
, "sum"((CASE WHEN ("d_day_name" = 'Friday ') THEN "ss_sales_price" ELSE null END)) "fri_sales"
, "sum"((CASE WHEN ("d_day_name" = 'Saturday ') THEN "ss_sales_price" ELSE null END)) "sat_sales"
FROM
${database}.${schema}.store_sales
, ${database}.${schema}.date_dim
WHERE ("d_date_sk" = "ss_sold_date_sk")
GROUP BY "d_week_seq", "ss_store_sk"
)
SELECT
"s_store_name1"
, "s_store_id1"
, "d_week_seq1"
, ("sun_sales1" / "sun_sales2")
, ("mon_sales1" / "mon_sales2")
, ("tue_sales1" / "tue_sales2")
, ("wed_sales1" / "wed_sales2")
, ("thu_sales1" / "thu_sales2")
, ("fri_sales1" / "fri_sales2")
, ("sat_sales1" / "sat_sales2")
FROM
(
SELECT
"s_store_name" "s_store_name1"
, "wss"."d_week_seq" "d_week_seq1"
, "s_store_id" "s_store_id1"
, "sun_sales" "sun_sales1"
, "mon_sales" "mon_sales1"
, "tue_sales" "tue_sales1"
, "wed_sales" "wed_sales1"
, "thu_sales" "thu_sales1"
, "fri_sales" "fri_sales1"
, "sat_sales" "sat_sales1"
FROM
wss
, ${database}.${schema}.store
, ${database}.${schema}.date_dim d
WHERE ("d"."d_week_seq" = "wss"."d_week_seq")
AND ("ss_store_sk" = "s_store_sk")
AND ("d_month_seq" BETWEEN 1212 AND (1212 + 11))
) y
, (
SELECT
"s_store_name" "s_store_name2"
, "wss"."d_week_seq" "d_week_seq2"
, "s_store_id" "s_store_id2"
, "sun_sales" "sun_sales2"
, "mon_sales" "mon_sales2"
, "tue_sales" "tue_sales2"
, "wed_sales" "wed_sales2"
, "thu_sales" "thu_sales2"
, "fri_sales" "fri_sales2"
, "sat_sales" "sat_sales2"
FROM
wss
, ${database}.${schema}.store
, ${database}.${schema}.date_dim d
WHERE ("d"."d_week_seq" = "wss"."d_week_seq")
AND ("ss_store_sk" = "s_store_sk")
AND ("d_month_seq" BETWEEN (1212 + 12) AND (1212 + 23))
) x
WHERE ("s_store_id1" = "s_store_id2")
AND ("d_week_seq1" = ("d_week_seq2" - 52))
ORDER BY "s_store_name1" ASC, "s_store_id1" ASC, "d_week_seq1" ASC
LIMIT 100很显然，tpcds的查询复杂度相比oltp和tpcc高非常多。
4. 是否有可能将OLAP和OLTP统一起来？
目前有个趋势是将OLTP和OLAP相融合，在同一个系统中同时提供TP和AP 2种服务，即HTAP产品，国内的数据库创业公司PingCAP的TiDB即是其中的佼佼者。
但由于两者服务类型相差甚大，完全融合是很难的，如何解决AP业务对要求更高实时性和稳定性的TP业务带来影响，如何同时提供2种服务且2种服务与业界其他系统相比具备足够竞争力，这些都是很大的挑战。
在目前的HTAP系统中，一般通过存储层的数据多副本来进行针对AP和TP业务的不同方式的优化，使用多个副本来以行存方式更好满足TP业务，通过增加一个副本来以列存方式为AP业务提供服务。
在存储系统上，配置独立的计算/查询系统，分别满足TP和AP不同的要求。比如TP系统很重要的一个特点就是事务的ACID，而AP系统更加关心分布式并行查询能力。
5. 数仓有哪些基础知识和概念？
OLAP的查询语句比OLTP更复杂，显然是因为两则操作的数据集和目的都是不一样的。数据库模型是2维的关系-实体模型。而数仓则是多维立方体模型。相对来说，给数仓建模的难度更高。为此，有必要再介绍下输出基础知识和一些重要概念。
先来看看这张图，基于该图，介绍下数仓的数据来源，作用和存在方式。
二、说说数仓中数据的前世今生？
1. 数仓中的数据从何而来？
结构化数据：一般来自于数据库，比如MySQL等关系型数据库的表中保存的记录（rows）。即承担OLTP功能的数据载体。这类数据最好处理，因为数据表达方式作为规范，约束性最好 半结构化数据：该部分数据来源较多，包括用户行为日志（如app的页面访问记录）、平台或管理服务日志（tomcat、mysql等服务日志）等等，也包括存储于MongoDB等NoSQL数据库中的记录（Docs等）。这些数据一般以Json或XML等形式存在，在ETL时难度较大 非结构化数据：包括图片、音频、视频和网页等，这些数据非常复杂，信息量也很大，一般不会直接抽取出来直接保存到数仓中，而是记录他们的元数据信息（metadata），举图片为例，可能保存该图片的产生时间、格式、大小等等，至于图片本身，一般通过url链接保存在对象或文件存储系统中
2. 数仓的作用有哪些？
进行交互式/即席查询（ad-hoc） 用于报表类查询（BI Reporting） 进行数据分析类查询（Data Analytics） 用于数据挖掘类查询（Data Mining）
3. 数据在数仓中是如何组织的？
4. 什么是多维数据模型？
5. 什么是事实表？
6. 什么是维度表？
维度表（Dimension Table ）是依赖事实表而存在的，“皮之不存，毛将焉附”，没有事实表数据，维度表也就没有存在的意义。每个维度表都是对事实表中的每个列/字段进行展开描述。
比如事实表中的用户ID，就可以进一步展开成一张维度表，记录该用户ID实体的用户名、联系信息、地址信息、年龄、性别和注册方式等等；
基于数据模型及操作又可以引入数据立方体概念及对其的常见操作。
7. 什么是数据立方体？
8. 数据立方体有哪些常见操作？
推荐文章：
浅谈OLAP系统核心技术点
Kafka集群消息积压问题及处理策略
数据仓库架构和建设方法论
基于Hive进行数仓建设的资源元数据信息统计
关注大数据学习与分享，获取更多技术干货