Premiers pas avec les tables hybrides¶

Introduction¶

Une table hybride est un type de table Snowflake optimisé pour les charges de travail transactionnelles et analytiques hybrides. Ces charges de travail nécessitent une faible latence et un débit élevé sur des lectures et écritures petites mais aléatoires, qui accèdent souvent à une seule ligne d’une table. Les tables hybrides appliquent des contraintes d’intégrité uniques et référentielles, qui sont essentielles pour les charges de travail transactionnelles.

Vous pouvez utiliser une table hybride avec d’autres tables et fonctionnalités Snowflake pour optimiser les charges de travail Unistore qui rassemblent les données transactionnelles et analytiques en une seule plateforme.

Les tables hybrides sont intégrées en toute transparente à l’architecture Snowflake existante. Les clients se connectent au même service de base de données Snowflake. Les requêtes sont compilées et optimisées dans la couche de services Cloud et exécutées dans le même moteur de requête dans les entrepôts virtuels. Cette architecture offre plusieurs avantages clés :

• Les fonctionnalités de la plateforme Snowflake telles que la gouvernance des données fonctionnent d’emblée avec les tables hybrides.

• Vous pouvez exécuter des charges de travail hybrides mélangeant des requêtes opérationnelles et analytiques.

• Vous pouvez joindre des tables hybrides à d’autres tables Snowflake et la requête s’exécute nativement et efficacement dans le même moteur de requête. Aucune fédération n’est obligatoire.

• Vous pouvez exécuter une transaction atomique sur des tables hybrides et d’autres tables Snowflake. Il n’est pas nécessaire d’orchestrer votre propre validation en deux phases.

Les tables hybrides s’appuient sur un magasin de lignes comme magasin de données principal pour offrir d’excellentes performances de requêtes opérationnelles. Lorsque vous écrivez dans une table hybride, les données sont écrites directement dans le magasin de lignes. Les données sont copiées de manière asynchrone dans le stockage d’objets afin d’améliorer les performances et l’isolation de la charge de travail des analyses volumineuses, sans incidence sur les charges de travail opérationnelles en cours. Certaines données peuvent également être mises en cache au format de colonne dans votre entrepôt afin d’améliorer les performances des requêtes analytiques. Il vous suffit d’exécuter des instructions SQL sur la table hybride logique et l’optimiseur de requêtes décide où lire les données afin de fournir les meilleures performances. Vous obtenez une vue cohérente de vos données sans avoir à vous soucier de l’infrastructure sous-jacente.

Conditions préalables¶

Ce tutoriel suppose que vous :

• Êtes familiarisé avec l’interface Snowsight

• Êtes familiarisé avec SQL

• Utilisez un compte Snowflake dans sélectionner les régions AWS

• Avez la possibilité d’exécuter en tant qu’utilisateur ayant reçu le droit d’accès au rôle ACCOUNTADMIN

• Êtes conscient des fonctionnalités et limitations non prises en charge sur les tables hybrides

Étape 1. Configurer votre compte¶

Pour commencer, configurez votre compte Snowflake en créant une nouvelle feuille de calcul, un rôle, des objets de base de données et un entrepôt virtuel. Vous pourrez alors créer deux tables hybrides et une table standard. Suivez les étapes suivantes :

1. Dans Worksheets, cliquez sur le bouton + dans le coin supérieur droit de Snowsight, puis sélectionnez SQL Worksheet.

2. Renommez la feuille de calcul en sélectionnant son nom d’horodatage généré automatiquement et en tapant Hybrid Tables - QuickStart.

