Firebolt whitepaper - 2. Firebolt cloud data warehouse

官方文档的技术白皮书
官方文档
https://www.firebolt.io/resources/firebolt-cloud-data-warehouse-whitepaper

Firebolt cloud data warehouse Firebolt云数据仓库

The Firebolt cloud data warehouse was architected to deliver the speed and efficiency at scale needed for ad hoc, interactive operational and customer-facing analytics. Like some of its predecessors, it is built on a decoupled storage and compute architecture. But it adds to the previous generations of cloud data warehouses by optimizing decoupled storage and compute together to improve speed, scale, and efficiency.

Firebolt云数据仓库的架构旨在为特定的、交互式的运营和面向客户的分析提供所需的速度和效率。像它的一些前辈一样，它是建立在一个解耦的存储和计算体系结构上的。但它通过优化解耦的存储和计算来提高速度、规模和效率，从而增加了前几代云数据仓库的功能。

图1-Firebolt存储计算分离架构

Like other modern cloud data warehouses, Firebolt is a multi-tenant SaaS cloud data warehouse. It supports ANSI SQL via JDBC drivers and REST APIs.

与其他现代云数据仓库一样，Firebolt是一个多租户SaaS云数据仓库。它通过JDBC驱动程序和REST api在与其他云数据仓库类似的级别上支持ANSI SQL。

Firebolt is also built on a decoupled storage and compute architecture that enables horizontal, linear data and compute scalability, with the ability to both scale and isolate compute workloads and users. Storage, like others, is built on S3. In the case of Firebolt, the compute clusters are called engines. They are used both for ingestion and all types of analytics. Workload isolation is simple. Whenever you need to support a new isolated workload, you start up a new engine with a few clicks. You can also scale up and down with a few clicks the same way. Once users connect to an engine and send queries, data is fetched and cached locally in each node of the engine.

Firebolt还建立在一个解耦的存储和计算架构上，支持水平、线性扩展数据和计算可伸缩性，具有扩展和隔离计算工作负载和用户的能力。与其他存储一样，存储也构建在S3之上。在Firebolt的例子中，计算集群被称为引擎。它们被用于写入和所有类型的分析。工作负载隔离很简单。每当需要支持新的独立工作负载时，只需单击几下鼠标就可以启动一个新引擎。你也可以用同样的方式点击几下缩放。一旦用户连接到引擎并发送查询，数据就会被获取并缓存到引擎的每个节点的本地。

Firebolt has added features to help with performance and efficiency that other data warehouses do not have. The biggest differences are how decoupled compute and storage are optimized together for performance, scale and efficiency:

Firebolt增加了其他数据仓库不具备的特性，以帮助提高性能和效率。最大的区别在于解耦计算和存储如何在性能、规模和效率方面进行优化:

• Storage is managed across tiers - While storage is decoupled from compute, all data is managed through a common data access layer as a single logical data set across remote storage and SSD data caches on nodes. While remote storage has segments, only much smaller data ranges are fetched and cached in each node’s SSD.

  存储跨层管理—存储与计算解耦，所有数据通过公共数据访问层作为单个逻辑数据集，跨远程存储和节点上的SSD数据缓存进行管理。虽然远程存储有数据段，但只有小得多的数据范围被拉取和缓存到每个节点的SSD中。

• Indexes accelerate data access and operations - Firebolt uses indexing extensively to improve performance. Each table has a sparse (primary) index consisting of any number of columns in any order. In addition, there are indexes that help with aggregations or joins. Indexes dramatically improve performance and overcome some of the limitations of decoupled storage and compute. They are also updated during ingestion, which supports real-time streaming analytics use cases.

  索引加速数据访问和操作—Firebolt广泛使用索引来提高性能。每个表都有一个稀疏(主)索引，它由任意数量、任意顺序的列组成。此外，还有一些索引可以帮助进行聚合、字符串搜索或连接。索引极大地提高了性能，并克服了解耦存储和计算的一些限制。它们还会在写入期间更新，从而支持实时流分析场景。

• Query optimization is compute- and storage-aware - Firebolt optimizes queries to take advantage of the existing location of data across nodes and drive future data distribution to improve query performance on each engine.

  查询优化是计算和存储感知的—Firebolt优化查询，利用节点间现有数据的位置，并驱动未来的数据分布，以提高每个引擎上的查询性能。

• Distributed, dynamic query execution built for performance and efficiency - The Firebolt query execution engine builds on the storage, indexing, and query optimization, vectorized processing, and a host of other execution techniques to maximize performance and price-performance.

  基于性能和效率构建的分布式、动态查询计划—Firebolt查询执行引擎基于存储、索引和查询优化，然后使用LLVM(低级别虚拟机)进行下推优化、向量化处理、以及许多其他的执行技术，以最大限度地提高性能和性价比。

This optimization of storage and compute with indexing reduces both remote data access and the amount of data cached by 10x or more, which also leads to 10x less data to scan and process. Query optimization and indexing reduces the amount of data and computing needed even more. The combination has helped customers improve query performance 4-6000x while improving price performance 10x or more.

这种使用索引的存储和计算优化既减少了远程数据访问，也减少了缓存10倍或更多的数据量，这也导致需要扫描和处理的数据减少了10倍。查询优化和索引减少了所需的数据量和计算量。这种组合帮助客户将查询性能提高4-6000x，同时将价格性能提高10倍或更多。

Firebolt Storage Firebolt存储

Firebolt’s performance and efficiency improvements start with storage, which is built on the Firebolt File Format (F3), pronounced “Triple F”. Firebolt stores and manages data differently on each tier - ranging from remote storage, to local SSD data caches in each node. All data is accessed via a common data access layer that manages the entire data lifecycle from ingestion, data storage and indexes, to data access and caching across these tiers.

Firebolt的性能和效率提升从存储开始，它建立在Firebolt文件格式(F3)的基础上，发音为“Triple F”。Firebolt在每一层的数据存储和管理方式都不同——从远程存储到每个节点的本地SSD数据缓存。所有数据都是通过公共数据访问层来访问的，该层管理整个数据生命周期，从写入、数据存储和索引，到跨这些层的数据访问和缓存。

图2-F3 数据架构

The first big challenge with most decoupled storage today is data access latency, or the amount of time it takes to fetch all the data needed by a given query. In this case, the network bandwidth is the main bottleneck. AWS offers 10Gbps-100Gbps network bandwidths, which can transfer roughly 1-10 GB of data per second, at most. Even working with 100GB of data could easily lead to seconds of data access times for a query. You would need to wait a few minutes for 1TB to load into a cluster.

当今大多数存储计算分离的第一个大挑战是数据访问延迟，即获取给定查询所需的所有数据所花费的时间。在这种情况下，网络带宽是主要的瓶颈。AWS提供10Gbps-100Gbps的网络带宽，每秒最多可以传输1- 10gb的数据。即使使用100GB的数据，查询也很容易导致数秒的数据访问时间。您需要等待几分钟才能将1TB的容量加载到集群中。

The biggest way to lower access times is to reduce the amount of data fetched from storage. Most cloud data warehouses and query engines have very inefficient data access because they fetch entire partitions or segments of data from decoupled storage. Many implement partition-level pruning and sorting to reduce the number of partitions fetched. But fetching entire partitions makes no sense if you only need a fraction of the data in each partition. For many queries, especially lookups, you only need 1% or less of the partition.

降低访问时间的最好的方法是减少从存储中获取的数据量。大多数云数据仓库和查询引擎的数据访问效率非常低，因为它们从解耦存储中获取整个分区或段数据。许多算法实现分区级的剪枝和排序，以减少获取的分区数量。但是，如果您只需要每个分区中数据的一部分，那么获取整个分区是没有意义的。对于许多查询，特别是查找，您只需要1%或更少的分区。

Firebolt accesses much smaller ranges through the use of indexes. For example, Firebolt only fetches the data ranges it needs, not entire partitions or segments. What makes this possible is sparse indexing. In Firebolt, the unit of remote storage is a segment, and it can be any size (see the Data ingestion section below for more.) Within each segment are much smaller units called ranges. Firebolt only accesses much smaller ranges of data instead of larger segments. Storing only the ranges not only dramatically reduces data access times. It reduces the amount of data scanned, and scan times as well. Indexes can help in many other ways as well.

