Utiliser des commandes SQL pour créer et gérer des vues sémantiques¶
Cette rubrique explique comment utiliser les commandes SQL suivantes pour créer et gérer des vues sémantiques.
Créer une vue sémantique¶
Pour créer une vue sémantique, exécutez la commande CREATE SEMANTIC VIEW. Vous devez utiliser un rôle doté des privilèges suivants :
CREATE SEMANTIC VIEW sur le schéma dans lequel vous créez la vue sémantique.
USAGE sur la base de données et le schéma dans lesquels vous créez la vue sémantique.
SELECT sur les tables et les vues utilisées dans la vue sémantique.
La vue sémantique doit être valide. Voir Comment Snowflake valide les vues sémantiques.
Cet exemple utilise la fonction TPC-H sample data disponible dans Snowflake. Ce jeu de données contient des tables qui représentent un scénario métier simplifié avec des clients, des commandes et des lignes d’articles.
L’exemple crée une vue sémantique nommée tpch_rev_analysis en utilisant les tables de l’ensemble de données TPC-H. La vue sémantique définit :
Trois tables logiques (
orders,customersetline_items).Une relation entre les tables
ordersetcustomers.Une relation entre les tables
line_itemsetorders.Des faits qui seront utilisés pour calculer les métriques.
Des dimensions pour le nom du client, la date de la commande et l’année au cours de laquelle la commande a été passée.
Les métriques correspondant à la valeur moyenne d’une commande et au nombre de lignes moyen d’une commande.
CREATE SEMANTIC VIEW tpch_rev_analysis
TABLES (
orders AS SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS
PRIMARY KEY (o_orderkey)
WITH SYNONYMS ('sales orders')
COMMENT = 'All orders table for the sales domain',
customers AS SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER
PRIMARY KEY (c_custkey)
COMMENT = 'Main table for customer data',
line_items AS SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.LINEITEM
PRIMARY KEY (l_orderkey, l_linenumber)
COMMENT = 'Line items in orders'
)
RELATIONSHIPS (
orders_to_customers AS
orders (o_custkey) REFERENCES customers,
line_item_to_orders AS
line_items (l_orderkey) REFERENCES orders
)
FACTS (
line_items.line_item_id AS CONCAT(l_orderkey, '-', l_linenumber),
orders.count_line_items AS COUNT(line_items.line_item_id),
line_items.discounted_price AS l_extendedprice * (1 - l_discount)
COMMENT = 'Extended price after discount'
)
DIMENSIONS (
customers.customer_name AS customers.c_name
WITH SYNONYMS = ('customer name')
COMMENT = 'Name of the customer',
orders.order_date AS o_orderdate
COMMENT = 'Date when the order was placed',
orders.order_year AS YEAR(o_orderdate)
COMMENT = 'Year when the order was placed'
)
METRICS (
customers.customer_count AS COUNT(c_custkey)
COMMENT = 'Count of number of customers',
orders.order_average_value AS AVG(orders.o_totalprice)
COMMENT = 'Average order value across all orders',
orders.average_line_items_per_order AS AVG(orders.count_line_items)
COMMENT = 'Average number of line items per order'
)
COMMENT = 'Semantic view for revenue analysis';
Les sections suivantes expliquent cet exemple plus en détail :
Note
Pour un exemple complet, voir Exemple d’utilisation de SQL pour créer une vue sémantique.
Définir les tables logiques¶
Dans la commande CREATE SEMANTIC VIEW, utilisez la clause TABLES pour définir les tables logiques de la vue. Dans cette clause, vous pouvez :
Indiquer le nom de la table physique et un alias facultatif.
Identifiez les colonnes suivantes dans la table logique :
Les colonnes utilisées comme clés primaires.
Les colonnes qui contiennent des valeurs uniques (autres que les colonnes de clé primaire).
Vous pouvez utiliser ces colonnes pour définir les relations dans cette vue sémantique.
Ajouter des synonymes pour la table (afin d’améliorer la découvrabilité).
Ajouter un commentaire descriptif.
Dans l”exemple présenté plus haut, la clause TABLES définit trois tables logiques :
Une table
orderscontenant les informations sur les commandes de la table TPC-Horders.Une table
customerscontenant les informations sur les clients de la table TPC-Hcustomers.Une table
line_itemcontenant les lignes des commandes de la table TPC-Hlineitem.
L’exemple identifie les colonnes à utiliser comme clés primaires pour chaque table logique, afin que vous puissiez identifier les relations entre les tables.
L’exemple fournit également des synonymes et des commentaires qui décrivent les tables logiques et facilitent la découverte des données.
TABLES (
orders AS SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS
PRIMARY KEY (o_orderkey)
WITH SYNONYMS ('sales orders')
COMMENT = 'All orders table for the sales domain',
customers AS SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER
PRIMARY KEY (c_custkey)
COMMENT = 'Main table for customer data',
line_items AS SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.LINEITEM
PRIMARY KEY (l_orderkey, l_linenumber)
COMMENT = 'Line items in orders'
Identifier les relations entre les tables logiques¶
Dans la commande CREATE SEMANTIC VIEW, utilisez la clause RELATIONSHIPS pour identifier les relations entre les tables de la vue. Pour chaque relation, vous spécifiez :
Un nom facultatif pour la relation
Le nom de la table logique contenant la clé étrangère
Les colonnes de cette table qui définissent la clé étrangère
Le nom de la table logique contenant la clé primaire ou les colonnes à valeurs uniques
Les colonnes de cette table qui définissent la clé primaire ou qui contiennent des valeurs uniques.
Si vous avez déjà spécifié PRIMARY KEY pour la table logique dans la clause TABLES, vous n’avez pas besoin de spécifier la colonne de clé primaire dans la relation.
S’il existe un seul mot-clé UNIQUE pour la table logique dans la clause TABLES, vous n’avez pas besoin de spécifier les colonnes correspondantes dans la relation.
Dans l”exemple présenté plus haut, la clause RELATIONSHIPS spécifie deux relations :
Une relation entre les tables
ordersetcustomers. Dans la tableorders,o_custkeyest la clé étrangère qui renvoie à la clé primaire de la tablecustomers(c_custkey).Une relation entre les tables
line_itemsetorders. Dans la tableline_items,l_orderkeyest la clé étrangère qui renvoie à la clé primaire de la tableorders(o_orderkey).
RELATIONSHIPS (
orders_to_customers AS
orders (o_custkey) REFERENCES customers (c_custkey),
line_item_to_orders AS
line_items (l_orderkey) REFERENCES orders (o_orderkey)
)
Définir les faits, les dimensions et les métriques¶
Dans la commande CREATE SEMANTIC VIEW, utilisez les clauses FACTS, DIMENSIONS et METRICS pour définir les faits, les dimensions et les métriques de la vue sémantique.
Vous devez définir au moins une dimension ou une métrique dans la vue sémantique.
Pour chaque fait, dimension ou métrique, vous spécifiez :
La table logique à laquelle il appartient
Un nom pour le fait, la dimension ou la métrique
L’expression SQL pour le calculer
Des synonymes et commentaires facultatifs
L”exemple présenté plus haut définit plusieurs faits, dimensions et mesures :
FACTS (
line_items.line_item_id AS CONCAT(l_orderkey, '-', l_linenumber),
orders.count_line_items AS COUNT(line_items.line_item_id),
line_items.discounted_price AS l_extendedprice * (1 - l_discount)
COMMENT = 'Extended price after discount'
)
DIMENSIONS (
customers.customer_name AS customers.c_name
WITH SYNONYMS = ('customer name')
COMMENT = 'Name of the customer',
orders.order_date AS o_orderdate
COMMENT = 'Date when the order was placed',
orders.order_year AS YEAR(o_orderdate)
COMMENT = 'Year when the order was placed'
)
METRICS (
customers.customer_count AS COUNT(c_custkey)
COMMENT = 'Count of number of customers',
orders.order_average_value AS AVG(orders.o_totalprice)
COMMENT = 'Average order value across all orders',
orders.average_line_items_per_order AS AVG(orders.count_line_items)
COMMENT = 'Average number of line items per order'
)
Remplacer une vue sémantique existante¶
Pour remplacer une vue sémantique existante (par exemple, pour modifier la définition de la vue), indiquez OR REPLACE lors de l’exécution de CREATE SEMANTIC VIEW. Si vous souhaitez préserver les privilèges accordés à la vue sémantique existante, indiquez COPY GRANTS. Par exemple :
CREATE OR REPLACE SEMANTIC VIEW tpch_rev_analysis
TABLES (
orders AS SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS
PRIMARY KEY (o_orderkey)
WITH SYNONYMS ('sales orders')
COMMENT = 'All orders table for the sales domain',
customers AS SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER
PRIMARY KEY (c_custkey)
COMMENT = 'Main table for customer data',
line_items AS SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.LINEITEM
PRIMARY KEY (l_orderkey, l_linenumber)
COMMENT = 'Line items in orders'
)
RELATIONSHIPS (
orders_to_customers AS
orders (o_custkey) REFERENCES customers,
line_item_to_orders AS
line_items (l_orderkey) REFERENCES orders
)
FACTS (
line_items.line_item_id AS CONCAT(l_orderkey, '-', l_linenumber),
orders.count_line_items AS COUNT(line_items.line_item_id),
line_items.discounted_price AS l_extendedprice * (1 - l_discount)
COMMENT = 'Extended price after discount'
)
DIMENSIONS (
customers.customer_name AS customers.c_name
WITH SYNONYMS = ('customer name')
COMMENT = 'Name of the customer',
orders.order_date AS o_orderdate
COMMENT = 'Date when the order was placed',
orders.order_year AS YEAR(o_orderdate)
COMMENT = 'Year when the order was placed'
)
METRICS (
customers.customer_count AS COUNT(c_custkey)
COMMENT = 'Count of number of customers',
orders.order_average_value AS AVG(orders.o_totalprice)
COMMENT = 'Average order value across all orders',
orders.average_line_items_per_order AS AVG(orders.count_line_items)
COMMENT = 'Average number of line items per order'
)
COMMENT = 'Semantic view for revenue analysis and different comment'
COPY GRANTS;
Lister les vues sémantiques¶
Pour dresser la liste des vues sémantiques du schéma actuel ou d’un schéma spécifié, exécutez la commande SHOW SEMANTIC VIEWS. Par exemple :
SHOW SEMANTIC VIEWS;
+-------------------------------+-----------------------+---------------+-------------------+----------------------------------------------+-----------------+-----------------+-----------+
| created_on | name | database_name | schema_name | comment | owner | owner_role_type | extension |
|-------------------------------+-----------------------+---------------+-------------------+----------------------------------------------+-----------------+-----------------+-----------|
| 2025-03-20 15:06:34.039 -0700 | MY_NEW_SEMANTIC_MODEL | MY_DB | MY_SCHEMA | A semantic model created through the wizard. | MY_ROLE | ROLE | ["CA"] |
| 2025-02-28 16:16:04.002 -0800 | O_TPCH_SEMANTIC_VIEW | MY_DB | MY_SCHEMA | NULL | MY_ROLE | ROLE | NULL |
| 2025-03-21 07:03:54.120 -0700 | TPCH_REV_ANALYSIS | MY_DB | MY_SCHEMA | Semantic view for revenue analysis | MY_ROLE | ROLE | NULL |
+-------------------------------+-----------------------+---------------+-------------------+----------------------------------------------+-----------------+-----------------+-----------+
Vous pouvez également interroger les vues sémantiques des schémas ACCOUNT_USAGE et INFORMATION_SCHEMA.
Afficher les détails d’une vue sémantique¶
Pour voir les détails d’une vue sémantique, exécutez la commande DESCRIBE SEMANTIC VIEW. Par exemple :
DESCRIBE SEMANTIC VIEW tpch_rev_analysis;
+--------------+------------------------------+---------------+--------------------------+----------------------------------------+
| object_kind | object_name | parent_entity | property | property_value |
|--------------+------------------------------+---------------+--------------------------+----------------------------------------|
| NULL | NULL | NULL | COMMENT | Semantic view for revenue analysis |
| TABLE | CUSTOMERS | NULL | BASE_TABLE_DATABASE_NAME | SNOWFLAKE_SAMPLE_DATA |
| TABLE | CUSTOMERS | NULL | BASE_TABLE_SCHEMA_NAME | TPCH_SF1 |
| TABLE | CUSTOMERS | NULL | BASE_TABLE_NAME | CUSTOMER |
| TABLE | CUSTOMERS | NULL | PRIMARY_KEY | ["C_CUSTKEY"] |
| TABLE | CUSTOMERS | NULL | COMMENT | Main table for customer data |
| DIMENSION | CUSTOMER_NAME | CUSTOMERS | TABLE | CUSTOMERS |
| DIMENSION | CUSTOMER_NAME | CUSTOMERS | EXPRESSION | customers.c_name |
| DIMENSION | CUSTOMER_NAME | CUSTOMERS | DATA_TYPE | VARCHAR(25) |
| DIMENSION | CUSTOMER_NAME | CUSTOMERS | SYNONYMS | ["customer name"] |
| DIMENSION | CUSTOMER_NAME | CUSTOMERS | COMMENT | Name of the customer |
| TABLE | LINE_ITEMS | NULL | BASE_TABLE_DATABASE_NAME | SNOWFLAKE_SAMPLE_DATA |
| TABLE | LINE_ITEMS | NULL | BASE_TABLE_SCHEMA_NAME | TPCH_SF1 |
| TABLE | LINE_ITEMS | NULL | BASE_TABLE_NAME | LINEITEM |
| TABLE | LINE_ITEMS | NULL | PRIMARY_KEY | ["L_ORDERKEY","L_LINENUMBER"] |
| TABLE | LINE_ITEMS | NULL | COMMENT | Line items in orders |
| RELATIONSHIP | LINE_ITEM_TO_ORDERS | LINE_ITEMS | TABLE | LINE_ITEMS |
| RELATIONSHIP | LINE_ITEM_TO_ORDERS | LINE_ITEMS | REF_TABLE | ORDERS |
| RELATIONSHIP | LINE_ITEM_TO_ORDERS | LINE_ITEMS | FOREIGN_KEY | ["L_ORDERKEY"] |
| RELATIONSHIP | LINE_ITEM_TO_ORDERS | LINE_ITEMS | REF_KEY | ["O_ORDERKEY"] |
| FACT | DISCOUNTED_PRICE | LINE_ITEMS | TABLE | LINE_ITEMS |
| FACT | DISCOUNTED_PRICE | LINE_ITEMS | EXPRESSION | l_extendedprice * (1 - l_discount) |
| FACT | DISCOUNTED_PRICE | LINE_ITEMS | DATA_TYPE | NUMBER(25,4) |
| FACT | DISCOUNTED_PRICE | LINE_ITEMS | COMMENT | Extended price after discount |
| FACT | LINE_ITEM_ID | LINE_ITEMS | TABLE | LINE_ITEMS |
| FACT | LINE_ITEM_ID | LINE_ITEMS | EXPRESSION | CONCAT(l_orderkey, '-', l_linenumber) |
| FACT | LINE_ITEM_ID | LINE_ITEMS | DATA_TYPE | VARCHAR(134217728) |
| TABLE | ORDERS | NULL | BASE_TABLE_DATABASE_NAME | SNOWFLAKE_SAMPLE_DATA |
| TABLE | ORDERS | NULL | BASE_TABLE_SCHEMA_NAME | TPCH_SF1 |
| TABLE | ORDERS | NULL | BASE_TABLE_NAME | ORDERS |
| TABLE | ORDERS | NULL | SYNONYMS | ["sales orders"] |
| TABLE | ORDERS | NULL | PRIMARY_KEY | ["O_ORDERKEY"] |
| TABLE | ORDERS | NULL | COMMENT | All orders table for the sales domain |
| RELATIONSHIP | ORDERS_TO_CUSTOMERS | ORDERS | TABLE | ORDERS |
| RELATIONSHIP | ORDERS_TO_CUSTOMERS | ORDERS | REF_TABLE | CUSTOMERS |
| RELATIONSHIP | ORDERS_TO_CUSTOMERS | ORDERS | FOREIGN_KEY | ["O_CUSTKEY"] |
| RELATIONSHIP | ORDERS_TO_CUSTOMERS | ORDERS | REF_KEY | ["C_CUSTKEY"] |
| METRIC | AVERAGE_LINE_ITEMS_PER_ORDER | ORDERS | TABLE | ORDERS |
| METRIC | AVERAGE_LINE_ITEMS_PER_ORDER | ORDERS | EXPRESSION | AVG(orders.count_line_items) |
| METRIC | AVERAGE_LINE_ITEMS_PER_ORDER | ORDERS | DATA_TYPE | NUMBER(36,6) |
| METRIC | AVERAGE_LINE_ITEMS_PER_ORDER | ORDERS | COMMENT | Average number of line items per order |
| FACT | COUNT_LINE_ITEMS | ORDERS | TABLE | ORDERS |
| FACT | COUNT_LINE_ITEMS | ORDERS | EXPRESSION | COUNT(line_items.line_item_id) |
| FACT | COUNT_LINE_ITEMS | ORDERS | DATA_TYPE | NUMBER(18,0) |
| METRIC | ORDER_AVERAGE_VALUE | ORDERS | TABLE | ORDERS |
| METRIC | ORDER_AVERAGE_VALUE | ORDERS | EXPRESSION | AVG(orders.o_totalprice) |
| METRIC | ORDER_AVERAGE_VALUE | ORDERS | DATA_TYPE | NUMBER(30,8) |
| METRIC | ORDER_AVERAGE_VALUE | ORDERS | COMMENT | Average order value across all orders |
| DIMENSION | ORDER_DATE | ORDERS | TABLE | ORDERS |
| DIMENSION | ORDER_DATE | ORDERS | EXPRESSION | o_orderdate |
| DIMENSION | ORDER_DATE | ORDERS | DATA_TYPE | DATE |
| DIMENSION | ORDER_DATE | ORDERS | COMMENT | Date when the order was placed |
| DIMENSION | ORDER_YEAR | ORDERS | TABLE | ORDERS |
| DIMENSION | ORDER_YEAR | ORDERS | EXPRESSION | YEAR(o_orderdate) |
| DIMENSION | ORDER_YEAR | ORDERS | DATA_TYPE | NUMBER(4,0) |
| DIMENSION | ORDER_YEAR | ORDERS | COMMENT | Year when the order was placed |
+--------------+------------------------------+---------------+--------------------------+----------------------------------------+
Récupérer l’instruction SQL correspondant à une vue sémantique¶
Vous pouvez appeler la fonction GET_DDL pour récupérer l’instruction DDL qui a créé une vue sémantique.
Lorsque vous appelez GET_DDL, indiquez 'SEMANTIC_VIEW' comme type d’objet. Par exemple :
SELECT GET_DDL('SEMANTIC_VIEW', 'tpch_rev_analysis', TRUE);
+-----------------------------------------------------------------------------------+
| GET_DDL('SEMANTIC_VIEW', 'TPCH_REV_ANALYSIS', TRUE) |
|-----------------------------------------------------------------------------------|
| create or replace semantic view DYOSHINAGA_DB.DYOSHINAGA_SCHEMA.TPCH_REV_ANALYSIS |
| tables ( |
| ORDERS primary key (O_ORDERKEY) with synonyms=('sales orders') comment='All orders table for the sales domain', |
| CUSTOMERS as CUSTOMER primary key (C_CUSTKEY) comment='Main table for customer data', |
| LINE_ITEMS as LINEITEM primary key (L_ORDERKEY,L_LINENUMBER) comment='Line items in orders' |
| ) |
| relationships ( |
| ORDERS_TO_CUSTOMERS as ORDERS(O_CUSTKEY) references CUSTOMERS(C_CUSTKEY), |
| LINE_ITEM_TO_ORDERS as LINE_ITEMS(L_ORDERKEY) references ORDERS(O_ORDERKEY) |
| ) |
| facts ( |
| ORDERS.COUNT_LINE_ITEMS as COUNT(line_items.line_item_id), |
| LINE_ITEMS.DISCOUNTED_PRICE as l_extendedprice * (1 - l_discount) comment='Extended price after discount', |
| LINE_ITEMS.LINE_ITEM_ID as CONCAT(l_orderkey, '-', l_linenumber) |
| ) |
| dimensions ( |
| ORDERS.ORDER_DATE as o_orderdate comment='Date when the order was placed', |
| ORDERS.ORDER_YEAR as YEAR(o_orderdate) comment='Year when the order was placed', |
| CUSTOMERS.CUSTOMER_NAME as customers.c_name with synonyms=('customer name') comment='Name of the customer' |
| ) |
| metrics ( |
| ORDERS.AVERAGE_LINE_ITEMS_PER_ORDER as AVG(orders.count_line_items) comment='Average number of line items per order', |
| ORDERS.ORDER_AVERAGE_VALUE as AVG(orders.o_totalprice) comment='Average order value across all orders' |
| ); |
+-----------------------------------------------------------------------------------+
Supprimer une vue sémantique¶
Pour supprimer une vue sémantique, exécutez la commande DROP SEMANTIC VIEW. Par exemple :
DROP SEMANTIC VIEW tpch_rev_analysis;
Octroyer des privilèges sur des vues sémantiques¶
Privilèges de la vue sémantique liste les privilèges que vous pouvez accorder à une vue sémantique.
Pour travailler avec une vue sémantique, vous devez obligatoirement disposer des privilèges suivants sur cette vue :
Un privilège quelconque (par exemple, REFERENCE ou SELECT) est requis pour l’exécution de la commande DESCRIBE SEMANTIC VIEW sur la vue.
SELECT est nécessaire pour exécuter
SELECT ... FROM SEMANTIC_VIEW().L’un ou l’autre de ces privilèges est exigé pour afficher la vue dans la sortie de la commande SHOW SEMANTIC VIEWS.
Note
Actuellement, le privilège REFERENCES vous permet d’interroger une vue sémantique, mais à l’avenir, cette possibilité sera limitée aux rôles disposant du privilège SELECT.
Pour utiliser une vue sémantique dont vous n’êtes pas propriétaire dans Cortex Analyst, vous devez utiliser un rôle qui possède les privilèges REFERENCES et SELECT sur cette vue.
Pour accorder les privilèges REFERENCES et SELECT sur une vue sémantique, utilisez la commande GRANT <privilèges> … TO ROLE. Par exemple, pour accorder au rôle my_analyst_role les privilèges REFERENCES et SELECT sur la vue sémantique nommée my_semantic_view, vous pouvez exécuter l’instruction suivante :
GRANT REFERENCES, SELECT ON SEMANTIC VIEW my_semantic_view TO ROLE my_analyst_role;
Si vous disposez d’un schéma contenant des vues sémantiques que vous souhaitez partager avec les utilisateurs de Cortex Analyst, vous pouvez utiliser des autorisations futures pour accorder les privilèges sur toute vue sémantique que vous créez dans ce schéma. Par exemple :
GRANT REFERENCES, SELECT ON FUTURE SEMANTIC VIEWS IN SCHEMA my_schema TO ROLE my_analyst_role;