Tag Archives: bigquery

Workspace audit log exports in BigQuery now enriched with Drive label metadata

What’s changing

Google Workspace audit logs enable admins to have visibility into activity on their data, such as file shares and downloads, when it occurred, and who within the organization performed the action. The Google Drive audit events include activity on content your users create in Google Docs, Sheets, Slides, as well as the files that your users upload to Drive, such as PDFs and Microsoft Word files. 

Today, we’re excited to announce that for admins who analyze these logs in BigQuery, these events are now enriched with Drive labels metadata. Admins leverage Drive labels to apply descriptive metadata, such as file sensitivity, to Drive items. With the enrichment of label metadata on log events, admins can now focus their analysis on activity occurring on their most important files by filtering on label conditions. 


Getting started 


Rollout pace 

  • This feature is available now. 

Availability 

  • Available to Enterprise Essentials Plus, Enterprise Standard and Enterprise Plus, Education Standard and Education Plus 

Resources 

Google Workspace Updates Weekly Recap – February 2, 2024

3 New updates

Unless otherwise indicated, the features below are available to all Google Workspace customers, and are fully launched or in the process of rolling out. Rollouts should take no more than 15 business days to complete if launching to both Rapid and Scheduled Release at the same time. If not, each stage of rollout should take no more than 15 business days to complete.


New ways to work with filters in Connected Sheets for Looker and Connected Sheets for BigQuery 
  • With Looker: Connected Sheets for Looker users will now be able to see whether Looker-defined filters, always_filters and conditionally_filters, are applied in their Google Sheets. | Rolling out to Rapid Release domains and Scheduled Release domains now. | Available to all Google Workspace customers, Google Workspace Individual subscribers, and users with personal Google accounts. | Learn more about always_filter and conditionally_filter in Looker. 
  • With BigQuery: Connected Sheets for BigQuery users can now right-click any value in the Connected Sheets preview and filter rows in the preview on that specific value. As a result, a new query will be sent to BigQuery to re-fetch the preview. This new option provides an additional way to easily explore data in the Connected Sheets preview for BigQuery. | This is now available to all Google Workspace customers, Google Workspace Individual subscribers, and users with personal Google accounts. | Learn more about sorting & filtering BigQuery data in Google Sheets.

Latest improvement for page setup in Google Docs 
We’re introducing a new menu item within the Format option that enables you to easily switch between pageless and pages mode in Google Docs. | Rollout to Rapid Release domains is complete; launch to Scheduled Release domains planned for February 5, 2024. | Available to all Google Workspace customers, Google Workspace Individual subscribers, and users with personal Google accounts. | Learn more about changing a document’s page setup. 
Latest improvement for page setup in Google Docs


Developers can now choose which calendar events to be notified about 
Developers using the Calendar API can now subscribe to specific changes in users’ calendars — this includes event types such as working location, out-of-office, or focus time. This update reduces the amount of notifications down to those that are most relevant to the developer. | Use our Developer Documentation for more information about subscribing to alerts for specific calendar event changes. | Rolling out now to Rapid Release domains and Scheduled Release domains. | The Calendar API is available to all Google Workspace customers, however Calendar features may vary across Google Workspace editions.



Previous announcements

The announcements below were published on the Workspace Updates blog earlier this week. Please refer to the original blog posts for complete details.


For Google Meet Hardware, ‘Auto-Update Expiration (AUE)’ will now be referred to as 'end of Meet support' 
We’re making changes to the terminology that refers to when a Meet hardware device is no longer supported. | This update impacts all Google Workspace customers with Meet hardware devices. | Learn more about 'end of Meet support'.

Local data storage exports your organization's Workspace data into the geographic location of your choice, launching in beta 
We’re introducing Google Workspace’s new feature, local data storage. This feature allows admins to export their organization’s Workspace data into the geographic location or locations of their choice. | Available to Google Workspace Enterprise Plus customers with Assured Controls add-on only. | Learn more about the local data storage exports beta.

