在R中将平面文件保存为SQL数据库而无需完全加载到内存中

Question

希望我接下来要写的内容有一些意义。 如果你看一下如何在R语言中处理一个50GB大小的CSV文件，就可以了解如何像SQL一样查询csv文件。 在我的情况下，我存储了大量数据，这些数据以大型（或大于我的RAM）平面文件的形式存在。

例如，我想将其中一个文件存储为SQLite数据库，而不必完全加载它到内存中。想象一下，如果您可以自动读取适合RAM的有限块的文件，并将其存储到SQL中，然后释放一些内存，处理下一个块，直到整个文件都在数据库中。在R中是否可行？如果该表可以存储为tibble，则更好，但这并非至关重要。感谢任何建议。

- larry77

---

这是可行的，无论是使用SQLite、SQL Server、Postgres还是任何其他DBMS，但我认为你不需要sqldf：虽然它确实提供了以SQL方式访问大量数据的功能，但它假定数据已经驻留在内存中。你所说的只是一次加载部分数据到内存中，这表明你需要DBI和RSQLite包。你应该弄清楚如何将50GB的数据导入sqlite文件，无论是通过R还是直接导入。 - r2evans

我的意思是，你似乎已经描述了一种合理的策略，即每次读取文件块并将其导入数据库。你试过了吗？它不起作用吗？由于你没有提供任何关于你使用的文件/数据库的具体细节，所以很难提供更具体的建议。 - MrFlick

这可能很有用：https://dev59.com/9WUp5IYBdhLWcg3w661B#24582022（直接导入CSV文件而不加载到R中）。 - r2evans

Answer 1

1) dbWriteTable函数

dbWriteTable函数可以将文件读入数据库而无需通过R语言。如果数据库不存在，则会创建它。

library(RSQLite)

cat("a,b\n1,2\n", file = "myfile.csv")  # create test file

con <- dbConnect(SQLite(), "mydb") 

dbWriteTable(con, "mytable", "myfile.csv")
dbGetQuery(con, "select count(*) from mytable")  # ensure it is there

dbDisconnect(con)

2）SQLite CLI 我们可以使用 SQLite CLI 进行操作，该工具可从 SQLite 下载网站下载。

https://www.sqlite.org/download.html

这种方式不需要使用 R 创建数据库。如果 mydb 数据库不存在，则将创建它。第一行在 shell 或 cmd 提示符处输入，然后该工具会提供自己的提示符，以便输入其余内容。

sqlite3 mydb
.mode csv
.import myfile.csv mytable
.quit

3) 其他数据库 另一种选择是使用具有直接读取csv文件功能的数据库。H2具有csvread，MySQL具有load data infile，而PostgreSQL具有copy。

- G. Grothendieck

---

我在RSQLite的新闻文件中看到了一篇文章，表明这是从2016年开始的情况。我从未使用过它，也没有尝试过，但这是一个非常有趣的功能。 - r2evans

我必须检查这个。重要的是在创建数据库之前不管R如何，永远不要将整个文件存储到内存中。 - larry77

已添加其他选项。 - G. Grothendieck

但重点不仅是避免使用R。重点是在处理大于RAM原始数据集时不要耗尽RAM。你的解决方案能解决这个问题吗？ - larry77

这些解决方案中，内存不应该是一个限制，但你必须尝试它们才能确信。在这个规模上，SQLite 可能会存在性能问题，也可能不会。 - G. Grothendieck

Answer 2

显然已经有一个函数可以做到这一点

https://raw.githubusercontent.com/inbo/inborutils/master/R/csv_to_sqlite.R

我正在进行测试。即使选择了对应的选项，我也看不到任何进度条，但它似乎完成了工作。

- larry77

---

忽略我之前的评论，我错过了那个函数中的“_chunked”部分。我仍然认为使用sqlite本地操作会更快，但如果这对你来说不太费时间，那很好。 - r2evans