테이블 설계 고려 사항¶
이 항목에서는 테이블을 설계하고 관리할 때의 모범 사례, 일반 지침 및 중요한 고려 사항을 설명합니다.
이 항목의 내용:
열의 날짜/시간 데이터 타입¶
날짜 또는 타임스탬프가 포함된 열을 정의하는 경우에는 문자 데이터 타입이 아닌 날짜 또는 타임스탬프 데이터 타입 을 선택하는 것이 좋습니다. Snowflake에서는 VARCHAR 데이터에 비해 DATE 및 TIMESTAMP 데이터의 저장 효율성이 높으므로, 쿼리 성능이 향상됩니다. 필요한 세분성 수준에 따라 적합한 날짜 또는 타임스탬프 데이터 타입을 선택합니다.
참조 무결성 제약 조건¶
Snowflake의 참조 무결성 제약 조건은 정보를 제공하기 위한 용도이며 NOT NULL 이외에는 적용되지 않습니다. NOT NULL 이외의 제약 조건은 비활성화된 상태로 생성됩니다.
그러나 제약 조건은 중요한 메타데이터를 제공합니다. 기본 키와 외래 키를 사용하면 프로젝트 팀의 구성원이 스키마 디자인에 익숙해질 수 있으며 테이블의 상관 관계를 이해할 수 있습니다.
또한, 대부분의 비즈니스 인텔리전스(BI) 및 시각화 도구는 테이블과 함께 외래 키 정의를 가져와 올바른 조인 조건을 빌드합니다. 이러한 접근 방식을 시간을 절약할 수 있으며 나중에 다른 사용자가 테이블의 조인 방법을 추측한 후 도구를 수동으로 구성하는 방법에 비해 오류가 발생할 가능성이 감소합니다. 다른 개발자가 조인을 해석할 필요가 없으므로, 기본 및 외래 키를 기반으로 하는 기준 조인을 사용하면 설계의 무결성을 보장할 수 있습니다. 또한, 일부 BI 및 시각화 도구는 제약 정보를 활용하여 쿼리를 보다 효율적인 형식(예: 조인 제거)으로 다시 작성합니다.
CREATE | ALTER TABLE … CONSTRAINT 명령을 사용하여 테이블을 생성 또는 수정할 때 제약 조건을 지정합니다.
다음 예에서, 두 번째 테이블(salesorders)에 대한 CREATE TABLE 문은 첫 번째 테이블(salespeople)의 열을 참조하는 외래 키 제약 조건을 정의합니다.
create or replace table salespeople (
sp_id int not null unique,
name varchar default null,
region varchar,
constraint pk_sp_id primary key (sp_id)
);
create or replace table salesorders (
order_id int not null unique,
quantity int default null,
description varchar,
sp_id int not null unique,
constraint pk_order_id primary key (order_id),
constraint fk_sp_id foreign key (sp_id)
references salespeople(sp_id)
);
GET_DDL 함수를 쿼리하여 지정된 테이블을 다시 생성하기 위해 실행할 수 있는 DDL 문을 검색합니다. 이 문에는 현재 테이블에 설정된 제약 조건이 포함되어 있습니다.
예:
select get_ddl('table', 'mydb.public.salesorders');
+-----------------------------------------------------------------------------------------------------+
| GET_DDL('TABLE', 'MYDATABASE.PUBLIC.SALESORDERS') |
|-----------------------------------------------------------------------------------------------------|
| create or replace TABLE SALESORDERS ( |
| ORDER_ID NUMBER(38,0) NOT NULL, |
| QUANTITY NUMBER(38,0), |
| DESCRIPTION VARCHAR(16777216), |
| SP_ID NUMBER(38,0) NOT NULL, |
| unique (SP_ID), |
| constraint PK_ORDER_ID primary key (ORDER_ID), |
| constraint FK_SP_ID foreign key (SP_ID) references MYDATABASE.PUBLIC.SALESPEOPLE(SP_ID) |
| ); |
+-----------------------------------------------------------------------------------------------------+
아니면, Information Schema에서 TABLE_CONSTRAINTS 뷰 뷰를 쿼리하여 스키마를 기준으로(또는 데이터베이스의 모든 스키마에서) 모든 테이블 제약 조건 목록을 검색합니다.
예:
select table_name, constraint_type, constraint_name
from mydb.information_schema.table_constraints
where constraint_schema = 'PUBLIC'
Order by table_name;
+-------------+-----------------+-----------------------------------------------------+
| TABLE_NAME | CONSTRAINT_TYPE | CONSTRAINT_NAME |
|-------------+-----------------+-----------------------------------------------------|
| SALESORDERS | UNIQUE | SYS_CONSTRAINT_fce2257e-c343-4e66-9bea-fc1c041b00a6 |
| SALESORDERS | FOREIGN KEY | FK_SP_ID |
| SALESORDERS | PRIMARY KEY | PK_ORDER_ID |
| SALESORDERS | UNIQUE | SYS_CONSTRAINT_bf90e2b3-fd4a-4764-9576-88fb487fe989 |
| SALESPEOPLE | PRIMARY KEY | PK_SP_ID |
+-------------+-----------------+-----------------------------------------------------+
클러스터링 키를 설정해야 하는 경우¶
대부분의 테이블에서는 클러스터링 키 를 지정할 필요가 없습니다. 최적화 엔진과 마이크로 파티셔닝을 통해 Snowflake가 자동 튜닝을 수행합니다. 대부분의 경우, 데이터는 날짜 또는 타임스탬프를 기준으로 마이크로 파티션으로 로드 및 구성되며 동일한 차원을 따라 쿼리됩니다.
언제 테이블에 대한 클러스터링 키를 지정해야 합니까? 우선, 소규모 테이블을 클러스터링하는 경우에는 일반적으로 쿼리 성능이 크게 향상되지 않습니다.
더 큰 규모의 데이터 세트의 경우 다음 시점에 테이블에 대한 클러스터링 키를 지정할 수 있습니다.
데이터가 로드되는 순서는 가장 일반적으로 쿼리되는 차원과 일치하지 않습니다(예: 데이터는 날짜별로 로드되지만 보고서는 데이터를 ID로 필터링함). 기존 스크립트 또는 보고서가 날짜 및 ID(및 잠재적으로 세 번째 또는 네 번째 열)를 기준으로 데이터를 쿼리하는 경우 다중 열 클러스터링 키를 생성하여 성능이 약간 향상될 수 있습니다.
쿼리 프로필 은 테이블에 대한 일반적인 쿼리의 총 지속 시간 중 상당한 비율이 스캔에 소비되었음을 나타냅니다. 이는 하나 이상의 특정 열을 필터링하는 쿼리에 적용됩니다.
재클러스터링은 기존 데이터를 다른 순서로 다시 씁니다. 이전 주문은 Fail-safe 보호를 제공하기 위해 7일 동안 저장됩니다. 테이블을 재클러스터링하면 재정렬되는 데이터의 크기와 관련된 컴퓨팅 비용이 발생합니다.
자세한 내용은 자동 클러스터링 섹션을 참조하십시오.
열 길이를 지정해야 하는 경우¶
Snowflake는 열 데이터를 효과적으로 압축합니다. 따라서 필요한 것보다 큰 열을 생성하면 데이터 테이블의 크기에 미치는 영향이 최소화됩니다. 마찬가지로 최대 길이 선언(예: VARCHAR(16777216))이 있는 열과 더 작은 정밀도 사이에는 쿼리 성능 차이가 없습니다.
그러나 열 데이터의 크기를 예측할 수 있는 경우 다음과 같은 이유로 적절한 열 길이를 정의하는 것이 좋습니다.
데이터 로딩 작업은 순서 없이 로드된 열과 같은 문제를 감지할 가능성이 더 높습니다. VARCHAR(10) 열에 잘못 로드된 50자 문자열입니다. 이러한 문제는 오류를 생성합니다.
열 길이가 지정되지 않은 경우 일부 서드 파티 도구는 최대 크기 값을 소비할 것으로 예상할 수 있으며, 이로 인해 클라이언트 측 메모리 사용량이 증가하거나 비정상적인 동작이 발생할 수 있습니다.
반정형 데이터를 VARIANT 열에 저장하기 vs 중첩 구조 평면화¶
반정형 데이터에 대해 어떤 타입의 작업을 수행하고 싶은지 아직 확실하지 않은 경우에는 임시로 VARIANT 열에 데이터를 저장하는 것이 좋습니다. 대부분이 일반 타입이고 기본 타입(문자열 및 정수)만 사용하는 데이터의 경우 VARIANT 열의 관계형 데이터 및 데이터에 대한 작업에 대한 저장 요구 사항 및 쿼리 성능은 매우 유사합니다.
정리를 향상하고 저장소 사용량을 줄이려면 반정형 데이터에 다음이 포함되는 경우 오브젝트 및 키 데이터를 별도의 관계형 열로 평면화하는 것이 좋습니다.
날짜 및 타임스탬프, 특히 ISO 8601이 아닌 날짜 및 타임스탬프, 문자열 값
문자열 내의 숫자
배열
날짜 및 타임스탬프와 같은 기본이 아닌 값은 VARIANT 열에 로딩될 때 문자열로 저장되므로, 이러한 값에 대한 작업은 해당 데이터 타입으로 관계형 열에 저장할 때보다 속도가 느려지고 공간도 더 많이 사용할 수 있습니다.
데이터에 대한 사용 사례를 알고 있는 경우 일반적인 데이터 세트에 대해 테스트를 수행합니다. 데이터 세트를 테이블의 VARIANT 열에 로드합니다. FLATTEN 함수를 사용하여 쿼리할 오브젝트와 키를 별도의 테이블로 추출합니다. 두 테이블에 대해 일반적인 쿼리 세트를 실행하여 최상의 성능을 제공하는 구조를 확인합니다.
영구 테이블을 임시 테이블로 또는 그 반대로 변환¶
현재는 ALTER TABLE 명령을 사용하여 영구 테이블을 일시적 테이블로 변경할 수 없습니다. TRANSIENT 속성은 테이블 생성 시 설정되며 수정할 수 없습니다.
마찬가지로 임시 테이블을 영구 테이블로 직접 변경할 수 없습니다.
열 기본값 및 부여된 권한과 같은 데이터 및 기타 속성을 유지하면서 기존 영구 테이블을 임시 테이블로(또는 그 반대로) 변환하려면 새 테이블을 생성하고 COPY GRANTS 절을 사용한 다음 데이터를 복사합니다.
CREATE TRANSIENT TABLE my_new_table LIKE my_old_table COPY GRANTS; INSERT INTO my_new_table SELECT * FROM my_old_table;
모든 데이터를 보존하지만 부여된 권한 및 기타 속성을 보존하지 않으려면 CREATE TABLE AS SELECT (CTAS) 를 사용할 수 있으며, 그러한 예는 다음과 같습니다.
CREATE TRANSIENT TABLE my_transient_table AS SELECT * FROM mytable;
테이블의 복사본을 만드는 또 다른 방법(그러나 수명 주기를 영구에서 임시로 변경)은 테이블을 CLONE 하는 것입니다. 예를 들면 다음과 같습니다.
CREATE TRANSIENT TABLE foo CLONE bar COPY GRANTS;이전 파티션은 영향을 받지 않지만 (일시적이지 않음), 복제본에 추가된 새 파티션은 일시적인 수명 주기를 따릅니다.
사용자는 일시적 테이블을 영구 테이블로 복제할 수 없습니다.