3. Complétez les étapes suivantes en copiant le bloc de commandes SQL dans votre feuille de calcul et exécutez-les toutes.

   1. Utilisez le rôle ACCOUNTADMIN pour créer le rôle personnalisé hybrid_quickstart_role, puis accordez ce rôle à l’utilisateur actuel.

   2. Créez l’entrepôt hybrid_quickstart_wh et la base de données hybrid_quickstart_db. Accordez la propriété de ces objets au nouveau rôle.

   3. Utilisez le nouveau rôle pour créer le schéma data.

   4. Utilisez le nouvel entrepôt. (La base de données et le schéma que vous avez créés sont déjà utilisés, par défaut.)

   USE ROLE ACCOUNTADMIN;
   CREATE OR REPLACE ROLE hybrid_quickstart_role;
   SET my_user = CURRENT_USER();
   GRANT ROLE hybrid_quickstart_role TO USER IDENTIFIER($my_user);
   
   CREATE OR REPLACE WAREHOUSE hybrid_quickstart_wh WAREHOUSE_SIZE = XSMALL, AUTO_SUSPEND = 300, AUTO_RESUME = TRUE;
   GRANT OWNERSHIP ON WAREHOUSE hybrid_quickstart_wh TO ROLE hybrid_quickstart_role;
   CREATE OR REPLACE DATABASE hybrid_quickstart_db;
   GRANT OWNERSHIP ON DATABASE hybrid_quickstart_db TO ROLE hybrid_quickstart_role;
   
   USE ROLE hybrid_quickstart_role;
   CREATE OR REPLACE SCHEMA data;
   
   USE WAREHOUSE hybrid_quickstart_wh;
   

   Copy

Étape 2. Créer et charger en masse trois tables¶

Ce tutoriel utilise l’entreprise fictive de food truck Tasty Bytes Snowflake pour simuler un cas d’utilisation où vous pouvez servir des données à une application.

Vous allez créer trois tables :

• order_header table hybride - Cette table stocke les métadonnées de commande telles que truck_id, customer_id, order_amount et ainsi de suite.

• truck table hybride - Cette table stocke les métadonnées du camion telles que truck_id, franchise_id, menu_type_id et ainsi de suite.

• truck_history tableau standard - Cette table stocke des informations historiques sur les food trucks, vous permettant de suivre les changements au fil du temps.

Vous créez des tables hybrides et standard pour démontrer à quel point elles fonctionnent bien ensemble. Néanmoins, les tables hybrides présentent quelques différences fondamentales dans leur définition et leur comportement :

• Les tables hybrides nécessitent une clé primaire sur une ou plusieurs colonnes (ce qui implique la création d’un index de clé primaire).

• Les tables hybrides permettent la création d’index secondaires sur n’importe quelle colonne.

• Les contraintes PRIMARY KEY, FOREIGN KEY et UNIQUE </sql-reference/constraints-overview> sont toutes appliquées aux tables hybrides.

• Les verrous sur les tables hybrides sont au niveau de la ligne, pas au niveau de la table.

• Les données de la table hybride résident dans un stockage de lignes, mais sont également copiées dans un stockage d’objets en colonnes.

Ces différences se traduisent par :

• La prise en charge de l’intégrité référentielle lorsque les données de la table sont chargées, mises à jour ou supprimées.

• Des opérations DML plus rapides (en particulier celles qui mettent à jour des lignes uniques).

• Des requêtes de recherche plus rapides.

Vous pouvez charger des données en masse dans des tables hybrides en copiant des données à partir d’une zone de préparation ou d’autres tables (c’est-à-dire en utilisant CTAS, COPY INTO <table> ou INSERT INTO. .. SELECT). Le chargement en masse de données dans une table hybride à l’aide d’une instruction CTAS est fortement recommandé car il existe plusieurs optimisations qui ne peuvent être appliquées que lorsque les données sont chargées dans le cadre de la création de la table.

Créez un format de fichier, qui décrit un ensemble de données préparées auquel vous pouvez accéder ou que vous pouvez charger dans les tables Snowflake, et une zone de préparation </user-guide/data-load-overview>, qui est un objet Snowflake qui pointe vers un emplacement de stockage cloud auquel Snowflake peut accéder pour ingérer et interroger des données. Les données sont stockées dans un compartiment S3 AWS accessible au public auquel vous faites référence lorsque vous créez la zone de préparation.

CREATE OR REPLACE FILE FORMAT csv_format TYPE = CSV FIELD_DELIMITER = ',' SKIP_HEADER = 1 NULL_IF = ('NULL', 'null') EMPTY_FIELD_AS_NULL = true;
CREATE OR REPLACE STAGE frostbyte_tasty_bytes_stage URL = 's3://sfquickstarts/hybrid_table_guide' FILE_FORMAT = csv_format;

Utilisez maintenant l’instruction LIST pour renvoyer tous les fichiers dans la FROSTBYTE_TASTY_BYTES_STAGE :

LIST @frostbyte_tasty_bytes_stage;

L’instruction doit renvoyer deux enregistrements : un pour le fichier TRUCK.csv et un pour le fichier ORDER_HEADER.csv.

