SQL을 사용해 시맨틱 뷰 생성 예시
SQL을 사용해 시맨틱 뷰 생성 예시
다음은 시맨틱 뷰 를 생성하는 완전한 예시입니다.
이 예시는 Snowflake에서 사용할 수 있는 TPC-H 샘플 데이터 를 사용합니다. 이 데이터셋은 고객, 주문, 라인 아이템이 있는 단순화된 비즈니스 시나리오를 나타내는 테이블을 포함합니다.
출처: Snowflake 문서
본문
시맨틱 뷰 생성
다음 문은 시맨틱 뷰를 생성합니다.
CREATE OR REPLACE SEMANTIC VIEW tpch_analysis
TABLES (
region AS SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.REGION PRIMARY KEY (r_regionkey),
nation AS SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.NATION PRIMARY KEY (n_nationkey),
customer AS SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER PRIMARY KEY (c_custkey),
orders AS SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS PRIMARY KEY (o_orderkey),
lineitem AS SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.LINEITEM PRIMARY KEY (l_orderkey, l_linenumber),
supplier AS SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.SUPPLIER PRIMARY KEY (s_suppkey)
)
RELATIONSHIPS (
nation (n_regionkey) REFERENCES region,
customer (c_nationkey) REFERENCES nation,
orders (o_custkey) REFERENCES customer,
lineitem (l_orderkey) REFERENCES orders,
supplier (s_nationkey) REFERENCES nation
)
FACTS (
region.r_name AS r_name,
nation.n_name AS n_name,
orders.o_orderkey AS o_orderkey,
customer.c_customer_order_count AS COUNT(orders.o_orderkey),
lineitem.line_item_id AS CONCAT(l_orderkey, '-', l_linenumber),
orders.count_line_items AS COUNT(lineitem.line_item_id)
)
DIMENSIONS (
nation.nation_name AS n_name,
customer.customer_name AS c_name,
customer.customer_region_name AS region.r_name,
customer.customer_nation_name AS nation.n_name,
customer.customer_market_segment AS c_mktsegment,
customer.customer_country_code AS LEFT(c_phone, 2),
orders.order_date AS orders.o_orderdate
)
METRICS (
customer.customer_count AS COUNT(c_custkey),
customer.customer_order_count AS SUM(c_customer_order_count),
orders.order_count AS COUNT(o_orderkey),
orders.order_average_value AS AVG(orders.o_totalprice),
orders.average_line_items_per_order AS AVG(orders.count_line_items),
supplier.supplier_count AS COUNT(s_suppkey)
)
;