BigQuery Region 이관(변경) 프로젝트

 

💡GCP BigQuery 의 Region간 이동 (US to Seoul) 경험을 공유한다.

 

 

 

US Rgeion 에 잘못 생성해 운영중인 BigQuery Dataset을 Seoul Region 으로 옮기려 한다.

 

기 생성된 Dataset의 Region 정보만 변경하고 싶지만, 한번 생성한 Dataset의 Region 변경은 불가능하다.

 

이미 US에 생성되어 데이터가 쌓이고 있기때문에

다시 Seoul Region에 dataset을 생성하고, 데이터를 옮겨야한다.

 

 

그래서 BigQuery Data Transfer Service (이하 DTS) 를 이용하여 Region 변경을 진행하려 한다.

선행작업으로 BigQuery Admin 권한을 부여하면, DTS 권한이 포함된다.

• bigquery.transfers.update
• bigquery.transfers.get

BigQuery Admin 계정을 가진 SA를 생성하여

Google Cloud Console에 upload한다.

 

 

- GCP 에서 제공하는 UI를 이용한 복제

GCP 콘솔의 BIgQuery 에서 UI 형태로 제공하지만,

Dataset 1개씩 수동으로 job을 생성해야 함으로, 복제할 dataset 수가 많은 경우에는 python 을 이용하여 프로젝트 전체의 dataset 복제를 진행하는 편이 수월하다.

 

 

 

 

 

 

 

 

- Cloud Shell 에서, Python을 이용한 복제

Region을 변경하려면,

1. 원본의 dataset(US)의 백업본(Seoul)을 생성한다.
2. 원본 dataset(US)을 삭제한다.
3. 복제된 dataset(Seoul)을 원본dataset(Seoul)의 이름으로 복제한다.

 

이관 시 dataset 에서 procedure, scheduled query, view table, external table, model 들을 확인해야 한다.

 

DTS를 이용해 dataset을 복사할때 skip 되는 부분들이다.

 

1. routine (procedure)

2. scheduled query

# status 0 : active
# sataus 4 : close

from google.cloud import bigquery_datatransfer
from google.cloud import bigquery
import google.api_core.exceptions
import os
import sys

try:
    arg = sys.argv[1]
except IndexError:
    print("The project is required.")
    sys.exit(1)

if ( arg == "1" ):      # project #1
    key_path = "/home/whiseung/workspace/keys/project1.json"
elif  ( arg == "2" ):   # project #2
    key_path = "/home/whiseung/workspace/keys/project2.json"
elif  ( arg == "3" ):   # project #3
    key_path = "/home/whiseung/workspace/keys/project3.json"
else :
    print("The project does not exist.")
    sys.exit(1)

transfer_client = bigquery_datatransfer.DataTransferServiceClient.from_service_account_json(key_path)
client = bigquery.Client.from_service_account_json(key_path)
project = client.project

## us region
parent = transfer_client.common_project_path(project)
## seoul region
# parent = transfer_client.common_project_path(project + "/locations/asia-northeast3")


print(parent)
configs = transfer_client.list_transfer_configs(parent=parent)

print("Configs:")
for config in configs:
    if ( config.destination_dataset_id != "" ):
        print(f"\tDataset: {config.destination_dataset_id}.{config.display_name}, state: {config.state}, Schedule: {config.schedule}" )

3. model : model 은 list만 추출하는 code

from google.cloud import bigquery
import datetime

print("Capture Time : " + str(datetime.datetime.now() + datetime.timedelta(hours=9, minutes=0, seconds=0)))

keys = ["/home/whiseung/workspace/keys/project1.json"
        ,"/home/whiseung/workspace/keys/project2.json"
        ,"/home/whiseung/workspace/keys/project3.json"]

for key in keys:
    client = bigquery.Client.from_service_account_json(key)
    datasets = list(client.list_datasets())  # Make an API request.
    project = client.project

    if datasets:
        for dataset in datasets:
            dst = dataset.dataset_id
            
            ## 백업이 아닌 dataset만
            if ( str(dataset.dataset_id).find('_bak') < 0 and str(dataset.dataset_id).find('_cloocus_backup') < 0 and str(dataset.dataset_id).find('_US') < 0 ):
                models = client.list_models(dst)  # Make an API request.

                for model in models:
                    full_model_id = "{},{},{}".format(
                        model.project, model.dataset_id, model.model_id
                    )
                    friendly_name = model.friendly_name
                    print("{}".format(full_model_id))

4. view table : view table ddl문 추출하는 code

from google.cloud import bigquery
import datetime
import sys

def keySetting():
    try:
        arg = sys.argv[1]
    except IndexError:
        print("The project is required.")
        sys.exit(1)

   if ( arg == "1" ):      # project #1
        key_path = "/home/whiseung/workspace/keys/project1.json"
    elif  ( arg == "2" ):   # project #2
        key_path = "/home/whiseung/workspace/keys/project2.json"
    elif  ( arg == "3" ):   # project #3
        key_path = "/home/whiseung/workspace/keys/project3.json"
    else :
    	print("The project does not exist.")
    	sys.exit(1)

    return key_path


key_path = keySetting()

client = bigquery.Client.from_service_account_json(key_path)
datasets = list(client.list_datasets())  # Make an API request.
project = client.project

if datasets:
    print("-- Capture Time : " + str(datetime.datetime.now() + datetime.timedelta(hours=9, minutes=0, seconds=0)))
    for dataset in datasets:
        dst = dataset.dataset_id
        ## 백업이 아닌 dataset만
        if ( str(dataset.dataset_id).find('_bak') < 0 ):
            query_job_not_like = client.query("SELECT table_catalog, table_schema, table_name, table_type, ddl FROM `{}.{}.INFORMATION_SCHEMA.TABLES`  WHERE table_type = 'VIEW' ORDER BY  table_catalog, table_schema, table_name, table_type ".format(project, dst))
            results = query_job_not_like.result()
            for row in results:
                print("-- {}.{}.{}".format(row[0],row[1],row[2]))
                print("{}".format(row[4]))

5. external table : external table ddl 추출하는 code

from google.cloud import bigquery
import datetime
import sys

def keySetting():
    try:
        arg = sys.argv[1]
    except IndexError:
        print("The project is required.")
        sys.exit(1)

    if ( arg == "1" ):      # project #1
        key_path = "/home/whiseung/workspace/keys/project1.json"
    elif  ( arg == "2" ):   # project #2
        key_path = "/home/whiseung/workspace/keys/project2.json"
    elif  ( arg == "3" ):   # project #3
        key_path = "/home/whiseung/workspace/keys/project3.json"
    else :
        print("The project does not exist.")
        sys.exit(1)

    return key_path

key_path = keySetting()

client = bigquery.Client.from_service_account_json(key_path)
datasets = list(client.list_datasets())  # Make an API request.
project = client.project


if datasets:
    print("-- Capture Time : " + str(datetime.datetime.now() + datetime.timedelta(hours=9, minutes=0, seconds=0)))
    for dataset in datasets:
        dst = dataset.dataset_id
        ## 백업이 아닌 dataset만
        if ( str(dataset.dataset_id).find('_bak') < 0 ):
            query_job_not_like = client.query("SELECT table_catalog, table_schema, table_name, table_type, ddl FROM `{}.{}.INFORMATION_SCHEMA.TABLES`  WHERE table_type = 'EXTERNAL' ORDER BY  table_catalog, table_schema, table_name, table_type ".format(project, dst))
            results = query_job_not_like.result()
            for row in results:
                print("-- {}.{}.{}".format(row[0],row[1],row[2]))
                print("{}".format(row[4]))

 

 

- Python 코드 샘플

https://whiseung.tistory.com/entry/BigQuery-Data-Transfer-Service

BigQuery Data Transfer Service 샘플 코드

- python 코드 실행

https://whiseung.tistory.com/entry/Google-Cloud-Shell-%EA%B0%84%EB%8B%A8-%EC%82%AC%EC%9A%A9%EB%B2%95

Google Cloud Shell 간단 사용법