Snowflakeの外部テーブルとマテリアライズドビューを用いて簡単にデータ分析環境を構築してみる

「カスタマーストーリー」を支えるツール特集

#ビッグデータ

#ビジネス

#Snowflake

さがら

2020.10.14

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

Snowflakeには、「外部テーブル」と呼ばれる外部のストレージに格納されたファイルに対してデータベース内のテーブルのようにクエリを実行できる機能と、「マテリアライズドビュー」と呼ばれる定義されたSELECT文のクエリ結果をSnowflakeのストレージ内に保存して通常のビューよりも高速にクエリを実行することができる機能があります。

この2つの機能を組み合わせて、Snowflake内にテーブルを構築せずとも、Snowflakeのコンピューティングリソースを用いて高速に外部ストレージのデータを分析できる環境を構築してみます。

せっかく環境を構築してみるので、おまけに動作速度の違いも検証してみます。

環境構築

使用データ

Citi Bikeというシェアサイクリングサービスのデータを使用します。運営元が公式に公開しているオープンデータです。

このデータはS3を介して公開されているため、このS3環境を外部ステージとして環境構築を行ってみます。

データベースの作成

まず、ベースとなるデータベースの作成が必要です。

※すでに作成済の、既存のデータベースを使用しても全く問題ありません。このブログでは「０から外部テーブル・マテリアライズドビューを構築する」説明のため、データベースの作成から手順を記載しております。

以下のコマンドを実行します。

CREATE DATABASE external_material_trial;

ウェアハウスの作成

クエリを実行するコンピューティングリソースを設定するために、ウェアハウスを作成します。サイズは後述の動作検証の前提条件も考慮してLARGEにしています。

以下のコマンドを実行します。

CREATE WAREHOUSE "COMPUTE_WH"
    SET WAREHOUSE_SIZE = 'LARGE'
    AUTO_SUSPEND = 60
    AUTO_RESUME = TRUE
    MIN_CLUSTER_COUNT = 1
    MAX_CLUSTER_COUNT = 1
    SCALING_POLICY = 'STANDARD';

コンテキストの設定

クエリを実行する際、「どのロールで、どのウェアハウスを用いて、どのデータベース・スキーマに対して、クエリを実行するのか」、を明確にするために、コンテキストの設定が必要です。

以下のコマンドを実行します。（「;」で区切られているため、3回に分けて実行が必要です！）

USE ROLE SYSADMIN;
USE WAREHOUSE COMPUTE_WH;
USE external_material_trial.public;

ファイルフォーマットの作成

対象のS3上に保存されているデータのフォーマットはCSVですが、一概にCSVといっても、区切り文字がカンマだったりタブだったり、囲み文字が二重引用符(ダブルクォーテーション)だったり何もなかったり、いろんなパターンがあります。

今回読み込む対象のデータは、区切り文字はカンマ、囲み文字が二重引用符のため、これを１つのファイルフォーマットとして定義しておきます。

以下のコマンドを実行します。

CREATE FILE FORMAT "EXTERNAL_MATERIAL_TRIAL"."PUBLIC".my_csv_format
    TYPE = 'CSV'
    COMPRESSION = 'AUTO'
    FIELD_DELIMITER = ','
    RECORD_DELIMITER = '\n'
    SKIP_HEADER = 0
    FIELD_OPTIONALLY_ENCLOSED_BY = '\042' /*ダブルクォーテーションを意味する*/
    DATE_FORMAT = 'AUTO'
    TIMESTAMP_FORMAT = 'AUTO'
    NULL_IF = ('');

外部ステージの設定

対象のS3のURLと、先程作成したファイルフォーマットを指定して、外部ステージを設定します。

CREATE STAGE citibike_trips url='s3://snowflake-workshop-lab/citibike-trips'
FILE_FORMAT = my_csv_format;

外部テーブルの構築

以下のコマンドを実行して、外部テーブルを作ります。

CREATE EXTERNAL TABLE ext_citibike_trips
  WITH location = @citibike_trips
  auto_refresh = true
  file_format = my_csv_format;

ここで注意が必要なのは、「外部テーブルは、VARIANT型の”VALUE”という１列に、JSON形式でデータが格納される」ということです。

文章だとわかりづらいので、試しに作成した外部テーブルに対して「SELECT *」クエリを実行してみると、下図のように”VALUE”という１列だけが表示され、各レコードはJSON形式で格納されていることがわかります。

このため、元のデータの形を維持してクエリを実行したい場合、以下のようにクエリを記述する必要があります。

SELECT 
  value:c1 AS tripduration,
  value:c2 AS starttime,    
  value:c3 AS stoptime
FROM "EXTERNAL_MATERIAL_TRIAL"."PUBLIC"."EXT_CITIBIKE_TRIPS"
LIMIT 10;

マテリアライズドビューの構築

前述の外部テーブルで、S3上のデータに対してクエリを実行することが出来るようになりましたが、よりクエリの動作速度を向上させるため、マテリアライズドビューを定義します。

以下のクエリを実行します。

