1
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

エクセルマクロ(Excel-VBA)でSnowflakeからSQLでデータを取得する

Posted at

Excel-VBAでSnowflakeからSQLでデータを取得する

↓ この記事ではこんな感じのツールを作ります。マクロの前提知識が無くても動くところまで行くと思います。
image.png

はじめに

Excel-VBAでSnowflakeに接続する記事がなかったので書いてみました。
初めてQiitaで記事を投稿します。読みにくいところあるかと思いますがご容赦ください。

全体の流れ

1.SnowflakeODBCドライバーのインストール、DSN設定
2.Excel準備、Excel-VBAを記述、接続テスト

1.Snowflake ODBCドライバーのインストール ~ DSN設定

作業の流れ
1.1 インストーラのダウンロード
1-2 インストールモジュールの実行
1-3 ODBCでのテスト接続&DSN設定

1-1. インストーラのダウンロード

以下のsnowflake Developersページにて、「ODBC FOR WINDOWS」を選択すると最新のインストールモジュールが取得できる。
(ダウンロードサイト)
https://developers.snowflake.com/odbc/

image.png

1-2. インストールモジュールの実行

ダウンロードしたインストールモジュール(msiファイル)をエクスプローラでダブルクリック等をして実行。
image.png
 ↓ Setup Wizardが表示されるので「Next」押下
wizard1.PNG
 ↓ (インストールPathを変更したい場合は直接編集して)「Next」押下
wizard2.PNG
 ↓「Install」押下
wizard3.PNG
 ↓「Finish」押下
wizard4.PNG

1-3. ODBCでのテスト接続&DSN設定

画面左下の虫眼鏡からODBCを検索し、起動
image.png

 ↓ ODBCデータソースアドミニストレータ の画面が表示されるので「追加」を押下
image.png

 ↓ リスト下部の「SnowflakeDSIIDriver」を選択し、「完了」を押下
image.png

 ↓
以下の接続設定画面にて以下を入力して「Test..」を実行
image.png

設定箇所 設定内容
Data Source snowflake-test
User (SnowflakeユーザID)
Password (Snowflakeパスワード)
Server (サーバ名)例:アカウントIDがAB12345(※)でAWS東京リージョンの場合
AB12345.ap-northeast-1.aws.snowflakecomputing.com
※アカウントIDが不明な場合、Snowsight等でselect current_account();を打てば確認できます。(大抵の場合はSnowsightのURLの一部にアカウントIDが埋め込まれているのでわかると思います。)

 ↓ 特に問題なければ以下のようにSUCCESSと表示される
image.png

 ↓ 元の画面に戻り、OKを押してDSN登録完了
image.png

 ↓ 「snowflake-test」が追加されたことを確認
image.png

(参考URL)
Windows用 ODBC ドライバーのインストールおよび構成
https://docs.snowflake.com/ja/user-guide/odbc-windows.html#step-1-install-the-odbc-driver

2.Excel準備、Excel-VBAを記述、接続テスト

作業の流れ
2-1. マクロが有効なBOOKの作成
2-2. Excelシートの準備
2-3. マクロの設定
2-4. テスト実行!

2-1. マクロが有効なBOOKの作成

Excelで新規ブックを作成⇒名前を付けて保存からファイルの種類をExcel マクロ有効ブック(*.xlsm)を指定し、適当なファイル名を付けて保存
image.png

2-2. Excelシートの準備

今回はB3セルにSQLを記入、C7セル以降に結果を表示するツールを作成します。

■ 2-2-1. Excelシートの設定

以下を実施し、ツール画面を作成する
・B列の幅を広げる
・3行目の高さを広げる
・B2セルに「SQL」と記入
・B7セルに「SQL結果⇒」と記入
(設定例)
image.png

■ 2-2-2. 実行用ボタンを設定する

開発タブ(※)から「挿入」⇒ActiveXコントロールの「コマンドボタン」を選択し、B4セルあたりに配置
image.png

※開発タブが表示されていない場合は以下を参考にして表示してから実行してください。

 ↓
image.png
 ↓
配置したコマンドボタンを右クリック⇒「プロパティ」でボタン名等を設定する
image.png
(設定箇所)

項目名
(オブジェクト名) Button_SQL実行 など
Caption SQL実行 など

 ↓ 以下のようにボタン名が変更されます
image.png

2-3. マクロの設定

2-3.1 ボタン押下時のプロシージャを作りつつExcel-VBA Editorの起動

開発タブにて「デザインモード」がオンになっていることを確認(なっていない場合はクリックして切り替える)
image.png
先ほど作成した「SQL実行」ボタンをダブルクリック
image.png
 ↓ 以下のようにExcel-VBA用のエディタ画面が表示されます
image.png

2-3-2. 参照設定

