생성된 열 만들기 및 관리

생성된 열은 항상 행의 다른 열에서 계산되는 열입니다. 이러한 열을 사용하면 쿼리를 더 간단하게 만들고, 쿼리 시 표현식 평가 비용을 절약할 수 있으며, 색인을 생성하거나 외래 키로 사용할 수 있습니다. 이 문서에서는 데이터베이스에서 이 열 유형을 관리하는 방법을 설명합니다.

새 테이블에 생성 열 추가

다음 CREATE TABLE 스니펫에서 사용자 관련 정보를 저장하기 위한 테이블을 만듭니다. FirstName과 LastName의 열이 있으며 FirstName 및 LastName의 연결을 갖는 FullName의 생성된 열을 정의합니다.

Google 표준 SQL

CREATE TABLE Users (
  Id STRING(20) NOT NULL,
  FirstName STRING(50),
  LastName STRING(50),
  Age INT64 NOT NULL,
  FullName STRING(100) AS (ARRAY_TO_STRING([FirstName, LastName], " ")) STORED,
) PRIMARY KEY (Id);

PostgreSQL

CREATE TABLE users (
  id VARCHAR(20) NOT NULL,
  firstname VARCHAR(50),
  lastname VARCHAR(50),
  age BIGINT NOT NULL,
  fullname VARCHAR(100) GENERATED ALWAYS AS (firstname || ' ' || lastname) STORED,
  PRIMARY KEY(id)
);

FullName 값은 새 행이 삽입되거나 기존 행의 FirstName 또는 LastName이 업데이트될 때 계산됩니다. 계산된 값은 테이블의 다른 열과 함께 저장됩니다. 괄호 안의 SQL을 생성 표현식이라고 합니다.

• expression은 열 데이터 유형에 할당할 수 있는 유효한 SQL 표현식으로 다음과 같은 제한사항이 있습니다.

  • 이 표현식은 동일 테이블의 열만 참조할 수 있습니다.

  • 표현식은 서브 쿼리를 포함할 수 없습니다.

  • 표현식은 PENDING_COMMIT_TIMESTAMP(), CURRENT_DATE(), CURRENT_TIMESTAMP()와 같은 비결정적 함수를 포함할 수 없습니다.

  • 생성 열의 표현식은 수정할 수 없습니다.

• 표현식 다음의 STORED 속성은 함수 결과를 테이블의 다른 열과 함께 저장합니다. 이후에 참조 열을 업데이트하면 표현식이 다시 평가되고 저장됩니다.

• STORED 속성이 없는 생성 열은 허용되지 않습니다.

• 생성 열에 대한 직접 쓰기는 허용되지 않습니다.

• 생성 열은 기본 키로 또는 기본 키의 일부로 사용될 수 없습니다. 하지만 보조 색인 키일 수 있습니다.

• 생성 열 또는 생성 열에서 참조하는 열에서는 열 옵션 allow_commit_timestamp가 허용되지 않습니다.

• 생성 열 또는 생성 열로 참조되는 열의 데이터 유형은 변경할 수 없습니다.

• 생성 열에서 참조하는 열을 삭제할 수 없습니다.

생성 열은 다음 예시와 같이 다른 열과 마찬가지로 쿼리할 수 있습니다.

Google 표준 SQL

SELECT Id, FullName
FROM Users;

PostgreSQL

SELECT id, fullname
FROM users;

이는 저장된 생성 열을 사용하지 않는 다음 문과 동일합니다.

Google 표준 SQL

SELECT Id, ARRAY_TO_STRING([FirstName, LastName], " ") as FullName
FROM Users;

PostgreSQL

SELECT id, firstname || ' ' || lastname as fullname
FROM users;

쿼리를 더 간단하게 만들고 쿼리 시 표현식 평가 비용을 절약하는 것 외에 생성 열의 색인을 생성하거나 외래 키로 사용할 수도 있습니다.

생성 열에 색인 만들기

FullName 생성 열을 조회하기 위해 다음 스니펫과 같이 보조 색인을 만들 수 있습니다.

Google 표준 SQL

CREATE INDEX UsersByFullName ON Users (FullName);

PostgreSQL

CREATE INDEX UserByFullName ON users (fullname);

기존 테이블에 생성 열 추가

다음 ALTER TABLE 문을 사용하여 생성 열을 Users 테이블에 추가하여 사용자의 이니셜을 생성하고 저장합니다.

Google 표준 SQL

ALTER TABLE Users ADD COLUMN Initials STRING(2)
AS (ARRAY_TO_STRING([SUBSTR(FirstName, 0, 1), SUBSTR(LastName, 0, 1)], "")) STORED;

PostgreSQL

ALTER TABLE users ADD COLUMN Initials VARCHAR(2)
GENERATED ALWAYS AS (SUBSTR(FirstName, 0, 1) || SUBSTR(LastName, 0, 1)) STORED;

