기본 열 값 지정
이 페이지에서는 BigQuery 테이블의 열 기본값을 설정하는 방법을 설명합니다. 기본값을 가진 열에 대한 데이터가 없는 테이블에 행을 추가하면 기본값은 대신 열에 기록됩니다.
기본값 표현식
열의 기본값 표현식은 리터럴 또는 다음 함수 중 하나여야 합니다.
CURRENT_DATECURRENT_DATETIMECURRENT_TIMECURRENT_TIMESTAMPGENERATE_UUIDRANDSESSION_USERST_GEOGPOINT
이러한 함수(예: [CURRENT_DATE(), DATE '2020-01-01'])로 STRUCT 또는 ARRAY 기본값을 구성할 수 있습니다.
함수는 데이터가 테이블에 기록될 때 평가됩니다.
기본값 유형은 적용할 열 유형과 일치하거나 강제 변환되어야 합니다. 기본값이 설정되어 있지 않으면 기본값은 NULL입니다.
기본값 설정
새 테이블을 만들 때 열 기본값을 설정할 수 있습니다. CREATE TABLE DDL 문을 사용하고 열 이름과 유형 뒤에 DEFAULT 키워드 및 기본값 표현식을 추가합니다. 다음 예시에서는 2개 STRING 열(a, b)이 있는 simple_table이라는 테이블을 만듭니다. b 열의 기본값은 'hello'입니다.
CREATE TABLE mydataset.simple_table ( a STRING, b STRING DEFAULT 'hello');
b 열을 생략하는 simple_table에 데이터를 삽입하면 기본값 'hello'가 대신 사용됩니다. 예를 들면 다음과 같습니다.
INSERT mydataset.simple_table (a) VALUES ('val1'), ('val2');
simple_table 테이블에는 다음 값이 포함됩니다.
+------+-------+ | a | b | +------+-------+ | val1 | hello | | val2 | hello | +------+-------+
열에 STRUCT 유형이 있으면 전체 STRUCT 필드에 기본값을 설정해야 합니다. 필드 하위 집합의 기본값을 설정할 수 없습니다. 배열의 기본값은 NULL일 수 없으며 NULL 요소를 포함할 수 없습니다.
다음 예시에서는 complex_table이라는 테이블을 만들고 ARRAY 유형을 포함하는 중첩 필드가 포함된 struct_col 열의 기본값을 설정합니다.
CREATE TABLE mydataset.complex_table (
struct_col STRUCT<x STRUCT<x1 TIMESTAMP, x2 NUMERIC>, y ARRAY<DATE>>
DEFAULT ((CURRENT_TIMESTAMP(), NULL),
[DATE '2022-01-01', CURRENT_DATE()])
);
매개변수화된 유형을 준수하지 않는 기본값이나 열의 모드가 REQUIRED인 경우에 NULL 기본값과 같이 열의 제약조건을 위반하는 기본값은 설정할 수 없습니다.
기본값 변경
열의 기본값을 변경하려면 다음 옵션 중 하나를 선택합니다.
콘솔
Google Cloud 콘솔에서 BigQuery 페이지로 이동합니다.
Explorer 패널에서 프로젝트와 데이터 세트를 펼친 후 테이블을 선택합니다.
세부정보 패널에서 스키마 탭을 클릭합니다.
스키마 수정을 클릭합니다. 이 버튼을 보려면 스크롤해야 할 수도 있습니다.
현재 스키마 페이지에서 변경하려는 최상위 필드를 찾습니다.
해당 필드의 기본값을 입력합니다.
저장을 클릭합니다.
SQL
ALTER COLUMN SET DEFAULT DDL 문을 사용합니다.
Google Cloud 콘솔에서 BigQuery 페이지로 이동합니다.
쿼리 편집기에서 다음 문을 입력합니다.
ALTER TABLE mydataset.mytable ALTER COLUMN column_name SET DEFAULT default_expression;
실행을 클릭합니다.
쿼리를 실행하는 방법에 대한 자세한 내용은 대화형 쿼리 실행을 참조하세요.
열의 기본값을 설정하면 향후 테이블에 삽입할 때만 영향을 줍니다.
기존 테이블 데이터는 변경되지 않습니다. 다음 예시에서는 a 열의 기본값을 SESSION_USER()로 설정합니다.
ALTER TABLE mydataset.simple_table ALTER COLUMN a SET DEFAULT SESSION_USER();
a 열을 생략하는 행을 simple_table에 삽입하면 현재 세션 사용자가 대신 사용됩니다.
INSERT mydataset.simple_table (b) VALUES ('goodbye');
simple_table 테이블에는 다음 값이 포함됩니다.
+------------------+---------+ | a | b | +------------------+---------+ | val1 | hello | | val2 | hello | | user@example.com | goodbye | +------------------+---------+
기본값 삭제
열의 기본값을 삭제하려면 다음 옵션 중 하나를 선택합니다.
콘솔
Google Cloud 콘솔에서 BigQuery 페이지로 이동합니다.
Explorer 패널에서 프로젝트와 데이터 세트를 펼친 후 테이블을 선택합니다.
세부정보 패널에서 스키마 탭을 클릭합니다.
스키마 수정을 클릭합니다. 이 버튼을 보려면 스크롤해야 할 수도 있습니다.
현재 스키마 페이지에서 변경하려는 최상위 필드를 찾습니다.
기본값으로
NULL을 입력합니다.저장을 클릭합니다.
SQL
ALTER COLUMN DROP DEFAULT DDL 문을 사용합니다.
Google Cloud 콘솔에서 BigQuery 페이지로 이동합니다.
쿼리 편집기에서 다음 문을 입력합니다.
ALTER TABLE mydataset.mytable ALTER COLUMN column_name DROP DEFAULT;
또한
ALTER COLUMN SET DEFAULTDDL 문으로 값을NULL로 변경하여 열에서 기본값을 삭제할 수도 있습니다.실행을 클릭합니다.
쿼리를 실행하는 방법에 대한 자세한 내용은 대화형 쿼리 실행을 참조하세요.
기본값으로 DML 문 사용
INSERT DML 문을 사용하여 기본값을 가진 행을 테이블에 추가할 수 있습니다.
열 값이 지정되지 않았거나 값 표현식 대신 DEFAULT 키워드가 사용된 경우 기본값이 사용됩니다. 다음 예시에서는 테이블을 만들고 모든 값이 기본값인 행을 삽입합니다.
CREATE TABLE mydataset.mytable ( x TIME DEFAULT CURRENT_TIME(), y INT64 DEFAULT 5, z BOOL); INSERT mydataset.mytable (x, y, z) VALUES (DEFAULT, DEFAULT, DEFAULT);
mytable 테이블은 다음과 같습니다.
+-----------------+---+------+ | x | y | z | +-----------------+---+------+ | 22:13:24.799555 | 5 | null | +-----------------+---+------+
z 열에는 기본값이 없으므로 NULL이 기본값으로 사용됩니다. 기본값이 CURRENT_TIME()과 같은 함수이면 값이 기록될 때 평가됩니다. x 열의 기본값으로 INSERT를 다시 호출하면 TIME의 값이 달라집니다. 다음 예시에서 z 열에만 명시적으로 설정된 값이 있고 생략된 열은 기본값을 사용합니다.
INSERT mydataset.mytable (z) VALUES (TRUE);
mytable 테이블은 다음과 같습니다.
+-----------------+---+------+ | x | y | z | +-----------------+---+------+ | 22:13:24.799555 | 5 | null | | 22:18:29.890547 | 5 | true | +-----------------+---+------+
MERGE DML 문을 사용하여 테이블을 기본값으로 업데이트할 수 있습니다.
다음 예시에서는 테이블 두 개를 만들고 MERGE 문으로 테이블 중 하나를 업데이트합니다.
CREATE TABLE mydataset.target_table (
a STRING,
b STRING DEFAULT 'default_b',
c STRING DEFAULT SESSION_USER())
AS (
SELECT
'val1' AS a, 'hi' AS b, '123@google.com' AS c
UNION ALL
SELECT
'val2' AS a, 'goodbye' AS b, SESSION_USER() AS c
);
CREATE TABLE mydataset.source_table (
a STRING DEFAULT 'default_val',
b STRING DEFAULT 'Happy day!')
AS (
SELECT
'val1' AS a, 'Good evening！' AS b
UNION ALL
SELECT
'val3' AS a, 'Good morning!' AS b
);
MERGE mydataset.target_table T
USING mydataset.source_table S
ON T.a = S.a
WHEN NOT MATCHED THEN
INSERT(a, b) VALUES (a, DEFAULT);
결과는 다음과 같습니다.
+------+-----------+--------------------+ | a | b | c | +------+-----------+--------------------+ | val1 | hi | 123@google.com | | val2 | goodbye | default@google.com | | val3 | default_b | default@google.com | +------+-----------+--------------------+
UPDATE DML 문을 사용하여 테이블을 기본값으로 업데이트할 수 있습니다.
다음 예시는 b 열의 각 행이 기본값과 동일하도록 source_table 테이블을 업데이트합니다.
UPDATE mydataset.source_table SET b = DEFAULT WHERE TRUE;
결과는 다음과 같습니다.
+------+------------+ | a | b | +------+------------+ | val1 | Happy day! | | val3 | Happy day! | +------+------------+
테이블 추가
bq query 명령어를 --append_table 플래그와 함께 사용하면 기본값을 가진 대상 테이블에 쿼리 결과를 추가할 수 있습니다. 쿼리에서 기본값을 가진 열을 생략하면 기본값이 할당됩니다. 다음 예시는 z 열의 값만 지정하는 데이터를 추가합니다.
bq query \
--nouse_legacy_sql \
--append_table \
--destination_table=mydataset.mytable \
'SELECT FALSE AS z UNION ALL SELECT FALSE AS Z'
mytable 테이블은 x 및 y 열의 기본값을 사용합니다.
+-----------------+---+-------+ | x | y | z | +-----------------+---+-------+ | 22:13:24.799555 | 5 | NULL | | 22:18:29.890547 | 5 | true | | 23:05:18.841683 | 5 | false | | 23:05:18.841683 | 5 | false | +-----------------+---+-------+
데이터 로드
bq load 명령어 또는 LOAD DATA 문을 사용하여 기본값을 가진 테이블에 데이터를 로드할 수 있습니다.
로드된 데이터의 열이 대상 테이블보다 적을 때 기본값이 적용됩니다. 로드된 데이터의 NULL 값은 기본값으로 변환되지 않습니다.
AVRO, Parquet, ORC와 같은 바이너리 형식에는 인코딩된 파일 스키마가 있습니다. 파일 스키마에서 일부 열이 생략되면 기본값이 적용됩니다.
JSON 및 CSV와 같은 텍스트 형식에는 인코딩된 파일 스키마가 없습니다. bq 명령줄 도구를 사용하여 스키마를 지정하려면 --autodetect 플래그를 사용하거나 JSON 스키마를 제공하면 됩니다. LOAD DATA 문을 사용하여 스키마를 지정하려면 열 목록을 제공해야 합니다. 다음은 CSV 파일에서 a 열만 로드하는 예시입니다.
LOAD DATA INTO mydataset.insert_table (a) FROM FILES( uris = ['gs://test-bucket/sample.csv'], format = 'CSV');
API 작성
Storage Write API는 쓰기 스트림 스키마에 대상 테이블 스키마에 포함된 필드가 누락된 경우에만 기본값을 채웁니다.
이 경우 누락된 필드는 쓰기마다 열의 기본값으로 채워집니다. 필드가 쓰기 스트림 스키마에 있지만 데이터 자체에서 누락된 경우 누락된 필드가 NULL로 채워집니다.
예를 들어 다음 스키마를 사용하여 BigQuery 테이블에 데이터를 기록한다고 가정하겠습니다.
[
{
"name": "a",
"mode": "NULLABLE",
"type": "STRING",
},
{
"name": "b",
"mode": "NULLABLE",
"type": "STRING",
"defaultValueExpression": "'default_b'"
},
{
"name": "c",
"mode": "NULLABLE",
"type": "STRING",
"defaultValueExpression": "'default_c'"
}
]
다음 쓰기 스트림 스키마에는 대상 테이블에 있는 c 필드가 누락되었습니다.
[
{
"name": "a",
"type": "STRING",
},
{
"name": "b",
"type": "STRING",
}
]
다음 값을 테이블에 스트리밍한다고 가정해 보겠습니다.
{'a': 'val_a', 'b': 'val_b'}
{'a': 'val_a'}
결과는 다음과 같습니다.
+-------+-------+-----------+ | a | b | c | +-------+-------+-----------+ | val_a | val_b | default_c | | val_a | NULL | default_c | +-------+-------+-----------+
쓰기 스트림 스키마에는 b 필드가 포함되므로 필드에 값이 지정되지 않은 경우에도 기본값 default_b가 사용되지 않습니다. 쓰기 스트림 스키마에는 c 필드가 포함되어 있지 않으므로 c 열의 모든 행이 대상 테이블의 기본값 default_c로 채워집니다.
다음 쓰기 스트림 스키마는 쓰려는 테이블의 스키마와 일치합니다.
[
{
"name": "a",
"type": "STRING",
},
{
"name": "b",
"type": "STRING",
}
{
"name": "c",
"type": "STRING",
}
]
다음 값을 테이블에 스트리밍한다고 가정해 보겠습니다.
{'a': 'val_a', 'b': 'val_b'}
{'a': 'val_a'}
대상 테이블에 포함된 필드는 쓰기 스트림 스키마에서 누락되지 않으므로 스트리밍된 데이터에 필드가 입력되었는지 여부와 관계없이 열의 기본값이 적용되지 않습니다.
+-------+-------+------+ | a | b | c | +-------+-------+------+ | val_a | val_b | NULL | | val_a | NULL | NULL | +-------+-------+------+
AppendRowsRequest 메시지 내의 default_missing_value_interpretation에서 연결 수준 기본값 설정을 지정할 수 있습니다. 값이 DEFAULT_VALUE로 설정된 경우 열이 사용자 스키마에 표시되더라도 누락된 값은 기본값을 선택합니다.
또한 AppendRowsRequest 메시지 내 missing_value_interpretations 맵에서 요청 수준 기본값을 지정할 수 있습니다.
각 키는 열의 이름이고 값은 누락된 값을 해석하는 방법을 나타냅니다.
예를 들어 {'col1': NULL_VALUE, 'col2': DEFAULT_VALUE} 맵은 col1의 모든 누락된 값이 NULL로 해석되고 col2의 모든 누락된 값이 테이블 스키마의 col2에 설정된 기본값으로 해석됨을 의미합니다.
필드가 이 맵에 없고 누락된 값이 있으면 누락된 값이 NULL로 해석됩니다.
키는 최상위 열 이름만 될 수 있습니다. 키는 col1.subfield1 같은 구조체 하위 필드일 수 없습니다.
insertAll API 메서드를 사용합니다.
tabledata.insertAll API 메서드는 데이터가 테이블에 기록될 때 행 수준에서 기본값을 채웁니다.
행에 기본값이 있는 열이 없으면 해당 열에 기본값이 적용됩니다.
예를 들어 다음과 같은 테이블 스키마가 있다고 가정해 보겠습니다.
[
{
"name": "a",
"mode": "NULLABLE",
"type": "STRING",
},
{
"name": "b",
"mode": "NULLABLE",
"type": "STRING",
"defaultValueExpression": "'default_b'"
},
{
"name": "c",
"mode": "NULLABLE",
"type": "STRING",
"defaultValueExpression": "'default_c'"
}
]
다음 값을 테이블에 스트리밍한다고 가정해 보겠습니다.
{'a': 'val_a', 'b': 'val_b'}
{'a': 'val_a'}
{}
결과는 다음과 같습니다.
+-------+------------+-----------+ | a | b | c | +-------+------------+-----------+ | val_a | val_b | default_c | | val_a | default_b | default_c | | NULL | default_b | default_c | +-------+------------+-----------+
삽입된 첫 번째 행에는 c 필드의 값이 없으므로 기본값 default_c가 c 열에 기록됩니다. 두 번째 삽입된 행에는 b 또는 c 필드의 값이 없으므로 기본값이 b 및 c 열에 기록됩니다. 삽입된 세 번째 행에는 값이 없습니다. 다른 기본값이 설정되지 않았으므로 a 열에 작성된 값은 NULL입니다. b 및 c 열에 기본값 default_b 및 default_c가 기록됩니다.
기본값 보기
열의 기본값을 보려면 INFORMATION_SCHEMA.COLUMNS 뷰를 쿼리합니다. column_default 열 필드에는 열의 기본값이 포함됩니다. 기본값이 설정되어 있지 않으면 기본값은 NULL입니다. 다음 예시에서는 mytable 테이블의 열 이름과 기본값을 보여줍니다.
SELECT column_name, column_default FROM mydataset.INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'mytable';
결과는 다음과 비슷합니다.
+-------------+----------------+ | column_name | column_default | +-------------+----------------+ | x | CURRENT_TIME() | | y | 5 | | z | NULL | +-------------+----------------+
제한사항
- Legacy SQL을 사용하여 기본값이 있는 테이블에서 읽을 수 있지만 Legacy SQL을 사용하여 기본값이 있는 테이블에 쓸 수는 없습니다.
- 기존 테이블에 새 기본값이 있는 열은 추가할 수 없습니다.
그러나 기본값 없이 열을 추가한 후
ALTER COLUMN SET DEFAULTDDL 문을 사용하여 기본값을 변경할 수 있습니다. - 소스 테이블보다 열이 더 많고 추가 열에 기본값이 포함된 대상 테이블로 소스 테이블을 복사하거나 추가할 수 없습니다. 대신
INSERT destination_table SELECT * FROM source_table을 실행하여 데이터를 복사할 수 있습니다.
다음 단계
- BigQuery에 데이터를 로드하는 방법에 대한 자세한 내용은 데이터 로드 소개를 참조하세요.