通过Simba Spark ODBC向含Identity列的Databricks表插入数据报错

问题背景 #

通过Excel的Simba Spark ODBC连接向Databricks Delta表插入数据时，触发报错：Providing values for GENERATED ALWAYS AS IDENTITY column ID is not supported。但将VBA生成的SQL直接复制到Databricks Notebook执行可成功插入，且INSERT语句已明确排除IDENTITY列ID。

建表语句 #

CREATE TABLE test_table (
  ID BIGINT GENERATED ALWAYS AS IDENTITY
  ,DATE DATE
  ,TIME TIMESTAMP
  ,WHAT_FILE_WAS_UPDATED STRING
  ,WHAT_MODULE_WAS_IMPORTED STRING
  ,MODULE_ERROR STRING
  ,MODULE_RUN_TIME DECIMAL(15,13)
)

Databricks中可正常执行的插入语句 #

INSERT INTO
  test_table (
    DATE,
    `TIME`,
    WHAT_FILE_WAS_UPDATED,
    WHAT_MODULE_WAS_IMPORTED,
    MODULE_ERROR,
    MODULE_RUN_TIME
  )
VALUES(
    '2024-04-01',
    '2024-04-01 11:15:59',
    'test string',
    'test string',
    NULL,
    '0.0000020345052'
  )

原VBA生成SQL的代码 #

strSQL = "INSERT INTO" & vbLf
strSQL = strSQL & "  test_table(" & vbLf
strSQL = strSQL & "    DATE," & vbLf
strSQL = strSQL & "    TIME," & vbLf
strSQL = strSQL & "    WHAT_FILE_WAS_UPDATED," & vbLf
strSQL = strSQL & "    WHAT_MODULE_WAS_IMPORTED," & vbLf
strSQL = strSQL & "    MODULE_ERROR," & vbLf
strSQL = strSQL & "    MODULE_RUN_TIME" & vbLf
strSQL = strSQL & "  )" & vbLf
strSQL = strSQL & "VALUES(" & vbLf
strSQL = strSQL & "    '" & Format(Date, "yyyy-mm-dd") & "'," & vbLf
strSQL = strSQL & "    '" & Format(Now, "yyyy-mm-dd hh:mm:ss") & "'," & vbLf
strSQL = strSQL & "    '" & Replace(clrWB.FullName, "\", "\") & "'," & vbLf
strSQL = strSQL & "    '" & Replace(BAS_path, "\", "\") & "'," & vbLf
strSQL = strSQL & "    NULL," & vbLf
strSQL = strSQL & "    '" & MinutesElapsed & "'" & vbLf
strSQL = strSQL & "  )"

报错信息 #

[Simba][Hardy] (80) Syntax or semantic analysis error thrown in server while executing query. Error message from server: org.apache.hive.service.cli.HiveSQLException: Error running query: org.apache.spark.sql.AnalysisException: Providing values for GENERATED ALWAYS AS IDENTITY column ID is not supported.
    at org.apache.spark.sql.hive.thriftserver.HiveThriftServerErrors$.runningQueryError(HiveThriftServerErrors.scala:48)
    at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation.$anonfun$exec

问题根源 #

Simba Spark ODBC驱动的默认行为与Databricks原生SQL解析逻辑存在差异：驱动会尝试向表的所有列（包括IDENTITY列）传递值，即便INSERT语句中已明确指定列列表并排除IDENTITY列。此外，原VBA代码存在两处潜在问题：

• 关键字TIME未用反引号包裹，可能导致驱动解析歧义
• 路径反斜杠未正确转义（Replace(clrWB.FullName, "\", "\")为无效操作）

解决方案 #

方案1：配置ODBC驱动忽略IDENTITY列 #

在ODBC数据源管理器中找到对应Databricks的数据源配置：

• 进入Spark SQL选项卡
• 启用Skip Identity Columns（部分版本驱动名为Ignore Identity Columns）
• 保存配置后重新连接

方案2：修正VBA生成的SQL #

调整VBA代码，解决关键字和转义问题，同时指定表的完全限定名（库+Schema+表名），避免驱动解析错误：

strSQL = "INSERT INTO" & vbLf
strSQL = strSQL & "  your_database.your_schema.test_table(" & vbLf ' 替换为实际数据库和Schema名
strSQL = strSQL & "    DATE," & vbLf
strSQL = strSQL & "    `TIME`," & vbLf ' 关键字用反引号包裹
strSQL = strSQL & "    WHAT_FILE_WAS_UPDATED," & vbLf
strSQL = strSQL & "    WHAT_MODULE_WAS_IMPORTED," & vbLf
strSQL = strSQL & "    MODULE_ERROR," & vbLf
strSQL = strSQL & "    MODULE_RUN_TIME" & vbLf
strSQL = strSQL & "  )" & vbLf
strSQL = strSQL & "VALUES(" & vbLf
strSQL = strSQL & "    '" & Format(Date, "yyyy-mm-dd") & "'," & vbLf
strSQL = strSQL & "    '" & Format(Now, "yyyy-mm-dd hh:mm:ss") & "'," & vbLf
strSQL = strSQL & "    '" & Replace(clrWB.FullName, "\", "\\") & "'," & vbLf ' 正确转义反斜杠
strSQL = strSQL & "    '" & Replace(BAS_path, "\", "\\") & "'," & vbLf
strSQL = strSQL & "    NULL," & vbLf
strSQL = strSQL & "    '" & MinutesElapsed & "'" & vbLf
strSQL = strSQL & "  )"

方案3：升级Simba ODBC驱动 #

安装最新版本的Simba Spark ODBC驱动，新版本已修复IDENTITY列的处理逻辑，与Databricks原生行为对齐。

验证方法 #

1. 应用任一方案后，运行VBA代码
2. 检查Databricks表是否成功插入数据
3. 确认ID列已自动生成正确的自增数值

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