Après avoir créé la zone de préparation, qui pointe vers l’emplacement des données dans le stockage cloud, vous pouvez créer et charger les données dans le truck en utilisant une commande CTAS qui sélectionne les données à partir du fichier TRUCK.csv. Notez la contrainte PRIMARY KEY sur la colonne truck_id.

La deuxième instruction DDL crée une table standard nommée truck_history, également en utilisant une instruction CTAS.

SET CURRENT_TIMESTAMP = CURRENT_TIMESTAMP();

CREATE OR REPLACE HYBRID TABLE truck (
  truck_id NUMBER(38,0) NOT NULL,
  menu_type_id NUMBER(38,0),
  primary_city VARCHAR(16777216),
  region VARCHAR(16777216),
  iso_region VARCHAR(16777216),
  country VARCHAR(16777216),
  iso_country_code VARCHAR(16777216),
  franchise_flag NUMBER(38,0),
  year NUMBER(38,0),
  make VARCHAR(16777216),
  model VARCHAR(16777216),
  ev_flag NUMBER(38,0),
  franchise_id NUMBER(38,0),
  truck_opening_date DATE,
  truck_email VARCHAR NOT NULL UNIQUE,
  record_start_time TIMESTAMP,
  PRIMARY KEY (truck_id)
  )
  AS
  SELECT
      t.$1 AS truck_id,
      t.$2 AS menu_type_id,
      t.$3 AS primary_city,
      t.$4 AS region,
      t.$5 AS iso_region,
      t.$6 AS country,
      t.$7 AS iso_country_code,
      t.$8 AS franchise_flag,
      t.$9 AS year,
      t.$10 AS make,
      t.$11 AS model,
      t.$12 AS ev_flag,
      t.$13 AS franchise_id,
      t.$14 AS truck_opening_date,
      CONCAT(truck_id, '_truck@email.com') truck_email,
      $CURRENT_TIMESTAMP AS record_start_time
    FROM @FROSTBYTE_TASTY_BYTES_STAGE (PATTERN=>'.*TRUCK.csv') t;

CREATE OR REPLACE TABLE truck_history (
  truck_id NUMBER(38,0) NOT NULL,
  menu_type_id NUMBER(38,0),
  primary_city VARCHAR(16777216),
  region VARCHAR(16777216),
  iso_region VARCHAR(16777216),
  country VARCHAR(16777216),
  iso_country_code VARCHAR(16777216),
  franchise_flag NUMBER(38,0),
  year NUMBER(38,0),
  make VARCHAR(16777216),
  model VARCHAR(16777216),
  ev_flag NUMBER(38,0),
  franchise_id NUMBER(38,0),
  truck_opening_date DATE,
  truck_email VARCHAR NOT NULL UNIQUE,
  record_start_time TIMESTAMP,
  record_end_time TIMESTAMP,
  PRIMARY KEY (truck_id)
  )
  AS
  SELECT
      t.$1 AS truck_id,
      t.$2 AS menu_type_id,
      t.$3 AS primary_city,
      t.$4 AS region,
      t.$5 AS iso_region,
      t.$6 AS country,
      t.$7 AS iso_country_code,
      t.$8 AS franchise_flag,
      t.$9 AS year,
      t.$10 AS make,
      t.$11 AS model,
      t.$12 AS ev_flag,
      t.$13 AS franchise_id,
      t.$14 AS truck_opening_date,
      CONCAT(truck_id, '_truck@email.com') truck_email,
      $CURRENT_TIMESTAMP AS record_start_time,
      NULL AS record_end_time
   FROM @frostbyte_tasty_bytes_stage (PATTERN=>'.*TRUCK.csv') t;

L’instruction DDL crée la structure de la table hybride order_header. Notez la contrainte PRIMARY KEY sur la colonne order_id, la contrainte FOREIGN KEY sur la colonne truck_id de la table truck et l’index secondaire sur la colonne order_ts.