Improving the Google Calendar appointment scheduling experience with new features 
We’re improving upon the current appointment scheduling experience with four new features. | Co-hosts, secondary calendars and delegate access features are available to Google Workspace Business Standard, Business Plus, Enterprise Standard, Enterprise Plus, Education Fundamentals, Education Standard, Education Plus, the Teaching and Learning Upgrade, and Nonprofits only. Check Calendars for availability feature is available to Google Workspace Business Standard, Business Plus, Enterprise Standard, Enterprise Plus, Education Fundamentals, Education Standard, Education Plus, the Teaching and Learning Upgrade, Nonprofits, Google Workspace Individual subscribers, and users with personal Google accounts only. | Learn more about the appointment scheduling features. 

Shareable class templates and classwork in Google Classroom are now generally available 
Last year, we announced a Google Classroom beta that simplifies lesson planning with shareable class templates and classwork. We’re excited to announce that this is now globally available for Google Workspace for Education Plus customers in English, Spanish, Portuguese and Japanese. | Available to Education Plus only. | Learn more about shareable class materials. 

Pin chat messages in Google Meet 
You can now pin messages to the top of the meeting chat. Pinned messages will appear for all users, even if they join the meeting after the message was initially sent. | Learn more about messaging pinning.


Completed rollouts

The features below completed their rollouts to Rapid Release domains, Scheduled Release domains, or both. Please refer to the original blog posts for additional details.

Rapid Release Domains: 

Scheduled Release Domains: 
Rapid and Scheduled Release Domains: 

For a recap of announcements in the past six months, check out What’s new in Google Workspace (recent releases).  

Google Workspace Updates Weekly Recap – January 12, 2024

4 New updates

Unless otherwise indicated, the features below are available to all Google Workspace customers, and are fully launched or in the process of rolling out. Rollouts should take no more than 15 business days to complete if launching to both Rapid and Scheduled Release at the same time. If not, each stage of rollout should take no more than 15 business days to complete.


Version history limits for Apps Script projects 
For all new scripts, you’ll be able to create and save up to 200 versions of your script. If needed, you can permanently delete a script version from the project history page. | This is available now to all Google Workspace customers. | Learn more using our developer documentation on working with Apps Script versions


Share a link to a specific time in a Google Drive video 
We’re adding new functionality to the Drive sharing button that lets you share timestamped links to specific parts of a video. On web, simply navigate to drive.google.com > find and open a video file > play the video (you can pause the playback before performing the following steps) > select the dropdown on the “Share” button in the top-right corner > select “Copy link to this time” > send the link. | Rolling out now to Rapid Release and Scheduled Release domains. | Available to all Google Workspace customers and users with personal Google Accounts. | Learn more about copying a specific time in the video
Share a link to a specific time in a Google Drive video

Introducing dropdown options on the sharing button in Google Docs, Sheets, Slides and Drawings We’re adding a new feature that ensures a seamless sharing experience across Workspace. In Google Docs, Sheets, Slides and Drawings, you will now see a dropdown on the Share button that surfaces quick actions, such as pending access requests and the “Copy link” option. | Rolling out now to Rapid Release and Scheduled Release domains. | Available to all Google Workspace customers and users with personal Google Accounts. 
dropdown options on the sharing button

Using functions in Connected Sheets for BigQuery 
Today, Connected Sheets for BigQuery supports 23 Sheets functions, such as AVERAGE and XLOOKUP. However, all of these functions behave somewhat differently than their native counterparts. Thus, to help Connected Sheets users write better functions, we now display context-aware Help Center content in Sheets. The ‘formula help’ shows descriptions for Connected Sheets functions when writing a formula that would query BigQuery, and otherwise shows descriptions of native Sheets functions. | This is available now to all Google Workspace customers and users with personal Google Accounts. | Learn more about the XLOOKUP function.
Using functions in Connected Sheets for BigQuery


Previous announcements

The announcements below were published on the Workspace Updates blog earlier this week. Please refer to the original blog posts for complete details.


Updates to metrics in Google Drive Apps Reports and Reports API 
We’re making some updates to the Google Drive metrics in the Admin Console Apps reports and the Reports API. As a result of these improvements, admins who analyze metrics will have more reporting clarity and can better understand activity trends within their domain. | Learn more about metrics in Drive Apps Reports and Reports API. 

Easily share Google Drive files to Google Calendar meeting attendees 
We’re introducing the option to share any file with all meeting participants on a Google Calendar invite via the sharing dialog within a file. | Learn more about sharing Drive files to Calendar. 