CREATE MATERIALIZED VIEW mtr_citibike_trips AS
SELECT
  NULLIF(value:c1,'')::integer AS tripduration,
  NULLIF(value:c2,'')::timestamp AS starttime,    
  NULLIF(value:c3,'')::timestamp AS stoptime,
  NULLIF(value:c4,'')::integer AS start_station_id,
  value:c5::string AS start_station_name,
  NULLIF(value:c6,'')::float AS start_station_latitude,
  NULLIF(value:c7,'')::float AS start_station_longitude,
  NULLIF(value:c8,'')::integer AS end_station_id,
  value:c9::string AS end_station_name,
  NULLIF(value:c10,'')::float AS end_station_latitude,
  NULLIF(value:c11,'')::float AS end_station_longitude,
  NULLIF(value:c12,'')::integer AS bikeid,
  value:c13::string AS membership_type,
  value:c14::string AS usertype,
  NULLIF(value:c15,'')::integer AS birth_year,
  NULLIF(value:c16,'')::integer AS gender
FROM "EXTERNAL_MATERIAL_TRIAL"."PUBLIC"."EXT_CITIBIKE_TRIPS";

上記のクエリを見ると、「なんでこんなNULLIF関数いれてるんだ？」となる方もいると思います。

この理由は、端的に言うと「各数値型・日付型への型変換時のエラー回避対策のため」です。

何も対策しないとエラーが起こるのですが、このエラーの具体的な原因としては、参照している外部テーブルのVALUEという列はJSON形式でデータを格納していて、あるカラムのある行に何もデータが入っていない場合は「空文字（''）」として格納されており、これが原因で各数値型・日付型への型変換を行っており且つ１行でも空文字を持つカラムはすべてエラーを起こしてしまいます。

この解決策として、各数値型・日付型への型変換を行っているカラムはすべてNULLIF関数をいれて空文字をNULLに変換した上で型変換をしています。

ちなみに、元のCSVをテーブルとしてCOPYする際は、「NULL_IF = ('')」としたファイルフォーマットを用いれば、この問題は起きないです。

外部テーブルの場合にはファイルフォーマットで「NULL_IF = ('')」としていても、JSON形式で格納される際にはすべてNULLではなく空文字として保存されてしまう点が、少し辛いなーと感じました。

（といっても、外部テーブルの場合でもクエリにNULLIF関数を追加するだけで解決するんですけどね。）

検証：動作速度比較

検証内容

先程の環境構築でも使用した、Citi Bikeのデータを使用して、動作速度の違いを検証します。

このデータはAmazon S3上にデータが保存されているため、以下の3つの場合について動作速度を検証してみます。

外部テーブルを用いたとき
マテリアライズドビューを用いたとき
通常のテーブルを構築して用いたとき

前提条件

Snowflake トライアル環境（Enterprise Edition, AWS TOKYO region）
ウェアハウスサイズ：L、クラスター数は1で固定。
使用データ：上述の環境構築で用いたCiti BIkeのデータ（6150万レコード、オブジェクト数377個、圧縮後サイズ1.9GB）
計測時間はSnowflakeのGUIで表示される”期間”（実行時間のこと）を使用。

各クエリ実行前に、キャッシュをクリアしてから実行する（ウェアハウスの一時停止により、キャッシュクリアしています）

実行するクエリは以下の２種類

クエリその１

SELECT
  DATE_TRUNC('hour', starttime) AS "date",
  COUNT(*) AS "num trips",
  AVG(tripduration)/60 AS "avg duration (mins)", 
  AVG(HAVERSINE(start_station_latitude, start_station_longitude, end_station_latitude, end_station_longitude)) AS "avg distance (km)" 
FROM aaa /*各ビューまたはテーブル名を記入*/
GROUP BY 1 ORDER BY 1;

クエリその２

SELECT
    DAYNAME(starttime) AS "day of week",
    COUNT(*) AS "num trips"
FROM aaa /*各ビューまたはテーブル名を記入*/
GROUP BY 1 ORDER BY 2 DESC;

結果

検証内容	クエリその１	クエリその２
１．外部テーブルを用いたとき	42.1秒	10.67秒
２．マテリアライズドビューを用いたとき	2.01秒	0.865秒
３．通常のテーブルを構築して用いたとき	2.07秒	0.636秒

この結果を見ると、外部テーブルを直接用いた場合は、レコード数も多く377個ものオブジェクトに別れているため時間がかなりかかっています。しかし、マテリアライズドビューを用いるだけで、通常のテーブルと同等の速度を得られることがわかりました！

マテリアライズドビューの注意点

注意点

上記の速度比較結果を見ると、「マテリアライズドビューいいじゃん！これなら普通のビュー使わず全部マテリアライズドビューでいけるんじゃ？」となりかねないですが、そうも言えない注意点があります。

例えば、以下のような点です。（2020年10月14日時点の情報）

ストレージを使用するため、通常のビューと異なり作成して放っておくと料金が発生する
元となっているテーブルに更新が発生するとマテリアライズドビューも更新するため、更新の際にはクレジットを使用し料金が発生する
マテリアライズドビューを定義する際、WINDOW関数など使用できない関数や句がある。（詳細は公式Docを）

使いどころ

上記注意点を踏まえると、以下のような場面でマテリアライズドビューは使えるのかな、と感じました。

更新頻度が週１や月１など低めのテーブルを用いたビューをマテリアライズドビューに変更する（Snowflakeのキャッシュは最長24時間保持のため、週1など更新頻度が低い場合はこの方が効率が良い）
あるテーブルに対して特定の条件で絞り込んで使用することが多い場合、その絞り込み条件をマテリアライズドビューの定義に含んで作成する
S3などクラウドストレージにまとまって格納されたデータファイルを取り急ぎSnowflakeのリソースを用いてSQLで分析したいとき