Line charts not allowed for BiqQuery SQL queries due to incorrect cols results

[thekantian]

• I am using the Google Chrome (49.0 (64-bit)) browser.
• My computer's OS is Mac OS.
• I'm running Metabase on a server in the Google Cloud Platform.
• My database is Google BigQuery.
• My Metabase version is 0.17.0

I'm writing a custom SQL query in order to use Goole BigQuery's table wildcard functions - https://cloud.google.com/bigquery/query-reference#tablewildcardfunctions. Metabase's UI does not yet support these, so this seems to be my only option for some of my tables.

My data returns properly in the table view and for the charts everything seemed good on the last version (0.16.0). However, when I upgraded to 0.17.0 I seem to have broke some of my charts and got a "We cloudn't create chart based on query" error message". I believe that this is due to column ordering, but I'm actually not sure yet. I've tried a few permutations but I cannot find documentation for what exactly the preferred order is! So I am not sure if that is the problem. Is it possible to document the expected column order requirements for people who write custom SQL? Maybe such a document exists and I just cannot find it?

Thanks!

[camsaul]

Related (maybe) #2546

[thekantian]

@camsaul thanks.

Of course might be related, but not sure - the root of my issue is that I just don't have a reference for what the column order should be (is x-axis value supposed to be first or last column, or have a special name, for example). Once I understand that, I can format my query and test if the result works properly. But right now I'm not even sure if my column names/order are OK...

[camsaul]

@thekantian I'm not sure it's an issue with your column order. In 0.17 we added some new logic to try to prevent people from using line charts when the data was the wrong type. This might just be an issue with this logic not recognizing your data as chartable; we'll look into it more and figure out exactly what's going on.

[agilliland]

I agree that this is likely the same issue as #2546 and the problem is with the charting constraints.

[tlrobinson]

@thekantian Do you mind posting a dump of the cols array in the returned /api/dataset request? You can get it from the network panel of the web inspector, or run dumpQueryBuilderState() in the JavaScript console (just need the data.data.cols array)

[thekantian]

"data":{"columns":["datetime","hit_count"],"cols":[{"name":"datetime","base-type":"DateTimeField"},{"name":"hit_count","base-type":"IntegerField”}]

is what I think you’re asking for? Let me know if not, happy to dig more. Thanks for the help here!

On May 9, 2016, at 1:07 PM, Tom Robinson notifications@github.com wrote:

@thekantian https://github.com/thekantian Do you mind posting a dump of the cols array in the returned /api/dataset request? You can get it from the network panel of the web inspector, or run dumpQueryBuilderState() in the JavaScript console (just need the data.data.cols array)

—
You are receiving this because you were mentioned.
Reply to this email directly or view it on GitHub #2551 (comment)

[tlrobinson]

@thekantian Thanks, that helps.

@camsaul It looks like BQ SQL queries are returning base-type instead of base_type in the cols objects, which would cause the frontend to be unable to determine how to display charts (it's also not formatting timestamps in table view for the same reason). I'm going to assign this to you.

[camsaul]

Fixed by #2577

[thekantian]

Thanks so much!

Will test whenever this makes it to a release.

On May 10, 2016, at 17:15, Cam Saül notifications@github.com wrote:

Closed #2551.

—
You are receiving this because you were mentioned.
Reply to this email directly or view it on GitHub