BigQuery Standard SQL + Looker

  • 28 March 2022
  • 0 replies

Userlevel 5

This content, written by Lloyd Tabb and Erin Franz, was initially posted in Looker Blog on Sep 28, 2016. The content is subject to limited support.

, we were excited to announce our support for Google BigQuery. At Looker, we love learning about advancements in database technology because our architecture fundamentally depends on it. The more flexible and powerful databases become, the more flexible and powerful Looker is.

We were particularly psyched to explore BigQuery because it’s been powering Google’s internal data culture for more than a decade. When Google introduced BigQuery to the public, the first thing that grabbed us was its unique multi-tenant setup, which means that your database essentially never runs out of storage and never gets slow. This is ideal for Looker, as our LookML modeling language is dependent on querying data sources directly.

Another key tenet of Looker’s philosophy is making data available to everyone in an organization, allowing everyone to be their own analyst. BigQuery closely aligns with this philosophy as well, and demonstrates it by making data management easy and accessible for any user. Since BigQuery is a fully managed service, there’s no need for a database administrator and there’s no infrastructure to maintain. Uploading data is trivial and sharing data is as easy as sharing in Google Docs.

At its start, BigQuery was a bit of an “Ugly Duckling”...

Because it was initially built to support Google-sized data, BigQuery was developed to be extremely powerful, but unfortunately not without a few quirks. Features that made BigQuery work with really large datasets - and it worked well - weren’t exactly standard.The SQL used by BigQuery wasn’t entirely familiar, and perhaps a bit “ugly” when compared to the elegance of the SQL standard.

We partnered with Google Cloud to fully support BigQuery anyway, and we’ve seen a number of customers deploy BigQuery and Looker together to much success. But we also assumed that the great team behind BigQuery was only getting started and maybe we’d get to see the original “ugly duckling” transform into something a bit more reflective of its incredible architecture under the hood.

Today we’re excited to see that Google hasn’t disappointed.

BigQuery’s transformation from “Ugly Duckling” into a “Beautiful Swan”

Google recently announced BigQuery support for Standard SQL. This means that users can get the same power from BigQuery, but with a much lower barrier to entry. Transitioning to BigQuery from existing data workflows and systems is now much easier, since the SQL is consistent with other databases.

But that’s not the only new thing BigQuery is rolling out. They’ve also announced:

  • Timezone Support: Ensuring time consistency in reporting
  • Partitioned Table Support: Reducing query complexity and improving query performance
  • Improved Query Optimizer: Including support for complex joins
  • Predicate Pushdown: Allowing for more efficient joining
  • Arrays and Structures: Making BigQuery a leader among relational datastores in querying nested and repeating structures in tables (what many think of as NoSQL or unstructured data). This implementation works great with BigQuery’s scanning architecture.

What does this mean for Looker’s integration with BigQuery?

In short: Looker and BigQuery work better together than ever before.

We’ve worked with Google Cloud Platform to make sure that Looker is ready with full dialect support for BigQuery’s new Standard SQL (in Looker 3.56). In addition, Looker will continue to support the legacy dialect to help customers transition seamlessly standard SQL.

Standard SQL will make writing LookML for BigQuery easier and more flexible. And just like with any other SQL dialect, Looker lets you leverage all the power of BigQuery directly in the modeling layer. Data is never abstracted into another engine for transformation.

Additionally, LookML natively supports BigQuery’s repeating nested objects as if they were joined tables. This means that modeling and querying nested objects is now as easy as querying standard columnar tables.

With a database as big and infinitely scalable as BigQuery, the queries are never slow. Since BigQuery prices by GB scanned, Looker shows the cost of all queries before they are run so you can keep track of your spending and identify areas where your data model needs further optimization.

Get started with Looker and BigQuery

Want to learn more about Looker and BigQuery? Check out our and request a demo!

0 replies

Be the first to reply!