Utiliser des commandes SQL pour créer et gérer des vues sémantiques¶

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 orders contenant les informations sur les commandes de la table TPC-H orders.

• Une table customers contenant les informations sur les clients de la table TPC-H customers.

• Une table line_items contenant les lignes des commandes de la table TPC-H lineitem.

L’exemple utilise la clause PRIMARY KEY pour identifier les colonnes à utiliser comme clés primaires pour chaque table logique. Les clés primaires et les valeurs uniques permettent de déterminer les types de relations entre les tables (par exemple, plusieurs à une ou une à une).

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.

  Vous pouvez également spécifier une date, une heure, un horodatage ou une colonne numérique, si vous le souhaitez joindre les colonnes d’une plage.

Dans l”exemple présenté plus haut, la clause RELATIONSHIPS spécifie deux relations :

• Une relation entre les tables orders et customers. Dans la table orders, o_custkey est la clé étrangère qui renvoie à la clé primaire de la table customers (c_custkey).

• Une relation entre les tables line_items et orders. Dans la table line_items, l_orderkey est la clé étrangère qui renvoie à la clé primaire de la table orders (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)
)

Utilisation d’une date, d’une heure, d’un horodatage ou d’une plage numérique pour joindre des tables logiques¶

Par défaut, lorsque vous spécifiez une relation entre deux tables logiques, les tables sont jointes selon une condition d’égalité.

Si vous devez joindre deux tables logiques sur une date, une heure, un horodatage ou une plage numérique (où les valeurs d’une colonne d’une table doivent se trouver dans la même plage que les valeurs d’une colonne d’une autre table), vous pouvez spécifier le mot clé ASOF avec le nom de colonne dans la clause REFERENCES :

RELATIONSHIPS(
  my_relationship AS
    logical_table_1(
      col_table_1
    )
    REFERENCES
    logical_table_2(
      ASOF col_table_2
    )
)

Une requête de la vue sémantique définie ci-dessus produit une JOIN ASOF qui utilise l’opérateur de comparaison >= dans la clause MATCH_CONDITION. Ceci joint les deux tables de sorte que les valeurs de col_table_1 sont supérieures ou égales aux valeurs contenues dans la colonne col_table_2 :

...
FROM logical_table_1 ASOF JOIN logical_table_2
  MATCH_CONDITION(
    logical_table_1.col_table_1 >= logical_table_2.col_table_2
  )
...

Vous pouvez utiliser le mot clé ASOF pour les colonnes de mêmes types que vous pouvez utiliser avec une JOIN ASOF.

Par exemple, supposons que vous ayez des tables contenant des données de client, d’adresse de client et de commande :

CREATE OR REPLACE TABLE customer(
  c_cust_id VARCHAR,
  c_first_name VARCHAR,
  c_last_name VARCHAR);

INSERT INTO customer VALUES
  ('cust001', 'Mary', 'Smith'),
  ('cust002', 'Bill', 'Wilson');

CREATE OR REPLACE TABLE customer_address(
  ca_cust_id VARCHAR,
  ca_zipcode VARCHAR,
  ca_street_addr VARCHAR,
  ca_start_date DATE,
  ca_end_date DATE
);

INSERT INTO customer_address VALUES
  ('cust001', '94025', '100 Main Street', '2024-01-01', '2024-03-31'),
  ('cust001', '94026', '200 Main Street', '2024-04-01', '2024-06-30'),
  ('cust001', '94027', '300 Main Street', '2024-07-01', NULL),
  ('cust002', '94028', '400 Main Street', '2024-01-01', '2024-04-30'),
  ('cust002', '94029', '500 Main Street', '2024-05-01', '2024-07-31'),
  ('cust002', '94030', '600 Main Street', '2024-08-01', NULL);

CREATE OR REPLACE TABLE orders(
  o_ord_id VARCHAR,
  o_cust_id VARCHAR,
  o_ord_date DATE,
  o_amount NUMBER
);

INSERT INTO orders VALUES
  ('ord100', 'cust001', '2024-02-01', 100),
  ('ord101', 'cust001', '2024-02-02', 200),
  ('ord102', 'cust001', '2024-05-01', 300),
  ('ord103', 'cust001', '2024-05-02', 400),
  ('ord104', 'cust001', '2024-08-01', 500),
  ('ord105', 'cust001', '2024-08-02', 600),
  ('ord106', 'cust002', '2024-03-01', 100),
  ('ord107', 'cust002', '2024-03-02', 200),
  ('ord108', 'cust002', '2024-06-01', 300),
  ('ord109', 'cust002', '2024-06-02', 400),
  ('ord110', 'cust002', '2024-09-01', 500),
  ('ord111', 'cust002', '2024-09-02', 600);

Dans cet exemple, la table customer_address possède une colonne ca_start_date, qui indique quand le client a commencé à résider à l’adresse spécifiée. La table orders possède une colonne o_ord_date, qui est la date de la commande.

Supposons que vous souhaitiez pouvoir interroger des informations sur les commandes des clients et récupérer les codes postaux correspondant à l’endroit où réside le client lorsque les commandes ont été passées.

Vous pouvez définir une vue sémantique qui spécifie une jointure ASOF entre les colonnes ca_start_date et o_ord_date :

