생성된 열 만들기 및 관리

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

새 테이블에 생성 열 추가

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

GoogleSQL 

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 표현식으로 다음과 같은 제한사항이 있습니다.

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

  • Spanner는 STORED 속성을 사용하지 않는 한 생성된 열을 허용하지 않습니다.

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

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

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

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

  • 생성 열을 기본 키로 사용할 수 있으며 다음과 같은 추가 제한사항이 있습니다.

    • 생성된 기본 키는 생성된 다른 열을 참조할 수 없습니다.

    • 생성된 기본 키는 키가 아닌 열을 최대 한 개 참조할 수 있습니다.

    • 생성된 기본 키는 DEFAULT 절이 있는 키 열이 아닌 열에 종속될 수 없습니다.

  • 생성된 키 열을 사용할 때 다음 규칙이 적용됩니다.

    • API 읽기: 생성된 키 열을 포함하여 키 열을 완전히 지정해야 합니다.
    • 변형 API: INSERT, INSERT_OR_UPDATE, REPLACE의 경우 Spanner에서 생성된 키 열을 지정할 수 없습니다. UPDATE: 생성된 키 열을 선택적으로 지정할 수 있습니다. DELETE: 생성된 키를 포함한 키 열을 완전히 지정해야 합니다.
    • DML: INSERT 또는 UPDATE 문에서 생성된 키에 명시적으로 쓸 수 없습니다.
    • 쿼리: 일반적으로 생성된 키 열을 쿼리에서 필터로 사용하는 것이 좋습니다. 선택적으로 생성된 키 열의 표현식이 참조로 하나의 열만 사용하는 경우 쿼리는 참조된 열에 일치(=) 또는 IN 조건을 적용할 수 있습니다. 자세한 내용 및 예시는 값 열에서 파생된 고유 키 만들기를 참조하세요.

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

GoogleSQL 

SELECT Id, FullName
FROM Users;

PostgreSQL 

SELECT id, fullname
FROM users;

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

GoogleSQL 

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

PostgreSQL 

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

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

생성 열에 색인 만들기

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

GoogleSQL 

CREATE INDEX UsersByFullName ON Users (FullName);

PostgreSQL 

CREATE INDEX UserByFullName ON users (fullname);

기존 테이블에 생성 열 추가

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

GoogleSQL 

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을 반환하는 생성된 다른 열을 추가합니다.

    GoogleSQL 

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

    GoogleSQL 

    CREATE NULL_FILTERED INDEX UsersAbove18ByAge
ON Users (AgeAbove18);

    PostgreSQL 

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

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

    GoogleSQL 

    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세를 넘는 모든 사용자를 검색하려면 다음과 같이 생성 열에서 동일한 색인과 필터를 사용합니다.

    GoogleSQL 

    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에서는 기본 키에 생성 열을 사용할 수 있습니다.

다음 예시에서는 ShardId 생성 열로 UserInfoLog 테이블을 만드는 DDL 문을 보여줍니다. ShardId 열의 값은 다른 열에 따라 달라집니다. UserId 열의 MOD 함수를 사용하여 파생됩니다. ShardId은 기본 키의 일부로 선언됩니다.

GoogleSQL 

CREATE TABLE UserInfoLog (
  ShardId INT64 NOT NULL
  AS (MOD(UserId, 2048)) STORED,
  UserId INT64 NOT NULL,
  FullName STRING(1024) NOT NULL,
) PRIMARY KEY (ShardId, UserId);

PostgreSQL 

CREATE TABLE UserInfoLog (
  ShardId BIGINT GENERATED ALWAYS
  AS (MOD(UserId, '2048'::BIGINT)) STORED NOT NULL,
  UserId BIGINT NOT NULL,
  FullName VARCHAR(1024) NOT NULL,
  PRIMARY KEY(ShardId, UserId));

일반적으로 특정 행에 효율적으로 액세스하려면 모든 키 열을 지정해야 합니다. 앞의 예시에서 ShardId 및 UserId를 모두 제공해야 합니다. 그러나 Spanner는 다른 단일 열에 의존하고 종속된 열 값이 완전히 결정되는 경우 생성된 기본 키 열의 값을 추론할 수 있습니다. 생성된 기본 키 열이 참조하는 열이 다음 조건 중 하나를 충족하면 true입니다.

  • WHERE 절의 상수 값 또는 바인딩된 매개변수와 같습니다.
  • WHERE 절에서 IN 연산자로 설정된 값을 가져옵니다.
  • 동일 조인 조건에서 값을 가져옵니다.

예를 들면 다음 쿼리의 경우 다음과 같습니다.

GoogleSQL 

SELECT * FROM UserInfoLog
AS T WHERE T.UserId=1;

PostgreSQL 

SELECT * FROM UserInfoLog
AS T WHERE T.UserId=1;

Spanner는 제공된 UserId에서 ShardId 값을 추론할 수 있습니다. 이전 쿼리는 쿼리 최적화 후 다음 쿼리와 동일합니다.

GoogleSQL 

SELECT * FROM UserInfoLog
AS T WHERE T.ShardId = MOD(1, 2048)
AND T.UserId=1;

PostgreSQL 

SELECT * FROM UserInfoLog
AS T WHERE T.ShardId = MOD(1, 2048)
AND T.UserId=1;

다음 예시에서는 Students 테이블을 만들고 StudentInfo JSON 열의 id 필드를 검색하고 기본 키로 사용하는 표현식을 사용하는 방법을 보여줍니다.

GoogleSQL 

CREATE TABLE Students (
  StudentId INT64 NOT NULL
  AS (CAST(JSON_VALUE(StudentInfo, "$.id") AS INT64)) STORED,
  StudentInfo JSON NOT NULL,
) PRIMARY KEY (StudentId);

PostgreSQL 

CREATE TABLE Students (
  StudentId BIGINT GENERATED ALWAYS
  AS (((StudentInfo ->> 'id'::TEXT))::BIGINT) STORED NOT NULL,
  StudentInfo JSONB NOT NULL,
  PRIMARY KEY(StudentId));

생성 열의 속성 보기

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를 전달하여 열의 현재 상태를 확인할 수 있습니다. SPANNER_STATE는 다음 값을 반환합니다.

  • COMMITTED: 열을 완전히 사용할 수 있습니다.
  • WRITE_ONLY: 열이 백필됩니다. 읽기가 허용되지 않습니다.

열의 상태를 찾으려면 다음 쿼리를 사용합니다.

GoogleSQL 

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;

참고: 생성된 열은 읽기 또는 쿼리 작업의 성능에 영향을 주지 않습니다. 하지만 쓰기 작업이 생성된 열 표현식에서 참조된 열을 수정하는 경우 생성된 열의 열 표현식 평가 오버헤드로 인해 쓰기 작업(`DML` 문 및 `변형`)의 성능에 영향을 줄 수 있습니다. 오버헤드는 애플리케이션, 스키마 디자인, 데이터 세트 특성의 쓰기 워크로드에 따라 달라지므로 생성 열을 사용하기 전에 애플리케이션을 벤치마킹하는 것이 좋습니다.

다음 단계

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

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