Consulta de exibições semânticas

Privilégios necessários para consultar uma exibição semântica

Se você estiver usando uma função que não seja proprietária da exibição semântica, deverá receber o privilégio SELECT nessa exibição semântica para consultá-la.

Nota

Para consultar uma exibição semântica, você não precisa do privilégio SELECT nas tabelas usadas na exibição semântica. Você só precisa do privilégio SELECT na própria exibição semântica.

Esse comportamento é consistente com os privilégios necessários para consultar exibições padrão.

Para obter informações sobre a concessão de privilégios em exibições semânticas, consulte Concessão de privilégios em exibições semânticas.

Consultar uma exibição semântica

Para consultar uma exibição semântica, use a cláusula SEMANTIC_VIEW na cláusula FROM. O exemplo a seguir seleciona a dimensão customer_market_segment e a métrica order_average_value da exibição semântica tpch_analysis, que você já definiu:

SELECT * FROM SEMANTIC_VIEW(
    tpch_analysis
    DIMENSIONS customer.customer_market_segment
    METRICS orders.order_average_value
  );
Copy
+-------------------------+---------------------+
| CUSTOMER_MARKET_SEGMENT | ORDER_AVERAGE_VALUE |
+-------------------------+---------------------+
| AUTOMOBILE              |     142570.25947219 |
| FURNITURE               |     142563.63314267 |
| MACHINERY               |     142655.91550608 |
| HOUSEHOLD               |     141659.94753445 |
| BUILDING                |     142425.37987558 |
+-------------------------+---------------------+

O exemplo a seguir seleciona a dimensão customer_name e o fato c_customer_order_count da exibição semântica tpch_analysis:

SELECT * FROM SEMANTIC_VIEW(
    tpch_analysis
    DIMENSIONS customer.customer_name
    FACTS customer.c_customer_order_count
  )
  ORDER BY customer_name
  LIMIT 5;
Copy
+--------------------+------------------------+
| CUSTOMER_NAME      | C_CUSTOMER_ORDER_COUNT |
|--------------------+------------------------|
| Customer#000000001 |                      9 |
| Customer#000000002 |                     11 |
| Customer#000000003 |                      0 |
| Customer#000000004 |                     20 |
| Customer#000000005 |                     10 |
+--------------------+------------------------+

Observe o seguinte:

  • Na cláusula SEMANTIC_VIEW, você deve especificar pelo menos uma das seguintes cláusulas:

    • METRICS

    • DIMENSIONS

    • FACTS

    Não é possível omitir todas essas cláusulas da cláusula SEMANTIC_VIEW.

  • Ao especificar uma combinação dessas cláusulas, observe o seguinte:

    • Não é possível especificar FACTS e METRICS na mesma cláusula SEMANTIC_VIEW.

    • Embora você possa especificar FACTS e DIMENSIONS em uma consulta, você deve fazê-lo somente se as dimensões puderem determinar os fatos de forma única.

      A consulta agrupa os resultados por dimensões. Se os fatos não dependem das dimensões, os resultados podem ser não determinísticos.

    • Se você especificar FACTS e DIMENSIONS, todos os fatos e as dimensões usados na consulta (incluindo aqueles especificados na cláusula WHERE) deverão ser definidos na mesma tabela lógica.

    • Se você especificar uma dimensão e uma métrica, a tabela lógica para a dimensão deverá estar relacionada à tabela lógica para a métrica.

      Além disso, a tabela lógica para a dimensão deve ter um nível igual ou inferior de granularidade do que a tabela lógica para a métrica.

      Para determinar quais dimensões atendem a esses critérios, você pode executar o comando SHOW SEMANTIC DIMENSIONS FOR METRIC.

      Para obter mais detalhes, consulte Escolha das dimensões que você pode retornar para uma determinada métrica.

  • Na cláusula DIMENSIONS, você pode especificar uma expressão que se refere a um fato. Da mesma forma, na cláusula FACTS, você pode especificar uma expressão que se refere a uma dimensão. Por exemplo:

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

    Uma das principais diferenças entre o uso de DIMENSIONS e FACTS é que a consulta agrupa os resultados pelas dimensões e expressões especificadas na cláusula DIMENSIONS.

  • Especifique as cláusulas METRICS, DIMENSIONS e FACTS na ordem em que você deseja que apareçam nos resultados.

    Se você quiser que as dimensões apareçam primeiro nos resultados, especifique DIMENSIONS antes de METRICS. Caso contrário, especifique primeiro METRICS.

    Por exemplo, suponha que você especifique primeiro a cláusula METRICS:

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

    Na saída, a primeira coluna é a coluna de métrica (customer_order_count) e a segunda coluna é a coluna de dimensão (customer_name):

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

    Se, em vez disso, você especificar primeiro a cláusula DIMENSIONS:

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

    Na saída, a primeira coluna é a coluna de dimensão (customer_name) e a segunda coluna é a coluna de métrica (customer_order_count):

    +--------------------+----------------------+
    | CUSTOMER_NAME      | CUSTOMER_ORDER_COUNT |
    |--------------------+----------------------|
    | Customer#000000001 |                    6 |
    | Customer#000000002 |                    7 |
    | Customer#000000003 |                    0 |
    | Customer#000000004 |                   20 |
    | Customer#000000005 |                    4 |
    +--------------------+----------------------+
    
  • Você pode usar a relação definida por uma cláusula SEMANTIC_VIEW em outras construções SQL, incluindo JOIN, PIVOT, UNPIVOT, GROUP BY e expressões de tabela comuns (CTEs).

  • Os cabeçalhos das colunas de saída usam os nomes não qualificados das métricas e dimensões.

    Se você tiver várias métricas e dimensões com os mesmos nomes, use um alias de tabela para atribuir nomes diferentes aos cabeçalhos das colunas. Consulte Tratamento de nomes de colunas duplicados na saída.

