R 和 PostgreSQL - 预先指定可能的列名和类型

Question

>我有多个以.csv格式存储的大型类似数据文件。这些是每年发布的数据文件。其中大多数具有相同的变量，但在某些年份，它们添加了变量或更改了变量的名称。

我正在循环浏览我的文件目录(~30.csv个文件(，将它们转换为数据帧，并通过以下方式将它们导入到 Google Cloud SQL PostgreSQL 12 数据库中：

DBI::dbAppendTable(con, tablename, df)

其中con是我与数据库的连接，tablename是表名，df是从.csv生成的数据框。

问题是这些.csv文件中的每一个都有不同数量的列，有些文件没有其他文件的列。

有没有一种简单的方法可以为 PostgreSQL 12 数据库预定义一个结构，该结构指定"这.csv列中的任何一个都将进入这一个数据库列"以及".csv中未包含的任何列都应在数据库中填充 NA"。我想我可以在 R 中想出一些东西，在上传到数据库之前让所有数据帧看起来都相似，但这似乎很麻烦。我正在成像一个像 JSON 这样的文档，SQL 数据库将其与以下内容进行比较：

SQL | Data frame
----------------------------------
age = "age","Age","AGE"
sex = "Sex","sex","Gender","gender"
...
fnstatus = "funcstatus","FNstatus"

这将向数据库指定它可能看到的所有可能的列以及如何解析这些列。对于它在给定.csv中看不到的列，它将用NA填充所有记录。

Answer 1

虽然我不能说这样的功能是否可用，因为 Postgres 有许多新颖的方法和扩展的数据类型，但我会犹豫是否使用这些功能，因为可维护性可能是一个挑战。

企业、服务器、关系数据库(如PostgreSQL(应该是规划的基础设施系统。正如 r2evans 注释的那样，表 [包括模式、列、用户等] 应该预先定义。设计人员需要在进行任何数据迁移或交互之前考虑整个用途和需求。通常不建议根据一次性应用程序需求动态调整数据库表和列。因此，像 R 这样的客户端应该动态对齐数据，以满足计划的关系数据库规范。

一种方法是使用临时表作为所有原始 CSV 数据的暂存，可能使用所有VARCHAR进行设置。然后，使用COALESCE和::在单个追加查询中最终迁移的所有原始数据填充此表，以便类型转换到最终目标。

# BUILD LIST OF DFs FROM ALL CSVs
df_list <- lapply(list_of_csvs, read.csv)
# NORMALIZE ALL COLUMN NAMES TO LOWER CASE
df_list <- lapply(df_list, function(df), setNames(df, tolower(names(df))))
# RETURN LIST OF UNIQUE NAMES
all_names <- unique(lapply(df_list, names))
# CREATE TABLE QUERY
dbSendQuery(con, "DROP TABLE IF EXISTS myTempTable")
sql <- paste("CREATE TABLE myTempTable (", 
paste(all_names, collapse = " VARCHAR(100), "),
"VARCHAR(100)",
")")
dbSendQuery(con, sql)
# APPEND DATAFRAMES TO TEMP TABLE
lapply(df_list, function(df) DBI::dbAppendTable(con, "myTempTable", df))
# RUN FINAL CLEANED APPEND QUERY
sql <- "INSERT INTO myFinalTable (age, sex, fnstatus, ...)
SELECT COALESCE(age)::int
, COALESCE(sex, gender)::varchar(5)
, COALESCE(funcstatus, fnstatus)::varchar(10)
...
FROM myTempTable"
dbSendQuery(con, sql)