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: Users can now leverage ChatGPT's natural language capability to connect directly to Snowflake’s Data Warehouse.
Example Use Cases:
Check out these links from the application before you get started:
Before you get started, make sure you go through the following steps in your application environment:
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.
**Context**: You are an expert at writing Snowflake SQL queries. A user is going to ask you a question.
**Instructions**:
1. No matter the user's question, start by running `runQuery` operation using this query: "SELECT column_name, table_name, data_type, comment FROM `{database}.INFORMATION_SCHEMA.COLUMNS`"
-- Assume warehouse = "<insert your default warehouse here>", database = "<insert your default database here>", unless the user provides different values
2. Convert the user's question into a SQL statement that leverages the step above and run the `runQuery` operation on that SQL statement to confirm the query works. Add a limit of 100 rows
3. Now remove the limit of 100 rows and return back the query for the user to see
4. Use the <your role> role when querying Snowflake
5. Run each step in sequence. Explain what you are doing in a few sentences, run the action, and then explain what you learned. This will help the user understand the reason behind your workflow.
**Additional Notes**: If the user says "Let's get started", explain that the user can provide a project or dataset, along with a question they want answered. If the user has no ideas, suggest that we have a sample flights dataset they can query - ask if they want you to query that
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.
openapi: 3.1.0
info:
title: Snowflake Statements API
version: 1.0.0
description: API for executing statements in Snowflake with specific warehouse and role settings.
servers:
- url: 'https://<orgname>-<account_name>.snowflakecomputing.com/api/v2'
paths:
/statements:
post:
summary: Execute a SQL statement in Snowflake
description: This endpoint allows users to execute a SQL statement in Snowflake, specifying the warehouse and roles to use.
operationId: runQuery
tags:
- Statements
requestBody:
required: true
content:
application/json:
schema:
type: object
properties:
warehouse:
type: string
description: The name of the Snowflake warehouse to use for the statement execution.
role:
type: string
description: The Snowflake role to assume for the statement execution.
statement:
type: string
description: The SQL statement to execute.
required:
- warehouse
- role
- statement
responses:
'200':
description: Successful execution of the SQL statement.
content:
application/json:
schema:
type: object
properties:
status:
type: string
data:
type: object
additionalProperties: true
'400':
description: Bad request, e.g., invalid SQL statement or missing parameters.
'401':
description: Authentication error, invalid API credentials.
'500':
description: Internal server error.
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.
Before you set up authentication in ChatGPT, please take the following steps in Snowflake.
Snowflake accounts with network policies that limit connections by IP, may require exceptions to be added for ChatGPT.
## Example with ChatGPT IPs as of September 19, 2024
## Make sure to get the current IP ranges from https://platform.openai.com/docs/actions/production
CREATE NETWORK RULE chatgpt_network_rule
MODE = INGRESS
TYPE = IPV4
VALUE_LIST = ('23.102.140.112/28',
'13.66.11.96/28',
'104.210.133.240/28',
'20.97.188.144/28',
'20.161.76.48/28',
'52.234.32.208/28',
'52.156.132.32/28',
'40.84.220.192/28',
'23.98.178.64/28',
'40.84.180.128/28');
CREATE NETWORK POLICY chatgpt_network_policy
ALLOWED_NETWORK_RULE_LIST = ('chatgpt_network_rule');
CREATE SECURITY INTEGRATION CHATGPT_INTEGRATION
TYPE = OAUTH
ENABLED = TRUE
OAUTH_CLIENT = CUSTOM
OAUTH_CLIENT_TYPE = 'CONFIDENTIAL'
OAUTH_REDIRECT_URI = 'https://oauth.pstmn.io/v1/callback' --- // this is a temporary value while testing your integration. You will replace this with the value your GPT provides
OAUTH_ISSUE_REFRESH_TOKENS = TRUE
OAUTH_REFRESH_TOKEN_VALIDITY = 7776000;
NETWORK_POLICY = chatgpt_network_policy --- // this line should only be included if you followed step 1 above
DESCRIBE SECURITY INTEGRATION CHATGPT_INTEGRATION;
You’ll find the required information in these 3 columns:
select SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('CHATGPT_INTEGRATION');
You’ll find the Client Secret in OAUTH_CLIENT_SECRET. Do not include the double quotes when copying the value.
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.
In ChatGPT, click on "Authentication" and choose "OAuth". Enter in the information below.
Form Field | Value |
---|---|
Authentication Type | OAuth |
Client ID | OAUTH_CLIENT_ID from SHOW_OAUTH_CLIENT_SECRETS |
Authorization URL | OAUTH_AUTHORIZATION_ENDPOINT from DESCRIBE SECURITY INTEGRATION |
Token URL | OAUTH_TOKEN_ENDPOINT from DESCRIBE SECURITY INTEGRATION |
Scope | <empty>* |
Token Exchange Method | Default (POST Request) |
*Snowflake scopes pass the role, but you’ll notice the action itself also specifies the role as a parameter in runQuery, so the Scope is unnecessary. You may elect to pass roles in the scope instead of the action parameters if it makes more sense for your GPT.
Once you've set up authentication in ChatGPT, follow the steps below in the application to finalize the Action.
ALTER SECURITY INTEGRATION CHATGPT_INTEGRATION_DEV SET OAUTH_REDIRECT_URI='https://chat.openai.com/aip/<callback_id>/oauth/callback';
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.