Lab Details:

1. This lab walks you through Cloud BigQuery.

2. You will be creating a BigQuery Dataset and loading the CSV data.

3. Region: us-central1

4. Duration: 60 minutes

What is BigQuery?

• BigQuery is a fully managed big data tool for companies who need a cloud-based interactive query service for massive datasets. 

• BigQuery is not a database, it's a query service. 

• BigQuery supports SQL queries, which makes it quite user-friendly. It can be accessed from Console, CLI, or using SDK. You can query billions of rows, it only takes seconds to write, and seconds to return.

• You can use its REST APIs and get your work done by sending a JSON request.

• Let’s understand with help of an example, Suppose you are a data analyst and you need to analyze tons of data. If you choose a tool like traditional MySQL, you need to have an infrastructure ready, that can store this huge data.

• You can focus on analysis rather than working on infrastructure. Hardware is completely abstracted.

• Designing this infrastructure itself will be a difficult task because you will have to figure out RAM size, CPU type, or any other configurations.

• BigQuery is mainly for Big Data. You shouldn’t confuse it with OLTP (Online Transaction Processing) database. 

Terms related to BigQuery:

• Datasets: Datasets hold one or more tables of data.

• Tables: Tables are row-column structures that hold actual data

• Jobs: Operations that you perform on the data, such as loading data, running queries, or exporting data.

Lab Tasks:

1. Login into GCP Console.

2. Creating a BigQuery Dataset.

3. Create a Table.

4. Loading the data through an external CSV.

5. Reading data through the Table using SQL Query.

Launching Lab Environment:

1. Launch the lab environment by clicking on . This will create a GCP environment with the resources and roles required for this lab.

2. Once the lab environment is ready,  will be active. Click on , this will open Google Sign-In page. (Make sure you are using an Incognito mode).

3. On your previous tab, Click on   and copy your credentials to Sign-in to the console. 

4. Once logged in, you will be redirected to the GCP Console.

Note : If you have completed one lab, make sure to sign out of the GCP account before starting a new lab. If you face any issues, please go through FAQs and Troubleshooting for Labs.

Creating a BigQuery Dataset:

1. Click on the hamburger icon on the top left corner 

2. Click on BigQuery under the BigData section.                                                                                                                

3. Find the project id listed in the sidebar. Click on the Kebab menu in the corner then click on Open                                                                       

4. Click on Create Dataset on the top bar. 

5. Enter dataset id as whizlabs_bq_dataset. if you enter anything else then it won't validate the lab.      

6. Choose the Data location as United States(US).

7. Keep the given options as is.                                                                                                                        

8. Click on Create Dataset.

9. Click on the right arrow in the sidebar to expand the project.                                                                                 

10. You will be able to see the dataset. Click on the Kebab menu in the corner then click on Open.                                                                                                 

11. You can see + Icon to create a table and upload the data. 

Uploading the Source file:

1. Choose the option as Upload in Source.                                                                                                                                

2. Click on Browse to choose the file from the local system. Click here to download the file which you can choose to upload.

3. Enter the table name as user_details. Do not choose any other name. It is required for the validation of the lab.

4. Keep the given options as is.                                                

5. Click on the Check box to auto-detect the schema.                                                                                                                                

6. Click on Create table.

7. Click on the right arrow to expand the dataset.                                                                                                                

8. You will be able to see the table which you created.                                                                                                

9. You can see the schema as shown.                                                                                

Querying the Data:

1. Click on Compose new query.

2. Modify and enter the below query to fetch the data from the table, change your project id.  

   select username from `<project_id>.whizlabs_bq_dataset.user_details` where age > 32

3. Click on Run to trigger the query.

4. You can see the output in the Query results section.                                                               

Completion and Conclusion:

1. In this lab, you have created a BigQuery Dataset.

2. You have created a Table and loaded data through an external CSV.

3. You have read the data from the table using SQL Query.

End Lab:

1. You have successfully completed the lab.

2. Once you have completed the steps click on  from your whizlabs dashboard.