Remarques relatives à la conception de table¶
Ce chapitre fournit des bonnes pratiques, des lignes directrices générales et des remarques importantes sur la conception et la gestion de tables.
Dans ce chapitre :
Types de données date/heure pour les colonnes¶
Lorsque vous définissez des colonnes pour qu’elles contiennent des dates ou des horodatages, Snowflake recommande de choisir un type de donnée « Date » ou « Horodatage » plutôt qu’un type de données « Caractère ». Snowflake stocke les données DATE et TIMESTAMP plus efficacement que VARCHAR, ce qui permet d’obtenir des performances de requête plus élevées. Sélectionnez un type de données d’horodatage ou de date approprié en fonction du niveau de granularité requis.
Contraintes d’intégrité référentielle¶
Les contraintes d’intégrité référentielle dans Snowflake existent à des fins d’information et, à l’exception de NOT NULL, ne sont pas appliquées. Les contraintes autres que NOT NULL sont créées et automatiquement désactivées.
Cependant, les contraintes fournissent des métadonnées précieuses. Les clés primaires et les clés étrangères permettent aux membres de votre équipe de projet de s’orienter vers la conception du schéma et de se familiariser avec la manière dont les tables sont reliées entre elles.
De plus, la plupart des outils de Business Intelligence (BI) et de visualisation importent les définitions de clé étrangère avec les tables, et créent les conditions de liaison adéquates. Cette approche permet de vous faire gagner du temps, et de réduire le risque d’erreur par rapport à devoir deviner plus tard la façon dont les tables sont liées et à configurer manuellement l’outil. Le fait de baser les liaisons sur les clés primaires et étrangères permet également d’assurer l’intégrité de la conception, puisque les liaisons (ou dites « jointures ») ne sont pas laissées à l’interprétation des différents développeurs. Certains outils de BI et de visualisation exploitent également les informations de contrainte pour réécrire les requêtes sous des formes plus efficaces, par exemple l’élimination de liaison.
Spécifiez une contrainte lors de la création ou de la modification d’une table à l’aide des commandes CREATE | ALTER TABLE … CONSTRAINT.
Dans l’exemple suivant, l’instruction CREATE TABLE de la deuxième table (salesorders) définit une contrainte de clé étrangère hors ligne qui fait référence à une colonne de la première 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)
);
Interrogez la fonction GET_DDL pour récupérer une instruction DDL qui pourrait être exécutée pour recréer la table spécifiée. L’instruction contient les contraintes actuellement définies sur une table.
Par exemple :
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) |
| ); |
+-----------------------------------------------------------------------------------------------------+
Vous pouvez également récupérer une liste de toutes les contraintes de table par schéma (ou à travers tous les schémas d’une base de données) en interrogeant la vue Vue TABLE_CONSTRAINTS dans le schéma d’information.
Par exemple :
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 |
+-------------+-----------------+-----------------------------------------------------+
Quand définir une clé de clustering ?¶
Spécifier une clé de clustering n’est pas nécessaire pour la plupart des tables. Snowflake effectue un réglage automatique via le moteur d’optimisation et le micro-partitionnement. Dans de nombreux cas, les données sont chargées et organisées en micropartitions par date ou horodatage, et sont interrogées selon la même dimension.
Quand est-il conseillé de spécifier une clé de clustering pour une table ? Tout d’abord, sachez que le clustering d’une petite table n’améliore généralement pas les performances de requête de manière significative.
Pour les ensembles de données plus importants, vous pouvez spécifier une clé de clustering pour une table dans les cas suivants :
L’ordre dans lequel les données sont chargées ne correspond pas à la dimension par laquelle elles sont le plus souvent interrogées. Par exemple, les données sont chargées par date, mais les rapports filtrent les données par ID. Si vos scripts ou rapports existants interrogent les données par date et ID (et éventuellement une troisième ou quatrième colonne), vous pouvez voir une certaine amélioration des performances en créant une clé de clustering multi-colonnes.
Le profil de requête indique qu’un pourcentage important de la durée totale des requêtes classiques dans la table est consacré à l’analyse. Ceci s’applique aux requêtes qui filtrent sur une ou plusieurs colonnes spécifiques.
Notez que le reclustering réécrit les données existantes avec un ordre différent. L’ordre précédent est conservé pendant 7 jours afin d’assurer une protection Fail-safe. Le reclustering d’une table entraîne des coûts de calcul qui sont corrélés à la taille des données qui sont réorganisées.
Pour plus d’informations, voir Clustering automatique.
Quand spécifier les longueurs de colonnes¶
Snowflake compresse efficacement les données de colonne. Par conséquent, la création de colonnes plus grandes que nécessaire a des conséquences minimes sur la taille des tables de données. De même, il n’y a pas de différence de performance de requête entre une colonne avec une déclaration de longueur maximale, par exemple VARCHAR(16777216) et une précision inférieure.
Cependant, lorsque la taille de vos données de colonne est prévisible, Snowflake recommande de définir une longueur de colonne appropriée, et ceci pour les raisons suivantes :
Les opérations de chargement de données sont plus susceptibles de détecter des problèmes, tels que des colonnes chargées dans le mauvais ordre, par exemple une chaîne de 50 caractères chargée par erreur dans une colonne VARCHAR(10). Ce genre de problème crée des erreurs.
Lorsque la longueur de colonne n’est pas spécifiée, certains outils tiers peuvent anticiper la consommation de la valeur de taille maximale, ce qui peut se traduire par une utilisation accrue de mémoire côté client ou un comportement inhabituel.
Stockage de données semi-structurées dans une colonne VARIANT vs. Aplatissement de la structure imbriquée¶
Si vous n’êtes pas encore sûr des types d’opérations que vous voulez effectuer sur vos données semi-structurées, Snowflake recommande de stocker les données dans une colonne VARIANT pour le moment. Pour les données qui sont le plus souvent régulières et n’utilisent que des types natifs (chaînes et entiers), les exigences de stockage et les performances de requête pour les opérations sur les données relationnelles et les données dans une colonne VARIANT sont très similaires.
Pour un meilleur élagage et une consommation de stockage réduite, Snowflake recommande d’aplatir votre objet et vos données clés en colonnes relationnelles séparées si vos données semi-structurées incluent :
Des dates et horodatages, et plus particulièrement des dates et horodatages non ISO 8601 en tant que valeurs de chaîne.
Des nombres dans les chaînes.
Des tableaux.
Les valeurs non natives, telles que les dates et les horodatages, sont stockées sous forme de chaînes lorsqu’elles sont chargées dans une colonne VARIANT. Les opérations sur ces valeurs peuvent donc être plus lentes et consommer plus d’espace que lorsqu’elles sont stockées dans une colonne relationnelle avec le type de données correspondant.
Si vous connaissez les cas d’utilisation des données, effectuez des tests sur un ensemble de données classique. Chargez l’ensemble de données dans une colonne VARIANT d’une table. Utilisez la fonction FLATTEN pour extraire les objets et les clés que vous souhaitez interroger vers une table séparée. Exécutez un ensemble classique de requêtes sur les deux tables pour voir quelle structure fournit les meilleures performances.
Conversion d’une table permanente en table transitoire ou vice-versa¶
Actuellement, il n’est pas possible de changer une table permanente en table transitoire en utilisant la commande ALTER TABLE. La propriété TRANSIENT est définie au moment de la création de la table et ne peut pas être modifiée.
Actuellement, il n’est pas possible de changer directement une table transitoire en table permanente.
Pour convertir une table permanente existante en une table transitoire (ou inversement) tout en préservant les données et autres caractéristiques telles que les valeurs par défaut des colonnes et les privilèges accordés, vous pouvez créer une nouvelle table et utiliser la clause COPY GRANTS, puis copier les données :
CREATE TRANSIENT TABLE my_new_table LIKE my_old_table COPY GRANTS; INSERT INTO my_new_table SELECT * FROM my_old_table;
Si vous souhaitez conserver toutes les données, mais pas les privilèges accordés et d’autres caractéristiques, vous pouvez utiliser une commande CREATE TABLE AS SELECT (CTAS), par exemple :
CREATE TRANSIENT TABLE my_transient_table AS SELECT * FROM mytable;
Une autre façon de faire une copie d’une table (mais ceci change le cycle de vie de permanent à transitoire) consiste à CLONE la table, par exemple :
CREATE TRANSIENT TABLE foo CLONE bar COPY GRANTS;Les anciennes partitions ne sont pas affectées (elles ne deviendront pas transitoires), mais les nouvelles partitions ajoutées au clone suivront le cycle de vie transitoire.
Vous ne pouvez pas cloner une table transitoire vers une table permanente.