Databricks SQL AnalysisException错误：documents字段类型转换失败求助

问题背景 #

视图DDL #

CREATE VIEW myschema.table (
  accountId,
  agreementType,
  capture_file_name,
  capture_file_path,
  createdDate,
  currency,
  de_agentid,
  de_applicationshardid,
  de_datacontenttype,
  de_eventapplicationtime,
  de_eventmode,
  de_eventpublishtime,
  de_eventsequenceid,
  de_id,
  de_partitionkey,
  de_source,
  documents,
  effectiveDate,
  eh_EnqueuedTimeUtc,
  eh_Offset,
  eh_SequenceNumber,
  eh_SystemProperties_x_opt_enqueued_time,
  eh_SystemProperties_x_opt_kafka_key,
  endDate,
  expirationDate,
  externalId,
  externalSource,
  id,
  isInWorkflow,
  isSigned,
  name,
  notice,
  noticeDate,
  noticePeriod,
  parties,
  reconciled_file_name_w_path,
  requestor,
  resourceVersion,
  status,
  terminateForConvenience,
  updatedDate,
  value,
  de_action,
  de_eventapplication_year,
  de_eventapplication_month,
  de_eventapplication_day,
  de_eventapplication_hour,
  de_eventapplication_minute)
TBLPROPERTIES (
  'transient_lastDdlTime' = '1664473495')
AS select * from parquet.`/mnt/store/de_entitytype=Agreement`

查询语句 #

select de_id from myschema.table;

错误信息 #

Error in SQL statement: AnalysisException: Cannot up cast documents from array<struct<accountId:string,agreementId:string,createdBy:string,createdDate:string,id:string,obligations:array,resourceVersion:bigint,updatedBy:string,updatedDate:string>> to array.
The type path of the target object is:
You can either add an explicit cast to the input data or choose a higher precision type of the field in the target object

示例数据（documents字段为复杂数组结构） #

{
  "eh_SequenceNumber": "153582",
  "eh_Offset": "25770201768",
  "eh_EnqueuedTimeUtc": "10/7/2022 1:05:07 PM",
  "eh_SystemProperties_x_opt_kafka_key": "08daa864-8ed7-4401-84e9-22ba9157c0ad",
  "eh_SystemProperties_x_opt_enqueued_time": "1665147907333",
  "de_id": "7432aa9e-4a65-446e-9bd0-b253dfa0c150",
  "de_eventpublishtime": "10/07/2022 01:05:07.333095Z",
  "de_datacontenttype": "JSON",
  "de_partitionkey": "08daa864-8ed7-4401-84e9-22ba9157c0ad",
  "de_eventsequenceid": "19856",
  "de_eventapplicationtime": "10/07/2022 01:05:07.290264Z",
  "de_eventmode": "Original",
  "de_applicationshardid": "00000000-0000-0000-0000-000000000000",
  "de_agentid": "00000000-0000-0000-0000-000000000000",
  "de_entitytype": "Agreement",
  "de_action": "Post",
  "de_source": "adm/Post/Agreement",
  "reconciled_file_name_w_path": "dbfs:/mnt/test.avro",
  "capture_file_name": "test.avro",
  "capture_file_path": "dbfs:/mnt/test/",
  "id": "08daa864-8ed7-4401-84e9-22ba9157c0ad",
  "accountId": "b111d426-1206-4ac7-a381-4651d277b78c",
  "createdDate": "2022-10-07T13:05:07.290264Z",
  "updatedDate": "2022-10-07T13:05:07.290264Z",
  "documents": [
    {
      "id": "7c3aea45-3d46-ed11-9c69-3863bb335c17",
      "accountId": "b111d426-1206-4ac7-a381-4651d277b78c",
      "obligations": [],
      "createdDate": "2022-10-07T13:05:00.946504Z",
      "updatedDate": "2022-10-07T13:05:07.290264Z",
      "name": "test.doc",
      "externalId": "7c3aea45-3d46-ed11-9c69-3863bb335c17",
      "externalSource": "test",
      "agreementId": "08daa864-8ed7-4401-84e9-22ba9157c0ad",
      "resourceVersion": 1
    }
  ],
  "name": "test.doc",
  "externalId": "7c3aea45-3d46-ed11-9c69-3863bb335c17",
  "externalSource": "test",
  "parties": [],
  "status": 0,
  "resourceVersion": 1
}