CREATE OR REPLACE HYBRID TABLE order_header (
  order_id NUMBER(38,0) NOT NULL,
  truck_id NUMBER(38,0),
  location_id NUMBER(19,0),
  customer_id NUMBER(38,0),
  discount_id FLOAT,
  shift_id NUMBER(38,0),
  shift_start_time TIME(9),
  shift_end_time TIME(9),
  order_channel VARCHAR(16777216),
  order_ts TIMESTAMP_NTZ(9),
  served_ts VARCHAR(16777216),
  order_currency VARCHAR(3),
  order_amount NUMBER(38,4),
  order_tax_amount VARCHAR(16777216),
  order_discount_amount VARCHAR(16777216),
  order_total NUMBER(38,4),
  order_status VARCHAR(16777216) DEFAULT 'INQUEUE',
  PRIMARY KEY (order_id),
  FOREIGN KEY (truck_id) REFERENCES TRUCK(truck_id),
  INDEX IDX01_ORDER_TS(order_ts)
);

L’instruction DML suivante insère des données dans la table order_header, en utilisant une instruction INSERT INTO. .. SELECT.

INSERT INTO order_header (
  order_id,
  truck_id,
  location_id,
  customer_id,
  discount_id,
  shift_id,
  shift_start_time,
  shift_end_time,
  order_channel,
  order_ts,
  served_ts,
  order_currency,
  order_amount,
  order_tax_amount,
  order_discount_amount,
  order_total,
  order_status)
  SELECT
      t.$1 AS order_id,
      t.$2 AS truck_id,
      t.$3 AS location_id,
      t.$4 AS customer_id,
      t.$5 AS discount_id,
      t.$6 AS shift_id,
      t.$7 AS shift_start_time,
      t.$8 AS shift_end_time,
      t.$9 AS order_channel,
      t.$10 AS order_ts,
      t.$11 AS served_ts,
      t.$12 AS order_currency,
      t.$13 AS order_amount,
      t.$14 AS order_tax_amount,
      t.$15 AS order_discount_amount,
      t.$16 AS order_total,
      '' as order_status
    FROM @frostbyte_tasty_bytes_stage (PATTERN=>'.*ORDER_HEADER.csv') t;

Étape 3. Explorer vos données¶

Vous avez créé plus tôt le rôle hybrid_quickstart_role, l’entrepôt hybrid_quickstart_wh, la base de données hybrid_quickstart_db et le schéma data. Continuez à utiliser ces objets.

Vous avez également créé et chargé les tables truck, truck_history et order_header. Vous pouvez maintenant exécuter quelques requêtes et vous familiariser avec les données de ces tables et leurs métadonnées.

Utilisez la commande SHOW TABLES pour afficher les propriétés et les métadonnées des tables standard et des tables hybrides. Utilisez la commande SHOW HYBRID TABLES pour afficher les informations sur les tables hybrides uniquement.

SHOW TABLES LIKE '%truck%';

SHOW HYBRID TABLES LIKE '%order_header%';

Affichez les informations sur les colonnes de la table en utilisant les commandes DESCRIBE <objet>. Notez les colonnes avec les contraintes PRIMARY KEY et UNIQUE.

DESCRIBE TABLE truck;

DESCRIBE TABLE order_header;

Répertoriez les tables hybrides pour lesquelles vous avez des privilèges d’accès.

SHOW HYBRID TABLES;

Listez tous les index pour lesquels vous avez des privilèges d’accès. Notez la valeur dans la colonne is_unique pour chaque index.

SHOW INDEXES;

Regardez des exemples de données à partir des tables en exécutant ces requêtes simples.

SELECT * FROM truck LIMIT 10;
SELECT * FROM truck_history LIMIT 10;
SELECT * FROM order_header LIMIT 10;

Le résultat de la première requête ressemble à ce qui suit :

Étape 4. Tester le comportement des contraintes UNIQUE et FOREIGN KEY¶

Dans cette étape, vous testerez les contraintes UNIQUE et FOREIGN KEY. Ces contraintes sont appliquées lorsqu’elles sont définies sur des tables hybrides.

Les contraintes UNIQUE préservent l’intégrité des données en empêchant l’insertion de valeurs en double dans une colonne. Les contraintes FOREIGN KEY fonctionnent en tandem avec les contraintes PRIMARY KEY pour préserver l’intégrité référentielle. Une valeur ne peut pas être insérée dans une colonne de clé primaire si aucune valeur de clé étrangère correspondante n’existe dans la table référencée. Par exemple, la vente d’un produit avec ID 100 ne peut être enregistrée dans une table de faits de vente si aucun ID de produit de ce type n’existe déjà dans une table de dimensions de produit référencée.

