Snowflake to Snowflake: Establishing Secure Cross-Account Connectivity
Data exists everywhere in silos and for a reason, but breaking those silos has been challenging. One of the solutions is Data Sharing, Snowflake's innovative data cloud architecture, makes data sharing vastly simplified, secure, and highly effective. It allows global data sharing cross-region, and cross-cloud through the listing, data exchange, and direct share.
However, sometimes you cannot do data sharing, which might be due to compliance or for any other reason. But you need to access the data from another Snowflake account. Traditional Oracle Database, which I have worked for 25+ years, has a nice feature called Database link, which allows you to access the data from another database (Oracle and non-Oracle).
Snowflake to Snowflake Connectivity
Similar to Oracle’s Dblink feature (but not exactly the same) we can build this in Snowflake using External network access and SQL REST API. The use case could be: 1. Getting data from another Snowflake during run time, 2. Execute task or stored procedure, 3. Some sort of trigger when certain events occur in one account and execute something in another account using Alert. I am sure you can find many more use cases (art of possible)!
Let's say, you have two Snowflake accounts, for simplicity call your account a “Local” account and the other Snowflake account as “Remote”, as described in the following architecture diagram:
So, let's look into the details step what we need and how to do it :
- You will need your Snowflake account and URL, here is the guide to find your URL. Basically, it would be accountlocator.region.csp or orgname-account_alias.
- Set up the remote account to use Snowflake Oauth so that SQL API is accessible using Oauth2. You can also do Okta Oauth instead of Snowflake Oauth. We are using Snowflake Oauth. To use Oauth, you need to create a Security Integration, once created, you will get OAUTH_CLIENT_ID and OAUTH_CLIENT_SECRET using DESCRIBE SECURITY INTEGRATION command. You will also need to create REFRESH_TOKEN. Here is the document that has instructions to do all: https://community.snowflake.com/s/article/HOW-TO-OAUTH-TOKEN-GENERATION-USING-SNOWFLAKE-CUSTOM-OAUTH
- Create various objects in the local account for Snowpark external account access using the information created in the above steps. This object allows to do authentication of the remote Snowflake account using OAUTH. Then use this security mechanism in UDF using SQL REST API which allows to execute workload in the remote Snowflake account.
First, we allow the local Snowflake account to whitelist the remote Snowflake account using the network rule object
CREATE OR REPLACE NETWORK RULE snow_access_rule
MODE = EGRESS
TYPE = HOST_PORT
VALUE_LIST = ('your_remote_account.snowflakecomputing.com'
); Then, create a security integration and SECRET in the local account to do authentication to the remote account using oauth. Here, the values of OAUTH_CLIENT_ID, OAUTH_CLIENT_SECRET, and OAUTH_REFRESH_TOKEN, will get from the remote Snowflake account (Step 2). When you create the refresh token, it will ask you to authenticate with a Snowflake username and password, once authenticated, you will be granted access to all the roles that have been assigned to that remote user, which can you use in this method in the local account.
CREATE OR REPLACE SECURITY INTEGRATION snow_remote_oauth
TYPE = API_AUTHENTICATION
AUTH_TYPE = OAUTH2
OAUTH_CLIENT_ID = '0VCAxxxxxxxxx='
OAUTH_CLIENT_SECRET = 'qxxxxxxxxx='
OAUTH_TOKEN_ENDPOINT = 'https://your_remote_account.snowflakecomputing.com/oauth/token-request'
OAUTH_AUTHORIZATION_ENDPOINT = 'https://your_remote_account.snowflakecomputing.com/oauth/authorize'
OAUTH_ALLOWED_SCOPES = ('https://your_remote_account.snowflakecomputing.com')
ENABLED = TRUE;
CREATE OR REPLACE SECRET snow_remote_token
TYPE = oauth2
API_AUTHENTICATION = snow_remote_oauth
OAUTH_REFRESH_TOKEN =
'ver:2-hint:999999--.......';
-- refresh token usually expire in an hour
CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION snow_access_int
ALLOWED_NETWORK_RULES = (snow_access_rule)
ALLOWED_AUTHENTICATION_SECRETS = (snow_remote_token)
ENABLED = true;Finally, write UDF or Stored Procedure to use SQL REST API to execute commands on your remote Snowflake account.
CREATE OR REPLACE FUNCTION exec_remote(account_name string, sqltext string, wh_name string, role_name string)
RETURNS STRING
LANGUAGE PYTHON
RUNTIME_VERSION = 3.8
HANDLER = 'get_data'
EXTERNAL_ACCESS_INTEGRATIONS = (snow_access_int)
PACKAGES = ('snowflake-snowpark-python','requests')
SECRETS = ('cred' = snow_remote_token)
AS
$$
import _snowflake
import requests
import json
token = _snowflake.get_oauth_access_token('cred')
session = requests.Session()
# this can be parameterized
timeout = 60
def get_data(account_name, sqltext, wh_name, role_name):
apiurl='https://'+account_name+'.snowflakecomputing.com/api/v2/statements'
jsonBody = {'statement': sqltext.replace("'","\'"),
'timeout': timeout,
'warehouse': wh_name.upper(),
'role': role_name.upper()
}
header = {
"Authorization": "Bearer " + token,
"Content-Type": "application/json",
"Accept": "application/json",
"Snowflake-Account": account_name,
"X-Snowflake-Authorization-Token-Type": "OAUTH"
}
response = session.post(apiurl, json=jsonBody, headers=header)
return response.json()['data']
$$;That’s all you need, easy and simple. Now you can call UDF as you call any function; for example:
select exec_remote('your_remote_snowflake_account'
, 'select * from SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.customer limit 10'
,'demo_wh'
,'sysadmin');
-- you will see output in array of data
-- Or using UDF in SELECT statement,like federated query
select
value[0]::number c_custkey,
value[1]::string c_name,
value[2]::string c_address,
value[3]::string c_nationkey,
value[4]::string c_phone,
value[5]::number c_acctbal,
value[6]::string c_mktsegment,
value[7]::string c_comment
from
table(flatten(input =>parse_json(
exec_remote('your_remote_snowflake_account'
, 'select * from SAMPLE_DATA.TPCH_SF1.customer limit 10'
,'demo_wh'
,'sysadmin')) ));
Furthermore, you can execute any stored procedure or job in remote Snowflake account as shown below:
-- call/execute remote stored procedure
select exec_remote('your_remote_snowflake_account',
'call testdb.testschema.desc_user(''upatel'')',
'demo_wh','sysadmin');
-- execute remote job
select exec_remote('your_remote_snowflake_account',
'EXECUTE TASK SANDBOX.TEST.FETCH_TARGETED_USERS',
'demo_wh', 'sysadmin');As you can see you can do many things on Snowflake remote account from your local account. However, you cannot load/insert data from your local account to a remote account. Of course, you can unload data in the bucket and copy it into a remote using this.
Another suggestion, if you have a lot of data returning from the remote server you need to modify the UDF to do multiple calls to get all the data from a remote server. I recommend creating a temp table using this rather than calling UDF within the SQL statement. Please read SQL REST API restrictions and usage here.
In addition to authenticating using OATUH, you can also authenticate using the JWT token see code below:
-- for folloing url, use the snowflake account you want to connect
CREATE OR REPLACE NETWORK RULE snow_az_upatel
MODE = EGRESS
TYPE = HOST_PORT
VALUE_LIST = ('xxx.snowflakecomputing.com');
-- you need to have user with public key, create unencrypted version, follow below:
-- https://docs.snowflake.com/en/user-guide/key-pair-auth
-- copy paste your private key from file
CREATE or REPLACE SECRET upatel_private_key
TYPE = password
USERNAME = 'RSAUSER'
PASSWORD = 'MIIEwA
morelines here
WeYcM='
comment='my private key file'
;
CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION snow_az_int
ALLOWED_NETWORK_RULES = (snow_az_upatel)
ALLOWED_AUTHENTICATION_SECRETS = (upatel_private_key)
ENABLED = true;
-- following code required jwt.zip file which you can download from following ulr and upload into your stage internal or externa
-- change the stage name from below code
-- https://github.com/umeshsf/publiccode/blob/8881e490a0a4d9b63b973fe6999d5cec1f5c5e79/JWT.zip
CREATE OR REPLACE FUNCTION exec_remote(account_name string, sqltext string, wh_name string, role_name string)
RETURNS STRING
LANGUAGE PYTHON
RUNTIME_VERSION = 3.8
HANDLER = 'get_data'
EXTERNAL_ACCESS_INTEGRATIONS = (snow_az_int)
PACKAGES =('urllib3','snowflake-snowpark-python','cryptography','pyjwt')
--change below with yoru stage
IMPORTS=('@resources.stages.aws_python_stage/JWT.zip')
SECRETS = ('cred' = upatel_private_key)
AS
$$
import _snowflake
import urllib3
import json
import JWTGenerator
http = urllib3.PoolManager()
timeout = 60
def get_data(account_name, sqltext, wh_name, role_name):
apiurl='https://'+account_name+'.snowflakecomputing.com/api/v2/statements'
sec_object = _snowflake.get_username_password('cred');
jwt_token=JWTGenerator.getJWTToken(account_name, sec_object.username, sec_object.password )
jsonBody = {'statement': sqltext.replace("'","\'"),
'timeout': timeout,
'warehouse': wh_name.upper(),
'role': role_name.upper()
}
header = {
'Authorization': 'Bearer '+jwt_token,
'X-Snowflake-Authorization-Token-Type': 'KEYPAIR_JWT',
'Content-Type': 'application/json',
'Accept':'application/json'
}
response = http.request('POST',apiurl, json=jsonBody, headers=header)
return response.json()['data']
$$;Conclusion:
With the use of Snowpark External Network Access, you can connect to any publicly (cloud) available sites, and with the use of SQL REST API, you can run the Snowflake workload in the remote Snowflake account securely. This will open up a lot of use cases in your data engineering workload.
Happy New Year and I wish you all the best!
Disclaimer: The opinions expressed in this post are my own and not necessarily those of my employer (Snowflake).
