Error Code 7

[mcancell]

All of a sudden starting today (2023-12-13) I cannot save Results to a BQ Table. I tried by saving from the editor and also tried scheduling the query, specifying the output table. It seems to be confined to just one query.  Actually, I see that query tables that I was able to write yesterday will no longer write.

I am the Owner of the account and should have all perms to allow basically any operation, incl. writing tables, etc.

Has anyone else ever encountered this issue?

Error code 7 : Access Denied: Table ps-analytics-329721:XT_Web_Logs.qt_Ford_Month: Permission bigquery.tables.get denied on table ps-analytics-329721:XT_Web_Logs.qt_Ford_Month (or it may not exist).

[ms4446 · Accepted solution]

It's good to hear that you were able to create a new table using the CREATE OR REPLACE TABLE statement with your query. This indicates that you do have the necessary permissions to create and write to a table in BigQuery. The issue seems to be specific to the way you were initially trying to save the query results to a table.

Given this new information, here are a few additional considerations:

1. Query Execution Context:

   • When you use CREATE OR REPLACE TABLE as part of your query, it directly creates or replaces a table with the result set of the query. This is a different operation compared to running a query and then saving the results to a table. The former is a DDL (Data Definition Language) operation, while the latter is more of a DML (Data Manipulation Language) operation followed by a write operation.

2. Permissions for Different Operations:

   • The fact that the table creation works fine suggests that your permissions are correctly set for creating and writing to tables. The issue might be with the specific method or interface you were using to save the results initially.

3. Interface or Tool-Specific Issues:

   • If you were using a specific interface or tool (like the BigQuery web UI or a third-party tool like Coginiti) to run the query and then save the results, there might be some limitations or issues with that specific process. It's worth trying different methods (like using the BigQuery CLI or another tool) to see if the issue persists.

4. Query Complexity:

   • Complex queries, especially those involving large datasets or complicated transformations, might encounter issues when being saved directly to a table through certain interfaces. The CREATE OR REPLACE TABLE approach might be more robust in handling such complexities.

5. Next Steps:

   • If the CREATE OR REPLACE TABLE approach works for your needs, it might be a good workaround for the issue you're facing. However, if you need to use the original method (running a query and then saving the results), you might want to explore different tools or interfaces, or even reach out to Google Cloud Support for more specific guidance.

While the root cause of the initial issue isn't entirely clear, the successful creation of a table using a different method suggests that it might be related to the specific way you were trying to save the results initially. Exploring alternative methods or tools could be a practical next step.

[ms4446]

Here are some steps you can take to troubleshoot and resolve this issue:

1. Check IAM Permissions:

   • Even as the owner, it's worth double-checking the IAM (Identity and Access Management) permissions for your account. Ensure that you have roles like BigQuery Admin or BigQuery Data Editor, which allow for table creation and data manipulation.

2. Examine the Specific Table:

   • The error mentions the table ps-analytics-329721:XT_Web_Logs.qt_Ford_Month. Verify that this table exists and that you have the necessary permissions to access and modify it. If the table doesn't exist, the error might be misleading, and the real issue could be with table creation.

3. Review Recent Changes:

   • Since you mentioned that this issue started suddenly, review any recent changes made to your BigQuery settings or IAM policies. Sometimes, changes in one part of the Google Cloud setup can inadvertently affect other areas.

4. Try a Different Query or Table:

   • To isolate the issue, try writing to a different table or running a different query. If these actions succeed, the problem might be specific to the original table or query.

5. Use the bq Command-Line Tool:

   • If you're comfortable with command-line tools, use the bq command-line tool to attempt the same operation. Sometimes, the command-line tool provides more detailed error messages.

6. Review Quotas and Limits:

   • Ensure that you haven't exceeded any quotas or limits that might prevent table creation or data insertion.

7. Inspect the Query:

   • There might be something specific in the query causing the issue. Ensure that the query syntax is correct and that it's structured properly to write to a table.

8. Check Dataset Permissions:

   • Ensure that you have the necessary permissions on the dataset XT_Web_Logs as well, not just the table.

[mcancell]

Thank you. I am now going to go thru the checklist and do each of those steps.  I will post results and let you know what happened.

[mcancell]

1. Check IAM Permissions:

• aiplatform.notebookRuntimes.assign
• appengine.applications.get
• bigquery.connections.create
• bigquery.connections.list
• bigquery.datasets.create
• bigquery.datasets.delete
• bigquery.datasets.get
• bigquery.datasets.getIamPolicy
• bigquery.datasets.listTagBindings
• bigquery.jobs.create
• bigquery.jobs.list
• bigquery.jobs.listAll
• bigquery.models.create
• bigquery.models.delete
• bigquery.models.getData
• bigquery.models.list
• bigquery.models.updateData
• bigquery.models.updateMetadata
• bigquery.reservationAssignments.list
• bigquery.reservations.list
• bigquery.routines.delete
• bigquery.routines.get
• bigquery.routines.list
• bigquery.savedqueries.create
• bigquery.savedqueries.delete
• bigquery.savedqueries.get
• bigquery.savedqueries.list
• bigquery.tables.getIamPolicy
• bigquery.tables.setIamPolicy
• bigquery.transfers.get
• bigquery.transfers.update
• billing.resourcebudgets.read
• billing.resourceCosts.get
• cloudasset.assets.searchAllResources
• cloudnotifications.activities.list
• cloudprivatecatalogproducer.products.create
• dataform.repositories.commit
• dataform.repositories.create
• dataform.repositories.list
• iam.serviceAccounts.get
• iam.serviceAccounts.list
• logging.logEntries.list
• logging.privateLogEntries.list
• monitoring.notificationChannels.list
• orgpolicy.policy.get
• recommender.bigqueryPartitionClusterRecommendations.list
• recommender.iamPolicyInsights.get
• recommender.iamPolicyInsights.list
• recommender.iamPolicyLateralMovementInsights.get
• recommender.iamPolicyLateralMovementInsights.list
• recommender.iamPolicyRecommendations.get
• recommender.iamPolicyRecommendations.list
• recommender.iamPolicyRecommendations.update
• resourcemanager.projects.createBillingAssignment
• resourcemanager.projects.get
• resourcemanager.projects.getIamPolicy
• resourcemanager.projects.setIamPolicy
• resourcemanager.projects.update
• serviceusage.services.disable
• serviceusage.services.enable
• serviceusage.services.get
• serviceusage.services.list

2.  Examine the Specific Table: - I believe this is misleading bc it's a new table I am trying to write, so it does NOT exist yet

3. Review Recent Changes: - I've not touched IAM perms in many months (years?)

4. Try a Different Query or Table:

Well this is interesting. I tried a brain-dead simple query and was able to export the results to a BQ Table. Now, one thing I notice is that the original query (that I referenced) is under the category of  "Shared queries". The one I was able to run is under  "(Classic) Queries (122)->"Project queries". Could that be causing this? I rarely use the resident BQ Editor anymore. I switched over to using Coginiti for editing, but that product doesnt support reading JSON tables so I created the new queries in BQ. But again, it was working yesterday...

---

SELECT DISTINCT
  *,
  DATE_TRUNC(CURRENT_DATE(), WEEK(MONDAY)) AS HistoryWeek 
FROM `ps-analytics-329721.Query_Tables.qt_LoE_Perf` 

5. Use thebqCommand-Line Tool: - I am not familiar with using the bq CMD line so I'd need to figure that out.

6. Review Quotas and Limits: - Hmmm I am seeing this "Current usage > 90%". When I go to "Edit Quota", there no Quotas to choose. Could this be the issue?

