Loading Data
In this section, we will learn how to load data in BigQuery.
Batch Loading
Batch loading is a process for loading the source data into a BigQuery table. There is a list of options for batch loading in BIgQuery, as follows.
- Load jobs.
- SQL.
- BigQuery Data Transfer Services.
- BigQuery Storage Write API.
- Other managed services.
Streaming
Streaming is a process of sending smaller batches of data in real-time. It helps in the availability of data for querying. The options for streaming include:
- Dataflow.
- Storage Write API.
- BigQuery connector for SAP.
Data Integration Method
A user must consider the things mentioned below before choosing Data Integration Method.
- Data Source.
- Reliability of solutions.
- Latency.
- Data Ingestion Format.
- Slow changing vs. Fast changing.
Streaming API
We will use the legacy tabledata.insertAll method in this section. If you are working on a new project, then using the BigQuery Storage Write API is suggested. The reason is Write API is cheap, with more robust features.
Before you start
-
Ensure that you have access to your target table. And the table must exist before you begin to write your data in it.
-
For streaming data, check the quota policy,
-
Verify that the billing is enabled for your project.
- Grant all IAM roles.
Required Permissions
You need a few permissions from the IAM. The list of permissions is as follows.
- bigquery.tables.create
- bigquery.datasets.get
- bigquery.tables.get
- bigquery.tables.updateData
- roles/bigquery.dataEditor
- roles/bigquery.dataOwner
- roles/bigquery.admin
Streaming Data into BigQuery
Follow the below example to stream data into BigQuery.
from google.cloud import bigquery
client = bigquery.Client()
rows_to_insert = [
{"full_name": "Phred Phlyntstone", "age": 32},
{"full_name": "Wylma Phlyntstone", "age": 29},
]
errors = client.insert_rows_json(table_id, rows_to_insert) # Make an API request.
if errors == []:
print("New rows have been added.")
else:
print("Encountered errors while inserting rows: {}".format(errors))
When you insert rows, the insertID field does not need to be filled manually. The example that follows shows how to stream data without delivering an insertID for each row.
from google.cloud import bigquery
client = bigquery.Client()
rows_to_insert = [
{"full_name": "Phred Phlyntstone", "age": 32},
{"full_name": "Wylma Phlyntstone", "age": 29},
]
errors = client.insert_rows_json(
table_id, rows_to_insert, row_ids=[None] * len(rows_to_insert)
) # Make an API request.
if errors == []:
print("New rows have been added.")
else:
print("Encountered errors while inserting rows: {}".format(errors))
Streaming Data Availability
In this section, we will learn some points on Streaming data availability.
-
After BigQuery fully accepts a tabledata.insertAll request, the data is directly available for real-time analysis using Google Standard SQL queries.
-
For the PARTITIONTIME pseudo column, the partitioned table has a NULL value.
-
BigQuery has assigned the final NON NULL value of the PARTITIONTIME column in the background.
- The streamed row may not be available for a few minutes. It may take 90 minutes in rare cases.
Best Efforts De-duplications
De-duplicate in BigQuery is multiple occurrences of any given row. The system expects that any rows given with identical insertIds will also match. It is unclear which row BigQuery saves if two rows have similar insertIds.
Disabling best effort de-duplication
We can disable this feature by not giving the same insertids for each row inserted. You can also remove the duplicate rows manually. Follow the below steps.
Step 1: Your table schema should include the insertId column, and the insertId value should be included in the data for each row.
Step 2: Perform the given query to look for duplication after streaming has ended:
#standardSQL
SELECT
MAX(count) FROM(
SELECT
ID_COLUMN,
count(*) as count
FROM
`TABLE_NAME`
GROUP BY
ID_COLUMN)
Step 3: Run the following query to remove duplicates. Disable result flattening, allow huge results, and specify a destination table.
External Data Sources
An external data source is one that you can access directly through BigQuery even though the data is not kept there. There are only a few external sources that BigQuery supports. The list of those is below:
Frequently Asked Questions
What are the features of Google BigQuery?
The features of BigQuery include:
Multicloud aspects.
Inbuilt ML Integration.
Foundation for BI.
Geospatial Analysis.
Automated Data Transfer.
What is the architecture of BigQuery?
BigQuery stores data in a columnar structure called Capacitor. You may expect that BigQuery can achieve high compression ratios and scan versions. This is because each field, or column in the table, is stored in a separate Capacitor file.
What language is BigQuery written in?
Although a legacy SQL dialect is also offered, BigQuery supports the Google Standard SQL dialect. Google Standard SQL offers the widest set of abilities. And is the best choice if you're new to BigQuery. For e.g., Google Standard SQL is the only database engine that supports capabilities like DDL and DML.
Conclusion
We have discussed the topic of Advanced concepts of BigQuery. We have learned how to export table data loading data, stream API, and use external data sources.
We hope this blog has helped you enhance your knowledge of the Advanced concepts of BigQuery. If you want to learn more, check out our articles on:
And many more on our platform Coding Ninjas Studio.
But suppose you have just started your learning process and are looking for questions from tech giants like Amazon, Microsoft, Uber, etc. In that case, you must look at the problems, interview experiences, and interview bundles for placement trials.
You may also consider our paid courses to give your career an edge over others!
Happy Learning!
24+ registered 
