カテゴリー: 技術メモ2

Azure側を最小権限で構成する方法が書かれた記事がありそうで全然見つからない。 そこで、本記事ではAzure側を最小権限で構成する方法を調べてまとめてみた。 2023年7月現在、公式はSASを使う方法ではなくストレージ統合を使う方法を推奨している。 ストレージ統合によりAzureADへ認証を委譲することで認証情報を持ち回る必要がなくなる。 本記事ではストレージ統合を使用する。 ストレージアカウントにはURL(認証)・N/Wの2系統のパブリックアクセスの経路が存在するが、 URL(認証)によるパブリックアクセスは遮断し、Snowflake VNetからのアクセスのみ許可する。 Azure Portal/CLIからのアクセスを固定IPで制限する。 [arst_toc tag=\"h4\"] ストレージアカウント ストレージアカウントは、Blob(Object)、Files(SMB)、Queue、Table(NoSQL)の4種類が存在する。 Snowflakeの外部ステージとして使用するのはAWS S3でありAzure Blobである。 Blobは最上位階層にない。ストレージアカウント内にはコンテナがあり、コンテナ内にBlobがある。 Blobストレージ内のオブジェクトに対してのみ、外部からアクセス(パブリックアクセス)できる。 パブリックアクセスには、URL・ネットワークの2経路が存在し、それぞれ独立して存在する。 他方を制限したからといってもう一方が自動的に制限されたりはしない。 パブリックURLアクセス ストレージアカウントレベルで、パブリックアクセスを以下から選択できる。 Enabled Disabled また、コンテナレベルで、パブリックアクセスを以下から選択できる。 Private (no anonymous access) Blob (anonymous read access for blobs only) Container (anonymous read access for containers and blobs) 上位階層であるストレージアカウントレベルの設定が優先される。上位で不許可にすれば不許可。 もしストレージアカウントレベルで許可した場合、コンテナレベルの設定が効く。 その組み合わせは以下の通り。確かにMECEなのだが絶望的に冗長に思える。 コンテナ自体はURLアクセス不許可だがBlobはURLアクセス可とか設定できてしまう。 パブリックネットワークアクセス パブリックネットワークアクセスとして以下から選択できる。 Enabled from all network Enabled from selected virtual networks and IP addresses Disabled 分かりづらいが、ストレージアカウント/Blobへのアクセスに使われるN/Wインターフェースを選ぶ。 Disabledにすると、仮想ネットワークのプライベートIPアドレスを使用するN/Wインターフェースとなる。 Enabledには2つあり、全てのN/Wからアクセス可能なN/Wインターフェースか、 選択した仮想N/WまたはIPアドレスからアクセス可能なN/Wインターフェースを選ぶ もちろんDisabledにした場合は、仮想プライベートエンドポイントが必要となる。 ストレージ統合の際のストレージアカウントの最小権限構成 以下のようにして最小権限構成を行なった。 パブリックURLアクセスをストレージアカウントレベルでDisabled パブリックネットワークアクセスを固定IP、及びSnowflake VNet IDsに設定 Snowflakeのストレージ統合機能によりサービスプリンシパルを作成 ストレージコンテナのIAMでサービスプリンシパルにBlob読み書きロール設定 ストレージアカウントに対するSnowflakeのVNetサブネットIDsからのアクセス許可 ストレージアカウントへのパブリックアクセスを「選択したVNetまたはIPアドレスのみ」とした場合、 SnowflakeのVNetまたはIPアドレスが分かる必要がある。IPアドレスは不定なのでVNet IDsを指定する。 公式にドンピシャの説明があるので、それを参考にVNet IDsを設定する。 VNet サブネット IDs の許可 Snowflake側で以下のSQLを実行すると、属するVNet IDがJSONで複数出力される。 それを記録しておく。 USE ROLE ACCOUNTADMIN; SELECT SYSTEM$GET_SNOWFLAKE_PLATFORM_INFO() ; { \"snowflake-vnet-subnet-id\":[ \"/subscriptions/hogehoge\", \"/subscriptions/fugafuga\" ] } 続いて、ストレージアカウントに上の2個のVNet IDからのアクセスを許可する。 以下のように、Azure CLIでログインしてVNet IDの個数だけコマンドを実行する。 $ az storage account network-rule add --account-name --resource-group --subnet \"/subscriptions/hogehoge\" ... $ az storage account network-rule add --account-name --resource-group --subnet \"/subscriptions/fugafuga\" ... ストレージ統合を作成する AWS環境と同様に、CREATE STORAGE INTEGRATION によりストレージ統合を作成する。 Azure CLIやAzure PortalでテナントIDを取得しておいてAZURE_TENANT_IDに渡す。 STORAGE_ALLOWED_LOCATIONSにはBLOBの位置を表す文字列を渡す。 書式は azure://ストレージアカウント名.blob.core.windows.net/コンテナ名/パス 。 BLOBの位置は複数渡せる。 CREATE STORAGE INTEGRATION storage_integration_azure TYPE = EXTERNAL_STAGE STORAGE_PROVIDER = \'AZURE\' ENABLED = TRUE AZURE_TENANT_ID = \'\' STORAGE_ALLOWED_LOCATIONS = (\'azure://storageikuty1.blob.core.windows.net/container1/files/\') ; 続いて、同意URL(AZURE_CONSENT_URL)を取得する。 DESC STORAGE INTEGRATION storage_integration_azure ; property property_type property_value property_default ENABLED Boolean true false STORAGE_PROVIDER String AZURE STORAGE_ALLOWED_LOCATIONS List azure://storageikuty1.blob.core.windows.net/container1/files/ [] STORAGE_BLOCKED_LOCATIONS List [] AZURE_TENANT_ID String AZURE_CONSENT_URL String https://login.microsoftonline.com/hogehoge&response_type=code AZURE_MULTI_TENANT_APP_NAME String COMMENT String URLを開き、表示された画面で「承認」ボタンを押下する。 この際、権限がなければ権限不足である旨表示されてここで終了する。(権限は後述) このタイミングでAzure環境にSnowflakeと対応するサービスプリンシパルが作られる。 上手くいくとSnowflakeの企業サイトに遷移する。 Azure Portalにログインし、Snowflakeと共有したいストレージアカウントを選択する。 IAMから、サービスプリンシパルに対して以下のロールを割り当てる。 サービスプリンシパルIDは、上で得られたマルチテナントアプリ名のハイフンの前の部分。 この辺り、むちゃくちゃ煩雑で、AWSよりも大変。 Storage Blob Data Reader(ストレージBlobデータ閲覧者) Storage Blob Data Contributor(ストレージBlobデータ共同作成者) 次にDBにフォーマットと(外部)ステージを作成する。 USE ROLE SYSADMIN; USE DATABASE HOGE; CREATE OR REPLACE FILE FORMAT MY_CSV_FORMAT TYPE = CSV COMMENT = \'接続検証\' ; CREATE OR REPLACE STAGE my_azure_stage STORAGE_INTEGRATION = storage_integration_azure URL = \'azure://storageikuty1.blob.core.windows.net/container1/files/\' FILE_FORMAT = my_csv_format; ; 該当のBlobにファイルをアップロードする。(パスを指定した場合忘れがち) そして疎通確認。 $ show storage_integration_azure ... $ list @storage_integration_azure ... まとめ Azure環境でストレージ統合によりBlobアクセスを設定してみた。 その際、Azure環境側でパブリックアクセスの開き方が最小限になるように構成した。 Azure側、Snowflake側共に大きな権限が必要だが、公式推奨だし可能ならこの方法が良さそう。

Azureクラウド。データと特にAIの世界で頭ひとつ抜け出しそうだ。 有限な時間を有効に使うには、\"ベストソリューション\"に全振りすることが適切だと信じているが、 一方で、それだと認知の奥行きのようなものが少ない気がしている。 考え方を広げるには色々知るべきだとも思う。 知識を糧にするために必要なことは要約と文書化だと信じている。 データ・パイプラインを構築する数多の技術の1つとして、Azure Data Factoryがある。 今回、Azure Data Factoryに入門してみようと思う。 Azureクラウドは公式の説明に癖がある。 分かるまでとっつきづらい印象がある。知識を糧にするため要約と文書化を続けてみたい。 [arst_toc tag=\"h4\"] パイプライン、アクティビティ、データセット、リンクされたサービス データが物理的に格納されている場所をデータセットとリンクする。「リンクサービス」などと言う。 対応するサービスは後述する。 アクティビティには入力データセットと出力データセットを設定できる。 アクティビティは入力データセットのデータを消費し、出力データセットにデータを生成する。 アクティビティは大きく「データのコピー」と「データの変換」と「制御」の責務を持たせられる。 複数のアクティビティを束ねてパイプラインを構成する。これらの関係は下図の通り。 このうち「制御」アクティビティは、変数の追加、別のパイプラインの実行、Assert、ForEach、 If Condition、ルックアップ、変数の設定、Until、検証、Wait、Web、Webhookなどができる。 ユーザは、UI上でポチポチしてパイプラインを組み上げられる。 下図のように、アクティビティの出力を別のアクティビティの入力とすることで機能を作り込んでいく。 前のアクティビティが正常終了しなかった場合、次のアクティビティは実行されない。 もちろん並列実行させることもできる。 サポートするデータストア・コネクタ 移動アクティビティは、ソースからシンクへデータをコピーする。 サポートされているデータストアは公式に記述があるが、一部記述に矛盾があり信用できない。 Azure Data Factory と Azure Synapse Analytics のコネクタの概要 さすがに仕様上は凄まじいサポート具合だと思う。[汎用]により事実上無限に繋げられる。 [Azure] Blob, Cognitive Search Index, CosmosDB, Data Explore, ADSL Gen1/Gen2, Azure Database (MariaDB/PostgreSQL/MySQL), Databricks Delta Lake, Files, SQL Database, SQL Managed Instance, Synapse Analytics, Table Storage [Database] AWS RDS(Oracle, SQL Server), AWS Redshift, DB2, Drill, GCP BigQuery, GreenPlum, HBase, Apache Impala, Informix, MariaDB, Microsoft Access, MySQL, Netezza, Phenix, PostgreSQL, SAP Business Warehouse, SAP HANA, Snowflake, Spark, SQL Server, Sybase, Terradata, Vertica [NoSQL] Cassandra, MongoDB [Files] S3, FileSystem, FTP, GCP Cloud Storage, HDFs, Oracle Storage, SFTP [サービスとアプリ] Amazon Marketplace WebService, Appfingures, Asana, Concur,data world, Dataverse, Dynamics365, Dynamics AX, Dynamics CRM, GitHub, Google AdWords, Google Spredsheet, HubSpot, Jira, Magento, Microsoft365, Oracle Eloqua, Oracle Responsys, Oracle Service Cloud, Paypal, Quickbase,Quick Books,Salesforce, Salesforce Service Cloud, Sales Marketing Cloud, C4C, SAP ECC, Service Now, SharePoint Online, Shopify, SmartSheet, Square, TeamDesk, Twillo, Web(HTML), Xero, Zen Desk, Zoho [汎用] HTTP, OData, ODBC, RESTfulAPI ファイル形式は以下。 Arvo,バイナリ,Common Data Model形式,区切りテキスト, 差分形式, Excel, JSON, ORC, Parquet, XML スケジュール設定と実行 「スケジュール」と名前が付くものがパイプライン、アクティビティ、データセットに存在するが、 パイプラインの実行時刻・タイミングを定義するのは「出力データセット」であるようだ。 アクティビティの実行タイミングは、入力ではなく出力のデータセットのスケジュールで決まる。 このため、入力データセットは省略できるが、出力データセットは必ず1個作る必要がある。 アクティビティと入出力データセットの関係が下図に示されている。 出力データセットが、「8-9AM」、「9-10AM」、「10-11AM」の3回の枠でスケジュールされている。 それに合わせてアクティビティと入力データセットのスケジュールが決まる。 下図はさらに、入力が準備され、アクティビティと出力がこれから実行される様を表している。 アクティビティにスケジュールをオプションで設定できるが、 その場合は出力データセットのものと合わせる必要がある。(何の意味があるのか...) パイプラインにパイプラインがアクティブな期間を設定できる。 非アクティブな時間に出力データセットが発火しても無視される。 統合ランタイム(IR) Azure内のフルマネージドなサーバーレスコンピューティング。 仮想化されたコンピューティングリソースをData Factoryでは統合ランタイムと呼ぶ。 悪いセンスの極みみたいな名前だが、Windowsで開発した経験があるとピンと来ると思う。 アクティビティや入出力データセットで定義された内容を処理する際に消費される計算資源。 各処理がどこで実行されるのか、というのはパイプラインの実行で気になるポイント。 可能な限りデータの移動やコピーは省略して欲しいし、各サービスが得意な機能を使って欲しい。 この辺りを最大限考慮して必要な場所で処理するよ、と公式には書かれている。 3種類の統合ランタイムが存在する。 Azure統合ランタイム Azureでデータフローを実行する。クラウドストア間でコピーアクティビティを実行する。 コピーは負荷が高い操作であるので、オートスケールが考慮されている。 他に、アクティビティの負荷をコンピューティングに割り当てる(ディスパッチ)機能を有する。 Self-Hosted統合ランタイム オンプレミスかAzure上のプライベートネットワークにインストールするコンピューティングリソース。 Windowsマシンを自前で用意(Self-Host)して、Azureサービスに提供するというもの。 まさにMicrosoftのWindowsをAzureと繋ぐコンピューティング仮想化の姿とも言える。 オンプレミスに大量のコンピュータを用意してAzureに提供したりすることができる。 Azure-SSIS統合ランタイム SSIS(SQL Server Integration Service)パッケージを実行する専用のAzure上の フルマネージドクラスタ。 SSISプロジェクト用に独自のAzureSQL Database、SQL Managed Instanceを持ち込める。 ノードサイズのスケールアップ、クラスターのスケールアウトを実行できる。 SSIS統合ランタイムを手動で停止することでコストを節約することもできる。 SSDTやSSMS等、SQLServer用クライアントツールをSSIS統合ランタイムに繋ぐことができる。 統合ランタイムの場所 仮想マシンがAzureクラウド上のどこに物理的に配置されるのかは気になるところ。 一般に性能観点だけであればデータに近いところに仮想マシンを配置しておくと良い結果となる。 が、データコンプライアンスの観点で、仮想マシンを配置する場所を固定しないといけない場合がある。 Azure IR Azure IRについて、デフォルトでは自動的に配置場所が決まる。 つまり、シンクデータストアと\"同じリージョン\"または\"同じ地理的な場所\"に配置される。 クラウドサービスを使っていると、そのサービスのリージョン、地理的な場所が不定となる場合がある。 シンクデータストアの場所がわからず、世界中の好き勝手なところにAzure IRが作られてしまう。 そんな時は、自動的なコンフィグレーションを無視して配置場所を固定することができる。 また、データが特定のリージョンから離れないように、明示的にVMの場所を固定できる。 Self-hosted IR Self-hostするものなので、そもそも場所は自分で決めるもの。 Azure-SSIS IR 箱としてのデータベースと、そのデータベースを駆動するコンピューティングリソースが分離している。 普通に考えて、箱の場所とVMの場所は同じにしないとパフォーマンスが悪いだろう。(公式に記載あり) ただし、Data FactoryとSSIS IRの場所は必ずしも同じでなくても良いようだ。 データ統合単位(DIU) なんか説明なくいきなり出てくるワード。Data Integration Unitの略だろうか。 Azure Data Factoryにおいて、1つの単位の能力を表す尺度。 CPU、メモリ、ネットワークリソース割り当てを組み合わせたもの。 DIUはAzure IRランタイムにのみ適用される。Self-hostedランタイムには適用されない。 要はAzure IRランタイムの戦闘力を数値化したもの。課金に影響する。 データ統合単位 シナリオにあった統合ランタイムの選択 Azure統合ランタイムからパブリックにアクセスできないデータストアにアクセスする場合、 データストアのファイアウォールなどにAzure統合ランタイムのパブリックIPを設定して抜け穴を作る。 しかし、Azureを含むクラウドアーキテクチャの設計においてあまり望ましくない。 よりベターなのは、PrivateLinkや、Load Balancerの追加セットアップを行う。 また、オンプレミスにある場合、Express Route、S2S VPN経由でアクセスを行う。 この追加セットアップは必要以上に面倒を増やすし、最初からSelf-Hostedにした方が良い。 Enterpriseの現場において、データ転送の経路に対するセキュリティ要件は厳しい。 転送経路が全てプライベートになっている状態を保証できることが理想となる。 つまり、プライベートエンドポイント間のPrivateLinkを設定することが理想である。 Azure統合ランタイムは、デフォでプライベートエンドポイントとPrivateLinkはサポートされない。 マネージド仮想ネットワークを使用すると、データストアに対してプライベートエンドポイントを設定し、 統合ランタイムをPrivateLinkを設定できる。 また、Self-hosted統合ランタイムにおいて、仮想ネットワークにプライベートエンドポイントと PrivateLinkを設定できる。 クラウドインフラストラクチャの責任共有モデルについての議論もある。 Azure統合ランタイムのパッチ、バージョン更新等のメンテナンスはAzure側が自動的に行う。 対して、Self-hosted統合ランタイムは、ユーザが責任を持つ必要がある。 より楽をしたいならAzure統合ランタイムがより良い選択肢となる。 まとめ ドキュメントを調べてAzure Data Factoryの用語をまとめてみた。 Azureクラウドの公式ドキュメントは癖があり、ドキュメントを読むだけで一苦労だなという印象。 やはり、AWSと比較してEnterpriseを全面的に推している印象がある。 後続の記事でガンガン知識を文書化していく。

Moden Data Stackの重要な技術として有名なdbtを理解したい。 と言いつつも使う機会がなかなか無いな、という点と、認定試験があるようなので、 まずは認定試験に合格することを目的に、知識を獲得していきたい。 SnowPro Certificationsと同様に参考書も問題集も無いが、 公式に学習要領のようなものが無茶苦茶丁寧に書かれている。 もちろん試験に向けた資料なので偏りだったり不足はあるのだろうけれど、 何も知らない素人がステップバイステップで物事を理解していくには大分心強い。 dbt Analytics Engineering Certification Exam Study Guide この記事では、この学習要領を上から順に読んでいく。 後続の記事で(ブログドリブンで)頭に入れた知識をアウトプットしていく。 [arst_toc tag=\"h4\"] トピック一覧 何を理解したら 「dbtのことを理解した」 と言えるのか。 dbtが備えている機能とその詳細が試験要項に一覧化されている。 初心者には本当に助かる。これらが頭の中で整理されて当たり前になったときが次のステップ。 何も知らない状況では分からない言葉や概念が多い。 振り返らず5分程度で訳を付けて終了。何となく全体像が見えてくる。 Topic 1: Developing dbt models Identifying and verifying any raw object dependencies Understanding core dbt materializations Conceptualizing modularity and how to incorporate DRY principles Converting business logic into performant SQL queries Using commands such as run, test, docs and seed Creating a logical flow of models and building clean DAGs Defining configurations in dbt_project.yml Configuring sources in dbt Using dbt Packages dbtモデルの実装方法と理論について。 dbtは物理的に存在するDBオブジェクトを抽象化するフレームワークであって論理と物理のマップがある。 抽象的な構造から見た具体化方式について理解する。 dbtを構成する概念の理解とDRY(Don\'t Repeat Yourself)法則をどう実現するか。 ビジネスロジックを高性能なクエリに変換する仕組みを理解する。 run,test,docs,seedなどのコマンドの使い方を理解する。 モデルのフローとDAGs、dbt_project.ymlの定義方法、sourceの構成方法を理解する。 Topic 2: Debugging data modeling errors Understanding logged error messages Troubleshooting using compiled code Troubleshooting .yml compilation errors Distinguishing between a pure SQL and a dbt issue that presents itself as a SQL issue Developing and implementing a fix and testing it prior to merging dbtモデルのデバッグ方法について。 ログとして記録されたエラーメッセージの解釈方法を理解する。 dbtによりコンパイル/生成されたコードを使ったトラブルシュート方法を理解する。 .ymlコンパイルエラーに対するトラブルシュート方法を理解する。 SQL自身の問題なのかdbtに纏わる問題なのかを区別する方法を理解する。 (git branchに?)mergeする前に修正/テストする方法を理解する。 Topic 3: Monitoring data pipelines Understanding and testing the warehouse-level implications of a model run failing at different points in the DAG Understanding the general landscape of tooling 作ったパイプラインの監視。 DAG のさまざまなポイントでモデル実行が失敗した場合のウェアハウスレベルの影響の理解+テスト方法。 (ウェアハウスレベルってどこのレベル??) Topic 4: Implementing dbt tests Using generic, singular and custom tests on a wide variety of models and sources Understanding assumptions specific to the datasets being generated in models and to the raw data in the warehouse Implementing various testing steps in the workflow Ensuring data is being piped into the warehouse and validating accuracy against baselines dbtテストの実装。 様々な種類のモデル・ソースに対する generic, singular, customテストの使用方法。 モデルで生成されるデータセットとウェアハウス内の生データに特有の仮定を理解する。(意味不..) ワークフロー内にテストステップを実装する方法。 データがウェアハウスにパイプされていることを確認し、ベースラインに対する精度を検証する。 Topic 5: Deploying dbt jobs Understanding the differences between deployment and development environments Configuring development and deployment environments Configuring the appropriate tasks, settings and triggers for the job Understanding how a dbt job utilizes an environment in order to build database objects and artifacts Using dbt commands to execute specific models デプロイ環境と開発環境の違いを理解する。 コンフィグによってデプロイ環境と開発環境を作る。 ジョブの適切なタスク、設定、トリガーの構成。 dbtジョブがDBオブジェクト・生成物をビルドするために環境をどのように使うかを理解する。 特定のdbtモデルを実行するためのdbtコマンドの使用方法。 Topic 6: Creating and Maintaining dbt documentation Updating dbt docs Implementing source, table, and column descriptions in .yml files Using dbt commands to generate a documentation site Using macros to show model and data lineage on the DAG dbt documentationの作成・維持。 dbt docsの更新、.ymlファイル内に source,table,列定義を書く方法。 documentationサイトを生成するためのdbtコマンドの使用方法。 DAGにおけるモデルとリネージを表示するマクロの使用方法。 Topic 7: Promoting code through version control Understanding concepts and working with Git branches and functionalities Creating clean commits and pull requests Merging code to the main branch gitとのコラボw。版管理。 版管理の概念。Git branchとの連携とその機能。 cleanコミットとプルリク。mainブランチへのマージ。 Topic 8: Establishing environments in data warehouse for dbt Understanding environment’s connections Understanding the differences between production data, development data, and raw data データウェアハウスの各環境とdbtの接続を確立する。 環境との接続確立方法を理解する。本番環境データ、開発環境データ、生データの違いを理解する。 学び方 素人が脱初心者するためのステップ。なんだかやたら丁寧。 各ステップで参照すべきリソースがまとまっている。順番を無視する理由は特にないので最初から見ていく。 Checkpoint 0 - 前提条件 SQLの理解が必要。結合、集計、CTEsの書き方、ウィンドウ関数に詳しくないといけない。 Gitの理解が必要。ブランチ戦略や基本的なgitコマンド、プルリクの方法など。 Checkpoint 1 - Build a Foundation やたら丁寧だな... オンライン動画。 dbt Fundamentals 読み物 dbt viewpoint ドキュメント Source properties Node selection syntax dbt_project.yml General resource properties やってみる Creating a dbt project from scratch to deployment Debugging errors コマンド達 dbt compile dbt run dbt source freshness dbt test dbt docs generate dbt build dbt run-operation Checkpoint 2 - Modularity and Refactoring オンライン動画 Refactoring SQL for Modularity Readings How we structure our dbt projects Your Essential dbt Project Checklist ドキュメント Refactoring legacy SQL to dbt やってみる - Refactoring SQL for performance and clarity Checkpoint 3 - Doing More with dbt オンライン動画 Jinja, Macros, and Packages Advanced Materializations Analyses and Seeds ドキュメント Exposures Env_var Target Schema Database やってみる - Utilizing packages and macros in a dbt project - Implementing all core materializations into a dbt project - Implementing seeds コマンド達 - dbt snapshot - dbt seed Checkpoint 4 - Deployment and Testing オンライン動画 Advanced Testing Advanced Deployment 読み物 The exact grant statements we use in a dbt project The exact GitHub pull request template we use at dbt Labs How to review an analytics pull request How we configure Snowflake ドキュメント Tags Hooks & Operations Custom Schema Threads やってみる - Defining environments in your data platform - Defining environments in dbt - Promoting code through git including use of multiple branches, pull requests - Troubleshooting errors in production runs - Defining dbt jobs for optimal performance まとめ 本来探し回らないと見つからないドキュメントが集まっていて良さそう。 このレベルでまとめるだけで何となく全体像が見えてきたような。 後続の記事で(ブログドリブンで)インプットした内容をアウトプットしていく。

ドキュメントを読んで使ったことが無い機能に詳しくなるシリーズ。 今回は検索最適化サービス(Search Optimization Service)。 他のドキュメントと比較して英語・日本語ともに文体が異なる気がする。 日本語訳が変なのではなく、元の英文の文体が違う。 まず、\"クラスタリング\"とか\"Materialized View\"が構造の名称である一方で、 \"検索最適化\"が目的の名称となっている点に違和感を感じる。 似て非なる索引(index)とは違うのだよ、という何かなのだろうか... [arst_toc tag=\"h4\"] 検索最適化サービスの概要 検索最適化サービスは、1つまたは少数の異なる行のみを返す「ポイントルックアップクエリ」。むむ?。 要は、巨大なテーブルに対するSELECT文の結果が数行となるようなケースを高速化する。 例えば、あるテーブルのageというカラムに対する等価条件を設定したクエリを速くしたい場合、 あらかじめ高速化用のデータ構造を構築し、ageに対する等価条件を使ったクエリを高速化する。 等価条件検索、範囲条件検索、部分文字列検索に加え、 プレビュー機能として、正規表現検索、半構造化型値検索、地理空間関数検索 が用意される。 他の高速化技術との比較 高速化機能として、他にクラスタリング、Materialized Viewがあるが、それぞれ機序が異なる。 クラスタリングは、マイクロパーティションの分散と枝刈りを利用した方法であって、 ナチュラルキーよりも高いプルーニング効率が得られるケースに適用する。 Materialized Viewは、同一テーブルに対して複数のクラスタリングキーを透過的に適用する 用途で用いられる。もちろん直アクセスでは遅い場所の事実上のキャッシュとしても使う。 検索最適化サービスは、ある意味、RDBMSの索引チックな使い方ができる。 良く使うカラムと検索述語を「アクセスキー」として登録し、高速検索用の索引を構築する。 検索最適化サービスの構成に必要な権限 以下の権限が必要。 テーブルの所有権 テーブルを含むスキーマに対する ADD SEARCH OPTIMIZATION 権限 検索最適化サービスを適用したテーブルにアクセスするには普通にSELECTできれば良い。 検索最適化サービスで未サポートのもの 以下に対して検索最適化サービスを適用することはできない。 (逆に以下以外なら適用できる。) 外部テーブル マテリアライズドビュー COLLATE 句 で定義された列 列の連結 分析表現 テーブル列にキャスト（文字列にキャストされる固定小数点数を除く） Materialized Viewは外部テーブルアクセスを高速化できるが、 検索最適化は外部テーブル、Materialized Viewを対象に出来ない。 何かの事情でCOLLATE句を指定した場合、その列は使用できない。 述語を指定する際に、定数側の明示的キャストはサポートされるが、 列側をキャストすると効かなくなる。例えば以下のような違いがある。 ただし、NUMBERからVARCHARへのキャストだけは機能する。 -- 機能する SELECT hoge FROM table as t1 WHERE timestamp1 = \'2020-01-01\'::date; -- 機能する SELECT hoge FROM table as t1 WHERE cast(numeric_column as varchar) = \'2\' -- 機能しない SELECT hoge FROM table as t1 WHERE to_date(varchar_column) = \'2020-01-01\'; (分析表現って何!?) 検索最適化サービスの構成方法 ALTER TABLE と ADD SEARCH OPTIMIZATION を使用する。 テーブル全体(つまり全カラム)に対して適用できるか、カラムを指定することもできる。 カラムを指定する場合、ON句に最適化したい検索述語等を指定する。 例えば以下であればc1,c2列を使用した等価述語が最適化される。 c1列、c2列に対して等価述語(=)を使ったSQLが高速化される。 -- t1テーブルの c1,c2 の等価述語に対して検索最適化を適用 ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON EQUALITY(c1,c2); -- クエリ SELECT hoge FROM t1 where c1 = 100 and c2 = 200; 検索最適化サービスが適用済みか確認する方法 DESCRIBE により該当テーブルへの検索最適化サービスの適用状況を確認できる。 定義した「検索アクセスパス」の詳細と、有効無効が表示される。 DESCRIBE SEARCH OPTIMIZATION ON t1; +---------------+----------+--------+------------------+--------+ | expression_id | method | target | target_data_type | active | +---------------+----------+--------+------------------+--------+ | 1 | EQUALITY | C1 | NUMBER(38,0) | true | +---------------+----------+--------+------------------+--------+ テーブル全体に対して検索最適化サービスを適用していた場合、 列の追加により、追加した列に対して自動的に検索最適化サービスが適用される。 逆に列の削除により、削除した列が検索最適化サービスから除外される。 列のデフォルト値を無効にすると、検索最適化サービスが「無効」になる。 一度「無効」になってしまったら、SEARCH OPTIMIZATION を一度 DROP し、 再度 ADD しなければならない。 検索最適化サービスのコスト 以下のコストがかかる。 ストレージリソース コンピューティングリソース ストレージリソース 検索アクセスパスのためのデータ構造を構築するために使われる。 通常、元テーブルの1/4ぐらいのサイズになるらしい。 ただし、テーブル内の個別の値の数が多ければ多いほどストレージを消費する。 例えば、全カラムを対象としている場合、全カラムの値が全て異なるという状況は最悪で、 元テーブルと同じだけのストレージを消費してしまう。 コンピューティングリソース 検索最適化を維持するためにコンピューティングリソースが使われる。 テーブル内で大量のデータが変更される場合、リソースの消費量が激しくなる。 追加・変更に比例して大きくなる。削除は若干使用する。 ざっくり以下に比例してコストが増加する。 この機能が有効になっているテーブルの数、およびそれらのテーブル内の個別の値の数。 これらのテーブルで変更されるデータの量 SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS 関数によりコストを見積もれる。 -- テーブルに検索最適化を追加する場合の推定コスト select SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS(\'TABLE_WITHOUT_SEARCH_OPT\'); +---------------------------------------------------------------------------+ | SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS(\'TABLE_WITHOUT_SEARCH_OPT\') | |---------------------------------------------------------------------------| | { | | \"tableName\" : \"TABLE_WITHOUT_SEARCH_OPT\", | | \"searchOptimizationEnabled\" : false, | | \"costPositions\" : [ { | | \"name\" : \"BuildCosts\", | | \"costs\" : { | | \"value\" : 11.279, | | \"unit\" : \"Credits\" | | }, | | \"computationMethod\" : \"Estimated\", | | \"comment\" : \"estimated via sampling\" | | }, { | | \"name\" : \"StorageCosts\", | | \"costs\" : { | | \"value\" : 0.070493, | | \"unit\" : \"TB\" | | }, | | \"computationMethod\" : \"Estimated\", | | \"comment\" : \"estimated via sampling\" | | }, { | | \"name\" : \"MaintenanceCosts\", | | \"costs\" : { | | \"value\" : 30.296, | | \"unit\" : \"Credits\", | | \"perTimeUnit\" : \"MONTH\" | | }, | | \"computationMethod\" : \"Estimated\", | | \"comment\" : \"Estimated from historic change rate over last ~11 days.\" | | } ] | | } | +---------------------------------------------------------------------------+ -- すでに検索最適化が行われているテーブルに対するこの関数の出力 select SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS(\'TABLE_WITH_SEARCH_OPT\'); +---------------------------------------------------------------------------+ | SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS(\'TABLE_WITH_SEARCH_OPT\') | |---------------------------------------------------------------------------| | { | | \"tableName\" : \"TABLE_WITH_SEARCH_OPT\", | | \"searchOptimizationEnabled\" : true, | | \"costPositions\" : [ { | | \"name\" : \"BuildCosts\", | | \"computationMethod\" : \"NotAvailable\", | | \"comment\" : \"Search optimization is already enabled.\" | | }, { | | \"name\" : \"StorageCosts\", | | \"costs\" : { | | \"value\" : 0.052048, | | \"unit\" : \"TB\" | | }, | | \"computationMethod\" : \"Measured\" | | }, { | | \"name\" : \"Benefit\", | | \"computationMethod\" : \"NotAvailable\", | | \"comment\" : \"Currently not supported.\" | | }, { | | \"name\" : \"MaintenanceCosts\", | | \"costs\" : { | | \"value\" : 30.248, | | \"unit\" : \"Credits\", | | \"perTimeUnit\" : \"MONTH\" | | }, | | \"computationMethod\" : \"EstimatedUpperBound\", | | \"comment\" : \"Estimated from historic change rate over last ~11 days.\" | | } ] | | } | +---------------------------------------------------------------------------+ まとめ 検索最適化サービスについてドキュメントを読んでいくつか試してみた。 ドキュメントにある「ポイントルックアップクエリ」という言葉が機能を最も適切に表していると思う。 高速化したいクエリが明確な時に使えるのかもしれない。

使ったことがない機能のドキュメントを読んで詳しくなるシリーズ。 今回は外部関数(External Function)。 [arst_toc tag=\"h4\"] ユーザ定義関数 まずユーザ定義関数(User Defined Function)について。 UDFは、組み込み関数と同様にSQLから呼び出すことができる。組み込み関数にない機能を自作できる。 コードを共通化して2個以上の場所で発生するコードクローンを除去できる。 ユーザ定義関数は単一の値を返すパターンと、表を返すパターンの2パターンを定義できる。 単一の値を返すパターンはスカラー関数(UDFs)。 表、つまり0個,1個,2個以上の値を返すパターンは表関数(UDTFs)。 サポートされている言語はJava,JavaScript,Python,SQL。 スカラー関数(UDFs) スカラー関数は入力行に対して1つの出力行を返す。 公式のサンプルを読むのが早い。 -- UDFの定義 create or replace function addone(i int) returns int language python runtime_version = \'3.8\' handler = \'addone_py\' as $$ def addone_py(i): return i+1 $$; -- UDFの実行 select addone(1); 2 こんな調子で、create function の中に書き下した処理をインラインハンドラと言う。 処理を修正する際は、alter function を実行して対応する。 確かにお手軽だが、行数が増えてきたり、処理を共通化したりしたい場合は微妙。 あらかじめコードをコンパイルしておき再利用することもできる(ステージングハンドラ)。 ステージングハンドラであれば、より多くの行数に対応できるし、処理の共通化も用意。 ステージングハンドラの実行例は以下の通り。 CREATE FUNCTION my_udf(i NUMBER) RETURNS NUMBER LANGUAGE JAVA IMPORTS = (\'@mystage/handlers/my_handler.jar\') HANDLER = \'MyClass.myFunction\' インラインハンドラは全ての言語で対応できるが、 ステージングハンドラは、JavaまたはPythonが必要。 表関数(UDTFs) 表関数は入力行に対して表、つまり0行,1行,2行,...行 を返す。 実行例は以下の通り。なるほど。 戻り値が表(table)になるため、SQLだと処理と戻り値のギャップが少ないが、 SQLでは手続き的な処理が書けないため自由度は低い。 -- サンプルテーブル・データの作成 create or replace table orders ( product_id varchar, quantity_sold numeric(11, 2) ); insert into orders (product_id, quantity_sold) values (\'compostable bags\', 2000), (\'re-usable cups\', 1000); -- UDTFの定義 create or replace function orders_for_product(PROD_ID varchar) returns table (Product_ID varchar, Quantity_Sold numeric(11, 2)) as $$ select product_ID, quantity_sold from orders where product_ID = PROD_ID $$ ; -- UDTFの実行 select product_id, quantity_sold from table(orders_for_product(\'compostable bags\')) order by product_id; +------------------+---------------+ | PRODUCT_ID | QUANTITY_SOLD | |------------------+---------------| | compostable bags | 2000.00 | +------------------+---------------+ JavaScriptで書くと以下のようになる。手続き的に処理を書いている。 これであればある程度の自由度を獲得できる。 -- UDTFの定義 -- IP アドレスの「範囲」を IP アドレスの完全なリストに変換 CREATE OR REPLACE FUNCTION range_to_values(PREFIX VARCHAR, RANGE_START FLOAT, RANGE_END FLOAT) RETURNS TABLE (IP_ADDRESS VARCHAR) LANGUAGE JAVASCRIPT AS $$ { processRow: function f(row, rowWriter, context) { var suffix = row.RANGE_START; while (suffix <= row.RANGE_END) { rowWriter.writeRow( {IP_ADDRESS: row.PREFIX + "." + suffix} ); suffix = suffix + 1; } } } $$; -- UDTFの実行 SELECT * FROM TABLE(range_to_values('192.168.1', 42::FLOAT, 45::FLOAT)); +--------------+ | IP_ADDRESS | +==============+ | 192.168.1.42 | +--------------+ | 192.168.1.43 | +--------------+ | 192.168.1.44 | +--------------+ | 192.168.1.45 | +--------------+ 外部関数 ユーザ定義関数のロジック(ハンドラ)を外部のコンピューティングリソースに配置することができる。 概念図は以下の通り。Snowflake側から見て、外部関数、API統合、プロキシサービス、リモートサービスから成る。 外部関数(External Function)はスカラー関数(UDFs)だが、自前でコードを書かない。 その実体は、プロキシサービスを介してリモートサービスのコードを実行するためのオブジェクトで、 CREATE EXTERNAL FUNCTION により定義する。 リモートサービスは、外部のコンピューティングリソース。 AWS Lambda、Azure Functionsが分かりやすいが、 HTTPSでJSONを受けてJSONを返せれば、一般的なWebサーバでも良い様子。 プロキシサービスは、要はリモートサービスを抽象化するレイヤの機能を持つ。 AWS API Gateway、Azure API Managementサービスと言うと分かりやすい。 外部関数が直接リモートサービスを叩かない理由は、プロキシサービスに認証機能を持たせるため。 API統合は、外部関数をSnowflakeの世界に持ち込むための抽象化レイヤ。 同様の機能として、S3,Blobへのアクセス手段を抽象化するストレージ統合がある。 使い場合はUDFと同様。 select my_external_function_2(column_1, column_2) from table_1; select col1 from table_1 where my_external_function_3(col2) < 0; create view view1 (col1) as select my_external_function_5(col1) from table9; select upper(zipcode_to_city_external_function(zipcode)) from address_table; リモートサービスの入出力定義 前述のように、リモートサービスはHTTPSでJSONを受けてJSONを返す必要がある。 そのインターフェースは公式に記述がある。 リモートサービスの入力および出力データ形式 基本的に、POSTを受けて同期的に応答するようにする。 ただし、非同期処理もサポートしており、その際はGETでポーリングリクエストを受ける必要がある。 関数 ext_func(n integer) returns varchar のケースにおいて、リクエストヘッダは以下。 いわゆるRPC(Remote Procedure Call)を定義する何かに準じている。 ヘッダ文字列値 sf-external-function-formatjson固定 sf-external-function-format-version1.0固定 sf-external-function-query-batch-idリクエスト固有の識別子。この値を使って処理の成否を確認する。 sf-external-function-name関数名 ext_func sf-external-function-name-base64関数名(base64) base64(ext_func) sf-external-function-signature関数名署名 (N NUMBER) sf-external-function-signature-base64関数名署名(base64) base64(N NUMBER) sf-external-function-return-type戻り値型 VARCHAR(16777216) sf-external-function-return-type-base64戻り値型(base64) base64(VARCHAR(16777216)) リクエスト本文はJSON。 重要なことは、外部関数はスカラー関数であり、1個の入力に対して1個の応答を返す必要があるが、 一度に複数の入力を受けることができ、それぞれに対して応答を返す必要がある、ということ。 複数の入力のセットを公式ではバッチ、と読んでいる様子。 署名 f(integer, varchar, timestamp) を持つ外部関数にシリアル化したJSONを送る。例は以下。 先頭の数字が1個多い気がするが、これは行番号を表す。 { \"data\": [ [0, 10, \"Alex\", \"2014-01-01 16:00:00\"], [1, 20, \"Steve\", \"2015-01-01 16:00:00\"], [2, 30, \"Alice\", \"2016-01-01 16:00:00\"], [3, 40, \"Adrian\", \"2017-01-01 16:00:00\"] ] } リモートサービスの応答定義 レスポンス本文はJSON。リクエスト本文と同様にコレクションの先頭は行番号を設定する必要がある。 公式には、都市名を引数として、その緯度軽度を返す関数の例が書いてある。 このように、入力と対応する応答値を、入力と同じ順番にコレクションに詰めてシリアライズして返す。 この外部関数は、VARIANT型を戻している。このように、スカラー関数ではあるが、 1回の処理が(半)構造を持てるので、事実上複数の入力値に対して複数の応答値を返せる。 -- 呼び出し例 select val:city, val:latitude, val:longitude from (select ext_func_city_lat_long(city_name) as val from table_of_city_names); -- レスポンス { \"data\": [ [ 0, { \"City\" : \"Warsaw\", \"latitude\" : 52.23, \"longitude\" : 21.01 } ], [ 1, { \"City\" : \"Toronto\", \"latitude\" : 43.65, \"longitude\" : -79.38 } ] ] } StatusCode=200で、同期応答完了。 202を返すと、非同期応答となり処理を継続できる様子だが今回は省略。 その他はエラー応答として扱われる。 まとめ 外部関数に関するドキュメントと、その理解に必要なUDFs、UDTSsのドキュメントを読んでみた。 実体はHTTPS上のWebAPIであり、事実上無限の自由度を獲得できると思う。 (これを持ち出す頃には、本末転倒な何かに陥っていないか注意は必要だと思う)

使ったことがない機能のドキュメントを読んで詳しくなるシリーズ。 ステージに配置したファイルに対してロードせずに直接クエリを実行できる仕組み。 [arst_toc tag=\"h4\"] 外部テーブルの基本 ファイルの構造を自動的に解釈してテーブルにしてくれる訳ではなく、 いったんレコードがVARIANT型のVALUEカラムに書かれる。 外部テーブルに対するSELECT文の中で、VALUEカラム内の値を取得して新規に列を作る。 この列を仮想列と言ったりする。 外部テーブルは、クエリ実行のたびにファイルにアクセスすることになるため遅い。 Materialized Viewを作成することで、高速に外部テーブルにアクセスできる。 複数ファイルとパーティション化 外部テーブルの実体は外部ステージ上のファイルであって、通常、複数のファイルから構成される。 複数のファイルが1つの外部テーブルとして扱われるところがポイント。 stackoverflowにドンピシャの記事があってとても参考になった。 Snowflake External Table Partition - Granular Path /appname/lob/ という論理ディレクトリの下に Granular にファイルが配置されている例。 例えば、/appname/lob/2020/07/24/hoge.txt のように論理ディレクトリ以下に日付を使って ファイルが配置されることを想定している。 CREATE EXTERNAL TABLEする際、LOCATION を /appname/lob/ とすることで、 /appname/lob/以下に配置された複数のファイルが1つの外部テーブルで扱われる。 その際、それぞれのファイル名が metadata$filename に渡される。 公式によると、外部テーブルのパーティショニングを行うことが推奨されている。 パーティション化された外部テーブル 下記の例では、ファイル名からYYYY/MM/DDを抽出して日付化して、パーティションキーとしている。 CREATE OR REPLACE EXTERNAL TABLE Database.Schema.ExternalTableName( date_part date as to_date(substr(metadata$filename, 14, 10), \'YYYY/MM/DD\'), col1 varchar AS (value:col1::varchar)), col2 varchar AS (value:col2::varchar)) PARTITION BY (date_part) INTEGRATION = \'YourIntegration\' LOCATION=@SFSTG/appname/lob/ AUTO_REFRESH = true FILE_FORMAT = (TYPE = JSON); パーティショニングとは、名前が似ているがマイクロパーティションの事ではないと思う。 概念的に似ていて、データを別々の塊として格納し、検索時にプルーニング的な何かを期待する。 ガバナンス系オブジェクトとの関係 以前、列レベルセキュリティのマスキングポリシーをまとめたように、 テーブルと独立してマスキングポリシーを定義し、カラムに適用(Apply)することで列を保護する。 [clink url=\"https://ikuty.com/2023/03/31/column-level-security/\"] 外部テーブルは上記のように、VALUE列にKey-Valueで列値が入り、 その後、SELECT文内で仮想列を定義する、という仕様で、外部テーブルに列がある訳ではない。 このため、マスキングポリシーを適用する先がない、という問題が起こる。 ただ、VALUE列全体にApplyすることは出来る様子。 外部テーブル-セキュリティカラムイントロ Masking Policyはビューのカラムに対して適用することができるが、 これは、外部テーブルに設定したMaterialized Viewにも設定できる。 本記事の図のように、Materialized Viewを用意した上であれば、 Masking Policyを適用することができる。

データをコピーしてしまうと、データをコピーする際の計算資源とストレージが必要になる。 そして、「どちらのデータが正しいか問題」が発生してしまい、由々しき事態になる。 SSOT(Single Source Of True)という考え方があり、なるべくデータは1箇所に集めたい。 Zero Copy Cloning Zero Copy Cloning を使うことにより、1つのデータをコピーせずに参照できる。 Cloneだけで意味が通じるが、「コピーしてないよ」を強調するために\"Zero Copy\"を付けて呼ぶ。 Clone は同一アカウント内のオブジェクト操作レベルで使用する。 つまり、Database、Schema、Table単位で使用する。 Share Share は 同一クラウドプロバイダ内の複数のアカウントで参照するために使用する。 一度 Shareというオブジェクトを作成し、Share から Database オブジェクトを作る。 Share から Databaseオブジェクトを作る際にコピーは行われない。 Cross-Cloud,Cross-Region してしまうと、「コピーせずに」が出来なくなる。 なので、Shareは同一Cloud,同一Regionまで。 Replication Cross-Cloud, Cross-Region でデータをコピーするための仕組み。 DR対応などで異なるリージョンにバックアップを取りたい場合や、 異なるクラウドプラットフォームに別機能を実装する場合などに用いる。 どうしてもコピーが発生してしまう。 一度 Cross-Cloud, Cross-Region で Replication を行い、 Replication先で Share, Clone を行うことで、Cross する回数が最小化される。 Whitepaper Shareについて以下が参考になった。 Snowflake Data Sharing EXTENDING THE BUILT-FOR-THE CLOUD DATA WAREHOUSE BEYOND ORGANIZATIONAL AND APPLICATION BOUNDARIES

Snowpipeは外部ステージ上に置かれたファイルを自動的にSnowflakeテーブルにロードする仕組み。 クラウドプロバイダの機能が透過的に利用されるため、その仕組みを意識する必要がない。 AWS,Azure,GCPがサポートされている。 Snowpipeを設定する際に、登場するオブジェクトにどう権限設定したら良いのかいつも悩む。 オレオレ設定をしてガバくなってないか、調べ直してみて書いてみる。 セキュリティを構成する SYSADMINでリソースを作って、SYSADMINにAssumeRoleして操作をする、というのは良くない。 別にロールを作ってそこに必要な権限を集めると良さそう。 既に外部ステージが存在し、そこに続々とファイルが配置されている。 また、既にSnowflakeにテーブルが存在し、外部ステージに到着するファイルを読み込みたい。 このシチュエーションで、外部ステージとSnowflakeテーブルを結ぶパイプを作成する。 パイプと関連する全リソースにアクセス可能なロールは以下のように作る。 パイプから見れば、外部ステージ、テーブルは既存のオブジェクトであり、 使用する、つまり USAGE が必要。またデータ取得のため、外部ステージから READ が必要。 同様に、パイプから見れば、データベース、スキーマは既存のオブジェクトであり、 USAGEが必要。またデータ投入のため、テーブルに対して INSERT が必要。 パイプがテーブル内のデータを参照して処理をするため テーブルに大して SELECTが必要。 例えば SYSADMIN が PIPE を CREATE した場合、その所有者は SYSADMIN になるが、 これを繰り返すと、SYSADMIN が全ての所有者になってしまう。 今回、Snowpipeに関わるオブジェクトを操作・閲覧可能なロールを作る、という話なので、 新ロールを PIPE の所有者にする。

なんだか分かった気がして分からないSnowflakeのウェアハウスの同時実行特性。 ウェアハウスが1度に何個のクエリを同時処理するか だいぶ抽象化されているがウェアハウスは並列化された計算可能なコンピュータ。 処理すべきクエリが大量にあった場合、1つのウェアハウスがそれらを同時に処理しようとする。 1個のウェアハウスが同時に最大何個のクエリを同時処理するか、 MAX_CONCURRENCY_LEVEL により制御できる。 デフォルト値は 8 に設定されている。 MAX_CONCURRENCY_LEVEL を超えると、クエリはキューに入れられる。 または、マルチクラスタウェアハウスの場合は、次のウェアハウスた立ち上がる。 もちろん、クエリ1個が軽ければ同時処理は早く終わるが、重ければ時間がかかる。 逆に言うと、MAX_CONCURRENCY_LEVEL を小さくすると、1つのウェアハウスが同時処理する 最大クエリ数の上限が下がり、クエリ1個に割りあたるコンピューティングリソースが増える。 MAX_CONCURRENCY_LEVEL を大きくすると、クエリ1個に割り当たるリソースが減る。 同時実行数を減らすとキューイングされる数が増える MAX_CONCURRENCY_LEVEL を小さくするとクエリの処理性能が上がるが、 キューに入るクエリの数が増える。 ちなみにデフォルトだと、キューに入ったクエリは永遠にキューに残り続ける。 キューに入ったクエリをシステム側でキャンセルする時間を STATEMENT_QUEUED_TIMEOUT_IN_SECONDS により設定できる。 ウェアハウスサイズとの関係 ウェアハウスサイズアップはスケールアップ、マルチクラスタはスケールアウト、 なんかと、テキトーに丸めて表現されたりするが、 ウェアハウスサイズアップにより、コンピューティングリソースが増加するため、 同じ MAX_CONCURRENCY_LEVEL であっても クエリ1個に割り当たるリソースが増えることで、 結果的に所要時間が小さくなる。見かけ上、並列性が上がった風になる。 ウェアハウスサイズ、ウェアハウス数の2つのファクタによりリソースの総量が決まるため、 これらを固定した状態で MAX_CONCURRENCY_LEVEL を増やしたとしても、 結果として処理できる量が増えたりはしない様子。 同時実行性能が関係するパフォーマンスのベスプラ お金をかけずに性能が上がるということはない。 MAX_CONCURRENCY_LEVELはあくまで微調整用途。 大量の小さなクエリをガンガン処理するといったケースでは、ベスプラでは 順当にウェアハウス数を増やして、その上でウェアハウスサイズを増やすべきとされている。 この辺りが、「気分でウェアハウスサイズを増やしても性能上がらないなー」 という現象の一因（、かもしれない）。

基本的には同一のテーブルに対して設定できるクラスタリングキーは2個から3個。 ただし2個以上設定すると、1個の場合と比較して原理的に効果が落ちる。 パフォーマンスを落とさずにクラスタリングキーを追加できないか。 ベーステーブルへのクエリ実行において透過的にMaterialized Viewが使われる Snowflakeの特徴を利用して、任意の個数のクラスタリングパターンを追加する。 そんな内容が書かれた記事を見つけたので読書感想文を書いてみる。 Defining multiple cluster keys in Snowflake with materialized views [arst_toc tag=\"h4\"] オレオレクラスタリングの必要性と難しさ 色々な理由でナチュラルクラスタリングに頼らず自力でクラスタリングキーを設定する場合がある。 その際、クエリの傾向をみて、より適切なプルーニングが行われるカラムを選ばないといけない。 (\"適切な\"といのは、想定する使われ方と異なる使われ方がされた場合にプルーニング対象が減り クエリパフォーマンスが落ちる頻度を減らすということ。) ナチュラルクラスタリングは良く出来ていて、オレオレキーがナチュラルキーを超えるのは難しい。 結構なケースで「あっちが立てばこっちが立たず」という状況になる(、と思う)。 腕力に任せてオレオレキーの数だけテーブルを作る ストレージの料金は安いのだから、クラスタリングキーの数だけテーブルを作ってしまえ。 という、マッチョなやり方を取ることもできる。 同一テーブルに対して一度に設定できるクラスタリングキーは限られているが、 CREATE TABLE文に CLUSTER BY を付けることできるので、 クラスタリングキーごとにテーブルを複数作ってしまえば、この問題はある程度解決する。 注文管理テーブル的なテーブル\"注文\"があったとして、\"注文日付\"、\"顧客キー\"、\"店員キー\"の いずれかを条件とするクエリが均等に来るだろうという前提。 以下のようにCREATE TABLE文に CLUSTER BY をつける。 --注文日付をクラスタリングキーとしてテーブルを再作成 create table \"注文_clustered_by_注文日付\" cluster by (\"注文日付\") as ( select \"注文日付\", \"注文キー\", \"顧客キー\", \"店員キー\" from \"テストDB\".\"テストスキーマ\".\"注文\" ) --顧客キーをクラスタリングキーとしてテーブルを再作成 create table \"注文_clustered_by_顧客キー\" cluster by (\"顧客キー\") as ( select \"注文日付\", \"注文キー\", \"顧客キー\", \"店員キー\" from \"テストDB\".\"テストスキーマ\".\"注文\" ) --店員キーをクラスタリングキーとしてテーブルを再作成 create table \"注文_clustered_by_店員キー\" cluster by (\"店員キー\") as ( select \"注文日付\", \"注文キー\", \"顧客キー\", \"店員キー\" from \"テストDB\".\"テストスキーマ\".\"注文\" ) もちろん通常のTableであれば利用者側が用途に応じて利用するテーブルを選択しないといけない。 利用者側にそれぞれのテーブルを提供し、利用者は用途に応じてテーブルを使用する必要がある。 もう一歩、使い勝手が良くない。利用者が1つのテーブルを触る際に、意識せずに(透過的に) 最適なクラスタリングが行われたデータにアクセスさせることはできないか。 Materialized Viewを使うとこれが可能になる。 Materialized Viewの復習 Snowflakeに限らず、普通のRDBMSにも Materialized View は存在する。 Materialized Viewの各列に設定したサブクエリを事前計算して、クエリ時に再利用する仕組み。 以下のようにすると、cntは事前に処理され、クエリ時に再利用される。 create materialized view \"注文_aggregated_by_日付\" as ( select \"日付\", count(*) as cnt from \"注文\" group by 1 ) Snowflakeの場合、ベーステーブルに対してクエリを行うと、自動的にMaterialized Viewが評価される。 例えば、以下のクエリは\"注文\"ではなく\"注文_aggregated_by_日付\"が評価される。 select \"日付\", count(*) as cnt from \"注文\" group by 1 meta-dataにMaterialized Viewの定義が記録され、特定のルールでベーステーブルを触ると meta-dataからルックアップされたMaterialized Viewが評価される、的な動きだろうと思っている。 ベーステーブルを変更(INSERT,UPDATE)すると、自動的にMaterialized Viewに反映される。 その際、ベーステーブルにおいて自動クラスタリングが行われる。(瞬時ではなく後で非同期に行われる) Materialized Viewの効果的な使い方 ベーステーブルをあらかじめ\"日付\"でソートしておく。 ナチュラルキーとして\"日付\"が選ばれて自動クラスタリングが行われ、\"日付\"のブロックごとに パーティションが並ぶことになる。 create table \"注文\" as ( select \"日付\", \"注文キー\", \"顧客キー\", \"店員キー\" from \"テストDB\".\"テストスキーマ\".\"注文\" order by \"日付\" ) \"注文\"テーブルは\"日付\"の順で並んでいるのだから、\"日付\"を条件としたクエリを叩くと、 超絶有効にプルーニングが効くことになる。 select \"日付\", count(*) as cnt from \"注文\" where \"日付\" between date\'1993-03-01\' and date\'1993-03-31\' group by 1 \"注文\"テーブルに対して\"顧客キー\"を指定してクエリを行なった場合、 もし Materialized View を作っていなかったとしたら、Full Scan的な検索になるはず。 select * from \"注文\" where \"顧客キー\"=52671775 そこで、以下のように\"顧客キー\"をクラスタリングキーとしてMaterialized Viewを作っておく。 create materialized view \"注文_clustered_by_顧客キー\" cluster by(\"顧客キー\") as ( select \"日付\", \"注文キー\", \"顧客キー\", \"店員キー\" from \"注文\" ) ; すると、以下のクエリは\"注文\"ではなく \"注文_clusterd_by_顧客キー\"に対して行われる。 select * from \"注文\" where \"顧客キー\"=52671775 Materialized Viewなので、ベーステーブルである\"注文\"に対して追加・更新を行うと、 ベーステーブルから派生させてMaterialized Viewも自動更新される。 ユーザには\"注文\"を提供するだけで良い。 (Materialized Viewへのアクセス権が必要) コストが許す限り望むだけのMaterialized Viewを作っておくことで、 ベーステーブルに対するクエリが最適なクラスタリングが行われたMaterialized Viewに 通される。