Beginners guide to BQ – Part 1: Getting Started

Baruch Shteken
Cloud Computing
June 27, 2020

What is this article about?

In this article, we will start exploring Google BigQuery. 

You will learn how to create a Project, Schema and a query. 

This article is mainly for BI Developers who want to expand their capabilities to handling Big Data. 

What is BigQuery?

BigQuery is a full serverless service and Data Warehouse that can run extremely fast interactive and ad-hoc queries.

Data can be saved in Google Storage and be queried by BiqQuery, or alternatively, data can be imported to BigQuery and be queried there, too.

The difference between a Relational DB and Big Query


RDB vs BigQuery


Getting Started

In order to start working in Google Big Query – you have to create a project (an environment). 

Here is how to create your first google project:

** If you already have a project skip to the next section.

  1. Open your browser
  2. Enter https://cloud.google.com/bigquery
  3. Click on “Try BigQuery free”
  4. Enter your google credentials
  5. Check “I Agree to the terms…” and click “Agree to terms” 
  6. If you got to the “Google cloud platform“page  - you are in the right place.

This page holds all of the GCP(Google Cloud Platform) environment. 

  1. Click on “Create Project”. Give it a name and click: “Create”.
  2. You should now enter the Google Console screen. 
  3. You should now see your project name, on the Left bottom pane.

Creating a Data Set

In order to create a table, you first need to create a Data set (a Data base).

Here is how to create a data set and a table:

  1. Enter https://cloud.google.com/bigquery
  2. On the Left bottom pane, you should see your project name. Click on it.
  3. Now you will see an option to create a Dataset.
dataset
  1. Click on the “Create Dataset” 
  2. Now, give your Dataset a name and choose its location.
  3. Click “Create Dataset”
create data set
  1. After you create a Data set, you can create tables inside of it.

Creating a Table

Now let’s create a Table inside your Data set:

  1. Click on your Data set from the Dataset list and you will see this window:
create table
  1. Click on the Create Table button, and the following will appear:
more options
  1. The Table Source can be one of the following:
  • Empty Table – no data is loaded
  • Google Cloud Storage – data is loaded from Google Storage
  • Upload – upload a file from your computer and create a table from it
  • Drive - data is loaded from Google Drive
  • Google Cloud BigTable - data is loaded from Google BigTable
  1. Click “Upload” and browse for the “Financial sample.csv” file from here.

* * Most of the time we would use Google Cloud Storage, since this is where most businesses keep their data. But this time we just want to get some data from our local computer.

** If you have a bucket you can load lots of files to the same table using the wildcard character *. Read more about it here

  1. Enter Table Name: “Financial_Example”
  2. Check “Auto Detect” Schema and input parameters
  3. Click “Create Table” 
  4. On the left pane search for your table, click it, and click on “Query Table” 
  5. Run the query after adding some relevant fields to the select statement.
  6. You should now view your table data


Advanced

  1. When creating a table, Destination section: 
  • Choose in which project your table should be created
  1. Then, you need to choose if the table will be native or external.
  • External means that only metadata is saved in BigQuery - data is not saved. Queries are executed on Data in Google Storage.
  • Native means that both metadata and data are saved in BigQuery. Queries are executed on Data in BigQuery.
  1. If your table is native - you can partition the table.
  2. We advise to create a corresponding folder for each Table. That will hold files that contain rows/objects with the same schema.



Baruch Shteken

D.A.Team - Creating revenue through data

Related Posts

Newsletter BrazilClouds.com

Thank you! Your submission has been received!

Oops! Something went wrong while submitting the form