Getting Table Not found Exception

[techGeek]

Stackoverflow link

I am using Java SDK jars for BigQuery data fetching. From BigQuery I am fetching a detailed Usage Cost report for Invoice month. In my account, there are more resources so the Bill value is also high. The total rows present in the BigQuery for the particular invoice month is 19680970. so I used the below approach like querying for the entire invoice month once so that Big Query will have a temporary table so that results will be quicker and cost efficient.

Unfortunately, the process took more than a day, so while querying from the nextPage token as per the below code getting Table not found. I think internally nextPage token points to the temporary table. As per Google documentation Temporary table expires after 24 hours. It seems to be not handled on the Google side by changing the next page token.

My code is

    String query = "SELECT * FROM `Sample-3806.Usage.gcp_billing_export_resource_v1_01HYD5_5UDWC_836E8` where invoice.month="202310";
    TableResult tableResult = this.bigQueryExecutor.executeBigQuery(query);
    if(tableResult!=null)
    {                           
        do
        {
            if(isNextPageAvailable && tableResult!=null)
            {
                tableResult = tableResult.getNextPage();
                isNextPageAvailable = false;
            }
            if(tableResult!=null)
            {
                Iterable<FieldValueList> itr = tableResult.getValues();
                for(FieldValueList valueList : itr)
                {
                    // Code logics
                }
                isNextPageAvailable = tableResult.hasNextPage();
            }
        }while(isNextPageAvailable);
    }

Getting exception like ::

{
  "code": 404,
  "errors": [
    {
      "domain": "global",
      "message": "Not found: Table Sample-3806.Usage.gcp_billing_export_resource_v1_01HYD5_5UDWC_836E8:_d5733737d9bad3beded5b.35no6nc1575fccadb9fc743d158beda8a28fca489",
      "reason": "notFound"
    }
  ],
  "message": "Not found: Table Sample-3806.Usage.gcp_billing_export_resource_v1_01HYD5_5UDWC_836E8:_d5733737d9bad3beded5b.35no6nc1575fccadb9fc743d158beda8a28fca489",
  "status": "NOT_FOUND"
}

What changes are required to fix this by cost efficiency?

[ms4446]

The challenge arises from the temporary tables BigQuery creates to store query results, which expire after about 24 hours. Attempting to access these results after the expiration leads to a "Table not found" error, particularly problematic when dealing with large datasets and pagination.

Strategies for Efficiently Handling Large Queries

• Pagination with a Shorter Time Frame: Break your queries into smaller intervals (daily, weekly) to reduce the size of temporary tables. This may increase overall query costs, but individual queries will be faster.

• Export Query Results to a Permanent Table: Save initial query results to a permanent BigQuery table to ensure data persistence and avoid expiration issues.

• Incremental Data Fetching: Use date or timestamp columns to query your data in smaller, manageable chunks.

Optimize Query Performance

• Selective Column Querying: Query only the columns you need to reduce data processing.
• Effective Filtering: Use WHERE clauses to narrow down the dataset your query needs to scan.
• Materialized Views: Create pre-computed views for frequently used billing data to speed up queries.

Manage Pagination Carefully

Handle pagination properly, especially for queries that may run longer than the 24-hour lifespan of temporary tables.

Cost Management

• BigQuery Slots: Consider purchasing more BigQuery slots for faster processing of large queries.
• Cost Controls: Set up BigQuery cost controls to manage unexpected expenses.

String destinationTable = "your_dataset.your_new_table"; 
String query = String.format( "CREATE TABLE `%s` AS SELECT * FROM `Sample-3806.Usage.gcp_billing_export_resource_v1_01HYD5_5UDWC_836E8` WHERE invoice.month='202310'", destinationTable); 
this.bigQueryExecutor.executeBigQuery(query); 

Choose the best strategies based on your use case, considering data access frequency and real-time update needs. Combine these strategies with query optimization and cost management techniques for an efficient and cost-effective approach to working with large datasets in BigQuery.

[techGeek]

Hi @ms4446 , thanks for the response. I cannot fetch data by day or weekly because of some value mismatches. Is there any way to identify if a temporary table exists and if it does not exist query from the main table?

[ms4446]

In BigQuery, there isn't a direct method to check for the existence of a temporary table created as a result of a query job. This is because their ephemeral nature means BigQuery's metadata doesn't track them the same way as permanent tables. Here's a strategy to handle scenarios where a temporary table may have expired or wasn't directly created by you:

Strategy for Handling Temporary Table Expiration

Since your application cannot directly check for a temporary table's existence, structure your logic to gracefully handle exceptions:

1. Attempt to Query the Temporary Table: Begin by querying the data you expect to be in the temporary table.
2. Catch Specific Exceptions for Missing Data: If the query fails with a BigQueryException whose message suggests the table is missing (e.g., contains "Not found: Table" or has an error code of 404), that's your signal the temporary table likely expired.
3. Fallback to the Main Table: On catching this specific exception, execute your query directly against the main table.

Implementing Fallback Logic in Java

Here's how this logic might look in Java, assuming you're primarily catching BigQueryException:

public TableResult executeQueryWithFallback(String tempTableQuery, String mainTableQuery) { 
    try { 
        TableResult result = this.bigQueryExecutor.executeBigQuery(tempTableQuery); 
        return result; 
    } catch (BigQueryException e) { 
        if (e.getMessage().contains("Not found: Table") || e.getCode() == 404) { 
            try { 
               TableResult fallbackResult = this.bigQueryExecutor.executeBigQuery(mainTableQuery); 
                return fallbackResult; 
            } catch (BigQueryException fallbackException) { 
                throw fallbackException; // Handle other errors
            } 
        } else { 
            throw e; // Rethrow if not a missing table error
        } 
    } 
} 

Notes:

• Other Workarounds: If strict exception handling isn't feasible, consider less precise workarounds like assuming a temporary table has likely expired if it was created more than a set amount of time ago (e.g., 23 hours).
• Query Efficiency: Optimize your queries whenever possible, especially the fallback query against the main table.
• Cost Implications: Repeated fallbacks to the main table can increase costs. Assess this risk against the benefits of using temporary tables in your workflow.