Hinweise zum Tabellenentwurf¶
Unter diesem Thema werden bewährte Verfahren, allgemeine Richtlinien und wichtige Hinweise zum Entwerfen und Verwalten von Tabellen bereitgestellt.
Unter diesem Thema:
Datum/Uhrzeit-Datentypen für Spalten¶
Bei der Definition von Spalten, die Datumsangaben oder Zeitstempel enthalten, empfiehlt Snowflake, anstelle eines Zeichendatentyps einen Datums- oder Zeitstempel-Datentyp zu verwenden. Snowflake speichert DATE- und TIMESTAMP-Daten effizienter als VARCHAR-Daten, wodurch die Abfrageleistung verbessert wird. Wählen Sie einen geeigneten Datums- oder Zeitstempel-Datentyp aus, abhängig von der erforderlichen Granularität.
Einschränkungen für die referenzielle Integrität¶
Einschränkungen für die referenzielle Integrität sind in Snowflake informativ und werden mit Ausnahme von NOT NULL nicht erzwungen. Andere Einschränkungen als NOT NULL werden als deaktiviert erstellt.
Einschränkungen liefern jedoch wertvolle Metadaten. Mithilfe der Primär- und Fremdschlüssel können sich Mitglieder Ihres Projektteams am Schemaentwurf orientieren und sich mit den Beziehungen zwischen den Tabellen vertraut machen.
Darüber hinaus importieren die meisten Business Intelligence-Tools (BI) und Visualisierungstools die Fremdschlüsseldefinitionen mit den Tabellen und erstellen die korrekten Join-Bedingungen. Dieser Ansatz spart Zeit und ist potenziell weniger fehleranfällig, da später niemand raten muss, wie die Tabellen mit Join verknüpft werden können, und das Tool dann manuell konfiguriert werden muss. Durch das Verknüpfen über Primär- und Fremdschlüssel wird auch die Integrität des Entwurfs sichergestellt, da die Verknüpfungen nicht unterschiedlichen Entwicklern zur Interpretation überlassen werden. Einige BI- und Visualisierungstools nutzen auch Einschränkungsinformationen, um Abfragen in effizientere Formen umzuschreiben, z. B. durch Beseitigen von Join.
Geben Sie eine Einschränkung an, wenn Sie eine Tabelle mit den Befehlen CREATE | ALTER TABLE … CONSTRAINT erstellen oder ändern.
Im folgenden Beispiel definiert die Anweisung CREATE TABLE für die zweite Tabelle (salesorders) eine Out-of-Line-Fremdschlüsseleinschränkung, die auf eine Spalte in der ersten Tabelle (salespeople) verweist:
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)
);
Fragen Sie die Funktion GET_DDL ab, um eine DDL-Anweisung abzurufen, durch deren Ausführung die angegebene Tabelle neu erstellt wird. Die Anweisung enthält die Einschränkungen, die aktuell für eine Tabelle festgelegt sind.
Beispiel:
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) |
| ); |
+-----------------------------------------------------------------------------------------------------+
Rufen Sie alternativ eine Liste aller Tabelleneinschränkungen nach Schema (oder über alle Schemas in einer Datenbank) ab, indem Sie die Ansicht TABLE_CONSTRAINTS-Ansicht im Information Schema abfragen.
Beispiel:
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 |
+-------------+-----------------+-----------------------------------------------------+
Verwendung eines Gruppierungsschlüssels¶
Für die meisten Tabellen ist die Angabe eines Gruppierungsschlüssels nicht erforderlich. Snowflake führt die automatische Optimierung über die Optimierungs-Engine und durch Mikropartitionierung durch. In vielen Fällen werden Daten nach Datum oder Zeitstempel geladen und in Mikropartitionen organisiert und in derselben Dimension abgefragt.
Wann sollte ein Gruppierungsschlüssel für eine Tabelle angeben werden? Beachten Sie zunächst, dass das Clustern einer kleinen Tabelle die Abfrageleistung normalerweise nicht wesentlich verbessert.
In folgenden Fällen können Sie bei größeren Datasets in Betracht ziehen, einen Gruppierungsschlüssel für eine Tabelle anzugeben:
Die Reihenfolge, in der die Daten geladen werden, stimmt nicht mit der Dimension überein, anhand der sie am häufigsten abgefragt werden (z. B. werden die Daten anhand des Datums geladen, die Berichte jedoch anhand der ID gefiltert). Wenn Ihre vorhandenen Skripte oder Berichte die Daten nach Datum und ID (und möglicherweise nach einer dritten oder vierten Spalte) abfragen, können Sie durch das Erstellen eines mehrspaltigen Gruppierungsschlüssels einige Leistungsverbesserungen erzielen.
Mit Query Profile wird festgestellt, dass ein erheblicher Prozentsatz der Gesamtdauer typischer Abfragen auf der Tabelle für das Scannen verbraucht wird. Dies gilt für Abfragen, die nach einer oder mehreren bestimmten Spalten filtern.
Hinweis: Beim Reclustering werden vorhandene Daten in einer anderen Reihenfolge geschrieben. Die vorherige Reihenfolge wird 7 Tage lang gespeichert, um Fail-safe-Schutz zu bieten. Beim Reclustering einer Tabelle entstehen Computekosten, die mit der Menge der neu geordneten Daten korrelieren.
Weitere Informationen dazu finden Sie unter Automatic Clustering.
Angabe der Spaltenlängen¶
Snowflake komprimiert die Spaltendaten effektiv. Das Erstellen von Spalten, die größer als nötig sind, hat daher nur minimale Auswirkungen auf die Größe der Datentabellen. Ebenso gibt es keinen Unterschied in der Abfrageleistung zwischen einer Spalte mit Angabe der maximalen Länge (z. B. VARCHAR(16777216)) und einer geringeren Genauigkeit.
Wenn die Größe Ihrer Spaltendaten vorhersehbar ist, empfiehlt Snowflake das Definieren einer geeigneten Spaltenlänge aus den folgenden Gründen:
Datenladeoperationen erkennen mit höherer Wahrscheinlichkeit Probleme wie falsch geladene Spalten, z. B. eine 50-stellige Zeichenfolge, die irrtümlicherweise in eine VARCHAR(10)-Spalte geladen wurde. Solche Probleme führen zu Fehlern.
Wenn die Spaltenlänge nicht angegeben ist, kann es vorkommen, dass einige Tools von Drittanbietern als Größe den maximalen Wert verwenden. Dies kann zu einer erhöhten Speichernutzung des Clients oder zu einem ungewöhnlichen Verhalten führen.
Speichern von semistrukturierten Daten in einer VARIANT-Spalte vs. Vereinfachen der geschachtelten Struktur¶
Wenn Sie sich noch nicht sicher sind, welche Arten von Operationen Sie mit Ihren semistrukturierten Daten durchführen werden, empfiehlt Snowflake, diese zunächst in einer VARIANT-Spalte zu speichern. Für Daten, die meist regulär sind und nur native Typen (Zeichenfolgen und ganze Zahlen) verwenden, sind die Speicheranforderungen und die Abfrageleistung bei Operationen auf relationalen Daten und Daten in einer VARIANT-Spalte sehr ähnlich.
Für ein besseres Verkürzen und einen geringeren Speicherverbrauch empfiehlt Snowflake, Ihre Objekt- und Schlüsseldaten in separate relationale Spalten zu vereinfachen, wenn Ihre semistrukturierten Daten Folgendes enthalten:
Datumsangaben und Zeitstempel, insbesondere wenn diese nicht ISO-8601-konform sind, als Zeichenfolgenwerte
Zahlen innerhalb von Zeichenfolgen
Arrays
Nicht-native Werte wie Datum und Zeitstempel werden beim Laden in eine VARIANT-Spalte als Zeichenfolgen gespeichert, sodass Operationen mit diesen Werten langsamer sein können und auch mehr Platz beanspruchen als bei der Speicherung in einer relationalen Spalte mit dem entsprechenden Datentyp.
Wenn Sie Ihre Anwendungsfälle für die Daten kennen, führen Sie Tests an einem typischen Dataset durch. Laden Sie das Dataset in die VARIANT-Spalte einer Tabelle. Verwenden Sie die Funktion FLATTEN, um die Objekte und Schlüssel, die Sie abfragen möchten, in eine separate Tabelle zu extrahieren. Führen Sie einige typische Abfragen für beide Tabellen aus, um festzustellen, welche Struktur die beste Performance bietet.
Konvertieren einer permanenten Tabelle in eine transiente Tabelle oder umgekehrt¶
Derzeit ist es nicht möglich, eine permanente Tabelle mit dem Befehl ALTER TABLE in eine transiente Tabelle zu ändern. Die Eigenschaft TRANSIENT wird bei der Tabellenerstellung festgelegt und kann nicht geändert werden.
Außerdem ist es derzeit nicht möglich, eine transiente Tabelle direkt in eine permanente Tabelle zu ändern.
Um eine vorhandene permanente Tabelle in eine transiente Tabelle (oder umgekehrt) zu konvertieren und dabei Daten und andere Merkmale wie Spaltenstandards und erteilte Berechtigungen beizubehalten, können Sie eine neue Tabelle erstellen und die Klausel COPY GRANTS verwenden. Kopieren Sie anschließend die Daten:
CREATE TRANSIENT TABLE my_new_table LIKE my_old_table COPY GRANTS; INSERT INTO my_new_table SELECT * FROM my_old_table;
Wenn Sie alle Daten, aber nicht die erteilten Berechtigungen und anderen Merkmale bewahren möchten, können Sie CREATE TABLE AS SELECT (CTAS) verwenden. Beispiel:
CREATE TRANSIENT TABLE my_transient_table AS SELECT * FROM mytable;
Eine weitere Möglichkeit, eine Kopie einer Tabelle zu erstellen (aber den Lebenszyklus von permanent in transient zu ändern), besteht darin, die Tabelle mit CLONE zu klonen. Beispiel:
CREATE TRANSIENT TABLE foo CLONE bar COPY GRANTS;Alte Partitionen sind nicht betroffen (sie werden nicht transient), aber neue Partitionen, die dem Klon hinzugefügt werden, folgen dem transienten Lebenszyklus.
Eine transiente Tabelle können Sie nicht in eine permanente Tabelle klonen.