Adding a Snowflake Data Warehouse as a Data Store with Bastion

You can add a Snowflake data warehouse as a data store with Bastion to Qubole Data Service (QDS) by using the QDS UI or REST API.

Prerequisites

You must be a system administrator to add a data store.
You must have a Qubole Enterprise edition account.

Before you begin, you should review and understand the parameters required for adding a data store. See Understanding the parameters used for Snowflake

Configure Your Proxy Server

Follow the instructions below to configure your proxy server:

A. Install Proxy Server: Squid Proxy

Follow the instructions below to install the proxy server.

Update the server.
```
sudo yum update -y
```

Configure EPEL repo.

sudo yum -y install epel-release
sudo yum -y update
sudo yum clean all

Install squid.
```
sudo yum -y install squid
```

Start and enable squid server.

sudo systemctl start squid
sudo systemctl enable squid

Check the status of squid server.
```
sudo systemctl status squid
```

B. Configure Proxy Server: Squid Proxy

All the configurations for the squid server are present in /etc/squid/squid.conf file.

1. Configure proxy Sources To Access Internet

First, you need to configure the sources from which squid proxy should accept connections. For example, you may need to access this proxy server only from your home network or from specific CIDR ranges.

You can use the following format to add a source IP range with an ACL:

acl localnet src 110.220.330.0/24

Open /etc/squid/squid.conffile and add the source as shown below. Change the IP to the desired network/IP source based on your needs. In the following example, we have added a single source IP.

Restart the proxy server after making the ACL changes.

sudo systemctl restart squid

2. Test proxy Server Connectivity

Use the following curl format to test and verify whether the proxy server is working fine with a simple curl request. By default squid proxy runs on 3128 port.

curl -x http://<squid-proxy-server-IP>:3128  -L http://google.com

C. Configure Basic Proxy Authentication

Along with access ACL’s, you can add basic authentication to your proxy server for extra security. Follow the steps given below for setting up a basic auth for the squid proxy server.

Install httpd-tools.
```
sudo yum -y install httpd-tools
```

Create a passwd file and make squid as the file owner.

sudo touch /etc/squid/passwd && sudo chown squid /etc/squid/passwd

Add pxuser to the password file using htpasswd utility. It will prompt for a custom password. Enter a strong password you need. This username and password will be used for all connections through this proxy.
```
sudo htpasswd /etc/squid/passwd pxuser
```
Open squid config file.
```
sudo vi /etc/squid/squid.conf
```

Add the following to the config file and save it.

auth_param basic program /usr/lib64/squid/basic_ncsa_auth /etc/squid/passwd
auth_param basic children 5
auth_param basic realm Squid Basic Authentication
auth_param basic credentialsttl 2 hours
acl auth_users proxy_auth REQUIRED
http_access allow auth_users

Now, restart squid server for the configuration changes to take place.
```
sudo systemctl restart squid
```
If you use curl to test the proxy connection, you will receive the “authentication required message”. Now, use proxy user and password (configured in step 3) to test the connectivity. Refer the example syntax shown below:
```
curl -x http://35.196.101.43:3128  --proxy-user pxuser:12345  -I http://google.com
```
With username and password, your proxy request goes through.

Note

These commands are to install squid in centos and similar distros. They may vary according to the OS.

Adding a Snowflake Data Warehouse as a Data Store with Bastion by using the QDS UI

Note

All the values are case-sensitive except the Host Address.

From the Home menu, click Explore to navigate to the Explore page.
Pull down the drop-down list that defaults to Qubole Hive and choose + Add Data Store.
Enter the name of a data store that is to be created in QDS in the Data Store Name field.
From the Database Type drop-down, select Snowflake.
Enter the name of the Snowflake catalog in the Catalog Name field.
Enter the Database Name.
Enter the name of the Snowflake warehouse in the Warehouse Name field.
Enter the base URL of your Snowflake account login URL in the Host Address text field. For example, qubole.snowflakecomputing.com
Enter the username (to be used on the host) in the Username text field.
Enter the password (for the username on the host) in the Password text field.
Select Use Bastion Node check box and Bastion Node related fields appear.
Enter the IP address of the proxy server in the IP address field.
Enter the proxy server running on port in the Port field.
Enter the Bastion username in the Bastion Username field.
Enter the Bation password in the Bastion Password field.
Click Save. After the Data Store is added, it displays a success message.

Important

You can also enable privatelink for your Snowflake accounts if your Snowflake account is on AWS. It provides additional security as the privatelink host URL for Snowflake is available only from the ec2 instances within the VPC configured for the privatelink. To know more, see AWS PrivateLink & Snowflake.