Azure Cosmos DB for PostgreSQL에서 pg_azure_storage를 사용하여 데이터를 수집하는 방법
적용 대상: 
Azure Cosmos DB for PostgreSQL(PostgreSQL에 대한 Citus 데이터베이스 확장 기반)
이 문서에서는 pg_azure_storage PostgreSQL 확장을 사용하여 데이터를 조작하고 ABS(Azure Blob Storage)에서 직접 Azure Cosmos DB for PostgreSQL로 로드하는 방법을 보여 줍니다. ABS는 클라우드 네이티브 스케일링 가능하고 내구성이 뛰어나며 안전한 스토리지 서비스입니다. 이러한 특성으로 기존 데이터를 저장하고 클라우드로 이동하는 것이 좋습니다.
데이터베이스 및 Blob Storage 준비
Azure Blob Storage에서 데이터를 로드하려면 데이터베이스에 pg_azure_storage PostgreSQL 확장을 설치합니다.
SELECT * FROM create_extension('azure_storage');
Important
pg_azure_storage 확장은 PostgreSQL 13 이상을 실행하는 Azure Cosmos DB for PostgreSQL 클러스터에서만 사용할 수 있습니다.
이 문서에 대한 퍼블릭 데모 데이터 세트를 준비했습니다. 사용자 고유의 데이터 세트를 사용하려면 온-프레미스 데이터를 클라우드 스토리지로 마이그레이션하여 데이터 세트를 Azure Blob Storage로 효율적으로 가져오는 방법을 알아봅니다.
참고 항목
"컨테이너(컨테이너 및 Blob에 대한 익명 읽기 액세스)"를 선택하면 퍼블릭 URL을 사용하고 pg_azure_storage에서 계정 키를 구성할 필요 없이 컨테이너 콘텐츠를 열거하여 Azure Blob Storage에서 파일을 수집할 수 있습니다. 액세스 수준 "프라이빗(익명 액세스 없음)" 또는 "Blob(Blob에 대해서만 익명 읽기 액세스)"으로 설정된 컨테이너에는 액세스 키가 필요합니다.
컨테이너 내용 나열
이 방법을 위해 미리 만들어진 Azure Blob Storage 계정 및 컨테이너 데모가 있습니다. 컨테이너의 이름은 github이며 pgquickstart 계정에 있습니다. azure_storage.blob_list(account, container) 함수를 사용하여 컨테이너에 있는 파일을 쉽게 확인할 수 있습니다.
SELECT path, bytes, pg_size_pretty(bytes), content_type
FROM azure_storage.blob_list('pgquickstart','github');
-[ RECORD 1 ]--+-------------------
path | events.csv.gz
bytes | 41691786
pg_size_pretty | 40 MB
content_type | application/x-gzip
-[ RECORD 2 ]--+-------------------
path | users.csv.gz
bytes | 5382831
pg_size_pretty | 5257 kB
content_type | application/x-gzip
일반 SQL WHERE 절을 사용하거나 blob_list UDF의 prefix 매개 변수를 사용하여 출력을 필터링할 수 있습니다. 후자는 Azure Blob Storage 쪽에서 반환된 행을 필터링합니다.
참고 항목
컨테이너 콘텐츠를 나열하려면 익명 액세스가 사용하도록 설정된 계정 및 액세스 키 또는 컨테이너가 필요합니다.
SELECT * FROM azure_storage.blob_list('pgquickstart','github','e');
-[ RECORD 1 ]----+---------------------------------
path | events.csv.gz
bytes | 41691786
last_modified | 2022-10-12 18:49:51+00
etag | 0x8DAAC828B970928
content_type | application/x-gzip
content_encoding |
content_hash | 473b6ad25b7c88ff6e0a628889466aed
SELECT *
FROM azure_storage.blob_list('pgquickstart','github')
WHERE path LIKE 'e%';
-[ RECORD 1 ]----+---------------------------------
path | events.csv.gz
bytes | 41691786
last_modified | 2022-10-12 18:49:51+00
etag | 0x8DAAC828B970928
content_type | application/x-gzip
content_encoding |
content_hash | 473b6ad25b7c88ff6e0a628889466aed
ABS에서 데이터 로드
COPY 명령을 사용하여 데이터 로드
먼저 샘플 스키마를 만듭니다.
CREATE TABLE github_users
(
user_id bigint,
url text,
login text,
avatar_url text,
gravatar_id text,
display_login text
);
CREATE TABLE github_events
(
event_id bigint,
event_type text,
event_public boolean,
repo_id bigint,
payload jsonb,
repo jsonb,
user_id bigint,
org jsonb,
created_at timestamp
);
CREATE INDEX event_type_index ON github_events (event_type);
CREATE INDEX payload_index ON github_events USING GIN (payload jsonb_path_ops);
SELECT create_distributed_table('github_users', 'user_id');
SELECT create_distributed_table('github_events', 'user_id');
테이블에 데이터를 로드하는 것은 COPY 명령을 호출하는 것만큼 간단합니다.
-- download users and store in table
COPY github_users
FROM 'https://pgquickstart.blob.core.windows.net/github/users.csv.gz';
-- download events and store in table
COPY github_events
FROM 'https://pgquickstart.blob.core.windows.net/github/events.csv.gz';
확장에서 복사 명령에 제공된 URL이 Azure Blob Storage, 가리키는 파일이 gzip 압축되었으며 자동으로 처리되었음을 인식했습니다.
COPY 명령은 더 많은 매개 변수 및 형식을 지원합니다. 위의 예제에서는 파일 확장자를 기준으로 형식 및 압축을 자동으로 선택했습니다. 그러나 일반 COPY 명령과 유사한 형식을 제공할 수 있습니다.
COPY github_users
FROM 'https://pgquickstart.blob.core.windows.net/github/users.csv.gz'
WITH (FORMAT 'csv');
현재 확장은 다음 파일 형식을 지원합니다.
| format | description |
|---|---|
| csv | PostgreSQL COPY에서 사용하는 쉼표로 구분된 값 형식 |
| tsv | 탭으로 구분된 값, 기본 PostgreSQL COPY 형식 |
| binary | 이진 PostgreSQL COPY 형식 |
| text | 단일 텍스트 값이 포함된 파일(예: 큰 JSON 또는 XML) |
blob_get()을 사용하여 데이터 로드
COPY 명령은 편리하지만 유연성은 제한적입니다. 내부적으로 COPY는 blob_get 함수를 사용합니다. 이 함수를 직접 사용하여 더 복잡한 시나리오에서 데이터를 조작할 수 있습니다.
SELECT *
FROM azure_storage.blob_get(
'pgquickstart', 'github',
'users.csv.gz', NULL::github_users
)
LIMIT 3;
-[ RECORD 1 ]-+--------------------------------------------
user_id | 21
url | https://api.github.com/users/technoweenie
login | technoweenie
avatar_url | https://avatars.githubusercontent.com/u/21?
gravatar_id |
display_login | technoweenie
-[ RECORD 2 ]-+--------------------------------------------
user_id | 22
url | https://api.github.com/users/macournoyer
login | macournoyer
avatar_url | https://avatars.githubusercontent.com/u/22?
gravatar_id |
display_login | macournoyer
-[ RECORD 3 ]-+--------------------------------------------
user_id | 38
url | https://api.github.com/users/atmos
login | atmos
avatar_url | https://avatars.githubusercontent.com/u/38?
gravatar_id |
display_login | atmos
참고 항목
위의 쿼리에서 LIMIT 3이 적용되기 전에 파일이 완전히 페치됩니다.
이 함수를 사용하면 복잡한 쿼리에서 즉시 데이터를 조작하고 INSERT FROM SELECT으로 가져오기를 수행할 수 있습니다.
INSERT INTO github_users
SELECT user_id, url, UPPER(login), avatar_url, gravatar_id, display_login
FROM azure_storage.blob_get('pgquickstart', 'github', 'users.csv.gz', NULL::github_users)
WHERE gravatar_id IS NOT NULL;
INSERT 0 264308
위의 명령에서 gravatar_id가 있고 로그인이 즉석에서 대문자로 표시된 계정으로 데이터를 필터링했습니다.
blob_get() 옵션
경우에 따라 decoder, compression 및 options 매개 변수를 사용하여 blob_get에서 수행하려는 작업을 정확히 제어해야 할 수 있습니다.
디코더를 auto(기본값) 또는 다음 값 중 하나로 설정할 수 있습니다.
| format | description |
|---|---|
| csv | PostgreSQL COPY에서 사용하는 쉼표로 구분된 값 형식 |
| tsv | 탭으로 구분된 값, 기본 PostgreSQL COPY 형식 |
| binary | 이진 PostgreSQL COPY 형식 |
| text | 단일 텍스트 값이 포함된 파일(예: 큰 JSON 또는 XML) |
compression은 auto(기본값), none 또는 gzip일 수 있습니다.
마지막으로 options 매개 변수는 jsonb 형식입니다. 값을 작성하는 데 도움이 되는 네 가지 유틸리티 함수가 있습니다.
각 유틸리티 함수는 해당 이름과 일치하는 디코더용으로 고안되었습니다.
| 디코더 | options 함수 |
|---|---|
| csv | options_csv_get |
| tsv | options_tsv |
| binary | options_binary |
| text | options_copy |
함수 정의를 살펴보면 어떤 디코더에서 어떤 매개 변수가 지원되는지 확인할 수 있습니다.
options_csv_get - delimiter, null_string, header, quote, escape, force_not_null, force_null, content_encoding options_tsv - delimiter, null_string, content_encoding options_copy - delimiter, null_string, header, quote, escape, force_quote, force_not_null, force_null, content_encoding.
options_binary - content_encoding
위의 내용을 알고 있으면 구문 분석 중에 null gravatar_id를 사용하여 기록을 삭제할 수 있습니다.
INSERT INTO github_users
SELECT user_id, url, UPPER(login), avatar_url, gravatar_id, display_login
FROM azure_storage.blob_get('pgquickstart', 'github', 'users.csv.gz', NULL::github_users,
options := azure_storage.options_csv_get(force_not_null := ARRAY['gravatar_id']));
INSERT 0 264308
프라이빗 스토리지에 액세스
계정 이름 및 액세스 키 가져오기
액세스 키가 없으면 프라이빗 또는 Blob 액세스 수준으로 설정된 컨테이너를 나열할 수 없습니다.
SELECT * FROM azure_storage.blob_list('mystorageaccount','privdatasets');ERROR: azure_storage: missing account access key HINT: Use SELECT azure_storage.account_add('<account name>', '<access key>')스토리지 계정에서 액세스 키를 엽니다. 스토리지 계정 이름을 복사하고 key1 섹션의 키를 복사합니다(먼저 키 옆에 표시를 선택해야 함).
pg_azure_storage에 계정 추가
SELECT azure_storage.account_add('mystorageaccount', 'SECRET_ACCESS_KEY');이제 해당 스토리지에 대한 프라이빗 및 Blob 액세스 수준으로 설정된 컨테이너를
azure_storage_admin역할이 부여된citus사용자로만 나열할 수 있습니다.support라는 새 사용자를 만드는 경우 기본적으로 컨테이너 콘텐츠에 액세스할 수 없습니다.SELECT * FROM azure_storage.blob_list('pgabs','dataverse');ERROR: azure_storage: current user support is not allowed to use storage account pgabssupport사용자가 특정 Azure Blob Storage 계정을 사용하도록 허용사용 권한을 부여하는 것은
account_user_add를 호출하는 것만큼 간단합니다.SELECT * FROM azure_storage.account_user_add('mystorageaccount', 'support');account_list의 출력에서 허용되는 사용자를 볼 수 있습니다. 이 출력에는 액세스 키가 정의된 모든 계정이 표시됩니다.SELECT * FROM azure_storage.account_list();account_name | allowed_users ------------------+--------------- mystorageaccount | {support} (1 row)사용자가 더 이상 액세스할 수 없다고 결정한 경우
account_user_remove를 호출하기만 하면 됩니다.SELECT * FROM azure_storage.account_user_remove('mystorageaccount', 'support');
다음 단계
축하합니다. Azure Blob Storage에서 Azure Cosmos DB for PostgreSQL로 직접 데이터를 로드하는 방법을 배웠습니다.
- Azure Cosmos DB for PostgreSQL을 사용하여 실시간 대시보드를 만드는 방법을 알아봅니다.
- pg_azure_storage에 대해 자세히 알아봅니다.
- Postgres COPY 지원에 대해 알아봅니다.