[GCP] Datastream: BigQuery로 PostgreSQL 복제

 

• 활성 계정 이름 목록 표시

gcloud auth list

ACTIVE: *
ACCOUNT: student-03-6fa3524d3040@qwiklabs.net

To set the active account, run:
    $ gcloud config set account `ACCOUNT`

• 프로젝트 ID 목록 표시.

gcloud config list project

[core]
project = qwiklabs-gcp-02-48ffbd2fb025

gcloud 전체 문서는 Google Cloud에서 gcloud CLI 개요 가이드 참고

 

1. 복제를 위한 데이터베이스 만들기

Datastream 복제를 위한 PostgreSQL용 Cloud SQL 데이터베이스 준비

Cloud SQL 데이터베이스 만들기

• 다음 명령어를 실행하여 Cloud SQL API를 사용 설정합니다.

gcloud services enable sqladmin.googleapis.com

• 다음 명령어를 실행하여 PostgreSQL용 Cloud SQL 데이터베이스 인스턴스를 만듭니다.

POSTGRES_INSTANCE=postgres-db
DATASTREAM_IPS=35.247.10.221,35.233.208.195,34.82.253.59,35.247.95.52,34.82.254.46
gcloud sql instances create ${POSTGRES_INSTANCE} \
    --database-version=POSTGRES_14 \
    --cpu=2 --memory=10GB \
    --authorized-networks=${DATASTREAM_IPS} \
    --region=us-west1 \
    --root-password pwd \
    --database-flags=cloudsql.logical_decoding=on

참고: 이 명령어는 us-west1에 데이터베이스를 만듭니다. 다른 리전의 경우 DATASTREAM_IPS를 리전에 맞는 적절한 Datastream 공개 IP로 교체해야 합니다.참고: 데이터베이스 인스턴스가 생성되면 이 인스턴스의 공개 IP를 기록해 두세요. 나중에 Datastream의 연결 프로필을 만들 때 필요합니다.

 

샘플 데이터로 데이터베이스 채우기

Cloud Shell에서 다음 명령어를 실행하여 PostgreSQL 데이터베이스에 연결합니다.

gcloud sql connect postgres-db --user=postgres

비밀번호를 묻는 메시지가 표시되면 pwd를 입력합니다.

데이터베이스에 연결되면 다음 SQL 명령어를 실행하여 샘플 스키마와 테이블을 만듭니다.

CREATE SCHEMA IF NOT EXISTS test;

CREATE TABLE IF NOT EXISTS test.example_table (
id  SERIAL PRIMARY KEY,
text_col VARCHAR(50),
int_col INT,
date_col TIMESTAMP
);

ALTER TABLE test.example_table REPLICA IDENTITY DEFAULT; 

INSERT INTO test.example_table (text_col, int_col, date_col) VALUES
('hello', 0, '2020-01-01 00:00:00'),
('goodbye', 1, NULL),
('name', -987, NOW()),
('other', 2786, '2021-01-01 00:00:00');

복제를 위한 데이터베이스 구성하기

• 다음 SQL 명령어를 실행하여 게시와 복제 슬롯을 만듭니다.

CREATE PUBLICATION test_publication FOR ALL TABLES;
ALTER USER POSTGRES WITH REPLICATION;
SELECT PG_CREATE_LOGICAL_REPLICATION_SLOT('test_replication', 'pgoutput');

 

 

 

작업 2. Datastream 리소스를 만들고 복제 시작하기

이제 데이터베이스가 준비되었으니 Datastream 연결 프로필과 스트림을 생성하여 복제를 시작합니다.

1. 탐색 메뉴에서 애널리틱스 아래의 모든 제품 보기를 클릭한 후 Datastream을 선택합니다.
2. 사용 설정을 클릭하여 Datastream API를 사용 설정합니다.

연결 프로필 만들기

두 개의 연결 프로필을 만듭니다. 하나는 PostgreSQL 소스에 대한 프로필이며 다른 하나는 BigQuery 대상에 대한 프로필입니다.

PostgreSQL 연결 프로필

1. Cloud 콘솔에서 연결 프로필 탭으로 이동하여 프로필 만들기를 클릭합니다.
2. PostgreSQL 연결 프로필 유형을 선택합니다.

1. 연결 프로필 이름에 postgres-cp를 입력합니다.
2. 데이터베이스 연결 세부정보를 입력합니다.
   • 리전: us-west1
   • 이전에 생성한 Cloud SQL 인스턴스의 IP 및 포트(작업 1)
     • Cloud SQL 인스턴스의 IP 주소를 확인하려면 다음 단계를 따르세요.
       • 탐색 메뉴에서 Cloud SQL을 클릭합니다.
       • Cloud SQL 페이지에서 postgres-db라는 PostgreSQL 인스턴스를 찾습니다.
       • 인스턴스의 공개 IP 주소를 복사합니다.
   • 사용자 이름: postgres
   • 비밀번호의 경우 비밀번호 직접 입력을 선택하고 pwd를 입력합니다.
   • 데이터베이스: postgres
