Introduction to BigQuery

What is BigQuery? 

  • BigQuery is a service provided by Google Cloud Platform, a suite of products & services that includes application hosting, cloud computing, database services, etc on on Google's scalable infrastructure 
  • BigQuery is Google’s fully managed solution for companies who need a fully-managed and cloud based interactive query service for massive datasets
  • Google BigQuery is an enterprise data warehouse built using BigTable and Google Cloud Platform. 
  • It’s serverless and completely managed.
  • BigQuery works great with all sizes of data, from a 100 row Excel spreadsheet to several Petabytes of data.
  • Most importantly, it can execute a complex query on those data within a few seconds.
  • We need to note before we proceed, BigQuery is not a transactional database. It takes around 2 seconds to run a simple query like ‘SELECT * FROM bigquery-public-data.object LIMIT 10’ on a 100 KB table with 500 rows. Hence, it shouldn’t be thought of as OLTP (Online Transaction Processing) database. BigQuery is for Big Data!
  • BigQuery supports SQL-like query, which makes it user-friendly and beginner friendly.
  • It’s accessible via its web UI, command-line tool, or client library (written in C#, Go, Java, Node.js, PHP, Python, and Ruby).
  • You can also take advantage of its REST APIs and get our job` done by sending a JSON request
  • BigQuery is serverless, highly available, and petabyte scalable service which allows you to execute complex SQL queries quickly.
  • It lets you focus on analysis rather than handling infrastructure.
  • The idea of hardware is completely abstracted and not visible to us, not even as virtual machines.

Now, let’s dive deeper to understand it better. Suppose you are a data scientist (or a startup which analyzes data) and you need to analyze terabytes of data. If you choose a tool like MySQL, the first step before even thinking about any query is to have an infrastructure in place, that can store this magnitude of data.

Designing this setup itself will be a difficult task because you have to figure out what will be the RAM size, DCOS or Kubernetes, and other factors. And if you have streaming data coming, you will need to set up and maintain a Kafka cluster. In BigQuery, all you have to do is a bulk upload of your CSV/JSON file, and you are done. BigQuery handles all the backend for you. If you need streaming data ingestion, you can use Fluentd. Another advantage of this is that you can connect Google Analytics with BigQuery seamlessly.

Why BigQuery?

  •  Service for interactive analysis of massive datasets (TBs) 
    • Query billions of rows: seconds to write, seconds to return 
    • Uses a SQL-style query syntax 
    • It's a service, can be accessed by a API 
  • Reliable and Secure
    • Replicated across multiple sites 
    • Secured through Access Control Lists
  • Scalable
    • Store hundreds of terabytes
    • Pay only for what you use 
  • Fast (really)
    • Run ad hoc queries on multi-terabyte data sets in seconds

Comments

Post a Comment

Popular posts from this blog

BigQuery Execution Details

Image
  How to use BigQuery execution plans to optimize query performance BigQuery breaks down your SQL into a series of execution steps (stages) and creates a query execution tree. The query execution tree consists of a series of leaf nodes and the master node. The execution plan is an ideal tool to understand how the query is turned into series of work units and it provides valuable insights on where the query is spending so much time. Finding the execution plan for your query To find the execution plan, Once you execute the query in the WebUI, Navigate to the the preview panel (The area right below the query editor), And click on the fourth tab "Execution details" to see the query plan To check the execution plan for older queries, you can navigate and find the query that you ran in the past using the "Query History" window and once you find the query, click on query to expand, and then click on "Open query in editor", Now you should see the "Execution d...
Read more

BigQuery Organisation

Image
BigQuery is structured as a hierarchy with 4 levels:  Projects: Top-level containers in the Google Cloud Platform that store the data  Datasets: Within projects, datasets hold one or more tables of data  Tables: Within datasets, tables are row-column structures that hold actual data  Jobs: The tasks you are performing on the data, such as running queries, loading data, and exporting data Projects  Projects are the top-level containers that store the data  Within the project, you can configure settings, permissions, and other metadata that describe your applications  Each project has a name, ID, and number that you’ll use as identifiers  When billing is enabled, each project is associated with one billing account but multiple projects can be billed to the same account Datasets  Datasets allow you to organize and control access to your tables  All tables must belong to a dataset. You must create a dataset before loading data into BigQuery...
Read more

BigQuery Time Travel (Snapshot decorator)

  BigQuery Time Travel and Undelete using Snapshot decorator What is Time Travel in BigQuery With Time travel, You can access the data in the table from any point (eg. any particular second) within the last seven days, even if the data has been deleted. You can use time travel to query data that was updated or deleted, restore a table that was deleted, or restore a table that expired. Better yet, there is no additional cost to enable this feature and there are no maintenance overheads as well How to query a table using Time travel in BigQuery If you have to take a look at the data in a table as of any particular time, all you have to do is add the AS OF Suffix (also known as snapshot decorator) SELECT * FROM table FOR SYSTEM_TIME AS OF '2020-01-01 12:00:00-07:00'; You can also use relative time as shown below: SELECT * FROM `mydataset.mytable` FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR); with Time Travel you can query up to 7 days in the past H...
Read more