テーブル設計の考慮事項

このトピックでは、テーブルを設計および管理する際のベストプラクティス、一般的なガイドライン、および重要な考慮事項について説明します。

列の日付/時刻データ型

日付またはタイムスタンプを含むように列を定義するときは、文字データ型ではなく、 日付またはタイムスタンプデータ型 を選択することをSnowflakeはお勧めします。Snowflakeは VARCHAR よりも効率的に DATE および TIMESTAMP データを保存するため、クエリのパフォーマンスが向上します。必要な粒度のレベルに応じて、適切な日付またはタイムスタンプのデータ型を選択します。

参照整合性制約

参照整合性制約が標準テーブルに作成される場合、主キー/外部キー関係で定義される参照整合性制約は情報であり、強制はされません。NOT NULL 制約は強制されますが、他の制約は強制されません。ただし、 ハイブリッドテーブル の制約は強制されます。制約の概要 を参照してください。

一般的に、制約は貴重なメタデータを提供します。主キーと外部キーによって、プロジェクトチームはスキーマ設計を理解し、テーブルとその列の関係を見ることができます。

さらに、ほとんどのビジネスインテリジェンス（BI）および視覚化ツールは、テーブルと共に外部キー定義をインポートし、適切な結合条件を構築します。この方法は、誰かがテーブルの結合方法を推測し、ツールを手動で設定するよりも、時間を節約し、エラーを起こしにくい可能性があります。結合を主キーと外部キーに基づいて行うと、結合の解釈が異なる開発者に任されることがなくなるため、設計の整合性も向上します。一部の BI および視覚化ツールでは、制約情報を活用して、たとえば結合の削除を使用して、クエリをより効率的に書き換えることもできます。

CREATE | ALTER TABLE ... CONSTRAINT コマンドを使用してテーブルを作成または変更するときに制約を指定します。

次の例では、2番目のテーブル（salesorders）の CREATE TABLE ステートメントは、最初のテーブル（salespeople）の列を参照する行外の外部キー制約を定義しています：

CREATE OR REPLACE TABLE salespeople (
  sp_id INT NOT NULL UNIQUE,
  name VARCHAR DEFAULT NULL,
  region VARCHAR,
  constraint pk_sp_id PRIMARY KEY (sp_id)
);
CREATE OR REPLACE TABLE salesorders (
  order_id INT NOT NULL UNIQUE,
  quantity INT DEFAULT NULL,
  description VARCHAR,
  sp_id INT NOT NULL UNIQUE,
  constraint pk_order_id PRIMARY KEY (order_id),
  constraint fk_sp_id FOREIGN KEY (sp_id) REFERENCES salespeople(sp_id)
);

GET_DDL 関数をクエリして、指定されたテーブルを再作成するために実行できる DDL ステートメントを取得します。このステートメントには、テーブルに現在設定されている制約が含まれます。

例:

SELECT GET_DDL('TABLE', 'mydb.public.salesorders');

+-----------------------------------------------------------------------------------------------------+
| GET_DDL('TABLE', 'MYDB.PUBLIC.SALESORDERS')                                                         |
|-----------------------------------------------------------------------------------------------------|
| create or replace TABLE SALESORDERS (                                                               |
|   ORDER_ID NUMBER(38,0) NOT NULL,                                                                   |
|   QUANTITY NUMBER(38,0),                                                                            |
|   DESCRIPTION VARCHAR(16777216),                                                                    |
|   SP_ID NUMBER(38,0) NOT NULL,                                                                      |
|   unique (SP_ID),                                                                                   |
|   constraint PK_ORDER_ID primary key (ORDER_ID),                                                    |
|   constraint FK_SP_ID foreign key (SP_ID) references MYDATABASE.PUBLIC.SALESPEOPLE(SP_ID)           |
| );                                                                                                  |
+-----------------------------------------------------------------------------------------------------+

または、Information Schemaの TABLE_CONSTRAINTS ビュー をクエリして、スキーマごとに（またはデータベース内のすべてのスキーマ全体で）すべてのテーブル制約のリストを取得します。

例:

SELECT table_name, constraint_type, constraint_name
  FROM mydb.INFORMATION_SCHEMA.TABLE_CONSTRAINTS
  WHERE constraint_schema = 'PUBLIC'
  ORDER BY table_name;

+-------------+-----------------+-----------------------------------------------------+
| TABLE_NAME  | CONSTRAINT_TYPE | CONSTRAINT_NAME                                     |
|-------------+-----------------+-----------------------------------------------------|
| SALESORDERS | UNIQUE          | SYS_CONSTRAINT_fce2257e-c343-4e66-9bea-fc1c041b00a6 |
| SALESORDERS | FOREIGN KEY     | FK_SP_ID                                            |
| SALESORDERS | PRIMARY KEY     | PK_ORDER_ID                                         |
| SALESORDERS | UNIQUE          | SYS_CONSTRAINT_bf90e2b3-fd4a-4764-9576-88fb487fe989 |
| SALESPEOPLE | PRIMARY KEY     | PK_SP_ID                                            |
+-------------+-----------------+-----------------------------------------------------+

クラスタリングキーを設定するタイミング

ほとんどのテーブルでは、 クラスタリングキー を指定する必要はありません。Snowflakeは、最適化エンジンとマイクロパーティションを介して自動チューニングを実行します。多くの場合、データはロードされ、日付またはタイムスタンプによってマイクロパーティションに編成され、同じディメンションに沿ってクエリされます。

