使用 SQL 命令创建和管理语义视图¶

定义逻辑表¶

在 CREATE SEMANTIC VIEW 命令中，使用 TABLES 子句定义视图中的逻辑表。在本子句中，您可以执行以下操作：

• 指定物理表名和可选别名。

• 识别逻辑表中的以下列：

  • 用作主键的列。

  • 包含唯一值的列（主键列除外）。

  您可以使用这些列在此语义视图中定义关系。

• 为表添加同义词（以增强可发现性）。

• 包括描述性注释。

在 前面介绍的示例 中，TABLES 子句定义了以下三个逻辑表：

• 一个 orders 表，包含来自 TPC-H orders 表的订单信息。

• 一个 customers 表，包含来自 TPC-H customers 表的客户信息。

• A line_items table containing the line items in orders from the TPC-H lineitem table.

The example uses the PRIMARY KEY clause to identify the columns to be used as primary keys for each logical table. Primary keys and unique values help determine the types of relationships between the tables (for example, many-to-one or one-to-one).

该示例还提供了用于描述逻辑表的同义词和注释，使数据更易于发现。

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

识别逻辑表之间的关系¶

In the CREATE SEMANTIC VIEW command, use the RELATIONSHIPS clause to identify the relationships between the tables in the view. For each relationship, you specify:

• 关系的可选名称。

• 包含外键的逻辑表的名称。

• 表中用于定义外键的列。

• 包含主键或具有唯一值的列的逻辑表的名称。

• 表中定义主键或包含唯一值的列。

  • 如果您已经在 TABLES 子句中为逻辑表指定 PRIMARY KEY，则无需在关系中指定主键列。

  • 如果 TABLES 子句中逻辑表只有一个 UNIQUE 关键字，则无需在关系中指定相应的列。

  You can also specify a date, time, timestamp, or numeric column, if you want to join the columns on a range.

在 前面介绍的示例 中，RELATIONSHIPS 子句指定了以下两种关系：

• orders 和 customers 表之间的关系。在 orders 表中，o_custkey 是一个外键，指向 customers 表中的主键 c_custkey。

• line_items 和 orders 表之间的关系。在 line_items 表中，l_orderkey 是一个外键，指向 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)
)

Using a date, time, timestamp, or numeric range to join logical tables¶

By default, when you specify a relationship between two logical tables, the tables are joined on an equality condition.

If you need to join two logical tables on a date, time, timestamp, or numeric range (where the values in a column of one table need to be in the same range as the values in a column of another table), you can specify the ASOF keyword with the column name in the REFERENCES clause:

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

A query of the semantic view defined above produces an ASOF JOIN that uses the >= comparison operator in the MATCH_CONDITION clause. This joins the two tables so that the values in col_table_1 are greater than or equal to the values in 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
  )
...

You can use the ASOF keyword for columns of the same types that you can use with ASOF JOIN.

For example, suppose that you have tables containing customer, customer address, and order data:

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

In this example, the customer_address table has a ca_start_date column, which indicates when the customer started residing at the specified address. The orders table has a o_ord_date column, which is the date of the order.

Suppose that you want to be able to query information about customer orders and retrieve the zip codes corresponding to where the customer resided when the orders were placed.

You can define a semantic view that specifies an ASOF join between the ca_start_date and o_ord_date columns:

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

Suppose that you query this semantic view to return the sum of the order amounts per month for each zip code:

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

The query effectively uses an ASOF JOIN to join the tables on the date columns, where the order date is greater than or equal to the address starting date:

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

Joining logical tables that contain ranges of values¶

You can use a range join when you want to join a table with another table that defines a range of possible values in the first table. For example, suppose that one table represents sales orders and has a column with the timestamp when the order was placed. Suppose that another table represents fiscal quarters and contains the distinct ranges of time that represent these quarters. You can create a semantic view that joins the two tables so that the row for an order includes the fiscal quarter in which the order was placed.

In the table that contains the ranges, each range must be distinct. No two ranges can overlap.

In the table data, if you want to specify the lowest possible value for the range or the highest possible value for the range, use NULL.

For example, the following table defines a set of ranges of times that do not overlap:

• The first row covers the range that includes everything up to (but not including) January 1, 2024.

• The last row covers the range that includes everything from March 20, 2024, onwards.

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

For cases like these, you can set up a semantic view that supports range-join queries. When you create the semantic view, you must do the following:

1. For the logical table containing the start and end times of a time period, define a constraint that specifies that no two ranges can overlap.

   In the TABLE clause of the CREATE SEMANTIC VIEW command, specify the CONSTRAINT clause in the logical table definition. For the syntax, see the documentation for CONSTRAINT in the CREATE SEMANTIC VIEW topic.

2. Define a relationship between the column containing the timestamp in one table and the start and end time columns in the other table.

   In the RELATIONSHIPS clause of the CREATE SEMANTIC VIEW command, use the BETWEEN clause to specify the columns containing the start and end times. For the syntax, see the documentation for RELATIONSHIP in the CREATE SEMANTIC VIEW topic.

For example, suppose that the my_time_periods table defines distinct periods of time:

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

Suppose that the my_events table captures events that occurred within those periods of time:

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

You can define a semantic view that joins the tables. Rows in my_events are joined with rows in my_time_periods, where the value in the event_timestamp column in my_events is within the range specified by the start_time and end_time columns in 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(*)
  );

The following query demonstrates how the rows are joined:

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

As shown in the examples, the dim_time_period_name dimension for each row in the results is the name of the time period that the dim_event_timestamp dimension falls into.

定义事实、维度和指标¶

