Interrogation des vues sémantiques¶

Privilèges requis pour interroger une vue sémantique¶

Si vous utilisez un rôle qui n’est pas propriétaire de la vue sémantique, vous devez bénéficier du privilège SELECT sur cette vue sémantique pour pouvoir l’interroger.

Pour plus d’informations sur l’octroi de privilèges sur les vues sémantiques, voir Octroyer des privilèges sur des vues sémantiques.

Interroger une vue sémantique¶

Pour interroger une vue sémantique, utilisez la clauseSEMANTIC_VIEW dans la clause FROM. L’exemple suivant sélectionne la dimension customer_market_segment et la métrique order_average_value de la vue sémantique tpch_analysis, que vous avez définies précédemment :

SELECT * FROM SEMANTIC_VIEW(
    tpch_analysis
    DIMENSIONS customer.customer_market_segment
    METRICS orders.order_average_value
  );

+-------------------------+---------------------+
| CUSTOMER_MARKET_SEGMENT | ORDER_AVERAGE_VALUE |
+-------------------------+---------------------+
| AUTOMOBILE              |     142570.25947219 |
| FURNITURE               |     142563.63314267 |
| MACHINERY               |     142655.91550608 |
| HOUSEHOLD               |     141659.94753445 |
| BUILDING                |     142425.37987558 |
+-------------------------+---------------------+

L’exemple suivant sélectionne la dimension customer_name and le fait c_customer_order_count de la vue sémantique tpch_analysis :

SELECT * FROM SEMANTIC_VIEW(
    tpch_analysis
    DIMENSIONS customer.customer_name
    FACTS customer.c_customer_order_count
  )
  ORDER BY customer_name
  LIMIT 5;

+--------------------+------------------------+
| CUSTOMER_NAME      | C_CUSTOMER_ORDER_COUNT |
|--------------------+------------------------|
| Customer#000000001 |                      9 |
| Customer#000000002 |                     11 |
| Customer#000000003 |                      0 |
| Customer#000000004 |                     20 |
| Customer#000000005 |                     10 |
+--------------------+------------------------+

Remarques :

• Dans la clause SEMANTIC_VIEW, vous devez spécifier au moins l’une des clauses suivantes :

  • METRICS

  • DIMENSIONS

  • FACTS

  Vous ne pouvez pas omettre toutes ces clauses dans la clause SEMANTIC_VIEW.

• Lorsque vous spécifiez une combinaison de ces clauses, notez ce qui suit :

  • Vous ne pouvez pas spécifier FACTS et METRICS dans la même clause SEMANTIC_VIEW.

  • Bien que vous puissiez spécifier à la fois FACTS et DIMENSIONS dans une requête, vous ne devez le faire que si les dimensions peuvent déterminer les faits de manière unique.

    La requête regroupe les résultats par dimensions. si les faits ne dépendent pas des dimensions, les résultats peuvent être non déterministes.

  • Si vous spécifiez à la fois FACTS et DIMENSIONS, tous les faits et dimensions utilisés dans la requête (y compris ceux spécifiés dans la clause WHERE) doivent être définis dans la même table logique.

  • Si vous spécifiez une dimension et une métrique, la table logique de la dimension doit être liée à la table logique de la métrique.

    En outre, la table logique de la dimension doit avoir un niveau de granularité égal ou inférieur à celui de la table logique de la métrique.

    Pour déterminer quelles dimensions répondent à ces critères, vous pouvez exécuter la commande SHOW SEMANTIC DIMENSIONS FOR METRIC.

    Pour plus de détails, voir Choix des dimensions que vous pouvez renvoyer pour une métrique donnée.

• Dans la clause DIMENSIONS, vous pouvez spécifier une expression qui fait référence à un fait. De même, dans la clause FACTS, vous pouvez spécifier une expression qui fait référence à une dimension. Par exemple :

  -- Dimension expression that refers to a fact
  DIMENSIONS my_table.my_fact
  
  -- Fact expression that refers to a dimension
  FACTS my_table.my_dimension
  

  Copy

  Une des principales différences entre l’utilisation de DIMENSIONS et FACTS est que la requête regroupe les résultats selon les dimensions et les expressions spécifiées dans la clause DIMENSIONS.

