BigQuery

What’s happening in BigQuery: a new ingest format, data type updates, ML, and query scheduling

BigQuery Series September 2018

This month we released several new features in beta, including query scheduling, new BigQuery ML models and functions, and geospatial types and queries. We also released the ORC ingest format into GA. Let’s take a closer look at these features and what they might mean for you.

Geospatial types and queries in BigQuery GIS beta

For those who work with geospatial datasets, BigQuery GIS is now in beta. Originally announced in August, BigQuery GIS brings new data types and functions directly to the data, making it the only cloud MPP data warehouse to support GIS data types and functions as first class citizens. Using standard SQL, you can conduct GIS analyses at big data scale and benefit from the same computational libraries that power products like Earth Engine, Google Maps and Google Earth. Learn more in the BigQuery GIS documentation or watch this Cloud OnAir webinar.

Language: SQL

  #standardSQL
-- Finds Citi Bike stations with > 30 bikes
SELECT
  ST_GeogPoint(longitude, latitude)  AS WKT,
  num_bikes_available
FROM
  `bigquery-public-data.new_york.citibike_stations`
WHERE num_bikes_available > 30
BigQuery Geo Viz monthly roll-up screenshot

New multi-class logistic regression modeling in BigQuery ML beta

Multi-class logistic regression and automatic class imbalance handling are now fully supported in BigQuery ML Beta. You can use the new multi-class logistic regression model to predict up to 10 possible values, like whether an input is “low value”, “medium-value”, or “high-value”. The model also uses `logistic_reg` without impacting any existing binary classification models. For both binary and multi-class regressions, you can now weight each class manually or automatically, which means we take care of any class imbalances for you. In addition, we’ve added a new function to return a confusion matrix for a given logistic regression model. Learn more in the BigQuery ML documentation. Finally, BigQuery ML is now available in beta, in the Tokyo (asia-northeast1) region.

Schedule queries in BigQuery

Are you tired of running the same query every morning to update your report? If you don’t feel like coding a cron job or running some other tool to create your weekly aggregation table, you can now schedule your queries (in beta) right from the BigQuery Web UI.
BigQuery scheduled queries interface in the legacy UI

Click the Schedule Query button underneath the query you want to schedule. You can choose the schedule it runs on, the destination table, and how you’ll be notified. For now you must use the classic web UI to schedule queries, but in the future we’ll add it to the new UI in the Cloud Console. Learn more in our documentation for scheduling queries.

BigQuery Cost Controls now generally available

BigQuery offers multiple ways to control costs and efficiently maximize your resources. These include the ability to estimate costs before running queries, analyzing audit logs, and more. Now our Cost Control system is generally available, allowing businesses to set customer cost controls using custom quotas. You can learn more in our documentation.

NUMERIC data type now generally available

Many types of data like financial reporting and scientific measurements require high-precision numerical data. The NUMERIC data type, now generally available, lets you work with large integers and floating-point numbers easily. To start using the NUMERIC type, create a table with that data type and import your data.

Conclusion

That’s it for this month! We hope these updates help you analyse your data even more easily. For more information and the complete list of releases, check out our release notes.