시맨틱 뷰에서 논리 테이블용 필터 정의하기

시맨틱 뷰에서 논리 테이블용 필터 정의하기

시맨틱 뷰에서는 논리 테이블에 대한 필터를 정의할 수 있어요. 필터(filter) 는 쿼리의 WHERE 절에서 조건으로도 사용할 수 있는 fact 또는 dimension이에요. 해당 fact 또는 dimension은 BOOLEAN 값으로 해석되어야 해요. Cortex Analyst는 SQL 쿼리를 만들 때 이 필터를 활용할 수 있어요.

출처: Snowflake 문서

본문

시맨틱 뷰에서는 논리 테이블에 대한 필터를 정의할 수 있어요. 필터는 쿼리의 WHERE 절에서 조건으로도 사용할 수 있는 fact 또는 dimension이에요. 이 fact 또는 dimension은 BOOLEAN 값으로 해석되어야 해요.

Cortex Analyst는 SQL 쿼리를 생성할 때 이 필터를 사용할 수 있어요.

필터 정의하기

CREATE SEMANTIC VIEW 명령의 DIMENSIONS 또는 FACTS 절에서 필터를 정의해요. 필터를 정의할 때 dimension 또는 fact가 필터이기도 하다는 것을 나타내기 위해 LABELS = (FILTER)를 지정해요:

CREATE [ OR REPLACE ] SEMANTIC VIEW [ IF NOT EXISTS ] <name>
  ...
  FACTS (
    <table_alias>.<filter> LABELS = (FILTER) AS <sql_expr>
    [ , ... ]
  )
  DIMENSIONS (
    <table_alias>.<filter> LABELS = (FILTER) AS <sql_expr>
    [ , ... ]
  )
  ...

여기서 *sql_expr*는 필터의 조건 표현식이에요.

예를 들어 고객과 주문에 대한 정보가 들어 있는 다음 테이블들에 대한 시맨틱 뷰를 만들고 싶다고 가정해 보죠:

CREATE OR REPLACE TABLE customers(
  customer_id NUMBER,
  name VARCHAR,
  loyalty_points NUMBER,
  status VARCHAR,
  region VARCHAR,
  is_active BOOLEAN,
  signup_date DATE
);

INSERT INTO customers VALUES
  (1, 'Alice', 150, 'ACTIVE', 'US', TRUE, '2023-01-15'),
  (2, 'Bob', 50, 'INACTIVE', 'EU', FALSE, '2022-06-20'),
  (3, 'Charlie', 200, 'ACTIVE', 'US', TRUE, '2023-03-10'),
  (4, 'Dave', 180, 'INACTIVE', 'US', FALSE, '2022-08-01');

CREATE OR REPLACE TABLE orders(
  order_id NUMBER,
  customer_id NUMBER,
  order_date DATE,
  total_amount NUMBER,
  is_completed BOOLEAN,
  is_refunded BOOLEAN
);

INSERT INTO orders VALUES
  (101, 1, '2024-01-01', 100, TRUE, FALSE),
  (102, 1, '2024-02-15', 200, TRUE, FALSE),
  (103, 2, '2024-02-20', 50, FALSE, TRUE),
  (104, 3, '2024-03-01', 200, TRUE, FALSE),
  (105, 4, '2024-06-05', 1200, TRUE, FALSE);

뷰를 쿼리하고 다음 기준에 따라 결과를 필터링할 수 있도록 시맨틱 뷰를 구성하고 싶다고 가정해 보죠:

  • 완료된 주문
  • 고액 주문($100 초과 주문)
  • 충성도 포인트가 150 이상이고 $1000 초과 주문을 한 중요 고객
  • 활성 고객
  • 미국 고객

시맨틱 뷰를 만들 때 이 기준들에 대한 필터를 정의할 수 있어요:

CREATE OR REPLACE SEMANTIC VIEW my_sv_orders
  TABLES (
    customers PRIMARY KEY (customer_id),
    orders PRIMARY KEY (order_id)
  )
  RELATIONSHIPS (
    orders(customer_id) REFERENCES customers
  )
  FACTS (
    orders.completed_only LABELS = (FILTER) AS orders.is_completed,
    orders.high_value_order LABELS = (FILTER) AS orders.total_amount > 100,
    customers.vip_customer LABELS = (FILTER) AS
      customers.loyalty_points >= 150 AND SUM(orders.order_amount) > 1000,
    orders.order_amount AS orders.total_amount
  )
  DIMENSIONS (
    customers.customer_name AS customers.name,
    customers.active_only LABELS = (FILTER) AS customers.status = 'ACTIVE',
    customers.region_us LABELS = (FILTER) AS customers.region IN ('US'),
    orders.order_date AS orders.order_date
  )
  METRICS (
    orders.total_revenue AS SUM(orders.total_amount),
    orders.order_count AS COUNT(*)
  );