Les deux types de contraintes prennent en charge l’exactitude et la cohérence des données pour les applications qui s’appuient fortement sur un traitement des transactions fiable mais rapide.

SET truck_email = (SELECT truck_email FROM truck LIMIT 1);
SET max_truck_id = (SELECT MAX(truck_id) FROM truck);
SET new_truck_id = $max_truck_id+1;
INSERT INTO truck VALUES
  ($new_truck_id,2,'Stockholm','Stockholm län','Stockholm','Sweden','SE',1,2001,'Freightliner','MT45 Utilimaster',0,276,'2020-10-01',$truck_email,CURRENT_TIMESTAMP());

Duplicate key value violates unique constraint SYS_INDEX_TRUCK_UNIQUE_TRUCK_EMAIL

SET new_unique_email = CONCAT($new_truck_id, '_truck@email.com');
INSERT INTO truck VALUES ($new_truck_id,2,'Stockholm','Stockholm län','Stockholm','Sweden','SE',1,2001,'Freightliner','MT45 Utilimaster',0,276,'2020-10-01',$new_unique_email,CURRENT_TIMESTAMP());

Étape 4.2. Tester une contrainte FOREIGN KEY¶

Dans cette étape, vous testerez une contrainte FOREIGN KEY.

Tout d’abord, montrez le DDL que vous avez utilisé pour créer la table order_header en exécutant la fonction GET_DDL. Notez la contrainte FOREIGN KEY pour la colonne truck_id dans la sortie.

SELECT GET_DDL('table', 'order_header');

Copy

La sortie de cette commande ressemble au résultat partiel suivant :

Essayez maintenant d’insérer un nouvel enregistrement dans la table order_header, en utilisant un ID de camion inexistant.

SET max_order_id = (SELECT MAX(order_id) FROM order_header);
SET new_order_id = ($max_order_id +1);
SET no_such_truck_id = -1;
INSERT INTO order_header VALUES
  ($new_order_id,$no_such_truck_id,6090,0,0,0,'16:00:00','23:00:00','','2022-02-18 21:38:46.000','','USD',17.0000,'','',17.0000,'');

Copy

L’instruction INSERT devrait échouer car elle viole les contraintes FOREIGN KEY sur la table truck. Vous devriez recevoir le message d’erreur suivant :

Foreign key constraint SYS_INDEX_ORDER_HEADER_FOREIGN_KEY_TRUCK_ID_TRUCK_TRUCK_ID was violated.

Utilisez maintenant la nouvelle variable new_truck_id que vous avez utilisée précédemment et insérez un nouvel enregistrement dans la table order_header :

INSERT INTO order_header VALUES
  ($new_order_id,$new_truck_id,6090,0,0,0,'16:00:00','23:00:00','','2022-02-18 21:38:46.000','','USD',17.0000,'','',17.0000,'');

Copy

L’instruction INSERT devrait s’exécuter avec succès cette fois-ci.

TRUNCATE TABLE truck;

91458 (0A000): Hybrid table 'TRUCK' cannot be truncated as it is involved in active foreign key constraints.

DELETE FROM truck WHERE truck_id = $new_truck_id;

Foreign keys that reference key values still exist.

DELETE FROM order_header WHERE order_id = $new_order_id;
DELETE FROM truck WHERE truck_id = $new_truck_id;

Étape 5 : Utiliser le verrouillage au niveau des lignes pour exécuter des mises à jour simultanées¶

Contrairement aux tables standard, qui utilisent le verrouillage au niveau des partitions ou des tables, les tables hybrides utilisent le verrouillage au niveau des lignes <label-txn-locking> pour les opérations de mise à jour. Le verrouillage au niveau des lignes permet des mises à jour simultanées sur des enregistrements indépendants, de sorte que les transactions n’attendent pas le verrouillage complet de la table. Pour les applications qui s’appuient sur des charges de travail transactionnelles importantes, les temps d’attente pour les verrous doivent être réduits au minimum, ce qui permet aux opérations simultanées d’accéder très fréquemment à la même table.

Dans cette étape, vous pouvez tester les mises à jour simultanées de différents enregistrements dans la table hybride order_header.