Google Meet is now available on Logitech Android appliances 
Google Meet is now supported on Logitech’s Rally Bar and Rally Bar Mini Android-based appliances for collaboration rooms and spaces of just about any size. After initial setup, admins can easily enroll, manage, and monitor these devices using the Google admin console. Google Meet on Logitech Android-based devices is supported on CollabOS v1.11 as a video conferencing provider | Learn more about Meet on Logitech Android appliances. 

Google Meet hardware devices from Poly now support interoperability with Cisco Webex and Zoom 
We’re expanding the existing interoperability between Google Meet, Cisco Webex, and Zoom to include Android-based Meet hardware devices from Poly. Specifically, these devices include: Poly Studio X30, X50, X52, and X70. | Learn more about support interoperability with Cisco Webex and Zoom.

Extending Trusted Types to Gmail
We’re excited to announce the expansion of Trusted Types to Gmail. This will provide a defense against DOM XSS and further enhances our advanced data protection controls to keep users and data safe across more of the apps they use everyday. | Learn more about Trusted Types.


Completed rollouts

The features below completed their rollouts to Rapid Release domains, Scheduled Release domains, or both. Please refer to the original blog posts for additional details.

Rapid Release Domains: 

Scheduled Release Domains: 

Rapid and Scheduled Release Domains: 

For a recap of announcements in the past six months, check out What’s new in Google Workspace (recent releases). 

Improving consistency of Gmail data across the Security Investigation Tool and BigQuery

What’s changing 

In August 2022, we announced the movement of the existing Gmail logs in BigQuery to Google Workspace logs and reports in BigQuery, creating a single space to access all of your Google Workspace audit events. 


To further improve this experience, you’ll see all Gmail log events from the Security Investigation Tool (SIT) in your Google Workspace logs and reports in BigQuery. This includes device types and post-delivery actions (for example open, delete, clicked links, and attachment downloads). This change will give admins a more complete picture of Gmail activity happening in their domain, and bring consistency across reporting tools. 


Use the table below to familiarize yourself with the newly added columns — complete details can be found in our Help Center.


List of newly added columns

Description

event_info.mail_event_type

The event type corresponds to the Event attribute in Gmail log events in Security Investigation Tool.

event_info.client_context.client_type

The type of client or device where the action occurred, including: WEB, IOS, ANDROID, IMAP, POP3, and API.

event_info.client_context.session_context.delegate_user_email

Email address of the delegated user who performed the action on the account owner's behalf.

message_info.attachment.file_name

File attachment name.

message_info.post_delivery_info

Information about the post-delivery event.

message_info.post_delivery_info.action_type

Post-delivery action type.


message_info.post_delivery_info.interaction

Information about the user's interaction with message links, Drive items, or attachments.

message_info.post_delivery_info.interaction.link_url

The URL associated with the interaction, which is set only for link click interactions.

message_info.post_delivery_info.interaction.drive_id

The unique ID of the Google Drive item associated with the interaction. This ID is used to access the item in Drive.

message_info.post_delivery_info.interaction.attachment

The target attachments of the interaction, which are set only for attachment interactions.

message_info.post_delivery_info.interaction.attachment.file_extension_type

File extension (not MIME part type), not including the period.

message_info.post_delivery_info.interaction.attachment.malware_family

Malware type, if malware is detected during message handling.

message_info.post_delivery_info.interaction.attachment.file_name

Attachment file name.

message_info.post_delivery_info.interaction.attachment.sha256

SHA256 hash of the attachment.


Getting started


Rollout pace


Availability

  • Available to Google Workspace Enterprise Essentials, Enterprise Standard, Enterprise Plus, Education Plus, Education Standard 
  • Not available to Google Workspace Essentials, Business Starter, Business Standard, Business Plus, Frontline, and Nonprofits, as well as legacy G Suite Basic and Business customers

Resources

 

Unified experience for Gmail logs in BigQuery, configure your existing Gmail logs to route to Workspace logs

What’s changing 