• Spécifiez les clauses METRICS, DIMENSIONS et FACTS dans l’ordre dans lequel vous voulez qu’elles apparaissent dans les résultats.

  Si vous souhaitez que les dimensions apparaissent en premier dans les résultats, indiquez DIMENSIONS avant METRICS. Sinon, indiquez d’abord METRICS.

  Par exemple, supposons que vous indiquiez la clause METRICS en premier :

  SELECT * FROM SEMANTIC_VIEW(
      tpch_analysis
      METRICS customer.customer_order_count
      DIMENSIONS customer.customer_name
    )
    ORDER BY customer_name
    LIMIT 5;
  

  Copy

  Dans la sortie, la première colonne est celle de la métrique (customer_order_count) et la deuxième colonne est celle de la dimension (customer_name) :

  +----------------------+--------------------+
  | CUSTOMER_ORDER_COUNT | CUSTOMER_NAME      |
  |----------------------+--------------------|
  |                    6 | Customer#000000001 |
  |                    7 | Customer#000000002 |
  |                    0 | Customer#000000003 |
  |                   20 | Customer#000000004 |
  |                    4 | Customer#000000005 |
  +----------------------+--------------------+
  

  Inversement, si vous spécifiez d’abord la clause DIMENSIONS :

  SELECT * FROM SEMANTIC_VIEW(
      tpch_analysis
      DIMENSIONS customer.customer_name
      METRICS customer.customer_order_count
    )
    ORDER BY customer_name
    LIMIT 5;
  

  Copy

  Dans la sortie, la première colonne est celle de la dimension (customer_name) et la deuxième colonne est celle de la métrique (customer_order_count) :

  +--------------------+----------------------+
  | CUSTOMER_NAME      | CUSTOMER_ORDER_COUNT |
  |--------------------+----------------------|
  | Customer#000000001 |                    6 |
  | Customer#000000002 |                    7 |
  | Customer#000000003 |                    0 |
  | Customer#000000004 |                   20 |
  | Customer#000000005 |                    4 |
  +--------------------+----------------------+
  

• Vous pouvez utiliser la relation définie par une clause SEMANTIC_VIEW dans d’autres constructions SQL, notamment JOIN, PIVOT, UNPIVOT, GROUP BY et les expressions de table communes (CTE).

• Les en-têtes des colonnes de sortie utilisent les noms non qualifiés des métriques et des dimensions.

  Si plusieurs métriques et dimensions portent le même nom, utilisez un alias de table pour attribuer des noms différents aux en-têtes de colonne. Voir Gestion des noms de colonnes en double dans la sortie.

Pour renvoyer toutes les métriques ou dimensions dans une table logique donnée, utilisez un astérisque comme caractère générique, qualifié par le nom de la table logique. Par exemple, pour renvoyer toutes les métriques et dimensions définies dans la table logique customer :

SELECT * FROM SEMANTIC_VIEW(
  tpch_analysis
  DIMENSIONS customer.*
  METRICS customer.*
);

+-----------------------+-------------------------+--------------------+----------------------+----------------------+----------------+----------------------+
| CUSTOMER_COUNTRY_CODE | CUSTOMER_MARKET_SEGMENT | CUSTOMER_NAME      | CUSTOMER_NATION_NAME | CUSTOMER_REGION_NAME | CUSTOMER_COUNT | CUSTOMER_ORDER_COUNT |
|-----------------------+-------------------------+--------------------+----------------------+----------------------+----------------+----------------------|
| 18                    | BUILDING                | Customer#000034857 | INDIA                | ASIA                 |              1 |                    0 |
| 14                    | AUTOMOBILE              | Customer#000145116 | EGYPT                | MIDDLE EAST          |              1 |                    0 |
...

Choix des dimensions que vous pouvez renvoyer pour une métrique donnée¶

Lorsque vous spécifiez une dimension et une métrique à renvoyer, la table de base de la dimension doit être liée à la table de base de la métrique. En outre, la table de base de la dimension doit avoir un niveau de granularité égal ou inférieur à celui de la table de base de la métrique.

Par exemple, supposons que vous interrogiez la vue sémantique tpch_analysis que vous avez créée dans Exemple d’utilisation de SQL pour créer une vue sémantique. Supposons que vous vouliez renvoyer la dimension customer.customer_count et la métrique orders.order_date :

SELECT * FROM SEMANTIC_VIEW (
  tpch_analysis
  DIMENSIONS orders.order_date
  METRICS customer.customer_order_count
);