CREATE OR REPLACE SEMANTIC VIEW customer_orders_view
  TABLES (
    customer_address UNIQUE (ca_cust_id, ca_start_date),
    customer UNIQUE (c_cust_id),
    orders UNIQUE (o_ord_id)
  )
  RELATIONSHIPS (
    customer_address(ca_cust_id) REFERENCES customer,
    -- Defines an ASOF JOIN on the date columns.
    orders(o_cust_id, o_ord_date)
      REFERENCES
        customer_address(ca_cust_id, ASOF ca_start_date)
  )
  FACTS (
    customer_address.f_zipcode AS ca_zipcode
  )
  DIMENSIONS (
    -- Relies on the ASOF join to retrieve the zip code
    -- where the order date is greater than or equal to
    -- the address starting date.
    orders.f_cust_zipcode AS customer_address.f_zipcode,
    orders.dim_year_month AS DATE_TRUNC('month', o_ord_date)
  )
  METRICS (
    orders.m_order_amount AS SUM(o_amount)
  );

Supposons que vous interrogiez cette vue sémantique pour renvoyer la somme des montants de commande par mois pour chaque code postal :

SELECT * FROM SEMANTIC_VIEW(
  customer_orders_view
  DIMENSIONS orders.dim_year_month, orders.f_cust_zipcode
  METRICS orders.m_order_amount
);

+----------------+----------------+----------------+
| DIM_YEAR_MONTH | F_CUST_ZIPCODE | M_ORDER_AMOUNT |
|----------------+----------------+----------------|
| 2024-02-01     | 94025          |            300 |
| 2024-05-01     | 94026          |            700 |
| 2024-08-01     | 94027          |           1100 |
| 2024-03-01     | 94028          |            300 |
| 2024-09-01     | 94030          |           1100 |
| 2024-06-01     | 94029          |            700 |
+----------------+----------------+----------------+

La requête utilise effectivement une JOIN ASOF pour joindre les tables sur les colonnes de date, où la date de la commande est supérieure ou égale à la date de début de l’adresse :

...
FROM orders ASOF JOIN customer_address
  MATCH_CONDITION(
    orders.o_ord_date >= customer_address.ca_start_date
  )
  ON
    orders.o_cust_id = customer_address.ca_cust_id
...

Jointure de tables logiques contenant des plages de valeurs¶

Vous pouvez utiliser une jointure de plage lorsque vous souhaitez joindre une table à une autre table qui définit une plage de valeurs possibles dans la première table. Par exemple, supposons qu’une table représente les commandes client et possède une colonne avec l’horodatage de la commande. Supposons qu’une autre table représente les trimestres fiscaux et contienne les différentes plages de temps qui représentent ces trimestres. Vous pouvez créer une vue sémantique qui joint les deux tables de sorte que la ligne d’une commande inclue le trimestre fiscal au cours duquel la commande a été passée.

Dans la table qui contient les plages, chaque plage doit être distincte. Deux plages ne peuvent pas se chevaucher.

Dans les données de la table, si vous souhaitez spécifier la valeur la plus faible possible pour la plage ou la valeur la plus élevée possible pour la plage, utilisez NULL.

Par exemple, le tableau suivant définit un ensemble de plages de temps qui ne se chevauchent pas :

• La première ligne couvre la plage qui inclut tout jusqu’au (mais non compris) 1er janvier 2024.

• La dernière ligne couvre la plage qui inclut tout à partir du 20 mars 2024.

+----------------+------------------+-------------------------+-------------------------+
| TIME_PERIOD_ID | TIME_PERIOD_NAME | START_TIME              | END_TIME                |
|----------------+------------------+-------------------------+-------------------------|
|              1 | Before_January   | NULL                    | 2024-01-01 00:00:00.000 |
|              2 | Early_January    | 2024-01-01 00:00:00.000 | 2024-01-15 00:00:00.000 |
|              3 | Late_January     | 2024-01-15 00:00:00.000 | 2024-02-01 00:00:00.000 |
|              4 | Early_February   | 2024-02-01 00:00:00.000 | 2024-02-15 00:00:00.000 |
|              5 | Late_February    | 2024-02-15 00:00:00.000 | 2024-03-01 00:00:00.000 |
|              6 | Early_March      | 2024-03-01 00:00:00.000 | 2024-03-20 00:00:00.000 |
|              7 | After_March20    | 2024-03-20 00:00:00.000 | NULL                    |
+----------------+------------------+-------------------------+-------------------------+

Pour de tels cas, vous pouvez mettre en place une vue sémantique qui prend en charge les requêtes de jointure de plage. Lorsque vous créez la vue sémantique, vous devez effectuer les opérations suivantes :

1. Pour la table logique contenant les heures de début et de fin d’une période, définissez une contrainte qui spécifie que deux plages ne peuvent se chevaucher.

   Dans la clause TABLE de la commande CREATE SEMANTIC VIEW, spécifiez la clause CONSTRAINT dans la définition de la table logique. Pour la syntaxe, consultez la documentation pourCONSTRAINT dans la rubrique CREATE SEMANTIC VIEW.

2. Définissez une relation entre la colonne contenant l’horodatage dans une table et les colonnes d’heure de début et de fin dans l’autre table.

   Dans la clause RELATIONSHIPS de la commande CREATE SEMANTIC VIEW, utilisez la clause BETWEEN pour spécifier les colonnes contenant les heures de début et de fin. Pour la syntaxe, consultez la documentation pourRELATIONSHIP dans la rubrique CREATE SEMANTIC VIEW.

Par exemple, supposons que la table my_time_periods définit des périodes de temps distinctes :