In the coming months, we will move the location of the existing Gmail logs in BigQuery to Google Workspace logs and reports in BigQuery. This change will only impact existing Google Workspace customers, who have BigQuery Export enabled. New customers will be guided through setting up a Workspace BigQuery project for Gmail logs. 


While this change is happening, for a brief period of time, Gmail data will be exported to both the Gmail logs and the Workspace logs — existing Google Workspace customers who are impacted will receive an email notification with more information in the coming weeks. 

Gmail logs in BigQuery 



                
 Migrating to Unified Workspace logs in BigQuery







Who’s impacted 

Admins


Why it’s important 

This change will create a single space to access all of your Google Workspace audit events and eliminate the need to switch between various sources of truth. 


Once this merger is completed, you will not be able to use Gmail logs in BigQuery in the Admin console. Visit the Help Center for more information, including detailed step-by-step instructions on handling this change. 


Additional details

Temporary dual export period and BigQuery storage requirements 
Once Gmail logs are set to be exported to Workspace Logs, this data will temporarily exported to two destinations: 
  • Your previous, Gmail-only BigQuery project 
  • Your new or existing Workspace BigQuery project, which stores data for all Google Workspace applications 

This will also impact your BigQuery quotas limits and how often they have to refresh, as well as the cost associated with exporting logs to BigQuery. To avoid impact on your quotas and duplicative data, we strongly recommend turning off Gmail log exports to your Gmail-only BigQuery projects


Getting started 


Rollout pace 


Availability 

  • Available to Google Workspace Enterprise Essentials, Enterprise Standard, Enterprise Plus, Education Plus, Education Standard 
  • Not available to Google Workspace Essentials, Business Starter, Business Standard, Business Plus, Frontline, and Nonprofits, as well as legacy G Suite Basic and Business customers 

Resources 

Logica: organizing your data queries, making them universally reusable and fun

We present Logica, a novel open source Logic Programming language. A successor to Yedalog (a language developed at Google earlier) it is a Datalog-like logic programming language. Logica code compiles to SQL and runs on Google BigQuery (with experimental support for PostgreSQL and SQLite), but it is much more concise and supports the clean and reusable abstraction mechanisms that SQL lacks. It supports modules and imports, it can be used from an interactive Python notebook and it even makes testing your queries natural and easy.

“Data is the new oil”, they say, and SQL is so far the lingua franca for working with data. When SQL (or “Structured English Query Language”, as it was first named) was invented in the 1970s, its authors might not have imagined the popularity that it would reach half a century later. Today, systems ranging from tiny smart watch applications to enterprise IT solutions, read and write their data using SQL. Even the browser that you are using to read this post now might have a working built-in SQL database in it.

Despite the widespread adoption, SQL is not flawless. Constructing statements from long chains of English words (which are often capitalized to keep the old-fashioned COBOL spirit of the 70s alive!) can be very verbose—a single query spanning hundreds of lines is a routine occurrence. The main flaw of SQL, however, lies in its very limited support for abstraction.

Good programming is about creating small, understandable, reusable pieces of logic that can be tested, given names, and organized into packages which can later be used to construct more useful pieces of logic. SQL resists this workflow. Although you can encapsulate certain repeated computations into views and functions, the syntax and support for these can vary among implementations, the notions of packages and imports are generally nonexistent, and higher-level constructions (e.g. passing a function to a function) are impossible.

This inherent resistance to decomposition of logic into bite-sized pieces is what leads into the contrived, lengthy queries, the copy-pasted chunks of code and, eventually, unmaintainable, unstructured (note the irony) SQL codebases. To make things worse, SQL code is rarely tested, because “testing SQL queries” sounds rather esoteric to most engineers, at best. Because of that, a number of alternative query languages and libraries have been developed. Of those, systems based on logic programming perhaps come the closest to addressing SQL’s limitations.

Logic programming languages solve problems of SQL by using syntax of mathematical propositional logic rather than natural English language. The language of formal logic was designed by mathematicians specifically to make expression of complex statements easier and suits this purpose much better than natural language. Logica extends classical Logic programming syntax further, most notably with aggregation, hence the name, which stands for

Logica = Logic + Aggregation.

Let us see how it all works. SQL operates with relations, which are sets of rows. In logic programming the analog of a relation is a predicate. While a predicate is a set of rows, we think of it as a logical condition, which describes the rows of a relation. Here is, for example, the definition of a simple predicate:

