Repository navigation
MySQL backtick quoting #55
Description
Hello!
Thanks for this great project. I have been experimenting with it quite a bit, hoping to get an existing DB-based dashboard working with Google BigQuery. The dashboard is written using a SQL framework, and the queries generated always have fields and table names quoted, like this:
select title, release_year AS year, `locations` from `film_locations` limit 10;
This is valid in MySQL but not BigQuery. We need to somehow remove the backtick quotes, or replace it with BigQuery quotes (which is [field_quoted])
2017/08/17 03:37:11.874755 resultreader.go:123: 0xc42072cd20 bq limit: 10 sel:SELECT title, release_year AS year, `locations` FROM [bigquery-public-data:san_francisco.film_locations] LIMIT 10
2017/08/17 03:37:12.438817 resultreader.go:148: could not run {Location: "query"; Message: "Field '`locations`' not found in table 'bigquery-public-data:san_francisco.film_locations'; did you mean 'locations'?"; Reason: "invalidQuery"}
Metadata
Metadata
Assignees
Labels
No labels
Activity
araddon commentedon Aug 17, 2017
Yes, let me plug in a different dialect writer for that adapter.
https://github.com/araddon/qlbridge/blob/master/expr/dialect.go#L51
Thanks for the report.
araddon commentedon Aug 19, 2017
I think this is fixed now, added tests to verify, let me know. Also would love to hear more of your use/case feedback overall if you want.
derekchan commentedon Aug 21, 2017
@araddon - Thank you for the fix. But how do you build the project from a fresh clone? I've never used Golang before - and my builds is failing. It is complaining about cannot find package "cloud.google.com/go/civil" and "k8s.io/client-go/pkg/api/v1" and so on.
Build logs here : https://circleci.com/gh/derekchan/dataux/1
araddon commentedon Aug 21, 2017
I will push up binary releases, and docker later today, should have done that. I also need to start vendoring as well.
araddon commentedon Aug 21, 2017
Pushed up these, will work on vendoring next. https://github.com/dataux/dataux/releases
derekchan commentedon Aug 22, 2017
I got this working! Found a minor edge case but I am able to work around it. It will fail if we do something like:
araddon commentedon Aug 22, 2017
Thanks for the feedback, ill get tests for that as soon as i can!
derekchan commentedon Aug 22, 2017
And our use-case:
We have a dashboard application written in PHP, which is based on a MVC framework with SQL support. The framework (part of Laravel) supports both MySQL and PostgreSQL, so everything was good when we stayed on MySQL, PgSQL or even AWS Redshift.
We are recently experimenting with BigQuery, but BigQuery has no PHP driver. The closest thing is an ODBC driver. This leaves us with a few options:
Using the official Google PHP API client - This means a almost full re-write of the application, since most of the logic is based on the SQL framework.
Using the ODBC driver and hope it works. But no, it didn't. We got a few Segfaults from the drivers which we didn't investigate further.
Using a middleware (so we are here) that talks a SQL protocol and can translate the SQL syntax somehow.
Our experience so far: