{"id":1123,"date":"2017-11-07T18:47:19","date_gmt":"2017-11-08T01:47:19","guid":{"rendered":"https:\/\/hypotyposis.net\/blog\/?p=1123"},"modified":"2017-11-07T18:47:19","modified_gmt":"2017-11-08T01:47:19","slug":"bigquery-sheets-1","status":"publish","type":"post","link":"https:\/\/hypotyposis.net\/blog\/2017\/11\/07\/bigquery-sheets-1\/","title":{"rendered":"Learning BigQuery + Google Sheets I"},"content":{"rendered":"

I went down the road of studying BigQuery<\/a> and Google Sheets<\/a>, inspired by a suggestion<\/a> from Michael Manoochehri<\/a>:<\/p>\n

\n a powerful and lucrative integration is BigQuery\/Google Sheets via Apps Script. Some of our customers use this combo for report generation once we (Switchboard) provide foundational data in BigQuery\n<\/p><\/blockquote>\n

What is BigQuery? \u00c2\u00a0|\u00c2\u00a0 BigQuery \u00c2\u00a0|\u00c2\u00a0 Google Cloud Platform<\/a>:<\/p>\n

\n What is BigQuery?<\/p>\n

Storing and querying massive datasets can be time consuming and expensive without the right hardware and infrastructure. Google BigQuery is an enterprise data warehouse that solves this problem by enabling super-fast SQL queries using the processing power of Google's infrastructure. Simply move your data into BigQuery and let us handle the hard work. You can control access to both the project and your data based on your business needs, such as giving others the ability to view or query your data.\n<\/p><\/blockquote>\n

What have I done so far and what are the next steps?<\/h1>\n

As a learning exercise, I'm using Apps Script to write out data about all the public datasets into a Google Sheet and creating a visualization of the datasets: BigQuery Learning (public view with Google account)<\/a>. Most of the code in the project is borrowed from the sample tutorial code listed BigQuery Service \u00c2\u00a0|\u00c2\u00a0 Apps Script \u00c2\u00a0|\u00c2\u00a0 Google Developers<\/a>. When I run the sample code, whose core functionality is in the SQL query:<\/p>\n

'SELECT TOP(word, 300) AS word, COUNT(*) AS word_count ' +\n      'FROM publicdata:samples.shakespeare WHERE LENGTH(word) > 10;'\n<\/code><\/pre>\n
Roughly translated into plain English, this query says: Compute the 300 most common words whose length is greather than 10 (and the number of times the word occurs) in the public Shakespeare corpus.  The rest of the code then stores that list in a Google Sheet.  Here are the top ten words:<\/p>\n
word    word_count\ncounterfeit 28\nremembrance 24\ncountenance 24\nacquaintance    23\nsatisfaction    20\nentertainment   20\ndispleasure 20\nsovereignty 19\nimagination 19\ndisposition 19\n<\/code><\/pre>\n
Of the many public data sets to play with, I've chosen the Shakespeare data set, not only because it is used in the Google tutorials, but is small (and therefore you're less likely to spend too much money accidentally doing an inefficient query).<\/p>\n
I wrote a non-trivial query on the database to calculate the number of words in each of the corpora: Shakespeare corpora by descending word count<\/a>.<\/p>\n
Does the BigQuery API provide access to saved queries? That is, can list my saved queries, read the content of my queries,  write saved queries and even run them?  (The last function is, on second thoughts, such a big deal since there is already functionality I know in the API to run queries.)<\/p>\n
 \/* list corpora by descending total number of words *\/\nSELECT\n  corpus,\n  corpus_date,\n  SUM(word_count) AS num_words\nFROM\n  publicdata:samples.shakespeare\nGROUP BY\n  corpus, corpus_date\nORDER BY\n  num_words DESC\n<\/code><\/pre>\n
From the query interface, you can see a number of options for what to do with the output of the query, including:<\/p>\n