BigQuery Clustered Tables

BigQuery supports clustering for both partitioned and non-partitioned tables. Clustering improves the performance of queries that use filter clauses and queries that aggregate data. For more information about clustered tables, see BigQuery's clustered table documentation.

Fivetran supports clustering for partitioned and non-partitioned tables in your BigQuery data warehouse.

Create clustered tables

To convert BigQuery non-clustered tables (non-partitioned or partitioned) into clustered tables, do the following:

Pause the connector

1. Go to your Fivetran dashboard and select the connector for which you want to convert your non-clustered tables into clustered tables.

2. Check the connector sync status in the top right corner of your dashboard: ENABLED or PAUSED.

   If your connector is already paused, skip to Step 2.

3. Set the connector status toggle to PAUSED.

Convert to clustered tables

1. In the BigQuery console, execute one of the following SQL statements to convert an existing table into a clustered table. The following statements will create a clustered table, insert data from the original table, and then drop the original table:

   • For a non-partitioned table, use:

   create table [schema-name].copy
   cluster by [column_names_upto_4]
   as select * from [schema-name].[table-name];
   drop table [schema-name].[table-name];
   

   • For a partitioned table based on a TIMESTAMP column, use:

   create table [schema-name].copy
   partition by date([timestamp-column])
   cluster by [column_names_upto_4]
   as select * from [schema-name].[table-name];
   drop table [schema-name].[table-name];
   

2. Go to the BigQuery Web UI in the GCP console.

3. In the navigation bar, select your project.

   

4. Select your dataset. In the dataset, find and select the copy table you created.

   

5. Rename the copy table to have the original table's name. In the details panel, click Copy Table.

   

6. In the Table name field, enter your original table's name and click Copy.

   

7. Drop the copy table. Execute the following SQL command in the BigQuery query editor:

   drop table [schema-name].copy;
   

IMPORTANT: Repeat the above instructions for every table you want to convert.

Enable the connector

Go to your Fivetran dashboard and set the connector status toggle to ENABLED.