aster.cloud aster.cloud
  • /
  • Platforms
    • Public Cloud
    • On-Premise
    • Hybrid Cloud
    • Data
  • Architecture
    • Design
    • Solutions
    • Enterprise
  • Engineering
    • Automation
    • Software Engineering
    • Project Management
    • DevOps
  • Programming
  • Tools
  • About
  • /
  • Platforms
    • Public Cloud
    • On-Premise
    • Hybrid Cloud
    • Data
  • Architecture
    • Design
    • Solutions
    • Enterprise
  • Engineering
    • Automation
    • Software Engineering
    • Project Management
    • DevOps
  • Programming
  • Tools
  • About
aster.cloud aster.cloud
  • /
  • Platforms
    • Public Cloud
    • On-Premise
    • Hybrid Cloud
    • Data
  • Architecture
    • Design
    • Solutions
    • Enterprise
  • Engineering
    • Automation
    • Software Engineering
    • Project Management
    • DevOps
  • Programming
  • Tools
  • About
  • Multi-Cloud
  • Public Cloud

Zero-ETL Approach To Analytics On Bigtable Data Using BigQuery

  • aster_cloud
  • August 10, 2022
  • 4 minute read

Modern businesses are increasingly relying on real-time insights to stay ahead of their competition. Whether it’s to expedite human decision-making or fully automate decisions, such insights require the ability to run hybrid transactional analytical workloads that often involve multiple data sources.

BigQuery is Google Cloud’s serverless, multi-cloud data warehouse that simplifies analytics by bringing together data from multiple sources. Cloud Bigtable is Google Cloud’s fully-managed, NoSQL database for time-sensitive transactional and analytical workloads.


Partner with aster.cloud
for your next big idea.
Let us know here.


cyberpogo

Customers use Bigtable for a wide range of use cases such as real time fraud detection, recommendations, personalization and time series. Data generated by these use cases has significant business value.

Historically, while it has been possible to use ETL tools like Dataflow to copy data from Bigtable into BigQuery to unlock this value, this approach has several shortcomings, such as data freshness issues and paying twice for the storage of the same data, not to mention having to maintain an ETL pipeline. Considering the fact that many Bigtable customers store hundreds of Terabytes or even Petabytes of data, duplication can be quite costly. Moreover, copying data using daily ETL jobs hinders your ability to derive insights from up-to-date data which can be a significant competitive advantage for your business.

Today, with the General Availability of Bigtable federated queries with BigQuery, you can query data residing in Bigtable via BigQuery faster, without moving or copying the data, in all Google Cloud regions with increased federated query concurrency limits, closing a longstanding gap between operational data and analytics.

During our feature preview period, we heard about two common patterns from our customers.

  1. Enriching Bigtable data with additional attributes from other data sources (using SQL JOIN operator) such as BigQuery tables and other external databases (e.g. CloudSQL, Spanner) or file formats (e.g. CSV, Parquet) supported by BigQuery
  2. Combining hot data in Bigtable with cold data in BigQuery for longitudinal data analysis over long time periods (using SQL UNION operator)
Read More  Google Cloud Armor WAF Rule To Help Mitigate CVE-2021-44228 Apache Log4j Vulnerability

Let’s take a look at how to set up federated queries so BigQuery can access data stored in Bigtable.

Setting up an external table

Suppose you’re storing digital currency transaction logs in Bigtable. You can create an external table to make this data accessible inside BigQuery using a statement like the following.

BigQuery.jpg

External table configuration provides BigQuery with information like column families, whether to return multiple versions for a record, column encoding and data types given Bigtable allows for a flexible schema with 1000s of columns and varying encodings with version history. You can also specify app profiles to reroute these analytical queries to a different cluster and/or track relevant metrics like CPU utilization separately.

Writing a query that accesses the Bigtable data

You can query external tables backed by Bigtable just like any other table in BigQuery.

SELECT *
 FROM `myProject.myDataset.TransactionHistory`

The query will be executed by Bigtable, so you’ll be able to take advantage of Bigtable’s high throughput, low-latency database engine and quickly identify the requested columns and relevant rows within the selected row range even across a petabyte dataset. However note that unbounded queries like the example above could take a long time to execute over large tables so to achieve short response times make sure a rowkey filter is provided as part of the WHERE clause.

SELECT SPLIT(rowkey, '#')[OFFSET(1)] AS TransactionID,
 SPLIT(rowkey, '#')[OFFSET(2)] AS BillingMethod
 FROM `myProject.myDataset.TransactionHistory`
 WHERE rowkey LIKE '2022%'

Query operators not supported by Bigtable will be executed by BigQuery with the required data streamed to BigQuery’s database engine seamlessly.

The external table we created can also take advantage of BigQuery features like JDBC/ODBC drivers and connectors for popular Business Intelligence and data visualization tools such as DataStudio, Looker and Tableau, in addition to AutoML tables for training machine learning models and BigQuery’s Spark connector for data scientists to load data into their model development environments.

Read More  Policy Troubleshooter For BeyondCorp Enterprise Is Now GA!

To use the data in Spark you’ll need to provide a SQL query as shown in the PySpark example below. Note that the code for creating the Spark session is excluded for brevity.

sql = SELECT “”” SELECT rowkey, userid 
 FROM `myProject.myDataset.TransactionHistory` “””

 df = spark.read.format(“bigquery”).load(sql)

