How to Simplify Snowflake on GCP Setup?

Why is the cloud becoming so popular? It all boils down to the intricate features that come with the platform. For instance, companies no longer have to bear the costs of acquiring expensive and bulky Data Storage and Analysis tools with Cloud Storage. Cloud service providers such as Google Cloud offer virtual machines and hardware and house them in a regional data center. This is extremely cost-effective for companies since data can be shared simultaneously between different individuals.

With the advent of cloud technology, many companies have sprung up to offer related services. However, you cannot talk of cloud technology without mentioning top players like Google Cloud Platform and Snowflake. With this in mind, this post will dive into the nuts and bolts of integrating Snowflake on GCP. Read along to learn more about the steps involved in integrating Snowflake on GCP!

Introduction to Snowflake

In simple terms, Snowflake is a SaaS-based Data Warehouse platform built over AWS Infrastructure. One of the reasons for this software’s popularity among businesses all over the world is its scalability, which makes it cost-effective. The architecture consists of Cloud-only virtual compute instances and efficient storage buckets.

Snowflake is designed in such a way that no hardware or software is required to Install, Configure, or Manage it. Snowflake performs all of the tasks performed by hardware and software, such as Management, Maintenance, and Tuning. Snowflake also enables you to Organize, Discover, and Execute a wide range of data. It provides best-in-class Performance, and Concurrency, allowing you to securely and easily mobilize your data.

Key Features of Snowflake

Below are some of the key features of Snowflake:

• Standard and Extended SQL Support: Since Snowflake is an SQL-based platform, it supports all standard and extended SQL commands. 
• Web-based Graphical User Interface: Snowflake offers users an interactive dashboard to connect with the cloud. The tool allows users to monitor system usage and query data. 
• Command-line Client: This comes as a separate downloadable tool you can install to query data with snowflake and perform other functions. It is built using Python and is a great way to interact with the data warehouse. 
• Extensive Integration Features: Snowflake supports integration with a wide array of third-party tools such as Google Cloud. Later in this article, you will learn about Snowflake on GCP Integration.

For further information on Snowflake, check out Snowflake’s official website here.

Introduction to Google Cloud Platform

Google Cloud Platform (GCP) provides computing resources necessary for developing and deploying applications on the web. By creating an application using the platform, Google automatically keeps track of all its resources, including Storage, Processing Power, and Network Connectivity. Instead of leasing a server or a DNS address, as is the case with normal websites with GCP, you pay for the resources used by your application. 

Key Features of Google Cloud Platform

Some of the key features of Google Cloud Platform are as follows:

• Big Data: GCP offers a dedicated Big Data solution for clients with such needs. Some of the features include BigQuery, which allows users to run SQL-like commands on large chunks of data. 
• Hosting: GCP offers two hosting solutions for customers: the AppEngine, the Platform-as-a-Service, and Compute Engine that acts as Infrastructure-as-a-Service. 
• Containers: These come in handy for PaaS applications since they help boost app deployment. 

Steps to Create Snowflake on GCP Integration

Snowflake on GCP provides the users with best of the both worlds. All the benefits of these robust platforms combined are bound to give you unmatched data processing and analyzing power.

Prerequisites

• Completing the instructions in this section requires access to your Cloud Storage project as a project editor. If you are not a project editor, ask your Cloud Storage administrator to perform these tasks.
• Confirm that Snowflake supports the Google Cloud Storage region that your storage is hosted in. For more information, see Supported Cloud Regions.

Below is a detailed outline of how you integrate Snowflake on GCP: 

Step 1: Create a Cloud Storage Integration

The first step involved to integrate Snowflake on GCP is to allow Snowflake access to GCP data using a GCP bucket. Use the following commands to do so:

CREATE STORAGE INTEGRATION <integration_name>
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = GCS
  ENABLED = TRUE
  STORAGE_ALLOWED_LOCATIONS = ('gcs://<bucket>/<path>/', 'gcs://<bucket>/<path>/')
  [ STORAGE_BLOCKED_LOCATIONS = ('gcs://<bucket>/<path>/', 'gcs://<bucket>/<path>/') ]

Once this is done, Snowflake will create a Cloud Storage account. You can retrieve it by keying the demo_int command. Next, you can locate the value under the property storage_gcp_service account. 

Step 2: Grant Permissions

Snowflake creates a single Cloud Storage service account for each Snowflake account, so all Cloud Storage integrations for a single Snowflake account will use the same service account. Grant the following permissions under your GCP bucket, which will be posted to the service account that you created above: 

storage.buckets.get

storage.objects.create

storage.objects.delete

storage.objects.get

storage.objects.list

Step 3: Setting up the Snowflake Environment

You need a Data Warehouse where the data will be loaded and a database schema that will hold the tables. Below is the code that will serve this purpose.

create warehouse if not exists demo_warehouse
create database if not exists demo_database
use database demo_database
create schema if not exists demo_schema

Next up, you can use the account admin role to set up the database and the schema:

use role accountadmin
use warehouse demo_warehouse
use database demo_database
use schema demo_schema

Finally, you will need to create a table that will populate the data.

create or replace table demo_table (region string, country string, item_type string, sales_channel string, order_priority char(1),
order_date string, order_id integer, ship_date string, 
units_sold integer, unit_price float, unit_cost float, 
total_revenue float, total_cost float, total_profit float);

Step 4: Loading the Data

Now that you have a database and schema for use, you can create a stage that references the CGS bucket and folder paths to load and unload data. It is also important to define the file format as is the case below: 

create or replace stage demo_stage
url = 'gcs://demo_snowflake/sample_sales_data/2020/06/12/small_sample.csv'
storage_integration = demo_int
file_format = demo_csv_format;

Once this is done, you can now load data from the stage to the table. You can do this from external storage or directly from the bucket. That’s it. By following the steps mentioned above, you can load and unload data from GCP to Snowflake and perform Snowflake on GCP Integration. 

Conclusion

In conclusion, deploying Snowflake on Google Cloud Platform (GCP) presents a powerful solution for organizations seeking to leverage the benefits of a modern data warehousing architecture. With its unique architecture that separates storage and compute, Snowflake offers exceptional scalability, performance, and flexibility, allowing businesses to manage and analyze vast amounts of data efficiently.Hevo, a Snowflake partner, provides seamless data migration with Snowflake. Sign up for Hevo’s 14-day free trial and enhance your data migration game.

FAQs