SQL 

CLIクライアント(SnowSQL)を使ってSnowflakeにアクセスしてみよう

Snowflakeで今日拝なびら(ちゅう うがなびら=こんにちは)。
ちゅらデータshinyashikiです!

前回「SnowflakeにPythonから繋いでみよう!」ではPythonからSnowflakeへの繋ぎを行いました。
今回はSnowflakeのCLIツール(SnowSQL)を使って可視化するためのデータを登録してみます。

前回、今回、そして次回と3部構成でご紹介いたします。
SnowflakeにPythonから繋いでみよう!←前回掲載記事
CLIクライアント(SnowSQL)を使ってSnowflakeにアクセスしてみよう ← 本記事
SnowflakeとPythonでデータ可視化(次回掲載予定:1/8)

はじめに
使用するSnowflake環境について
ユーザや仮想ウェアハウス、データベースといったSnowflakeのリソースの作成方法については本記事では省略します。必要に応じて事前にご用意ください。

なお、本記事では以下のリソースが存在すると仮定します。
アカウント: sample123.ap-northeast-1.aws
ユーザ: TRAINING_SHINYASHIKI
仮想ウェアハウス: SHINYASHIKI_TRAINING_WH
データベース: SHINYASHIKI_TRAINING_DB

検証で使用する端末について
本記事の検証端末としてMacを使用しています。

$ sw_vers
ProductName:	Mac OS X
ProductVersion:	10.15.7
BuildVersion:	19H2

SnowSQLのインストール

いろんなやり方のインストールがありますが、今回はHomebrewを使ったインストールをします。
Homebrewを使ったインストール
Homebrewが導入済みのMacOSであれば、以下のようにbrew caskコマンドでインストールできます。

$ brew cask install snowflake-snowsql
…
installer: Package name is Snowflake SnowSQL
installer: Installing at base path /
installer: The install was successful.
🍺  snowflake-snowsql was successfully installed!

インストールが成功したようです。

確認

では早速使ってみます。
まずは手っ取り早くSnowflakeのバージョンを確認してみます。

$ snowsql -v
zsh: command not found: snowsql

ん?実行に失敗しましたね。
どうやら実行可能ファイル「snowsql」のPATHが通っていないようです。

エイリアスの設定

実行可能ファイル「snowsql」の場所を確認したところ、 /Applications/SnowSQL.app/Contents/MacOS/snowsql にインストールされています。
zsh用の設定として.zshrcに以下のようにエイリアスを設定します。

$ cat ~/.zshrc
alias snowsql=/Applications/SnowSQL.app/Contents/MacOS/snowsql

改めてsnowsqlのバージョンを確認してみます。

$ snowsql -v
Version: 1.2.10

バージョンが表示されましたね!
無事インストールされたことが確認できました。

データ登録の準備

登録するデータ(CSV)を取得する

それではSnowflakeに登録するデータを準備します。
今回は機械学習やデータ可視化でよく使われるアヤメの花のデータ「iris.data」を使います。
こちらからダウンロードします。
ダウンロードした「iris.data」をテキストエディタなどで開いて確認してみると、以下のようなCSV形式となっています。

$ cat ~/Downloads/iris.data
5.1,3.5,1.4,0.2,Iris-setosa
4.9,3.0,1.4,0.2,Iris-setosa
4.7,3.2,1.3,0.2,Iris-setosa
...