Cette requête échoue parce que la table orders pour la dimension order_date a un niveau de granularité supérieur à celui de la table customer pour la métrique customer_order_count :

010234 (42601): SQL compilation error:
Invalid dimension specified: The dimension entity 'ORDERS' must be related to and
  have an equal or lower level of granularity compared to the base metric or dimension entity 'CUSTOMER'.

Pour dresser la liste des dimensions que vous pouvez renvoyer avec une métrique spécifique, exécutez la commande SHOW SEMANTIC DIMENSIONS FOR METRIC. Par exemple :

SHOW SEMANTIC DIMENSIONS IN tpch_analysis FOR METRIC customer_order_count;

+------------+-------------------------+-------------+----------+----------+---------+
| table_name | name                    | data_type   | required | synonyms | comment |
|------------+-------------------------+-------------+----------+----------+---------|
| CUSTOMER   | CUSTOMER_COUNTRY_CODE   | VARCHAR(15) | false    | NULL     | NULL    |
| CUSTOMER   | CUSTOMER_MARKET_SEGMENT | VARCHAR(10) | false    | NULL     | NULL    |
| CUSTOMER   | CUSTOMER_NAME           | VARCHAR(25) | false    | NULL     | NULL    |
| CUSTOMER   | CUSTOMER_NATION_NAME    | VARCHAR(25) | false    | NULL     | NULL    |
| CUSTOMER   | CUSTOMER_REGION_NAME    | VARCHAR(25) | false    | NULL     | NULL    |
| NATION     | NATION_NAME             | VARCHAR(25) | false    | NULL     | NULL    |
+------------+-------------------------+-------------+----------+----------+---------+

Exemples de base¶

Les exemples suivants utilisent la vue tpch_analysis définie dans Exemple d’utilisation de SQL pour créer une vue sémantique :

• Récupération d’une métrique

• Grouper des données métriques par dimension

• Utiliser la sous-clause SEMANTIC_VIEW avec d’autres constructions

• Spécifier des expressions scalaires utilisant des dimensions

• Spécifier la clause WHERE

SELECT * FROM SEMANTIC_VIEW(
    tpch_analysis
    METRICS customer.customer_count
  );

+----------------+
| CUSTOMER_COUNT |
+----------------+
|          15000 |
+----------------+

SELECT * FROM SEMANTIC_VIEW(
    tpch_analysis
    DIMENSIONS customer.customer_market_segment
    METRICS orders.order_average_value
  );

+-------------------------+---------------------+
| CUSTOMER_MARKET_SEGMENT | ORDER_AVERAGE_VALUE |
+-------------------------+---------------------+
| AUTOMOBILE              |     142570.25947219 |
| FURNITURE               |     142563.63314267 |
| MACHINERY               |     142655.91550608 |
| HOUSEHOLD               |     141659.94753445 |
| BUILDING                |     142425.37987558 |
+-------------------------+---------------------+

SELECT * FROM SEMANTIC_VIEW(
    tpch_analysis
    DIMENSIONS customer.customer_name
    METRICS orders.average_line_items_per_order,
            orders.order_average_value
  )
  WHERE average_line_items_per_order > 4
  ORDER BY average_line_items_per_order DESC
  LIMIT 5;

+--------------------+------------------------------+---------------------+
| CUSTOMER_NAME      | AVERAGE_LINE_ITEMS_PER_ORDER | ORDER_AVERAGE_VALUE |
+--------------------+------------------------------+---------------------+
| Customer#000045678 |                         6.87 |           175432.21 |
| Customer#000067890 |                         6.42 |           182376.58 |
| Customer#000012345 |                         5.93 |           169847.42 |
| Customer#000034567 |                         5.76 |           178952.36 |
| Customer#000056789 |                         5.64 |           171248.75 |
+--------------------+------------------------------+---------------------+

SELECT * FROM SEMANTIC_VIEW(
    tpch_analysis
    DIMENSIONS DATE_PART('year', orders.order_date)
  );

+--------------------------------------+
| DATE_PART('YEAR', ORDERS.ORDER_DATE) |
|--------------------------------------|
|                                 1992 |
|                                 1997 |
|                                 1998 |
|                                 1993 |
|                                 1996 |
|                                 1994 |
|                                 1995 |
+--------------------------------------+

SELECT * FROM SEMANTIC_VIEW(
    tpch_analysis
    DIMENSIONS orders.order_date
    METRICS orders.average_line_items_per_order,
            orders.order_average_value
    WHERE orders.order_date > '1995-01-01'
  )
  ORDER BY order_date ASC
  LIMIT 5;

