Google Developers Blog: bigquery

Code that final mile: from big data analysis to slide presentation

Wednesday, September 26, 2018

Posted by Wesley Chun (@wescpy), Developer Advocate, Google Cloud Google Cloud Platform (GCP) provides infrastructure, serverless products, and APIs that help you build, innovate, and scale. G Suite provides a collection of productivity tools, developer APIs, extensibility frameworks and low-code platforms that let you integrate with G Suite applications, data, and users. While each solution is compelling on its own, users can get more power and flexibility by leveraging both together. In the latest episode of the G Suite Dev Show, I'll show you one example of how you can take advantage of powerful GCP tools right from G Suite applications. BigQuery, for example, can help you surface valuable insight from massive amounts of data. However, regardless of "the tech" you use, you still have to justify and present your findings to management, right? You've already completed the big data analysis part, so why not go that final mile and tap into G Suite for its strengths? In the sample app covered in the video, we show you how to go from big data analysis all the way to an "exec-ready" presentation. The sample application is meant to give you an idea of what's possible. While the video walks through the code a bit more, let's give all of you a high-level overview here. Google Apps Script is a G Suite serverless development platform that provides straightforward access to G Suite APIs as well as some GCP tools such as BigQuery. The first part of our app, the runQuery() function, issues a query to BigQuery from Apps Script then connects to Google Sheets to store the results into a new Sheet (note we left out CONSTANT variable definitions for brevity): function runQuery() { // make BigQuery request var request = {query: BQ_QUERY}; var queryResults = BigQuery.Jobs.query(request, PROJECT_ID); var jobId = queryResults.jobReference.jobId; queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId); var rows = queryResults.rows; // put results into a 2D array var data = new Array(rows.length); for (var i = 0; i < rows.length; i++) { var cols = rows[i].f; data[i] = new Array(cols.length); for (var j = 0; j < cols.length; j++) { data[i][j] = cols[j].v; } } // put array data into new Sheet var spreadsheet = SpreadsheetApp.create(QUERY_NAME); var sheet = spreadsheet.getActiveSheet(); var headers = queryResults.schema.fields; sheet.appendRow(headers); // header row sheet.getRange(START_ROW, START_COL, rows.length, headers.length).setValues(data); // return Sheet object for later use return spreadsheet; } It returns a handle to the new Google Sheet which we can then pass on to the next component: using Google Sheets to generate a Chart from the BigQuery data. Again leaving out the CONSTANTs, we have the 2nd part of our app, the createColumnChart() function: function createColumnChart(spreadsheet) { // create & put chart on 1st Sheet var sheet = spreadsheet.getSheets()[0]; var chart = sheet.newChart() .setChartType(Charts.ChartType.COLUMN) .addRange(sheet.getRange(START_CELL + ':' + END_CELL)) .setPosition(START_ROW, START_COL, OFFSET, OFFSET) .build(); sheet.insertChart(chart); // return Chart object for later use return chart; } The chart is returned by createColumnChart() so we can use that plus the Sheets object to build the desired slide presentation from Apps Script with Google Slides in the 3rd part of our app, the createSlidePresentation() function: function createSlidePresentation(spreadsheet, chart) { // create new deck & add title+subtitle var deck = SlidesApp.create(QUERY_NAME); var [title, subtitle] = deck.getSlides()[0].getPageElements(); title.asShape().getText().setText(QUERY_NAME); subtitle.asShape().getText().setText('via GCP and G Suite APIs:\n' + 'Google Apps Script, BigQuery, Sheets, Slides'); // add new slide and insert empty table var tableSlide = deck.appendSlide(SlidesApp.PredefinedLayout.BLANK); var sheetValues = spreadsheet.getSheets()[0].getRange( START_CELL + ':' + END_CELL).getValues(); var table = tableSlide.insertTable(sheetValues.length, sheetValues[0].length); // populate table with data in Sheets for (var i = 0; i < sheetValues.length; i++) { for (var j = 0; j < sheetValues[0].length; j++) { table.getCell(i, j).getText().setText(String(sheetValues[i][j])); } } // add new slide and add Sheets chart to it var chartSlide = deck.appendSlide(SlidesApp.PredefinedLayout.BLANK); chartSlide.insertSheetsChart(chart); // return Presentation object for later use return deck; } Finally, we need a driver application that calls all three one after another, the createColumnChart() function: function createBigQueryPresentation() { var spreadsheet = runQuery(); var chart = createColumnChart(spreadsheet); var deck = createSlidePresentation(spreadsheet, chart); } We left out some detail in the code above but hope this pseudocode helps kickstart your own project. Seeking a guided tutorial to building this app one step-at-a-time? Do our codelab at g.co/codelabs/bigquery-sheets-slides. Alternatively, go see all the code by hitting our GitHub repo at github.com/googlecodelabs/bigquery-sheets-slides. After executing the app successfully, you'll see the fruits of your big data analysis captured in a presentable way in a Google Slides deck:

This isn't the end of the story as this is just one example of how you can leverage both platforms from Google Cloud. In fact, this was one of two sample apps featured in our Cloud NEXT '18 session this summer exploring interoperability between GCP & G Suite which you can watch here: Stay tuned as more examples are coming. We hope these videos plus the codelab inspire you to build on your own ideas.

Hangouts Chat alerts & notifications... with asynchronous messages

Thursday, June 28, 2018

Posted by Wesley Chun (@wescpy), Developer Advocate, G Suite While most chatbots respond to user requests in a synchronous way, there are scenarios when bots don't perform actions based on an explicit user request, such as for alerts or notifications. In today's DevByte video, I'm going to show you how to send messages asynchronously to rooms or direct messages (DMs) in Hangouts Chat, the team collaboration and communication tool in G Suite. What comes to mind when you think of a bot in a chat room? Perhaps a user wants the last quarter's European sales numbers, or maybe, they want to look up local weather or the next movie showtime. Assuming there's a bot for whatever the request is, a user will either send a direct message (DM) to that bot or @mention the bot from within a chat room. The bot then fields the request (sent to it by the Hangouts Chat service), performs any necessary magic, and responds back to the user in that "space," the generic nomenclature for a room or DM. Our previous DevByte video for the Hangouts Chat bot framework shows developers what bots and the framework are all about as well as how to build one of these types of bots, in both Python and JavaScript. However, recognize that these bots are responding synchronously to a user request. This doesn't suffice when users want to be notified when a long-running background job has completed, when a late bus or train will be arriving soon, or when one of their servers has just gone down. Recognize that such alerts can come from a bot but also perhaps a monitoring application. In the latest episode of the G Suite Dev Show, learn how to integrate this functionality in either type of application. From the video, you can see that alerts and notifications are "out-of-band" messages, meaning they can come in at any time. The Hangouts Chat bot framework provides several ways to send asynchronous messages to a room or DM, generically referred to as a "space." The first is the HTTP-based REST API. The other way is using what are known as "incoming webhooks." The REST API is used by bots to send messages into a space. Since a bot will never be a human user, a Google service account is required. Once you create a service account for your Hangouts Chat bot in the developers console, you can download its credentials needed to communicate with the API. Below is a short Python sample snippet that uses the API to send a message asynchronously to a space. from apiclient import discovery from httplib2 import Http from oauth2client.service_account import ServiceAccountCredentials SCOPES = 'https://www.googleapis.com/auth/chat.bot' creds = ServiceAccountCredentials.from_json_keyfile_name( 'svc_acct.json', SCOPES) CHAT = discovery.build('chat', 'v1', http=creds.authorize(Http())) room = 'spaces/<ROOM-or-DM>' message = {'text': 'Hello world!'} CHAT.spaces().messages().create(parent=room, body=message).execute() The alternative to using the API with service accounts is the concept of incoming webhooks. Webhooks are a quick and easy way to send messages into any room or DM without configuring a full bot, i.e., monitoring apps. Webhooks also allow you to integrate your custom workflows, such as when a new customer is added to the corporate CRM (customer relationship management system), as well as others mentioned above. Below is a Python snippet that uses an incoming webhook to communicate into a space asynchronously. import requests import json URL = 'https://chat.googleapis.com/...&thread;_key=T12345' message = {'text': 'Hello world!'} requests.post(URL, data=json.dumps(message)) Since incoming webhooks are merely endpoints you HTTP POST to, you can even use curl to send a message to a Hangouts Chat space from the command-line: curl \ -X POST \ -H 'Content-Type: application/json' \ 'https://chat.googleapis.com/...&thread;_key=T12345' \ -d '{"text": "Hello!"}' To get started, take a look at the Hangouts Chat developer documentation, especially the specific pages linked to above. We hope this video helps you take your bot development skills to the next level by showing you how to send messages to the Hangouts Chat service asynchronously.

Live from the Firebase Dev Summit in Berlin: Firebase, six months after I/O

Monday, November 7, 2016

Posted by Francis Ma, Firebase Product ManagerOriginally posted to the Firebase blogOur goal with Firebase is to help developers build better apps and grow them into successful businesses. Six months ago at Google I/O, we took our well-loved backend-as-a-service (BaaS) and expanded it to 15 features to make it Google’s unified app development platform, available across iOS, Android, and the web.We launched many new features at Google I/O, but our work didn’t stop there. Since then, we’ve learned a lot from you (750,000+ projects created on Firebase to date!) about how you’re using our platform and how we can improve it. Thanks to your feedback, today we’re launching a number of enhancements to Crash Reporting, Analytics, support for game developers and more. For more information on our announcements, tune in to the livestream video from Firebase Dev Summit in Berlin. They’re also listed here:Improve App Quality to Deliver Better User ExperiencesFirebase Crash Reporting comes out of Beta and adds a new feature that helps you diagnose and reproduce app crashes.Often the hardest part about fixing an issue is reproducing it, so we’ve added rich context to each crash to make the process simple. Firebase Crash Reporting now shows Firebase Analytics event data in the logs for each crash. This gives you clarity into the state of your app leading up to an error. Things like which screens of your app were visited are automatically logged with no instrumentation code required. Crash logs will also display any custom events and parameters you explicitly log using Firebase Analytics. Firebase Crash Reporting works for both iOS and Android apps.Glide, a popular live video messaging app, relies on Firebase Crash Reporting to ensure user quality and release agility. “No matter how much effort you put into testing, it will never be as thorough as millions of active users in different locations, experiencing a variety of network conditions and real life situations. Firebase allows us to rapidly gain trust in our new version during phased release, as well as accelerate the process of identifying core issues and providing quick solutions.” - Roi Ginat, Founder, Glide.Firebase Test Lab for Android supports more devices and introduces a free tier.We want to help you deliver high-quality experiences, so testing your app before it goes into the wild is incredibly important. Firebase Test Lab allows you to easily test your app on many physical and virtual devices in the cloud, without writing a single line of test code. Beginning today, developers on the Spark service tier (which is free!) can run five tests per day on physical devices and ten tests per day on virtual devices—with no credit card setup required. We’ve also heard that you want more device options, so we’ve added 11 new popular Android device models to Test Lab, available today.

Make Faster Data Driven Decisions with Firebase Analytics Firebase Analytics now offers live conversion collection, a new integration with Google “Data Studio”, and real-time exporting to BigQuery. We know that your data is most actionable when you can see and process it as quickly as possible. Therefore, we’re announcing a number of features to help you maximize the potential of your analytics events:

Real-time uploading of conversion events

Real-time exporting to BigQuery

DebugView for validation of your analytics instrumentation is currently offered in limited availability and will be made more broadly available later this year

We were happy to give you a sneak preview at the Firebase Dev Summit of a new feature we are now building, StreamView, which will offer a live, dynamic view of your analytics data as it streams in. To further enhance your targeting options, we’ve improved the connection between Firebase Analytics and other Firebase features, such as Dynamic Links and Remote Config. For example, you can now use Dynamic Links on your Facebook business page, and we can identify Facebook as a source in Firebase Analytics reporting. Also, you can now target Remote Config changes by User Properties, in addition to Audiences.

Build Better Games using Firebase Firebase now has a Unity plugin!Game developers are building great apps, and we want Firebase to work for you, too. We’ve built an entirely new plugin for Unity that supports Analytics, the Realtime Database, Authentication, Dynamic Links, Remote Config, Notifications and more. We've also expanded our C++ SDK with Realtime Database support.Integrate Firebase Even Easier with Open-Sourced UI LibraryFirebaseUI is updated to v1.0.FirebaseUI is a library that provides common UI elements when building apps, and it’s a quick way to integrate with Firebase. FirebaseUI 1.0 includes a drop-in UI flow for Firebase Authentication, with common identity providers such as Google, Facebook, and Twitter. FirebaseUI 1.0 also added features such as client-side joins and intersections for the Realtime Database, plus integrations with Glide and SDWebImage that make downloading and displaying images from Firebase Storage a cinch. Follow our progress or contribute to our Android, iOS, and Web components on Github. Learn More via Udacity and Join the Firebase CommunityWe want to provide the best tool for developers, but it’s also important that we give resources and training to help you get more out of the platform. As such, we’ve created a new Udacity course: Firebase in a Weekend! It’s an instructor-led video course to help all developers get up and running with Firebase on iOS and Android, in two days.Finally, to help wrap your head around all our announcements, we’ve created a new demo app. This is an easy way to see how Analytics, Crash Reporting, Test Lab, Notifications, and Remote Config work in a live environment, without having to write a line of code.Helping developers build better apps and successful businesses is at the core of Firebase. We work hard on it every day. We love hearing your feedback and ideas for new features and improvements—and we hope you can see from the length of this post that we take them to heart! Follow us on Twitter, join our Slack channel, participate in our Google Group, and let us know what you think. We’re excited to see what you’ll build next!

Using BigQuery and Firebase Analytics to understand your mobile app

Friday, September 30, 2016

Google Cloud Platform BlogPosted by Sara Robinson, Developer Advocate
announcedGoogle BigQuery How does the BigQuery export work?linking your Firebase project to BigQuery

Diving into the dataone for iOSone for Android—SELECT user_dim.app_info.app_instance_id, user_dim.device_info.device_category, user_dim.device_info.user_default_language, user_dim.device_info.platform_version, user_dim.device_info.device_model, user_dim.geo_info.country, user_dim.geo_info.city, user_dim.app_info.app_version, user_dim.app_info.app_store, user_dim.app_info.app_platform FROM [firebase-analytics-sample-data:ios_dataset.app_events_20160601] User datauser_dim.app_info.app_instance_idunion featureUNION ALLEXACT_COUNT_DISTINCTSELECT user_dim.geo_info.country as country, EXACT_COUNT_DISTINCT( user_dim.app_info.app_instance_id ) as users FROM [firebase-analytics-sample-data:android_dataset.app_events_20160601], [firebase-analytics-sample-data:ios_dataset.app_events_20160601] GROUP BY country ORDER BY users DESCuser_propertiessome user propertiesSELECT user_dim.user_properties.value.value.string_value as language_code, EXACT_COUNT_DISTINCT(user_dim.app_info.app_instance_id) as users, FROM [firebase-analytics-sample-data:android_dataset.app_events_20160601], [firebase-analytics-sample-data:ios_dataset.app_events_20160601] WHERE user_dim.user_properties.key = "language" GROUP BY language_code ORDER BY users DESC Event dataSELECT event_dim.name, COUNT(event_dim.name) as event_count FROM [firebase-analytics-sample-data:android_dataset.app_events_20160601] GROUP BY event_dim.name ORDER BY event_count DESCSELECT event_dim.params.value.int_value as virtual_currency_amt, COUNT(*) as num_times_spent FROM [firebase-analytics-sample-data:android_dataset.app_events_20160601] WHERE event_dim.name = "spend_virtual_currency" AND event_dim.params.key = "value" GROUP BY 1 ORDER BY num_times_spent DESC Building complex queriesTABLE_DATE_RANGESELECT user_dim.geo_info.city, COUNT(user_dim.geo_info.city) as city_count FROM TABLE_DATE_RANGE([firebase-analytics-sample-data:android_dataset.app_events_], DATE_ADD('2016-06-07', -7, 'DAY'), CURRENT_TIMESTAMP()), TABLE_DATE_RANGE([firebase-analytics-sample-data:ios_dataset.app_events_], DATE_ADD('2016-06-07', -7, 'DAY'), CURRENT_TIMESTAMP()) GROUP BY user_dim.geo_info.city ORDER BY city_count DESCSELECT user_dim.app_info.app_platform as appPlatform, user_dim.device_info.device_category as deviceType, COUNT(user_dim.device_info.device_category) AS device_type_count FROM TABLE_DATE_RANGE([firebase-analytics-sample-data:android_dataset.app_events_], DATE_ADD('2016-06-07', -7, 'DAY'), CURRENT_TIMESTAMP()), TABLE_DATE_RANGE([firebase-analytics-sample-data:ios_dataset.app_events_], DATE_ADD('2016-06-07', -7, 'DAY'), CURRENT_TIMESTAMP()) GROUP BY 1,2 ORDER BY device_type_count DESCPARTITION BYEXACT_COUNT_DISTINCTuser_dim.user_idSELECT STRFTIME_UTC_USEC(eventTime,"%Y%m%d") as date, appPlatform, eventName, COUNT(*) totalEvents, EXACT_COUNT_DISTINCT(IF(userId IS NOT NULL, userId, fullVisitorid)) as users FROM ( SELECT fullVisitorid, openTimestamp, FORMAT_UTC_USEC(openTimestamp) firstOpenedTime, userIdSet, MAX(userIdSet) OVER(PARTITION BY fullVisitorid) userId, appPlatform, eventTimestamp, FORMAT_UTC_USEC(eventTimestamp) as eventTime, eventName FROM FLATTEN( ( SELECT user_dim.app_info.app_instance_id as fullVisitorid, user_dim.first_open_timestamp_micros as openTimestamp, user_dim.user_properties.value.value.string_value, IF(user_dim.user_properties.key = 'user_id',user_dim.user_properties.value.value.string_value, null) as userIdSet, user_dim.app_info.app_platform as appPlatform, event_dim.timestamp_micros as eventTimestamp, event_dim.name AS eventName, event_dim.params.key, event_dim.params.value.string_value FROM TABLE_DATE_RANGE([firebase-analytics-sample-data:android_dataset.app_events_], DATE_ADD('2016-06-07', -7, 'DAY'), CURRENT_TIMESTAMP()), TABLE_DATE_RANGE([firebase-analytics-sample-data:ios_dataset.app_events_], DATE_ADD('2016-06-07', -7, 'DAY'), CURRENT_TIMESTAMP()) ), user_dim.user_properties) ) GROUP BY date, appPlatform, eventNameexport your Google Analytics data
Visualizing analytics dataGoogle Data Studio

this post What’s next?herethis detailed guideBigQuery reference docsfirebase-analyticsgoogle-bigquerylet me know

Updates to Google BigQuery following Cloud Platform Live

Monday, March 31, 2014

By Felipe Hoffa, Cloud Platform teamCross-posted from the Google Cloud Platform BlogEditor's note: This post is a follow-up to the announcements we made on March 25th at Google Cloud Platform Live.announcedGoogle BigQuery1000x increase in streaming capacitywe announced the ability to stream datacheck out the live demo from the keynoteTABLE_DATE_RANGE()documentation and syntax for table wildcard functionsImproved SQL support and table viewsGoogle Cloud SDK)User-defined metadataJSON parsing functionsJSON_EXTRACTJSON_EXTRACT_SCALARJSONPathSELECT json_extract_scalar( "{'book': { 'category':'fiction', 'title':'Harry Potter'}}", "$.book.category"); Fast parallel exportsexporting data documentationMassive price reductionsprice reductionR has its own connectortutorialPython pandas toovideo we made with PearsonActiveRecord connectorlogs with fluentdFelipe Hoffa is part of the Cloud Platform Team. He'd love to see the world's data accessible for everyone in BigQuery.Posted by Louis Gray, Googler

Geoip geolocation with Google BigQuery

Monday, March 3, 2014

By Felipe Hoffa, Cloud Platform teamCross-posted from the Google Cloud Platform BlogGoogle BigQueryStack Overflowtransform IP addresses into geographical locations in BigQuerySELECT COUNT(*) c, city, countryLabel, NTH(1, latitude) lat, NTH(1, longitude) lng FROM ( SELECT INTEGER(PARSE_IP(contributor_ip)) AS clientIpNum, INTEGER(PARSE_IP(contributor_ip)/(256*256)) AS classB FROM [publicdata:samples.wikipedia] WHERE contributor_ip IS NOT NULL ) AS a JOIN EACH [fh-bigquery:geocode.geolite_city_bq_b2b] AS b ON a.classB = b.classB WHERE a.clientIpNum BETWEEN b.startIpNum AND b.endIpNum AND city != '' GROUP BY city, countryLabel ORDER BY 1 DESC

publicdata:samples.wikipediaTechnical detailsGeoLite City IPv4 made available by MaxMindnewer database availableblockslocationsthe new table with the BigQuery web UIcheck out our site"What is BigQuery"Stack Overflowwww.reddit.com/r/bigquery+Google Cloud Platform#BigQueryThis post includes GeoLite data created by MaxMind, available from http://www.maxmind.com, distributed under the Creative Commons Attribution-ShareAlike 3.0 Unported License.Felipe Hoffa is part of the Cloud Platform Team. He'd love to see the world's data accessible for everyone in BigQuery.Posted by Scott Knaster, Editor

Visualizing airport delay correlations with Google BigQuery and Maps API

Thursday, September 26, 2013

Thanks to BigQuery, running complex correlations over billions or trillions of attributes is surprisingly easy. But don’t forget if your data does have some spatial component, a quick mapping visual can add some great context to your results.

Josh Livni works with maps at Google, where he helps developers tell compelling stories using the Google Maps APIs. As you read this, he's probably writing code, thinking about snowboarding, or both.

Posted by Scott Knaster, Editor

By Josh Livni, Maps Developer Relations TeamCross-posted with the Google Geo Developers BlogLast week Felipe and Michael from the Google Cloud Platform Developer Relations team released a video demonstrating the new Pearson correlation analysis available in Google BigQuery. Their example used more than 70 million flight records to find correlations on departure times, which could be used to predict whether your plane would be late. Specifically, you can find out which set of airports best predict your airport’s possible flight delays the following day.As you can see from their code (watch the video for context), they’re using iPython Notebook to work with the BigQuery results, and matplotlib to get a quick visual of results. But one aspect that’s hard to see in these results is the spatial relationship of the airports. Maps to the rescue!My goal was to create a simple visualization, where selecting any airport would highlight which airports you should look at to predict delays the next day. Using the Google Maps JavaScript API I went ahead and did just that:

To create this map, using the same code noted above, we first created a JSON dump of the correlations on departure time for each airport to each other airport. Then, using an article on visualizing earthquakes as a template, I modified the code so that we have a simple dictionary of all airports, keyed by their airport id. Last but not least, I added a function that gets called when an airport marker is clicked in order to restyle all the other airport markers according to their correlation. This method checks the selected season (winter, for example, has different correlations than spring), and highlights correlated markers by setting the color and scale of the circle symbol.https://gist.github.com/jlivni/6602202 Thanks to BigQuery, running complex correlations over billions or trillions of attributes is surprisingly easy. But don’t forget if your data does have some spatial component, a quick mapping visual can add some great context to your results.Josh Livni works with maps at Google, where he helps developers tell compelling stories using the Google Maps APIs. As you read this, he's probably writing code, thinking about snowboarding, or both. Posted by Scott Knaster, Editor

Google BigQuery goes real-time with streaming inserts, time-based queries, and more

Wednesday, September 18, 2013

By Felipe Hoffa, Developer Programs EngineerCross-posted from the Google Cloud Platform BlogGoogle BigQueryJOINGROUP BYReal-time analyticstabledata().insertAll()Using it is as easy as calling the new endpointbody = {"rows":[ {"json": {"column_name":7.7,}} ]} response = bigquery.tabledata().insertAll( projectId=PROJECT_ID, datasetId=DATASET_ID, tableId=TABLE_ID, body=body).execute() Table decorators for recent imported datanew syntax for "table decorators"New window and statistical functionsSUM()COUNT()AVG()MIN()MAX()FIRST_VALUELAST_VALUE()Refer to the previous release announcementhow to find interesting and anomalous patterns in ambient sensorshow to predict tomorrow's flight delays using 70 million flights datasetadds new statistical functionsCOVAR_POP()COVAR_SAMP()STDDEV_POP()STDDEV_SAMP()VAR_POP()VAR_SAMP()BigQuery browser tool: Improved history retrieval

https://bigquery.cloud.google.com/Stack Overflow+Google Cloud Platform#BigQueryFelipe Hoffa is part of the Cloud Platform team. He'd love to see the world's data accessible for everyone in BigQuery.Posted by Scott Knaster, Editor

How Safari Books Online uses Google BigQuery for business intelligence

Wednesday, July 31, 2013

This guest post was written by Daniel Peter, Senior Programmer Analyst at Safari Books Online.Cross-posted from the Google Cloud Platform BlogGoogle BigQuery

the articlePosted by Scott Knaster, Editor

Google BigQuery new features: bigger, faster, smarter

Tuesday, June 11, 2013

By Felipe Hoffa, Cloud Platform teamGoogle BigQueryToday we announced several updates Large results--allow_large_results Analytic functionsadvanced functionswindow functionsData Sensing Lab at Google I/Opercentile_cont()SELECT percentile_cont(0.5) OVER (PARTITION BY room ORDER BY data) AS median, room FROM [io_sensor_data.moscone_io13] WHERE sensortype='temperature' SELECT MAX(median) AS median, room FROM ( SELECT percentile_cont(0.5) OVER (PARTITION BY room ORDER BY data) AS median, room FROM [io_sensor_data.moscone_io13] WHERE sensortype='temperature' ) GROUP BY room dense_rank()SELECT DENSE_RANK() OVER (ORDER BY median) rank, median, room FROM ( SELECT MAX(median) AS median, room FROM ( SELECT percentile_cont(0.5) OVER (PARTITION BY room ORDER BY data) AS median, room FROM [io_sensor_data.moscone_io13] WHERE sensortype='temperature' ) GROUP BY room ) documentationOVER()PARTITION BYORDER BYORDER BYPARTITION BYGROUP EACH BYJOIN EACH BYGROUP BYJOIN BYwe announcedGROUP EACH BYJOIN EACH BY Query caching--use_cache in bquseQueryCache BigQuery Web UI: Query validator, cost estimator, and abandonmentBigQuery UI--dry_run Pricing updatespricing Bigger quotadoubling interactive query quotasGoogle+The BigQuery UI features a collection of public datasets for you to use when trying out these new features. To get started, visit our sign-up page and Quick Start guide. You should take a look at our API docs, and ask questions about BigQuery development on Stack Overflow. Finally, don't forget to give us feedback and join the discussion on our Cloud Platform Developers Google+ page.Felipe Hoffa has recently joined the Cloud Platform team. He'd love to see the world's data accessible for everyone in BigQuery.Posted by Ashleigh Rentz, Editor Emerita

BigQuery gets big new features to make data analysis even easier

Thursday, March 14, 2013

By Michael Manoochehri, Developer Programs Engineer, Cloud PlatformBig JOIN and Big Group AggregationsBig JOIN

/* JOIN EACH example
* Selects the top 10 most edited Wikipedia pages
* of words that appear in works of Shakespeare.
*/

SELECT
TOP(wiki.title, 10), COUNT(*)
FROM
[publicdata:samples.wikipedia] AS wiki
JOIN EACH
[publicdata:samples.shakespeare] AS shakespeare
ON
shakespeare.word = wiki.title;

query referenceNative support for TIMESTAMP data type

/* TIMESTAMP example
* Which hours in the day are the most popular for GitHub actions?
* This query converts github_timeline "created_at" date time
* strings to BigQuery TIMESTAMP, and extracts the hour from each.
*/

SELECT
HOUR(TIMESTAMP(created_at)) AS event_create_hour,
COUNT(*) AS event_count
FROM
[publicdata:samples.github_timeline]
GROUP BY
event_create_hour
ORDER BY
event_count DESC;

query referenceAdd columns to existing BigQuery tablesBigQuery API referenceBigQuery Web UI: Dataset links and dataset sharing notifications

sign up pageQuick Start guideour API docsStack OverflowCloud Platform Developers Google+ pageMichael Manoochehri is a Developer Programs Engineer supporting the Google Cloud Platform. His goal is to help make cloud computing and data analysis universally accessible and useful.Posted by Scott Knaster, Editor

Find sample code and more for Google Cloud Platform, now on GitHub

Tuesday, January 22, 2013

By Julia Ferraioli, Developer Advocate, Google Compute EngineCross-posted from the Google Open Source BlogGitHubApp EngineBigQueryCompute EngineCloud SQLCloud Storageguest book appOAuth 2.0 helpergetting started with Pythonmonitoring your Google Compute Engine instances with App EngineTrick of the trade: to find samples relating to a specific platform, try filtering on the name in the “Find a Repository” text field.pull requestscode reviewsuggestions for samplesJulia Ferraioli is a Developer Advocate for Google Compute Engine, based in Seattle. She helps developers harness the power of Google's infrastructure to tackle their computationally intensive processes and jobs. She comes from an industrial background in software engineering, and an academic background in machine learning and assistive technology.Posted by Scott Knaster, Editor

Streak brings CRM to the inbox with Google Cloud Platform

Tuesday, October 9, 2012

By Aleem Mawani, Co-Founder of StreakCross-posted with the Google App Engine BlogThis guest post was written by Aleem Mawani, Co-Founder of Streak, a startup alum of Y Combinator, a Silicon Valley incubator. Streak is a CRM tool built into Gmail. In this post, Aleem shares his experience building and scaling their product using Google Cloud Platform.Everyone relies on email to get work done – yet most people use separate applications from their email to help them with various business processes. Streak fixes this problem by letting you do sales, hiring, fundraising, bug tracking, product development, deal flow, project management and almost any other business process right inside Gmail. In this post, I want to illustrate how we have used Google Cloud Platform to build Streak quickly, scalably and with the ability to deeply analyze our data.

We use several Google technologies on the backend of Streak:

App Engine to serve our app.

App Engine Datastore to persist user data.

Memcache to make operations fast.

BigQuery to analyze our logs and power dashboards.

App Engine Search API to let users sift through their data.

Prediction API to machine learn over user data.

Google Translate API to translate our app to over 40 languages.

Our core learning is that you should use the best tool for the job. No one technology will be able to solve all your data storage and access needs. Instead, for each type of functionality, you should use a different service. In our case, we aggressively mirror our data in all the services mentioned above. For example, although the source of truth for our user data is in the App Engine Datastore, we mirror that data in the App Engine Search API so that we can provide full text search, Gmail style, to our users. We also mirror that same data in BigQuery so that we can power internal dashboards.

System Architecture

App Engine - We use App Engine for Java primarily to serve our application to the browser and mobile clients in addition to serving our API. App Engine is the source of truth for all our data, so we aggressively cache using Memcache. We also use Objectify to simplify access to the Datastore, which I highly recommend.

Google Cloud Storage - We mirror all of our Datastore data as well as all our log data in Cloud Storage, which acts as a conduit to other Google cloud services. It lets us archive the data as well as push it to BigQuery and the Prediction API.

BigQuery - Pushing the data into BigQuery allows us to run non-realtime queries that can help generate useful business metrics and slice user data to better understand how our product is getting used. Not only can we run complex queries over our Datastore data but also over all of our log data. This is incredibly powerful for analyzing the request patterns to App Engine. We can answer questions like:

Which requests cost us the most money?

What is the average response time for every URL on our site over the last 3 days?

BigQuery helps us monitor error rates in our application. We process all of our log data with debug statements, as well as something called an “error type” for any request that fails. If it’s a known error, we'll log something sensible, and we log the exception type if we haven’t seen it before. This is beneficial because we built a dashboard that queries BigQuery for the most recent errors in the last hour grouped by error type. Whenever we do a release, we can monitor error rates in the application really easily.

A Streak dashboard powered by BigQuery showing current usage statistics

In order to move the data into Cloud Storage from the Datastore and LogService, we developed an open source library called Mache. It’s a drop-in library that can be configured to automatically push data into BigQuery via Cloud Storage. The data can come from the Datastore or from LogService and is very configurable - feel free to contribute and give us feedback on it!
Google Cloud Platform also makes our application better for our users. We take advantage of the App Engine Search API and again mirror our data there. Users can then query their Streak data using the familiar Gmail full text search syntax, for example, “before:yesterday name:Foo”. Since we also push our data to the Prediction API, we can help users throughout our app by making smart suggestions. In Streak, we train models based on which emails users have categorized into different projects. Then, when users get a new email, we can suggest the most likely box that the email belongs to.

One issue that arises is how to keep all these mirrored data sets in sync. It works differently for each service based on the architecture of the service. Here’s a simple breakdown:

Having these technologies easily available to us has been a huge help for Streak. It makes our products better and helps us understand our users. Streak’s user base grew 30% every week for 4 consecutive months after launch, and we couldn’t have scaled this easily without Google Cloud Platform. To read more details on why Cloud Platform makes sense for our business, check out our case study and our post on the Google Enterprise blog.Aleem Mawani is the co-founder of Streak.com, a CRM tool built into Gmail. Previously, Aleem worked on Google Drive and various ads products at Google. He has a degree from the University of Waterloo in Software engineering and an MBA from Harvard University.Posted by Scott Knaster, Editor