MagicNumber(x: 2);

MagicNumber(x: 3);

MagicNumber(x: 5);

The definition claims that the condition MagicNumber(x) must hold when X is precisely either 2, 3, or 5. That means, if we were to query this predicate (i.e. request all values of X that satisfy it), the output should be a “relation” with a single column X and rows 2, 3, and 5. The SQL equivalent would be:

SELECT 2 AS x

UNION ALL

SELECT 3 AS x

UNION ALL

SELECT 5 AS x;

Rather than listing the individual values, we could have defined the predicate by encoding a logical condition upon X as follows:

MagicNumber(x:) :-

  x in [2, 3, 5];

Now, here is where the magic starts. Firstly, any table in your database is itself already a predicate, so the following definition:

MagicComment(comment_text:) :-

 `comments`(user_id:, comment_text:),

 user_id == 5;

Defines a predicate MagicComment, which includes precisely those comment_text values, which are present in the comments table where user_id == 5. In SQL this would read:

SELECT comment_text FROM comments WHERE user_id = 5;

Observe what happens if we replace the condition “user_id == 5” in our predicate with MagicNumber(x: user_id):

MagicComment(comment_text:) :-

 `comments`(user_id:, comment_text:),

 MagicNumber(x: user_id);

Here, we are querying for comments of users whose ID is one of the “magic numbers” we just defined above. Note how easily we could reuse a previously defined piece of code without having to copy anything around. We could now even extract the MagicNumber to a common module and import it in wherever it is needed:

import my_project.magic.MagicNumber;

As a final example, let us mock the comments table, in a unittest of a query.

import my_project.magic.MagicComment;


MockComments(user_id: 1, comment_text: "Hello");

MockComments(user_id: 2, comment_text: "Logic");

MockComments(user_id: 3, comment_text: "Programming");


MagicCommentTest := MagicComment(`comments`: MockComments);

If we query the MagicComment predicate here, it will not try to read the comments table in the database. Instead, it will use the predicate we just defined, thus letting us verify its correctness by testing the output (it must include two rows “Logic” and “Programming”). Observe how natural and frictionless many of the good programming practices become with Logica, and compare that to what you would have to do to achieve the same using bare SQL.

There is much more to Logica, so make sure you give it a try—chances are, you will love it! Start with this tutorial to learn Logica. Even if you do not end up using it in your next project, learning a new powerful language may open your mind to new ideas and perspectives on data processing and computing in general.

The simple examples above are only a small sample of how concise Logica code can be over SQL for complex queries. In particular, we did not even touch the topic of aggregations in this article. For all of this see examples section of the Logica open source repository.

We also hope that some of the readers consider contributing to Logica development. That’s what open source is all about!

By Konstantin Tretyakov and Evgeny Skvortsov – Logica Open Source Project

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

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.

Machine Learning in Google BigQuery



Google BiqQuery allows interactive analysis of large datasets, making it easy for businesses to share meaningful insights and develop solutions based on customer analytics. However, many of the businesses that are using BigQuery aren’t using machine learning to help better understand the data they are generating. This is because data analysts, proficient in SQL, may not have the traditional data science background needed to apply machine learning techniques.

Today we’re announcing BigQuery ML, a capability inside BigQuery that allows data scientists and analysts to build and deploy machine learning models on massive structured or semi-structured datasets. BigQuery ML is a set of simple SQL language extensions which enables users to utilize popular ML capabilities, performing predictive analytics like forecasting sales and creating customer segmentations right at the source, where they already store their data. BigQuery ML additionally sets smart defaults automatically and takes care of data transformation, leading to a seamless and easy to use experience with great results.
When designing the BigQuery ML backend, the team was faced with a dilemma. Transferring large amounts of data from BigQuery servers to special-purpose servers running machine learning algorithms would be time-consuming and would incur an overhead in terms of security and privacy considerations. However, because the core components of gradient descent — an optimization method that is the workhorse of machine learning algorithms — can be implemented using common SQL operations*, we were able to repurpose the existing BigQuery SQL processing engine for BigQuery ML.