Para retornar todas as métricas ou dimensões em uma determinada tabela lógica, use um asterisco como curinga, qualificado pelo nome da tabela lógica. Por exemplo, para retornar todas as métricas e dimensões definidas na tabela lógica customer:

SELECT * FROM SEMANTIC_VIEW(
  tpch_analysis
  DIMENSIONS customer.*
  METRICS customer.*
);
Copy
+-----------------------+-------------------------+--------------------+----------------------+----------------------+----------------+----------------------+
| 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 |
...

Escolha das dimensões que você pode retornar para uma determinada métrica

Ao especificar uma dimensão e uma métrica para retornar, a tabela base da dimensão deve estar relacionada à tabela base da métrica. Além disso, a tabela base da dimensão deve ter um nível de granularidade igual ou inferior à tabela base da métrica.

Por exemplo, suponha que você consulte a exibição semântica tpch_analysis que criou em Exemplo de uso do SQL para criar uma exibição semântica. Imagine que você queira retornar a dimensão customer.customer_count e a métrica orders.order_date:

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

Há falha na consulta porque a tabela orders para a dimensão order_date tem um nível de granularidade mais alto do que a tabela customer para a métrica 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'.

Para listar as dimensões que você pode retornar com uma métrica específica, execute o comando SHOW SEMANTIC DIMENSIONS FOR METRIC. Por exemplo:

SHOW SEMANTIC DIMENSIONS IN tpch_analysis FOR METRIC customer_order_count;
Copy
+------------+-------------------------+-------------+----------+----------+---------+
| 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    |
+------------+-------------------------+-------------+----------+----------+---------+

Exemplos básicos

Os exemplos a seguir usam a visualização tpch_analysis definida em Exemplo de uso do SQL para criar uma exibição semântica:

Recuperação de métrica

A instrução a seguir recupera a contagem total de clientes consultando uma métrica:

SELECT * FROM SEMANTIC_VIEW(
    tpch_analysis
    METRICS customer.customer_count
  );
Copy
+----------------+
| CUSTOMER_COUNT |
+----------------+
|          15000 |
+----------------+

Agrupamento de dados métricos por uma dimensão

A instrução a seguir agrupa dados métricos (order_average_value) por uma dimensão (customer_market_segment):

SELECT * FROM SEMANTIC_VIEW(
    tpch_analysis
    DIMENSIONS customer.customer_market_segment
    METRICS orders.order_average_value
  );
Copy
+-------------------------+---------------------+
| CUSTOMER_MARKET_SEGMENT | ORDER_AVERAGE_VALUE |
+-------------------------+---------------------+
| AUTOMOBILE              |     142570.25947219 |
| FURNITURE               |     142563.63314267 |
| MACHINERY               |     142655.91550608 |
| HOUSEHOLD               |     141659.94753445 |
| BUILDING                |     142425.37987558 |
+-------------------------+---------------------+

Usando a subcláusula SEMANTIC_VIEW com outras construções

O exemplo a seguir demonstra como você pode usar dimensões e métricas na subcláusula SEMANTIC_VIEW com outras construções SQL para filtrar, classificar e limitar os resultados:

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

Especificar expressões escalares que usam dimensões

O exemplo a seguir usa uma expressão escalar que se refere a uma dimensão na cláusula DIMENSIONS:

SELECT * FROM SEMANTIC_VIEW(
    tpch_analysis
    DIMENSIONS DATE_PART('year', orders.order_date)
  );
Copy
+--------------------------------------+
| DATE_PART('YEAR', ORDERS.ORDER_DATE) |
|--------------------------------------|
|                                 1992 |
|                                 1997 |
|                                 1998 |
|                                 1993 |
|                                 1996 |
|                                 1994 |
|                                 1995 |
+--------------------------------------+

Especificar a cláusula WHERE

O exemplo a seguir especifica uma cláusula WHERE que se refere a uma dimensão na cláusula DIMENSIONS:

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

Especificação de fatos na cláusula WHERE

O seguinte exemplo usa o fato region.r_name em uma condição na cláusula WHERE:

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

Tratamento de nomes de colunas duplicados na saída

As colunas de saída usam os nomes não qualificados das métricas e dimensões. Se você tiver várias métricas e dimensões com os mesmos nomes, várias colunas usarão o mesmo nome.

