great_expectations: great_expectations : not able to connect to BigQuery instance

@pmariac with BatchRequest it works but using RuntimeBatchRequest it returns that error as well. I think it is because of this: ( this is my code which returns the same error as you, I debug it a little bit and come across that query error)

> runtime_batch_request = RuntimeBatchRequest(
>     datasource_name= datasource_name,
>     data_connector_name="default_runtime_data_connector_name",
>     data_asset_name="default_name", 
>     runtime_parameters={"query": "SELECT * from DATASET_ID.TABLE_NAME LIMIT 10"},
>     batch_identifiers={"default_identifier_name": "default_identifier"},
>     batch_spec_passthrough={
>          "create_temp_table": False
>         , "table_name": "{}".format(table_name)
>         , "schema_name": "{project_id}.{dataset_id}".format(project_id=project_id_spec_passthrough,dataset_id=dataset_id)
>     },  # 

> `google.cloud.bigquery.dbapi.exceptions.DatabaseError: 400 Syntax error: Unexpected keyword SELECT at [2:6]
> -----Query Job SQL Follows-----
> > | . | . | . | . | . |
> 1:SELECT `COLUMN_A` AS `unexpected_values`
> 2:FROM SELECT * from DATASET_ID.TABLE_NAME LIMIT 10
> 3:WHERE `COLUMN_A` IS NULL
> 4: LIMIT @`param_1`
> | . | . | . | . | . |`

I already tried different types of queries, with and without parenthesis, etc but seems not be working because it is expecting a table after the FROM and it is not working for subquerys as well

@talagluck I think it’s worth reopening this issue.

Got the exact same error here.

I have the BigQuery Read Session User permission. I’m auth using

gcloud auth login                                                                                                      
gcloud auth application-default login  

At first, everything is working as expected :

• The “temp” table is created and contains expected items
• The datasource is successfully instantiated and saved
• The Suite is created

But on calling the validator.head() method I got the same error as abtpst:

---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
/tmp/ipykernel_481/3293600047.py in <module>
----> 1 validator.head(n_rows=5, fetch_all=False)

~/.local/lib/python3.8/site-packages/great_expectations/validator/validator.py in head(self, n_rows, domain_kwargs, fetch_all)
   1632             }
   1633 
-> 1634         data: Any = self.get_metric(
   1635             metric=MetricConfiguration(
   1636                 metric_name="table.head",

~/.local/lib/python3.8/site-packages/great_expectations/validator/validator.py in get_metric(self, metric)
    365     def get_metric(self, metric: MetricConfiguration) -> Any:
    366         """return the value of the requested metric."""
--> 367         return self.get_metrics({"_": metric})["_"]
    368 
    369     def graph_validate(

~/.local/lib/python3.8/site-packages/great_expectations/validator/validator.py in get_metrics(self, metrics)
    358             metrics=resolved_metrics,
    359         )
--> 360         return {
    361             metric_name: resolved_metrics[metric_configuration.id]
    362             for (metric_name, metric_configuration) in metrics.items()

~/.local/lib/python3.8/site-packages/great_expectations/validator/validator.py in <dictcomp>(.0)
    359         )
    360         return {
--> 361             metric_name: resolved_metrics[metric_configuration.id]
    362             for (metric_name, metric_configuration) in metrics.items()
    363         }

KeyError: ('table.head', 'batch_id=58be3851fb1261d02a0f5918a9adef39', '04166707abe073177c1dd922d3584468')

Hi @abtpst ! We’ve recently done some work around RuntimeBatchRequest. Would you please confirm whether this is still an issue?

Gotcha - thanks for the update, @FredRodrigues! We currently have a few other RuntimeBatchRequest issues in progress, so I was wondering if this was related, but this feels specific to BigQuery. I’m going to re-open this, and take a look over the next week.

I currently have the exact same issue. At first, it was a problem of permissions, because I have read-only access to that dataset. I solved it by using the create_temp_table to False. Now I am having the same issue as you. I even reproduce the exact same code, only changing the project id, and this error happens on a dataset that has read-only access. The project where I have full access, the code works. Hope this info helps.