INVALID_ARGUMENT error when creating non-incremental materialized view using MaterializedViewDefinition.setMaxStaleness

[jingc-ac]

I'm using v2-rev20240229-2.0.0 and want to create a non-incremental materialized view, so i created MaterializedViewDefinition like:

      val viewDef = new MaterializedViewDefinition
      viewDef.setQuery(query).setAllowNonIncrementalDefinition(true).setMaxStaleness("INTERVAL \"4\" HOUR")

got error:

{
  "code": 400,
  "errors": [
    {
      "domain": "global",
      "message": "The max_staleness must be set for non-incremental materialized views.",
      "reason": "invalid"
    }
  ],
  "message": "The max_staleness must be set for non-incremental materialized views.",
  "status": "INVALID_ARGUMENT"
}

I tried many formats of maxStaleness as follow, always same error.
"INTERVAL "4" HOUR"
"INTERVAL 4 HOUR"
"4 hours"
"INTERVAL "4:0:0" HOUR TO SECOND"
"4:0:0"
"INTERVAL '4:0:0' HOUR TO SECOND"
"0-0 103 16:10:25"
"'4:0:0' HOUR TO SECOND"
"4 HOUR"

which format should I use?

[diegomarquezp]

Hi @jingc-ac, thanks for sharing your concern. At a glance, it seems like the maxStaleness interval is not being sent in the request. Would you mind expanding the reproduction snippet you shared to include the way you create the client and request execution?

Also, there is a more up-to-date bigquery library in case you were not aware of it. It includes a sample on materialized views

[jingc-ac]

Hi @diegomarquezp I'm not using google-cloud-bigquery lib, i'm using "com.google.apis" % "google-api-services-bigquery" % "v2-rev20240229-2.0.0"
the example you sent does not work for me, because I need to create a non-incremental materialized view which the max-staleness and allow_non_incremental_definition need to be set. The google-cloud-bigquery lib does not have these APIs, I have to use google-api-services-bigquery lib.

import com.google.api.client.http.javanet.NetHttpTransport
import com.google.api.services.bigquery.Bigquery
import com.google.api.client.json.gson.GsonFactory
import com.google.auth.http.HttpCredentialsAdapter
import com.google.auth.oauth2.GoogleCredentials
import com.google.api.services.bigquery.model.{MaterializedViewDefinition, Table, TableReference}

  def main(args: Array[String]): Unit = {
    val projectId = "..."
    val datasetName = "..."
    val materializedViewName = "..."
    val query = "SELECT..."
    val bigquery = createAuthorizedClient
    createNonIncrementalMaterializedView(bigquery, projectId, datasetName, materializedViewName, query)
  }

 def createAuthorizedClient: Bigquery = {
    val credentials = GoogleCredentials.getApplicationDefault
    val requestInitializer = new HttpCredentialsAdapter(credentials)
    val HTTP_TRANSPORT = new NetHttpTransport
    val JSON_FACTORY = new GsonFactory
    new Bigquery.Builder(HTTP_TRANSPORT, JSON_FACTORY, requestInitializer)
      .setApplicationName("test/1.0")
      .build()
  }
  def createNonIncrementalMaterializedView(bigquery: Bigquery, projectId: String, datasetName: String, materializedViewName: String, query: String): Unit = {
    try {
      val tView = new Table
      val tableRef = new TableReference
      tableRef.setTableId(materializedViewName)
      tableRef.setDatasetId(datasetName)
      tableRef.setProjectId(projectId)
      tView.setTableReference(tableRef)
      val viewDef = (new MaterializedViewDefinition).setQuery(query)
        .setAllowNonIncrementalDefinition(true)
        .setMaxStaleness("INTERVAL \"4\" HOUR")
      tView.setMaterializedView(viewDef)
      bigquery.tables.insert(projectId, datasetName, tView).execute
      System.out.println(s"Materialized view ${projectId}.${datasetName}.${materializedViewName} created successfully")
    } catch {
      case e: Throwable =>
        System.out.println(s"Materialized view ${projectId}.${datasetName}.${materializedViewName} was not created. \n" + e.toString)
    }
  }

[diegomarquezp]

Thank you for sharing the reproducer. I did create a java version of it to ease the troubleshooting on our side.

You were right about google-cloud-bigquery not supporting non incremental materialized views, apologies for the misleading info.

I'll get back to you after inspecting the requests being created

[jingc-ac]

@diegomarquezp it turns out both MaterializedViewDefinition and Table have setMaxStaleness method, and I need to use the Table.setMaxStaleness instead of MaterializeViewDefinition.setMaxStaleness to successfully create materialized view. i.e. in my code, it's like:

tView.setMaterializedView(viewDef).setMaxStaleness("0-0 0 4:0:0")

This is confusing

[diegomarquezp]

Glad you found out the solution. This is indeed something not obvious judging from the documentation and should be addressed.

@suztomo apparently the only source of usage information is the javadoc plus some generic documentation in the official docs

[suztomo]

@jingc-ac Which document did you reference when you created this issue?

[jingc-ac]

@suztomo https://googleapis.dev/java/google-api-services-bigquery/latest/com/google/api/services/bigquery/model/MaterializedViewDefinition.html

[suztomo]

Thank you.

https://googleapis.dev/java/google-api-services-bigquery/latest/com/google/api/services/bigquery/model/MaterializedViewDefinition.html has:

setMaxStaleness(String maxStaleness)
[Optional] Max staleness of data that could be returned when materizlized view is queried (formatted as Google SQL Interval type).

You read some document when you wanted to create a non-incremental materialized view. What was that document?

[diegomarquezp]

Hi @jingc-ac. Was this the only source of information to produce the code that worked?