​

AmberProc for Snowflake

Welcome to AmberProc for Snowflake, a cutting-edge native application designed to supercharge your Snowflake stored procedures and functions development. Powered by OpenAI’s GPT-4 and tailored with an exclusive dataset curated by our team, AmberProc offers unmatched assistance directly within your Snowflake environment.

Key Features

 

1. Intelligent Code Assistance: Benefit from real-time, context-aware code suggestions to accelerate the development of error-free stored procedures and functions.

2. Automated Debugging: Easily identify and correct common errors, improving your code’s reliability and streamlining the debugging process.

3. Performance Optimization: Analyze your code to uncover optimization opportunities, ensuring maximum efficiency.

4. Learning and Insights: Enhance your coding skills with insights into patterns, common issues, and best practices for Snowflake stored procedures.

5. Query Resolution: Quickly find solutions and detailed explanations for your Snowflake queries.

 

Getting Started

To start using AmberProc for Snowflake, open up a SQL worksheet and follow these steps:

Initial Setup

1.    Assign Required Role:
       USE ROLE ACCOUNTADMIN;

2.    Create Database and Schema:
       CREATE DATABASE AMBERPROC_CONFIG;
       CREATE SCHEMA AMBERPROC_CONFIG.INIT_SCHEMA;

3.    Set Active Database and Schema:
       USE DATABASE AMBERPROC_CONFIG;
       USE SCHEMA INIT_SCHEMA;

4.   Grant Necessary Permissions: sql     GRANT CREATE NETWORK RULE, CREATE SECRET, USAGE ON SCHEMA AMBERPROC_CONFIG.INIT_SCHEMA TO ROLE ACCOUNTADMIN;

Configuration

1.    Network Rule and Secret Creation:
       CREATE OR REPLACE NETWORK RULE AMBERPROC_NETWORK_RULE
       MODE = EGRESS
       TYPE = HOST_PORT
       VALUE_LIST = ('api.openai.com');

       CREATE OR REPLACE SECRET AMBERPROC_API_KEY
       TYPE = GENERIC_STRING
       SECRET_STRING = '';
       SECRET_STRING value will be kept empty

2.    External Access Integration:
       CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION AMBERPROC_EXT_INT
       ALLOWED_NETWORK_RULES = (AMBERPROC_NETWORK_RULE)
       ALLOWED_AUTHENTICATION_SECRETS = (AMBERPROC_API_KEY)
       ENABLED = TRUE;

3.    Grant Access to Application:
        GRANT USAGE ON DATABASE AMBERPROC_CONFIG TO APPLICATION AMBERPROC;
    GRANT USAGE ON SCHEMA AMBERPROC_CONFIG.INIT_SCHEMA TO APPLICATION      AMBERPROC;
        GRANT USAGE ON INTEGRATION AMBERPROC_EXT_INT TO APPLICATION AMBERPROC;
    GRANT READ ON SECRET AMBERPROC_CONFIG.INIT_SCHEMA.AMBERPROC_API_KEY TO APPLICATION AMBERPROC;

4.    Initialize Application:
       CALL AmberProc.functions.init_app(PARSE_JSON('{
       "secret_name": "AMBERPROC_CONFIG.INIT_SCHEMA.AMBERPROC_API_KEY",
       "external_access_integration_name": "AMBERPROC_EXT_INT",
}'));
       Result:
       AmberProc is initialized successfully.

5.    Access Permissions for Streamlit: Launch the application and navigate to the Streamlit section. To utilize the app’s features, the EXECUTE TASK and EXECUTE MANAGED TASK permissions must be granted first. Once those necessary permissions have been enabled, the app will be ready for use.

 

Debugging Guide

When encountering issues during the setup or operation of AmberProc for Snowflake, follow these steps to diagnose and resolve common problems:

1.    Role Permissions: Ensure the ACCOUNTADMIN role is assigned to the user performing the setup. This role is necessary for creating databases, schemas, network rules, and secrets.

        USE ROLE ACCOUNTADMIN;

2.    Verify Configuration Source: Ensure that the correct Database AMBERPROC_CONFIG and Schema INIT_SCHEMA is selected. If a custom database and schema is opted, ensure the accurate selection of the intended database and schema for the application configuration settings throughout the setup process.

         USE DATABASE AMBERPROC_CONFIG;
         USE SCHEMA INIT_SCHEMA;

3.    Application Permissions: Verify that the application AmberProc has been granted the necessary permissions to access the database, schema, integration, and secrets. These permissions are crucial for the application’s ability to interact with Snowflake resources.

          GRANT USAGE ON DATABASE AMBERPROC_CONFIG TO APPLICATION AMBERPROC;
   GRANT USAGE ON SCHEMA AMBERPROC_CONFIG.INIT_SCHEMA TO APPLICATION AMBERPROC;
          GRANT USAGE ON INTEGRATION AMBERPROC_EXT_INT TO APPLICATION AMBERPROC;
      GRANT READ ON SECRET AMBERPROC_CONFIG.INIT_SCHEMA.AMBERPROC_API_KEY TO APPLICATION AMBERPROC;

4. Check Network Configuration: Confirm that the network rule AMBERPROC_NETWORK_RULE accurately reflects the required egress settings for accessing api.openai.com. Incorrect network configurations can prevent AmberProc from communicating with external services.

            CREATE OR REPLACE NETWORK RULE AMBERPROC_NETWORK_RULE
            MODE = EGRESS
            TYPE = HOST_PORT
            VALUE_LIST = ('api.openai.com');

5.    Application Initialization Parameters: Check the initialization parameters provided to the AMBERPROC_CONFIG.INIT_SCHEMA.INIT_APP procedure. Ensure that the secret_name and external_access_integration_name accurately reflect the created resources.

            CALL AmberProc.app_functions.init_app(PARSE_JSON('{
            "secret_name": "AMBERPROC_CONFIG.INIT_SCHEMA.AMBERPROC_API_KEY",
            "external_access_integration_name": "AMBERPROC_EXT_INT",
             }'));

For further assistance with setting up AmberProc, consult the documentation or reach out to our support team at amberproc@amberflux.com 
 

Future Plans
 

AmberFlux is constantly working on enhancing AmberProc for Snowflake. Moving forward, the application’s functionalities will be upgraded in the following ways:

1.    Dataset Expansion: To include broader aspects of Snowflake’s capabilities, enhancing the application’s utility and assisting users in other aspects of Snowflake and its sub applications.

2.    Model Options: Additional Models using Snowflake Cortex like Llama2, Gemini, Claude3 etc will be integrated to offer customers the option to choose the model of their choice. All the models will be fine-tuned on our dataset to ensure quality assistance

​