read.csv.sql返回的DataFrame存储位置及数据库持久化方法

我在共享实验室服务器上用R的sqldf包处理数十GB、超1亿行的CSV数据，服务器RAM由多用户共用，管理员要求将数据导入数据库（如PostgreSQL、SQLite）而非全加载至内存，以降低内存占用。

使用read.csv.sql时发现，它会将CSV导入临时SQLite数据库，执行SQL语句后返回DataFrame，临时数据库文件会自动删除。测试1.5亿行数据时，生成的30GB临时库文件在操作结束后被删除，但返回的df_data仍可正常操作，说明该DataFrame存储在RAM中，不符合我“数据全程存于数据库，仅用RAM做计算”的需求。想请教如何让数据一直保留在数据库中直到程序结束？

测试代码：

df_data <- suppressWarnings(read.csv.sql(
    file = "X:/logs/data.csv",
    sql = "
       select
           nullif(timestamp, '') as timestamp_value,
           nullif(user_account, '') as user_account,
           nullif(country_code, '') as country_code,
           nullif(prefix_value, '') as prefix_value,
           nullif(user_query, '') as user_query,
           nullif(returned_code, '') as returned_code,
           nullif(execution_time, '') as execution_time,
           nullif(output_format, '') as output_format
       from
           file
      ",
    header = FALSE,
    sep = "|",
    eol = "\n",
    `field.types` = list(
        timestamp_value = c("TEXT"),
        user_account = c("TEXT"),
        country_code = c("TEXT"),
        prefix_value = c("TEXT"),
        user_query = c("TEXT"),
        returned_code = c("TEXT"),
        execution_time = c("REAL"),
        output_format = c("TEXT")
    ),
    dbname = "X:/logs/sqlite_tmp.db",
    drv = "SQLite"
))

方法1：用sqldf连接持久化SQLite数据库 #

核心是手动创建持久化数据库连接，避免sqldf自动删除数据库，后续分析直接在数据库中执行SQL，仅将统计结果加载到内存。

示例代码：

library(sqldf)

# 1. 手动创建持久化SQLite连接，指定保留的数据库文件
con <- dbConnect(SQLite(), dbname = "X:/logs/persistent_data.db")

# 2. 将CSV导入数据库的指定表（表名设为log_data）
read.csv.sql(
    file = "X:/logs/data.csv",
    sql = "CREATE TABLE log_data AS
           select
               nullif(timestamp, '') as timestamp_value,
               nullif(user_account, '') as user_account,
               nullif(country_code, '') as country_code,
               nullif(prefix_value, '') as prefix_value,
               nullif(user_query, '') as user_query,
               nullif(returned_code, '') as returned_code,
               nullif(execution_time, '') as execution_time,
               nullif(output_format, '') as output_format
           from file",
    header = FALSE,
    sep = "|",
    eol = "\n",
    field.types = list(
        timestamp_value = "TEXT",
        user_account = "TEXT",
        country_code = "TEXT",
        prefix_value = "TEXT",
        user_query = "TEXT",
        returned_code = "TEXT",
        execution_time = "REAL",
        output_format = "TEXT"
    ),
    drv = con  # 使用已创建的持久化连接
)

# 3. 后续分析仅执行SQL并返回结果到内存，不加载全量数据
# 示例：统计不同国家的请求数
country_stats <- sqldf("SELECT country_code, COUNT(*) as request_count FROM log_data GROUP BY country_code", drv = con)

# 4. 程序结束后手动关闭连接（R退出时也会自动断开）
dbDisconnect(con)

方法2：用DBI+RSQLite直接操作数据库 #

如果sqldf的封装不够灵活，可直接用底层包完全控制数据流向：

library(DBI)
library(RSQLite)

# 创建持久化数据库连接
con <- dbConnect(RSQLite::SQLite(), "X:/logs/persistent_data.db")

# 导入CSV到数据库表（支持大文件分批导入）
dbWriteTable(
    conn = con,
    name = "log_data",
    value = "X:/logs/data.csv",
    header = FALSE,
    sep = "|",
    eol = "\n",
    field.types = c(
        timestamp_value = "TEXT",
        user_account = "TEXT",
        country_code = "TEXT",
        prefix_value = "TEXT",
        user_query = "TEXT",
        returned_code = "TEXT",
        execution_time = "REAL",
        output_format = "TEXT"
    ),
    row.names = FALSE,
    overwrite = TRUE  # 表已存在时覆盖
)

# 执行统计查询，仅返回结果到内存
execution_time_stats <- dbGetQuery(con, "SELECT returned_code, AVG(execution_time) as avg_time FROM log_data GROUP BY returned_code")

# 关闭连接
dbDisconnect(con)

关键注意事项 #

• 必须使用持久化数据库文件，避免sqldf的临时库自动删除逻辑
• 所有数据处理逻辑通过SQL在数据库中执行，仅将最终统计结果加载到RAM
• 若改用PostgreSQL，只需替换驱动为RPostgres包，操作逻辑完全一致

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