Troubleshooting Dust BigQuery Table Query Issues: Help Needed | Dust Community

Dust Community Icon

Troubleshooting Dust BigQuery Table Query Issues: Help Needed | Dust Community

Abboud Hammour
·
·
Hey Khaled K, while I'm not 100% sure what's happening here, given your last point, have you tried adding in the instructions "when querying table "x" query it in lowercase, to see if it makes a difference?
Abboud Hammour
·
·
Another thought - is this table a view on top of other tables by any chance? In this case, when selecting tables via Query Tables, you also have to select all the tables that the view is built off of
Khaled K
·
·
Hi Abboud Hammour thank you for your answer 1- yes I did rename that table to lowercase : but same resut and 2- you are right about your intuition When i first got that error of Query uses tables that are not allowed , I was indeed connecting a view. I understood that I needed to also include the tables that the view was querying but I didn't want to bias the agent with raw data (as the view is basically unnesting and preprocessing some columns) so I basically created a physical table (with a different name) that does the same job as the view and connected it while removing completely the view from the synced data in the BQ connection. But the agent kept on behaving as if it is still processing a view (thus showing the same error). I did try creating new agents, but they also behaved the same. I am wondering if there is some caching behaviour involved here ? I could try creating a new dataset that would host that physical table and create a new sync.
Abboud Hammour
·
·
Hmm I'm not 100% on caching it's possible I'll have to check, but yes I think for now your plan may be a good bet
Abboud Hammour
·
·
Other than trying a little bit later to see if caching was truly an issue
Khaled K
·
·
Hi Abboud Hammour I'm still having the same error even after trying a few things:
delete the connection and recreate it
connect a new dataset from a new gcp project
use completely different tables
I am wondering what I might be doing wrong here 🤔
added data to the connection
added the connected data to the space
added the data to the agent
my service account has the roles documented here (and I even tried with Admin, but same)
the agent is still able to read the data, infere a schema and calculate a query but I still get that Query uses tables that are not allowed error Did I miss something ?
Loading attachments
Abboud Hammour
·
·
Hmm yes you're right, everything sems ok to me.. I guess only 2 things I can think of:
1.
If you run that same query (copy the query from Tools Inspection when trying to run) in your BQ console via that same service account, does it work?
2.
Is it possible you have a specific type of underlying storage, for example I know when it's Delta Lake tables or a diff. kind of storage there are extra permissions we need to give
Khaled K
·
·
1- yes it does, just tried with the last query the agent calculated (cf. screenshot) 2- I'm only using native BigQuery tables. Some are results of scheduled queries, some are imported from firestore backups. Some are created from loading csv files after scrapping APIs... But they all end-up written in native tables. And again, the SA with least privilege does run the query succesfully
Loading attachments
Khaled K
·
·
and another screenshot with more robust impersonation of Service account (other than just setting the env var)
Loading attachments
⭐1
Abboud Hammour
·
·
Got it, might be a bit out of scope for what I'm able to figure out so will have to see with the team which may take a few days. I'll create a ticket though and let you know in case of any updates
🙏1
Khaled K
·
·
Unserstood, thanks ! I'll stay tuned Also: please feel free to register me as beta tester to any upcoming BigQuery-related feature
🙏1
Khaled K
·
·
Hi Abboud Hammour this just a kind reminder about the ticket, if not already created ^^ have a great day
Abboud Hammour
·
·
Hey Khaled K! Thanks for the reminder. May take a bit of time as there is a bit of a queue but I'mm be tracking it 🙂
🙏1
Ben Gauthier
·
·
Hey Abboud Hammour Any update on this? It's a blocker for us
Sébastien Flory
·
·
Query uses tables that are not allowed
This error is coming from us when we compare the tables used in the query (from an EXPLAIN) and the allowed tables (from your dust setup) to make sure the model doesn't reach out to non allowed tables for some reason.