시맨틱 뷰에서 SQL 쿼리를 논리 테이블로 사용하기

시맨틱 뷰에서 SQL 쿼리를 논리 테이블로 사용하기

시맨틱 뷰에서 논리 테이블로 물리적 테이블 대신 SQL 쿼리를 사용할 수 있어요. 여러 테이블을 조인한 결과를 논리 테이블로 만들거나, 기존 뷰 정의와 동일하게 쿼리를 지정할 수 있어요.

출처: Snowflake 문서

본문

시맨틱 뷰에서 논리 테이블로 물리적 테이블 대신 SQL 쿼리를 사용할 수 있어요.

논리 테이블을 SQL 쿼리로 정의하기

SQL 쿼리를 지정하려면 논리 테이블 정의에서 AS 절을 사용해요:

CREATE [ OR REPLACE ] SEMANTIC VIEW [ IF NOT EXISTS ] <name>
  TABLES (
    <table_alias> AS ( <query> )
      ... other keywords for the logical table ...
    [ , ... ]
  )

  ...

참고: 논리 테이블에 SQL 쿼리를 지정하면 논리 테이블의 별칭(alias)이 필수예요. 쿼리에는 세션 변수(session variables)를 사용할 수 없어요. 지정하는 쿼리에는 CREATE VIEW 명령의 AS 절에 지정하는 쿼리와 동일한 제약이 적용돼요.

예를 들어 고객 정보와 고객 주소에 대한 두 테이블이 있다고 가정해 보죠:

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

INSERT INTO customer_address VALUES
  ('cust001', '94027', '300 Main Street'),
  ('cust002', '94030', '600 Main Street');

이 두 테이블을 조인하는 SQL 쿼리에 해당하는 논리 테이블을 정의하려면, 논리 테이블 정의의 AS 절에 SQL 쿼리를 지정하면 돼요:

CREATE OR REPLACE SEMANTIC VIEW my_customer_sv
  TABLES (
    customer_info AS (
      SELECT * FROM customer JOIN customer_address
        ON customer.c_cust_id = customer_address.ca_cust_id
    ) PRIMARY KEY (c_cust_id) WITH SYNONYMS ('customer_details')
      COMMENT = 'Information about customers'
  )
  DIMENSIONS (
    customer_info.first_name AS customer_info.c_first_name,
    customer_info.last_name AS customer_info.c_last_name,
    customer_info.street_address AS customer_info.ca_street_addr,
    customer_info.zip_code AS customer_info.ca_zipcode
  );

다음 문은 이 시맨틱 뷰를 쿼리해요:

SELECT * FROM SEMANTIC_VIEW(
  my_customer_sv
  DIMENSIONS customer_info.first_name, customer_info.last_name,
    customer_info.street_address, customer_info.zip_code
);
+------------+-----------+-----------------+----------+
| FIRST_NAME | LAST_NAME | STREET_ADDRESS  | ZIP_CODE |
|------------+-----------+-----------------+----------|
| Bill       | Wilson    | 600 Main Street | 94030    |
| Mary       | Smith     | 300 Main Street | 94027    |
+------------+-----------+-----------------+----------+

YAML 사양에서 논리 테이블을 SQL 쿼리로 지정하기

시맨틱 뷰용 YAML 사양에서 논리 테이블을 SQL 쿼리로 정의하려면 base_table 아래에 definition name/value 쌍을 지정해요:

name: MY_CUSTOMER_SV
  tables:
  - name: CUSTOMER_INFO
    ...
    base_table:
      definition: SELECT * FROM customer JOIN customer_address ON customer.c_cust_id = customer_address.ca_cust_id

참고: base_table 아래에 definition을 지정하면 database, schema, table은 지정할 수 없어요. 마찬가지로 database, schema, table을 지정하면 definition은 지정할 수 없어요.

DESC SEMANTIC VIEW와 Snowflake 뷰에서 SQL 쿼리 논리 테이블이 나타나는 방식

DESCRIBE SEMANTIC VIEW 명령의 출력에는 논리 테이블(object_kind가 TABLE인 경우)에 대한 DEFINITION 속성이 포함돼요.

  • 논리 테이블이 SQL 쿼리로 설정된 경우, 출력에 DEFINITION 속성이 포함되고 이 값이 SQL 쿼리로 설정돼요. BASE_TABLE_DATABASE_NAME, BASE_TABLE_SCHEMA_NAME, BASE_TABLE_NAME 속성은 출력에 나타나지 않아요.
  • 논리 테이블이 물리적 테이블로 설정된 경우, 출력에 BASE_TABLE_DATABASE_NAME, BASE_TABLE_SCHEMA_NAME, BASE_TABLE_NAME 속성이 포함되고 이 값들이 각각 물리적 테이블을 포함한 데이터베이스, 물리적 테이블을 포함한 스키마, 물리적 테이블의 이름으로 설정돼요. DEFINITION 속성은 출력에 나타나지 않아요.

예를 들어 다음 문은 my_customer_sv 시맨틱 뷰의 속성을 출력해요:

DESC SEMANTIC VIEW my_customer_sv;
+-------------+----------------+---------------+-----------------+--------------------------------------------------------------------------------------------------+
| object_kind | object_name    | parent_entity | property        | property_value                                                                                   |
|-------------+----------------+---------------+-----------------+--------------------------------------------------------------------------------------------------|
| TABLE       | CUSTOMER_INFO  | NULL          | DEFINITION      | SELECT * FROM customer JOIN customer_address ON customer.c_cust_id = customer_address.ca_cust_id |
| TABLE       | CUSTOMER_INFO  | NULL          | SYNONYMS        | ["customer_details"]                                                                             |
| TABLE       | CUSTOMER_INFO  | NULL          | PRIMARY_KEY     | ["C_CUST_ID"]                                                                                    |
| TABLE       | CUSTOMER_INFO  | NULL          | COMMENT         | Information about customers                                                                      |
...

ACCOUNT_USAGE SEMANTIC_TABLES 뷰와 INFO_SCHEMA SEMANTIC_TABLES 뷰에는 컬럼 목록 끝에 definition 컬럼이 포함돼요. definition 컬럼에는 테이블의 SQL 쿼리가 포함돼요:

SELECT semantic_table_name, definition
  FROM SNOWFLAKE.ACCOUNT_USAGE.SEMANTIC_TABLES
  WHERE semantic_view_name = 'MY_CUSTOMER_SV';
+---------------------+--------------------------------------------------------------------------------------------------+
| SEMANTIC_TABLE_NAME | DEFINITION                                                                                       |
|---------------------+--------------------------------------------------------------------------------------------------|
| CUSTOMER_INFO       | SELECT * FROM customer JOIN customer_address ON customer.c_cust_id = customer_address.ca_cust_id |
+---------------------+--------------------------------------------------------------------------------------------------+

SELECT name, definition
  FROM INFORMATION_SCHEMA.SEMANTIC_TABLES
  WHERE semantic_view_name = 'MY_CUSTOMER_SV';
+---------------+--------------------------------------------------------------------------------------------------+
| NAME          | DEFINITION                                                                                       |
|---------------+--------------------------------------------------------------------------------------------------|
| CUSTOMER_INFO | SELECT * FROM customer JOIN customer_address ON customer.c_cust_id = customer_address.ca_cust_id |
+---------------+--------------------------------------------------------------------------------------------------+

더 알아보기 (Learn more)