Vous utiliserez la feuille de calcul Hybrid Tables - QuickStart principale que vous avez créée précédemment, et vous créerez une nouvelle feuille de calcul nommée Hybrid Tables - QuickStart Session 2 pour simuler une nouvelle session. À partir de la feuille de calcul Hybrid Tables - QuickStart, vous démarrerez une nouvelle transaction en utilisant l’instruction BEGIN, puis exécuterez une instruction UPDATE (une opération DML). Avant d’exécuter l’instruction de transaction COMMIT, vous ouvrirez la feuille de calcul Hybrid Tables - QuickStart Session 2 et exécuterez une autre instruction UPDATE. Enfin, vous validerez la transaction ouverte.

Étape 5.2. Exécuter des mises à jour simultanées¶

Tout d’abord, ouvrez la feuille de calcul Hybrid Tables - QuickStart. Assurez-vous que vous utilisez le bon rôle, le bon entrepôt, la bonne base de données et le bon schéma, puis définissez et sélectionnez la variable max_order_id.

USE ROLE hybrid_quickstart_role;
USE WAREHOUSE hybrid_quickstart_wh;
USE DATABASE hybrid_quickstart_db;
USE SCHEMA data;

SET max_order_id = (SELECT MAX(order_id) FROM order_header);
SELECT $max_order_id;

Copy

Notez la valeur de la variable max_order_id.

Démarrez une nouvelle transaction et exécutez la première instruction UPDATE.

BEGIN;
UPDATE order_header
  SET order_status = 'COMPLETED'
  WHERE order_id = $max_order_id;

Copy

Notez que vous n’avez pas validé la transaction, il y a donc maintenant un verrou ouvert sur la ligne qui correspond à cette condition :

WHERE order_id = $max_order_id

Copy

Exécutez la commande SHOW TRANSACTIONS, qui doit renvoyer une seule transaction ouverte.

SHOW TRANSACTIONS;

Copy

La sortie de cette commande ressemble au résultat partiel suivant :

Ouvrez la feuille de calcul Hybrid Tables - QuickStart Session 2. Assurez-vous que vous utilisez le bon rôle, le bon entrepôt, la bonne base de données et le bon schéma, puis définissez et sélectionnez la variable min_order_id.

USE ROLE hybrid_quickstart_role;
USE WAREHOUSE hybrid_quickstart_wh;
USE DATABASE hybrid_quickstart_db;
USE SCHEMA data;

Copy

SET min_order_id = (SELECT MIN(order_id) FROM order_header);
SELECT $min_order_id;

Copy

Notez que la valeur min_order_id est différente de la valeur max_order_id que vous avez utilisée dans la première instruction UPDATE. Exécutez la deuxième instruction UPDATE.

UPDATE order_header
  SET order_status = 'COMPLETED'
  WHERE order_id = $min_order_id;

Copy

Étant donné que les tables hybrides utilisent le verrouillage au niveau des lignes et que la transaction ouverte verrouille la ligne WHERE order_id = $MAX_ORDER_ID, l’instruction UPDATE s’exécute correctement.

Ouvrez la feuille de calcul Hybrid Tables - QuickStart et validez la transaction ouverte.

COMMIT;

Copy

Exécutez la requête suivante pour afficher les enregistrements mis à jour :

SELECT * FROM order_header WHERE order_status = 'COMPLETED';

Copy

La sortie de cette commande ressemble au résultat partiel suivant :

Étape 6. Faire preuve de cohérence¶

Dans cette étape, vous découvrirez une fonctionnalité unique des tables hybrides : la possibilité d’exécuter des opérations multi-instructions de manière native, simple et efficace dans une transaction atomique cohérente, avec accès aux tables hybrides et aux tables standard. Les transactions Snowflake garantissent les propriétés « ACID » d’atomicité, de cohérence, d’isolation et de durabilité. Toute transaction donnée est traitée comme une unité atomique ; préserve la cohérence de l’état de la base de données lors des écritures ; est isolée des autres transactions concurrentes (comme si elles étaient exécutées de manière séquentielle) ; et est engagée durablement (reste engagée, une fois engagée).

Dans cet exemple, l’entreprise acquiert un nouveau camion du même modèle qu’un camion existant. Par conséquent, vous devez mettre à jour la colonne year pour l’enregistrement concerné dans la table truck hybride pour refléter le changement. Après cette mise à jour, vous devez rapidement mettre à jour une ligne et insérer une nouvelle ligne dans la table truck_history. Cette table standard permettra de suivre et de conserver toutes les modifications apportées au parc de camions au fil du temps. Vous effectuez toutes ces étapes dans le cadre d’une transaction explicitement validée.