3. 계속을 클릭합니다.
4. 암호화 유형에서 없음을 선택합니다.
5. IP 허용 목록 연결 방법을 선택한 후 계속을 클릭합니다.
6. 테스트 실행을 클릭하여 Datastream이 데이터베이스에 도달할 수 있는지 확인합니다.
7. 만들기를 클릭합니다.

BigQuery 연결 프로필

1. Cloud 콘솔에서 연결 프로필 탭으로 이동하여 프로필 만들기를 클릭합니다.

1. BigQuery 연결 프로필 유형을 선택합니다.

1. 연결 프로필 이름에 bigquery-cp를 입력합니다.
2. 리전: us-west1
3. 만들기를 클릭합니다.

스트림 만들기

위에서 생성한 연결 프로필을 연결하고 소스에서 대상으로 스트리밍할 데이터의 구성을 정의하는 스트림을 만듭니다.

• Cloud 콘솔에서 스트림 탭으로 이동한 후 스트림 만들기를 클릭합니다.

스트림 세부정보 정의하기

1. 연결 스트림 이름에 test-stream을 입력합니다.
2. 리전: us-west1
3. PostgreSQL을 소스 유형으로 선택합니다.
4. BigQuery를 대상 유형으로 선택합니다.
5. 계속을 클릭합니다.

소스를 정의하고 테스트하기

1. 이전 단계에서 만든 postgres-cp 연결 프로필을 선택합니다.
2. [선택사항] 테스트 실행을 클릭하여 연결을 테스트합니다.
3. 계속을 클릭합니다.

소스 구성하기

1. 복제 슬롯 이름을 test_replication으로 지정합니다.
2. 게시 이름을 test_publication으로 지정합니다.
3. 복제를 위한 테스트 스키마를 선택합니다.
4. 계속을 클릭합니다.

대상 정의하기

• 이전 단계에서 만든 bigquery-cp 연결 프로필을 선택한 후 계속을 클릭합니다.

대상 구성하기

1. 리전을 선택하고 us-west1을(를) BigQuery 데이터 세트 위치로 선택합니다.
2. 비활성 한도를 0초로 설정합니다.
3. 계속을 클릭합니다.

스트림 검토 및 생성하기

• 마지막으로 검증 실행을 클릭하여 스트림 세부정보를 검증합니다. 검증이 성공적으로 완료되면 만들기 및 시작을 클릭한 다음 만들기 및 시작을 확인합니다.

스트림 상태가 실행 중으로 표시될 때까지 1~2분 정도 기다립니다.

 

작업 3. BigQuery에서 데이터 보기

스트림이 실행 중이므로, 데이터가 BigQuery 데이터 세트로 복제되고 있는지 확인합니다.

• Google Cloud 콘솔의 탐색 메뉴에서 BigQuery로 이동합니다.

팝업에서 완료를 클릭합니다.

1. BigQuery Studio 탐색기에서 프로젝트 노드를 펼쳐 데이터 세트 목록을 확인합니다.
2. 테스트 데이터 세트 노드를 펼칩니다.
3. example_table 테이블을 클릭합니다.
4. 미리보기 탭을 클릭하여 BigQuery의 데이터를 확인합니다.

참고: 미리보기 섹션에 데이터가 표시되는 데 몇 분 정도 걸릴 수 있습니다.

• 미리보기 탭에 '표시할 데이터가 없습니다'라는 메시지가 표시되면 다음 명령어를 사용하여 데이터를 확인하면 됩니다.

SELECT * FROM test.example_table ORDER BY id;

 

작업 4. 소스의 변경사항이 BigQuery에 복제되었는지 확인하기

• Cloud Shell에서 다음 명령어를 실행하여 Cloud SQL 데이터베이스에 연결합니다. 비밀번호는 pwd입니다.

gcloud sql connect postgres-db --user=postgres

 

• 다음 SQL 명령어를 실행하여 데이터를 일부 변경합니다.

INSERT INTO test.example_table (text_col, int_col, date_col) VALUES
('abc', 0, '2022-10-01 00:00:00'),
('def', 1, NULL),
('ghi', -987, NOW());

UPDATE test.example_table SET int_col=int_col*2; 

DELETE FROM test.example_table WHERE text_col = 'abc';

• BigQuery SQL 작업공간을 열고 다음 쿼리를 실행하여 BigQuery의 변경사항을 확인합니다.

SELECT * FROM test.example_table ORDER BY id;