Firebolt通过使用索引来访问更小的范围。例如，Firebolt只获取它需要的数据范围，而不是整个分区或段。使这成为可能的是稀疏索引。在Firebolt中，远程存储的单位是一个段，它可以是任何大小(更多信息，请参阅下面的数据写入部分)。在每个段中有更小的单位，称为range。Firebolt只访问更小范围的数据，而不是更大的数据段。只存储range大大减少了数据访问时间。它减少了扫描数据的数量，以及扫描时间。索引还可以在许多其他方面提供帮助。

Indexes 索引

Despite the extensive use of indexes in relational databases (RDBMS), indexes have not been used extensively in most data warehouses. But indexes have been shown to improve both performance and efficiency by 1-3 orders of magnitude when used in columnar databases.

尽管索引在关系数据库(RDBMS)中被广泛使用，但是索引在大多数数据仓库中并没有被广泛使用。但是，在列存数据库中使用索引可以提高1-3个数量级的性能和效率。

Firebolt built F3 with native support for a broad range of indexes to improve performance, scalability, and price-performance, and new ones are constantly being added. This ever-growing list of indexes includes:

Firebolt在F3中提供了对各种索引的本地支持，以提高性能、可伸缩性和性价比，而且还不断添加新的索引。这个不断增长的索引列表包括:

• Sparse (primary) indexes 稀疏索引(主)
• Aggregating indexes 聚合索引
• Join indexes 连接索引
• 元数据索引
• 字符串的索引

You can think of indexes as improving performance and cost in two ways. A sparse, or primary index for each table can reduce the amount of data fetched, stored, and processed by compute 10x or more. Other indexes improve performance and cost even more by replacing data and compute with cached, precomputed results.

您可以将索引看作从两方面改进性能和成本。每个表的稀疏索引或主索引可以减少通过计算获取、存储和处理10倍以上的数据量。其他索引通过将数据和计算替换为缓存的、预先计算的结果来提高性能和降低成本。

Firebolt automatically updates indexes during each atomic data ingestion operation, which ensures indexes are always consistent with the data.

Firebolt在每个原子写入操作期间自动更新索引，这确保索引始终与数据一致。

Sparse indexes 稀疏索引

A sparse index dramatically reduces the amount of data fetched, stored, and processed for each query. It is the primary index of a table, composed of any number of columns listed in any order of a table, and is declared with a single line of SQL alongside the table definition.

稀疏索引极大地减少了为每个查询获取、存储和处理的数据量。它是表的主索引，由表中任意顺序任意数量的列组成，并在表定义旁边用一行SQL声明。

A sparse index dramatically reduces the amount of data fetched, stored, and processed for each query. It is the primary index of a table, composed of any number of columns listed in any order of a table, and is declared with a single line of SQL alongside the table definition.

在写入数据时，Firebolt会根据稀疏索引自动排序和压缩数据，并增量地更新每个索引。然后，数据和索引作为一个新段提交。在初始声明之后，管理员不需要进行任何维护，比如清理或重新平衡。为了提高性能，Firebolt会随着时间的推移自动优化和合并片段。

Data is always accessed via F3 using the sparse index to find and fetch an exact range of data instead of fetching entire segments, partitions, or blocks, which is what other cloud data warehouses do. Only fetching these much smaller ranges can result in 10x or less data fetched over the network the first time that data is needed. This reduces data access times 10x or more. Sparse indexes also improve query performance and require fewer resources because Firebolt only stores, scans, and processes the much smaller data ranges locally in SSD on the compute nodes, not the entire segments.

数据总是通过F3访问，使用稀疏索引来查找和获取精确range的数据，而不是获取整个段、分区或块，这是其他云数据仓库所做的。只有获取这些小得多的范围，才能在第一次需要数据时通过网络获取10倍或更少的数据。这将减少10倍或更多的数据访问次数。稀疏索引还提高了查询性能，并需要更少的资源，因为Firebolt只在计算节点的SSD上本地存储、扫描和处理小得多的数据范围，而不是整个段。

Aggregating indexes 聚合索引