+------------+------------------------------+---------------------+
| ORDER_DATE | AVERAGE_LINE_ITEMS_PER_ORDER | ORDER_AVERAGE_VALUE |
|------------+------------------------------+---------------------|
| 1995-01-02 |                     3.884547 |     151237.54900533 |
| 1995-01-03 |                     3.894819 |     145751.84384615 |
| 1995-01-04 |                     3.838863 |     145331.39167457 |
| 1995-01-05 |                     4.040689 |     150723.67353678 |
| 1995-01-06 |                     3.990755 |     152786.54109399 |
+------------+------------------------------+---------------------+

SELECT * FROM SEMANTIC_VIEW(
    tpch_analysis
    FACTS customer.c_customer_order_count
    WHERE orders.order_date < '2021-01-01' AND region.r_name = 'AMERICA'
  );

Gestion des noms de colonnes en double dans la sortie¶

Les colonnes de sortie utilisent les noms non qualifiés des métriques et des dimensions. Si vous avez plusieurs métriques et dimensions portant le même nom, plusieurs colonnes utiliseront le même nom.

Pour contourner ce problème, utilisez un alias de table afin d’attribuer des noms différents aux colonnes.

Par exemple, supposons que vous définissiez une vue sémantique spécifiant les dimensions nation.name et region.name :

CREATE OR REPLACE SEMANTIC VIEW duplicate_names

  TABLES (
    nation AS SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.NATION PRIMARY KEY (n_nationkey),
    region AS SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.REGION PRIMARY KEY (r_regionkey)
  )

  RELATIONSHIPS (
    nation (n_regionkey) REFERENCES region
  )

  DIMENSIONS (
    nation.name AS nation.n_name,
    region.name AS region.r_name
  );

Si vous interrogez cette vue en sélectionnant ces deux dimensions, la sortie comprend deux colonnes nommées name sans aucun qualificateur :

SELECT * FROM SEMANTIC_VIEW(
    duplicate_names
    DIMENSIONS nation.name, region.name
  );

+----------------+-------------+
| NAME           | NAME        |
+----------------+-------------+
| BRAZIL         | AMERICA     |
| MOROCCO        | AFRICA      |
| UNITED KINGDOM | EUROPE      |
| IRAN           | MIDDLE EAST |
| FRANCE         | EUROPE      |
| ...            | ...         |
+----------------+-------------+

Pour désambiguïser les colonnes, utilisez un alias de table pour attribuer des noms de colonnes différents (par exemple, nation_name et region_name) :

SELECT * FROM SEMANTIC_VIEW(
    duplicate_names
    DIMENSIONS nation.name, region.name
  ) AS table_alias(nation_name, region_name);

+----------------+-------------+
| NATION_NAME    | REGION_NAME |
+----------------+-------------+
| BRAZIL         | AMERICA     |
| MOROCCO        | AFRICA      |
| UNITED KINGDOM | EUROPE      |
| IRAN           | MIDDLE EAST |
| FRANCE         | EUROPE      |
| ...            | ...         |
+----------------+-------------+

Définition et requête des métriques des fonctions de fenêtre¶

Vous pouvez définir des métriques qui appellent des fonctions de fenêtre et transmettent des valeurs agrégées. Ces métriques sont appelées métriques de fonction de fenêtre.

Les exemples suivants illustrent la différence entre une métrique de fonction de fenêtre et une métrique qui transmet une expression de niveau ligne à une fonction de fenêtre :

• La métrique suivante est une métrique de fonction de fenêtre :

  METRICS (
    table_1.metric_1 AS SUM(table_1.metric_3) OVER( ... )
  )
  

  Copy

  Dans cet exemple, la fonction de fenêtre SUM prend une autre métrique (table_1.metric_3) en tant qu’argument.

  La métrique suivante est également une métrique de fonction de fenêtre :

  METRICS (
    table_1.metric_2 AS SUM(
      SUM(table_1.column_1)
    ) OVER( ... )
  )
  

  Copy

  Dans cet exemple, la fonction de fenêtre SUM prend une expression de métrique valide (SUM(table_1.column_1)) en tant qu’argument.

