Create materialized view

Stay organized with collections Save and categorize content based on your preferences.

Create a materialized view.

Explore further

For detailed documentation that includes this code sample, see the following:

Code sample


Before trying this sample, follow the Go setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Go API reference documentation.

import (


// createMaterializedView demonstrates generated a materialized view based on an existing
// base table.
func createMaterializedView(projectID, datasetID, baseTableID, viewID string) error {
	// projectID := "my-project-id"
	// datasetID := "mydatasetid"
	// baseTableID := "mytableid"
	// viewID := "myviewid"
	ctx := context.Background()

	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %w", err)
	defer client.Close()

	// Get an appropriately escaped table identifier suitable for use in a standard SQL query.
	tableStr, err := client.Dataset(datasetID).Table(baseTableID).Identifier(bigquery.StandardSQLID)
	if err != nil {
		return fmt.Errorf("couldn't construct identifier: %w", err)

	metaData := &bigquery.TableMetadata{
		MaterializedView: &bigquery.MaterializedViewDefinition{
			Query: fmt.Sprintf(`SELECT MAX(TimestampField) AS TimestampField, StringField, 
					  MAX(BooleanField) AS BooleanField FROM %s GROUP BY StringField`, tableStr),

	viewRef := client.Dataset(datasetID).Table(viewID)
	if err := viewRef.Create(ctx, metaData); err != nil {
		return err
	return nil


Before trying this sample, follow the Java setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Java API reference documentation.


// Sample to create materialized view
public class CreateMaterializedView {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    String materializedViewName = "MY_MATERIALIZED_VIEW_NAME";
    String query =
            "SELECT MAX(TimestampField) AS TimestampField, StringField, "
                + "MAX(BooleanField) AS BooleanField "
                + "FROM %s.%s GROUP BY StringField",
            datasetName, tableName);
    createMaterializedView(datasetName, materializedViewName, query);

  public static void createMaterializedView(
      String datasetName, String materializedViewName, String query) {
    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.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      TableId tableId = TableId.of(datasetName, materializedViewName);

      MaterializedViewDefinition materializedViewDefinition =

      bigquery.create(TableInfo.of(tableId, materializedViewDefinition));
      System.out.println("Materialized view created successfully");
    } catch (BigQueryException e) {
      System.out.println("Materialized view was not created. \n" + e.toString());


Before trying this sample, follow the Python setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Python API reference documentation.

from import bigquery

bigquery_client = bigquery.Client()

view_id = "my-project.my_dataset.my_materialized_view"
base_table_id = "my-project.my_dataset.my_base_table"
view = bigquery.Table(view_id)
view.mview_query = f"""
SELECT product_id, SUM(clicks) AS sum_clicks
FROM  `{base_table_id}`

# Make an API request to create the materialized view.
view = bigquery_client.create_table(view)
print(f"Created {view.table_type}: {str(view.reference)}")

What's next

To search and filter code samples for other Google Cloud products, see the Google Cloud sample browser.