Query a column-based time-partitioned table
Stay organized with collections
Save and categorize content based on your preferences.
Query a table that uses column-based time partitioning.
Code sample
Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see the Google Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.
[[["Easy to understand","easyToUnderstand","thumb-up"],["Solved my problem","solvedMyProblem","thumb-up"],["Other","otherUp","thumb-up"]],[["Hard to understand","hardToUnderstand","thumb-down"],["Incorrect information or sample code","incorrectInformationOrSampleCode","thumb-down"],["Missing the information/samples I need","missingTheInformationSamplesINeed","thumb-down"],["Other","otherDown","thumb-down"]],[],[[["\u003cp\u003eThis page provides code samples in Go and Java for querying a time-partitioned table in BigQuery.\u003c/p\u003e\n"],["\u003cp\u003eThe code samples demonstrate how to query data within a specific date range (between 1800-01-01 and 1899-12-31) using a \u003ccode\u003eWHERE\u003c/code\u003e clause with a predicate on the partitioned column \u003ccode\u003edate\u003c/code\u003e.\u003c/p\u003e\n"],["\u003cp\u003eBoth code samples guide users to set up the proper credentials and environments for using the BigQuery client libraries before running the code.\u003c/p\u003e\n"],["\u003cp\u003eThe Java example utilizes named parameters for the start and end dates, while the Go example uses string formatting to construct the query.\u003c/p\u003e\n"],["\u003cp\u003eThe examples show how to execute a query, iterate over results, and print out the rows returned by the query.\u003c/p\u003e\n"]]],[],null,["# Query a column-based time-partitioned table\n\nQuery a table that uses column-based time partitioning.\n\nCode sample\n-----------\n\n### Go\n\n\nBefore trying this sample, follow the Go setup instructions in the\n[BigQuery quickstart using\nclient libraries](/bigquery/docs/quickstarts/quickstart-client-libraries).\n\n\nFor more information, see the\n[BigQuery Go API\nreference documentation](https://godoc.org/cloud.google.com/go/bigquery).\n\n\nTo authenticate to BigQuery, set up Application Default Credentials.\nFor more information, see\n\n[Set up authentication for client libraries](/bigquery/docs/authentication#client-libs).\n\n import (\n \t\"context\"\n \t\"fmt\"\n \t\"io\"\n\n \t\"cloud.google.com/go/bigquery\"\n \t\"google.golang.org/api/iterator\"\n )\n\n // queryPartitionedTable demonstrates querying a time partitioned table and using a predicate that\n // leverages the partitioned column.\n func queryPartitionedTable(w io.Writer, projectID, datasetID, tableID string) error {\n \t// projectID := \"my-project-id\"\n \t// datasetID := \"mydataset\"\n \t// tableID := \"mytable\"\n \tctx := context.Background()\n \tclient, err := bigquery.NewClient(ctx, projectID)\n \tif err != nil {\n \t\treturn fmt.Errorf(\"bigquery.NewClient: %w\", err)\n \t}\n \tdefer client.Close()\n\n \tq := client.Query(fmt.Sprintf(\"SELECT * FROM `%s.%s` WHERE `date` BETWEEN DATE('1800-01-01') AND DATE('1899-12-31')\", datasetID, tableID))\n \t// Run the query and process the returned row iterator.\n \tit, err := q.Read(ctx)\n \tif err != nil {\n \t\treturn fmt.Errorf(\"query.Read(): %w\", err)\n \t}\n \tfor {\n \t\tvar row []bigquery.https://cloud.google.com/go/docs/reference/cloud.google.com/go/bigquery/latest/index.html#cloud_google_com_go_bigquery_Value\n \t\terr := it.Next(&row)\n \t\tif err == iterator.Done {\n \t\t\tbreak\n \t\t}\n \t\tif err != nil {\n \t\t\treturn err\n \t\t}\n \t\tfmt.Fprintln(w, row)\n \t}\n \treturn nil\n }\n\n### Java\n\n\nBefore trying this sample, follow the Java setup instructions in the\n[BigQuery quickstart using\nclient libraries](/bigquery/docs/quickstarts/quickstart-client-libraries).\n\n\nFor more information, see the\n[BigQuery Java API\nreference documentation](/java/docs/reference/google-cloud-bigquery/latest/overview).\n\n\nTo authenticate to BigQuery, set up Application Default Credentials.\nFor more information, see\n\n[Set up authentication for client libraries](/bigquery/docs/authentication#client-libs).\n\n import com.google.cloud.bigquery.https://cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.BigQuery.html;\n import com.google.cloud.bigquery.https://cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.BigQueryException.html;\n import com.google.cloud.bigquery.https://cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.BigQueryOptions.html;\n import com.google.cloud.bigquery.https://cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.QueryJobConfiguration.html;\n import com.google.cloud.bigquery.https://cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.QueryParameterValue.html;\n import com.google.cloud.bigquery.https://cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.TableResult.html;\n\n // Sample to run query on partitioned table.\n public class QueryPartitionedTable {\n\n public static void main(String[] args) {\n // TODO(developer): Replace these variables before running the sample.\n String datasetName = \"MY_DATASET_NAME\";\n String tableName = \"MY_TABLE_NAME\";\n String query =\n String.format(\n \"SELECT * FROM `%s.%s` WHERE date BETWEEN @start_date AND @end_date\",\n datasetName, tableName);\n queryPartitionedTable(query);\n }\n\n public static void queryPartitionedTable(String query) {\n try {\n // Initialize client that will be used to send requests. This client only needs to be created\n // once, and can be reused for multiple requests.\n https://cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.BigQuery.html bigquery = https://cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.BigQueryOptions.html.getDefaultInstance().getService();\n\n https://cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.QueryJobConfiguration.html queryConfig =\n https://cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.QueryJobConfiguration.html.newBuilder(query)\n .https://cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.QueryJobConfiguration.Builder.html#com_google_cloud_bigquery_QueryJobConfiguration_Builder_addNamedParameter_java_lang_String_com_google_cloud_bigquery_QueryParameterValue_(\"start_date\", https://cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.QueryParameterValue.html.https://cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.QueryParameterValue.html#com_google_cloud_bigquery_QueryParameterValue_date_java_lang_String_(\"1800-01-01\"))\n .https://cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.QueryJobConfiguration.Builder.html#com_google_cloud_bigquery_QueryJobConfiguration_Builder_addNamedParameter_java_lang_String_com_google_cloud_bigquery_QueryParameterValue_(\"end_date\", https://cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.QueryParameterValue.html.https://cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.QueryParameterValue.html#com_google_cloud_bigquery_QueryParameterValue_date_java_lang_String_(\"1899-12-31\"))\n .build();\n\n https://cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.TableResult.html results = bigquery.https://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);\n\n results\n .https://cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.TableResult.html#com_google_cloud_bigquery_TableResult_iterateAll__()\n .forEach(row -\u003e row.forEach(val -\u003e System.out.printf(\"%s,\", val.toString())));\n\n System.out.println(\"Query partitioned table performed successfully.\");\n } catch (https://cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.BigQueryException.html | InterruptedException e) {\n System.out.println(\"Query not performed \\n\" + e.toString());\n }\n }\n }\n\nWhat's next\n-----------\n\n\nTo search and filter code samples for other Google Cloud products, see the\n[Google Cloud sample browser](/docs/samples?product=bigquery)."]]