BEGIN;
SET CURRENT_TIMESTAMP = CURRENT_TIMESTAMP();
UPDATE truck SET year = '2024', record_start_time=$CURRENT_TIMESTAMP WHERE truck_id = 1;
UPDATE truck_history SET record_end_time=$CURRENT_TIMESTAMP WHERE truck_id = 1 AND record_end_time IS NULL;
INSERT INTO truck_history SELECT *, NULL AS record_end_time FROM truck WHERE truck_id = 1;
COMMIT;

Étape 6.2. Afficher les résultats¶

Exécutez maintenant les requêtes SELECT pour examiner les résultats des instructions UPDATE et INSERT.

La première requête doit renvoyer deux lignes et la deuxième requête doit en renvoyer une.

SELECT * FROM truck_history WHERE truck_id = 1;

Copy

La sortie de cette commande ressemble au résultat partiel suivant :

SELECT * FROM truck WHERE truck_id = 1;

Copy

La sortie de cette commande ressemble au résultat partiel suivant :

Étape 7. Joindre une table hybride à une table standard¶

Dans cette étape, vous exécutez une requête join qui combine les données d’une table hybride (order_header) et d’une table standard (truck_history). Cette requête démontre l’interopérabilité des deux types de tables.

Étape 7.1. Explorer les données dans les tables¶

Vous avez précédemment créé et chargé la table order_header. Vous pouvez maintenant exécuter quelques requêtes et consulter certaines informations pour vous familiariser avec la table. Tout d’abord, répertoriez les tables de la base de données avec la commande SHOW TABLES, puis sélectionnez deux colonnes dans la sortie de cette liste.

SHOW TABLES IN DATABASE hybrid_quickstart_db;
SELECT "name", "is_hybrid" FROM TABLE(RESULT_SCAN(last_query_id()));

Copy

La sortie de cette commande ressemble au résultat partiel suivant :

Exécutez maintenant deux requêtes simples :

SELECT * FROM truck_history LIMIT 10;
SELECT * FROM order_header LIMIT 10;

Copy

La sortie de la deuxième requête ressemble au résultat partiel suivant :

Étape 7.2. Joindre une table hybride à une table standard¶

Pour joindre la table hybride order_header à la table standard truck_history, exécutez l’instruction SET suivante et la requête. La jonction de tables hybrides à des tables standard ne nécessite aucune syntaxe particulière.

SET order_id = (SELECT order_id FROM order_header LIMIT 1);

SELECT hy.*,st.*
  FROM order_header AS hy JOIN truck_history AS st ON hy.truck_id = st.truck_id
  WHERE hy.order_id = $order_id
    AND st.record_end_time IS NULL;

Copy

Le résultat de la jointure ressemble au résultat partiel suivant :

Étape 8. Démontrer la sécurité et la gouvernance¶

Dans cette étape, vous exécuterez deux exemples liés à la sécurité pour démontrer que la fonctionnalité de sécurité et de gouvernance Snowflake s’applique également aux tables standard et aux tables hybrides.

Les rôles et l’octroi de privilèges à ces rôles sont des mécanismes standard pour renforcer la sécurité lorsqu’un grand nombre d’utilisateurs de bases de données ont accès au même système, que la charge de travail soit transactionnelle, analytique ou hybride.

Étape 8.1. Configurer le contrôle d’accès aux tables hybrides et la gestion des utilisateurs¶

Le contrôle d’accès basé sur les rôles (RBAC) fonctionne de la même manière pour les tables hybrides et les tables standard. Vous pouvez gérer l’accès aux données de table hybride dans Snowflake en accordant des privilèges à certains rôles.

D’abord, créez un nouveau rôle hybrid_quickstart_bi_user_role. Utilisez le rôle ACCOUNTADMIN pour créer le nouveau rôle.

USE ROLE ACCOUNTADMIN;
CREATE ROLE hybrid_quickstart_bi_user_role;
SET my_user = CURRENT_USER();
GRANT ROLE hybrid_quickstart_bi_user_role TO USER IDENTIFIER($my_user);

Copy

Vous pouvez désormais accorder des privilèges USAGE pour l’entrepôt hybrid_quickstart_wh, la base de données hybrid_quickstart_db et tous ses schémas vers le nouveau rôle. Utilisez hybrid_quickstart_role pour exécuter les instructions GRANT.