Aggregations - ranging from COUNT or SUM to GROUP BY operations - are at the heart of any interactive analytics. But they can require a lot of compute. Some companies use materialized views to help pre-compute and store aggregations, but materialized views can be costly to maintain and limited in function. For example, several materialized views do not support COUNT DISTINCT.

聚合—从COUNT或SUM到GROUP BY操作—是任何交互式分析的核心，但它们需要大量的计算。一些公司使用物化视图来帮助预计算和存储聚合，但是物化视图的维护成本很高，而且功能有限。例如，一些物化视图不支持COUNT DISTINCT。您还需要重写查询以使用物化视图，除非查询优化器被设计为检测和使用它们。

Because of these limitations and costs, while many companies do use materialized view capabilities in the warehouse, many others have resorted to calculating their aggregations outside of the data warehouse using Spark or various ETL technologies. It can not only become costly to maintain. It can take weeks to get any changes done since you have to coordinate with a different team that then has to develop, test, and deploy the changes.

由于这些限制和成本，虽然许多公司确实在数据仓库中使用了物化视图功能，但其他许多公司却使用Spark或各种ETL技术在数据仓库之外计算它们的聚合。这不仅会增加维护成本。完成任何更改可能需要数周时间，因为您必须与不同的团队协调，然后开发、测试和部署改动。

Firebolt added aggregating indexes as a way for data engineers to do all this by adding one line of SQL when you create the fact table, or any time after. You can have any number of aggregating indexes associated with a table. Firebolt automatically populates and maintains each aggregating index - which can include raw data, aggregations, and other operators - during ingestion. Whenever the query optimizer sees an operation that can be performed faster using an aggregating index, it automatically adds the index into the query plan. A data engineer does not need to rewrite the query.

Firebolt添加了聚合索引，数据工程师可以在创建事实表时或之后添加一行SQL来完成所有这些工作。一个表可以有任意数量的聚合索引。在写入的过程中，Firebolt自动填充和维护每个聚合索引—可以包括原始数据、聚合和其他操作符。每当查询优化器看到使用聚合索引可以更快地执行的操作时，它就会自动将索引添加到查询计划中。数据工程师不需要重写查询。

Aggregating indexes are implemented as tables stored alongside the fact table, with their own sparse index to make queries faster. In many cases, they can replace fact tables as the main source for a query. For example, they can maintain many levels of granularity for GROUP BY and other aggregation operators automatically, such as COUNT DISTINCT.

聚合索引是作为存储在事实表旁边的表来实现的，它们有自己的稀疏索引，以使查询更快。在许多情况下，它们可以替换事实表作为查询的主要源。例如，它们可以自动维护groupby和其他聚合操作符(如COUNT DISTINCT)的许多粒度级别。

Whenever an aggregating index is used, it replaces the need to access, store and compute data with cached, pre-computed results. This improves price-performance by cutting out related data access times, cache and compute resources.

每当使用聚合索引时，它就用缓存的、预先计算的结果来代替访问、存储和计算数据的需要。通过减少相关数据访问时间提高了性价比。

Join indexes 连接索引

Firebolt has been extensively optimized to minimize the cost of joins, which is one of the most expensive operations in analytics.

Firebolt 尽可能的优化了JOIN操作，这是分析中最昂贵的操作之一。

A join index helps replace full table scans with lookups and other operators that are much faster, and much less expensive. Because indexes themselves are small, join indexes are stored in RAM to improve performance. For example, a join index can enable the query optimizer to turn multiple table scans into a single list of lookup operations.

连接索引帮助用查找和其他更快、更便宜的操作符替换全表扫描。因为索引本身很小，所以连接索引存储在内存中以提高性能。例如，连接索引可以使查询优化器将多个表扫描转换为单个查找操作。

Firebolt can also “pushdown” predicates to happen before joins to reduce the size of the fact table data set before the lookups as well. The query optimizer will find the best combination of predicate pushdowns and indexes based on the location of data across nodes to maximize performance. This includes optimization of nested joins and multiple levels of predicate pushdowns.

Firebolt还可以在连接之前使用“下推”谓词，从而在查找之前减少事实表数据集的大小。查询优化器将根据跨节点的数据位置找到谓词下推和索引的最佳组合，以最大化性能。这包括对嵌套连接和多级谓词下推的优化。

