Zenn
スタフェステックブログPublicationへの投稿
🏂

SnowflakeとChatGPTを組み合わせてワークシート上で自然言語からサンプルクエリを生成する

koonagi (kohei yamazaki)
2023/12/10に公開
Python
Snowflake
OpenAI
tech

こんにちは！ @koonagiです。
振り返るとインフラやデータ基盤に関連するブログを今年は10本ほど書いてました。
ブログに書けてない学んだことが今年は他にもたくさんあった一年だったので、来年はもっとアウトプットしていきたし。

さて、自分の今年の締めのブログとして内容としては、今年の自分の技術的な関心が強かったSnowflakeとChatGPTを組み合わせた内容にしようと思います。

できたもの

以下のページを参考にして、SnowflakeのUDFを使って、SQLで算出したい内容とテーブル情報を入れるとサンプルSQLが返ってくるもの作ってみた。

https://medium.com/snowflake/snowflake-unlock-the-power-of-openai-using-snowpark-ebfb19437087

構成としては、External Accessを使ってUDFからOpenAIのAPIを実行して、結果を返すものとなっています。

いきなり結果と所感

  • 生成されるSQLの精度についてはまちまち。たまに存在しないカラムや条件式が返ってくる。
  • 今回はテーブル情報のみをインプットに入れましたが、テーブルの実際の中身を何件かいれると精度は上がるかも。(セキュリティへの考慮は必須
  • いちいちChatGPT開かなくても、ワークシート上でサンプルSQLを生成できるようになったので、簡単なSQL調べるのが楽になった。
  • External Accessを使えば外部APIなんでも叩けるので、OpenAIと他のAPIを組み合わせればUDFの幅は広がりそう。
  • テーブル情報を手動で今回インプットとしていれているのですが、UDFからSnowflakeのセッションを取得する方法が分からなかったため。セッション取得できれば、テーブル名から自動でテーブルの情報を取得することが可能になるので、ちょっと面倒なテーブル情報入れる手間が省けそう。
  • プロンプトはかなり雑なので、改善の余地しかない。

利用例

インプット

select chatgpt_sql('''
SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORYから直近1日で実行時間の長いクエリトップ10を出して
name	type	kind
QUERY_ID	VARCHAR(16777216)	COLUMN
QUERY_TEXT	VARCHAR(16777216)	COLUMN
DATABASE_ID	NUMBER(38,0)	COLUMN
DATABASE_NAME	VARCHAR(16777216)	COLUMN
SCHEMA_ID	NUMBER(38,0)	COLUMN
SCHEMA_NAME	VARCHAR(16777216)	COLUMN
<略>
''');

アウトプット

SELECT 
    QUERY_ID,
    QUERY_TEXT,
    DATABASE_NAME,
    SCHEMA_NAME,
    QUERY_TYPE,
    USER_NAME,
    ROLE_NAME,
    WAREHOUSE_NAME,
    TOTAL_ELAPSED_TIME
FROM 
    SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE 
    START_TIME >= DATEADD(DAY, -1, CURRENT_TIMESTAMP())
ORDER BY 
    TOTAL_ELAPSED_TIME DESC
LIMIT 10;

出力されたクエリは正常に叩けます！

やり方

// スキーマとウェアハウスは事前につくっておく
USE ROLE ACCOUNTADMIN;
USE DEMODB.LLM; 
USE WAREHOUSE DEMO_WH;

-- ネットワーク作成用のユーザーの作成 ＆ 権限付与
CREATE ROLE if not exists   network_admin ;
GRANT CREATE INTEGRATION ON ACCOUNT TO ROLE network_admin;
GRANT CREATE NETWORK RULE ON SCHEMA demodb.llm TO ROLE network_admin;
GRANT CREATE SECRET ON SCHEMA demodb.llm TO ROLE network_admin;
GRANT USAGE on DATABASE demodb to ROLE network_admin ;
GRANT USAGE on SCHEMA demodb.llm  to ROLE network_admin ;
GRANT USAGE on WAREHOUSE demo_wh to ROLE network_admin;
GRANT ROLE network_admin to ROLE SYSADMIN;

-- ネットワークルールの設定 openaiへのアクセスを許可
USE ROLE network_admin;
USE WAREHOUSE DEMO_WH;
CREATE OR REPLACE NETWORK RULE web_access_rule
  MODE = EGRESS
  TYPE = HOST_PORT
  VALUE_LIST = ('api.openai.com'); 

-- OpenAIのSecret情報を格納
USE ROLE SYSADMIN;
USE DEMODB.LLM;
USE WAREHOUSE DEMO_WH;
CREATE OR REPLACE SECRET sf_openapi_key
    TYPE = password
    USERNAME = 'gpt-3.5-turbo'  -- model to use
    PASSWORD = '<API KEY>'; -- API key

-- External Accessの作成
CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION external_access_int
  ALLOWED_NETWORK_RULES = (web_access_rule)
  ALLOWED_AUTHENTICATION_SECRETS = (sf_openapi_key)
  ENABLED = true;

-- OpneAI API叩くUDF作成

CREATE OR REPLACE FUNCTION chatgpt_sql(query varchar)
RETURNS STRING
LANGUAGE PYTHON
RUNTIME_VERSION = 3.8
HANDLER = 'get_sql_query_from_natural_language'
EXTERNAL_ACCESS_INTEGRATIONS = (external_access_int)
SECRETS = ('openai_key' = sf_openapi_key )
PACKAGES = ('openai','snowflake-snowpark-python')
AS
$$
import _snowflake
import openai
from snowflake.snowpark import Session
import snowflake.snowpark as snowpark

def get_sql_query_from_natural_language(query):
    model = "gpt-3.5-turbo"

    # 認証情報取得
    sec_object = _snowflake.get_username_password('openai_key')
    openai.api_key = sec_object.password

    
    # インプットからテーブル名を抽出
    query_prompt = f"Extract the table name from this query: '{query}'"

    response = openai.ChatCompletion.create(
        model=model,
        messages=[{"role": "user", "content": query_prompt}],
        temperature=0
    )
    table_name = response.choices[0].message["content"]

    
    # インプットからスキーマ情報を取得
    query_prompt = f"Extract the schema info from this query: '{query}'"
    response = openai.ChatCompletion.create(
        model=model,
        messages=[{"role": "user", "content": query_prompt}],
        temperature=0
    )
    schema_info = response.choices[0].message["content"]

    
    # SQLクエリを生成
    query_prompt = f'''
    Given a table {table_name} with schema {schema_info }, {query}

    Response Format: SQL
    No other format is required except SQL.
    
    '''
    response = openai.ChatCompletion.create(
        model=model,
        messages=[{"role": "user", "content": query_prompt}],
        temperature=0
    )
    sql_query = response.choices[0].message["content"]
    return sql_query
$$;

おまけ

SnowflakeのQA用のUDFも作ってみた。
ワークシート上で簡単な調べ物は簡潔できるようになったので、めっちゃ楽！！

UDF

CREATE OR REPLACE FUNCTION chatgpt_qa(query varchar)
RETURNS STRING
LANGUAGE PYTHON
RUNTIME_VERSION = 3.8
HANDLER = 'get_snowflake_answer'
EXTERNAL_ACCESS_INTEGRATIONS = (external_access_int)
SECRETS = ('openai_key' = sf_openapi_key )
PACKAGES = ('openai')
AS
$$
import _snowflake
import openai
def get_snowflake_answer(question):
    # Snowflakeの認証情報を取得
    sec_object = _snowflake.get_username_password('openai_key')
    openai.api_key = sec_object.password

    # Snowflakeに関連する質問をOpenAIに送信
    messages = [{"role": "user", "content": f"Snowflake: {question}"}]
    model = "gpt-3.5-turbo"
    response = openai.ChatCompletion.create(model=model, messages=messages, temperature=0)

    # OpenAIからの回答を取得
    answer = response.choices[0].message["content"]

    return answer
$$;

最後に

External Accessが利用できるようになったので、OpenAIとの連携を始めとしてSnowflakeの幅がかなり広がったように感じました。設定も簡単なので、すぐに利用できます。
Snowflakeに貯めたデータの要約やそこからのアイデア抽出とかOpenAIとの組み合わせは夢がありますね〜。

来年は自社のデータ駆動をより邁進させて行くために奔走することになると思うので、Snowflakeをより理解して活用したり、社内へスキトラしたり頑張っていくぞ〜〜。
また来年よろしくお願いします。

スタフェステックブログ
スタフェステックブログPublication

スターフェスティバル株式会社のエンジニアが運営しています！

Discussion