--- title: 外部テーブルを定義してAmazon S3にあるTSVファイルをSnowflakeでテーブルライクに扱ってみた description: Amazon S3に配置されたTSVファイルを外部テーブルを使ってSnowflakeでテーブルライクに扱えるように試してみました。 image: https://knowledge.insight-lab.co.jp/hubfs/%E5%AE%9A%E7%BE%A9-png.png --- ![INSIGHT LAB](https://knowledge.insight-lab.co.jp/hs-fs/hubfs/INSIGHT%20LAB_%E6%96%87%E5%AD%97%E3%81%AE%E3%81%BF_WHT-1.png?width=400&height=110&name=INSIGHT%20LAB_%E6%96%87%E5%AD%97%E3%81%AE%E3%81%BF_WHT-1.png) ![INSIGHT LAB](https://knowledge.insight-lab.co.jp/hs-fs/hubfs/INSIGHT%20LAB_%E6%96%87%E5%AD%97%E3%81%AE%E3%81%BF.png?width=400&height=110&name=INSIGHT%20LAB_%E6%96%87%E5%AD%97%E3%81%AE%E3%81%BF.png) [![お問い合わせ](https://hubspot-no-cache-na2-prod.s3.amazonaws.com/cta/default/5935783/c64a7bb0-e32a-499b-851b-b4e7a66da275.png)](https://hubspot-cta-redirect-na2-prod.s3.amazonaws.com/cta/redirect/5935783/c64a7bb0-e32a-499b-851b-b4e7a66da275) - [Snowflakeとは](https://service.insight-lab.co.jp/snowflake) - [SNOWFLAKEナレッジ](https://knowledge.insight-lab.co.jp/snowflake) - [お役立ち資料](https://download.insight-lab.co.jp/snowflake/download) ###### 4 分で読むことができます # [外部テーブルを定義してAmazon S3にあるTSVファイルをSnowflakeでテーブルライクに扱ってみた](https://knowledge.insight-lab.co.jp/snowflake/s3-snowflake-external-table-0) 執筆者 [Chris Hirasawa](https://knowledge.insight-lab.co.jp/snowflake/author/chris) 更新日時 2020年12月13日 ###### Topics: [DWH](https://knowledge.insight-lab.co.jp/snowflake/tag/dwh) [s3](https://knowledge.insight-lab.co.jp/snowflake/tag/s3) [snowflake](https://knowledge.insight-lab.co.jp/snowflake/tag/snowflake) [EXTERNAL TABLE](https://knowledge.insight-lab.co.jp/snowflake/tag/external-table) [外部テーブル](https://knowledge.insight-lab.co.jp/snowflake/tag/外部テーブル) ![](https://knowledge.insight-lab.co.jp/hubfs/%E5%AE%9A%E7%BE%A9-png.png) 目次 本記事は[Snowflake Advent Calendar 2020](https://adventar.org/calendars/5085)の13日目の記事となります。 ## 外部テーブルの紹介 外部テーブル(EXTERNAL TABLE)とは、オブジェクトストレージサービスに配置したデータをSnowflakeから直接参照可能にするテーブルのことです。 現在外部テーブルとして連携・利用できるストレージサービスは下記です。 - **Amazon S3** ![](https://knowledge.insight-lab.co.jp/hs-fs/hubfs/kisspng-amazon-com-amazon-s3-amazon-web-services-amazon-el-amazon-5ada03da5749f0-5706537815242372743576-png-1.png?width=80&name=kisspng-amazon-com-amazon-s3-amazon-web-services-amazon-el-amazon-5ada03da5749f0-5706537815242372743576-png-1.png) - **Google Cloud Storage** **![](https://knowledge.insight-lab.co.jp/hs-fs/hubfs/1200px-Google-Cloud-Storage-Logo-png.png?width=80&name=1200px-Google-Cloud-Storage-Logo-png.png)** - **Microsoft Azure Blob Storage** **![](https://knowledge.insight-lab.co.jp/hs-fs/hubfs/blob-storage-png-1.png?width=80&name=blob-storage-png-1.png)**   実態は、**ファイルをPARSEしたJSONオブジェクトをテーブル**として表示しています。 データレイクとして扱っている**各ストレージサービスをそのままDWHのように扱える**ため連携コスト・それに準じた運用コストなどが抑えられると考えています。 更に詳しい仕様や利用例などは、Snowflakeの公式ドキュメントを合わせて参考になさって下さい。 - [**外部テーブルの概要**](https://docs.snowflake.com/ja/user-guide/tables-external-intro.html) - [**CREATE EXTERNAL TABLE**](https://docs.snowflake.com/ja/sql-reference/sql/create-external-table.html)   ## やってみたこと 今回は表題の通り、Amazon S3に配置したTSVファイルをSnowflake上でテーブルとして定義し、実際にクエリを叩いて使えるのかどうかを検証してみました。 途中でいくつかつまずくこともあるので、そういった部分も振り返りながら解説していきたいと思います。   ## 前提環境 まず、前提条件として作成しなければならないものが下記のようにあります。 - Amazon S3への[ストレージ統合](https://docs.snowflake.com/ja/user-guide/data-load-s3-config-storage-integration.html) - 接続・認証設定のようなものです。 - 対象BucketのAmazon リソースネーム (ARN)が必要になります。 - Amazon S3への[外部ステージ](https://docs.snowflake.com/ja/user-guide/data-load-s3-create-stage.html) - 接続後のロードやアンロードに関する更に細かい仕様を設定するものです。 - この設定でS3のBucketが参照できるようになります。   ## 外部テーブルの定義（本題） 統合とステージの作成後、ようやく外部テーブルの定義です。   ### Amazon S3の状態 先ずはAmazon S3側の説明です。 下記の様なTSVファイルがGZIP形式で、Amazon S3にアップロードされています。 ※1行目は、ヘッダーです。 ![](https://knowledge.insight-lab.co.jp/hubfs/000_2020-12-11-png.png) こちらがAmazon S3側です ![](https://knowledge.insight-lab.co.jp/hubfs/S3%E3%81%AE%E7%8A%B6%E6%85%8B-png.png) **\[BUCKET\_NAME\]/SAMPLE/\[yyyymmdd\]/\[yyyymmdd\].tsv.gz** のような形式で格納しています。 最初の\[yyyymmdd\]は実行日で最後の\[yyyymmdd\]は、データの断面と想定してアップロードしています。 ※Amazon S3へ連携したツールは、TalendやTroccoなどのETL/ELTツールです。   ### CREATE EXTERNAL TABLEの実施 ソースコードの全体像を以下に記載します。 ![](https://knowledge.insight-lab.co.jp/hubfs/%E5%AE%9A%E7%BE%A9-png.png) 上記を実行してテーブルが作成されたことを確認します。 ![](https://knowledge.insight-lab.co.jp/hubfs/001_2020-12-11-png.png) できてます。 外部テーブルの構文は以下に大別されると考えています。 - テーブル定義 - ファイル読み込み定義   ### テーブル定義の説明 まずはテーブルの定義から。 - 構文 ** AS ** - 例 **FIRST\_NAME VARCHAR AS (VALUE:c1::VARCHAR) NOT NULL** ※Constraintで「Default」を設定することはできません。 　上記のE\_MAIL列のように（"hoge"）と指定すれば実質「Default」値となります。 基本的に「VALUE」というカラムにJSONオブジェクト形式でファイルの値が格納されていますのでそちらから定義を作成します。 上記の例では、「VALUE」に格納されているのKeyの「c1」を指定して「VARCHAR」で型定義した上で「FIRST\_NAME」という外部テーブル上のカラム名を定義しています。 「VALUE」列に一体どのように格納されているのか例を下記に示します。 ![](https://knowledge.insight-lab.co.jp/hubfs/002_2020-12-11-png.png) 「{"Key" : "Value"}」の紛うことなきJSON形式です。 因みにTSVファイル中の値を二重引用符・カンマ（""）で括ることはあまりおすすめしません。 括った場合、読み込む際に必ずVARCHARにしないとエラーになるのでテーブル定義によってはNUMBERでテーブル定義しているのに読み込みはVARCHARで型変換しないといけなくて…。といったように型変換が大量に発生するケースがあります。 どうしても文字列に\\t（タブ）や\\n（改行）が入るのでエスケープするために二重引用符・カンマ（""）が必要な場合は、その列だけ、あるいはそれらの特殊文字列を置換することをおすすめします。 そして列定義時に「**METADATA$FILENAME**」で読み込んでいるファイルパスをメタ情報として取得できます。 これが便利で「**\[BUCKET\_NAME\]/SAMPLE/\[yyyymmdd\]/\[yyyymmdd\].tsv.gz**」が丸々入っているためパーティション情報として利用できます。   ### ファイル読み込み定義の説明 次にファイル読み込み系の定義です。 - PARTITION BY (DATE\_PART) - パーティション設定です。設定後、WHERE句にカラムを含めることでクエリ時の負荷が下がります。 - WITH LOCATION = @EXT\_STAGE/SAMPLE/ - S3のキープレフィックスを予め指定できます。 - AUTO\_REFRESH = TRUE - 本来はS3にファイルが置かれた場合に、「ALTER EXTERNAL TABLE TABLE\_NAME REFRESH;」しないと外部テーブルで表示されなかったりするのですが、ちょっとよくわかっていません。すみません…。 - FILE\_FORMAT = (     TYPE = CSV     COMPRESSION = GZIP     FIELD\_DELIMITER = '\\t'     SKIP\_HEADER = 1 ) - ファイルフォーマットです、「CREATE FILE FORMAT」で作成頂いたものも利用できます。 - PATTERN = '.\*\\.tsv\\.gz'; - 読み込むファイルを正規表現で指定できます。 - 同じキープレフィックスの中で複数の拡張子がある場合は、こちらで目的のファイルだけ取ってこれるようにします。   ### 外部テーブル定義の確認（結果） 少し説明が長くなりましたがいよいよデータをSnowflakeから見てみます。 SELECTした結果がこちら↓ **（右半分：実態であるVALUE列～メタデータまで）**![](https://knowledge.insight-lab.co.jp/hubfs/%E7%B5%90%E6%9E%9C2-png.png) **（左半分：VALUE列をもとに定義した仮想列）** ![](https://knowledge.insight-lab.co.jp/hubfs/%E7%B5%90%E6%9E%9C3-png.png) JSONでデータプレビューでカラム名が見えませんが、「VALUE」列が実態で、その他が外部テーブル定義で定義された通りの仮想列になっています。 利用してみた所感としては、やはり日付でのパーティションが組めるところが運用観点から見ても有用だと考えています。数百万件程度のクエリでも少し重くなる傾向にありますが、パーティションで日付を指定するとクエリの応答速度は早くなります。 ETL/ELTツールやSnowflakeのCOPYタスクを作成しなくても良いケースがありますので、実質的な障害点を減らすことができるとも考えています。   ## 要検証点（今後のアクション） TSVファイルを外部テーブル化して利用してみましたが、自動的に「c1～c〇〇」のようにVALUE列のKeyが生成されると下記の運用を想定した場合に大変そうです。 - 外部テーブル化した後、TSVファイル側でカラムの追加/削除が発生した場合 そもそも設計がおかしかったりするのかもしれませんが、TSVのファイル内で400カラム「c1～c400」を定義していたと想定し、運用中のスキーム変更で201番目にカラムが追加された場合に「c201～c401」の定義を変えたりする必要が出てきそうです。都度対応するのは少々大変そうです。 別のファイルフォーマット（JSON, PARQUET, ORC）ではどうなるのかを検証するのはいい手かもしれないと考えているので今後試してみたいところです。   ## 最後に 今回は外部テーブルを定義してS3→Snowflakeでストレージからテーブルを定義してみました。 今後、Google Cloud StorageやAzure Blob Storageなどの他のストレージサービスやPARQUETやORCなどの実運用を想定したカラムナフォーマットに対して外部テーブル定義をしてみたいと考えています。 少々文字が多くなり見にくくなってしまったかもしれませんが、 ここまで見て頂きありがとうございます。 以上「【データ連携】外部テーブルを定義してAmazon S3にあるファイルをSnowflakeでテーブルライクに扱ってみた」でした！   Snowflakeを体験してみませんか？ INSIGHT LABではSnowflake紹介セミナーを定期開催しています。Snowflakeの製品紹介だけでなく、デモンストレーションを通してSnowflakeのシンプルなUI操作や処理パフォーマンスの高さを体感いただけます。 [![詳細はこちら](https://hubspot-no-cache-na2-prod.s3.amazonaws.com/cta/default/5935783/0d0b0d86-fdb7-4351-ab14-d482c854defc.png)](https://hubspot-cta-redirect-na2-prod.s3.amazonaws.com/cta/redirect/5935783/0d0b0d86-fdb7-4351-ab14-d482c854defc) - [Tweet](https://twitter.com/share) ![Chris Hirasawa](https://knowledge.insight-lab.co.jp/hubfs/publicdomainq-0046330yay-2.jpg) #### 執筆者 [Chris Hirasawa](https://knowledge.insight-lab.co.jp/snowflake/author/chris) FISH ###### [前の投稿](https://knowledge.insight-lab.co.jp/snowflake/trocco-google-spread-sheet-snowflake) [![SaaS型データ連携ツール trocco(トロッコ)でGoogle Spreadsheets→Snowflakeのデータ転送を試してみました。](https://knowledge.insight-lab.co.jp/hubfs/image-png-Dec-03-2020-09-27-43-02-AM.png)](https://knowledge.insight-lab.co.jp/snowflake/trocco-google-spread-sheet-snowflake) ###### [【データ連携】troccoでGoogle SpreadsheetsからSnowflakeへデータを送ってみた](https://knowledge.insight-lab.co.jp/snowflake/trocco-google-spread-sheet-snowflake) ###### [次の投稿](https://knowledge.insight-lab.co.jp/snowflake/trocco-slack-snowflake) [![](https://knowledge.insight-lab.co.jp/hubfs/%E3%83%97%E3%83%AC%E3%82%BC%E3%83%B3%E3%83%86%E3%83%BC%E3%82%B7%E3%83%A7%E3%83%B31.png)](https://knowledge.insight-lab.co.jp/snowflake/trocco-slack-snowflake) ###### [troccoを使ってSlackからSnowflakeに会話データを送ってみる](https://knowledge.insight-lab.co.jp/snowflake/trocco-slack-snowflake) RECOMMEND こちらの記事も人気です。 ###### 4 分で読むことができます。 ##### [Snowflakeの料金体系｜クレジットと費用最適化のポイントをご紹介](https://knowledge.insight-lab.co.jp/snowflake/pricing) ###### 2021年 6月 28日 ###### 12 分で読むことができます。 ##### [【Snowflake×dbt】データパイプライン構築](https://knowledge.insight-lab.co.jp/snowflake/dbt-data-pipeline) ###### 2022年 6月 17日 ###### 5 分で読むことができます。 ##### [【禁断の比較？】SnowflakeとTreasure Dataを比べてみました](https://knowledge.insight-lab.co.jp/snowflake/snowflake-vs-treasuredata) ###### 2021年 5月 24日 ###### 1 分で読むことができます。 ##### [Snowsightでどこまでできるか！？限界に挑戦！](https://knowledge.insight-lab.co.jp/snowflake/snowsight-executive-dashboard) ###### 2023年 12月 14日 ###### 5 分で読むことができます。 ##### [【Snowflake】新機能「Streamlit in Snowflake」とは何者か！？](https://knowledge.insight-lab.co.jp/snowflake/-snowflake-streamlit-in-snowflake-introduce) ###### 2023年 9月 21日 ###### 11 分で読むことができます。 ##### [【Snowflake】SQLと標準関数のみでRAGシステムをサクッと構築（プロトタイプ編）](https://knowledge.insight-lab.co.jp/snowflake/-snowflake-rag-prototype) ###### 2024年 5月 28日 ##### 運営会社 運営会社 - [会社概要](https://insight-lab.co.jp/about/) - [お問い合わせ](https://contact.insight-lab.co.jp/main) ##### メニュー メニュー - [Snowflakeとは](https://service.insight-lab.co.jp/snowflake) - [Snowflakeナレッジ](https://knowledge.insight-lab.co.jp/snowflake) - [お役立ち資料](https://download.insight-lab.co.jp/snowflake/download) [![INSIGHT LAB](https://knowledge.insight-lab.co.jp/hs-fs/hubfs/INSIGHT%20LAB_%E6%96%87%E5%AD%97%E3%81%AE%E3%81%BF_WHT-1.png?width=200&height=55&name=INSIGHT%20LAB_%E6%96%87%E5%AD%97%E3%81%AE%E3%81%BF_WHT-1.png "INSIGHT LAB")](https://www.insight-lab.co.jp/) - [プライバシーポリシー](https://www.insight-lab.co.jp/privacy.html) Copyright © 2020 INSIGHT LAB, Inc.