Google made some big announcements at Google Next 2017. At iCharts, we’re most excited about Federated Querying. It’s a powerful new tool that has the potential to change the way organizations store data in the cloud. With Federated Querying, you can query structured and unstructured data at the same time. That means no more Hive or SparkSQL. You get the convenience of SQL, the flexibility of noSQL, and the performance of both, all in one data storage system. For an idea of how it works, and how iCharts ties in, read on, or check out our joint breakout session with Google.

The Google Cloud

Google offers organizations seven ways to store their data, from in-memory storage to data warehouses. Each offering has its own strengths and weaknesses. Two of these offerings are BigTable and BigQuery. BigTable is good for reading and writing small pieces of data really fast, but it’s not as good at analyzing a bunch of data at once. BigQuery is just the opposite. It’s designed mostly for analytics, but it’s slower than BigTable when performing smaller tasks. To put it another way, BigQuery is good for asking big questions, while BigTable is good for asking lots of little questions.

BigQuery is good for asking big questions, while BigTable is good for asking lots of little questions.

These data storage systems work great on their own, but many organizations need the best of both. To help these customers, Google offers a connector that links the two data systems, but it’s not an ideal solution. You still have to write your data in two places. At Google Next 2017, that all changed. Google announced Federated Querying for BigTable and BigQuery. Essentially, you can now use BigTable as the data store for BigQuery. You get the fast read times of BigTable, and the fast analytics of BigQuery, all in one package. Unlike the connector solution, you only have to store your data once. Best of all, you can use iCharts as a visualization layer on top of this data, so you can view data from multiple data sources in one visualization. Google’s approach to Federated Querying, combined with iCharts, offers customers a better way to store, analyze, and understand their data.

What is Google BigTable?

BigTable is Google’s cloud data storage solution that focuses on scalability and low latency. It’s designed to read and write data in under 10 milliseconds. This makes it perfect for applications that need frequent reads and writes. For example, the data behind advertising technology, finance, and the Internet of Things (IoT) can change very fast, but BigTable can keep up.

BigTable was an originator to the noSQL approach. It’s the same database that powers many of Google’s core services like search, maps, and Gmail. It works using a row-based data format. When you ask it a question, it scans each row sequentially. At the same time, it detects patterns in the questions you ask, so it can deliver results faster the more you use it. While this approach is excellent for small reads and writes, it’s slows down when you ask bigger questions. If you need to scan terabytes of information, it can take minutes. For this reason, it’s not popular for analytics.

What is Google BigQuery?

BigQuery is Google’s cloud-based data warehousing solution. Of all of Google’s data storage solutions, BigQuery is meant to hold the most data. With BigQuery, you can query terabytes of data in seconds, all with the convenience of SQL. This makes it perfect for running analytics, or serving as the backbone to an analytics dashboard. It’s also very durable, and it has great availability, so the database almost never goes down. Furthermore, because it uses SQL, you don’t need a developer on hand if you need an answer to a simple question.

BigQuery works using a columnar data format. This structured format makes it easier to scan large amounts of data because the database has a better idea of where your data is. However, BigQuery isn’t as good at handling smaller tasks. Reads and writes take around 2 seconds, compared to around 9 milliseconds with BigTable.

What is Federated Querying?

In essence, Federated Querying is the ability to ask one question (query) of multiple data sources. Google found that many of their customers wanted the analytics capabilities of BigQuery, but the flexibility of BigTable. There wasn’t a single data system that met all of their needs. To serve these customers, they made a connector, but it was a bit of a patchwork solution. Customers still needed to store their data on both systems.

Federated Querying is the ability to ask one question (query) of multiple data sources.

Now, with Federated Querying, Google offers customers the ability to use BigTable as the data storage for BigQuery. In other words, BigQuery can now sit on top of BigTable. This means that you can store your data in a single storage system. It also means you don’t have to partition your tables ahead of time. When you want to run a query, you can choose which rows to query then and there.

The big takeaway is that you can now use SQL to query unstructured data. The combination of BigTable and BigQuery means you can use SQL on BigTable, and make queries on an ad hoc basis. There’s no need to bring in a developer or use a tool like Hive to answer simple questions. Instead, you can use all of the same tools you would use on BigQuery, including iCharts.

 

Where does iCharts come in?

Google’s new Federated Querying approach has some stunning capabilities, but you need a visualization tool to make sense of its results. Without visualizations, you’re just looking at numbers. There’s no use in getting query results in 10 milliseconds if it takes you 10 minutes to figure out what they mean. You could export this data and run it through a program like Excel, but it doesn’t make sense to run your bleeding-edge data through a program that hasn’t really changed since 1985. With iCharts, you can see your Google data in real time. Building visualizations is as easy as picking a dataset, dragging & dropping some parameters, and choosing a chart type.

Get the best of SQL and noSQL in one data system, and visualize all of your data in real time with iCharts.

To try out this functionality on an iCharts pivot table for free, visit http://iCharts.net/GooglePivot