USE ROLE hybrid_quickstart_role;
GRANT USAGE ON WAREHOUSE hybrid_quickstart_wh TO ROLE hybrid_quickstart_bi_user_role;
GRANT USAGE ON DATABASE hybrid_quickstart_db TO ROLE hybrid_quickstart_bi_user_role;
GRANT USAGE ON ALL SCHEMAS IN DATABASE hybrid_quickstart_db TO hybrid_quickstart_bi_user_role;

Copy

En utilisant le nouveau rôle (hybrid_quickstart_bi_user_role), essayez de sélectionner certaines données à partir de la table order_header.

USE ROLE hybrid_quickstart_bi_user_role;
USE DATABASE hybrid_quickstart_db;
USE SCHEMA data;

SELECT * FROM order_header LIMIT 10;

Copy

Vous ne pouvez sélectionner aucune donnée car le rôle hybrid_quickstart_bi_user_role n’a pas obtenu le privilège SELECT nécessaire sur les tables. Vous recevez le message d’erreur suivant :

Object 'ORDER_HEADER' does not exist or not authorized.

Pour résoudre ce problème, utilisez le rôle hybrid_quickstart_role pour accorder les privilèges SELECT sur toutes les tables du schéma data à hybrid_quickstart_bi_user_role.

USE ROLE hybrid_quickstart_role;
GRANT SELECT ON ALL TABLES IN SCHEMA DATA TO ROLE hybrid_quickstart_bi_user_role;

Copy

Essayez à nouveau de sélectionner des données à partir de la table hybride order_header.

USE ROLE hybrid_quickstart_bi_user_role;
SELECT * FROM order_header LIMIT 10;

Copy

Cette fois, la requête réussit car HYBRID_QUICKSTART_BI_USER_ROLE dispose des privilèges appropriés à tous les niveaux de la hiérarchie. La sortie ressemble au résultat partiel suivant :

Étape 8.2. Créer et mettre en œuvre une politique de masquage¶

Dans cette étape, vous créez une politique de masquage et l’appliquez à la colonne truck_email dans la table hybride truck en utilisant une instruction ALTER TABLE. .. ALTER COLUMN. Une politique de masquage est un moyen standard de contrôler la visibilité au niveau des colonnes des données pour les utilisateurs ayant des rôles et des privilèges différents.

Note

Pour créer des politiques de masquage, vous devez utiliser un compte Enterprise Edition (ou un compte de niveau supérieur). Si vous utilisez un compte Standard Edition, ignorez cette étape. Pour plus d’informations, voir Éditions Snowflake.

Utilisez le rôle hybrid_quickstart_role, puis créez la nouvelle politique de masquage, qui vise à masquer des valeurs de colonnes entières à partir de rôles non autorisés.

USE ROLE hybrid_quickstart_role;

CREATE MASKING POLICY hide_column_values AS
  (col_value VARCHAR) RETURNS VARCHAR ->
    CASE WHEN CURRENT_ROLE() IN ('HYBRID_QUICKSTART_ROLE') THEN col_value
      ELSE '***MASKED***'
      END;

Copy

Appliquez maintenant cette politique à la table hybride.

ALTER TABLE truck MODIFY COLUMN truck_email
  SET MASKING POLICY hide_column_values USING (truck_email);

Copy

Parce que vous utilisez actuellement le hybrid_quickstart_role, la colonne truck_email ne doit pas être masquée. Exécutez la requête suivante :

SELECT * FROM truck LIMIT 10;

Copy

Passez à HYBRID_QUICKSTART_BI_USER_ROLE et exécutez à nouveau la requête. La colonne TRUCK_EMAIL devrait être masquée maintenant.

USE ROLE hybrid_quickstart_bi_user_role;
SELECT * FROM truck LIMIT 10;

Copy

Étape 9. Nettoyage, conclusion et lectures complémentaires¶

USE ROLE hybrid_quickstart_role;
USE WAREHOUSE hybrid_quickstart_wh;
USE DATABASE hybrid_quickstart_db;
USE SCHEMA data;

DROP DATABASE hybrid_quickstart_db;
DROP WAREHOUSE hybrid_quickstart_wh;
USE ROLE ACCOUNTADMIN;
DROP ROLE hybrid_quickstart_role;
DROP ROLE hybrid_quickstart_bi_user_role;