BigQuery validator malfunction

Question

I'm using the BigQuery web UI and sometimes the validator will display the green tick to show that all is good to go but then the query does not execute even while the validator has approved the current query. Other times it just seems to continuously keep thinking about it while never validating anything and then I don't know how to proceed. I'm very reliant on it as I'm new to SQL so I'm getting stuck a lot when it malfunctions.

I tried to delete the table and recreating it but it just gives me a blank error. Screenshot below.

Can this be caused by bad latency to US servers? I just tested my internet speed and it's looking very good but I am in South Africa. If this is the case, what would the work around be?

The error simply says "Cannot run query" as in attached screenshots. The query looks like this now:

SELECT *,
CASE 
WHEN STORE = 'Somerset Mall' THEN 'Somerset'
WHEN STORE = 'Pavilion 8ta Flagship' THEN 'Pavilion'
WHEN STORE = 'N1 City' THEN 'N1'
WHEN STORE = 'GALLIERIA' THEN 'Galleria'
WHEN STORE = 'KWADUKUZA' THEN 'Stanger'
WHEN STORE = 'Çape Town' THEN 'ÇBD'
WHEN STORE = 'Walmer Park' THEN 'Walmer'
WHEN STORE =  'Canal Walk' THEN 'Canal Walk'
WHEN STORE = 'Cape Gate' THEN 'Çape Gate'
WHEN STORE = 'CAVENDISH' THEN 'Cavendish'
WHEN STORE = 'Kenilworth' THEN 'Kenilworth'
WHEN STORE =  'Table View' THEN 'Table View'
WHEN STORE = 'Old Mutual Pinelands' THEN 'Old Mutual'
WHEN STORE = 'Sea Point' THEN 'Sea Point'
WHEN STORE = 'Knysna' THEN 'Knysna'
WHEN STORE = 'George' THEN 'George'
WHEN STORE = 'Mossel Bay' THEN 'Mossel Bay'
WHEN STORE = 'Hermanus' THEN 'Hermanus'
WHEN STORE = 'Mitchells Plain' THEN 'Mitchells Plain'
WHEN STORE = 'Stellenbosch' THEN  'Stellenbosch'
WHEN STORE = 'Tygervalley' THEN 'Tygervalley'
WHEN STORE = 'Worcester' THEN 'Worcester'
WHEN STORE = 'Gateway' THEN 'Gateway'
WHEN STORE = 'Musgrave' THEN 'Musgrave'
WHEN STORE = 'Pietermaritzburg' THEN 'Pietermaritzburg'
WHEN STORE = 'Richards Bay' THEN 'Richards Bay' 
WHEN STORE = 'ETHEKWENI' THEN 'eThekwini'
WHEN STORE = 'Bluff' THEN 'Bluff' 
WHEN STORE = 'Chatsworth' THEN 'Chatsworth'
WHEN STORE = 'Ballito' THEN 'Ballito'
WHEN STORE = 'Hemmingways 8ta Flagship' THEN 'Hemmingways'
WHEN STORE = 'Baywest' THEN 'Baywest'
WHEN STORE = 'Greenacres' THEN 'Bridge'
WHEN STORE = 'Vincent Park' THEN 'Vincent Park'
WHEN STORE = 'Bloemfontein' THEN 'Bloemfontein'
WHEN STORE = 'Welkom' THEN 'Welkom'
WHEN STORE = 'Kimberley' THEN 'Kimberley' 
ELSE 'NEW QMAN STORE?'
END AS STORE_NAME
FROM `tester-253410.test1.Qman_data`

blank error

screenshot of a failed query (bottom left) while validator is green

screenshot of query beginning

Howdy ... welcome to Stackoverflow. Maybe provide some description of the query or some screen shots of what you are seeing. Without examples of what may be happening, there isn't much to go on. — Kolban
it would be nice if you posted the text of the full query and error received — Felipe Hoffa

Enrique Zetina Enrique Zetina · Accepted Answer · 2019-11-11T20:07:36

I do not think that delete the table fix the issue.

Seems an issue with the BigQuery UI or with your browser, try to Clear cache & cookies.

My suggestion is to try with the command-line tool thru Cloud Shell running interactive and batch query jobs using the CLI setting the --dry_run flag (If set, do not run this job. A valid query will return a mostly empty response with some processing statistics, while an invalid query will return the same error it would if it was not a dry run) to validate your queries.

For example:

bq query \
--use_legacy_sql=false \
--dry_run \
'SELECT
   COUNTRY,
   AIRPORT,
   IATA
 FROM
   `project_id`.dataset.airports
 LIMIT
   1000'

Returning:

Query successfully validated. Assuming the tables are not modified, running this query will process 122 bytes of data.

BigQuery validator malfunction

1 Answers