Since the BigQuery engine is designed to efficiently scan large datasets rather than randomly draw small samples from them, BigQuery ML is based on the standard (batch) variant of gradient descent rather than the stochastic version. And while stochastic gradient descent is far more common in today’s large-scale machine learning systems, the batch variant has numerous practical advantages.

For example, in-database machine learning systems based on stochastic gradient descent process examples one by one, and can perform poorly when the data is suboptimally ordered. But BigQuery data is often distributed on disk so as to optimize the performance of regular SQL queries, and continually redistributing the data to support stochastic machine learning algorithms would be computationally expensive. In contrast, batch gradient descent is insensitive to the ordering and partitioning of data on disk, thereby completely circumventing this problem. Also, batch methods can be combined with line search techniques from the classical optimization literature, leading to a learning algorithm that is more stable and requires less fine tuning. Using line search with stochastic methods is far trickier. Our implementation also includes support for regularization and preconditioning. For more details, please see our paper.

We hope that you’ll find BigQuery ML useful for many predictive analytics tasks. To try it, visit the BigQuery console and follow the user guide. Creating a model is as simple as:
CREATE MODEL dataset.model_name
OPTIONS(model_type=’linear_reg’, input_label_cols=[‘input_label’])
AS SELECT * FROM input_table;
In the future, we plan to further integrate our gradient descent implementation with BigQuery infrastructure to realize more performance gains. We’re also going to explore other machine learning algorithms that can be easily and efficiently implemented for large-scale problems by leveraging the power of BigQuery.

Acknowledgements
BigQuery ML is the result of a large collaboration across many teams at Google. Key contributors and sponsors include Hossein Ahmadi, Corinna Cortes, Grzegorz Czajkowski, Mingge Deng, Amir Hormati, Abhishek Kashyap, Jing Jing Long, Dan McClary, Chris Meyers, Girishkumar Sabhnani, Vivek Sharma, Jordan Tigani, Chad Verbowski, Jiaxun Wu and Lisa Yin.


* For example, a gradient vector can be computed using the SUM and GROUP BY operators, and the weights of a model can be updated using an INNER JOIN.

Source: Google AI Blog


Hangouts Chat alerts & notifications… with asynchronous messages

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 services 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.

Operation Rosehub

Twelve months ago, a team of 50 Google employees used GitHub to patch the “Apache Commons Collections Deserialization Vulnerability” (or the “Mad Gadget vulnerability” as we call it) in thousands of open source projects. We recently learned why our efforts were so important.

The San Francisco Municipal Transportation Agency had their software systems encrypted and shut down by an avaricious hacker. The hacker used that very same vulnerability, according to reports of the incident. He demanded a Bitcoin ransom from the government. He threatened to leak the private data he stole from San Francisco’s citizens if his ransom wasn’t paid. This was an attack on our most critical public infrastructure; infrastructure which underpins the economy of a major US city.

Mad Gadget is one of the most pernicious vulnerabilities we’ve seen. By merely existing on the Java classpath, seven “gadget” classes in Apache Commons Collections (versions 3.0, 3.1, 3.2, 3.2.1, and 4.0) make object deserialization for the entire JVM process turing complete with an exec function. Since many business applications use object deserialization to send messages across the network, it would be like hiring a bank teller who was trained to hand over all the money in the vault if asked to do so politely, and then entrusting that teller with the key. The only thing that would keep a bank safe in such a circumstance is that most people wouldn’t consider asking such a question.

The announcement of Mad Gadget triggered the cambrian explosion of enterprise security disclosures. Oracle, Cisco, Red Hat, Jenkins, VMWare, IBM, Intel, Adobe, HP and SolarWinds all formally disclosed that they had been impacted by this issue.

But unlike big businesses, open source projects don’t have people on staff to read security advisories all day and instead rely on volunteers to keep them informed. It wasn’t until five months later that a Google employee noticed several prominent open source libraries had not yet heard the bad news. Those projects were still depending on vulnerable versions of Collections. So back in March 2016, she started sending pull requests to those projects updating their code. This was easy to do and usually only required a single line change. With the help of GitHub’s GUI, any individual can make such changes to anyone’s codebase in under a minute. Given how relatively easy the changes seemed, she recruited more colleagues at Google to help the cause. As more work was completed, it was apparent that the problem was bigger than we had initially realized.

