使用R的dbWriteTable写入Cloud Spanner时需指定列名的问题

问题场景 #

使用R的DBI包dbWriteTable向Cloud Spanner表插入数据，原本认为数据框列数与表列数一致即可，却触发报错要求必须指定列名。

原代码 #

write.to.spanner <- function(table, df){
  dbWriteTable(con, table, df, overwrite=FALSE, append=TRUE, row.names=FALSE)
}

req_df <- data.frame(req_id=123, req_name="test req")

write.to.spanner("dbi_test", req_df)

Cloud Spanner表DDL #

CREATE TABLE dbi_test (
  req_id INT64,
  req_name STRING(20),
) PRIMARY KEY(req_id);

报错信息 #

Warning: Error in .local: execute JDBC update query failed in dbSendUpdate ([Simba]SpannerJDBCDriver There was an error while executing the DML query : INVALID_ARGUMENT: com.simba.cloudspanner.shaded.com.google.api.gax.rpc.InvalidArgumentException: com.simba.cloudspanner.shaded.io.grpc.StatusRuntimeException: INVALID_ARGUMENT: INSERT must specify a column list [at 1:1]
INSERT INTO dbi_test VALUES(@var1,@var2)

报错原因 #

Cloud Spanner的SQL语法要求INSERT语句必须显式指定列列表，即便数据列数与表列数完全匹配也不能省略。而默认情况下dbWriteTable生成的INSERT语句没有包含列名，因此触发了语法校验错误。

解决方案 #

在dbWriteTable调用中添加fields参数，显式指定要插入的列名（直接使用数据框的列名即可，需确保与Spanner表列名完全一致）。

修改后的代码：

write.to.spanner <- function(table, df){
  dbWriteTable(con, table, df, overwrite=FALSE, append=TRUE, row.names=FALSE, fields = colnames(df))
}

req_df <- data.frame(req_id=123, req_name="test req")

write.to.spanner("dbi_test", req_df)

添加fields = colnames(df)后，dbWriteTable会生成带列名的INSERT语句：INSERT INTO dbi_test (req_id, req_name) VALUES(@var1,@var2)，符合Spanner的语法要求。

可行替代方案（手动编写INSERT语句） #

如果暂时无法使用dbWriteTable，也可以通过dbSendUpdate手动构造带列名的插入语句，如下代码可正常运行：

write.to.spanner <- function(table, df){
  insert_qry <- paste0("INSERT INTO ",table," (req_id, req_name) VALUES (",df[1,1],",'",df[1,2],"');")
  dbSendUpdate(con,insert_qry)
}

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