The combination of query optimization and join indexes help deliver sub-second query performance for complex, multi-terabyte table joins.

查询优化和连接索引的结合有助于为复杂的、tb级的表连接提供亚秒级的查询性能。

元数据索引

元数据索引有助于提高依赖于主索引以外的变量的操作符或谓词的查询性能。例如，一些元数据存储像COUNT这样的值。其他元数据跟踪MIN、MAX和其他值，以帮助进一步修剪范围。对于基数高的列，这种方法非常有效。Firebolt使用元数据和其他索引进行基于成本的查询优化。它还尽可能只使用索引运行查询，以避免访问数据，这有助于提高性能。

字符串索引

Firebolt有几个内置的索引来支持基于字符串的操作。默认情况下，稀疏索引支持字符串和LIKE操作。但是您也可以使用其他字符串索引，例如哈希索引来支持更快的EQUAL或GROUP BY操作。

Query optimization 查询优化

Firebolt’s extensive query optimization is one of the big reasons Firebolt is able to deliver sub-second query times. Each time a new query is received, the Firebolt query optimizer starts to build the best query plan just-in-time. It first looks to see where the data ranges are that it may need for the query across remote storage and the local cache within each node of the engine.

Firebolt广泛的查询优化是其能够提供秒级查询时间的主要原因之一。每次收到一个新的查询，Firebolt查询优化器就会立即开始构建最佳查询计划。它首先查看所需要的数据range在哪里，这些数据可能需要远程拉取，也查看本地缓存数据。

图3a-查询优化粒度

图3b-查询计划优化

Like several other data warehouses, the Firebolt Query optimizer parses each query, builds a logical query plan, and then applies plan transformations and rewrites to derive the best physical query plan.

与其他几个数据仓库一样，Firebolt Query优化器解析每个查询，构建逻辑查询计划，然后使用基于成本和其他优化技术来获得最佳的物理查询计划。

But there are several key differences with Firebolt that help improve performance and efficiency. One key difference is Firebolt’s use of sparse indexing. For each table, sparse indexing enables the F3 data access layer to reduce the amount of data fetched over the network, sometimes by 10x or more (Figure 3a.)

但是Firebolt有几个关键的不同之处，有助于提高性能和效率。一个关键的区别是Firebolt使用了稀疏索引。对于每个表，稀疏索引使F3数据访问层能够减少通过网络获取的数据量，有时减少10倍或更多(图3a)。

Another key difference is the level of optimization that is implemented. The query optimizer evaluates whether query performance can be improved by reordering query operations, or by using indexes in place of operations (Figure 3b.) It reorders operations to take advantage of predicate pushdowns, and uses aggregating and join indexes to further reduce both data access and scans. For example, you can “pushdown” a predicate as a filter prior to doing a join. This helps reduce data sets before accessing data or performing joins. Firebolt can pushdown operations to many levels. This makes a big difference with both star and snowflake schemas where multiple fact or dimension tables are involved. Firebolt can also pushdown GROUP BY and other operations multiple levels.

另一个关键区别是实现的优化级别。查询优化器评估查询性能是否可以通过重新排序查询操作或使用索引代替来提高查询性能(图3b)。重排序有利于谓词下推，并使用聚合和连接索引来进一步减少数据访问和扫描。例如，您可以在执行联接之前将谓词“下推”为过滤器。这有助于在访问数据或执行连接之前减少数据集。Firebolt可以将下推进行到多个层级。这对于涉及多个事实表或维度表的星形模式和雪花模式有很大的不同。Firebolt还可以在多个级别下推GROUP BY和其他操作，而其他少数数据库只能支持下推一个层级。

图4a-优化前的TPC-DS SQL

图4b-Firebolt优化后的逻辑执行计划

What might start as a query with complex nested joins along with several WHERE (predicate), GROUP BY, and ORDER BY operations (Figure 4a) can be replaced with several levels of pushdown predicates and LOOKUP operations (Figure 4b) - all of which can dramatically improve performance by reducing the amount of data needed from storage and the number of scans. The final result is a physical distributed query plan assembled just-in-time to deliver the best performance based on the available indexes and the location of all the data needed across nodes for the query.

