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 connection

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

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

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

3. Set the connection 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;
   

Enable the connection

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