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.

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

Observe que você pode definir um alias para uma dimensão ou métrica especificando o alias após o nome da dimensão ou métrica. Você também pode especificar a palavra-chave opcional AS antes do alias. O exemplo a seguir executa a mesma consulta, mas usa os aliases segment e average para a dimensão e a métrica retornadas nos resultados.

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

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;

+--------------------+------------------------+
| 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.*
);

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

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;

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

• Agrupamento de dados métricos por uma dimensão

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

• Especificar expressões escalares que usam dimensões

• Especificar a cláusula 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) 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'
  );

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

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

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

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

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

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