ID 열 지정

이 문서에서는 테이블에서 기본 키를 만들고 유지하는 데 사용되는 ID 열(때로는 자동 증가 열이라고 함)을 만들고 사용하는 방법을 설명합니다. ID 열이 있는 테이블에 행을 삽입하면 BigQuery에서 해당 열의 고유한 정수 값을 생성합니다.

개요

ID 열은 고유한 시스템 생성 값으로 채워지는 INT64 열입니다.

ID 열의 주요 사용 사례는 기본 키를 생성하는 것입니다. GENERATE_UUID`GENERATE_UUID` 함수를 사용하여 고유한 문자열을 생성하여 기본 키를 생성할 수도 있지만 일반적으로 다음과 같은 이유로 ID 열이 선호됩니다.

  • 정수 값은 문자열 값보다 저장공간이 적게 필요합니다.
  • 테이블 조인에 정수를 사용하는 것이 문자열을 사용하는 것보다 더 효율적입니다.

ID 열의 값은 첫 번째 값을 정의하는 시작 값과 연속적으로 생성된 값 간의 최소 차이를 정의하는 증분 값을 기반으로 생성됩니다.

생성된 ID 열 값에는 다음과 같은 속성이 있습니다.

  • 고유. 자동으로 생성된 값은 테이블 내에서 고유합니다.
  • 느슨하게 정렬됨. 생성된 값이 엄격하게 증가하거나 감소하는 순서로 보장되지는 않습니다.
  • 희소. 생성된 값이 연속적으로 보장되지는 않습니다. 일부 값이 건너뛸 수 있지만 ID 열의 값은 항상 지정한 증분의 배수만큼 다릅니다.

제한사항

  • 테이블에는 ID 열이 최대 하나만 있을 수 있습니다.
  • 레거시 SQL을 사용하여 ID 열이 있는 테이블에서 읽을 수 있지만 레거시 SQL을 사용하여 ID 열이 있는 테이블에 쓸 수는 없습니다.
  • ID 열에서 클러스터링 또는 파티션 나누기를 사용할 수 없습니다.

  • 소스 또는 대상 테이블에 ID 열이 있는 경우 다음 테이블 복사 작업은 지원되지 않습니다.

    • WRITE_APPEND 또는 WRITE_TRUNCATE 쓰기 처리로 테이블 복사
    • 다중 소스 테이블 복사

  • Storage Write API (gRPC) 또는 tabledata.insertAll API 메서드 를 사용하여 데이터를 스트리밍하는 것은 ID 열이 있는 테이블에서 지원되지 않습니다.

ID 열 만들기

CREATE TABLE DDL 문을 사용하여 새 테이블을 만들 때 ID 열을 만들 수 있습니다. GENERATED AS IDENTITY 절을 사용하여 INT64 열을 ID 열로 지정합니다. 테이블에는 ID 열이 최대 하나만 있을 수 있습니다. ID 열에 값을 수동으로 삽입할 수 있는지 여부를 결정하는 다음 생성 모드 중 하나를 지정할 수 있습니다.

  • GENERATED ALWAYS AS IDENTITY: 값은 항상 시스템에서 생성됩니다. 이 열에 데이터를 삽입하거나 업데이트할 때 자체 값을 제공할 수 없습니다. ALWAYS 또는 BY DEFAULT를 지정하지 않으면 ALWAYS가 사용됩니다.

  • GENERATED BY DEFAULT AS IDENTITY: ID 열에 값을 삽입하거나 수정할 수 있습니다. BigQuery는 삽입하거나 수정하는 값의 고유성을 적용하지 않습니다.

    데이터를 삽입할 때 열을 생략하거나 NULL을 제공하면 BigQuery에서 자동으로 값을 생성합니다. ID 열에는 NULL 값이 포함될 수 없습니다. INSERT, MERGE 또는 UPDATE 문에서 생성된 값을 사용하려면 DEFAULT 또는 NULL 키워드를 사용하면 됩니다.

다음 예시에서는 0에서 시작하여 5씩 증가하는 ID 열 id이 있는 테이블 mydataset.id_table을 만듭니다.

CREATE TABLE mydataset.id_table (
  id INT64 GENERATED ALWAYS AS IDENTITY(START WITH 0 INCREMENT BY 5),
  data STRING
);

열에 ID 열 속성 추가

기존 열을 수정하여 ID 값을 생성하려면 ALTER TABLE ALTER COLUMN SET GENERATED DDL 문을 사용합니다. 이 문은 기존 INT64 열을 ID 열로 변경합니다. ID 열의 기존 행에 값을 백필하지 않습니다.

ID 열이 있는 DML 문 사용

ID 열이 있는 INSERT, MERGE, UPDATE와 같은 DML 문을 사용할 수 있습니다. 다음 섹션에서는 라는 ID 열과 iddata라는 문자열 열이 있는 테이블 mydataset.mytable을 사용합니다.

CREATE OR REPLACE TABLE mydataset.mytable (
  id INT64 GENERATED BY DEFAULT AS IDENTITY(START WITH 100 INCREMENT BY 10),
  data STRING
);

데이터 삽입

ID 열이 있는 테이블에 데이터를 삽입할 때 열 목록에서 ID 열을 생략하여 값을 생성할 수 있습니다. 다음 INSERT 문은 id 열을 생략합니다. BigQuery에서 값을 생성합니다.

INSERT mydataset.mytable (data) VALUES ('A'), ('B'), ('C');

결과는 다음과 비슷하지만 생성된 값을 행에 할당하는 순서는 다를 수 있습니다.

+-----+------+
| id  | data |
+-----+------+
| 110 | A    |
| 120 | B    |
| 100 | C    |
+-----+------+

ID 열이 GENERATED BY DEFAULT AS IDENTITY로 정의된 경우 열에 자체 값을 지정할 수 있습니다. DEFAULT 키워드 또는 NULL을 사용하여 BigQuery에서 값을 생성하도록 할 수도 있습니다.

다음 INSERT 문은 한 행에 값을 제공하고 DEFAULT 또는 NULL을 사용하여 다른 두 행의 값을 생성합니다.

INSERT mydataset.mytable (id, data)
VALUES (155, 'D'), (DEFAULT, 'E'), (NULL, 'F');

결과는 다음과 비슷합니다.

+-----+------+
| id  | data |
+-----+------+
| 110 | A    |
| 120 | B    |
| 100 | C    |
| 155 | D    |
| 140 | E    |
| 130 | F    |
+-----+------+

ID 열이 GENERATED ALWAYS AS IDENTITY로 정의된 경우 DEFAULT 키워드만 사용하여 BigQuery에서 값을 생성하도록 할 수 있습니다. 자체 값을 제공하거나 NULL을 사용할 수 없습니다.

데이터 병합

MERGE 문 을 사용하여 ID 열이 있는 테이블에 데이터를 병합할 수 있습니다. ID 열이 GENERATED BY DEFAULT AS IDENTITY 생성 모드를 사용하는 경우 DEFAULT 또는 NULL 키워드를 사용하여 데이터를 삽입하거나 업데이트할 때 값을 생성할 수 있습니다. MERGE 문.

다음 예시에서는 mydataset.source_table을 mydataset.mytable에 병합하여 data 열에 일치하는 항목이 없으면 새 행을 삽입하고 일치하는 항목이 있으면 id 열을 새로 생성된 값으로 업데이트합니다.

CREATE OR REPLACE TABLE mydataset.source_table(data STRING)
AS SELECT * FROM UNNEST(['A', 'C', 'G']);

MERGE mydataset.mytable T
USING mydataset.source_table S
ON T.data = S.data
WHEN MATCHED THEN
  UPDATE SET id = DEFAULT
WHEN NOT MATCHED THEN
  INSERT(data)
  VALUES(S.data);

결과는 다음과 비슷합니다.

+-----+------+
| id  | data |
+-----+------+
| 160 | A    |
| 120 | B    |
| 150 | C    |
| 155 | D    |
| 140 | E    |
| 130 | F    |
| 170 | G    |
+-----+------+