错误原因 #

视图定义中手动指定了字段列表，但documents字段未声明具体类型，Spark默认将其推断为array<string>；而底层Parquet文件中的documents实际是array<struct<...>>的复杂嵌套数组类型。当查询视图时，Spark会校验视图定义的字段类型与底层数据的实际类型是否一致，类型不匹配导致转换失败——哪怕只查询de_id，也会触发全表的类型校验逻辑。

解决方法 #

方案1：修改视图定义，匹配实际数据类型 #

两种实现方式：

• 不手动指定字段列表，让Spark自动从Parquet文件推断所有字段的正确类型：

CREATE OR REPLACE VIEW myschema.table
TBLPROPERTIES ('transient_lastDdlTime' = '1664473495')
AS select * from parquet.`/mnt/store/de_entitytype=Agreement`;

• 手动指定documents字段的正确类型，其他字段保持原定义：

CREATE OR REPLACE VIEW myschema.table (
  accountId,
  agreementType,
  capture_file_name,
  capture_file_path,
  createdDate,
  currency,
  de_agentid,
  de_applicationshardid,
  de_datacontenttype,
  de_eventapplicationtime,
  de_eventmode,
  de_eventpublishtime,
  de_eventsequenceid,
  de_id,
  de_partitionkey,
  de_source,
  documents array<struct<accountId:string,agreementId:string,createdBy:string,createdDate:string,id:string,obligations:array<string>,resourceVersion:bigint,updatedBy:string,updatedDate:string>>,
  effectiveDate,
  eh_EnqueuedTimeUtc,
  eh_Offset,
  eh_SequenceNumber,
  eh_SystemProperties_x_opt_enqueued_time,
  eh_SystemProperties_x_opt_kafka_key,
  endDate,
  expirationDate,
  externalId,
  externalSource,
  id,
  isInWorkflow,
  isSigned,
  name,
  notice,
  noticeDate,
  noticePeriod,
  parties,
  reconciled_file_name_w_path,
  requestor,
  resourceVersion,
  status,
  terminateForConvenience,
  updatedDate,
  value,
  de_action,
  de_eventapplication_year,
  de_eventapplication_month,
  de_eventapplication_day,
  de_eventapplication_hour,
  de_eventapplication_minute)
TBLPROPERTIES ('transient_lastDdlTime' = '1664473495')
AS select * from parquet.`/mnt/store/de_entitytype=Agreement`;

方案2：直接查询底层Parquet文件 #

如果只是临时获取de_id数据，可以绕过视图，直接查询Parquet路径：

select de_id from parquet.`/mnt/store/de_entitytype=Agreement`;

方案3：显式转换documents类型（业务允许时） #

如果业务逻辑要求documents必须是array<string>类型，可以将每个结构体序列化为JSON字符串：

CREATE OR REPLACE VIEW myschema.table
TBLPROPERTIES ('transient_lastDdlTime' = '1664473495')
AS 
select 
  accountId,
  agreementType,
  capture_file_name,
  capture_file_path,
  createdDate,
  currency,
  de_agentid,
  de_applicationshardid,
  de_datacontenttype,
  de_eventapplicationtime,
  de_eventmode,
  de_eventpublishtime,
  de_eventsequenceid,
  de_id,
  de_partitionkey,
  de_source,
  transform(documents, d -> to_json(d)) as documents,
  effectiveDate,
  eh_EnqueuedTimeUtc,
  eh_Offset,
  eh_SequenceNumber,
  eh_SystemProperties_x_opt_enqueued_time,
  eh_SystemProperties_x_opt_kafka_key,
  endDate,
  expirationDate,
  externalId,
  externalSource,
  id,
  isInWorkflow,
  isSigned,
  name,
  notice,
  noticeDate,
  noticePeriod,
  parties,
  reconciled_file_name_w_path,
  requestor,
  resourceVersion,
  status,
  terminateForConvenience,
  updatedDate,
  value,
  de_action,
  de_eventapplication_year,
  de_eventapplication_month,
  de_eventapplication_day,
  de_eventapplication_hour,
  de_eventapplication_minute
from parquet.`/mnt/store/de_entitytype=Agreement`;

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