Design for Data-Warehouse in GCP | Part-1

In this blog, we shall learn about :-

• Various Data Storage Options available in GCP.
• Detailed Introduction to Google BigQuery.
• Creation of BigQuery Table in GCP by manual schema creation.
• Creation of BigQuery Table in GCP by auto schema detection.
• Import data into BigQuery through Cloud-Console.

Question - What are the possible Data-Storage options available on Google Cloud ?

Question : Explain Cloud-Storage Option ?

Question : Explain Cloud-SQL Option ?

Question : Explain Cloud-Spanner Option ?

Question : Explain Cloud-BigTable Option ?

Question : Explain Cloud-DataStore Option ?

Question : Explain Cloud-BigQuery Option ?

Question : What’s Google-BigQuery ?

Question : What are the characteristics of Google-BigQuery ?

Question : What are the important aspects of Google-BigQuery ?

Question : What’s a Google Cloud Project where BigQuery data is kept ?

Question : What’s a DataSet in the terminology of BigQuery ?

Question : Can you demonstrate the process of creation of a new dataset ?

Step #1 → We goto GCS console and create a new project :-

Step #2 → Next, we go inside this Project and create the new data-set :-

Step #3 → We supply the name of the dataset along with it’s location and expiration :-

Question : What’s a Table in the BigQuery ?

Question : What are the supported data-types with BigQuery Table ?

Question : What all options exists for creating a new BQ-Table in GCP ?

Answer → There are two options of creating a BQ table :-

• Creating Table with schema.
• Creating Table from CSV.

Question : Demonstrate the process of creation of a new BQ-Table with Schema ?

Answer → This is a 2-parts process. In the first part, we define the schema for the table and In the second part, we ingest the data into that table.

Step #1 → We shall be choosing the option of “Create Empty Table” here and prescribe the name of the table. In this case, we are giving the name of the table as patient_details :-

Step #2 → Next, we would be defining the table-schema :-

Step #3 → Finally, we have BQ-table created now : “patient_details”.

Step #4 → Let’s ingest the data into this newly created table. Here is the CSV file that we have to import into this table :-

Step #5 → We would again go into the “Create Table” option, this time from source and now upload the CSV file to import the patients data into this table.

Step #6 → Here, as soon as we select the “Automatically Detect” schema option with the preference of “Append to Table” and hit on button of Create Table.

Step #7 → As soon as we hit the “Create Table” button, it shows the Load-Data job on the screen.

Step #8 → Once the Load-Data job is finished, we can see the data being present into the table : “patient_details” on the “preview” option :-

That’s how the process of BQ-Table creation along with data-importing is completed.

Question : Demonstrate the process of creation of a new BQ-Table directly through the help of a CSV Query?

Answer → We can also get the BQ-table created directly by supplying the data in CSV file format and BQ would be detecting the schema automatically.

Step #1.) Here is the CSV file, which we are going to use, in order to create a BQ-table in GCP :-

Step #2.) Now, we choose the option of “Create from source” and upload the CSV file directly through “File Upload” option.

Step #3.) Next, we define the table name “test_results” and choose the option of “Automatically Detect” and hit the “Create Table” button.

Step #4.) As soon as we hit the “Create Table” button, it shows the Load-Data job on the screen.

Step #5 → Once the Load-Data job is finished, we can see the data being present into the table : “test_results” on the “preview” option :-

Question : Can you demonstrate the process of importing the data into BQ-table, from a GCP Bucket through command-prompt ?

Step #1 → We have a CSV file in the Google Bucket Storage, to which we are planning to read.

Step #2 → Let’s open the Google-Cloud-Shell and read this file :-

Step #3 → Once the above command has been executed, we can observe that, the BQ-table now exists and data has also been imported into the same table under Preview option :-

That’s all in this blog. Thanks for reading till here. If you liked reading it, do clap on the same. We shall see you in next part of this blog.