在 CREATE SEMANTIC VIEW 命令中，使用 FACTS、DIMENSIONS 和 METRICS 子句定义语义视图中的事实、维度和指标。

您必须在语义视图中定义至少一个维度或指标。

对于每个事实、维度或指标，您可以指定以下内容：

• 它所属的逻辑表。

  备注

  If you want to define a derived metric (a metric that is not specific to one logical table), you must omit the logical table name. See Defining derived metrics.

• 事实、维度或指标的名称。

• 用于计算它的 SQL 表达式。

  备注

  For dimensions, you can specify a Cortex Search Service to use for the dimension. For information, see Defining a dimension that uses a Cortex Search Service.

• 可选的同义词和注释。

前面介绍的示例 定义了以下多个事实、维度和指标：

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

Defining a dimension that uses a Cortex Search Service¶

To define a dimension that uses a Cortex Search Service, set the WITH CORTEX SEARCH SERVICE clause to the name of the Cortex Search Service. If the service is in a different database or schema, qualify the name of the service. For example:

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

Defining derived metrics¶

When you define a metric, you specify the name of the logical table that the metric belongs to. This is the logical table on which the metric is aggregated.

If you want to define a metric based on metrics from different logical tables, you can define a derived metric. A derived metric is a metric that is scoped to the semantic view (rather than to a specific logical table). A derived metric can combine metrics from multiple logical tables.

In the definition of a derived metric, omit the logical table name.

For example, suppose that you want to define a metric my_derived_metric_1 that is the sum of the metrics table_1.metric_1 and table_2.metric_2. When you define my_derived_metric_1, don't qualify the name with any logical table name:

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

You can use other derived metrics in the expression. For example:

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
)

Note the following restrictions when you define a derived metric:

• You cannot use the same name for a derived metric and a regular metric.

• The expression for a derived metric can use:

  • Aggregations of dimensions and facts defined in any logical table in the semantic view.

  • Scalar expressions of metrics defined in any logical table in the semantic view.

  • Other derived metrics.

  In the following example:

  • derived_metric_1 uses a scalar expression with two metrics.

  • derived_metric_2 uses an aggregation of a dimension.

  • derived_metric_3 adds an aggregation of a dimension to another derived metric.

  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

• You don't need to qualify the name of a metric, dimension, or fact in the expression if the name is not ambiguous. For example:

  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

  Note that metric_1 needs to be qualified by table_1 because there are two metrics named metric_1, but my_unique_metric_name does not need to be qualified because the name is unique.

• In the expression for a derived metric, you cannot use the following:

  • Aggregations of metrics.

  • Window functions.

  • References to physical columns.

  • References to facts or dimensions that are not aggregated.

• You cannot use a derived metric in the expression for a regular metric, dimension, or fact. Only another derived metric can use a derived metric in its expression.

Specifying the relationship for a metric when multiple relationship paths exist¶

In some cases, you multiple relationship paths might exist between two specific logical tables in a semantic view. In these cases, when you define a metric, you must specify the relationship path to use.

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

Identifying the dimensions that should be non-additive for a metric¶

In some cases, a metric should not be aggregated across specific dimensions. In these cases, you can mark the dimensions as non-additive.

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)

Marking a fact or metric as private¶

If you are defining a fact or metric only for use in calculations in the semantic view and you don't want the fact or metric to be returned in a query, you can specify the PRIVATE keyword to mark the fact or metric as private. For example:

FACTS (
  PRIVATE my_private_fact AS ...
)

METRICS (
  PRIVATE my_private_metric AS ...
)

When you query a semantic view that has private facts or metrics, you cannot specify a private fact or metric in the following clauses:

• The SELECT list

• 执行 CREATE SEMANTIC VIEW 命令。

• 执行 CREATE SEMANTIC VIEW 命令。

• METRICS

• 执行 CREATE SEMANTIC VIEW 命令。

Some commands and functions include private facts and metrics:

• Private facts and metrics do appear in the output of the DESCRIBE SEMANTIC VIEW command. The rows for private facts and metrics have PRIVATE in the access_modifier column.

• Private facts and metrics are listed in the return value of a GET_DDL function call, as noted in 获取语义视图的 SQL 语句.

Some commands and functions include private facts and metrics only under specific conditions:

• Private facts and metrics are listed in the INFORMATION_SCHEMA SEMANTIC_FACTS and SEMANTIC_METRICS views only if you are using a role that has been granted the REFERENCES or OWNERSHIP privilege on the semantic view.

  Otherwise, these views list only the public facts and metrics.

Other commands and functions do not include private facts and metrics:

• 以上任一权限也用于让该视图出现在 SHOW SEMANTIC VIEWS 命令的输出结果中。

• 以上任一权限也用于让该视图出现在 SHOW SEMANTIC VIEWS 命令的输出结果中。

Providing custom instructions for Cortex Analyst¶

In a semantic view, you can provide instructions for Cortex Analyst that explain how to:

• Generate the SQL statement

• Classify questions and prompt for additional information

To provide these custom instructions, use the following clauses:

• For instructions on how to generate the SQL statement, use the AI_SQL_GENERATION clause in the CREATE SEMANTIC VIEW command.

  For example, to tell Cortex Analyst to generate the SQL statement so that all numeric columns are rounded to two decimal points, specify the following:

  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

• For instructions on how to classify questions, use the AI_QUESTION_CATEGORIZATION clause.

  For example, to tell Cortex Analyst to reject questions about users, specify the following:

  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

  You can also provide instructions to ask for more details, if the question isn't clear. For example:

  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