CREATE OR REPLACE TABLE my_time_periods (
  time_period_id INT PRIMARY KEY,
  time_period_name VARCHAR(50),
  start_time TIMESTAMP,
  end_time TIMESTAMP
);

INSERT INTO my_time_periods (
    time_period_id, time_period_name, start_time, end_time
  ) VALUES
    (1, 'Before_January', NULL, '2024-01-01 00:00:00'::TIMESTAMP),
    (2, 'Early_January', '2024-01-01 00:00:00'::TIMESTAMP, '2024-01-15 00:00:00'::TIMESTAMP),
    (3, 'Late_January', '2024-01-15 00:00:00'::TIMESTAMP, '2024-02-01 00:00:00'::TIMESTAMP),
    (4, 'Early_February', '2024-02-01 00:00:00'::TIMESTAMP, '2024-02-15 00:00:00'::TIMESTAMP),
    (5, 'Late_February', '2024-02-15 00:00:00'::TIMESTAMP, '2024-03-01 00:00:00'::TIMESTAMP),
    (6, 'Early_March', '2024-03-01 00:00:00'::TIMESTAMP, '2024-03-20 00:00:00'::TIMESTAMP),
    (7, 'After_March20', '2024-03-20 00:00:00'::TIMESTAMP, NULL);

Supposons que la table my_events capture les événements qui se sont produits au cours de ces périodes :

CREATE OR REPLACE TABLE my_events (
  event_id INTEGER PRIMARY KEY,
  event_timestamp TIMESTAMP,
  event_name VARCHAR
);

INSERT INTO my_events (event_id, event_name, event_timestamp) VALUES
  (1, 'Login', '2024-01-15 10:00:00'::TIMESTAMP),
  (2, 'Purchase', '2024-01-15 14:30:00'::TIMESTAMP),
  (3, 'Logout', '2024-01-15 18:45:00'::TIMESTAMP),
  (4, 'Review', '2024-02-10 12:00:00'::TIMESTAMP),
  (5, 'Support', '2024-02-20 09:30:00'::TIMESTAMP),
  (6, 'Upgrade', '2024-03-05 16:00:00'::TIMESTAMP),
  (7, 'Feedback', '2024-03-25 11:00:00'::TIMESTAMP);

Vous pouvez définir une vue sémantique qui joint les tables. Les lignes dans my_events sont jointes à des lignes dans my_time_periods, où la valeur dans la colonne event_timestamp dans my_events se situe dans la plage spécifiée par les colonnes start_time et end_time dans my_time_periods.

CREATE OR REPLACE SEMANTIC VIEW my_semantic_view_range_join
  TABLES (
    my_events PRIMARY KEY (event_id),
    my_time_periods UNIQUE (start_time, end_time)
      CONSTRAINT my_time_period_range DISTINCT RANGE BETWEEN start_time AND end_time EXCLUSIVE
  )
  RELATIONSHIPS (
    my_time_periods_for_events AS
      my_events(event_timestamp) REFERENCES
        my_time_periods(BETWEEN start_time AND end_time EXCLUSIVE)
  )
  DIMENSIONS (
    my_events.dim_event_name AS event_name,
    my_events.dim_event_timestamp AS event_timestamp,
    my_time_periods.dim_time_period_name AS time_period_name
  )
  METRICS (
    my_events.m_event_count AS COUNT(*)
  );

La requête suivante montre comment les lignes sont jointes :

SELECT
    sv.dim_event_name,
    sv.dim_event_timestamp,
    sv.dim_time_period_name,
    sv.m_event_count
  FROM SEMANTIC_VIEW(
    my_semantic_view_range_join
    METRICS my_events.m_event_count
    DIMENSIONS
      my_events.dim_event_name,
      my_events.dim_event_timestamp,
      my_time_periods.dim_time_period_name
  ) AS sv
  ORDER BY
    sv.dim_event_timestamp,
    sv.dim_time_period_name;

+----------------+-------------------------+----------------------+---------------+
| DIM_EVENT_NAME | DIM_EVENT_TIMESTAMP     | DIM_TIME_PERIOD_NAME | M_EVENT_COUNT |
|----------------+-------------------------+----------------------+---------------|
| Login          | 2024-01-15 10:00:00.000 | Late_January         |             1 |
| Purchase       | 2024-01-15 14:30:00.000 | Late_January         |             1 |
| Logout         | 2024-01-15 18:45:00.000 | Late_January         |             1 |
| Review         | 2024-02-10 12:00:00.000 | Early_February       |             1 |
| Support        | 2024-02-20 09:30:00.000 | Late_February        |             1 |
| Upgrade        | 2024-03-05 16:00:00.000 | Early_March          |             1 |
| Feedback       | 2024-03-25 11:00:00.000 | After_March20        |             1 |
+----------------+-------------------------+----------------------+---------------+

Comme le montrent les exemples, la dimension dim_time_period_name pour chaque ligne des résultats correspond au nom de la période à laquelle appartient la dimension dim_event_timestamp.

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

  Note

  Si vous souhaitez définir une métrique dérivée (une métrique qui n’est pas spécifique à une seule table logique), vous devez omettre le nom de la table logique. Voir Définition de métriques dérivées.

• Un nom pour le fait, la dimension ou la métrique

• L’expression SQL pour le calculer

  Note

  Pour les dimensions, vous pouvez spécifier un Cortex Search Service. Pour plus d’informations, voir Définir une dimension qui utilise un Cortex Search Service.

• 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'
)

