テーブル設計の考慮事項¶

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

このトピックの内容：

列の日付/時刻データ型
参照整合性制約
クラスタリングキーを設定するタイミング
列の長さを指定する場合
VARIANT 列への半構造化データの保存とネスト構造のフラット化
永続テーブルの一時テーブルまたはその逆への変換

列の日付/時刻データ型¶

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

参照整合性制約¶

Snowflakeの参照整合性制約は情報提供であり、 NOT NULLを除き、強制されません。 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)
);

Copy

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

例：

select get_ddl('table', 'mydb.public.salesorders');

+-----------------------------------------------------------------------------------------------------+
| GET_DDL('TABLE', 'MYDATABASE.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)           |
| );                                                                                                  |
+-----------------------------------------------------------------------------------------------------+

Copy

または、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                                            |
+-------------+-----------------+-----------------------------------------------------+

Copy

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

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

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

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

データがロードされる順序は、最も一般的にクエリされるディメンションと一致しません（例えば、データは日付でロードされますが、レポートはデータを IDでフィルターします）。既存のスクリプトまたはレポートが日付と ID の両方（および場合によっては3列目または4列目）でデータをクエリする場合、複数列のクラスタリングキーを作成することでパフォーマンスが向上する場合があります。
クエリプロファイルは、テーブルに対する一般的なクエリの合計継続時間のかなりの割合がスキャンに費やされていることを示します。これは、1つ以上の特定の列でフィルタリングするクエリに適用されます。

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

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

列の長さを指定する場合¶

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

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

データロードの操作では、列の順序が正しくないなどの問題を検出する可能性が高くなります（たとえば、 VARCHAR(10) 列に誤ってロードされた50文字の文字列）。このような問題によりエラーが発生します。
列の長さが指定されていない場合、一部のサードパーティツールは最大サイズ値の消費を予測する場合があり、これはクライアント側のメモリ使用量の増加または異常な動作につながる可能性があります。

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

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

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

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

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

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

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

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

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

列のデフォルトや付与された権限などのデータやその他の特性を保持しながら、既存の永続テーブルを一時テーブルに（またはその逆に）変換するには、新しいテーブルを作成して COPY GRANTS 句を使用し、データをコピーします。

CREATE TRANSIENT TABLE my_new_table LIKE my_old_table COPY GRANTS;
INSERT INTO my_new_table SELECT * FROM my_old_table;

Copy

付与された権限やその他の特性ではなく、すべてのデータを保持したい場合は、 CREATE TABLE AS SELECT （CTAS）を使用できます。例：

CREATE TRANSIENT TABLE my_transient_table AS SELECT * FROM mytable;

Copy

テーブルのコピーを作成する別の方法（ただし、ライフサイクルを永続から一時に変更する）は、テーブルを CLONE することです。例：

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

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