Biq Query Standard SQL

1

Hi,

I just downloaded Metabase, I setuped a Big Query connection and typed a Big Query standard SQL request in the SQL editor, but I got an error. The same request in Big Query runs fine and a simple select in metabase also runs fine on my DB. So my question is : does Metabase supports requests written with Big Query standard SQL ?
Does Metabase makes direct request to BQ or retrieve & cache the database on the server ?

Thanks !

Rémy

2

Hmm, is this GitHub issue related to what you’re describing?

3

If you include #standardSQL at the top of the query it should work fine.

For now we’re defaulting to legacy sql as that’s what we first shipped with.

4

I’ve also experienced issues when moving to Standard SQL, even with the #standardSQL tag. It might be a local bug though - here’s an example:

This is legacySQL, which works well inside a card:

select round(avg(cnt), 2) as avg_cnt
from (
  select req_id , count(distinct id) as cnt
  from <dataset>.<table>
  where 1=1
  and date(open_date) > date(date_add(current_date(), -1, "YEAR"))
  group by 1
)

Here’s the same query, adapted to standardSQL (works well in the BQ console):

#standardSQL
select round(avg(cnt), 2) as avg_cnt
from (
  select req_id , count(distinct id) as cnt
  from <dataset>.<table>
  where 1=1
  and date(open_date) > date_sub(current_date(), INTERVAL -1 YEAR)
  group by 1
)

Which throws the following error:

Encountered " <ID> "YEAR "" at line 8, column 62. Was expecting: ")" ...

Should I open a ticket on github?

5

Yes, please. Note sure exactly what’s going on here, and it’d be good to get some other eyes on it.