Notebooks
O
OpenAI Gpt Action Snowflake Direct

Gpt Action Snowflake Direct

chatgptgpt_actions_libraryopenaigpt-4examplesopenai-apiopenai-cookbook
alph-notebooks/openai-cookbook / gpt_action_snowflake_direct.ipynb

GPT Actions - Snowflake direct

Introduction

This page provides an instruction & guide for developers building a GPT Action for a specific application. Before you proceed, make sure to first familiarize yourself with the following information:

This particular GPT Action provides an overview of how to connect to a Snowflake Data Warehouse. This Action takes a user’s question, scans the relevant tables to gather the data schema, then writes a SQL query to answer the user’s question.

Note: This cookbook returns back a ResultSet SQL statement, rather than the full result that is not limited by GPT Actions application/json payload limit. For production and advanced use-case, a middleware is required to return back a CSV file. You can follow instructions in the GPT Actions - Snowflake Middleware cookbook to implement this flow instead.

Value + Example Business Use Cases

Value: Users can now leverage ChatGPT's natural language capability to connect directly to Snowflake’s Data Warehouse.

Example Use Cases:

  • Data scientists can connect to tables and run data analyses using ChatGPT's Data Analysis
  • Citizen data users can ask basic questions of their transactional data
  • Users gain more visibility into their data & potential anomalies

Application Information

Application Key Links

Check out these links from the application before you get started:

Application Prerequisites

Before you get started, make sure you go through the following steps in your application environment:

  • Provision a Snowflake Data Warehouse
  • Ensure that the user authenticating into Snowflake via ChatGPT has access to the database, schemas, and tables with the necessary role

1. Configure the Custom GPT

Set GPT Instructions

Once you've created a Custom GPT, copy the text below in the Instructions panel. Have questions? Check out Getting Started Example to see how this step works in more detail.

[ ]

OpenAPI Schema

Once you've created a Custom GPT, copy the text below in the Actions panel. Update the servers url to match your Snowflake Account Name url plus /api/v2 as described here. Have questions? Check out Getting Started Example to see how this step works in more detail.

[ ]

2. Configure Snowflake Integration

Below are instructions on setting up authentication with this 3rd party application. Have questions? Check out Getting Started Example to see how this step works in more detail.

Configure IP Whitelisting for ChatGPT

Snowflake accounts with network policies that limit connections by IP, may require exceptions to be added for ChatGPT.

  • Review the Snowflake documentation on Network Policies
  • Go to the Snowflake Worksheets
  • Create a network rule with the ChatGPT IP egress ranges listed here
  • Create a corresponding Network Policy
[ ]

Network policies can be applied at the account, security integration, and user level. The most specific network policy overrides the more general network policies. Depending on how these policies are applied, you may need to alter the policies for individual users in addition to the security integration. If you face this issue, you may encounter Snowflake's error code 390422 or a generic "Invalid Client" error.

Create the Security Integration

[ ]
Optional: Automate Network Rule Configuration

There are now over 100 egress IP addresses used by ChatGPT. The list updates irregularly and without announcement. To keep up to date with it, we can fetch the list on a daily basis and apply it to our network rule.

Network rule to allow outbound traffic to OpenAI

CREATE OR REPLACE NETWORK RULE chatgpt_actions_rule
MODE = EGRESS       -- outbound
TYPE = HOST_PORT
VALUE_LIST = ('openai.com:443');

Access Integration to apply the rule

CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION chatgpt_actions_integration
ALLOWED_NETWORK_RULES = (chatgpt_actions_rule)
ENABLED = TRUE;

UDF to Fetch the IP ranges

CREATE OR REPLACE FUNCTION getChatGPTActionsAddresses()
RETURNS ARRAY  -- array<varchar>
LANGUAGE PYTHON
RUNTIME_VERSION = 3.10
PACKAGES = ('requests')
EXTERNAL_ACCESS_INTEGRATIONS = (chatgpt_actions_integration)
HANDLER = 'get_ip_address_ranges'
AS
$$
import requests