ちなみに各列の意味については以下の通りです(参考: Iris Data Set
1列目: sepal length in cm がく辺の長さ(cm)
2列目: sepal width in cm がく辺の幅(cm)
3列目: petal length in cm 花びらの長さ(cm)
4列目: petal width in cm 花びらの幅(cm)
5列目: class 種類

ダウンロードした「iris.data」は後ほど使うので、いったん/tmpディレクトリに移動しておきます。

$ mv ~/Downloads/iris.data /tmp
$ ls /tmp/iris.data 
/tmp/iris.data

Snowflakeにログインする

以下のコマンドでSnowflakeにログインします。

$ snowsql -a <アカウント名> -u <ユーザ名> -w <仮想ウェアハウス名>

ログイン時にパスワードを求められるので、パスワードを入力して接続できたのを確認します。

$ snowsql -a sample123.ap-northeast-1.aws -u TRAINING_SHINYASHIKI -w SHINYASHIKI_TRAINING_WH
Password: 
* SnowSQL * v1.2.10
Type SQL statements or !help
TRAINING_SHINYASHIKI#SHINYASHIKI_TRAINING_WH@(no database).(no schema)>

テーブルを作成する

データを登録するテーブルを作っていきます。
まずは以下のコマンドで対象となるデータベースを選択します。

> use <データベース名>

今回はデータベース「SHINYASHIKI_TRAINING_DB」があると仮定して進めていきます。

TRAINING_SHINYASHIKI#SHINYASHIKI_TRAINING_WH@(no database).(no schema)>
use SHINYASHIKI_TRAINING_DB;
+----------------------------------+                                            
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+
1 Row(s) produced. Time Elapsed: 0.236s
TRAINING_SHINYASHIKI#SHINYASHIKI_TRAINING_WH@SHINYASHIKI_TRAINING_DB.PUBLIC>

続いて先ほどのアヤメの花のデータ「iris.data」を格納するテーブルを作成します。
テーブル作成はMySQLやPostgreSQLなどと同様、CREATE TABLE文を使います。
なお、テーブルの列数、位置、およびデータ型をiris.dataのフィールドに対応した状態で作成します。

TRAINING_SHINYASHIKI#SHINYASHIKI_TRAINING_WH@SHINYASHIKI_TRAINING_DB.PUBLIC>create or replace table iris_data (                                                                              sepal_length number(2,1) ,                                                                             sepal_width number(2,1) ,
petal_length number(2,1) ,
petal_width number(2,1) ,
class string(20)
);
+---------------------------------------+                                       
| status                                |
|---------------------------------------|
| Table IRIS_DATA successfully created. |
+---------------------------------------+
1 Row(s) produced. Time Elapsed: 1.102s

実際にテーブルが作成されていることを確認します。

TRAINING_SHINYASHIKI#SHINYASHIKI_TRAINING_WH@SHINYASHIKI_TRAINING_DB.PUBLIC>desc table iris_data;
+--------------+-------------+--------+-------+---------+-------------+------------+-------+------------+---------+
| name         | type        | kind   | null? | default | primary key | unique key | check | expression | comment |
|--------------+-------------+--------+-------+---------+-------------+------------+-------+------------+---------|
| SEPAL_LENGTH | NUMBER(2,1) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
| SEPAL_WIDTH  | NUMBER(2,1) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
| PETAL_LENGTH | NUMBER(2,1) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
| PETAL_WIDTH  | NUMBER(2,1) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
| CLASS        | VARCHAR(20) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
+--------------+-------------+--------+-------+---------+-------------+------------+-------+------------+---------+
5 Row(s) produced. Time Elapsed: 2.014s

これでデータを登録する準備が整いました。

データ登録

Snowflakeではデータロード機能を使ってCSVなどローカルにあるファイルをSnowflake上のテーブルへ登録することができます。

データを登録する手順としては以下の流れとなります

1.PUTコマンドでステージと呼ばれる領域にローカルのファイルをアップロード
ステージには外部ステージと内部ステージがあり、今回は内部ステージのテーブルステージと呼ばれる領域を利用します。ステージについての詳細はこちらを参考ください

2.COPYコマンドでステージにあるデータをテーブルにコピー

データをアップロードする

まずはPUTコマンドを使ってローカルファイル「iris.data」をテーブルステージにアップロードします。

> put file://<ローカルファイルのパス> @データベース名.スキーマ名.%テーブルステージ名

・ローカルファイルパス: /tmp/iris.data
・データベース: SHINYASHIKI_TRAINING_DB
・スキーマ: public
・テーブルステージ: iris_data

