从内存到外存：用数据库管理数据.qmd

---
title: "从内存到外存：用数据库管理数据"
execute: 
  eval: true
---

在现代数据分析和处理过程中，随着数据量的不断增加，单纯依赖内存来存储和处理数据已经变得不再现实。数据库作为一种高效的外存储解决方案，能够应对大规模数据管理的需求，为数据的存储、检索和处理提供了可靠保障。本章节将详细介绍如何通过数据库来管理和操作数据，从而实现数据持久化、快速查询和高效分析。通过学习数据库管理的基本概念、SQL语言的使用技巧以及数据库与R语言的无缝集成，可以掌握在大数据时代下，高效管理和利用数据的方法。同时，我们还会探讨另一类外存储数据处理方案，即基于Arrow或Polars的大数据系统，从而通过内存高效的数据交换格式和跨语言的互操作性来提升数据处理性能和灵活性。

## 磁盘数据处理

磁盘数据处理（On-Disk Data Processing）是一种在数据处理过程中主要依赖磁盘等外部存储设备来存储和处理数据的技术。当数据集的规模超出内存容量时，这种方法尤其有效，因为它能够利用磁盘的大容量来存储大量数据。磁盘数据处理通过将数据分块存储在磁盘上，并在需要时逐块读取和处理，从而避免了内存不足的问题。

这种方法的一个显著优势是其可扩展性，能够处理比内存容量大得多的数据集，非常适合大数据分析和处理任务。例如，数据库管理系统（如MySQL、PostgreSQL）、分布式文件系统（如Hadoop HDFS）以及流式处理框架（如Apache Kafka）都广泛使用磁盘数据处理技术。然而，由于磁盘的读写速度相对较慢，磁盘数据处理可能会面临较高的I/O延迟。为了优化性能，通常会采用高速缓存、数据预取和并行I/O操作等技术。

总的来说，磁盘数据处理通过有效利用外部存储设备，为大规模数据处理提供了一种解决方案。尽管存在I/O速度较慢的挑战，但通过适当的优化，可以在大数据环境中实现高效的数据处理和分析。在本章中，我们会描述如何在R环境中调用数据库资源，同时还会介绍另一类新兴的大数据处理系统（Arrow和Polars），这些大数据处理方案允许用户把数据先存储为Parquet格式，实际处理的时候不需要把数据载入环境就能够对大数据进行分析。通过对这些工具的介绍，我们可以有效地利用计算机的磁盘资源来对比内存大的数据进行高效处理。

## 数据库操作——以duckdb为例

在实际应用中，大量数据存储在数据库，因此掌握如何访问这些数据至关重要。如果每次访问数据都需要请求数据库管理人员，这样会非常麻烦，因此在保障数据安全的前提下，最佳的方案是我们能够直接对数据库的数据进行自由访问。本部分会介绍如何使用 DBI 包连接到数据库，并通过SQL查询检索数据。SQL（Structured Query Language），即结构化查询语言，是数据库的通用语言，是所有数据科学家都需要掌握的重要工具。然而，在R语言中对数据库进行访问可以跳过对SQL的学习（如果你尚未掌握SQL的话），直接使用**dplyr**的核心函数来直接对数据进行筛选、排序、分组汇总等各式操作，因为底层能够借助**dbplyr**工具包把**dplyr**的代码转为SQL代码，从而完成对数据库的控制。下面我们将会循序渐进地介绍如何在R中对数据库的资源进行访问和处理。

### 基本环境配置

在本部分，我们会加载需要的R包。其中，**DBI**包负责对数据库进行连接并执行SQL语句，**dbplyr**负责把dplyr语句转换为SQL语句，而**tidyverse**包则包含了各种数据处理的基本操作函数。这里我们会以控制DuckDB 数据库为例，因此同时会加载**duckdb**包。执行代码如下：

```{r}
library(pacman)
p_load(DBI,dbplyr,tidyverse,duckdb)
```

这里我们稍微对DuckDB 数据库进行一个介绍（标识见图[-@fig-duckdb]），DuckDB 是一个嵌入式的 SQL 数据库管理系统，旨在提供高效的数据查询和处理功能。它设计用于数据分析和应用程序中的嵌入式数据库需求，支持标准的 SQL 查询语言，同时具备优秀的性能和低延迟。DuckDB 的特点包括内存友好型设计，支持在内存中处理大规模数据集，同时具备与多核处理器和并行计算环境的良好集成能力。它还提供了与许多流行数据分析工具的集成接口，如 R和Python，使得用户可以轻松地在其数据分析工作流中使用 DuckDB 进行快速和高效的数据查询与处理。

```{r}
#| label: fig-duckdb
#| fig-cap: "DuckDB数据库Logo"
#| echo: false
#| eval: true
knitr::include_graphics("fig/duckdb.png")
```

### 数据库的连接

万事开头难，对数据库操作的第一步就是必须让R环境与数据库连接起来。在R中要与数据库连接，一般需要两个包：其一是**DBI**，这个包提供了用于数据库连接、数据传输、执行查询的通用函数；其二是针对用户连接数据库系统的定制包，这些包能够把**DBI**命令转化为特定数据库系统能够解读的命令，比如要使用SQLite就需要**RSQLite**包，使用PostgreSQL就需要使用**PostgreSQL**包。对于咱们的试验来说，需要使用**duckdb**包来完成这个操作，实现方法如下：

```{r}
con = dbConnect(duckdb())
```

需要注意的是，这里我们创建的是一个虚拟临时数据库，因此当我们推出R环境的时候数据库就会自动被清楚，非常适合用来进行一次性的试验。如果需要连接一个已经存在的数据库，或者创建一个新的数据库，只需要对相关的参数（*dbdir*）进行设置即可。如果要连接不同的数据库，那么连接的时候需要的参数也会有所不同，相关说明可以参阅[DBI::dbConnect函数的帮助文档](https://dbi.r-dbi.org/reference/dbConnect.html)。

### 数据操作基础

在创建了数据库连接后，首先我们可以对这个数据库载入数据，这可以使用`dbWriteTable`函数进行实现：

```{r}
# 把iris数据集载入到数据库中
dbWriteTable(con, "iris", iris)

# 把ggplot2中的diamonds数据集载入到数据库中
dbWriteTable(con, "diamonds", diamonds)
```

在上面的函数中，我们知道在函数中需要声明3个要素，分别是数据库连接、表名称和数据。载入之后，我们可以观察一下数据库中都有哪些表：

```{r}
dbListTables(con)
```

如果要取出里面的表格，比如我们想要取出iris数据集，有两种方法：

```{r}
# 方法1：使用dbReadTable
con %>% 
  dbReadTable("iris") %>% 
  as_tibble()

# 方法2：使用tbl
tbl(con,"iris") %>% 
  as_tibble()
```

在上面两种方法中，方法1的`as_tibble`其实可以去除，我们只是为了显示方便，所以进行这一步操作，但是即使没有这样操作也可以得到传统的数据框结构。在方法2中，则必须使用`as_tibble`表示对数据进行调用，事实上也可以使用`collect`函数对数据进行提取。当然， 还有一种方案就是直接写SQL语句对数据进行查询，方法如下：

```{r}
sql <- "
  SELECT *
  FROM iris
"
as_tibble(dbGetQuery(con, sql))
```

使用`dbGetQuery`函数能够直接对数据库传SQL语句并进行执行。 现在，我们就可以自由地使用dplyr中的动词对数据进行各式操作。比如我们想要对diamond表进行一系列操作，方法如下：

```{r}
diamonds_db <- tbl(con, "diamonds")
diamonds_db

big_diamonds_db <- diamonds_db %>% 
  filter(price > 15000) %>% 
  select(carat:clarity, price)
big_diamonds_db
```

需要注意的是，我们在这些操作中都没有对数据进行采集，因此这些赋值对象都还是一个数据连接，而不是R中的数据框。如果需要转化为数据框，可以这样操作：

```{r}
diamonds_db %>% as_tibble()
big_diamonds_db %>% collect()
```

我们还需要知道的是，凡是能够用dplyr方法进行访问的操作，事实上都已经成功地把dplyr操作转化为了相对应的SQL语句，如果我们想看SQL语句转化的情况，可以使用`show_query`函数，实现方法如下：

```{r}
big_diamonds_db %>% 
  show_query()
```

基于这些操作，我们可以在磁盘上对数据库进行分析，然后把内存能够轻松容纳的结果导入到R环境中，进行进一步的分析和展示。在数据库使用完毕后，可以使用`dbDisconnect`函数关闭数据库连接：

```{r}
dbDisconnect(con)
```

## 基于Arrow的大数据处理方案

Apache Arrow是一个跨语言的开发平台，用于高性能数据分析，提供了一种内存中的数据格式，旨在高效地共享数据而无需额外的序列化和反序列化步骤。它的设计目标是加速大数据处理和分析，使在处理和传输大规模数据集时表现出色。Arrow支持多种编程语言，包括C++, Java, Python, R等，使得不同语言之间的数据交换变得非常高效。其列式内存格式使数据在内存中的表示非常紧凑和高效，不仅减少了内存使用，还提升了CPU缓存命中率，从而加速数据处理。此外，通过Arrow的内存格式，不同进程和系统之间可以实现零拷贝的数据共享，大幅减少数据传输的开销。Arrow还与许多大数据系统（如Apache Parquet、Apache Spark、DuckDB等）无缝集成，支持高效的数据存储和处理。除了基本的数据类型，Arrow还支持复杂的数据结构和操作，如嵌套数据、时间戳和向量化操作。因此，Apache Arrow通过提供高效的内存格式和跨语言支持，为大数据处理和分析提供了一个强大而灵活的基础设施，极大地提升了数据密集型应用的性能。

本部分聚焦的是如何利用Arrow来进行内存外的计算，在R包**arrow**中，`open_dataset`函数能够在不把数据载入到R环境的情况下对数据（可以是一份文件包含的数据，也可以是分散在多个文件中的数据；数据格式可以是CSV，也可以是parquet）进行查询操作，用户可以使用**dplyr**包提供的函数来对数据自由进行操作。在条件允许的情况下，我们推荐使用parquet来存储数据，然后再利用**arrow**包对其进行访问，因为Parquet格式有以下优点：

-   作为一种专门为大数据需求设计的自定义二进制格式，Parquet文件通常比等效的CSV文件更小。Parquet依赖于高效的编码来减少文件大小，并支持文件压缩。这有助于加快parquet文件的速度，因为从磁盘到内存的数据量更少。
-   Parquet文件是列式存储的，这意味着它们是按列组织的，非常类似于R的数据框。这通常比按行组织的CSV文件在数据分析任务中表现更好。
-   Parquet文件是分块的，因此支持并行操作。而且，如果分组恰当的话，可以为数据操作节省很多时间。

我们可以尝试把一份大数据集保存为分块的parquet文件，这可以利用**arrow**包的`write_dataset`函数进行实现。

```{r}
#| eval: false
# 构造数据框
nr_of_rows <- 1e7 # 构造1千万行数据
df <- data.frame(
  Logical = sample(c(TRUE, FALSE, NA), prob = c(0.85, 0.1, 0.05), nr_of_rows, replace = TRUE),
  Integer = sample(1L:100L, nr_of_rows, replace = TRUE),
  Real = sample(sample(1:10000, 20) / 100, nr_of_rows, replace = TRUE),
  Factor = as.factor(sample(labels(UScitiesD), nr_of_rows, replace = TRUE))
)

# 根据Factor进行分组，然后把数据写出到data文件夹中的test_parquet子文件夹
df %>% 
  group_by(Factor) %>% 
  write_dataset("data/test_parquet",format = "parquet")
```

我们可以观察一下文件夹中的文件信息：

```{r}
p_load(arrow)
pq_path = "data/test_parquet"

tibble(
  files = list.files(pq_path, recursive = TRUE),
  size_MB = file.size(file.path(pq_path, files)) / 1024^2
)
```

可以看到每一个文件大概2 MB左右，文件名是采用键值对方法进行命名的。现在，我们可以把数据从我们的环境中清除掉，然后使用另一种方式对其进行访问：

```{r}
# 清除构建的数据集
rm(df)

# 对保存的parquet数据集进行连接
df_pq = open_dataset(pq_path)

# 观察数据信息
df_pq
```

通过观察，我们知道`open_dataset`函数没有返回数据本身，但是能够探知数据每一列是什么类型的。下面让我们使用dplyr的函数来对其进行查询：

```{r}
# 构建查询
query = df_pq %>% 
  filter(Factor == "Atlanta",Real > 50) %>% 
  group_by(Logical) %>% 
  summarise(avg = mean(Integer)) %>% 
  arrange(-avg)

# 观察查询
query
```

这一步不会直接执行，只会先记录我们需要执行什么内容。如果我们需要把内容收集起来，可以使用`collect`函数：

```{r}
query %>% collect()
```

这种数据处理的速度相当的快，如果我们使用`open_dataset`对CSV文件进行操作，也是能够实现的，但是速度会慢很多，读者不妨进行尝试。 最后需要提及的是，Arrow对DuckDB具有很好的支持，因为数据都是列式存储的，因此不需要进行内存赋值就可以直接进行类型转换，方法如下：

```{r}
#| warning: false
df_pq %>% 
  to_duckdb() %>% 
  filter(Factor == "Atlanta",Real > 50) %>% 
  group_by(Logical) %>% 
  summarise(avg = mean(Integer)) %>% 
  arrange(-avg) %>% 
  collect()
```

在这种背景下，使用parquet对数据进行存储是非常诱人的，因为这样能够让我们轻松地使用dplyr函数来对存储在磁盘的数据进行操作。

## 基于Polars的大数据处理方案

Polars 是一个高性能的数据框架库，专为数据操作和分析设计。它由 Rust 编写，确保了速度和内存安全，并利用并行处理来最大化性能。Polars 在处理大型数据集时表现出色，设计目的是最小化内存使用，使用高效的数据结构以减少开销。它提供了丰富的数据操作功能，如过滤、排序、聚合和连接等，支持链式操作，使得代码简洁且易读。总的来说，Polars 的特点包括：

-   高性能：Polars利用并行计算和SIMD（单指令多数据）技术，在执行数据操作时大大提高了处理速度。它在处理大数据集时的性能优于许多传统的数据分析库。
-   内存效率：Polars可以使用 Apache Arrow的内存格式（如Parquet），这使得它能够更有效地利用内存。其数据结构经过优化，可以处理更大的数据集，而不会消耗过多的内存资源。
-   灵活的表达能力：Polars提供了一系列丰富的操作功能，包括数据选择、过滤、聚合、排序和连接等，支持链式调用，使得数据处理过程更为流畅。
-   惰性计算：Polars 采用惰性执行策略，意味着只有在必要时才会执行计算。这种设计可以减少不必要的计算和内存开销，提升整体性能。
-   跨平台支持：除了支持多种编程语言外，Polars还可以在不同的操作系统上运行，适用于各种开发环境。
-   用户友好：Polars 的 API设计直观，易于学习和使用，适合各种数据分析任务。

尽管核心是用 Rust 编写的，Polars 提供了 R 接口，因此可以在 R 中方便地使用。Polars 能处理复杂的数据查询和操作，包括时间序列数据、缺失值和类别数据等，未来可能还会支持更多编程语言。在R中要安装核心的Polars包，可以这样操作：

```{r}
#| eval: false
install.packages("polars", repos = "https://community.r-multiverse.org")
install.packages(
  'tidypolars', 
  repos = c('https://etiennebacher.r-universe.dev', getOption("repos"))
)
```

以上代码会安装**polars**和**tidypolars**两个R包，前者负责在R中调用Rust所构建的Polars工具，后者则可以把常用的tidyverse代码（特别是**dplyr**和**tidyr**包中的函数）直接转译为Polars所支持的代码。下面我们对该工具进行简单的演示，首先我们生成一份数据集，并保存在根目录下的temp文件夹中：

```{r}
#| eval: false
library(pacman)
p_load(tidyfst,arrow)

# 生成一亿行
nr_of_rows <- 1e8

# 构造数据框
df <- data.frame(
  Logical = sample(c(TRUE, FALSE, NA), prob = c(0.85, 0.1, 0.05), nr_of_rows, replace = TRUE),
  Integer = sample(1L:100L, nr_of_rows, replace = TRUE),
  Real = sample(sample(1:10000, 20) / 100, nr_of_rows, replace = TRUE),
  Factor = as.factor(sample(labels(UScitiesD), nr_of_rows, replace = TRUE))
)

# 检查大小
object_size(df) # 1.9 Gb

# 导出parquet文件
arrow::write_parquet(df,"temp/df.parquet") # 209.1 Mb

# 清除环境内的所有变量
rm(list = ls())
```

然后，我们利用**polars**包的`scan_parquet`方法把数据扫描到R环境中：

```{r}
#| eval: false
library(pacman)
p_load(polars,tidypolars,tidyverse,tidyfst)

# 扫描数据
scan_parquet_polars("temp/df.parquet") -> dat_pl
```

需要注意的是，在上面的操作中，我们并没有把数据导入到环境里面。我们用了“扫描”一词，其实相当于对数据进行了连接，类似于我们在前一章节中提到的`open_dataset`操作。在这个背景下，我们可以对这个没有导入环境的数据进行各种操作，并把结果收集到环境中进行展示，操作方法如下：

```{r}
#| eval: false
# 观察前6行
dat_pl %>% 
  head() %>% 
  compute()

# 看看总共有多少行
dat_pl %>% count() %>% compute()

# 分组汇总计算
pst(
  dat_pl %>% 
    group_by(Logical,Factor) %>% 
    summarise(Real_mean = mean(Real),Real_sd = sd(Real),
              median_Integer = median(Integer)) %>% 
    compute() -> res 
) # Finished in 3.920s elapsed (15.4s cpu)

# 查看结果
res

# 把结果转化为数据框并使用tibble形式进行展示
res %>% as_tibble()
```

通过上面的试验，我们可以发现只需要把数据先存为Parquet格式，然后使用`scan_parquet`方法进行数据连接，就可以利用我们熟悉的**dplyr**和**tidyr**函数对保存在磁盘中的数据进行各式的数据操作，这给我们的大数据分析提供了巨大的便利，是解决内存不足计算（Out-of-Memory Computation）的最佳方案之一。

## 小结

本章介绍了如何在数据分析中有效地使用数据库管理数据，并讨论如何使用Arrow来对存在磁盘的数据进行高效处理。通过学习使用**DBI**包连接数据库、执行SQL查询，以及借助**dbplyr**包将**dplyr**代码翻译成SQL，我们能够在R中直接操作和查询数据库中的数据。这种方法不仅提高了数据处理的效率，还减少了对中间文件（如CSV）的依赖，避免了繁琐的数据导入导出步骤。此外，我们还学习了Apache Arrow和Polars， 它们所提供的Parquet内存格式减少了数据在不同系统间转换的开销，这对于需要处理大量数据的应用程序来说尤为重要。

## 练习

-   尝试使用duckdb方法构建一个数据库，然后实现所有数据库的日常操作，比如对某一列创建索引
-   请比较一下是数据库操作快，还是使用Arrow/Polars对数据进行操作快，注意使用同样数据进行比较，同时对数据操作的时间和数据占据的内存进行比较。
-   请比较一下，在内存允许的情况下，究竟是在内存中对数据进行处理快，还是使用磁盘进行数据处理速度快。