저장된 생성 열을 기존 테이블에 추가하는 것은 열 값을 백필하는 장기 실행 작업입니다. 백필 중에는 저장된 생성된 열을 읽거나 쿼리할 수 없습니다. 백필 상태는 INFORMATION_SCHEMA에 반영됩니다.

생성 열을 사용하여 부분 색인 만들기

만 18세 이상의 사용자만 쿼리하려는 경우에는 어떻게 해야 하나요? 테이블의 전체 스캔은 비효율적이므로 부분 색인을 사용합니다.

1. 만 18세 이상인 경우 사용자의 연령을 반환하는 또 다른 생성 열을 추가하고 그 이외의 경우 NULL을 반환하는 경우 다음 문을 사용합니다.

   Google 표준 SQL

   ALTER TABLE Users ADD COLUMN AgeAbove18 INT64
   AS (IF(Age > 18, Age, NULL)) STORED;
   

   PostgreSQL

   ALTER TABLE Users ADD COLUMN AgeAbove18 BIGINT
   GENERATED ALWAYS AS (nullif( Age , least( 18, Age) )) STORED;
   

2. 이 새 열에 색인을 만들고 Google Standard SQL의 NULL_FILTERED 키워드 또는 PostgreSQL의 IS NOT NULL 조건자를 사용하여 NULL 값의 색인 생성을 중지합니다. 이 부분 색인은 만 18세 이하의 사용자를 제외하므로 일반 색인보다 더 작고 효율적입니다.

   Google 표준 SQL

   CREATE NULL_FILTERED INDEX UsersAbove18ByAge
   ON Users (AgeAbove18);
   

   PostgreSQL

   CREATE INDEX UsersAbove18ByAge ON users (AgeAbove18)
   WHERE AgeAbove18 IS NOT NULL;
   

3. 만 18세를 넘는 모든 사용자의 Id 및 Age를 검색하려면 다음 쿼리를 실행합니다.

   Google 표준 SQL

   SELECT Id, Age
   FROM Users@{FORCE_INDEX=UsersAbove18ByAge}
   WHERE AgeAbove18 IS NOT NULL;
   

   PostgreSQL

   SELECT Id, Age
   FROM users /*@ FORCE_INDEX = UsersAbove18ByAge */
   WHERE AgeAbove18 IS NOT NULL;
   

4. 다른 연령으로 필터링하려면, 예를 들어 만 21세를 넘는 모든 사용자를 검색하려면 다음과 같이 생성 열에서 동일한 색인과 필터를 사용합니다.

   Google 표준 SQL

   SELECT Id, Age
   FROM Users@{FORCE_INDEX=UsersAbove18ByAge}
   WHERE AgeAbove18 > 21;
   

   PostgreSQL

   SELECT Id, Age
   FROM users /*@ FORCE_INDEX = UsersAbove18ByAge */
   WHERE AgeAbove18 > 21;
   

생성 열 삭제

다음 DDL 문은 Users 테이블의 생성 열을 삭제합니다.

ALTER TABLE Users
DROP COLUMN Initials;

생성 열 표현식 수정

생성 열의 표현식 수정은 허용되지 않습니다. 대신 기존 열을 삭제하고 새 표현식을 사용하여 새로운 생성 열을 만들어야 합니다.

생성 열의 속성 보기

Spanner의 INFORMATION_SCHEMA에는 데이터베이스의 생성 열에 대한 정보가 포함됩니다. 다음은 정보 스키마를 쿼리하여 답변할 수 있는 몇 가지 질문의 예시입니다.

어떤 생성 열이 데이터베이스에 정의되나요?

SELECT c.TABLE_NAME, c.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS as c
WHERE c.GENERATION_EXPRESSION IS NOT NULL;

Users 테이블에서 생성 열의 현재 상태는 무엇인가요?

기존 테이블에 생성 열을 추가한 경우 현재 상태를 확인하여 해당 열이 백필되고 완전히 사용 가능한 상태인지 확인할 수 있습니다. SPANNER_STATE가 다음 쿼리에서 WRITE_ONLY를 반환하는 경우 Spanner는 열을 계속 백필하는 중이며 읽기가 허용되지 않음을 의미합니다.

Google 표준 SQL

SELECT c.TABLE_NAME, c.COLUMN_NAME, c.SPANNER_STATE
FROM INFORMATION_SCHEMA.COLUMNS AS c
WHERE c.TABLE_NAME="Users" AND c.GENERATION_EXPRESSION IS NOT NULL;

PostgreSQL

SELECT c.TABLE_NAME, c.COLUMN_NAME, c.SPANNER_STATE
FROM INFORMATION_SCHEMA.COLUMNS AS c
WHERE c.TABLE_NAME='users' AND c.GENERATION_EXPRESSION IS NOT NULL;

다음 단계

• Spanner의 정보 스키마 자세히 알아보기

• CREATE TABLE 매개변수 세부정보에서 생성 열에 대해 자세히 알아보기