Semantische Ansichten abfragen¶

Erforderliche Berechtigungen für die Abfrage einer semantischen Ansicht¶

Wenn Sie eine Rolle verwenden, die nicht Eigentümer der semantischen Ansicht ist, müssen Sie über die Berechtigung SELECT für diese semantische Ansicht verfügen, um diese abzufragen.

Informationen über die Erteilung von Berechtigungen für semantische Ansichten finden Sie unter Erteilung von Berechtigungen für semantische Ansichten.

Abfrage einer semantischen Ansicht¶

Um eine semantische Ansicht abzufragen, verwenden Sie die SEMANTIC_VIEW-Klausel in der FROM-Klausel. Im folgenden Beispiel werden die Dimension``customer_market_segment`` und die Metrik order_average_value von der semantischen Ansicht``tpch_analysis`` ausgewählt, die Sie zuvor definiert haben:

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

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

Beachten Sie, dass Sie einen Alias für eine Dimension oder Metrik definieren können, indem Sie den Alias nach dem Namen der Dimension oder Metrik angeben. Sie können auch das optionale Schlüsselwort AS vor dem Alias angeben. Im folgenden Beispiel wird dieselbe Abfrage ausgeführt, es werden jedoch die Aliasse segment und average für die Dimension und Metrik verwendet, die in den Ergebnissen zurückgegeben werden.

SELECT * FROM SEMANTIC_VIEW(
    tpch_analysis
    DIMENSIONS customer.customer_market_segment AS segment
    METRICS orders.order_average_value average
  )
  ORDER BY segment;

+------------+-----------------+
| SEGMENT    |         AVERAGE |
|------------+-----------------|
| AUTOMOBILE | 142570.25947219 |
| BUILDING   | 142425.37987558 |
| FURNITURE  | 142563.63314267 |
| HOUSEHOLD  | 141659.94753445 |
| MACHINERY  | 142655.91550608 |
+------------+-----------------+

Im folgenden Beispiel wählen die Dimension customer_name``und der Fakt``c_customer_order_count aus der semantischen Ansicht tpch_analysis aus:

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 |
+--------------------+------------------------+

Beachten Sie Folgendes:

• Sie müssen in der SEMANTIC_VIEW-Klausel mindestens eine der folgenden Klauseln angeben:

  • METRICS

  • DIMENSIONS

  • FACTS

  Sie können nicht alle diese Klauseln aus der SEMANTIC_VIEW-Klausel weglassen.

• Wenn Sie eine Kombination dieser Klauseln angeben, ist Folgendes zu beachten:

  • Sie können FACTS und METRICS nicht in der gleichenSEMANTIC_VIEW-Klausel angeben.

  • Sie können jedoch sowohl FACTS als auchDIMENSIONS in einer Abfrage angeben. Sie sollten dies nur tun, wenn die Dimensionen die Fakten eindeutig bestimmen können.

    Die Abfrage gruppiert die Ergebnisse nach Dimensionen: Wenn die Fakten nicht von den Dimensionen abhängen, können die Ergebnisse nicht-deterministisch sein.

  • Wenn Sie FACTS und DIMENSIONS angeben, müssen alle in der Abfrage verwendeten Fakten und Dimensionen (einschließlich diejenigen, die in der WHERE-Klausel angegeben sind), in der gleichen logischen Tabelle definiert sein.

  • Wenn Sie eine Dimension und eine Metrik angeben, muss die logische Tabelle für die Dimension mit der logischen Tabelle für die Metrik in Beziehung gesetzt werden.

    Außerdem muss die logische Tabelle für die Dimension eine gleiche oder geringere Granularität aufweisen als die logische Tabelle für die Metrik.

    Um festzustellen, welche Dimensionen diese Kriterien erfüllen, können Sie den SHOW SEMANTIC DIMENSIONS FOR METRIC-Befehl ausführen.

    Weitere Details dazu finden Sie unter Auswahl der Dimensionen, die Sie für eine bestimmte Metrik zurückgeben können.

• In der DIMENSIONS-Klausel können Sie einen Ausdruck angeben, der sich auf einen Fakt bezieht. Ähnlich verhält es sich mit der FACTS-Klausel, in der Sie einen Ausdruck angeben können, der sich auf eine Dimension bezieht. Beispiel:

  -- 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

  Einer der Hauptunterschiede zwischen der Verwendung von DIMENSIONS und FACTS besteht darin, dass die Abfrage die Ergebnisse nach den Dimensionen und Ausdrücken gruppiert, die in derDIMENSIONS-Klausel angegeben sind.

• Geben Sie die METRICS-, DIMENSIONS- und FACTS-Klauseln in der Reihenfolge an, in der sie in den Ergebnissen erscheinen sollen.

  Wenn Sie möchten, dass die Abmessungen zuerst in den Ergebnissen erscheinen, geben Sie DIMENSIONS vor METRICS an. Andernfalls geben Sie zuerst METRICS an.

  Nehmen wir zum Beispiel an, dass Sie zuerst die METRICS-Klausel angeben:

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

  Copy

  In der Ausgabe ist die erste Spalte die metrische Spalte (customer_order_count) und die zweite Spalte ist die Dimensionsspalte (customer_name):

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

  Wenn Sie stattdessen zuerst die DIMENSIONS-Klausel angeben:

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

  Copy

  In der Ausgabe ist die erste Spalte die Dimensionsspalte (customer_name) und die zweite Spalte ist die metrische Spalte (customer_order_count):

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