def get_ip_address_ranges():
  resp = requests.get("https://openai.com/chatgpt-actions.json", timeout=10)
  resp.raise_for_status()
  data = [entry["ipv4Prefix"] for entry in resp.json().get("prefixes", []) if "ipv4Prefix" in entry]
  return data
$$;

Procedure to update the network rule

CREATE OR REPLACE PROCEDURE update_chatgpt_network_rule()
RETURNS STRING
LANGUAGE SQL
AS
$$
DECLARE
ip_list STRING;
BEGIN
-- Properly quote the IPs for use in VALUE_LIST
ip_list := '''' || ARRAY_TO_STRING(getChatGPTActionsAddresses(), ''',''') || '''';

-- Run the dynamic SQL to update the rule
EXECUTE IMMEDIATE
  'ALTER NETWORK RULE chatgpt_network_rule SET VALUE_LIST = (' || ip_list || ')';

RETURN 'chatgpt_network_rule updated with ' || ARRAY_SIZE(getChatGPTActionsAddresses()) || ' entries';
END;
$$;

Call the procedure

CALL update_chatgpt_network_rule();

Run the procedure every day at 6AM Pacific Time

CREATE OR REPLACE TASK auto_update_chatgpt_network_rule
WAREHOUSE = COMPUTE_WH
SCHEDULE = 'USING CRON 0 6 * * * America/Los_Angeles'
AS
CALL update_chatgpt_network_rule();

3. Configure GPT Action Authentication

Gather key information from Snowflake

  • Retrieve your OAuth Client ID, Auth URL, and Token URL
[ ]

You’ll find the required information in these 3 rows:

../../../images/snowflake_direct_oauth.png

  • Retrieve your OAuth Client Secret using SHOW_OAUTH_CLIENT_SECRETS
[ ]

Now is a good time to test your Snowflake integration in Postman. If you configured a network policy for your security integration, ensure that it includes the IP of the machine you're using to test.

Set OAuth Values in GPT Action Authentication

In ChatGPT, click on "Authentication" and choose "OAuth". Enter in the information below.

Form FieldValue
Authentication TypeOAuth
Client IDOAUTH_CLIENT_ID from SHOW_OAUTH_CLIENT_SECRETS
Client SecretOAUTH_CLIENT_SECRET from SHOW_OAUTH_CLIENT_SECRETS
Authorization URLOAUTH_AUTHORIZATION_ENDPOINT from DESCRIBE SECURITY INTEGRATION
Token URLOAUTH_TOKEN_ENDPOINT from DESCRIBE SECURITY INTEGRATION
Scopesession:role:CHATGPT_INTEGRATION_ROLE*
Token Exchange MethodDefault (POST Request)

*Snowflake scopes pass the role in the format session:role:<your_role> for example session:role:CHATGPT_INTEGRATION_ROLE. You can optionally leave this field empty and specify the role in the GPT instructions, but by adding it here it becomes included in OAuth Consent Request which can sometimes be more reliable.

4. Update the Snowflake Integration Redirect URI

Once you've set up authentication in ChatGPT, follow the steps below in the application to finalize the Action.

  • Copy the callback URL from the GPT Action
  • Update the Redirect URI in your Security Integration to the callback URL provided in ChatGPT.
[ ]

FAQ & Troubleshooting

  • This guide is intended to illustrate general concepts and is provided for reference purposes only. We are unable to provide full support for the third party API integration.
  • The callback url can change if you update the YAML, double check it is correct when making changes.
  • Callback URL Error: If you get a callback URL error in ChatGPT, pay close attention to the Post-Action Steps above. You need to add the callback URL directly into your Security Integration for the action to authenticate correctly
  • Schema calls the wrong warehouse or database: If ChatGPT calls the wrong warehouse or database, consider updating your instructions to make it more explicit either (a) which warehouse / database should be called or (b) to require the user provide those exact details before it runs the query

Are there integrations that you’d like us to prioritize? Are there errors in our integrations? File a PR or issue in our github, and we’ll take a look.