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. Read BigQuery's clustered table documentation for more information.

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

Create Clustered Tables

Follow the step-by-step instructions to convert a BigQuery non-clustered table (non-partitioned or partitioned) into a clustered table.

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 table

1. In the BigQuery console, execute the following SQL statements to convert an existing table into a clustered table:

   • For non-partitioned tables 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 partitioned tables on TIMESTAMP column type 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 on the left of the console, select your project.

   

4. Select your dataset. In the dataset, find the table you want to copy and select that table.

   

5. In the navigation bar just above the detail view of your selected table, click Copy Table.

   

6. Enter your original table name and click Copy.

   

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

   drop table [schema-name].copy;
   

8. Repeat the steps for the tables you want to convert.

Enable the connector

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