TRAINING_SHINYASHIKI#SHINYASHIKI_TRAINING_WH@SHINYASHIKI_TRAINING_DB.PUBLIC>put file:///tmp/iris.data @SHINYASHIKI_TRAINING_DB.public.%iris_data;
iris.data_c.gz(0.00MB): [##########] 100.00% Done (0.270s, 0.00MB/s).           
+-----------+--------------+-------------+-------------+--------------------+--------------------+----------+---------+
| source    | target       | source_size | target_size | source_compression | target_compression | status   | message |
|-----------+--------------+-------------+-------------+--------------------+--------------------+----------+---------|
| iris.data | iris.data.gz |        4551 |         852 | NONE               | GZIP               | UPLOADED |         |
+-----------+--------------+-------------+------

アップロードに成功しました。
それではLISTコマンドで実際にアップロードされたか確認します。

> list @データベース名.スキーマ名.%テーブルステージ名
TRAINING_SHINYASHIKI#SHINYASHIKI_TRAINING_WH@SHINYASHIKI_TRAINING_DB.PUBLIC>list @SHINYASHIKI_TRAINING_DB.public.%iris_data;
+--------------+------+----------------------------------+-------------------------------+
| name         | size | md5                              | last_modified                 |
|--------------+------+----------------------------------+-------------------------------|
| iris.data.gz |  864 | 1234567890abcdefghijklmnopqrstuv | Tue, 10 Nov 2020 01:53:21 GMT |
+--------------+------+----------------------------------+-------------------------------+
1 Row(s) produced. Time Elapsed: 0.771s

正常にアップロードされていますね。
それではデータをロードしていきます。

データをロードする

ステージングにアップロードできたので、今度はCOPYコマンドを使ってテーブルにデータをコピーします。

> copy into <テーブル名> @%テーブルステージ名
TRAINING_SHINYASHIKI#SHINYASHIKI_TRAINING_WH@SHINYASHIKI_TRAINING_DB.PUBLIC>copy into iris_data from @%iris_data;
100068 (22000): End of record reached while expected to parse column '"IRIS_DATA"["SEPAL_WIDTH":2]'
  File '@IRIS_DATA/iris.data.gz', line 151, character 1
  Row 151, column "IRIS_DATA"["SEPAL_WIDTH":2]
  If you would like to continue loading when an error is encountered, use other values such as 'SKIP_FILE' or 'CONTINUE' for the ON_ERROR option. For more information on loading options, please run 'info loading_data' in a SQL client.

おや?エラーになりました。
何やら「iris.data」の151行目のデータがおかしいようなので確認してみます。

$ sed -n 151p /tmp/iris.data 


なるほど、空行になっているようです。
空行を削除して改めてアップロードしなおします。

アップロードし直す前にREMOVEコマンドを使って先ほどアップロードしたファイルを削除します。

> rm @データベース名.スキーマ名.%テーブルステージ名
TRAINING_SHINYASHIKI#SHINYASHIKI_TRAINING_WH@SHINYASHIKI_TRAINING_DB.PUBLIC>rm @SHINYASHIKI_TRAINING_DB.public.%iris_data;
+--------------+---------+                                                      
| name         | result  |
|--------------+---------|
| iris.data.gz | removed |
+--------------+---------+
1 Row(s) produced. Time Elapsed: 0.813s

LISTコマンドを使ってテーブルステージに「iris.data」が削除されたか確認します。

TRAINING_SHINYASHIKI#SHINYASHIKI_TRAINING_WH@SHINYASHIKI_TRAINING_DB.PUBLIC>list @SHINYASHIKI_TRAINING_DB.public.%iris_data;
+------+------+-----+---------------+                                           
| name | size | md5 | last_modified |
|------+------+-----+---------------|
+------+------+-----+---------------+
0 Row(s) produced. Time Elapsed: 0.233s

削除されたのを確認できたので、改めてアップロードしなおします。

TRAINING_SHINYASHIKI#SHINYASHIKI_TRAINING_WH@SHINYASHIKI_TRAINING_DB.PUBLIC>put file:///tmp/iris.data @SHINYASHIKI_TRAINING_DB.public.%iris_data;
iris.data_c.gz(0.00MB): [##########] 100.00% Done (0.257s, 0.00MB/s).           
+-----------+--------------+-------------+-------------+--------------------+--------------------+----------+---------+
| source    | target       | source_size | target_size | source_compression | target_compression | status   | message |
|-----------+--------------+-------------+-------------+--------------------+--------------------+----------+---------|
| iris.data | iris.data.gz |        4550 |         851 | NONE               | GZIP               | UPLOADED |         |
+-----------+--------------+-------------+-------------+--------------------+--------------------+----------+---------+
1 Row(s) produced. Time Elapsed: 4.960s

LISTコマンドを使ってアップロードされたか確認します。

TRAINING_SHINYASHIKI#SHINYASHIKI_TRAINING_WH@SHINYASHIKI_TRAINING_DB.PUBLIC>list @SHINYASHIKI_TRAINING_DB.public.%iris_data;
+--------------+------+----------------------------------+-------------------------------+
| name         | size | md5                              | last_modified                 |
|--------------+------+----------------------------------+-------------------------------|
| iris.data.gz |  864 | 1234567890abcdefghijklmnopqrstuv | Tue, 10 Nov 2020 02:12:36 GMT |
+--------------+------+----------------------------------+-------------------------------+
1 Row(s) produced. Time Elapsed: 0.797s

ではもう一度COPYコマンドを使ってテーブルにデータをコピーします。

TRAINING_SHINYASHIKI#SHINYASHIKI_TRAINING_WH@SHINYASHIKI_TRAINING_DB.PUBLIC>copy into iris_data from @%iris_data;
+--------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
| file         | status | rows_parsed | rows_loaded | error_limit | errors_seen | first_error | first_error_line | first_error_character | first_error_column_name |
|--------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------|
| iris.data.gz | LOADED |         150 |         150 |           1 |           0 | NULL        |             NULL |                  NULL | NULL                    |
+--------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
1 Row(s) produced. Time Elapsed: 3.537s

コピーに成功しましたね!

データを確認する

ではSELECT文でデータが登録されているか確認します。

TRAINING_SHINYASHIKI#SHINYASHIKI_TRAINING_WH@SHINYASHIKI_TRAINING_DB.PUBLIC>select * from iris_data;
+--------------+-------------+--------------+-------------+-----------------+   
| SEPAL_LENGTH | SEPAL_WIDTH | PETAL_LENGTH | PETAL_WIDTH | CLASS           |
|--------------+-------------+--------------+-------------+-----------------|
|          5.1 |         3.5 |          1.4 |         0.2 | Iris-setosa     |
|          4.9 |         3.0 |          1.4 |         0.2 | Iris-setosa     |
|          4.7 |         3.2 |          1.3 |         0.2 | Iris-setosa     |
|          4.6 |         3.1 |          1.5 |         0.2 | Iris-setosa     |
…
|          6.2 |         3.4 |          5.4 |         2.3 | Iris-virginica  |
|          5.9 |         3.0 |          5.1 |         1.8 | Iris-virginica  |
+--------------+-------------+--------------+-------------+-----------------+
150 Row(s) produced. Time Elapsed: 0.880s

ちゃんとデータが登録されてますね!
これで可視化を行う準備が整いました。

次回予告

前回そして今回で、PythonからSnowflakeへアクセスする準備と実際に使用するデータの登録まで行いました。
次回はついにPythonを使ってSnowflakeのデータを可視化してみます。
ではまた!
うにげーさびら!(よろしくお願いします!)



DATUM STUDIOは、クライアントの事業成長と経営課題解決を最適な形でサポートする、データ・ビジネスパートナーです。
データ分析の分野でお客様に最適なソリューションをご提供します。まずはご相談ください。

このページをシェアする: