생성된 열 만들기 및 관리

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

새 테이블에 생성 열 추가

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

생성 열을 STORED로 표시하여 쿼리 시 표현식을 평가하는 비용을 절약할 수 있습니다. 따라서 FullName 값은 새 행이 삽입되거나 기존 행에 FirstName 또는 LastName이 업데이트되는 경우에만 계산됩니다. 계산된 값은 테이블의 다른 열과 함께 저장됩니다.

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

DDL에서 STORED 속성을 생략하여 저장되지 않은 생성 열을 만들 수 있습니다. 생성 열 종류는 쿼리 시 평가되며 이를 사용하면 쿼리가 더욱 간단해질 수 있습니다. PostgreSQL에서 VIRTUAL 속성을 사용하여 저장되지 않는 생성 열을 만들 수 있습니다.

GoogleSQL 

FullName STRING(MAX) AS (CONCAT(FirstName, " ", LastName))

PostgreSQL 

fullname text GENERATED ALWAYS AS (firstname || ' ' || lastname) VIRTUAL

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

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

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

    • PENDING_COMMIT_TIMESTAMP(), CURRENT_DATE(), CURRENT_TIMESTAMP()와 같은 비확정 함수가 있는 표현식을 STORED 생성 열이나 색인이 생성된 생성 열에 만들 수 없습니다.

    • STORED 또는 색인이 생성된 생성 열의 표현식을 수정할 수 없습니다.

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

  • STORED가 아닌 생성 열은 NOT NULL로 표시될 수 없습니다.

  • 생성 열에 직접 쓸 수 없습니다.

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

  • 색인이 생성된 STORED 또는 생성 열의 경우 열의 데이터 유형이나 생성 열에서 참조하는 열의 데이터 유형을 변경할 수 없습니다.

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

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

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

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

    • 생성된 기본 키는 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;

Fullname을 사용하는 쿼리는 생성된 표현식이 있는 쿼리와 동일합니다. 따라서 생성 열을 사용하면 쿼리가 더욱 간단해질 수 있습니다.

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

    PostgreSQL 

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

  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;

    색인이 생성된 생성 열을 사용하면 STORED 생성 열과 달리 쿼리 시 표현식 평가 비용이 절약되고 기본 테이블과 색인에 값이 두 번 저장되는 것을 방지할 수 있습니다.

생성 열 삭제

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

GoogleSQL 

  ALTER TABLE Users DROP COLUMN Initials;

PostgreSQL 

  ALTER TABLE users DROP COLUMN initials;

생성 열 표현식 수정

GoogleSQL 

ALTER TABLE Users ALTER COLUMN FullName STRING(100) 
AS (ARRAY_TO_STRING(ARRAY_TO_STRING([LastName, FirstName ], " ")));

PostgreSQL 

ALTER TABLE users ADD COLUMN Initials VARCHAR(2)
GENERATED ALWAYS AS (lastname || ' ' || firstname) VIRTUAL;

STORED 생성 열이나 색인이 생성된 저장되지 않는 생성 열의 표현식을 업데이트할 수 없습니다.

생성 열에서 기본 키 만들기

Spanner에서는 STORED 생성 열을 기본 키에 사용할 수 있습니다.

다음 예시에서는 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에서 생성된 기본 키 열 값을 추론하는 경우가 있습니다. 이는 생성된 기본 키 열에서 참조하는 열이 다음 조건 중 하나를 충족하는 경우에 해당됩니다.

  • 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에는 데이터베이스의 생성 열에 대한 정보가 포함됩니다. 다음 예시는 정보 스키마를 쿼리할 때의 질문과 그에 대한 답변입니다.

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

GoogleSQL 

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

PostgreSQL 

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

IS_STORED는 저장된 생성 열의 경우 YES, 저장되지 않는 생성 열의 경우 NO, 생성되지 않은 열의 경우 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;

참고: SQL 쿼리를 사용하면 저장되지 않는 생성 열에만 액세스할 수 있습니다. 하지만 색인이 생성된 경우에는 읽기 API를 사용하여 색인에서 값에 액세스할 수 있습니다.

성능

STORED 생성 열은 읽기 또는 쿼리 작업의 성능에 영향을 받지 않습니다. 그러나 쿼리에 사용되는 저장되지 않은 생성 열은 생성 열 표현식 평가 오버헤드로 인해 성능에 영향을 미칠 수 있습니다.

쓰기 작업(DML 문 및 변형) 성능은 STORED 생성 열이나 색인이 생성된 생성 열을 사용할 경우에 영향을 받습니다. 쓰기 작업에서 생성 열 표현식에서 참조되는 열을 삽입하거나 수정하면 생성 열 표현식 평가로 인해 오버헤드가 발생합니다. 오버헤드는 애플리케이션, 스키마 디자인, 데이터 세트 특성의 쓰기 워크로드에 따라 달라지므로 생성 열을 사용하기 전에 애플리케이션을 벤치마킹하는 것이 좋습니다.

다음 단계