In some cases, you may want to create views to reformat the data into flat tables since Bigtable is a NoSQL database that allows for nested data structures.

SELECT rowkey as AccountID, i.timestamp as TransactionTime, 
 i.value as SKU, m.value as Merchant, c.value AS Charge
 FROM `myProject.myDataset.TransactionHistory`,  
 UNNEST(transaction.Item.cell) AS i 
 LEFT JOIN UNNEST(transaction.Merchant.cell) AS m 
 ON m.timestamp = i.timestamp
 LEFT JOIN UNNEST(transaction.Charge.cell) AS c 
 ON m.timestamp = c.timestamp

If your data includes JSON objects embedded in Bigtable cells, you can use BigQuery’s JSON functions to extract the object contents.

You can also use external tables to copy the data over to BigQuery rather than writing ETL jobs. If you’re exporting one day worth of data for the stock symbol GOOGL for some exploratory data analysis, the query might look like the example below.

INSERT INTO `myProject.myDataset.MyBigQueryTable` 
 (symbol, volume, price, timestamp)   
 SELECT 'GOOGL', volume, price, timestamp
 FROM `myProject.myDataset.BigtableView` 
 WHERE rowkey >= 'GOOGL#2022-07-07' 
 AND rowkey < 'GOOGL#2022-07-08'

Learn more

To get started with Bigtable, try it out with a Qwiklab.

You can learn more about Bigtable’s federated queries with BigQuery in the product documentation.

 

 

By Bora Beran Group Product Manager, Cloud Bigtable | Gaurav Saxena Sr Product Manager, BigQuery
Source Google Cloud


Our humans need coffee too! Your support is highly appreciated, thank you!

aster_cloud

Related Topics
  • BigQuery;
  • Bigtable
  • Google Cloud
You May Also Like
View Post
  • Computing
  • Design
  • Engineering
  • Multi-Cloud

Amazing Federated Multicloud Apps

  • June 2, 2023
View Post
  • Data
  • Public Cloud

Cloud Data Loss Prevention’s Sensitive Data Intelligence Service Is Now Available In Security Command Center

  • May 18, 2023
View Post
  • Multi-Cloud
  • Public Cloud
  • Software Engineering

Policy Controller Dashboard: Now Available For All Anthos And GKE Environments

  • May 18, 2023
View Post
  • Containers
  • Public Cloud
  • Software
  • Software Engineering

How To Easily Migrate Your Apps To Containers — Free Deep Dive And Workshop

  • May 18, 2023
View Post
  • Design
  • Engineering
  • Public Cloud

Kubernetes Costs Less, But Less Than What?

  • May 18, 2023
View Post
  • Public Cloud
  • Software

Best Practices For Monetizing Cloud-based Technology And 5G Networks

  • May 15, 2023
View Post
  • Public Cloud
  • Software

New Cloud Deploy Features Make Application Deployment Even More Efficient

  • May 15, 2023
View Post
  • Public Cloud
  • Software
  • Tech

Accelerate Smart Transformation To SAP S/4 HANA With Tata Consultancy Services Precast, Now For Google Cloud

  • May 15, 2023

Stay Connected!
LATEST
  • 1
    Building A Kubernetes Platform: How And Why To Apply Governance And Policy
    • June 4, 2023
  • 2
    Leave, This “United” “Kingdom”, This “Great” “Britain”
    • June 4, 2023
  • 3
    Amazing Federated Multicloud Apps
    • June 2, 2023
  • 4
    What’s The Future Of DevOps? You Tell Us. Take The 2023 Accelerate State Of DevOps Survey
    • June 2, 2023
  • 5
    Resolving Deployment Issues With Ts-node And Azure Development Pipelines
    • June 1, 2023
  • 6
    What To Expect From Apple’s WWDC 2023
    • June 1, 2023
  • 7
    What Is Platform Engineering And Why Adopt It In Your Company?
    • June 1, 2023
  • 8
    Four Steps To Managing Your Cloud Logging Costs On A Budget
    • May 31, 2023
  • 9
    Red Hat Puts Podman Container Management On The Desktop
    • May 30, 2023
  • 10
    The Agile Mindset: A Path to Personal Fulfillment and Growth
    • May 30, 2023
about
Hello World!

We are aster.cloud. We’re created by programmers for programmers.

Our site aims to provide guides, programming tips, reviews, and interesting materials for tech people and those who want to learn in general.

We would like to hear from you.

If you have any feedback, enquiries, or sponsorship request, kindly reach out to us at:

[email protected]
Most Popular
  • 1
    Huawei ICT Competition 2022-2023 Global Final Held In Shenzhen — 146 Teams From 36 Countries And Regions Win Awards
    • May 27, 2023
  • 2
    Huawei OceanStor Pacific Scale-Out Storage Tops IO500 Rankings
    • May 26, 2023
  • 3
    MongoDB And Alibaba Cloud Extend Global Partnership
    • May 25, 2023
  • 4
    Tricentis Launches Quality Engineering Community ShiftSync
    • May 23, 2023
  • 5
    G7 2023: The Real Threat To The World Order Is Hypocrisy.
    • May 27, 2023
  • /
  • Technology
  • Tools
  • About
  • Contact Us

Input your search keywords and press Enter.