Para contornar isso, use um alias de tabela para atribuir nomes diferentes às colunas.

Por exemplo, suponha que você defina a seguinte exibição semântica, que define as dimensões nation.name e 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
  );
Copy

Se você consultar essa visualização e selecionar essas duas dimensões, a saída incluirá duas colunas denominadas name sem nenhum qualificador:

SELECT * FROM SEMANTIC_VIEW(
    duplicate_names
    DIMENSIONS nation.name, region.name
  );
Copy
+----------------+-------------+
| NAME           | NAME        |
+----------------+-------------+
| BRAZIL         | AMERICA     |
| MOROCCO        | AFRICA      |
| UNITED KINGDOM | EUROPE      |
| IRAN           | MIDDLE EAST |
| FRANCE         | EUROPE      |
| ...            | ...         |
+----------------+-------------+

Para não ambiguidade das colunas, use um alias de tabela para atribuir nomes de coluna diferentes (por exemplo, nation_name e region_name):

SELECT * FROM SEMANTIC_VIEW(
    duplicate_names
    DIMENSIONS nation.name, region.name
  ) AS table_alias(nation_name, region_name);
Copy
+----------------+-------------+
| NATION_NAME    | REGION_NAME |
+----------------+-------------+
| BRAZIL         | AMERICA     |
| MOROCCO        | AFRICA      |
| UNITED KINGDOM | EUROPE      |
| IRAN           | MIDDLE EAST |
| FRANCE         | EUROPE      |
| ...            | ...         |
+----------------+-------------+

Definição e consulta de métricas de função de janela

É possível definir métricas que chamam funções de janela e passam valores agregados. Elas são chamadas de métricas de função de janela.

Os seguintes exemplos ilustram a diferença entre a métrica de função de janela e a métrica que passa uma expressão no nível da linha para uma função de janela:

  • Veja abaixo uma métrica de função de janela:

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

    Neste exemplo, a função de janela SUM usa outra métrica (table_1.metric_3) como argumento.

    A métrica abaixo também é de função de janela:

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

    Neste exemplo, a função de janela SUM usa uma expressão de métrica válida (SUM(table_1.column_1)) como argumento.

  • A métrica abaixo não é de função de janela:

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

    Neste exemplo, a função de janela SUM usa uma coluna (table_1.column_1) como argumento, e o resultado dessa chamada de função de janela é passado para uma chamada de função de agregação SUM separada.

As seguintes seções explicam como definir e consultar as métricas de função de janela:

Definição de métricas de função de janela

Ao especificar uma chamada de função de janela, use esta sintaxe, que está descrita em Parâmetros para métricas de função de janela.

O exemplo a seguir cria uma exibição semântica que inclui as definições de várias métricas de função de janela. O exemplo usa tabelas do banco de dados de amostra TPC-DS. Para obter informações sobre como acessar esse banco de dados, consulte Como faço para adicionar o conjunto de dados TPC-DS à minha conta?.

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

  );
Copy

Você também pode usar outras métricas da mesma tabela lógica na definição da métrica. Por exemplo:

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

Nota

Não é possível usar métricas de função de janela em cálculos no nível da linha (fatos e dimensões) ou nas definições de outras métricas.

Consulta de métricas de função de janela

Quando você consulta uma exibição semântica e a consulta retorna uma métrica de função de janela, você também deve retornar as dimensões especificadas em PARTITION BY dimension, PARTITION BY EXCLUDING dimension e ORDER BY dimension na instrução CREATE SEMANTIC VIEW para a exibição semântica.

Por exemplo, suponha que você especifique as dimensões date.date e date.year nas cláusulas PARTITION BY EXCLUDING e ORDER BY na definição da métrica store_sales.avg_7_days_sales_quantity:

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'),
    ...
  );
Copy

Se você retornar a métrica store_sales.avg_7_days_sales_quantity em uma consulta, também deverá retornar as dimensões date.date e date.year:

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

Se você omitir as dimensões date.date e date.year, ocorrerá um erro.

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

Para determinar quais dimensões você deve especificar na consulta, execute o comando SHOW SEMANTIC DIMENSIONS FOR METRIC. Por exemplo, para determinar as dimensões que você deve especificar ao recuperar a métrica store_sales.avg_7_days_sales_quantity, execute este comando:

SHOW SEMANTIC DIMENSIONS IN sv_window_function_example FOR METRIC avg_7_days_sales_quantity;
Copy

Na saída do comando, a coluna required contém true para as dimensões que você deve especificar na consulta.

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

Os outros exemplos a seguir consultam as métricas de função de janela definidas em Definição de métricas de função de janela. Observe que a cláusula DIMENSIONS inclui as dimensões especificadas nas cláusulas PARTITION BY EXCLUDING e ORDER BY das definições das métricas.

O seguinte exemplo retorna a quantidade de vendas dos 30 dias anteriores:

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

O seguinte exemplo retorna a média por período consecutivo de 7 dias da quantidade total de vendas dos 30 dias anteriores:

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