テーブルのクラスタリングキーはいつ指定する必要がありますか？まず、小さなテーブルをクラスタリングしても、クエリのパフォーマンスが大幅に向上することはありません。

データセットが大きい場合、次の場合にテーブルのクラスタリングキーを指定することを検討できます：

  • データがロードされる順序は、最も一般的にクエリされるディメンションと一致しません（たとえば、データは日付でロードされますが、レポートはデータを ID でフィルターします）。既存のスクリプトまたはレポートが日付 :emph:` と ` ID の両方（および場合によっては3列目または4列目）でデータをクエリする場合、複数列のクラスタリングキーを作成することでパフォーマンスが向上する場合があります。

  • クエリプロファイル は、テーブルに対する一般的なクエリの合計継続時間のかなりの割合がスキャンに費やされていることを示します。これは、1つ以上の特定の列でフィルタリングするクエリに適用されます。

再クラスタリングにより、既存のデータが異なる順序で書き換えられることに注意してください。Fail-safe保護を提供するために、以前の注文は7日間保存されます。テーブルを再クラスタリングすると、並べ替えられるデータのサイズに相関する計算コストが発生します。

詳細については、 自動クラスタリング をご参照ください。

列の長さを指定する場合

Snowflakeは列データを効果的に圧縮します。したがって、必要以上に大きな列を作成しても、データテーブルのサイズへの影響は最小限に抑えられます。同様に、最大長宣言（たとえば、 VARCHAR(134217728)）を持つ列と、精度が小さい列との間には、クエリパフォーマンスの違いはありません。

ただし、列データのサイズが予測可能な場合、Snowflakeは、次の理由から適切な列の長さを定義することをお勧めします。

  • データロードの操作では、列の順序が正しくないなどの問題を検出する可能性が高くなります（たとえば、VARCHAR（10）列に誤ってロードされた50文字の文字列）。このような問題によりエラーが発生します。

  • 列の長さが指定されていない場合、一部のサードパーティツールは最大サイズ値の消費を予測する場合があり、これはクライアント側のメモリ使用量の増加または異常な動作につながる可能性があります。

VARIANT 列への半構造化データの保存とネスト構造のフラット化

半構造化データに対して実行する操作の種類がまだわからない場合、現時点ではデータを VARIANT 列に保存することをSnowflakeはお勧めします。ほとんどが規則的でネイティブ型（文字列と整数）のみを使用するデータの場合、リレーショナルデータと VARIANT 列のデータに対する操作のストレージ要件とクエリパフォーマンスは非常に似ています。

プルーニングを改善し、ストレージの消費を抑えるために、半構造化データに以下が含まれる場合、Snowflakeは、オブジェクトとキーデータを別々のリレーショナル列にフラット化することをお勧めします。

  • 日付とタイムスタンプ、特に文字列値としてのISO 8601以外の日付とタイムスタンプ

  • 文字列内の数字

  • 配列

日付やタイムスタンプなどの非ネイティブ値は、 VARIANT 列にロードされると文字列として格納されるため、これらの値に対する操作は、対応するデータ型のリレーショナル列に格納される場合よりも遅くなり、より多くのスペースを消費します。

データのユースケースがわかっている場合は、一般的なデータセットでテストを実行します。データセットをテーブルの VARIANT 列にロードします。FLATTEN 関数を使用して、クエリする予定のオブジェクトとキーを別のテーブルに抽出します。両方のテーブルに対して一般的なクエリセットを実行して、どの構造が最高のパフォーマンスを提供するかを確認します。

永続テーブルの一時テーブルまたはその逆への変換

現在、 ALTER TABLE コマンドを使用して永続テーブルを 一時的な テーブルに変更することはできません。TRANSIENT プロパティはテーブルの作成時に設定され、変更することはできません。

同様に、一時テーブルを永続テーブルに直接変更することはできません。

列のデフォルトや付与された権限などのデータやその他の特性を保持したまま、既存の永続テーブルを一時テーブル（またはその逆）に変換するには、以下の例で説明するように、いずれかのインターフェイスを使用して新しいテーブルを作成します。

CREATE TABLE コマンドの COPY GRANTS 句を使用します。

CREATE TRANSIENT TABLE my_new_table LIKE my_old_table COPY GRANTS;

その後、 INSERT コマンドを使ってデータをコピーします。

INSERT INTO my_new_table SELECT * FROM my_old_table;

すべてのデータを保持したいが、付与された権限やその他の特性は保持したくない場合は、以下のインターフェイスのいずれかを使用できます。

CREATE TABLE AS SELECT （CTAS） ステートメントを使用します。

CREATE TRANSIENT TABLE my_transient_table AS SELECT * FROM mytable;

テーブルのコピーを作成する（ただしライフサイクルは永続的なものから一時的なものに変更する）もう1つの方法は、以下のインターフェイスのいずれかを使用してテーブルのクローンを作成することです。

CREATE TABLE コマンドの CLONE 句を使用します。

CREATE TRANSIENT TABLE foo CLONE bar COPY GRANTS;

古いパーティションは 影響を受けません （一時的にはなりません）が、クローンに追加された新しいパーティションは一時的なライフサイクルに従います。

一時テーブルを永続テーブルにクローンすることはできません。