BigQuery - Build a Dynamic Select Statement/Query Based on Fields/Filters

0
votes

I have searched high and low for a solution to the scenario mentioned below, but have had no luck. Heads up - I'm a coding novice, but am currently learning Python as my primary programming language.

In short, I have a table in BigQuery where the first column contains strings, while every other column contains integers. The integer columns are headed using a "month_year" format. I want my users to be able to enter a word into a textbox and select a "from" date and a "to" date, and then run the query so that it returns the data for that word as a trend graph between those dates.

I have this working great in Excel, whereby I have been able to manipulate the query using ActiveX controls linked to cells and then execute it via a data connection. Here's an example of a query that works in Excel:

SELECT que, jan_15, feb_15, mar_15, apr_15, FROM [xxx.xxxx] WHERE (que CONTAINS 'VITAMINS') ORDER BY 1 DESC, LIMIT 1000

So, "VITAMINS" - would be the textbox value and "jan_15, feb_15, mar_15, apr_15" would be the "from" date and "to" date values.

I would like to replicate this using Python. In addition to monthly data, I will also be creating tables for daily data and yearly data, so allowing users to set their from/to dates to search by day, month or year would be great.

Thanks!

1
javascriptjquerypythongoogle-bigquery
Could you please post the code for your best attempt, even if you are a beginner? Thanks. - lrnzcig
Hey @Irnzcig - honestly, I've been going around in circles so currently have no code to show. I only have the Excel workbook that works as needed using VBA. Closest thing I have found thus far is: demo.easyquerybuilder.com/asp-net-ajax - where the query is built in the bottom left-hand corner when the user adds filters/conditions. - Aqil Rouf
Well, I understand, but the point is that your question, as is it now, is really quite broad, it looks a bit like a programming service request... I believe this is not your intention, so in my opinion you should try to narrow it down to a more concrete doubt (or doubts). Even if your code is not complete, for let's say one of trials, what's stopping you? That way, I think you will have more chances to get a good answer. - lrnzcig
dynamic querying is a feature that is being asked by the community for very long time. vote for it here: code.google.com/p/google-bigquery/issues/…. hopefully they will develop it soon. - N.N.

1 Answers

1
votes

You should reconsider your design and separate data retrieval and presentation layer
So, as you are moving from Excel to BigQuery – I strongly recommend you to change your schema.
Based on your example, it can be just few columns:

  • que string
  • year string
  • month string
  • metric integer

In this case query that you need to build is as simple as (just an example)

SELECT que, TIMESTAMP([year] + '-' + [month] + '-01') AS ts, metric
FROM YourTable
WHERE que = 'VITAMINS'
AND TIMESTAMP([year] + '-' + [month] + '-01') 
  BETWEEN TIMESTAMP('2015-01' + '-01') AND TIMESTAMP('2015-04' + '-01')

where 'VITAMINS' , '2015-01' and '2015-04' come from user imput

When above query is built and run off of your client app – you can now use output to build your graph

You can have similar schemas for your daily and early data
But, moreover, if your metric is additive - you do not need to have three separate tables
Just have daily table and then you can just aggregate to needed level for monthly or yearly query