read.csv.sql用WHERE子句后SQLite数据库异常过大问题咨询

问题场景 #

用R的sqldf包处理超1亿行的CSV文件，仅需筛选导入1万行目标数据，代码如下：

library(sqldf)
suppressWarnings(read.csv.sql(
    file = input_file_path,
    sql = "
       create table mytable as 
       select
           -- 需保留的字段列表
       from
           file
       where
           -- 筛选条件
      ",
    header = FALSE,
    sep = "|",
    eol = "\n",
    `field.types` = list( -- 字段类型定义 ),
    dbname = sqlite_db_path,
    drv = "SQLite"
))

执行后mytable确实只有1万行，但生成的SQLite数据库文件却超过25GB，远大于预期。

问题根源 #

这不是read.csv.sql的bug，是它的工作机制导致的：

• read.csv.sql会先将整个CSV文件完整导入到SQLite数据库中一个名为file的表（对应SQL语句里的FROM file）；
• 随后执行你的CREATE TABLE...SELECT语句，从这个大表中筛选出符合条件的行存入mytable；
• 但这个临时的file表并不会自动被删除，依然占用着磁盘空间，导致数据库文件大小接近原CSV的实际大小。

解决办法 #

方法1：在SQL语句中自动清理临时表并回收空间 #

在SQL语句末尾添加删除临时表和空间回收的命令，一次性完成筛选、清理和压缩：

library(sqldf)
suppressWarnings(read.csv.sql(
    file = input_file_path,
    sql = "
       create table mytable as 
       select
           -- 需保留的字段列表
       from
           file
       where
           -- 筛选条件;
       drop table file;
       vacuum;
      ",
    header = FALSE,
    sep = "|",
    eol = "\n",
    `field.types` = list( -- 字段类型定义 ),
    dbname = sqlite_db_path,
    drv = "SQLite"
))

方法2：用内存临时数据库存储中间表 #

让sqldf将临时的file表存入内存，避免占用磁盘空间，再将筛选结果写入目标磁盘数据库：

library(sqldf)
library(RSQLite)

# 先连接到目标磁盘数据库
target_conn <- dbConnect(SQLite(), dbname = sqlite_db_path)

# 用内存临时数据库存储CSV导入的中间表，筛选后插入目标库
suppressWarnings(read.csv.sql(
    file = input_file_path,
    sql = "
       insert into main.mytable
       select
           -- 需保留的字段列表
       from
           file
       where
           -- 筛选条件
      ",
    header = FALSE,
    sep = "|",
    eol = "\n",
    `field.types` = list( -- 字段类型定义 ),
    dbname = ":memory:", # 中间表存在内存，不占磁盘
    drv = "SQLite",
    connection = target_conn # 指定插入到目标数据库的表
))

# 关闭连接
dbDisconnect(target_conn)

方法3：手动清理已生成的大数据库 #

如果已经生成了超大数据库，可以手动连接后清理临时表并回收空间：

library(RSQLite)

conn <- dbConnect(SQLite(), dbname = sqlite_db_path)
# 删除临时的file表
dbExecute(conn, "DROP TABLE IF EXISTS file;")
# 回收磁盘空间
dbExecute(conn, "VACUUM;")
dbDisconnect(conn)

内容的提问来源于stack exchange，提问作者user17911