For instance, when patching projects like the Spring Framework, it was clear we weren’t just patching Spring but also patching every project that depended on Spring. We were furthermore patching all the projects that depended on those projects and so forth. But even once those users upgraded, they could still be impacted by other dependencies introducing the vulnerable version of Collections. To make matters worse, build systems like Maven can not be relied upon to evict old versions.

This was when we realized the particularly viral nature of Mad Gadget. We came to the conclusion that, in order to improve the health of the global software ecosystem, the old version of Collections should be removed from as many codebases as possible.

We used BigQuery to assess the damage. It allowed us to write a SQL query with regular expressions that searched all the public code on GitHub in a couple minutes.

SELECT pop, repo_name, path
FROM (SELECT F.id as id, repo_name, path
FROM (SELECT id, repo_name, path
FROM [bigquery-public-data:github_repos.files]
WHERE path LIKE '%pom.xml') AS F
JOIN (SELECT id
FROM (SELECT id,content
FROM (SELECT id,content
FROM [bigquery-public-data:github_repos.contents]
WHERE NOT binary)
WHERE content CONTAINS 'commons-collections<')
WHERE content CONTAINS '>3.2.1<') AS C
ON F.id = C.id) AS V
JOIN (SELECT difference.new_sha1 AS id,
COUNT(repo_name) WITHIN RECORD AS pop
FROM FLATTEN([bigquery-public-data:github_repos.commits], difference.new_sha1)) AS P
ON V.id = P.id
ORDER BY pop DESC;


We were alarmed when we discovered 2,600 unique open source projects that still directly referenced insecure versions of Collections. Internally at Google, we have a tool called Rosie that allows developers to make large scale changes to codebases owned by hundreds of different teams. But no such tool existed for GitHub. So we recruited even more engineers from around Google to patch the world’s code the hard way.

Ultimately, security rests within the hands of each developer. However we felt that the severity of the vulnerability and its presence in thousands of open source projects were extenuating circumstances. We recognized that the industry best practices had failed. Action was needed to keep the open source community safe. So rather than simply posting a security advisory asking everyone to address the vulnerability, we formed a task force to update their code for them. That initiative was called Operation Rosehub.

Operation Rosehub was organized from the bottom-up on company-wide mailing lists. Employees volunteered and patches were sent out in a matter of weeks. There was no mandate from management to do this—yet management was supportive. They were happy to see employees spontaneously self-organizing to put their 20% time to good use. Some of those managers even participated themselves.

Patches were sent to many projects, avoiding threats to public security for years to come. However, we were only able to patch open source projects on GitHub that directly referenced vulnerable versions of Collections. Perhaps if the SF Muni software systems had been open source, we would have been able to bring Mad Gadget to their attention too.

Going forward, we believe the best thing to do is to build awareness. We want to draw attention to the fact that the tools now exist for fixing software on a massive scale, and that it works best when that software is open.

In this case, the open source dataset on BigQuery allowed us to identify projects that still needed to be patched. When a vulnerability is discovered, any motivated team or individual who wants to help improve the security of our infrastructure can use these tools to do just that.

By Justine Tunney, Software Engineer on TensorFlow

We’d like to recognize the following people for their contributions to Operation Rosehub: Laetitia Baudoin, Chris Blume, Sven Blumenstein, James Bogosian, Phil Bordelon, Andrew Brampton, Joshua Bruning, Sergio Campamá, Kasey Carrothers, Martin Cochran, Ian Flanigan, Frank Fort, Joshua French, Christian Gils, Christian Gruber, Erik Haugen, Andrew Heiderscheit, David Kernan, Glenn Lewis, Roberto Lublinerman, Stefano Maggiolo, Remigiusz Modrzejewski, Kristian Monsen, Will Morrison, Bharadwaj Parthasarathy, Shawn Pearce, Sebastian Porst, Rodrigo Queiro, Parth Shukla, Max Sills, Josh Simmons, Stephan Somogyi, Benjamin Specht, Ben Stewart, Pascal Terjan, Justine Tunney, Daniel Van Derveer, Shannon VanWagner, and Jennifer Winer.