ID 열이 GENERATED ALWAYS AS IDENTITY 생성 모드를 사용하는 경우 병합 업데이트 절에 ID 열을 포함할 수 없습니다. 병합 삽입 절을 사용하려면 열 목록에서 ID 열을 생략하거나 DEFAULT 키워드를 사용하면 됩니다.

데이터 업데이트

UPDATE 문 을 사용하여 GENERATED BY DEFAULT AS IDENTITY 생성 모드를 사용하는 ID 열의 값을 업데이트할 수 있습니다. DEFAULT 또는 NULL 키워드를 사용하여 새 값을 생성할 수 있습니다.

다음 예시에서는 id 열의 모든 값을 새로 생성된 값으로 업데이트합니다.

UPDATE mydataset.mytable
SET id = NULL
WHERE TRUE;

결과는 다음과 비슷합니다.

+-----+------+
| id  | data |
+-----+------+
| 190 | A    |
| 210 | B    |
| 240 | C    |
| 230 | D    |
| 180 | E    |
| 200 | F    |
| 220 | G    |
+-----+------+

ID 열이 GENERATED ALWAYS AS IDENTITY 생성 모드를 사용하는 경우 ID 열을 업데이트할 수 없습니다.

테이블에 추가

bq query 명령어를 --append_table 플래그와 함께 사용하면 ID 열이 있는 대상 테이블에 쿼리 결과를 추가할 수 있습니다. 쿼리에서 ID 열을 생략하면 값이 생성됩니다.

다음 예시에서는 data 열의 데이터만 mydataset.mytable에 추가합니다.

bq query \
    --nouse_legacy_sql \
    --append_table \
    --destination_table=mydataset.mytable \
    'SELECT "H" AS data'

생성된 id 값이 있는 새 행이 mydataset.mytable에 추가됩니다.

데이터 로드

데이터를 로드 하려면 ID 열이 있는 테이블에 bq load 명령어 또는 LOAD DATA 문을 사용하면 됩니다. 소스 데이터 또는 스키마에서 ID 열이 생략되면 값이 생성됩니다. ID 열이 GENERATED ALWAYS AS IDENTITY인 경우 생략해야 합니다.

다음 예시에서는 CSV 파일 data.csv의 데이터를 mydataset.mytable에 로드합니다. 파일에는 data 열의 데이터만 포함됩니다.

"X"
"Y"

다음 bq load 명령어는 data.csv를 mydataset.mytable에 로드하고, 헤더 행을 생략하며 스키마에서 data 열만 지정합니다.

bq load --source_format=CSV --skip_leading_rows=0 \
mydataset.mytable data.csv data:STRING

로드 작업은 새 행의 id 값을 생성합니다.

ID 열 속성 삭제

ALTER TABLE ALTER COLUMN DROP GENERATED DDL 문을 사용하여 열에서 ID 속성을 삭제할 수 있습니다.

다음 예시에서는 mydataset.mytable의 id 열에서 ID 열 속성을 삭제합니다.

ALTER TABLE mydataset.mytable
ALTER COLUMN id DROP GENERATED;

ID 열에 대한 정보 보기

열의 ID 열 구성을 보려면 INFORMATION_SCHEMA.COLUMNS 뷰를 쿼리합니다.

다음 예시에서는 mydataset.mytable의 열에 대한 ID 열 정보를 보여줍니다.

SELECT
  column_name,
  is_identity,
  identity_generation,
  identity_start,
  identity_increment
FROM
  mydataset.INFORMATION_SCHEMA.COLUMNS
WHERE
  table_name = 'mytable';

결과는 다음과 비슷합니다.

+-------------+-------------+---------------------+----------------+--------------------+
| column_name | is_identity | identity_generation | identity_start | identity_increment |
+-------------+-------------+---------------------+----------------+--------------------+
| id          | YES         | BY DEFAULT          | 100            | 10                 |
| data        | NO          | NULL                | NULL           | NULL               |
+-------------+-------------+---------------------+----------------+--------------------+

또는 ddl 열을 쿼리하여 INFORMATION_SCHEMA.TABLES 뷰에서 테이블의 CREATE TABLE DDL 문에 있는 ID 열 정의를 확인할 수 있습니다.

다음 단계