Get started with Google BigQuery
1. Check the left menu (which can be visible or reduced) for your main navigation items.
SQL workspace is the place where you’ll work with your SQL queries.SQL is a standard language for storing, manipulating and retrieving data in databases. Data Transfers – is where you can connect some Google (Google ads, Google Play, YouTube) and non-Google (Amazon S3, Teradata) data sources with BigQuery Scheduled queries – is where you see the queries that you scheduled to run regularly to fill out the table you use for your reporting/analysis Reservations – enables you to switch between on-demand pricing and flat-rate Pricing. With flat-rate pricing, you purchase dedicated query processing capacity. BI Engine – it’s a BigQuery feature that helps you analyze data stored in BigQuery with sub-second query response time; it makes your reports/dashboards more efficient and fast. Release notes – is where you can discover the latest updates in BigQuery
2. Click on +ADD DATA and then on Explore public datasets to explore the interface, even your in Sandbox mode.
3. Search for Analytics to add the obfuscated Google Analytics data then click on View dataset:
You’ll get access to bigquery-public-data project with different public datasets available (and not only google analytics): Dataset is a folder that contains tables with data. It gives you a possibility to organize your data per source, per period etc. For example, you can have one dataset for CRM data, another for Google Analytics data, another for Call tracking data etc.
4. Search for google_analytics_sample dataset. You’ll have two options on your right: query table or copy table.
5. Learn more about BigQuery UI in Features & Info discover some common shortcuts in Shortcut, change the look of the interface using Show preview features/Hide preview features in the panel in top-left corner.
6. Check the visibility of buttons by clicking on project, dataset or table.
Some buttons will be available or not depending if you clicked on the project, on the dataset or on the table. For example, when you click on the project (‘bigquery-public-data’) you’ll see that you can: pin/unpin the project (it’s useful when you have a lot of them but you use some more often than others); create a dataset in this project compose new query When you click on the dataset you’ll see the possibility to: Share this dataset with your colleagues Authorize a routine to access the data in this dataset You can copy or delete the dataset When you click on the table you’ll be able to: Query this table (it will create a basic query for this table so you won’t have to type manually th full name of the table) Copy or delete table Export the table to Data Studio or Google Cloud Storage Or scan the table with Google Cloud Data Loss prevention service (to verify if there’s a sensitive information)
7. See the history of what’s going on in your BigQuery you can check the Job history and Query history in the bottom panel. Job - is a task/operation, like data import to BigQuery
For example. In the same panel you can check the queries you saved previously so that you can re-use them.
8. Save your query as View or as saved query in the options above the query panel.
View is treated as a table, you can query your view and use it in your dashboard. Sometimes you’ll want to prepare the views for your dashboard, but some recommend to use scheduled queries that will fill out the real table. We’ll talk about it in one of our playbook about reporting. Schedule your query so it’ll run automatically every hour/day/week etc. when the new data is available, In MORE section you can Format query to make it more readable and play with the query settings if needed (to change the SQL dialect for example)