Google BigQuery Streaming Insert: A Comprehensive Guide

Are your trying to load data to Google BigQuery? Are you struggling with Google BigQuery Streaming Insert? If yes, then this blog will answer all your queries. You can load data into Google BigQuery using various ways, but loading data using Google BigQuery Streaming Insert allows you to insert data in near real-time. In this blog, you will learn about Google BigQuery, prerequisites of Google BigQuery Streaming Insert, its mechanism, limitation and pricing in detail.

Let’s see how this blog is structured for you:

1. Introduction to Google BigQuery
2. Streaming Data into Google BigQuery
3. Prerequisites for Google BigQuery Streaming Insert
4. Mechanism of Google BigQuery Streaming Insert
5. Streaming into Partitioned Tables
6. Limitations of Google BigQuery Streaming Insert
7. Pricing for Google BigQuery Streaming Insert
8. Conclusion

Introduction to Big Query

Google BigQuery is serverless, highly scalable, and cost-effective multi-cloud data warehouse designed for business agility. 

Here are few features of Google BigQuery:

• Google BigQuery allows you to analyze petabytes of data with quick speed and zero operational overhead. 
• You don’t need cluster deployment, virtual machines, setting up of keys or indexes, and software.
• Stream millions of rows per second for real-time analysis.
• Thousands of cores are used per query.
• Separate Storage and Computing is available in Google BigQuery. 

To understand more about Google BigQuery, please refer to the following Hevo Data article. 

Streaming Data into Google BigQuery

There are few ways of loading data into Google BigQuery, and the different formats of loading data into BigQuery as listed below:

• Loading data from Cloud Storage 
• Loading data into Google BigQuery
• Streaming data into Google BigQuery  

Google BigQuery processes and loads data efficiently. The purpose of this article is to talk about one of the cost-effective ways to load data into BigQuery – Streaming Data into BigQuery. Importance of pumping data into data stores in ‘near real-time’ is appreciated by everyone. Google BigQuery Streaming Insert makes sure that data is pumped in ‘near real-time’.

Prerequisites for Google BigQuery Streaming Insert

Before proceeding with the implementation of Google BigQuery Streaming Insert, it is important to consider the following prerequisites:

1. Write Access is a must, before streaming data into the destination table, and the existence of tables is must, before writing data into it.  In case tables are not available, one will have to use Template Tables. 
   Please Note – Template Tables are new tables you want to add to the database. In template tables, one does not need to specify the table structure or import the table metadata. These tables are created automatically.  
2. There is a quota policy which you have to follow to avoid errors. You can find it here. It is discussed later in this article. 
3. Google BigQuery Streaming Insert is not available on the Free Tier Account. So, make sure that the billing is open and set to proceed further. In case you attempt to use streaming without enabling billing, you receive the following error. 
   ‘BigQuery: Streaming insert is not allowed in the free tier.’

Mechanism of Google BigQuery Streaming Insert

Instead of using a job to load data into BigQuery, you can choose to stream your data into Google BigQuery with one record at a time by using the tabledata().insertAll() method. This approach enables querying data without any delay in running a load job.

from google.cloud import bigquery
client = bigquery.Client()
rows_to_insert = [
    {u"name": u“Richard", u"age": 18},
    {u"name": u"Dennise", u"age": 20},
    {u"name": u"Hakim", u"age": 23},
]
errors = client.insert_rows_json(table_id, rows_to_insert) #table id is id of table your data is stored in  
if errors == []:
    print("New rows have been added.")
else:
    print("Encountered errors while inserting rows: {}".format(errors))

There is flexibility as you do not need to populate the insertId field when you insert rows. The following examples show how to avoid sending insertId for each row when streaming.

errors = client.insert_rows_json(
    table_id, rows_to_insert, row_ids=[None] * len(rows_to_insert)
)  

Streaming into Partitioned Tables

You can stream data into a table, partitioned on a DATE or TIMESTAMP column with a duration between 5 years in the past and 1 year in the future. Data outside this range is rejected. 

When data streaming occurs, extraction of the UNPARTITIONED partition takes place. When there’s enough unpartitioned data, the data is partitioned to the corresponding partitions.

Limitations of Google BigQuery Streaming Insert

While using BigQuery, there are few limitations that you have to bear. 

1. Access Control:
   To insert streaming data, one must have the Write access for the dataset that contains the destination table. Further detail is available in the official documentation.
2. Quota Policy:
   If you do not populate the insertId field when you insert rows, the following quote applies:
   • Maximum bytes per second allowed are 1GB. This limit applies at a project level and does not apply to the individual tables. 

Another scenario occurs when you populate the insertId field while inserting rows:

• If you populate the insertId field for each inserted row, you will be limited to 500,000 rows per second in the US and EU multi-region, per project. In other words, the sum of rows per second streamed to all tables for a given project within a multi-region is limited to 500,000. Each table is additionally limited to 100,000 rows per second.
• Exceeding either of these limitations will cause quotaExceeded errors. 
• In all other regions, maximum rows per second per project is 100000, and again exceeding the limit will cause quotaExceeded errors.
• Maximum rows per second per table allowed are 100,000. 
• Maximum bytes per second per table allowed are 100 MB.

The following quotas apply whether you populate the insertId Field or not:

• Maximum row size allowed is 5 MB. Exceeding it will give an invalid error. 
• HTTP request size limit is 10 MB. Exceeding it will give an invalid error. 
• Maximum rows per request allowed are 10,000. It is the max limit, but a maximum of 500 rows per request is recommended. As experimentation with representative data (schema and data sizes) will help you determine the ideal batch size.
• insertId field length is 128. Exceeding this will give an invalid error. 

These are the quota limitations set by Google. If your quota increases beyond these limits, you can submit a request at Google Cloud Console. You will be getting a response in 2-3 working days. 

3. Data Availability:
   The first time a streaming insert occurs, the streamed data is inaccessible for up to 2-3 minutes. After this, the streamed data is immediately queryable. The availability of data for export and copy can take up to 90 minutes. 

Pricing for Google BigQuery Streaming Insert

In Google BigQuery, billing takes place based on rows that are successfully inserted. Individual rows are calculated using a 1KB of minimum size. The pricing for the insert operations cost you 0.010$ per 200 MB of data. For more details, please have a look at this link.

Conclusion

There are certain benefits of using the Google BigQuery streaming insert, but it does not come without limitations and a small cost. If you require real-time ingestion of non-transactional data, this feature is worth using. But, if you are looking for a fully automated solution, then try Hevo Data.

Hevo Data is a No-code Data Pipeline. It supports pre-built integration from 100+ data sources. You can easily migrate your data to Google BigQuery in real-time.

Explore more about Hevo by signing up for a 14-day free trial today.

Share your experience of using Google BigQuery Streaming Insert in the comment section below.