Définir une dimension qui utilise un Cortex Search Service¶

Pour définir une dimension qui utilise un Cortex Search Service, définissez la clause WITH CORTEX SEARCH SERVICE sur le nom du Cortex Search Service. Si le service se trouve dans une base de données ou un schéma différent, qualifier le nom du service. Par exemple :

DIMENSIONS (
  my_table.my_dimension AS my_dimension_expression
    WITH CORTEX SEARCH SERVICE my_db.my_schema.my_dimension_search_service
)

Définition de métriques dérivées¶

Lorsque vous définissez une métrique, vous spécifiez le nom de la table logique à laquelle appartient la métrique. Il s’agit de la table logique sur laquelle la métrique est agrégée.

Si vous souhaitez définir une métrique basée sur des métriques provenant d’autres tables logiques, vous pouvez définir une métrique dérivée. Une métrique dérivée est une métrique qui est liée à la vue sémantique (plutôt qu’à une table logique spécifique). Une métrique dérivée peut combiner des métriques provenant de plusieurs tables logiques.

Dans la définition d’une métrique dérivée, omettez le nom de la table logique.

Par exemple, supposons que vous souhaitiez définir une métrique my_derived_metric_1 qui est la somme des métriques table_1.metric_1 et table_2.metric_2. Lorsque vous définissez my_derived_metric_1, ne lui donnez pas le nom d’une table logique :

CREATE SEMANTIC VIEW sv_with_derived_metrics
  TABLES (
    table_1 PRIMARY KEY (column_1),
    table_2 PRIMARY KEY (column_2)
  )
  ...
  METRICS (
    table_1.metric_1 AS SUM(...),
    table_2.metric_2 AS SUM(...),
    my_derived_metric_1 AS table_1.metric_1 + table_2.metric_2
  )
 ...

Vous pouvez utiliser d’autres métriques dérivées dans l’expression. Par exemple :

METRICS (
  ...
  my_derived_metric_1 AS table_1.metric_1 + table_2.metric_2,
  my_view_metric_2 AS my_derived_metric_1 + table_3.metric_3
)

Notez les restrictions suivantes lorsque vous définissez une métrique dérivée :

• Vous ne pouvez pas utiliser le même nom pour une métrique dérivée et une métrique ordinaire.

• L’expression pour une métrique dérivée peut utiliser :

  • Agrégations de dimensions et de faits définis dans n’importe quelle table logique de la vue sémantique.

  • Expressions scalaires de métriques définies dans n’importe quelle table logique de la vue sémantique.

  • Autres métriques dérivées.

  Dans l’exemple suivant :

  • derived_metric_1 utilise une expression scalaire avec deux métriques.

  • derived_metric_2 utilise une agrégation d’une dimension.

  • derived_metric_3 ajoute une agrégation d’une dimension à une autre métrique dérivée.

  CopyExpand

  CREATE OR REPLACE SEMANTIC VIEW sv_derived_metrics
    TABLES (t1)
    DIMENSIONS (t1.dim1 AS t1.col1)
    METRICS (
      t1.m1 AS SUM(t1.col1),
      t2.m2 AS SUM(t1.col2),
      derived_metric_1 AS t1.m1 + t2.m2,
      derived_metric_2 AS SUM(t1.dim1),
      derived_metric_3 AS SUM(t1.dim1) + derived_metric_2
    )
    ...
  

  Show lessSee more

  Scroll to top

• Il n’est pas nécessaire d’utiliser le nom d’une métrique, d’une dimension ou d’un fait dans l’expression si le nom n’est pas ambigu. Par exemple :

  CopyExpand

  METRICS (
    table_1.metric_1 AS ...,
    table_1.my_unique_metric_name AS ...,
    table_2.metric_1 AS ...,
    my_derived_metric_1 AS table_1.metric_1 + my_unique_metric_name
  )
  

  Show lessSee more

  Scroll to top

  Remarque : le nom de la metric_1 doit contenir table_1, car il existe deux métriques nommées metric_1, mais my_unique_metric_name n’a pas besoin d’être utilisé, car le nom est unique.

• Dans l’expression d’une métrique dérivée, vous ne pouvez pas utiliser les éléments suivants :

  • Agrégations de métriques.

  • Des fonctions de fenêtre.

  • Références aux colonnes physiques.

  • Références à des faits ou à des dimensions qui ne sont pas agrégés.

• Vous ne pouvez pas utiliser une métrique dérivée dans l’expression pour une métrique, une dimension ou un fait ordinaire. Seule une autre métrique dérivée peut utiliser une métrique dérivée dans son expression.

Spécification de la relation pour une métrique lorsqu’il existe plusieurs chemins de relation¶

Dans certains cas, il peut exister plusieurs chemins de relation entre deux tables logiques spécifiques dans une vue sémantique. Dans ces cas, lorsque vous définissez une métrique, vous devez spécifier le chemin de la relation à utiliser.

CREATE OR REPLACE TABLE airports (
  airport_code VARCHAR PRIMARY KEY,
  city_name VARCHAR,
  airport_region_code VARCHAR
);

INSERT INTO airports VALUES
  ('SEA', 'Seattle', 'NA'),
  ('SFO', 'San Fransico', 'NA'),
  ('PVG', 'Shanghai', 'AS');

SELECT * FROM airports;

