시맨틱 뷰 쿼리하기
시맨틱 뷰 쿼리하기
시맨틱 뷰를 쿼리하려면 표준 SELECT 문을 사용할 수 있어요. 이 문 안에서 다음 두 방식 중 하나를 사용할 수 있어요.
- FROM 절에서 SEMANTIC_VIEW 절을 지정하기
- FROM 절에서 시맨틱 뷰의 이름을 지정하기
출처: Snowflake 문서
본문
시맨틱 뷰를 쿼리하려면 표준 SELECT 문을 사용할 수 있어요. 이 문 안에서 다음 두 방식 중 하나를 사용할 수 있어요:
- FROM 절에서 SEMANTIC_VIEW 절을 지정해요. 예를 들어:
자세한 내용은 FROM 절에서 SEMANTIC_VIEW 절 지정하기를 참고해요.SELECT * FROM SEMANTIC_VIEW( tpch_analysis DIMENSIONS customer.customer_market_segment METRICS orders.order_average_value ) ORDER BY customer_market_segment; - FROM 절에서 시맨틱 뷰의 이름을 지정해요. 예를 들어:
자세한 내용은 FROM 절에서 시맨틱 뷰 이름 지정하기를 참고해요.SELECT customer_market_segment, AGG(order_average_value) FROM tpch_analysis GROUP BY customer_market_segment ORDER BY customer_market_segment;
시맨틱 뷰를 쿼리하는 데 필요한 권한
시맨틱 뷰를 소유하지 않은 역할을 사용한다면, 그 시맨틱 뷰를 쿼리하려면 해당 시맨틱 뷰에 대한 SELECT 권한이 부여되어야 해요.
참고: 시맨틱 뷰를 쿼리할 때 시맨틱 뷰에 사용된 테이블에 대한 SELECT 권한은 필요하지 않아요. 시맨틱 뷰 자체에 대한 SELECT 권한만 있으면 돼요. 이 동작은 표준 뷰를 쿼리하는 데 필요한 권한과 일치해요.
시맨틱 뷰에 권한을 부여하는 방법은 Granting privileges on semantic views을 참고해요.
FROM 절에서 SEMANTIC_VIEW 절 지정하기
시맨틱 뷰를 쿼리하려면 FROM 절에서 SEMANTIC_VIEW 절을 지정할 수 있어요.
다음 예시는 앞서 정의한 tpch_analysis 시맨틱 뷰에서 customer_market_segment dimension과 order_average_value metric을 선택해요:
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 |
+-------------------------+---------------------+
dimension이나 metric 이름 뒤에 별칭(alias)을 지정해 별칭을 정의할 수 있어요. 별칭 앞에 선택적 키워드 AS를 지정할 수도 있어요. 다음 예시는 같은 쿼리를 실행하지만 결과에 반환되는 dimension과 metric에 segment와 average 별칭을 사용해요.
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 |
+------------+-----------------+
다음 예시는 tpch_analysis 시맨틱 뷰에서 customer_name dimension과 c_customer_order_count fact를 선택해요:
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 |
+--------------------+------------------------+
SEMANTIC_VIEW 절 지정 지침
SEMANTIC_VIEW 절을 지정할 때 다음 지침을 따라 해요:
- SEMANTIC_VIEW 절에서는 다음 절 중 최소한 하나를 지정해야 해요:
- METRICS
- DIMENSIONS
- FACTS 이 절들을 모두 생략할 수는 없어요.
- 이 절들을 조합해 지정할 때 다음을 참고해요:
- 같은 SEMANTIC_VIEW 절에서 FACTS와 METRICS를 함께 지정할 수 없어요.
- FACTS와 DIMENSIONS를 둘 다 지정할 수는 있지만, dimension이 fact를 고유하게 결정할 수 있을 때만 그렇게 해야 해요. 쿼리는 결과를 dimension으로 그룹화해요. fact가 dimension에 의존하지 않으면 결과가 비결정적(non-deterministic)일 수 있어요.
- FACTS와 DIMENSIONS를 둘 다 지정하면, 쿼리에 사용된 모든 fact와 dimension(WHERE 절에 지정된 것 포함)은 같은 논리 테이블에 정의되어야 해요.
- dimension과 metric을 지정하면, dimension의 논리 테이블은 metric의 논리 테이블과 관련되어야 해요. 또한 dimension의 논리 테이블은 metric의 논리 테이블과 같거나 더 낮은 세분성(granularity)이어야 해요.
- 이 기준을 충족하는 dimension을 확인하려면 SHOW SEMANTIC DIMENSIONS FOR METRIC 명령을 실행할 수 있어요. 자세한 내용은 Choosing the dimensions that you can return for a given metric을 참고해요.
- DIMENSIONS 절에서 fact를 참조하는 표현식을 지정할 수 있어요. 마찬가지로 FACTS 절에서 dimension을 참조하는 표현식을 지정할 수 있어요. 예를 들어:
-- Dimension expression that refers to a fact DIMENSIONS my_table.my_fact -- Fact expression that refers to a dimension FACTS my_table.my_dimension - DIMENSIONS와 FACTS를 사용하는 것 사이의 주요 차이점 중 하나는, 쿼리가 DIMENSIONS 절에 지정된 dimension과 표현식으로 결과를 그룹화한다는 점이에요.
- METRICS 절에서 다음을 포함하는 표현식을 지정할 수 있어요:
- metric을 참조하는 스칼라 표현식
- dimension 또는 fact의 집계
- METRICS, DIMENSIONS, FACTS 절을 결과에 나타나기를 원하는 순서로 지정해요. 결과에서 dimension을 먼저 표시하려면 METRICS 전에 DIMENSIONS를 지정해요. 그렇지 않으면 METRICS를 먼저 지정해요.
예를 들어 METRICS 절을 먼저 지정한다고 가정해 보죠:
출력에서 첫 번째 컬럼은 metric 컬럼(SELECT * FROM SEMANTIC_VIEW( tpch_analysis METRICS customer.customer_order_count DIMENSIONS customer.customer_name ) ORDER BY customer_name LIMIT 5;customer_order_count)이고 두 번째 컬럼은 dimension 컬럼(customer_name)이에요:
대신 DIMENSIONS 절을 먼저 지정하면:+----------------------+--------------------+ | CUSTOMER_ORDER_COUNT | CUSTOMER_NAME | |----------------------+--------------------| | 6 | Customer#000000001 | | 7 | Customer#000000002 | | 0 | Customer#000000003 | | 20 | Customer#000000004 | | 4 | Customer#000000005 | +----------------------+--------------------+
출력에서 첫 번째 컬럼은 dimension 컬럼(SELECT * FROM SEMANTIC_VIEW( tpch_analysis DIMENSIONS customer.customer_name METRICS customer.customer_order_count ) ORDER BY customer_name LIMIT 5;customer_name)이고 두 번째 컬럼은 metric 컬럼(customer_order_count)이에요:+--------------------+----------------------+ | CUSTOMER_NAME | CUSTOMER_ORDER_COUNT | |--------------------+----------------------| | Customer#000000001 | 6 | | Customer#000000002 | 7 | | Customer#000000003 | 0 | | Customer#000000004 | 20 | | Customer#000000005 | 4 | +--------------------+----------------------+ - SEMANTIC_VIEW 절이 정의하는 관계는 JOIN, PIVOT, UNPIVOT, GROUP BY, 공통 테이블 표현식(CTEs)을 포함한 다른 SQL 구성에서 사용할 수 있어요.
- 출력 컬럼 헤더는 metric과 dimension의 한정되지 않은(unqualified) 이름을 사용해요.
- 이름이 같은 metric과 dimension이 여러 개 있다면 테이블 별칭을 사용해 컬럼 헤더에 다른 이름을 지정해요. Handling duplicate column names in the output을 참고해요.
- 주어진 논리 테이블의 모든 metric이나 dimension을 반환하려면 논리 테이블 이름으로 한정된 별표(*)를 와일드카드로 사용해요. 예를 들어
customer논리 테이블에 정의된 모든 metric과 dimension을 반환하려면: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 | ...
SEMANTIC_VIEW 절 지정 예시
다음 예시들은 Example of using SQL to create a semantic view에 정의된 tpch_analysis 뷰를 사용해요:
- 메트릭 검색하기
- dimension으로 metric 데이터 그룹화하기
- 다른 구성과 함께 SEMANTIC_VIEW 하위 절 사용하기
- dimension을 사용하는 스칼라 표현식 지정하기
- WHERE 절 지정하기
- WHERE 절에서 fact 지정하기
메트릭 검색하기
다음 문은 metric을 쿼리해 고객의 총 수를 검색해요:
SELECT * FROM SEMANTIC_VIEW(
tpch_analysis
METRICS customer.customer_count
);
+----------------+
| CUSTOMER_COUNT |
+----------------+
| 15000 |
+----------------+
dimension으로 metric 데이터 그룹화하기
다음 문은 metric 데이터(order_average_value)를 dimension(customer_market_segment)으로 그룹화해요:
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 |
+-------------------------+---------------------+
다른 구성과 함께 SEMANTIC_VIEW 하위 절 사용하기
다음 예시는 결과를 필터링, 정렬, 제한하기 위해 SEMANTIC_VIEW 하위 절의 dimension과 metric을 다른 SQL 구성과 함께 사용하는 방법을 보여줘요:
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 |
+--------------------+------------------------------+---------------------+
dimension을 사용하는 스칼라 표현식 지정하기
다음 예시는 DIMENSIONS 절에서 dimension을 참조하는 스칼라 표현식을 사용해요:
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 |
+------+
WHERE 절 지정하기
다음 예시는 DIMENSIONS 절의 dimension을 참조하는 WHERE 절을 지정해요:
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 |
+------------+------------------------------+---------------------+
WHERE 절에서 fact 지정하기
다음 예시는 WHERE 절의 조건에서 region.r_name fact를 사용해요:
SELECT * FROM SEMANTIC_VIEW(
tpch_analysis
FACTS customer.c_customer_order_count
WHERE orders.order_date < '2021-01-01' AND region.r_name = 'AMERICA'
);
FROM 절에서 시맨틱 뷰 이름 지정하기
표준 SQL 뷰를 쿼리할 때처럼, SELECT 문의 FROM 절에서 시맨틱 뷰 이름을 지정할 수 있어요:
SELECT [ DISTINCT ]
{
[<qualifiers>.]<dimension_or_fact> |
<scalar_expression_over_dimension_or_fact> |
AGG( [<qualifiers>.]<metric> ) |
<aggregate_function>( [<qualifiers>.]<dimension_for_fact> )
}
[ , ... ]
FROM <semantic_view> [ AS <alias> ]
[ WHERE <expr_using_dimensions_or_facts> ]
[ GROUP BY <expr_using_dimensions_or_facts> [ , ... ] ]
[ HAVING <expr_using_metrics> ]
[ ORDER BY ... ]
[ LIMIT ... ]
내부적으로 이 문은 SEMANTIC_VIEW 절을 사용하는 SELECT 문으로 다시 작성(rewrite)돼요:
- GROUP BY 절에 지정한 표현식은 SEMANTIC_VIEW 절의 DIMENSIONS 절로 다시 작성돼요. SELECT 문에서 GROUP BY 절에 없는 표현식(예: SELECT 목록의 dimension 표현식)을 사용하면, 그 표현식은 SEMANTIC_VIEW 절의 FACTS 절에서 사용돼요.
- 시맨틱 뷰에 정의된 metric을 참조할 때는 반드시 metric을 AGG 함수에 전달해야 해요.
- dimension이나 fact를 집계 함수에 전달해 애드혹(ad-hoc) metric을 선택할 수 있어요.
- 앞의 두 범주에 속하지 않는 다른 계산 값은 fact 참조로 간주돼요.
다음 섹션에서 이러한 요구 사항을 더 자세히 설명해요:
- SELECT 문에서 dimension과 metric에 대한 요구 사항
- 메트릭 선택하기
- dimension 선택하기
- WHERE 절 지정하기
- HAVING 절 지정하기
- FROM 절에 시맨틱 뷰 이름 지정 시의 제한
SELECT 문에서 dimension과 metric에 대한 요구 사항
FROM 절에 시맨틱 뷰 이름을 지정하면, 계산 이름이 시맨틱 뷰의 모든 엔티티에서 고유하거나 특정 엔티티에 묶이지 않은 파생 메트릭과 LOD 메트릭을 참조할 때 dimension, fact, metric을 한정되지 않은(bare) 이름 또는 엔티티(논리 테이블) 이름으로 한정하는 점 표기법(dot-notation)으로 참조할 수 있어요.
한정되지 않은 이름은 계산 이름이 시맨틱 뷰의 모든 엔티티에서 고유할 때, 또는 특정 엔티티에 묶이지 않은 파생 메트릭과 LOD 메트릭을 참조할 때 동작해요:
SELECT customer_market_segment, AGG(order_average_value)
FROM tpch_analysis
GROUP BY customer_market_segment;
점 표기법(entity.calculation)은 두 개 이상의 엔티티가 같은 이름의 계산을 정의할 때 필요해요. 예를 들어 시맨틱 뷰에 한정되지 않은 이름 name을 공유하는 두 개의 dimension이 있다고 가정해 보죠:
DIMENSIONS (
nation.name AS nation.n_name,
region.name AS region.r_name
);
어느 엔티티의 계산을 원하는지 지정하려면 점 표기법을 사용해요:
SELECT nation.name, region.name
FROM duplicate_names
GROUP BY nation.name, region.name;
모호한 한정되지 않은 이름을 사용하면 쿼리가 오류로 실패해요:
-- Fails: 'name' exists in both the nation and region entities
SELECT name FROM duplicate_names GROUP BY name;
SQL compilation error: Ambiguous column name 'NAME'.
모호하지 않은 계산에도 점 표기법을 사용할 수 있어요. 이 경우 엔티티 한정자는 선택사항이지만 가독성을 높일 수 있어요.
메트릭 선택하기
시맨틱 뷰에 정의된 metric을 선택하려면, 시맨틱 뷰의 metric을 위한 특별한 집계 함수인 AGG 함수에 metric을 전달해야 해요. 예를 들어:
SELECT AGG(order_average_value) FROM tpch_analysis;
참고: AGG 함수는 metric 값 하나를 평가하므로 함수가 metric에 아무 영향도 미치지 않아요.
SELECT 목록에서 metric을 사용하는 표현식을 지정할 수 있어요. 예를 들어:
SELECT AGG(order_average_value) * 10 FROM tpch_analysis;
dimension이나 fact를 집계 함수에 전달해 애드혹 metric을 정의하고 선택할 수도 있어요. 예를 들어:
SELECT COUNT(customer_market_segment) FROM tpch_analysis;
dimension 선택하기
SELECT 목록에 dimension이 포함되면 해당 dimension을 GROUP BY 절에 지정해야 해요. 예를 들어:
SELECT customer_market_segment, customer_nation_name, AGG(order_average_value)
FROM tpch_analysis
GROUP BY customer_market_segment, customer_nation_name;
SELECT 목록과 GROUP BY 절에서 dimension 또는 dimension이나 fact를 사용하는 스칼라 표현식을 지정할 수 있어요. 예를 들어:
SELECT LOWER(customer_nation_name), AGG(order_average_value)
FROM tpch_analysis
GROUP BY customer_nation_name;
WHERE 절 지정하기
WHERE 절에서는 dimension이나 fact를 참조하는 조건 표현식만 사용할 수 있어요. 예를 들어:
SELECT customer_market_segment, AGG(order_average_value)
FROM tpch_analysis
WHERE customer_market_segment = 'BUILDING'
GROUP BY customer_market_segment;
dimension은 쿼리에서 사용된 모든 metric이 도달할 수 있어야 해요.
HAVING 절 지정하기
HAVING 절에서는 metric만 지정할 수 있고, 반드시 Selecting metrics에 나열된 집계 함수 중 하나에 전달해야 해요. 예를 들어:
SELECT customer_market_segment, AGG(order_average_value)
FROM tpch_analysis
GROUP BY customer_market_segment
HAVING AGG(order_average_value) > 142500;
FROM 절에 시맨틱 뷰 이름 지정 시의 제한
SELECT 문에서 다음을 지정할 수 없어요:
- FROM 절의 확장, 다음 포함:
- PIVOT
- UNPIVOT
- MATCH_RECOGNIZE
- LATERAL
- 조인(Joins)
- 윈도우 함수 호출
- QUALIFY
- 상관 서브쿼리(WHERE와 DIMENSIONS 애드혹 표현식의 비상관 서브쿼리는 지원됨. Using subqueries in semantic view queries 참고)
주어진 metric에 대해 반환할 수 있는 dimension 선택하기
반환할 dimension과 metric을 지정할 때, dimension의 기본 테이블은 metric의 기본 테이블과 관련되어야 해요. 또한 dimension의 기본 테이블은 metric의 기본 테이블과 같거나 더 낮은 세분성이어야 해요.
예를 들어 Example of using SQL to create a semantic view에서 만든 tpch_analysis 시맨틱 뷰를 쿼리하고, orders.order_date dimension과 customer.customer_order_count metric을 반환하려 한다고 가정해 보죠:
SELECT * FROM SEMANTIC_VIEW (
tpch_analysis
DIMENSIONS orders.order_date
METRICS customer.customer_order_count
);
이 쿼리는 order_date dimension의 orders 테이블이 customer_order_count metric의 customer 테이블보다 더 높은 세분성이므로 실패해요:
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'.
특정 metric으로 반환할 수 있는 dimension을 나열하려면 SHOW SEMANTIC DIMENSIONS FOR METRIC 명령을 실행해요. 예를 들어:
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 |
+------------+-------------------------+-------------+----------+----------+---------+
출력에서 중복 컬럼 이름 처리하기
시맨틱 뷰에 서로 다른 엔티티에 걸쳐 같은 이름의 계산이 여러 개 있으면, SEMANTIC_VIEW 절과 표준 SQL FROM 절 모두에서 점 표기법(entity.calculation)을 사용해 구분할 수 있어요.
표준 SQL에서 점 표기법 사용하기
예를 들어 nation.name과 region.name dimension이 있는 다음 시맨틱 뷰를 정의했다고 가정해 보죠:
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
);
두 dimension을 모두 선택하려면 점 표기법을 사용해요:
SELECT nation.name, region.name
FROM duplicate_names
GROUP BY nation.name, region.name;
+----------------+-------------+
| NAME | NAME |
+----------------+-------------+
| BRAZIL | AMERICA |
| MOROCCO | AFRICA |
| UNITED KINGDOM | EUROPE |
| IRAN | MIDDLE EAST |
| FRANCE | EUROPE |
| ... | ... |
+----------------+-------------+
출력 컬럼 이름을 바꾸려면 컬럼 별칭을 사용해요:
SELECT nation.name AS nation_name, region.name AS region_name
FROM duplicate_names
GROUP BY nation.name, region.name;
+----------------+-------------+
| NATION_NAME | REGION_NAME |
+----------------+-------------+
| BRAZIL | AMERICA |
| MOROCCO | AFRICA |
| UNITED KINGDOM | EUROPE |
| IRAN | MIDDLE EAST |
| FRANCE | EUROPE |
| ... | ... |
+----------------+-------------+
모호한 이름에 대한 SHOW COLUMNS 동작
시맨틱 뷰에 모호한 계산 이름이 있으면 SHOW COLUMNS는 각 계산에 대해 별도의 행을 내보내요. 모호한 이름의 경우 컬럼 이름은 entity.calcName 규약(예: orders.revenue)으로 보고돼요. 이 동작은 사용자와 다운스트림 도구 모두에게 완전한 카탈로그 가시성을 보장해요.
BI 도구 호환성
SHOW COLUMNS를 사용해 사용 가능한 컬럼을 발견하는 BI 도구는 쿼리를 생성할 때 점 표기법 컬럼 이름을 자동으로 큰따옴표로 감쌀 수 있어요(예: SELECT "orders.revenue" FROM sales_sv). Snowflake는 이러한 따옴표 처리된 식별자를 인식하고 올바른 엔티티와 계산으로 해석하므로, BI 도구 쿼리는 추가 구성 없이 동작해요.
윈도우 함수 메트릭 정의 및 쿼리하기
윈도우 함수를 호출하고 집계 값을 전달하는 메트릭을 정의할 수 있어요. 이러한 메트릭을 윈도우 함수 메트릭이라고 해요.
다음 예시들은 윈도우 함수 메트릭과 행 수준 표현식을 윈도우 함수에 전달하는 메트릭의 차이를 보여줘요:
- 다음 메트릭은 윈도우 함수 메트릭이에요:
이 예시에서 SUM 윈도우 함수는 다른 metric(METRICS ( table_1.metric_1 AS SUM(table_1.metric_3) OVER( ... ) )table_1.metric_3)을 인자로 받아요. - 다음 메트릭도 윈도우 함수 메트릭이에요:
이 예시에서 SUM 윈도우 함수는 유효한 metric 표현식(METRICS ( table_1.metric_2 AS SUM( SUM(table_1.column_1) ) OVER( ... ) )SUM(table_1.column_1))을 인자로 받아요. - 다음 메트릭은 윈도우 함수 메트릭이 아니에요:
이 예시에서 SUM 윈도우 함수는 컬럼(METRICS ( table_1.metric_1 AS SUM( SUM(table_1.column_1) OVER( ... ) ) )table_1.column_1)을 인자로 받고, 그 윈도우 함수 호출의 결과가 별도의 SUM 집계 함수 호출에 전달돼요.
다음 섹션들은 윈도우 함수 메트릭을 정의하고 쿼리하는 방법을 설명해요:
윈도우 함수 메트릭 정의하기
윈도우 함수 호출을 지정할 때는 이 구문을 사용해요. 이 구문은 Parameters for window function metrics에서 설명해요.
다음 예시는 여러 윈도우 함수 메트릭의 정의를 포함하는 시맨틱 뷰를 만들어요. 이 예시는 TPC-DS 샘플 데이터베이스의 테이블을 사용해요. 이 데이터베이스에 접근하는 방법은 Add the TPC-DS data set to your account를 참고해요.
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
)
참고: 윈도우 함수 메트릭은 행 수준 계산(fact와 dimension)이나 다른 metric의 정의에 사용할 수 없어요.
윈도우 함수 메트릭 쿼리하기
시맨틱 뷰를 쿼리하고 쿼리가 윈도우 함수 메트릭을 반환하면, 시맨틱 뷰의 CREATE SEMANTIC VIEW 문에서 PARTITION BY *dimension*, PARTITION BY EXCLUDING *dimension*, ORDER BY *dimension*에 지정된 dimension도 반환해야 해요.
예를 들어 store_sales.avg_7_days_sales_quantity metric의 정의에서 PARTITION BY EXCLUDING과 ORDER BY 절에 date.date와 date.year dimension을 지정한다고 가정해 보죠:
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'),
...
);
쿼리에서 store_sales.avg_7_days_sales_quantity metric을 반환하면 date.date와 date.year dimension도 반환해야 해요:
SELECT * FROM SEMANTIC_VIEW (
sv_window_function_example
DIMENSIONS date.date, date.year
METRICS store_sales.avg_7_days_sales_quantity
);
date.date와 date.year dimension을 생략하면 오류가 발생해요:
010260 (42601): SQL compilation error:
Invalid semantic view query: Dimension 'DATE.DATE' used in a
window function metric must be requested in the query.
쿼리에서 지정해야 하는 dimension을 확인하려면 SHOW SEMANTIC DIMENSIONS FOR METRIC 명령을 실행해요. 예를 들어 store_sales.avg_7_days_sales_quantity metric을 검색할 때 지정해야 하는 dimension을 확인하려면:
SHOW SEMANTIC DIMENSIONS IN sv_window_function_example FOR METRIC avg_7_days_sales_quantity;
명령 출력에서 required 컬럼이 쿼리에서 지정해야 하는 dimension에 대해 true를 포함해요.
+------------+-----------+--------------+----------+----------+---------+
| 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 |
+------------+-----------+--------------+----------+----------+---------+
다음 추가 예시들은 윈도우 함수 메트릭 정의하기에 정의된 윈도우 함수 메트릭을 쿼리해요. DIMENSIONS 절에 metric 정의의 PARTITION BY EXCLUDING과 ORDER BY 절에 지정된 dimension이 포함되어 있음을 참고해요.
다음 예시는 30일 전 판매 수량을 반환해요:
SELECT * FROM SEMANTIC_VIEW (
sv_window_function_example
DIMENSIONS date.date, date.year
METRICS store_sales.total_sales_quantity_30_days_ago
);
다음 예시는 30일 전 총 판매 수량의 7일 이동 평균을 반환해요:
SELECT * FROM SEMANTIC_VIEW (
sv_window_function_example
DIMENSIONS date.date, date.year
METRICS store_sales.avg_7_days_sales_quantity_30_days_ago
);
시맨틱 뷰 쿼리에서 서브쿼리 사용하기
비상관(uncorrelated) 서브쿼리를 사용해, 다른 시맨틱 뷰를 포함한 모든 Snowflake 객체에 대한 독립 쿼리로 계산된 값을 사용해 시맨틱 뷰 결과를 필터링하거나 범주화할 수 있어요.
다음 표는 서브쿼리 지원을 요약해요:
| WHERE 절 | DIMENSIONS 애드혹 표현식 | |
|---|---|---|
| 비상관(Uncorrelated) | 지원됨 | 지원됨 |
| 상관(Correlated) | 미지원 | 미지원 |
SEMANTIC_VIEW 절과 FROM 절에 시맨틱 뷰 이름을 지정하는 방식 모두 서브쿼리를 지원해요.
WHERE 절의 서브쿼리
WHERE 절의 서브쿼리는 집계 전에 시맨틱 뷰 결과의 행을 필터링해요. 모든 표준 Snowflake 서브쿼리 연산자가 지원돼요:[NOT] IN, [NOT] EXISTS, ALL, ANY, 그리고 비교 컨텍스트(=, >, < 등)의 스칼라 서브쿼리.
다음 예시들은 다음 기본 테이블과 시맨틱 뷰를 가정해요:
CREATE OR REPLACE TABLE t_orders(order_id INT, cust_id INT, amount NUMBER);
INSERT INTO t_orders VALUES (1, 101, 100), (2, 102, 200), (3, 103, 300), (4, 101, 120);
CREATE OR REPLACE TABLE t_customers(cust_id INT, region VARCHAR);
INSERT INTO t_customers VALUES (100, 'NORTH'), (100, 'SOUTH'), (101, 'EAST'), (102, 'WEST');
CREATE OR REPLACE SEMANTIC VIEW sv_orders
TABLES (ord AS t_orders PRIMARY KEY (order_id))
FACTS (ord.f_amount AS amount)
DIMENSIONS (ord.d_cust_id AS cust_id, ord.d_ord_id AS order_id)
METRICS (ord.m_total AS SUM(amount));
CREATE OR REPLACE SEMANTIC VIEW sv_customers
TABLES (cust AS t_customers PRIMARY KEY (cust_id))
FACTS (cust.f_cust_id AS cust_id)
DIMENSIONS (cust.d_region AS region, cust.d_cust_id AS cust_id);
다음 예시들은 SEMANTIC_VIEW 절을 사용해요:
-- Filter to customers that appear in t_customers
SELECT * FROM SEMANTIC_VIEW(
sv_orders
DIMENSIONS ord.d_cust_id
METRICS ord.m_total
WHERE ord.d_cust_id IN (SELECT cust_id FROM t_customers));
-- Exclude customers from a specific region
SELECT * FROM SEMANTIC_VIEW(
sv_orders
DIMENSIONS ord.d_cust_id
WHERE d_cust_id NOT IN (SELECT cust_id FROM t_customers WHERE region = 'WEST'));
-- Scalar subquery comparison
SELECT * FROM SEMANTIC_VIEW(
sv_orders
DIMENSIONS ord.d_cust_id
WHERE d_cust_id = (SELECT MIN(cust_id) FROM t_customers));
FROM 절에 시맨틱 뷰 이름을 지정하면 WHERE 절에서도 같은 서브쿼리 연산자가 지원돼요:
SELECT d_cust_id, AGG(m_total)
FROM sv_orders
WHERE d_cust_id IN (SELECT cust_id FROM t_customers)
GROUP BY d_cust_id;
다른 시맨틱 뷰에 대한 서브쿼리도 사용할 수 있어요:
SELECT * FROM SEMANTIC_VIEW(
sv_orders
DIMENSIONS ord.d_cust_id
METRICS ord.m_total
WHERE ord.d_cust_id IN (
SELECT * FROM SEMANTIC_VIEW(
sv_customers
DIMENSIONS cust.d_cust_id
WHERE cust.d_region = 'EAST')));
DIMENSIONS 애드혹 표현식의 서브쿼리
DIMENSIONS 애드혹 표현식의 서브쿼리는 계산된 dimension을 만들어요. 서브쿼리는 최소한 하나의 시맨틱 뷰 계산을 참조하는 표현식의 일부로 나타나야 해요. 시맨틱 뷰 컬럼 참조가 없는 독립형 서브쿼리는 유효하지 않아요.
-- Group rows by whether cust_id appears in the subquery result (TRUE/FALSE)
SELECT * FROM SEMANTIC_VIEW(
sv_orders
DIMENSIONS ord.d_cust_id IN (SELECT cust_id FROM t_customers)
METRICS ord.m_total);
-- CASE expression using a subquery
SELECT * FROM SEMANTIC_VIEW(
sv_orders
DIMENSIONS CASE WHEN d_cust_id IN (SELECT cust_id FROM t_customers)
THEN d_cust_id ELSE -1 END
METRICS m_total);
-- Scalar arithmetic with a scalar subquery
SELECT * FROM SEMANTIC_VIEW(
sv_orders
DIMENSIONS ord.d_cust_id + (SELECT MIN(cust_id) FROM t_customers)
METRICS ord.m_total);
FROM 절에 시맨틱 뷰 이름을 지정하면, 서브쿼리 표현식은 SELECT 목록에 들어가고 GROUP BY에 포함되어야 해요. 서수(ordinal), 별칭, 또는 전체 표현식을 사용할 수 있어요:
-- GROUP BY alias
SELECT d_cust_id IN (SELECT cust_id FROM t_customers) AS is_known_cust,
AGG(m_total)
FROM sv_orders
GROUP BY is_known_cust;
-- GROUP BY ordinal
SELECT d_cust_id IN (SELECT cust_id FROM t_customers), AGG(m_total)
FROM sv_orders
GROUP BY 1;
서브쿼리 제한
- 상관 서브쿼리는 지원되지 않아요. 서브쿼리 내부에서 외부 시맨틱 뷰 쿼리의 계산을 참조하는 것은 거부돼요:
-- Invalid: references d_cust_id from the outer query SELECT * FROM SEMANTIC_VIEW( sv_orders DIMENSIONS ord.d_cust_id METRICS ord.m_total WHERE ord.d_cust_id IN ( SELECT cust_id FROM t_customers WHERE cust_id = d_cust_id)); -- Error: Unsupported feature 'CORRELATED SUBQUERIES'. - FACTS 또는 METRICS 애드혹 표현식에서는 서브쿼리가 지원되지 않아요:
-- Invalid: subquery in a FACTS expression SELECT * FROM SEMANTIC_VIEW( sv_orders FACTS f_amount * (SELECT MAX(amount) FROM t_orders)); -- Error: Subqueries are not allowed in fact expressions -- Invalid: subquery in a METRICS expression SELECT * FROM SEMANTIC_VIEW( sv_orders METRICS m_total / (SELECT COUNT(*) FROM t_customers)); -- Error: Subqueries are not allowed in metric expressions - 독립형 서브쿼리는 유효한 DIMENSIONS 표현식이 아니에요. 서브쿼리는 시맨틱 뷰 계산을 참조하는 표현식의 일부로 나타나야 해요:
-- Invalid: no semantic view calculation referenced SELECT * FROM SEMANTIC_VIEW( sv_orders DIMENSIONS (SELECT cust_id FROM t_customers) METRICS ord.m_total); -- Error: Invalid expression in dimension expression. -- Missing reference to a row-level expression. - 서브쿼리 자체가 SEMANTIC_VIEW() 쿼리라면 모든 표준 시맨틱 뷰 규칙이 적용돼요, WHERE 절에서 집계 함수를 허용하지 않는 제한을 포함해요.
참고: Cortex Analyst는 서브쿼리를 사용하는 시맨틱 SQL 쿼리를 생성하지 않아요. 서브쿼리는 수동으로 작성한 쿼리에서만 사용할 수 있어요.