daanalytics

USE ROLE accountadmin;

GRANT DATABASE ROLE snowflake.object_viewer TO ROLE <ROLE>;
GRANT DATABASE ROLE snowflake.governance_viewer TO ROLE <ROLE>;

daanalytics

CREATE STREAMLIT IF NOT EXISTS ask_chatgpt_anything
  ROOT_LOCATION = '<stage_path_and_root_directory>' --'@<Streamlit Database>.Streamlit Schema>.<Streamlit Stage>'
  MAIN_FILE = '<path_to_main_file_in_root_directory>' --'/<Python file>'
  QUERY_WAREHOUSE = <streamlit_warehouse> -- warehouse_name (optional)
  TITLE = '<title>' -- (optional)
  COMMENT = '<comment>' --string_literal (optional)
;

daanalytics

--/ Create the UDF
CREATE OR REPLACE FUNCTION get_openai_response(prompt_output text, ai_model_engine text)
RETURNS STRING
LANGUAGE PYTHON
RUNTIME_VERSION = 3.8
HANDLER = 'return_openai_response'
EXTERNAL_ACCESS_INTEGRATIONS = (openai_external_access_integration)
PACKAGES = ('openai')
SECRETS = ('secret' = open_ai_secret)
AS

daanalytics

--/ Create an external access integration.
CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION openai_external_access_integration
  ALLOWED_NETWORK_RULES = (openai_network_rule)
  ALLOWED_AUTHENTICATION_SECRETS = (open_ai_secret)
  ENABLED = true
;

daanalytics

--/ Create a secret to hold credentials.
CREATE OR REPLACE SECRET open_ai_secret
  TYPE = GENERIC_STRING
  SECRET_STRING = '<OPENAI_API_KEY>'
;

daanalytics

--/ Create a network rule to represent the external network location.
CREATE OR REPLACE NETWORK RULE openai_network_rule
  MODE = EGRESS
  TYPE = HOST_PORT
  VALUE_LIST = ('api.openai.com')
;

daanalytics

import re

# Regular expression to extract topics and descriptions
pattern = r'\d+\.\s(.*?):\s(.*?)(?=\d+\.|$)'
topics_and_descriptions = re.findall(pattern, openai_response, re.S)

for topic, description in topics_and_descriptions:
    print("Topic:", topic)
    print("Description:", description.strip())
    print("-----")

daanalytics

CREATE OR REPLACE MASKING POLICY conditional_mask_phone 
AS
(phone_number STRING, gender STRING, marital_status STRING) RETURNS STRING ->
CASE
   WHEN current_role() IN ('TASTY_ADMIN') 
        THEN phone_number
   WHEN current_role() IN ('TASTY_TEST_ROLE')    
        THEN CASE
               WHEN gender = 'Female'
                 AND marital_status = 'Single'

daanalytics

ALTER TAG pii_email_tag SET MASKING POLICY data_governance.email_mask
;

daanalytics

-- Apply Dynamic Masking Policy to a table column
ALTER TABLE IF EXISTS customer_loyalty MODIFY COLUMN e_mail SET MASKING POLICY data_governance.email_mask
;