「ツール」タブより、「参照設定」を選択、以下2つのライブラリにチェックを入れて「OK」を押下
・Microsoft ActiveX Data Object 6.1 Library
・Microsoft ActiveX Data Object Recordset 6.0 Library
image.png

2-3-3. VBA記述

Editorに表示されている以下の部分を↓の変更後ソースコードに置き換えます。

Private Sub Button_SQL実行_Click()

End Sub

※ ソースコード先頭の接続設定にてuid(ユーザID)、pwd(パスワード)、role、warehouseの部分をご自身の設定に変更してください。
roleとwarehouseがわからない場合は、一度snowsightのワークシート画面右上にて以下を確認ください。
image.png

(変更後ソースコード)

Private Sub Button_SQL実行_Click()

    ' Snowflake接続設定
    Dim conn_str As String
    conn_str = "Provider=MSDASQL;" & _
               "DSN=snowflake-test;" & _
               "uid=Administrator;" & _   '★★★変更箇所★★★
               "pwd=xxxxxx;" & _          '★★★変更箇所★★★
               "ROLE=ACCOUNTADMIN;" & _   '★★★変更箇所★★★
               "WAREHOUSE=WAREHOUSE_XS;"  '★★★変更箇所★★★
 
    ' Snowflake接続
    Dim conn As New ADODB.Connection
    Set conn = New ADODB.Connection
    conn.ConnectionTimeout = 900
    conn.CommandTimeout = 900
    conn.Open conn_str 'コネクション取得
 
    conn.CursorLocation = adUseClient
 
    '*** SQL実行準備
 
    'B3セルからSQL文取得
    Dim sql_text As String
    sql_text = Range("B3").Value
 
    'command準備
    Dim adoCmd As New ADODB.Command
    Set adoCmd.ActiveConnection = conn
    adoCmd.CommandType = adCmdText
    adoCmd.CommandText = sql_text
 
'    'SQLにパラメータマーカー(例:where column1 = ?)を設定する場合、
'    '以下のような感じで記述することができます。
'    '実行パラメータ取得
'    Dim test_param As String
'    test_param = Range("B9").Value
'
'    '実行パラメータのセット
'    Dim param As ADODB.Parameter
'    Set param = New ADODB.Parameter
'    Set param = adoCmd.CreateParameter(Str(1), adVarChar, adParamInput, Len(test_param) * 3, test_param)
'    adoCmd.Parameters.Append param
 
    '結果セット受け取り準備
    Dim recordset As New ADODB.recordset
    Set recordset = New ADODB.recordset

    'SQLを実行しrecordsetに格納
    Set recordset = adoCmd.Execute() ' SQL実行
 
    '*** 結果をC7セル以降に張り付け
    Dim out_row As Integer '出力位置(行)
    Dim out_col As Integer '出力位置(列)
    Dim i As Integer
    out_row = 7 '7行目
    out_col = 3 'C列
 
    ' C7セルに結果行数の表示
    Cells(out_row, out_col).Value = "結果: " & recordset.RecordCount & " 件"
 
    ' C8の行に結果のヘッダ部分を反映(フィールド数分出力)
    For i = 0 To recordset.Fields.Count - 1
        Cells(out_row + 1, out_col + i).Value = recordset(i).Name
    Next i
 
    ' C9セル以降に結果部分の反映
    Cells(out_row + 2, out_col).CopyFromRecordset recordset
 
 
    '*** コネクション切断
    recordset.Close
    conn.Close
 
    ' 後片付け
    Set conn = Nothing
    Set recordset = Nothing
    Set adoCmd = Nothing
 
    MsgBox ("実行完了!")
End Sub

(参考URL)
接続パラメータ
https://docs.snowflake.com/ja/user-guide/odbc-parameters.html#connection-parameters

2-4. テスト実行!

実行するときは開発タブの「デザインモード」をオフにします
image.png

B3セルに適当なSELECT文を入力します(事故防止のためにlimit句はつけておきましょう)


select * 
from snowflake.information_schema.tables
limit 100
!悲しい事故防止が起きないように以下実行前に一度Excelを保存しましょう。

「SQL実行」ボタンを押します
 ↓
以下のようにC7セル以降にSQL実行結果が表示されます
image.png

おわりに

ソースコードの解説の代わりにコメントを多めに入れてみました。
Excelの別のセルでWHERE句条件を入れたい場合でパラメータマーカー(where column1 = ?)を使用したい場合の例もコメントに入れておいたので興味があればやってみてください。
尚、当記事執筆時点(2022/11/13)ではパラメータで日本語文字列が入る際、記述例のようにパラメータの桁数を入力文字の3倍等に設定しないとパラメータの文字が途中で切れた状態でSQLが実行されてしまいますので注意が必要です。

それでは良いSnowflakeライフを!

1
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
1
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?