3

I am relatively new to GBQ. I am trying to create a mappping in IICS to copy one view from GBQ to create a table within GBQ. At this point I am not doing any transformations, just a source to target copy. I am getting the below error - [ERROR] The [QUERY] job failed with the error - [Cannot reference a standard SQL view in a legacy SQL query.]

Note that the IICS mapping is showing as valid and upon running the mapping the target table is dynamically getting created in GBQ but the mapping is failing throwing the above error. Please help.

Thanks

Debojit Pal
  • 33
  • 1
  • 3

1 Answers1

2

BigQuery supports two SQL dialects: standard SQL and legacy SQL and they cannot be mixed while can perfectly be running separately and in parallel

So, in your case looks like you are have Query that is written in Legacy SQL and that query references the view written in Standard SQL

The best way to handle this "issue" is to rewrite your query using Standard SQL

See Migrating to Standard SQL for details

Mikhail Berlyant
  • 165,386
  • 8
  • 154
  • 230
  • Read it and weep. This is the bitter truth, particularily if you inherit others' legacy projects. If you're facing the difficult choice to rewrite from scratch or add using standardSQL I don't envy you. Back in the day, people wrote legacySQL, and it worked, so, it's quite possible to keep doing so, but I find that it messes with my head to mix the two, and ultimately it makes me less sharp when writing standardSQL. At any rate, one shouldn't start a new project using legacySQL. – nJGL Sep 25 '20 at 13:12