+--------------+--------------+---------------------+
| AIRPORT_CODE | CITY_NAME    | AIRPORT_REGION_CODE |
|--------------+--------------+---------------------|
| SEA          | Seattle      | NA                  |
| SFO          | San Fransico | NA                  |
| PVG          | Shanghai     | AS                  |
+--------------+--------------+---------------------+

CREATE OR REPLACE TABLE flights (
  flight_id INTEGER PRIMARY KEY,
  departure_airport VARCHAR,
  arrival_airport VARCHAR,
  is_late BOOLEAN,
  aircraft_id INTEGER,
  departure_time DATETIME,
  arrival_time DATETIME
);

INSERT INTO flights VALUES
  (1, 'SFO', 'SEA', true, 1, '2025-01-03 06:00:00', '2025-01-03 11:00:00'),
  (2, 'SEA', 'SFO', false, 2, '2025-01-03 11:00:00', '2025-01-03 16:00:00'),
  (3, 'SEA', 'PVG', false, 3, '2025-01-03 11:00:00', '2025-01-04 11:00:00'),
  (4, 'SFO', 'PVG', true, 1, '2025-01-03 06:00:00', '2025-01-04 11:00:00');

SELECT * FROM flights;

+-----------+-------------------+-----------------+---------+-------------+-------------------------+-------------------------+
| FLIGHT_ID | DEPARTURE_AIRPORT | ARRIVAL_AIRPORT | IS_LATE | AIRCRAFT_ID | DEPARTURE_TIME          | ARRIVAL_TIME            |
|-----------+-------------------+-----------------+---------+-------------+-------------------------+-------------------------|
|         1 | SFO               | SEA             | True    |           1 | 2025-01-03 06:00:00.000 | 2025-01-03 11:00:00.000 |
|         2 | SEA               | SFO             | False   |           2 | 2025-01-03 11:00:00.000 | 2025-01-03 16:00:00.000 |
|         3 | SEA               | PVG             | False   |           3 | 2025-01-03 11:00:00.000 | 2025-01-04 11:00:00.000 |
|         4 | SFO               | PVG             | True    |           1 | 2025-01-03 06:00:00.000 | 2025-01-04 11:00:00.000 |
+-----------+-------------------+-----------------+---------+-------------+-------------------------+-------------------------+

CREATE OR REPLACE SEMANTIC VIEW flights_sv
  TABLES (
    flights PRIMARY KEY (flight_id),
    airports PRIMARY KEY (airport_code)
  ) RELATIONSHIPS (
    flight_departure_airport AS flights(departure_airport) REFERENCES airports(airport_code),
    flight_arrival_airport AS flights(arrival_airport) REFERENCES airports(airport_code)
  ) DIMENSIONS (
    airports.city_name AS city_name
  ) METRICS (
    flights.m_flight_count AS COUNT(flight_id)
  );

SELECT * FROM SEMANTIC_VIEW (
  flights_sv
  METRICS flights.m_flight_count
  DIMENSIONS airports.city_name
);

010246 (42601): SQL compilation error:
Invalid dimension specified: Multi-path relationship between the dimension entity 'AIRPORTS'
  and the base metric or dimension entity 'FLIGHTS' is not supported.

