Snowflake CREATE Warehouse
How to script a consistent and automated “create warehouse snowflake” environment
Prior to undertaking data loading, Snowflake should be configured with some baselines. In this post, we undertake some “lite” Snowflake data warehouse training using theWORKSHEETfeature. This is an easy-to-understand starting point in anyone’s journey to better learn Snowflake data warehouse setup and operations.
If you are wondering why use Snowflake data warehouse, check out that link for the benefits of the platform. For now, we are assuming you are sold and are ready to give it a try.
Let's get started!
Step 1: Create a Snowflake account
If you do not already have an account, Snowflake offers a free trial. Sign up for an account.
If you have an account already, log into it.
Step 2: Locate the WORKSHEET Feature In Your Snowflake Account
If you log in via a web browser, click theWORKSHEETicon at the top of the page.
Important: Ensure that the role for the session is set toACCOUNTADMINto complete this process.
Step 2: Prepare Your Automated Commands
Your Snowflake environment will use the automated setup SQL command we detail later in this post. However, there are a few edits you will need to make.
First, make sure you change the user_name,user_password, and any other variables as needed in the commands where you seeCHANGEME:
- role_name = CHANGEME_ROLE;
- user_name = ‘CHANGEME’;
- user_password = ‘CHANGEME’;
- warehouse_name = ‘CHANGEME_WAREHOUSE’;
- database_name = ‘CHANGEME_DATABASE’;
- schema_name = ‘CHANGEME_SCHEMA’;
For example, you might switchCHANGEMEto something that reflects your team or a project name:
- role_name = MARKETING_ROLE;
- user_name = ‘datasc!encet3amalpha’;
- user_password = ‘@saN3m!toD!3411tTyUU912’;
- warehouse_name = ‘MARKETING_WAREHOUSE’;
- database_name = ‘MARKETING_DATABASE’;
- schema_name = ‘MARKETING_SCHEMA’;
Ultimately, these values are up to you to supply in accordance with allowed Snowflake values.
Step 3: Configure The Automation Script
Here is the script to paste into your SnowflakeWORKSHEETspace.
Don’t forget to change the items from Step 2!
/*
You can set up access to Snowflake in a few simple steps.
First, log in to your Snowflake account and open up an SQL worksheet.
The SQL commands below will allow you to configure a complete data destination.
Ensure that the role for the session is set to ACCOUNTADMIN in order to complete this process.
*/
begin;
/*
The following variables must be set. You can change these defaults to values suited for your Snowflake deployment.
Remembers to change the password a unique value.
*/
set role_name = ‘CHANGEME_ROLE’;
set user_name = ‘CHANGEME’;
set user_password = ‘CHANGEME’;
set warehouse_name = ‘CHANGEME_WAREHOUSE’;
set database_name = ‘CHANGEME_DATABASE’;
set schema_name = ‘CHANGEME_SCHEMA’;
/*
We need to set the role .to be authorized to perform various operations properly
*/
use role accountadmin;
/*
We will set a role to use for making the connection.
*/
create role if not exists identifier($role_name);
grant role identifier($role_name) to role SYSADMIN;
/*
Create a user and assign authorizations
*/
create user if not exists identifier($user_name)
password = $user_password
default_role = $role_name
default_warehouse = $warehouse_name;
grant role identifier($role_name) to user identifier($user_name);
/*
We can now switch to the default system admin role for the following operations.
*/
use role sysadmin;
/*
Creating the warehouse data destination. Adjust these defaults as needed per current Snowflake best practices
*/
create warehouse if not exists identifier($warehouse_name)
warehouse_size = xsmall
warehouse_type = standard
auto_suspend = 600
auto_resume = true
initially_suspended = true;
/*
Creating the database and schema within your data destination
*/
create database if not exists identifier($database_name);
create schema if not exists identifier($schema_name);
/*
Authorize the role to use the warehouse, database, and schema
*/
grant USAGE
on warehouse identifier($warehouse_name)
to role identifier($role_name);
grant CREATE SCHEMA, MONITOR, USAGE
on database identifier($database_name)
to role identifier($role_name);
grant ALL
on schema identifier($schema_name)
to role identifier($role_name);
commit;
Step 4: Optional Network Policy Whitelisting
Network policies provide options for managing network configurations to your Snowflake service.
If you want to create a network policy restricting access to your account based on user IP address enables you to create an IP allowed list, as well as an IP blocked list, if desired.
Here is a snippet to create a whitelist (allowed):
/*
If you want to use a network policy or modify an existing one, uncomment the one (create or alter) that aligns with your needs.
This example shows whitelisting two IP addresses: 1.1.1.1/32, 2.2.2.2/32
Please adjust these to correspond to the IP addresses you want to whitelist.
*/
— create network policy openbridge_ip_whitelist ALLOWED_IP_LIST = (‘1.1.1.1/32’, ‘2.2.2.2/32’);
— alter network policy <your_current_network_policy_name> SET {[ALLOWED_IP_LIST] = (‘1.1.1.1/32’, ‘2.2.2.2/32’)]};
Step 5: RUN Your Snowflake Worksheet
With your edits in place, you can paste the content in the Snowflake Worksheet. Make sure you have selected All Queries when you start your RUN. You should see a “success” output from Snowflake. If not, check your inputs in the script.
As a sanity check, review that the Warehouse, Database, Schema, and others elements are present and align with your configuration.
Snowflake is ready to receive data!
If all looks good, you have now configured a Snowflake.
The process demonstrated how you can start to automate the configuration of a base Snowflake configuration. For example, you can use parts of this process in aWORKSHEETas needed by modifying the script to separately create users, roles, whitelists, and so forth.
Good luck and enjoy using your Snowflake cloud data warehouse.
Now that you have Snowflake configured, want to learn how to load data into Snowflake via automated data pipelines, check out Openbridge.
Snowflake CREATE Warehouse was originally published in Openbridge on Medium, where people are continuing the conversation by highlighting and responding to this story.
source https://blog.openbridge.com/snowflake-create-warehouse-876b0855a536?source=rss----4c5221789b3---4