• Sie können die durch eine SEMANTIC_VIEW-Klausel definierte Beziehung in anderen SQL-Konstrukten verwenden, einschließlich JOIN, PIVOT, UNPIVOT, GROUP BY und allgemeinen Tabellenausdrücken (CTEs).

• Die Spaltenüberschriften der Ausgabe verwenden die unqualifizierten Namen der Metriken und Dimensionen.

  Wenn Sie mehrere Metriken und Dimensionen mit denselben Namen haben, verwenden Sie einen Tabellenalias, um den Spaltenüberschriften unterschiedliche Namen zuzuweisen. Siehe Behandlung doppelter Spaltennamen in der Ausgabe.

Um alle Metriken oder Dimensionen in einer gegebenen logischen Tabelle zurückzugeben, verwenden Sie ein Sternchen als Platzhalter, qualifiziert durch den Namen der logischen Tabelle. Beispielsweise, um alle in der logischen Tabelle``customer`` definierten Metriken und Dimensionen zurückzugeben:

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 |
...

Auswahl der Dimensionen, die Sie für eine bestimmte Metrik zurückgeben können¶

Wenn Sie eine Dimension und eine Metrik angeben, die zurückgegeben werden soll, muss die Basistabelle für die Dimension mit der Basistabelle für die Metrik in Beziehung stehen. Außerdem muss die Basistabelle für die Dimension eine gleiche oder geringere Granularität aufweisen als die Basistabelle für die Metrik.

Angenommen, Sie fragen die semantische Ansicht tpch_analysis ab, die Sie in Beispiel für die Verwendung von SQL zur Erstellung einer semantischen Ansicht erstellt haben. Angenommen, Sie möchten die Dimension customer.customer_count und die Metrik orders.order_date zurückgeben:

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

Diese Abfrage schlägt fehl, weil die Tabelle orders für die Dimension order_date eine höhere Granularität als die Tabelle``customer`` für die Metrik customer_order_count hat:

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'.

Um die Dimensionen aufzulisten, die Sie mit einer bestimmten Metrik zurückgeben können, führen Sie den Befehl SHOW SEMANTIC DIMENSIONS FOR METRIC aus. Beispiel:

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    |
+------------+-------------------------+-------------+----------+----------+---------+

Grundlegende Beispiele¶

Die folgenden Beispiele verwenden die Ansicht tpch_analysis, die in Beispiel für die Verwendung von SQL zur Erstellung einer semantischen Ansicht definiert ist:

• Metrik abrufen

• Gruppierung metrischer Daten nach einer Dimension

• Verwendung der Subklausel SEMANTIC_VIEW mit anderen Konstrukten

• Angabe von skalaren Ausdrücken, die Dimensionen verwenden

• Angabe der WHERE-Klausel

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) AS year
  )
  ORDER BY year;

+------+
| YEAR |
|------|
| 1992 |
| 1993 |
| 1994 |
| 1995 |
| 1996 |
| 1997 |
| 1998 |
+------+

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

Behandlung doppelter Spaltennamen in der Ausgabe¶

Die Ausgabespalten verwenden die unqualifizierten Namen der Metriken und Dimensionen. Wenn Sie mehrere Metriken und Dimensionen mit demselben Namen haben, werden mehrere Spalten denselben Namen verwenden.

Um dies zu umgehen, verwenden Sie einen Tabellenalias, um den Spalten andere Namen zuzuweisen.

Nehmen wir zum Beispiel an, dass Sie die folgende semantische Ansicht definieren, die die Dimensionen nation.name und region.name definiert:

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
  );

Wenn Sie diese Ansicht abfragen und diese beiden Dimensionen auswählen, enthält die Ausgabe zwei Spalten mit dem Namen name ohne Qualifizierer:

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      |
| ...            | ...         |
+----------------+-------------+

Um die Spalten eindeutig zuzuordnen, verwenden Sie einen Tabellenalias, um verschiedene Spaltennamen zu vergeben (zum Beispiel nation_name und 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      |
| ...            | ...         |
+----------------+-------------+

Fensterfunktionsmetriken definieren und abfragen¶

Sie können Metriken festlegen, die Fensterfunktionen abrufen und aggregierte Werte übergeben. Diese Metriken werden Fensterfunktions-Metriken genannt.

Die folgenden Beispiele veranschaulichen den Unterschied zwischen einer Metrik einer Fensterfunktion und einer Metrik, die einen Ausdruck auf Zeilenebene an eine Fensterfunktion übergibt:

• Die folgende Metrik ist eine Fensterfunktionsmetrik:

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

  Copy

  In diesem Beispiel nimmt die SUM-Fensterfunktion eine andere Metrik (table_1.metric_3) als Argument.

  Die folgende Metrik ist auch eine Fensterfunktionsmetrik:

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

  Copy

  In diesem Beispiel nimmt die SUM-Fensterfunktion einen gültigen metrischen Ausdruck (SUM(table_1.column_1)) als Argument.

• Die folgende Metrik ist keine Fensterfunktionsmetrik:

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

  Copy

  In diesem Beispiel nimmt die SUM-Fensterfunktion eine Spalte (table_1.column_1) als Argument. Das Ergebnis dieses Fensterfunktionsaufrufs wird an einen separaten aggregierten SUM-Funktionsaufruf übergeben.

In den folgenden Abschnitten wird erklärt, wie Sie Fensterfunktionsmetriken definieren und abfragen:

• Fensterfunktions-Metriken definieren

• Fensterfunktionsmetriken abfragen

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
);