METRICS (
  <table_alias>.<metric>
    [ USING ( <relationship_name> [ , ... ] )
    AS <sql_expr>
  [ , ... ]
)

CREATE OR REPLACE SEMANTIC VIEW flights_sv
  TABLES (
    flights PRIMARY KEY (flight_id),
    airports PRIMARY KEY (airport_code)
  ) RELATIONSHIPS (
    flight_departure_airport AS flights(departure_airport) REFERENCES airports(airport_code),
    flight_arrival_airport AS flights(arrival_airport) REFERENCES airports(airport_code)
  ) DIMENSIONS (
    airports.city_name AS city_name
  ) METRICS (
    flights.m_flight_count AS COUNT(flight_id),
    flights.m_flight_departure_count USING (flight_departure_airport) AS flights.m_flight_count,
    flights.m_flight_arrival_count USING (flight_arrival_airport) AS flights.m_flight_count
  );

SELECT * FROM SEMANTIC_VIEW (
  flights_sv
  METRICS flights.m_flight_arrival_count, flights.m_flight_departure_count
  DIMENSIONS airports.city_name
);

+------------------------+--------------------------+--------------+
| M_FLIGHT_ARRIVAL_COUNT | M_FLIGHT_DEPARTURE_COUNT | CITY_NAME    |
|------------------------+--------------------------+--------------|
|                      1 |                        2 | San Fransico |
|                      1 |                        2 | Seattle      |
|                      2 |                     NULL | Shanghai     |
+------------------------+--------------------------+--------------+

CREATE OR REPLACE TABLE regions (
  region_code VARCHAR PRIMARY KEY,
  region_name VARCHAR
);

INSERT INTO regions VALUES
  ('NA', 'North America'),
  ('AS', 'Asia');

SELECT * FROM regions;

+-------------+---------------+
| REGION_CODE | REGION_NAME   |
|-------------+---------------|
| NA          | North America |
| AS          | Asia          |
+-------------+---------------+

CREATE OR REPLACE SEMANTIC VIEW flights_by_regions_sv
  TABLES (
    flights PRIMARY KEY (flight_id),
    airports PRIMARY KEY (airport_code),
    regions PRIMARY KEY (region_code)
  ) RELATIONSHIPS (
    flight_departure_airport AS flights(departure_airport) REFERENCES airports(airport_code),
    flight_arrival_airport AS flights(arrival_airport) REFERENCES airports(airport_code),
    airport_region AS airports(airport_region_code) REFERENCES regions(region_code)
  ) DIMENSIONS (
    airports.city_name AS city_name,
    regions.region_name AS region_name
  ) METRICS (
    flights.m_flight_count AS COUNT(flight_id),
    flights.m_flight_departure_count USING (flight_departure_airport) AS flights.m_flight_count,
    flights.m_flight_arrival_count USING (flight_arrival_airport) AS flights.m_flight_count
  );

SELECT * FROM SEMANTIC_VIEW (
  flights_by_regions_sv
  METRICS flights.m_flight_arrival_count, flights.m_flight_departure_count
  DIMENSIONS regions.region_name
);

+------------------------+--------------------------+---------------+
| M_FLIGHT_ARRIVAL_COUNT | M_FLIGHT_DEPARTURE_COUNT | REGION_NAME   |
|------------------------+--------------------------+---------------|
|                      2 |                        4 | North America |
|                      2 |                     NULL | Asia          |
+------------------------+--------------------------+---------------+

CREATE OR REPLACE TABLE weather (
  airport_code VARCHAR PRIMARY KEY,
  weather_condition VARCHAR,
  start_date DATETIME,
  end_date DATETIME
);

INSERT INTO weather VALUES
  ('SEA', 'rainy', '2025-01-01 10:00:00', '2025-01-01 12:00:00'),
  ('SEA', 'rainy', '2025-01-03 10:00:00', '2025-01-03 12:00:00'),
  ('SFO', 'sunny', '2025-01-03 05:00:00', '2025-01-03 09:00:00'),
  ('SFO', 'sunny', '2025-01-03 10:00:00', '2025-01-03 18:00:00'),
  ('PVG', 'cloudy', '2025-01-04 10:00:00', '2025-01-04 12:00:00');

SELECT * FROM weather;

+--------------+-------------------+-------------------------+-------------------------+
| AIRPORT_CODE | WEATHER_CONDITION | START_DATE              | END_DATE                |
|--------------+-------------------+-------------------------+-------------------------|
| SEA          | rainy             | 2025-01-01 10:00:00.000 | 2025-01-01 12:00:00.000 |
| SEA          | rainy             | 2025-01-03 10:00:00.000 | 2025-01-03 12:00:00.000 |
| SFO          | sunny             | 2025-01-03 05:00:00.000 | 2025-01-03 09:00:00.000 |
| SFO          | sunny             | 2025-01-03 10:00:00.000 | 2025-01-03 18:00:00.000 |
| PVG          | cloudy            | 2025-01-04 10:00:00.000 | 2025-01-04 12:00:00.000 |
+--------------+-------------------+-------------------------+-------------------------+

CREATE OR REPLACE SEMANTIC VIEW flights_and_weather_sv
  TABLES (
    flights PRIMARY KEY (flight_id),
    airports PRIMARY KEY (airport_code),
    weather PRIMARY KEY (airport_code, start_date, end_date)
  ) RELATIONSHIPS (
    flight_departure_airport AS flights(departure_airport) REFERENCES airports(airport_code),
    flight_arrival_airport AS flights(arrival_airport) REFERENCES airports(airport_code),
    flight_departure_weather AS flights(departure_airport, departure_time) REFERENCES weather(airport_code, BETWEEN start_date AND end_date EXCLUSIVE),
    flight_arrival_weather AS flights(arrival_airport, arrival_time) REFERENCES weather(airport_code, BETWEEN start_date AND end_date EXCLUSIVE)
  ) DIMENSIONS (
    airports.city_name AS city_name,
    weather.weather_condition AS weather_condition
  ) METRICS (
    flights.m_flight_count AS COUNT(flight_id),
    flights.m_flight_departure_count USING (flight_departure_airport, flight_departure_weather) AS flights.m_flight_count,
    flights.m_flight_arrival_count USING (flight_arrival_airport, flight_arrival_weather) AS flights.m_flight_count
  );

SELECT * FROM SEMANTIC_VIEW (
  flights_by_regions_sv
  METRICS flights.m_flight_arrival_count, flights.m_flight_departure_count
  DIMENSIONS weather.weather_condition
);

+------------------------+--------------------------+-------------------+
| M_FLIGHT_ARRIVAL_COUNT | M_FLIGHT_DEPARTURE_COUNT | WEATHER_CONDITION |
|------------------------+--------------------------+-------------------|
|                      2 |                     NULL | cloudy            |
|                      1 |                        2 | sunny             |
|                      1 |                        2 | rainy             |
+------------------------+--------------------------+-------------------+

CREATE OR REPLACE SEMANTIC VIEW flights_derived_metrics_sv
  TABLES (
    flights PRIMARY KEY (flight_id),
    airports PRIMARY KEY (airport_code)
  ) RELATIONSHIPS (
    flight_departure_airport AS flights(departure_airport) REFERENCES airports(airport_code),
    flight_arrival_airport AS flights(arrival_airport) REFERENCES airports(airport_code)
  ) DIMENSIONS (
    airports.city_name AS city_name
  ) METRICS (
    flights.m_flight_count AS COUNT(flight_id),
    flights.m_flight_departure_count USING (flight_departure_airport) AS flights.m_flight_count,
    flights.m_flight_arrival_count USING (flight_arrival_airport) AS flights.m_flight_count,
    global_m_departure_arrival_ratio AS DIV0(flights.m_flight_departure_count, flights.m_flight_arrival_count),
    global_m_departure_arrival_sum AS flights.m_flight_departure_count + flights.m_flight_arrival_count
  );

SELECT * FROM SEMANTIC_VIEW (
  flights_derived_metrics_sv
  METRICS global_m_departure_arrival_ratio,
    flights.m_flight_arrival_count, flights.m_flight_departure_count
  DIMENSIONS airports.city_name
);

+------------------------+--------------------------+----------------------------------+--------------+
| M_FLIGHT_ARRIVAL_COUNT | M_FLIGHT_DEPARTURE_COUNT | GLOBAL_M_DEPARTURE_ARRIVAL_RATIO | CITY_NAME    |
|------------------------+--------------------------+----------------------------------+--------------|
|                      1 |                        2 |                         2.000000 | Seattle      |
|                      1 |                        2 |                         2.000000 | San Fransico |
|                      2 |                     NULL |                             NULL | Shanghai     |
+------------------------+--------------------------+----------------------------------+--------------+

Identification des dimensions qui doivent être non additionnées pour une métrique¶

Dans certains cas, une métrique ne doit pas être agrégée à travers des dimensions spécifiques. Dans ces cas-là, vous pouvez marquer les dimensions comme non additionnées.

CREATE OR REPLACE TABLE bank_accounts (
  customer_id VARCHAR,
  account_type VARCHAR,
  year NUMBER,
  month NUMBER,
  day NUMBER,
  balance NUMBER
);

INSERT INTO bank_accounts VALUES
  ('cust-001', 'checking', 2024, 01, 01, 100),
  ('cust-001', 'savings', 2024, 01, 01, 110),
  ('cust-001', 'checking', 2024, 02, 10, 140),
  ('cust-001', 'savings', 2024, 02, 10, 150),
  ('cust-001', 'checking', 2024, 03, 15, 200),
  ('cust-001', 'savings', 2024, 03, 30, 210),
  ('cust-001', 'checking', 2025, 02, 15, 280),
  ('cust-001', 'savings', 2025, 02, 15, 290),
  ('cust-001', 'checking', 2025, 03, 20, 300),
  ('cust-001', 'savings', 2025, 03, 20, 310),
  ('cust-002', 'checking', 2025, 03, 30, 200),
  ('cust-002', 'savings', 2025, 03, 30, 310);

SELECT * FROM bank_accounts;

+-------------+--------------+------+-------+-----+---------+
| CUSTOMER_ID | ACCOUNT_TYPE | YEAR | MONTH | DAY | BALANCE |
|-------------+--------------+------+-------+-----+---------|
| cust-001    | checking     | 2024 |     1 |   1 |     100 |
| cust-001    | savings      | 2024 |     1 |   1 |     110 |
| cust-001    | checking     | 2024 |     2 |  10 |     140 |
| cust-001    | savings      | 2024 |     2 |  10 |     150 |
| cust-001    | checking     | 2024 |     3 |  15 |     200 |
| cust-001    | savings      | 2024 |     3 |  30 |     210 |
| cust-001    | checking     | 2025 |     2 |  15 |     280 |
| cust-001    | savings      | 2025 |     2 |  15 |     290 |
| cust-001    | checking     | 2025 |     3 |  20 |     300 |
| cust-001    | savings      | 2025 |     3 |  20 |     310 |
| cust-002    | checking     | 2025 |     3 |  30 |     200 |
| cust-002    | savings      | 2025 |     3 |  30 |     310 |
+-------------+--------------+------+-------+-----+---------+

CREATE OR REPLACE SEMANTIC VIEW bank_accounts_sv
  TABLES (
    bank_accounts
  )
  DIMENSIONS (
    bank_accounts.customer_id_dim AS bank_accounts.customer_id,
    bank_accounts.account_type_dim AS bank_accounts.account_type,
    bank_accounts.year_dim AS bank_accounts.year,
    bank_accounts.month_dim AS bank_accounts.month,
    bank_accounts.day_dim AS bank_accounts.day
  )
  METRICS (
    bank_accounts.m_account_balance AS SUM(balance)
  );

SELECT * FROM SEMANTIC_VIEW(
    bank_accounts_sv
    METRICS bank_accounts.m_account_balance
    DIMENSIONS customer_id_dim, year_dim
  )
  ORDER BY customer_id_dim, year_dim;

+-------------------+-----------------+----------+
| M_ACCOUNT_BALANCE | CUSTOMER_ID_DIM | YEAR_DIM |
|-------------------+-----------------+----------|
|               910 | cust-001        |     2024 |
|              1180 | cust-001        |     2025 |
|               510 | cust-002        |     2025 |
+-------------------+-----------------+----------+

CREATE OR REPLACE SEMANTIC VIEW bank_accounts_sv
  TABLES (
    bank_accounts
  )
  DIMENSIONS (
    bank_accounts.customer_id_dim AS bank_accounts.customer_id,
    bank_accounts.account_type_dim AS bank_accounts.account_type,
    bank_accounts.year_dim AS bank_accounts.year,
    bank_accounts.month_dim AS bank_accounts.month,
    bank_accounts.day_dim AS bank_accounts.day
  )
  METRICS (
    bank_accounts.m_account_balance
      NON ADDITIVE BY (year_dim, month_dim, day_dim)
      AS SUM(balance)
  );

SELECT * FROM SEMANTIC_VIEW(
    bank_accounts_sv
    METRICS bank_accounts.m_account_balance
    DIMENSIONS customer_id_dim, year_dim
  )
  ORDER BY customer_id_dim, year_dim;

+-------------------+-----------------+----------+
| M_ACCOUNT_BALANCE | CUSTOMER_ID_DIM | YEAR_DIM |
|-------------------+-----------------+----------|
|               210 | cust-001        |     2024 |
|               610 | cust-001        |     2025 |
|               510 | cust-002        |     2025 |
+-------------------+-----------------+----------+

SELECT * FROM SEMANTIC_VIEW(
    bank_accounts_sv
    METRICS bank_accounts.m_account_balance
    DIMENSIONS year_dim
  )
  ORDER BY year_dim;

+-------------------+----------+
| M_ACCOUNT_BALANCE | YEAR_DIM |
|-------------------+----------|
|               210 |     2024 |
|               510 |     2025 |
+-------------------+----------+

METRICS (
  bank_accounts.m_account_balance
    NON ADDITIVE BY (year_dim DESC, month_dim DESC, day_dim DESC)
    AS SUM(balance)
);

METRICS (
  bank_accounts.m_account_balance
    NON ADDITIVE BY (
      year_dim DESC NULLS FIRST,
      month_dim DESC NULLS FIRST,
      day_dim DESC NULLS FIRST
    )
    AS SUM(balance)

Marquer un fait ou une métrique comme privé¶

Si vous définissez un fait ou un indicateur uniquement pour une utilisation dans les calculs de la vue sémantique et que vous ne souhaitez pas que le fait ou la métrique soit renvoyé dans une requête, vous pouvez spécifier le mot-clé PRIVATE pour marquer le fait ou la métrique comme privé. Par exemple :

FACTS (
  PRIVATE my_private_fact AS ...
)

METRICS (
  PRIVATE my_private_metric AS ...
)

Lorsque vous interrogez une vue sémantique comportant des faits ou des métriques privés, vous ne pouvez pas spécifier de fait ou de métrique privé dans les clauses suivantes :

• La liste SELECT

• FACTS dans la clause SEMANTIC_VIEW

• METRICS dans la clause SEMANTIC_VIEW

• METRICS

• WHERE dans l’instruction SELECT ou la clause SEMANTIC_VIEW

Certaines commandes et fonctions incluent des faits et des métriques privés :

• Les faits et les métriques privés apparaissent dans la sortie de la commande DESCRIBE SEMANTIC VIEW. Les lignes pour les faits et les métriques privés ont PRIVATE dans la colonne access_modifier.

• Les faits et métriques privés sont répertoriés dans la valeur de retour d’un appel de fonction GET_DDL, comme indiqué dans Récupérer l’instruction SQL correspondant à une vue sémantique.

Certaines commandes et fonctions incluent des faits et des métriques privés uniquement dans des conditions spécifiques :

• Les faits et les métriques privés sont répertoriés dans les vuesINFORMATION_SCHEMA SEMANTIC_FACTS et SEMANTIC_METRICS uniquement si vous utilisez un rôle qui s’est vu accorder le privilège REFERENCES ou OWNERSHIP sur la vue sémantique.

  Sinon, ces vues ne répertorient que les faits et les métriques publics.

Les autres commandes et fonctions n’incluent pas de faits et de métriques privés :

• Les faits privés n’apparaissent pas dans la sortie de la commande SHOW SEMANTIC FACTS.

• Les métriques privées n’apparaissent pas dans la sortie de la commande SHOW SEMANTIC METRICS.

Fournir des instructions personnalisées pour Cortex Analyst¶

Dans une vue sémantique, vous pouvez fournir des instructions pour Cortex Analyst qui expliquent comment :

• Générer l’instruction SQL

• Classer les questions et demander des informations supplémentaires

Pour fournir ces instructions personnalisées, utilisez les clauses suivantes :

• Pour obtenir des instructions sur la façon de générer l’instruction SQL, utilisez la clause AI_SQL_GENERATION dans la commande CREATE SEMANTIC VIEW.

  Par exemple, pour demander à Cortex Analyst de générer l’instruction SQL de sorte que toutes les colonnes numériques soient arrondies à deux décimales, spécifiez ce qui suit :

  CopyExpand

  CREATE SEMANTIC VIEW my_semantic_view
    ...
    -- Definitions of logical tables, relationships, dimensions, facts, and metrics
    ...
    AI_SQL_GENERATION 'Ensure that all numeric columns are rounded to 2 decimal points.'
    ...
    -- Additional clauses
  

  Show lessSee more

  Scroll to top

• Pour obtenir des instructions sur la manière de classer les questions, utilisez la clause AI_QUESTION_CATEGORIZATION.

  Par exemple, pour demander à Cortex Analyst de rejeter les questions sur les utilisateurs, spécifiez ce qui suit :

  CopyExpand

  CREATE SEMANTIC VIEW my_semantic_view
    ...
    -- Definitions of logical tables, relationships, dimensions, facts, and metrics
    ...
    AI_QUESTION_CATEGORIZATION 'Reject all questions asking about users. Ask users to contact their admin.'
    ...
    -- Additional clauses
  

  Show lessSee more

  Scroll to top

  Vous pouvez également fournir des instructions pour demander plus de détails, si la question n’est pas claire. Par exemple :

  CopyExpand

  AI_QUESTION_CATEGORIZATION 'If the question asks for users without providing a product_type, consider this question UNCLEAR and ask the user to specify product_type.'
  

  Show lessSee more

  Scroll to top