• La métrique suivante n’est pas une métrique de fonction de fenêtre :

  METRICS (
    table_1.metric_1 AS SUM(
      SUM(table_1.column_1) OVER( ... )
    )
  )
  

  Copy

  Dans cet exemple, la fonction de fenêtre SUM prend une colonne (table_1.column_1) en tant qu’argument, et le résultat de cet appel de fonction de fenêtre est transmis à un appel de la fonction d’agrégation SUM distinct.

Les sections suivantes expliquent comment définir et interroger les métriques des fonctions de fenêtre :

• Définition des métriques de la fonction de fenêtre

• Interrogation des métriques de la fonction de fenêtre

CREATE OR REPLACE SEMANTIC VIEW sv_window_function_example
  TABLES (
    store_sales AS SNOWFLAKE_SAMPLE_DATA.TPCDS_SF10TCL.store_sales,
    date AS SNOWFLAKE_SAMPLE_DATA.TPCDS_SF10TCL.date_dim PRIMARY KEY (d_date_sk)
  )
  RELATIONSHIPS (
    sales_to_date AS store_sales(ss_sold_date_sk) REFERENCES date(d_date_sk)
  )
  DIMENSIONS (
    date.date AS d_date,
    date.d_date_sk AS d_date_sk,
    date.year AS d_year
  )
  METRICS (
    store_sales.total_sales_quantity AS SUM(ss_quantity)
      WITH SYNONYMS = ('Total sales quantity'),

    store_sales.avg_7_days_sales_quantity as AVG(total_sales_quantity)
      OVER (PARTITION BY EXCLUDING date.date, date.year ORDER BY date.date
        RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW)
      WITH SYNONYMS = ('Running 7-day average of total sales quantity'),

    store_sales.total_sales_quantity_30_days_ago AS LAG(total_sales_quantity, 30)
      OVER (PARTITION BY EXCLUDING date.date, date.year ORDER BY date.date)
      WITH SYNONYMS = ('Sales quantity 30 days ago'),

    store_sales.avg_7_days_sales_quantity_30_days_ago AS AVG(total_sales_quantity)
      OVER (PARTITION BY EXCLUDING date.date, date.year ORDER BY date.date
        RANGE BETWEEN INTERVAL '36 days' PRECEDING AND INTERVAL '30 days' PRECEDING)
      WITH SYNONYMS = ('Running 7-day average of total sales quantity 30 days ago')

  );

METRICS (
  orders.m3 AS SUM(m2) OVER (PARTITION BY m1 ORDER BY m2),
  orders.m4 AS ((SUM(m2) OVER (..)) / m1) + 1
)

CREATE OR REPLACE SEMANTIC VIEW sv_window_function_example
  ...
  DIMENSIONS (
    ...
    date.date AS d_date,
    ...
    date.year AS d_year
    ...
  )
  METRICS (
    ...
    store_sales.avg_7_days_sales_quantity as AVG(total_sales_quantity)
      OVER (PARTITION BY EXCLUDING date.date, date.year ORDER BY date.date
        RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW)
      WITH SYNONYMS = ('Running 7-day average of total sales quantity'),
    ...
  );

SELECT * FROM SEMANTIC_VIEW (
  sv_window_function_example
  DIMENSIONS date.date, date.year
  METRICS store_sales.avg_7_days_sales_quantity
);

010260 (42601): SQL compilation error:
Invalid semantic view query: Dimension 'DATE.DATE' used in a
   window function metric must be requested in the query.

SHOW SEMANTIC DIMENSIONS IN sv_window_function_example FOR METRIC avg_7_days_sales_quantity;

+------------+-----------+--------------+----------+----------+---------+
| table_name | name      | data_type    | required | synonyms | comment |
|------------+-----------+--------------+----------+----------+---------|
| DATE       | DATE      | DATE         | true     | NULL     | NULL    |
| DATE       | D_DATE_SK | NUMBER(38,0) | false    | NULL     | NULL    |
| DATE       | YEAR      | NUMBER(38,0) | true     | NULL     | NULL    |
+------------+-----------+--------------+----------+----------+---------+

SELECT * FROM SEMANTIC_VIEW (
  sv_window_function_example
  DIMENSIONS date.date, date.year
  METRICS store_sales.total_sales_quantity_30_days_ago
);

SELECT * FROM SEMANTIC_VIEW (
  sv_window_function_example
  DIMENSIONS date.date, date.year
  METRICS store_sales.avg_7_days_sales_quantity_30_days_ago
);