7. Inspect the Query:

    # Day 1
      SELECT 
        meta.count,
        meta.`completed-in`, 
        data.country,
        data.language,
        (SELECT STRING_AGG(CAST(entityTags AS STRING), '\n') FROM UNNEST(data.entityTags)  entityTags) AS entityTags,
        (SELECT STRING_AGG(modelTags,  '\n')                 FROM UNNEST(data.modelTags)   modelTags)  AS modelTags,
        (SELECT STRING_AGG(makeTags,   '\n')                 FROM UNNEST(data.makeTags)    makeTags)   AS makeTags,
        data.sa,
        data.messageType,
        (SELECT STRING_AGG(classTags,  '\n')                 FROM UNNEST(data.classTags)   classTags)   AS classTags,
        data.published,
        data.author,
        data.content,
        data.link,
        data.queue,
        (SELECT STRING_AGG(userTags,  '\n')                  FROM UNNEST(data.userTags)    userTags)   AS userTags,
        data.title,
        data.dataSource,
        data.threadId,
        data.dataCategory,
        data.id,
        DATE_TRUNC(CAST(data.published AS DATE), DAY)           AS min_Week, 
        DATE_ADD(CAST(data.published AS DATE), INTERVAL 1 WEEK) AS max_Week 
      FROM `ps-analytics-329721.XT_Web_Logs.xt_Ford_01` CROSS JOIN UNNEST(`data`) as data
      WHERE 
            (data.dataSource   LIKE '%Mustang%' OR data.dataSource LIKE '%F150%' OR data.dataSource LIKE '%Escape%')
        AND data.messageType  ='public'
        AND (data.country     ='USA' OR data.country='Canada')
        AND data.queue        <> 'Not Relevant'  
    UNION ALL 
    # Day 2
Repeats for 31 Days ...

I am wondering if this is because the data is JSON and has repeatable sections?

Table info
Table ID: ps-analytics-329721.XT_Web_Logs.xt_Ford_01
Created: Dec 11, 2023, 7:31:39 PM UTC-5
Last modified: Dec 11, 2023, 7:32:10 PM UTC-5
Table expiration: NEVER
Data location: US
Case insensitive: false
Description: Labels
Primary key(s): External Data Configuration
Source URI(s): https://drive.google.com/open?id=1YhNpquVz9YqU8PODkL7EiTn2NOTh7eYU

Auto-detect schema: true
Ignore unknown values: false
Source format: NEWLINE_DELIMITED_JSON
Compression: 

Connection ID:

7. Check Dataset Permissions: - Yes, absolutely. It's my dataset. In fact I am the owner of every dataset under the Project level.

[ms4446]

Based on the detailed information you've provided, there are a few key points to consider that might be causing the issue with saving results to a BigQuery table:

1. IAM Permissions:

   • Your IAM permissions seem comprehensive, but they don't explicitly include bigquery.tables.create or bigquery.tables.update. These permissions are crucial for creating new tables or updating existing ones. It's worth checking if these permissions are included in any of the roles you have.

2. Shared Queries vs. Project Queries:

   • The fact that the query under "Shared queries" is not working while a "Project query" does, suggests there might be a difference in how permissions are applied to these categories. Shared queries might have different access controls or dependencies that are not immediately apparent.

3. Quotas and Limits:

   • The "Current usage > 90%" warning is significant. If you're nearing a quota limit, it could affect your ability to perform certain operations. While this might not directly cause the permission error, it's an area worth exploring. You should check your BigQuery quotas in the Google Cloud Console to see if any limits are being approached or exceeded.

4. Complexity of the Query:

   • The query you're running is quite complex, especially with the UNION ALL for 31 days and the processing of JSON data. This complexity might be causing issues, particularly if there are resource limitations or specific constraints on the dataset or table you're querying.

5. JSON Data and Table Structure:

   • Since your data is in JSON format with repeatable sections, ensure that the schema of your destination table aligns correctly with the output of your query. Mismatches in schema can sometimes cause issues in writing data to tables.

6. Dataset Permissions:

   • While you have confirmed that you have the necessary permissions on the dataset, it's still worth double-checking any dataset-specific permissions or constraints that might be affecting your ability to write to a new table.

Given these points, here are some steps to take:

• Review and Adjust IAM Permissions: Ensure that your role includes permissions for creating and updating tables.
• Investigate Shared Query Permissions: Look into any specific permissions or settings that apply to shared queries.
• Check Quotas: Review your BigQuery quotas in the Google Cloud Console to ensure you're not hitting any limits.
• Simplify the Query: Try simplifying the query or breaking it down into smaller parts to see if that resolves the issue.
• Align Schemas: Make sure the schema of your destination table matches the output of your query.
• Consult Google Cloud Support: If the issue persists, consider reaching out to Google Cloud Support for more personalized assistance.

[mcancell]

I will recheck everything, as you suggest. Btw, yes the UNION is complex. But even when I dont union tables 2-31, I still get the error. So it's not the UNION.

[mcancell]

Guess what. When I wrap the query in a CREATE Table, the table gets created no problem:

CREATE OR REPLACE TABLE
    XT_Web_Logs.qt_Ford_Month (
      datePublished DATE,
      count         INT64,    --1
      `completed-in`INT64,    --2
      country       STRING,   --3
      `language`    STRING,   --4
      entityTags    STRING,  --5
      modelTags     STRING,   --6
      makeTags      STRING,   --7
      sa            STRING,   --8
      messageType   STRING,   --9
      classTags     STRING,   --10
      published     TIMESTAMP,--11
      author        STRING,   --12
      content       STRING,   --13
      link          STRING,   --14
      queue         STRING,   --15
      userTags      STRING,   --16
      title         STRING,   --17
      dataSource    STRING,   --18
      threadId      STRING,   --19
      dataCategory  STRING,   --20
      id            STRING,    --21
      min_Week      DATE,
      max_Week      DATE
    )
  AS (
  WITH 
  # All Days 
    Month AS...

[ms4446 · Accepted solution]

It's good to hear that you were able to create a new table using the CREATE OR REPLACE TABLE statement with your query. This indicates that you do have the necessary permissions to create and write to a table in BigQuery. The issue seems to be specific to the way you were initially trying to save the query results to a table.

Given this new information, here are a few additional considerations:

1. Query Execution Context:

   • When you use CREATE OR REPLACE TABLE as part of your query, it directly creates or replaces a table with the result set of the query. This is a different operation compared to running a query and then saving the results to a table. The former is a DDL (Data Definition Language) operation, while the latter is more of a DML (Data Manipulation Language) operation followed by a write operation.

2. Permissions for Different Operations:

   • The fact that the table creation works fine suggests that your permissions are correctly set for creating and writing to tables. The issue might be with the specific method or interface you were using to save the results initially.

3. Interface or Tool-Specific Issues:

   • If you were using a specific interface or tool (like the BigQuery web UI or a third-party tool like Coginiti) to run the query and then save the results, there might be some limitations or issues with that specific process. It's worth trying different methods (like using the BigQuery CLI or another tool) to see if the issue persists.

4. Query Complexity:

   • Complex queries, especially those involving large datasets or complicated transformations, might encounter issues when being saved directly to a table through certain interfaces. The CREATE OR REPLACE TABLE approach might be more robust in handling such complexities.

5. Next Steps:

   • If the CREATE OR REPLACE TABLE approach works for your needs, it might be a good workaround for the issue you're facing. However, if you need to use the original method (running a query and then saving the results), you might want to explore different tools or interfaces, or even reach out to Google Cloud Support for more specific guidance.

While the root cause of the initial issue isn't entirely clear, the successful creation of a table using a different method suggests that it might be related to the specific way you were trying to save the results initially. Exploring alternative methods or tools could be a practical next step.

[mcancell]

Thanks. Btw, I was just using the BQ Web UI "Save Results" to a BQ Table.  That's how I got/get the Error 7. Funny thing is that when I Save Results to a Sheet, it works fine. I have never encountered this before and frankly dont know what's going on cause even with no union, e.g., 1 table, it still fails. I guess (I hope) I can still schedule the current query version (with the CREATE Table) to run per schedule. I assume I would just not add a Table name to save results.  I do this type of thing when I train ML models, so I expect that should be ok. 

[ms4446 · Accepted solution]

Here are a few suggestions:

1. Check for UI-Specific Limitations or Bugs:

   • Sometimes, web interfaces have limitations or bugs that are not present in the underlying service. It's possible that the BigQuery Web UI has a specific issue with saving results from complex queries.

2. Use CREATE TABLE as a Workaround:

   • Since the CREATE TABLE approach works, you can use it as a workaround. If you need to regularly update the table with new data, you can modify the query to use CREATE OR REPLACE TABLE, which will overwrite the existing table with new data.

3. Scheduling the Query:

   • When scheduling the query, you typically don't need to specify an output table if your query already includes a CREATE TABLE or CREATE OR REPLACE TABLE statement. The query itself will handle the creation and filling of the table.

4. Contact Google Cloud Support:

   • If this issue is a recurring problem and significantly impacts your workflow, consider reaching out to Google Cloud Support. They might provide more insight into whether this is a known issue or a limitation of the Web UI.