위 예시에서 LABELS = (FILTER)는 해당 항목이 필터로도 사용할 수 있는 fact 또는 dimension임을 나타내요. 이 예시는 다음 필터를 정의해요:

  • fact인 필터:
    • orders.completed_only — 주문이 완료되면 TRUE예요.
    • orders.high_value_order — 금액이 $100보다 크면 TRUE예요.
    • customers.vip_customer — 고객의 충성도 포인트가 150 이상이고 $1000 초과 주문을 했으면 TRUE예요.
  • dimension인 필터:
    • customers.active_only — 고객이 활성 상태면 TRUE예요.
    • customers.region_us — 고객이 미국에 있으면 TRUE예요.

쿼리에서 필터 지정하기

시맨틱 뷰를 쿼리할 때 WHERE 절의 조건으로 필터 이름을 지정할 수 있어요. 예를 들어 완료된 주문의 행을 반환하려면 WHERE 절에서 orders.completed_only 필터를 지정해요. 다음 예시는 SEMANTIC_VIEW 절을 사용해요:

SELECT * FROM SEMANTIC_VIEW(
  my_sv_orders
  DIMENSIONS orders.order_date
  METRICS orders.total_revenue
  WHERE orders.completed_only
);
+------------+---------------+
| ORDER_DATE | TOTAL_REVENUE |
|------------+---------------|
| 2024-01-01 |           100 |
| 2024-03-01 |           200 |
| 2024-02-15 |           200 |
| 2024-06-05 |          1200 |
+------------+---------------+

다음 예시는 SELECT 문의 표준 절을 사용해 뷰를 쿼리해요:

SELECT
    order_date,
    AGG(total_revenue)
  FROM my_sv_orders
  WHERE completed_only
  GROUP BY order_date
  ORDER BY order_date;
+------------+--------------------+
| ORDER_DATE | AGG(TOTAL_REVENUE) |
|------------+--------------------|
| 2024-01-01 |                100 |
| 2024-02-15 |                200 |
| 2024-03-01 |                200 |
| 2024-06-05 |               1200 |
+------------+--------------------+

이 필터들은 표준 논리 연산자(AND, OR, NOT) 및 다른 기준과 함께 사용할 수 있어요. 예를 들어:

SELECT * FROM SEMANTIC_VIEW(
  my_sv_orders
  DIMENSIONS customers.customer_name
  METRICS orders.total_revenue
  WHERE (customers.active_only OR customers.region_us)
    AND orders.completed_only
);

SELECT * FROM SEMANTIC_VIEW(
    my_sv_orders
    DIMENSIONS customers.customer_name, orders.high_value_order, orders.order_date
  )
  orders WHERE orders.high_value_order AND orders.order_date >= '2024-02-01';

필터에 대한 정보 보기

DESCRIBE SEMANTIC VIEW, SHOW SEMANTIC FACTS, SHOW SEMANTIC DIMENSIONS 명령을 실행하면 출력에 필터에 대한 정보가 포함돼요:

  • 필터인 fact와 dimension에 대해 DESC SEMANTIC VIEW 명령의 출력에는 LABELS 속성이 포함되고, 이 값은 ["filter"]로 설정돼요. EXPRESSION 속성은 필터의 SQL 표현식으로 설정돼요.
DESC SEMANTIC VIEW my_sv_orders;

+--------------+-------------------------------------------------------+---------------+--------------------------+---------------------------------------------------------------------+
| object_kind  | object_name                                           | parent_entity | property                 | property_value                                                      |
|--------------+-------------------------------------------------------+---------------+--------------------------+---------------------------------------------------------------------|
...
| DIMENSION    | ACTIVE_ONLY                                           | CUSTOMERS     | TABLE                    | CUSTOMERS                                                           |
| DIMENSION    | ACTIVE_ONLY                                           | CUSTOMERS     | EXPRESSION               | customers.status = 'ACTIVE'                                         |
| DIMENSION    | ACTIVE_ONLY                                           | CUSTOMERS     | DATA_TYPE                | BOOLEAN                                                             |
| DIMENSION    | ACTIVE_ONLY                                           | CUSTOMERS     | ACCESS_MODIFIER          | PUBLIC                                                              |
| DIMENSION    | ACTIVE_ONLY                                           | CUSTOMERS     | LABELS                   | ["filter"]                                                          |
...
| FACT         | VIP_CUSTOMER                                          | CUSTOMERS     | TABLE                    | CUSTOMERS                                                           |
| FACT         | VIP_CUSTOMER                                          | CUSTOMERS     | EXPRESSION               | customers.loyalty_points >= 150 AND SUM(orders.order_amount) > 1000 |
| FACT         | VIP_CUSTOMER                                          | CUSTOMERS     | DATA_TYPE                | BOOLEAN                                                             |
| FACT         | VIP_CUSTOMER                                          | CUSTOMERS     | ACCESS_MODIFIER          | PUBLIC                                                              |
| FACT         | VIP_CUSTOMER                                          | CUSTOMERS     | LABELS                   | ["filter"]                                                          |
...
+--------------+-------------------------------------------------------+---------------+--------------------------+---------------------------------------------------------------------+
  • 필터인 fact와 dimension에 대해 SHOW SEMANTIC FACTS와 SHOW SEMANTIC DIMENSIONS 명령의 출력에는 labels 컬럼이 포함되고, 이 값은 ["filter"]예요.
