Google BigQuery视图无法保留数据模式，UI创建视图字段均为NULLABLE且不可修改，如何解决？

Yep, I’ve run into this exact frustration with BigQuery’s UI for creating views—defaulting every field to NULLABLE and not preserving the original table’s schema is such a pain. But there are a couple of solid workarounds to fix this:

1. 手动编写SQL视图语句，显式指定字段模式 #

Instead of relying on the UI’s auto-generated view definition, write your own CREATE VIEW statement where you explicitly define each field’s nullability to match the source table.

• First, get the source table’s schema details (you can pull this from the table’s DDL or the INFORMATION_SCHEMA). For example, run this query to fetch the table’s column definitions:

  SELECT column_name, data_type, is_nullable
  FROM `your-project.your-dataset.INFORMATION_SCHEMA.COLUMNS`
  WHERE table_name = 'your-source-table';
  

• Then build your view creation statement with explicit nullability:

  CREATE VIEW `your-project.your-dataset.your-target-view`
  (
    user_id INT64 NOT NULL,
    username STRING NOT NULL,
    signup_date DATE NULLABLE
  )
  AS
  SELECT user_id, username, signup_date FROM `your-project.your-dataset.your-source-table`;
  

  This forces the view to use the exact nullability rules you define, instead of defaulting everything to NULLABLE.

2. 动态生成视图DDL（适合字段较多的表） #

If your source table has tons of columns, manually writing every field is tedious. Use this query to auto-generate the full CREATE VIEW statement that mirrors the source table’s schema:

SELECT CONCAT(
  'CREATE VIEW `your-project.your-dataset.your-target-view` (\n',
  STRING_AGG(CONCAT('  ', column_name, ' ', data_type, ' ', is_nullable), ',\n'),
  '\n) AS SELECT ', STRING_AGG(column_name, ', '), ' FROM `your-project.your-dataset.your-source-table`;'
)
FROM `your-project.your-dataset.INFORMATION_SCHEMA.COLUMNS`
WHERE table_name = 'your-source-table';

Run this query, copy the generated SQL output, and execute it. This will create a view that perfectly matches the source table’s field nullability.

Why the UI behaves this way #

Quick context: When you create a view via the BigQuery UI, it infers the schema from the query result. Since SQL queries can potentially return NULL values even if the source field is NOT NULL, the UI plays it safe and defaults all fields to NULLABLE. Manual SQL definitions are the only way to override this behavior.

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