一个复杂的嵌套连接查询和几个WHERE(谓词)，GROUP BY，和ORDER BY操作(图4a)可以被几个级别的谓词下推和LOOKUP操作(图4b)所替代—所有这些操作都可以通过减少从存储获取的数据量和扫描次数来显著提高性能。最终的结果是一个实时组装的物理分布式查询计划，根据可用索引和查询所需的所有节点数据的位置提供最佳性能。

Query execution 查询执行

The final step is the actual query execution. Many of the innovations with query execution come from the integration with query optimization, storage, and indexing to maximize performance, scale, and efficiency. Federated query engines such as Presto are not as coupled to storage. Others such as Snowflake do not distinguish between storage and SSD, and store entire partitions in cache, not more specific ranges. Firebolt tracks which data is in storage and cache, and then builds a query plan to maximize performance.

最后一步是实际的查询执行。查询执行方面的许多创新都来自于与查询优化、存储和索引的集成，以最大化性能、规模和效率。像Presto这样的联邦查询引擎与存储的耦合程度不高。其他如Snowflake不区分远程存储和SSD，并将整个分区存储在缓存中，而不是更具体的范围。Firebolt跟踪哪些数据在存储和缓存中，然后构建查询计划以最大化性能。

图5-Firebolt物理执行计划的执行过程

Within the query execution engine there are several innovations inherited from public research and previous generations of on premises and cloud data warehouses. Two of the more common ones are:

在查询执行引擎中，有一些继承自公共研究和前几代的数据仓库和云数据仓库的创新。其中两个比较常见的是:

• Vectorized processing: Firebolt leverages query vectorization to process entire columns and data ranges instead of just one row at a time. Grouping entire columns into single operations dramatically accelerates performance by improving CPU cache locality and taking advantage of SIMD instructions.

  向量化处理：Firebolt利用查询向量化来处理整个列和数据范围，而不是一次只处理一行。通过提高 CPU 缓存局部性和利用 SIMD 指令，将整个列分组为单次计算可以显著提高性能。

• LLVM (Low Level Virtual Machine)： Firebolt用C/ C++构建，使用LLVM在执行前快速动态的优化查询计划。

There are also several innovations that are unique to Firebolt, discovered and implemented based on internal research and work with customers. Beyond the pushdown and query optimization techniques mentioned, Firebolt optimizes data and compute collocation.

Firebolt也有一些独特的创新，它们是通过内部研究和与客户合作发现和实现的。除了上面提到的下推和查询优化技术之外，Firebolt还优化了数据和计算配置，通过使用机器和深度学习技术，预计会有更多的改进。

• Index-based optimization: Each physical query plan is built in part using the indexes to build the fastest queries based on the current location of data ranges. An initial set of ranges is gathered as an index “set” which gets reduced through the query optimization process. This final index set is used to execute the query.

  基于索引的优化：每个物理查询计划都部分地使用索引来构建基于当前数据范围位置的最快查询。一个初始的范围集被收集为一个索引“集”，通过查询优化过程减少索引“集”。这个最终的索引集用于执行查询。

• Automatic data rebalancing: During ingestion, Firebolt will look to rebalance, or combine segments and their indexes in S3 as needed. There is no need to “vacuum” and do other types of manual rebalancing or maintenance.‍

  自动数据平衡：在写入的过程中，Firebolt将寻求数据平衡，或者根据需要在S3中合并段及其索引。不需要“真空”，也不需要做其他类型的手动重新平衡或维护。‍

• Reuse of compute sub-trees and operators: Beyond just reusing data in caches, Firebolt also reuses parts of query plans that it has already compiled. The collocation of data and compute increases reuse, further improving end-to-end performance.‍

  计算子树和运算符的重用：除了重用缓存中的数据外，Firebolt还重用它已经编译的部分查询计划。数据和计算的配置增加了重用，进一步提高了端到端性能。‍

• Explain plans: Firebolt exposes the logical query plans (Figure 4b) to help users understand the optimizations. It also collects statistics on performance. Firebolt is increasingly using this information to help make recommendations.

  解释计划：Firebolt公开了逻辑查询计划(图4b)，以帮助用户理解优化。它还收集性能统计信息。Firebolt使用这些信息来帮助提出更多的建议。