Unable to copy column details on bigquery while using create table query!

[mmadasamy]

Hi,

create table TARGET_TABLE as select COLUMN_1, COLUMN_2 from SOURCE_TABLE;

I have some of not nullable columns in my source table in bigquery. But these columns are not nullable in my target table on above query. Is there any way to replicate my columns as nullable or not nullable as like source table while using above query.  Seems to be this is working other DWH system Netezza. 

Note: All COLUMN_1 and COLUMN_2 are coming dynamically on above query. It's not static way.

Thanks,

Samy

[ms4446 · Accepted solution]

Unfortunately, BigQuery doesn't automatically copy the nullability information while using the CREATE TABLE ... AS SELECT statement. This means that even if the source table has columns with NOT NULL constraints, the target table's corresponding columns will be created as NULLABLE by default.

However, there are two ways to achieve your desired outcome:

1. Manually Specify the Nullability for Each Column

This is a straightforward approach, but it may not be practical for tables with a large number of columns or dynamic column names.

CREATE TABLE TARGET_TABLE (
  COLUMN_1 data_type NOT NULL,
  COLUMN_2 data_type,
  ...
) AS SELECT COLUMN_1, COLUMN_2 FROM SOURCE_TABLE;

Replace data_type with the actual data type of each column.

2. Use the INFORMATION_SCHEMA to Dynamically Build the CREATE Statement

This approach utilizes the INFORMATION_SCHEMA.COLUMNS view to dynamically build the CREATE TABLE statement based on the source table's schema, including nullability constraints. It's ideal for handling many columns or dynamically generated column lists.

DECLARE schema_string STRING;
SET schema_string = (
  SELECT STRING_AGG(
    FORMAT("""%s %s %s""", column_name, data_type, IF(is_nullable = 'NO', 'NOT NULL', '')), ', '
  )
  FROM `project.dataset.INFORMATION_SCHEMA.COLUMNS`
  WHERE table_name = 'SOURCE_TABLE'
  ORDER BY ordinal_position
);
SET schema_string = CONCAT('CREATE TABLE TARGET_TABLE (', schema_string, ');');
EXECUTE IMMEDIATE schema_string;
INSERT INTO TA

Ensure to replace project.dataset with your actual project and dataset names. This script aggregates the column definitions into a single string, which is then used to construct and execute the CREATE TABLE statement.

Both methods have their use cases:

• Method 1: Simpler but less flexible.
• Method 2: More complex but highly adaptable to varying schemas.