PostgreSQL için Azure Cosmos DB'de pg_azure_storage kullanarak verileri alma

Makale
05/30/2024

ŞUNLAR IÇIN GEÇERLIDIR: PostgreSQL için Azure Cosmos DB (PostgreSQL'e citus veritabanı uzantısıyla desteklenir)

Bu makalede, postgreSQL için Azure Cosmos DB'nize doğrudan Azure Blob Depolama (ABS) verileri işlemek ve yüklemek için pg_azure_storage PostgreSQL uzantısının nasıl kullanılacağı gösterilmektedir. ABS, bulutta yerel olarak ölçeklenebilir, dayanıklı ve güvenli bir depolama hizmetidir. Bu özellikler, mevcut verileri depolamak ve buluta taşımak için iyi bir seçimdir.

Veritabanı ve blob depolamayı hazırlama

Azure Blob Depolama'dan veri yüklemek için PostgreSQL uzantısını pg_azure_storage veritabanınıza yükleyin:

SELECT * FROM create_extension('azure_storage');

Önemli

pg_azure_storage uzantısı yalnızca PostgreSQL 13 ve üzerini çalıştıran PostgreSQL kümeleri için Azure Cosmos DB'de kullanılabilir.

Bu makale için bir genel gösterim veri kümesi hazırladık. Kendi veri kümenizi kullanmak için şirket içi verilerinizi bulut depolamaya geçirerek veri kümelerinizi verimli bir şekilde Azure Blob Depolama nasıl alacağınızı öğrenin.

Not

"Kapsayıcı (kapsayıcılar ve bloblar için anonim okuma erişimi)" seçeneğini belirlediğinizde, Azure Blob Depolama genel URL'lerini kullanarak dosyaları almanıza ve pg_azure_storage'da bir hesap anahtarı yapılandırmanıza gerek kalmadan kapsayıcı içeriğini numaralandırabilirsiniz. "Özel (anonim erişim yok)" veya "Blob (yalnızca bloblar için anonim okuma erişimi)" erişim düzeyine ayarlanmış kapsayıcılar bir erişim anahtarı gerektirir.

Kapsayıcı içeriğini listeleme

Bu nasıl yapılır için önceden oluşturulmuş bir tanıtım Azure Blob Depolama hesabı ve kapsayıcısı vardır. Kapsayıcının adı ve githubhesapta yer alır pgquickstart . işlevini kullanarak azure_storage.blob_list(account, container) kapsayıcıda hangi dosyaların mevcut olduğunu kolayca görebiliriz.

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

Normal bir SQL WHERE yan tümcesi kullanarak veya UDF parametresini prefixblob_list kullanarak çıkışı filtreleyebilirsiniz. İkincisi, Azure Blob Depolama tarafında döndürülen satırları filtreler.

Not

Kapsayıcı içeriğini listelemek için bir hesap ve erişim anahtarı veya anonim erişimin etkinleştirildiği bir kapsayıcı gerekir.

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'den veri yükleme

COPY komutuyla veri yükleme

Örnek şema oluşturarak başlayın.

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');

Verileri tablolara yüklemek komutu çağırmak COPY kadar basit hale gelir.

-- 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';

Uzantının copy komutuna sağlanan URL'lerin Azure Blob Depolama olduğunu, işaret ettiğimiz dosyaların gzip sıkıştırıldığını ve bizim için otomatik olarak işlendiğini fark ettiğini fark edin.

COPY komutu daha fazla parametre ve biçimi destekler. Yukarıdaki örnekte, dosya uzantılarına göre biçim ve sıkıştırma otomatik olarak seçilmiştir. Ancak, biçimi normal COPY komuta doğrudan benzer şekilde sağlayabilirsiniz.

COPY github_users
FROM 'https://pgquickstart.blob.core.windows.net/github/users.csv.gz'
WITH (FORMAT 'csv');

Uzantı şu anda aşağıdaki dosya biçimlerini destekler:

format	açıklama
csv	PostgreSQL COPY tarafından kullanılan virgülle ayrılmış değerler biçimi
Tsv	Sekmeyle ayrılmış değerler, varsayılan PostgreSQL COPY biçimi
ikili	İkili PostgreSQL COPY biçimi
text	Tek bir metin değeri içeren bir dosya (örneğin, büyük JSON veya XML)

blob_get() ile veri yükleme

Komutu COPY kullanışlıdır, ancak esneklik sınırlıdır. Dahili olarak COPY işlevi kullanır blob_get ve bu işlevi daha karmaşık senaryolarda verileri işlemek için doğrudan kullanabilirsiniz.

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

Not

Yukarıdaki sorguda, dosya uygulanmadan önce LIMIT 3 tamamen getirilir.

Bu işlevle, karmaşık sorgularda verileri anında işleyebilir ve içeri aktarmaları olarak INSERT FROM SELECTyapabilirsiniz.

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

Yukarıdaki komutta verileri mevcut olan hesaplara gravatar_id filtreledik ve anında oturum açma işlemlerini büyük harfle yaptık.

blob_get() seçenekleri

Bazı durumlarda, ve options parametrelerini kullanarak compressiondecodertam olarak ne blob_get yapmaya çalıştığınızı denetlemeniz gerekebilir.

Kod çözücü (varsayılan) veya aşağıdaki değerlerden herhangi birine ayarlanabilir auto :

format	açıklama
csv	PostgreSQL COPY tarafından kullanılan virgülle ayrılmış değerler biçimi
Tsv	Sekmeyle ayrılmış değerler, varsayılan PostgreSQL COPY biçimi
ikili	İkili PostgreSQL COPY biçimi
text	Tek bir metin değeri içeren bir dosya (örneğin, büyük JSON veya XML)

compression(varsayılan) none veya gzipolabilir auto .

Son olarak parametresi options türündedir jsonb. Bunun için değer oluşturmaya yardımcı olan dört yardımcı program işlevi vardır. Her yardımcı program işlevi, adıyla eşleşen kod çözücü için belirlenir.

Kod çözücü	options işlevi
csv	`options_csv_get`
Tsv	`options_tsv`
ikili	`options_binary`
text	`options_copy`

İşlev tanımlarına bakarak hangi parametrelerin hangi kod çözücü tarafından desteklendiğine bakabilirsiniz.

options_csv_get - sınırlayıcı, null_string, üst bilgi, alıntı, kaçış, force_not_null, force_null, content_encoding options_tsv - sınırlayıcı, null_string, content_encoding options_copy - sınırlayıcı, null_string, üst bilgi, alıntı, kaçış, force_quote, force_not_null, force_null, content_encoding. options_binary - content_encoding

Yukarıdaki bilgileri bilerek ayrıştırma sırasında null gravatar_id ile kayıtları atabiliriz.

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

Özel depolamaya erişme

Hesap adınızı ve erişim anahtarınızı alma

Erişim anahtarı olmadan Özel veya Blob erişim düzeyleri olarak ayarlanmış kapsayıcıları listelememize izin verilmez.
```
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>')
```
Depolama hesabınızda Erişim anahtarları'nı açın. Depolama hesap adını kopyalayın ve key1 bölümünden Anahtarı kopyalayın (önce anahtarın yanındaki Göster'i seçmeniz gerekir).
pg_azure_storage'a hesap ekleme
```
SELECT azure_storage.account_add('mystorageaccount', 'SECRET_ACCESS_KEY');
```
Artık bu depolama için Özel ve Blob erişim düzeyleri olarak ayarlanmış kapsayıcıları listeleyebilirsiniz, ancak yalnızca rolü kendisine verilmiş olan azure_storage_admin kullanıcı olarak citus listeleyebilirsiniz. adlı supportyeni bir kullanıcı oluşturursanız, kapsayıcı içeriğine varsayılan olarak erişmesine izin verilmez.
```
SELECT * FROM azure_storage.blob_list('pgabs','dataverse');
```
```
ERROR:  azure_storage: current user support is not allowed to use storage account pgabs
```
support Kullanıcının belirli bir Azure Blob Depolama hesabı kullanmasına izin verme

İzin vermek çağırmak account_user_addkadar basittir.
```
SELECT * FROM azure_storage.account_user_add('mystorageaccount', 'support');
```
erişim anahtarları tanımlanmış tüm hesapları gösteren çıktısında account_listizin verilen kullanıcıları görebiliriz.
```
SELECT * FROM azure_storage.account_list();
```
```
 account_name     | allowed_users
------------------+---------------
 mystorageaccount | {support}
(1 row)
```
Buna karar verirseniz kullanıcının artık erişimi olmaması gerekir. Yalnızca öğesini arayın account_user_remove.
```
SELECT * FROM azure_storage.account_user_remove('mystorageaccount', 'support');
```

Sonraki adımlar

Tebrikler, postgreSQL için Azure Cosmos DB'ye doğrudan Azure Blob Depolama veri yüklemeyi öğrendinsiniz.

PostgreSQL için Azure Cosmos DB ile gerçek zamanlı pano oluşturmayı öğrenin.
pg_azure_storage hakkında daha fazla bilgi edinin.
Postgres COPY desteği hakkında bilgi edinin.

Aracılığıyla paylaş