Query a table that has a clustering specification. ## Explore further For detailed documentation that includes this code sample, see the following: - [Query clustered tables](https://docs.cloud.google.com/bigquery/docs/querying-clustered-tables) ## Code sample ### Go Before trying this sample, follow the Go setup instructions in the [BigQuery quickstart using client libraries](https://docs.cloud.google.com/bigquery/docs/quickstarts/quickstart-client-libraries). For more information, see the [BigQuery Go API reference documentation](https://godoc.org/cloud.google.com/go/bigquery). To authenticate to BigQuery, set up Application Default Credentials. For more information, see [Set up authentication for client libraries](https://docs.cloud.google.com/bigquery/docs/authentication#client-libs). import ( "context" "fmt" "io" "cloud.google.com/go/bigquery" "google.golang.org/api/iterator" ) // queryClusteredTable demonstrates querying a table that has a clustering specification. func queryClusteredTable(w io.Writer, projectID, datasetID, tableID string) error { // projectID := "my-project-id" // datasetID := "mydataset" // tableID := "mytable" ctx := context.Background() client, err := bigquery.NewClient(ctx, projectID) if err != nil { return fmt.Errorf("bigquery.NewClient: %w", err) } defer client.Close() q := client.Query(fmt.Sprintf(` SELECT COUNT(1) as transactions, SUM(amount) as total_paid, COUNT(DISTINCT destination) as distinct_recipients FROM `+"`%s.%s`"+` WHERE timestamp > TIMESTAMP('2015-01-01') AND origin = @wallet`, datasetID, tableID)) q.Parameters = []bigquery.https://docs.cloud.google.com/go/docs/reference/cloud.google.com/go/bigquery/latest/index.html#cloud_google_com_go_bigquery_QueryParameter{ { Name: "wallet", Value: "wallet00001866cb7e0f09a890", }, } // Run the query and process the returned row iterator. it, err := q.Read(ctx) if err != nil { return fmt.Errorf("query.Read(): %w", err) } for { var row []bigquery.https://docs.cloud.google.com/go/docs/reference/cloud.google.com/go/bigquery/latest/index.html#cloud_google_com_go_bigquery_Value err := it.Next(&row) if err == iterator.Done { break } if err != nil { return err } fmt.Fprintln(w, row) } return nil } ### Java Before trying this sample, follow the Java setup instructions in the [BigQuery quickstart using client libraries](https://docs.cloud.google.com/bigquery/docs/quickstarts/quickstart-client-libraries). For more information, see the [BigQuery Java API reference documentation](https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/overview). To authenticate to BigQuery, set up Application Default Credentials. For more information, see [Set up authentication for client libraries](https://docs.cloud.google.com/bigquery/docs/authentication#client-libs). import com.google.cloud.bigquery.https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.BigQuery.html; import com.google.cloud.bigquery.https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.BigQueryException.html; import com.google.cloud.bigquery.https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.BigQueryOptions.html; import com.google.cloud.bigquery.https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.QueryJobConfiguration.html; import com.google.cloud.bigquery.https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.TableResult.html; public class QueryClusteredTable { public static void main(String[] args) throws Exception { // TODO(developer): Replace these variables before running the sample. String projectId = "MY_PROJECT_ID"; String datasetName = "MY_DATASET_NAME"; String tableName = "MY_TABLE_NAME"; queryClusteredTable(projectId, datasetName, tableName); } public static void queryClusteredTable(String projectId, String datasetName, String tableName) { try { // Initialize client that will be used to send requests. This client only needs to be created // once, and can be reused for multiple requests. https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.BigQuery.html bigquery = https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.BigQueryOptions.html.getDefaultInstance().getService(); String sourceTable = "`" + projectId + "." + datasetName + "." + tableName + "`"; String query = "SELECT word, word_count\n" + "FROM " + sourceTable + "\n" // Optimize query performance by filtering the clustered columns in sort order + "WHERE corpus = 'romeoandjuliet'\n" + "AND word_count >= 1"; https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.QueryJobConfiguration.html queryConfig = https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.QueryJobConfiguration.html.newBuilder(query).build(); https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.TableResult.html results = bigquery.https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.BigQuery.html#com_google_cloud_bigquery_BigQuery_query_com_google_cloud_bigquery_QueryJobConfiguration_com_google_cloud_bigquery_BigQuery_JobOption____(queryConfig); results .https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.TableResult.html#com_google_cloud_bigquery_TableResult_iterateAll__() .forEach(row -> row.forEach(val -> System.out.printf("%s,", val.toString()))); System.out.println("Query clustered table performed successfully."); } catch (https://docs.cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.BigQueryException.html | InterruptedException e) { System.out.println("Query not performed \n" + e.toString()); } } } ### Python Before trying this sample, follow the Python setup instructions in the [BigQuery quickstart using client libraries](https://docs.cloud.google.com/bigquery/docs/quickstarts/quickstart-client-libraries). For more information, see the [BigQuery Python API reference documentation](https://docs.cloud.google.com/python/docs/reference/bigquery/latest). To authenticate to BigQuery, set up Application Default Credentials. For more information, see [Set up authentication for client libraries](https://docs.cloud.google.com/bigquery/docs/authentication#client-libs). from google.cloud import https://docs.cloud.google.com/python/docs/reference/bigquery/latest # Construct a BigQuery client object. client = https://docs.cloud.google.com/python/docs/reference/bigquery/latest.https://docs.cloud.google.com/python/docs/reference/bigquery/latest/google.cloud.bigquery.client.Client.html() # TODO(developer): Set table_id to the ID of the destination table. # table_id = "your-project.your_dataset.your_table_name" sql = "SELECT * FROM `bigquery-public-data.samples.shakespeare`" cluster_fields = ["corpus"] job_config = https://docs.cloud.google.com/python/docs/reference/bigquery/latest.https://docs.cloud.google.com/python/docs/reference/bigquery/latest/google.cloud.bigquery.job.QueryJobConfig.html( clustering_fields=cluster_fields, destination=table_id ) # Start the query, passing in the extra configuration. client.https://docs.cloud.google.com/python/docs/reference/bigquery/latest/google.cloud.bigquery.client.Client.html#google_cloud_bigquery_client_Client_query_and_wait( sql, job_config=job_config ) # Make an API request and wait for job to complete. table = client.https://docs.cloud.google.com/python/docs/reference/bigquery/latest/google.cloud.bigquery.client.Client.html#google_cloud_bigquery_client_Client_get_table(table_id) # Make an API request. if table.clustering_fields == cluster_fields: print( "The destination table is written using the cluster_fields configuration." ) ### Rust use google_cloud_bigquery::client::BigQuery; pub async fn sample(project_id: &str) -> anyhow::Result<()> { let client = BigQuery::builder().build().await?; let mut rows = client .query( "SELECT \ title, views FROM `bigquery-public-data.wikipedia.pageviews_2020` \ WHERE datehour = '2020-01-01 00:00:00' AND title = 'Google' \ LIMIT 10", ) .with_project_id(project_id) .set_location("US") .until_done() .await? .read(); while let Some(row) = rows.next().await.transpose()? { let title: String = row.get("title")?; let views: i64 = row.get("views")?; println!("Title: {title}, Views: {views}"); } Ok(()) } ## What's next To search and filter code samples for other Google Cloud products, see the [Google Cloud sample browser](https://docs.cloud.google.com/docs/samples?product=bigquery).