使用Google Java Client向现有BQ表加载数据遇Schema不匹配错误

错误原因 #

你遇到的报错核心是提交的Schema与BigQuery已存在表的Schema不匹配：

• 目标表hd-pricing-dev:merch_meter.jira_issues_csv_load中issueid字段的模式是REQUIRED（必填）
• 但你代码里通过Field.of("issueid", StandardSQLTypeName.STRING)定义字段时，BigQuery Java客户端的这个方法默认会将字段模式设置为NULLABLE（可选），导致提交的Schema里issueid是NULLABLE，和表的REQUIRED模式冲突，触发报错。

解决办法 #

需要显式将issueid字段的模式设置为REQUIRED，修改对应的字段定义代码：

// 替换原来的issueid字段定义
Field.of("issueid", StandardSQLTypeName.STRING)
// 改为用Builder显式设置模式
Field.newBuilder("issueid", StandardSQLTypeName.STRING)
    .setMode(Field.Mode.REQUIRED)
    .build()

修改后的完整Schema代码示例：

Schema schema = Schema.of(
    Field.of("teamid", StandardSQLTypeName.STRING),
    Field.of("jiraboardid", StandardSQLTypeName.STRING),
    Field.newBuilder("issueid", StandardSQLTypeName.STRING).setMode(Field.Mode.REQUIRED).build(),
    Field.of("issuetype", StandardSQLTypeName.STRING),
    Field.of("sprintid", StandardSQLTypeName.STRING),
    Field.of("status", StandardSQLTypeName.STRING),
    Field.of("createddate", StandardSQLTypeName.TIMESTAMP),
    Field.of("completeddate", StandardSQLTypeName.TIMESTAMP),
    Field.of("sprintstartdate", StandardSQLTypeName.TIMESTAMP),
    Field.of("sprintenddate", StandardSQLTypeName.TIMESTAMP),
    Field.of("lastupdateduser", StandardSQLTypeName.STRING),
    Field.of("lastupdateddate", StandardSQLTypeName.TIMESTAMP),
    Field.of("issuepoints", StandardSQLTypeName.INT64),
    Field.of("creatoremailaddress", StandardSQLTypeName.STRING),
    Field.of("creatordisplayname", StandardSQLTypeName.STRING),
    Field.of("creatoraccountid", StandardSQLTypeName.STRING),
    Field.of("creatorisactive", StandardSQLTypeName.BOOL),
    Field.of("components", StandardSQLTypeName.STRING),
    Field.of("labels", StandardSQLTypeName.STRING),
    Field.of("issueName", StandardSQLTypeName.STRING),
    Field.of("parentissueid", StandardSQLTypeName.STRING),
    Field.of("parentissuetype", StandardSQLTypeName.STRING),
    Field.of("isDeleted", StandardSQLTypeName.STRING),
    Field.of("runDate", StandardSQLTypeName.TIMESTAMP)
);

这样提交的Schema就会和目标表的Schema完全匹配，加载数据时就不会再出现模式不匹配的错误。

内容的提问来源于stack exchange，提问作者gcpdev-guy