Show Menu
Cheatography
  1. Home >
  2. Cheat Sheets >

Google BigQuery Cheat Sheet by

What’s big query?

Cloud-­based big data analytics web service
Processes very large read-only data sets
SQL-like syntax
Runs on Google Cloud Storage Infras­tru­cture
Analyze terabytes of data with just a click of a button
Can be accessed with REST oriented API

Link BigQuery to GA 360

1. Sign in to Google Analytics
2. Click "­Adm­in" and Navigate to Analytics 360 property
3. Under property settings, click All Products, then click link BigQuery
4. Select the view you want to link
5. Confirm that you have enabled billing and applied any relevant credits or coupons to your project and click Save.
6. If you need to stop the export, return to this page, and click Adjust Link in the BigQuery section.

Logical Operators:

AND, OR, NOT IF(con­dition, true_r­eturn, false_­return)

Mathem­atical functions:

COUNT, SUM, AVG, MAX, MIN
+ (addit­ion), - (subtr­act­ion), / (divis­ion), * (multi­pli­cat­ion), % (modulo)
 

How to start?

Create a project from google cloud console page(h­ttp­s:/­/co­nso­le.c­lo­ud.g­oo­gle.co­m/p­roj­ect­cre­ate).
Select BigQuery in the menu to open Google BigQuery UI
Create a dataset
Now you are ready to query!

Best Practices

1. Only you need SELECT fields
2. Whenever possible use LIMIT
3. Store often queried interm­ediate tables (save table)
4. Test on smaller dataset
5. Compare results with GA’s web ui

Save options

Save Query (requires no space)
Save View (alias for a query)
Save Table (requires space, but offers fast execution and low cost)

Common queries:

SELECT - Select attributes (dim & met to be returned)
FROM - From which data table(s)
ORDER BY - DESC|ASC - order by dimension or metric, descending or ascending
JOIN - Join attributes
AS - Give an alias to an attribute
WITHIN - selecting inside nested values
 

Pricing

Operation
Pricing
Details
Active storage
$0.020 per GB
The first 10 GB is free each month.
Long-term storage
$0.010 per GB
The first 10 GB is free each month.
Streaming Inserts
$0.010 per 200 MB
You are charged for rows that are succes­sfully inserted. Individual rows are calculated using a 1 KB minimum size.
Queries (analysis)
$5.00 per TB
First 1 TB per month is free.

Order of commands

SELECT, WITHIN, FROM, FLATTEN, JOIN, WHERE, GROUP
BY, HAVING, ORDER BY, LIMIT

Reference

       
 

Metadata

  • Languages:
  • Published: 7th January, 2019
  • Rated: 4 stars based on 1 ratings

Comments

No comments yet. Add yours below!

Add a Comment

Your Comment

Please enter your name.

    Please enter your email address

      Please enter your Comment.

          Related Cheat Sheets

          oAuth End Points Cheat Sheet