SHOW SEMANTIC FACTS IN my_sv_orders;

+---------------+-------------+--------------------+------------+------------------+--------------+----------+---------+------------+
| database_name | schema_name | semantic_view_name | table_name | name             | data_type    | synonyms | comment | labels     |
|---------------+-------------+--------------------+------------+------------------+--------------+----------+---------+------------|
| MY_DB         | MY_SCHEMA   | MY_SV_ORDERS       | CUSTOMERS  | VIP_CUSTOMER     | BOOLEAN      | NULL     | NULL    | ["filter"] |
| MY_DB         | MY_SCHEMA   | MY_SV_ORDERS       | ORDERS     | COMPLETED_ONLY   | BOOLEAN      | NULL     | NULL    | ["filter"] |
| MY_DB         | MY_SCHEMA   | MY_SV_ORDERS       | ORDERS     | HIGH_VALUE_ORDER | BOOLEAN      | NULL     | NULL    | ["filter"] |
| MY_DB         | MY_SCHEMA   | MY_SV_ORDERS       | ORDERS     | ORDER_AMOUNT     | NUMBER(38,0) | NULL     | NULL    | NULL       |
+---------------+-------------+--------------------+------------+------------------+--------------+----------+---------+------------+

SHOW SEMANTIC DIMENSIONS IN my_sv_orders;

+---------------+-------------+--------------------+------------+---------------+--------------------+----------+---------+------------+
| database_name | schema_name | semantic_view_name | table_name | name          | data_type          | synonyms | comment | labels     |
|---------------+-------------+--------------------+------------+---------------+--------------------+----------+---------+------------|
| MY_DB         | MY_SCHEMA   | MY_SV_ORDERS       | CUSTOMERS  | ACTIVE_ONLY   | BOOLEAN            | NULL     | NULL    | ["filter"] |
| MY_DB         | MY_SCHEMA   | MY_SV_ORDERS       | CUSTOMERS  | CUSTOMER_NAME | VARCHAR(134217728) | NULL     | NULL    | NULL       |
| MY_DB         | MY_SCHEMA   | MY_SV_ORDERS       | CUSTOMERS  | REGION_US     | BOOLEAN            | NULL     | NULL    | ["filter"] |
| MY_DB         | MY_SCHEMA   | MY_SV_ORDERS       | ORDERS     | ORDER_DATE    | DATE               | NULL     | NULL    | NULL       |
+---------------+-------------+--------------------+------------+---------------+--------------------+----------+---------+------------+

또한 INFORMATION_SCHEMA의 SEMANTIC_DIMENSIONS 와 SEMANTIC_FACTS 뷰에는 labels라는 추가 VARIANT 컬럼이 있어요. 이 컬럼은 fact 또는 dimension이 필터이면 "filter"를 포함해요. 예를 들어:

SELECT * FROM INFORMATION_SCHEMA.SEMANTIC_DIMENSIONS WHERE semantic_view_name = 'MY_SV_ORDERS';
+-----------------------+----------------------+--------------------+------------+---------------+--------------------+-----------------------------+----------+---------+-------------------------------------+-----------------------------------+----------------------------+-----------------------------------+------------+
| SEMANTIC_VIEW_CATALOG | SEMANTIC_VIEW_SCHEMA | SEMANTIC_VIEW_NAME | TABLE_NAME | NAME          | DATA_TYPE          | EXPRESSION                  | SYNONYMS | COMMENT | CORTEX_SEARCH_SERVICE_DATABASE_NAME | CORTEX_SEARCH_SERVICE_SCHEMA_NAME | CORTEX_SEARCH_SERVICE_NAME | CORTEX_SEARCH_SERVICE_COLUMN_NAME | LABELS     |
|-----------------------+----------------------+--------------------+------------+---------------+--------------------+-----------------------------+----------+---------+-------------------------------------+-----------------------------------+----------------------------+-----------------------------------+------------|
| MY_DB                 | MY_SCHEMA            | MY_SV_ORDERS       | CUSTOMERS  | ACTIVE_ONLY   | BOOLEAN            | customers.status = 'ACTIVE' | NULL     | NULL    | NULL                                | NULL                              | NULL                       | NULL                              | [          |
|                       |                      |                    |            |               |                    |                             |          |         |                                     |                                   |                            |                                   |   "filter" |
|                       |                      |                    |            |               |                    |                             |          |         |                                     |                                   |                            |                                   | ]          |
...
+-----------------------+----------------------+--------------------+------------+---------------+--------------------+-----------------------------+----------+---------+-------------------------------------+-----------------------------------+----------------------------+-----------------------------------+------------+

더 알아보기 (Learn more)