Querying Massive Data in Seconds: Google BigQuery Modern Data Warehouse from Getting Started to Proficient Tutorials
In today's era of TB and PB data, almost every Internet team will face a big technical bottleneck:
The data report is too slow to check.
Traditional commercial databases (such as MySQL and PostgreSQL) are faced with hundreds of millions of log analysis or e-commerce flows, even if you build a perfect index, a complex one
GROUP BY
When the aggregate query hits, the CPU of the server can soar to 100 percent in an instant, then there will be chrysanthemum circles for several minutes or even hours, and finally there will be a direct OOM (memory overflow) crash. In order to solve this problem, many teams have to pay high prices to build Hadoop or even build their own ClickHouse clusters. As a result, not only the operation and maintenance threshold is frighteningly high, but also the monthly server hardware bill directly hurts the boss.
In the ecology of Google Cloud(GCP, Google Cloud), there is a dimension reduction strike designed to solve massive analysis, called
Google BigQuery
.
Its core logic is exceedingly pure:
Fully managed Serverless (serverless) architecture + hyperscale distributed columnar storage
. You don't need to worry about any underlying server configuration, you don't need to build indexes, just throw hundreds of gigabytes of files at it, and it can spit out the final aggregate result for you in a few seconds using standard SQL statements.
Today we don't back boring cryptographic formulas and refuse any nonsense. Directly from the most hard-core actual combat, hand-in-hand take you to configure the whole process, take you from zero proficient in BigQuery enterprise-level advanced play.
The first stage: deep disassembly, why can BigQuery "second-level query"?
Before you can write SQL, you have to model BigQuery underlying physical world in your head, otherwise it's hard to understand why it can run so fast without indexes.
The bottom layer of the BigQuery uses
Complete separation of computing and storage
The subversive architecture:
Container Terminals (Colossus distributed storage): Your data landing position. The BigQuery uses columnar storage (Capacitor format). Traditional database (line storage): In order to check the age of all users, the entire line of data including name, address, password, etc. must be read out from the hard disk, resulting in massive I/O waste. BigQuery (Column Storage): Data is stored in columns in groups. When you check the age, it only reads the data in the "age" column accurately, and does not even touch the other columns. Hard disk I/O was directly cut by more than 90%.
Super Engine (Dremel Computing Cluster): When you tap a complex query SQL on the console and click Execute, Google instantly dispatches hundreds of Slot (meter
cell) of the virtual compute node. Like an army, they cut your massive data into countless small pieces for concurrent scanning, and finally put together the results and spit them out to you in a few seconds.
Core conclusion: You pay according to * * query the amount of data scanned (Data Scanned)* * (about US $5 per TB scanned), or purchase fixed computing resources. Therefore, how to write "money-saving and efficient" SQL is a watershed that distinguishes rookies from big factory architects.
Phase II: Actual Combat Exercise I-Initial Experience of Data Import and Second-Level Query
Make sure you already have a GCP account. We first need to import a raw CSV user behavior log of more than 5 million lines into BigQuery.
1. Create a data set (Dataset)
In the BigQuery, the data structure is very clear: project (Project)-> data set (Dataset, equivalent to database)-> data table (Table).
Log in to the GCP console, search for and go to the BigQuery page.
In the Explorer menu on the left, click the three dots to the right of your project and select "Create dataset".
Dataset ID: Named ecommerce_analytics.
Data location (Data location): It is recommended to choose asia-east1 (Taiwan), which is close to China and fast. Click Create.
2. Import structured data with one click
Click the newly created ecommerce_analytics dataset and select Create table ".
Source: Select from Google Cloud Storage(GCS Object Storage) or Upload a local file directly.
File format: Select CSV.
Target Table Name: Enter the user_logs.
Schema: Select Auto detect ". BigQuery will be extremely clever to automatically scan the first line of your file and automatically tell which column is a string and which column is a number or timestamp.
Click Create Table. In a few seconds, more than five million rows of data were already lying firmly in Google's distributed columnar storage in the cloud.
3. Second-level pull-stream verification
In the query editor, type the following most standard aggregate SQL to see which of the top 10 commodity categories have the highest purchase amount in the past 30 days:
SQL
SELECT
product_category,
COUNT(order_id) AS total_sales
SUM(price) AS total_revenue
FROM
'I'
WHERE
event_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
GROUP BY
product_category
ORDER BY
total_revenue DESC
LIMIT 10;
Click Run. Stare at the timer in the upper right corner of the screen:
0.8 seconds!
In less than one second, BigQuery went through all five million rows of data and spit out the exact answer. The power of the big factory-level big data engine is fully reflected at this moment.
The third stage: enterprise-level high-level performance tuning-welding dead "save money and accelerate" double insurance.
Just now we experienced the speed of BigQuery, but if in the face of a real enterprise PB-level production environment, if you ignore the direct blind inquiry, not only will the speed slow down, but the deduction hourglass on the bill at the end of the month will make your flesh hurt even more.
As the chief data architect, the following two sets of physical defense lines must be installed immediately when building tables:
1. The first line of defense: partition (Partitioning)-cut invalid scanning
If you have accumulated logs for the past 5 years, and you only check "yesterday" data every day. If there is no partition, the BigQuery will scan all the hard disk space in the past 5 years by default, and the cost will be filled directly.
Hardcore specification configuration: When creating a table or designing a pipeline, specify "partitioning" by time column, such as event_date ".
Effect comparison: after opening the partition, when you limit the WHERE event_date = '2026-05-30' in the WHERE condition, the BigQuery will go directly and accurately to the physically isolated drawer on may 30 like turning over a book on the bottom floor, and the drawers on thousands of other dates will not even be looked. The scanning volume dropped from 100GB to 1GB in an instant, and the bill cost was cut by 99%.
2. The second line of defense: clustering (Clustering)-let the data "like a feather"
Time partition is not enough. What if I want to filter users of "a specific country (Country)" or "a specific channel (Source)" at high frequency?
Hard core specification configuration: On the basis of time partition, specify "clustering (Clustering)" for country and source columns ".
Bottom insider: BigQuery will automatically arrange data belonging to the same country and the same channel tightly on physical storage in the background. With the use of partitions, your multi-dimensional funnel analysis speed can soar again.
The fourth stage: commercial big data
Development norms and daily avoidance of pit blood and tears
The tools are extremely quick to use, but in the real scene, countless novice operators and developers often step into the following two bloody pits because they do not understand the underlying hidden rules of the BigQuery:
1. Prohibited to use
SELECT *
(The source of all evil, bring your head to see)
In traditional MySQL, we are used to knocking
SELECT * FROM table LIMIT 10
Let's see what looks on the inside.
Deadly disaster: LIMIT 10 won't save you money in BigQuery columnar storage! Because the BigQuery is read by column, when you write down SELECT *, it will force all the columns and full data at the bottom to be pulled out of the hard disk, even if you only need 10 rows at the end. If this table has 100GB, this line of ordinary commands will directly generate a 100 GB scan deduction.
Big Factory Standard Solution: If you only want to look at the structure of the table and data samples, do not click on the query! Click the name of the table directly to switch to the "Preview (Preview)" tab. Preview function to view data is completely free and zero scan volume. If you have to write SQL, you must clearly write which columns you need (such as SELECT user_id, age).
2. Embrace "universal flattening" (abandon the rigid traditional three paradigms)
Many students who have switched from traditional relational databases are used to splitting tables into very small ones: one table for users, one table for orders, one table for commodities, and finally five or six when writing and analyzing SQL.
JOIN
Put them together by force.
Architect tuning insider: In modern distributed data warehouses, JOIN is an expensive operation that consumes extreme cluster computing power (because it triggers a data shuffle Shuffle between massive nodes). BigQuery favorite structure is the De-normalized Table.
Correct Posture: Utilize BigQuery unique RECORD (nested field) and REPEATED (repeated field) types. You can put all orders and all historical traces of a user directly into this row as a nested array (Array). When querying, the UNNEST function is used for lightning unpacking to completely eliminate the performance pit JOIN.
Phase 5: Advanced Evolution-Open Real-Time Stream Processing and BI Report Screen
If your company's business needs to look at the "real-time market" (such as GMV real-time changes every second during the big promotion),BigQuery can also play with ease:
Real-time perfusion (Streaming Inserts): Your back-end App or stream processing engine (such as Apache Beam, Cloud Dataflow) can be BigQuery
The Storage Write API of, which generates tens of thousands of user behavior logs every second, is continuously injected into the user_logs table in real time like running water.
Zero-delay access to BI large screen: directly click "Browse Data (Explore Data)" at the top of the BigQuery to get through Google's own Looker Studio or third-party Tableau with one click.
Because the BigQuery is built in
BI Engine (memory acceleration engine)
, it caches high-frequency indicators in memory. When business owners and operators frequently drag and drop reports of any dimension on the front-end large screen, the bottom chart refreshes all in
Completed in tens of milliseconds
, truly achieved "data landing see, global second-level observation".
Summary
Using Google BigQuery to build an enterprise-class modern data warehouse, the core industrial-grade essence actually lies in sixteen words:
Column deposit acceleration, partition lock, preview Bai Piao, wide table universal
.
You have completely got rid of the original misery of building hardware clusters and staring at disk I/O of physical machines every day in order to do big data analysis. Hosting all the computing power, storage and scalability to Google's world-class torrent of Serverless computing power. No matter how volcanic data is generated by the business ahead, you can sit in front of the computer and win, allowing the massive amount of data to spit out real business value for you in an instant.