This post aims to discuss the differences between the above two Google Cloud solutions that is, Google Cloud Spanner vs BigQuery.
We will also discuss their similarities towards the end and assist you in discerning which solution fits your needs. So that you get a good eagle’s eye view of how things stand between the two.
Table of Contents
What is Google Cloud Spanner?
Image Source
Google Cloud Spanner is a relational DBMS that follows NewSQL in its approach. It is well suited for OLTP (online transaction processing) and guarantees ACID (atomicity, consistency, isolation, and durability) compliance. Still, it’s highly scalable like NoSQL systems and offers scale-out architecture. In scale-out architecture, new nodes can be easily added to an existing cluster to spread the storage+compute of data, thereby achieving scalability. Google Cloud Spanner offers the benefits of both NoSQL as well as NewSQL.
At the core of Google Spanner’s consistency is Google’s globally synchronized clock – TrueTime. TrueTime is a highly available and distributed global clock, developed by Google, which is available to all Google cloud offerings/servers. TrueTime guarantees that a new timestamp generated, let’s say T1, will always be greater than any timestamp T2, as long as T2’s generation finished before T1’s generation started. This gives True-time the ability to generate monotonically increasing timestamps, i.e. these timestamps will always be increasing throughout their entire domain.
This, in turn, allows applications to assign unique increasing timestamps to their progressively occurring transactions. Using TrueTime, Google Cloud Spanner assigns a timestamp to every data that is written to it, this timestamp is globally consistent.
So every transaction stored in Spanner will have a unique timestamp attached to it, this timestamp stores the moment in time when Spanner processed this transaction. Using these timestamps, Google Cloud Spanner can provide consistent reads across global locations, without blocking any writes. Also, Google Cloud Spanner can provide stringent concurrency-control guarantees for all its transactions.
For an external user, all transactions seem as if they occurred sequentially, one after the other, even though Google Cloud Spanner might have executed( and/or replicated) them across different geographical locations. So, for the external user, Google Cloud Spanner performs like a single machine database.
Google Cloud Spanner provides a global timestamp order, which the transactions can use for further operations/queries. In earlier database solutions, users had to choose between either high performance+weaker guarantees OR slow performance+stronger guarantees. But, Google Cloud Spanner can provide strong guarantees and high transaction fidelity with faster performance.
Developers need not worry about any clash/race between their different transactions and need to focus only on the correctness of each of their transactions, and their application logic.
What is Google BigQuery?
Image Source
Google BigQuery is an enterprise-level, petabyte-scale data warehouse that allows storing and querying huge volumes of data.
It eases the storage of nested and aggregated data in records while maintaining complex relationships. Google BigQuery also allows for querying the data and creating meaningful visualizations from it.
Google Bigquery allows the creation of real-time and predictive analytics, using its built-in Machine Language and Artificial Intelligence integrations.
A fully managed No-code Data Pipeline platform like Hevo Data helps you integrate and load data from 100+ Different Sources (including 40+ Free Sources) to a Data Warehouse such as Google BigQuery or Destination of your choice in real-time in an effortless manner. Hevo with its minimal learning curve can be set up in just a few minutes allowing the users to load data without having to compromise performance. Its strong integration with umpteenth sources allows users to bring in data of different kinds in a smooth fashion without having to code a single line.
Get Started with Hevo for Free
Check out some of the cool features of Hevo:
- Completely Automated: The Hevo platform can be set up in just a few minutes and requires minimal maintenance.
- Transformations: Hevo provides preload transformations through Python code. It also allows you to run transformation code for each event in the Data Pipelines you set up. Hevo also offers drag and drop transformations like Date and Control Functions, JSON, and Event Manipulation to name a few. These can be configured and tested before putting them to use.
- Connectors: Hevo supports 100+ integrations to SaaS platforms, files, Databases, analytics, and BI tools. It supports various destinations including Google BigQuery, Amazon Redshift, Snowflake Data Warehouses; Amazon S3 Data Lakes; MySQL, SQL Server, TokuDB, DynamoDB, PostgreSQL Databases to name a few.
- Real-Time Data Transfer: Hevo provides real-time data migration, so you can have analysis-ready data always.
- 100% Complete & Accurate Data Transfer: Hevo’s robust infrastructure ensures reliable data transfer with zero data loss.
- Scalable Infrastructure: Hevo has in-built integrations for 100+ sources (including 40+ free sources) that can help you scale your data infrastructure as required.
- 24/7 Live Support: The Hevo team is available round the clock to extend exceptional support to you through chat, email, and support calls.
- Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to the destination schema.
- Live Monitoring: Hevo allows you to monitor the data flow so you can check where your data is at a particular point in time.
Sign up here for a 14-Day Free Trial!
Google Cloud Spanner vs Google BigQuery Key Differences
Here are the 9 major differences that must be noted!
Cloud Spanner vs BigQuery: Use Cases
Google BigQuery supports many different data formats and allows you to analyze the data and create visualizations. Hence, Google BigQuery is more suited for Big Data, Social networking applications, and the likes of IoT. Google BigQuery does not support transactions and does not allow updating of existing records.
On the other hand, Google Cloud Spanner supports OLTP along with scalability and high availability. Hence, Cloud Spanner is more suited for E-commerce systems, Core Banking, Gaming, Telecom, etc.
It allows updating to existing records and appending data to existing tables.
Cloud Spanner vs BigQuery: Architecture
Google BigQuery separates storage and computes in such a way that users need to pay for computing only when they are running their analytics. Google BigQuery Compute is realized by using Dremel ( a query system that combines columnar data layout with multi-level execution trees).
Image Source
Google Cloud Spanner uses the Paxos consensus algorithm to split databases across many nodes, for sharding(partitioning) data. To ensure global consistency, it uses GPS and Atomic clocks, provided by TrueTime.
Image Source
Cloud Spanner vs BigQuery: Access Control
Google BigQuery arranges data in datasets, and permissions are given to own/read/write a dataset(s). It also allows for access controls at column and row levels.
Cloud Spanner on the other hand gives you fine-grained access control. It uses Google Cloud Identity and Access Management (IAM) settings, to provide differential access to individual tables.
Cloud Spanner vs BigQuery: Way of Approach
Cloud Spanner and Google Bigquery attempt to solve different problems.
Since Google Bigquery is a data warehouse with analytics and visualization capabilities, it integrates well with other offerings like Data Studio, Looker, BI engine, Apache big data ecosystem, etc.
Google Cloud Spanner, on the other hand, is a versatile and powerful DBMS, hence it integrates well with offerings like Cloud Storage, Cloud Run, Terraforms Liquibase, etc.
Cloud Spanner vs BigQuery: Location/Geographical Placement
Cloud Spanner is known to replicate your data across multiple geographical locations, and still allow for consistent reads, without blocking any writes. Google Cloud Spanner is offered as a globally distributed DBMS that follows the NewSQL paradigm.
In Google BigQuery, a dataset is created in a geographical location and always exists there only, you can only create a copy in another location. Within a location, Google BigQuery could internally duplicate some data, but that is not something that is divulged or advertised to users.
Cloud Spanner vs BigQuery: Pricing
Since both software solves a different set of problems, they are priced differently.
Google Cloud Spanner will charge you for Network usage, Compute Capacity ( or many node-hours), and some cost for storage. Compute capacity depends on the number of nodes used or a fraction of it ( e.g. 1300 processing units), multiplied by the number of hours it was used. The minimum chargeable time unit is 1 hour, after the initial 1 hour, the billing for compute capacity is prorated.
So, an instance with computes capacity of let’s say 400 processing units that ran for 3.5 hours would be charged as (400 processing units × 3.5 hours) ÷ 1000 processing units, which calculates to 1.4 node hours.
Cost per node per hour is least for US regions (0.90-0.99) and appreciably higher for south America-east ($1.35) and Asia-south($1.26 ). Storage charges are calculated using the volume of storage used by your database+backups, multiplied by the monthly rate. The storage unit here is 1 GB or 2^30 bytes, charged per month, ranging from $0.33-$0.70 per GB per month.
There are no Network or bandwidth charges for Google Cloud Spanner replication or network ingress traffic, but you will be charged for some egress traffic as the network reads. Egress charges between regions within the US (per GB) are $0.01.
Google BigQuery will charge you some amount for storage but the main expenditure is on analysis, i.e. querying, DDL/DML, scripts, etc. that scan the data tables. This charging is based on the total data processed in the columns selected, which in turn depends on the type of data ( blob or integer etc.) in that column. There are some charges for streaming inserts and using their Google BigQuery Storage API. Failed queries and those that return data from cache are not charged.
For querying, you can opt for one of the following 2 charging streams:-
- On-demand pricing: Here, the first 1 TB of query data processed per month is free. After that, the charges depend on the number of bytes processed by each query.
These range from US$ 5.00-9.00 USD per tebibyte, for 1 tebibyte and above, per month per account
- Flat rate pricing: Let’s you buy compute capacity in units of virtual CPUs/slots, with annual/monthly/per the minute commitment.
The commitments are priced starting from $2000-$2920 per 100 slots monthly and reduced to $1700-$2420 per 100 slots yearly.
* Above charges are as advertised by Google, at the time of writing this post.
Streaming inserts are charged depending on the region, ranging from the US $0.01-0.0136 per 200 mebibytes. Storage pricing ranges from US $0.020-0.23 per GB for Active storage and the US $0.010-0.016 per GB for Long-term storage.
Cloud Spanner vs BigQuery: Return on Investment
Return on Investment on such products can be variable, depending on your data and processing needs, the region where services were availed, and how well your analytics succeeded in achieving desired results, like an increase in sales.
Also, there will be a break-even period, only after which you start experiencing tangible results/improvements in your business processes. Based on some veritable experiences shared by actual users, ROI on Google Bigquery could range from 30 percent in the short term, to more than 300 percent in the long term( 3 years or more).
For Google Cloud Spanner, ROI comes from the fact that it scales faster than most other counterparts, and still provides ACID compliance. Also, everything is managed by Google, so Spanner’s total cost of ownership(TCO) is almost 78% lower than on-premises solutions and 37% lower than other cloud SAAS offerings.
Despite the many variables in its pricing, if Google Cloud Spanner suits your needs specifically, the ROI could range from 45% in short term, to more than 200% in the longish term.
Cloud Spanner vs BigQuery: Replication Methods
Replicas of data are created to ensure data availability, serve data via the nearest geographical location, ease of development of Apps and provide a single database experience.
Google Cloud Spanner replicas can be of 3 types:-
- Read-Write Replicas: These can be read and written upon, so they maintain a full copy of your data.
They can vote, can become a leader, and can serve reads.
- Read Replicas maintain a full copy of your data, can only serve reads, cannot vote for the leader, and cannot commit reads.
Read-only replicas can exist only in multi-region locations.
- Witness Replicas do not support reads but participate in voting to commit reads( choose a leader), but they cannot become a leader as there are no writes on them.
Witness replicas exist only in multi-region locations.
Google BigQuery replicates data on its own and remembers a week of change history. Your Google Bigquery dataset can either be in a single geographical location or multi-region locations.
In both cases, Google Bigquery will replicate your datasets in two different cloud zones.
Not only storage but Google Bigquery will also replicate your compute slots across multiple availability zones. This two-pronged replication allows Google Bigquery to ensure High Availability and Durability. In case of Machine-Level Failure, Google Bigquery can continue processing your queries with just a few milliseconds of delay. A soft regional failure will not result in loss of your data, but a hard regional failure could result in loss of data.
Hence, Google Bigquery allows you to create cross-region dataset copies , to protect your data from hard regional failures.
Cloud Spanner vs BigQuery: Programming Languages
Google BigQuery has API client libraries for C#, Go, Java, Node.js, PHP, Python, and Ruby. Also, there are third-party libraries available for Python, R, and Scala.
Cloud Spanner also has APIs for C++, C#, Go, Java, Node.js, PHP, Python, and Ruby. But the PostgreSQL interface is not supported by C#, Node.js, and Ruby.
Some Common Characteristics
Though both the solutions are quite different, they still have some common characteristics.
Cloud-based SAAS offerings Updates and Upgrades to the bare metal infrastructure running them, are transparent and done behind the scenes.
Use modern technology to provide Scalability, Automatics Sharding, Increased Availability through replication, and automatic failure recovery. Both enable Fast Transactions and Fast Querying.
Both use Colossus, the advanced successor to the Google File System (GFS) for storage.
Conclusion
To conclude, Google Cloud Spanner and Google BigQuery are two different cloud SAAS offerings that aim to solve a very different set of problems, and are largely as diverse as apples and oranges.
Hevo Data, a No-code Data Pipeline provides you with a consistent and reliable solution to manage data transfer between a variety of sources and a wide variety of Desired Destinations such as Google BigQuery with a few clicks.
Visit our Website to Explore Hevo
Hevo Data with its strong integration with 100+ Data Sources (including 40+ Free Sources) allows you to not only export data from your desired data sources & load it to the destination of your choice such as Google BigQuery, but also transform & enrich your data to make it analysis-ready so that you can focus on your key business needs and perform insightful analysis using BI tools.
Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You may also have a look at the amazing pricing, which will assist you in selecting the best plan for your requirements.
Share your experience of understanding Google Cloud Spanner vs BigQuery in the comment section below! We would love to hear your thoughts.
Pratik writes about various topics related to data industry who loves creating engaging content on topics like data analytics, machine learning, AI, big data, and business intelligence.