Udostępnij za pośrednictwem

Jak pozyskiwać dane przy użyciu pg_azure_storage w usłudze Azure Cosmos DB for PostgreSQL

  • Artykuł

DOTYCZY: Usługa Azure Cosmos DB for PostgreSQL (obsługiwana przez rozszerzenie bazy danych Citus do bazy danych PostgreSQL)

W tym artykule pokazano, jak używać rozszerzenia pg_azure_storage PostgreSQL do manipulowania danymi i ładowania ich do usługi Azure Cosmos DB for PostgreSQL bezpośrednio z usługi Azure Blob Storage (ABS). ABS to natywna dla chmury skalowalna, trwała i bezpieczna usługa magazynu. Te cechy sprawiają, że dobrym wyborem jest przechowywanie i przenoszenie istniejących danych do chmury.

Przygotowywanie bazy danych i magazynu obiektów blob

Aby załadować dane z usługi Azure Blob Storage, zainstaluj pg_azure_storage rozszerzenie PostgreSQL w bazie danych:

SELECT * FROM create_extension('azure_storage');

Ważne

Rozszerzenie pg_azure_storage jest dostępne tylko w klastrach usługi Azure Cosmos DB for PostgreSQL z systemem PostgreSQL 13 lub nowszym.

Przygotowaliśmy publiczny zestaw danych demonstracyjnych dla tego artykułu. Aby użyć własnego zestawu danych, postępuj zgodnie z instrukcjami migracji danych lokalnych do magazynu w chmurze, aby dowiedzieć się, jak efektywnie uzyskiwać zestawy danych do usługi Azure Blob Storage.

Uwaga

Wybranie pozycji "Kontener (anonimowy dostęp do odczytu dla kontenerów i obiektów blob)" umożliwi pozyskiwanie plików z usługi Azure Blob Storage przy użyciu ich publicznych adresów URL i wyliczanie zawartości kontenera bez konieczności konfigurowania klucza konta w pg_azure_storage. Kontenery ustawione na poziom dostępu "Prywatny (bez dostępu anonimowego)" lub "Dostęp do obiektów blob (anonimowy dostęp tylko do odczytu dla obiektów blob)" będzie wymagał klucza dostępu.

Wyświetlanie zawartości kontenera

Dla tego przewodnika przedstawiono wstępnie utworzone konto usługi Azure Blob Storage i kontener. Nazwa kontenera to github, i znajduje się na koncie pgquickstart . Przy użyciu funkcji można łatwo zobaczyć, które pliki znajdują się w kontenerze 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

Dane wyjściowe można filtrować przy użyciu regularnej klauzuli SQL WHERE lub przy użyciu prefix parametru funkcji zdefiniowanej przez blob_list użytkownika. Ten ostatni filtruje zwracane wiersze po stronie usługi Azure Blob Storage.

Uwaga

Wyświetlanie zawartości kontenera wymaga konta i klucza dostępu lub kontenera z włączonym dostępem anonimowym.

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

Ładowanie danych z abs

Ładowanie danych za pomocą polecenia COPY

Zacznij od utworzenia przykładowego schematu.

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

Ładowanie danych do tabel staje się tak proste, jak wywoływanie COPY polecenia.

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

Zwróć uwagę, że rozszerzenie rozpoznało, że adresy URL podane do polecenia kopiowania pochodzą z usługi Azure Blob Storage, wskazywane przez nas pliki zostały skompresowane za pomocą narzędzia gzip i zostało również automatycznie obsłużone.

Polecenie COPY obsługuje więcej parametrów i formatów. W powyższym przykładzie format i kompresja zostały automatycznie wybrane na podstawie rozszerzeń plików. Można jednak podać format bezpośrednio podobny do zwykłego COPY polecenia.

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

Obecnie rozszerzenie obsługuje następujące formaty plików:

format opis
CSV Format wartości rozdzielanych przecinkami używany przez kopię bazy danych PostgreSQL
tsv Wartości rozdzielane tabulatorami, domyślny format KOPIOWANIa postgreSQL
dane binarne Format kopiowania danych binarnych PostgreSQL
text Plik zawierający pojedynczą wartość tekstową (na przykład duży kod JSON lub XML)

Ładowanie danych przy użyciu blob_get()

Polecenie COPY jest wygodne, ale ograniczone w elastyczności. Wewnętrznie COPY używa blob_get funkcji , której można użyć bezpośrednio do manipulowania danymi w bardziej złożonych scenariuszach.

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

Uwaga

W powyższym zapytaniu plik jest w pełni pobierany przed LIMIT 3 zastosowaniem.

Dzięki tej funkcji można manipulować danymi na bieżąco w złożonych zapytaniach i importować je jako 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

W powyższym poleceniu przefiltrowaliśmy dane do kont z gravatar_id obecnymi i wielkimi literami identyfikatorów logowania na bieżąco.

Opcje blob_get()

W niektórych sytuacjach może być konieczne sterowanie dokładnie próbami blob_get wykonania przy użyciu decoderparametrów i options . compression

Dekoder można ustawić na auto (wartość domyślna) lub dowolną z następujących wartości:

format opis
CSV Format wartości rozdzielanych przecinkami używany przez kopię bazy danych PostgreSQL
tsv Wartości rozdzielane tabulatorami, domyślny format KOPIOWANIa postgreSQL
dane binarne Format kopiowania danych binarnych PostgreSQL
text Plik zawierający pojedynczą wartość tekstową (na przykład duży kod JSON lub XML)

compression może to być auto (wartość domyślna) none lub gzip.

options Na koniec parametr ma typ jsonb. Istnieją cztery funkcje narzędzi, które ułatwiają tworzenie wartości. Każda funkcja narzędzia jest przeznaczona dla dekodera pasującego do jego nazwy.

Dekoder options, funkcja
CSV options_csv_get
tsv options_tsv
dane binarne options_binary
text options_copy

Patrząc na definicje funkcji, można zobaczyć, które parametry są obsługiwane przez dekoder.

options_csv_get — ogranicznik, null_string, nagłówek, cytat, ucieczka, force_not_null, force_null, content_encoding options_tsv — ogranicznik, null_string, content_encoding options_copy — ogranicznik, null_string, nagłówek, cudzysłów, ucieczka, force_quote, force_not_null, force_null, content_encoding. options_binary - content_encoding

Znając powyższe informacje, możemy odrzucić nagrania z wartością null gravatar_id podczas analizowania.

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

Uzyskiwanie dostępu do magazynu prywatnego

  1. Uzyskiwanie nazwy konta i klucza dostępu

    Bez klucza dostępu nie będziemy mogli wyświetlać listy kontenerów ustawionych na poziomy dostępu prywatny lub 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>')

    Na koncie magazynu otwórz pozycję Klucze dostępu. Skopiuj nazwę konta magazynu i skopiuj sekcję Klucz z klucza key1 (musisz najpierw wybrać pozycję Pokaż obok klucza).

    Zrzut ekranu przedstawiający sekcję Zabezpieczenia i klucze dostępu do sieci > na stronie usługi Azure Blob Storage w witrynie Azure Portal.

  2. Dodawanie konta do pg_azure_storage

    SELECT azure_storage.account_add('mystorageaccount', 'SECRET_ACCESS_KEY');

    Teraz możesz wyświetlić listę kontenerów ustawionych na poziomy dostępu prywatny i blob dla tego magazynu, ale tylko jako citus użytkownik, któremu udzielono azure_storage_admin roli. Jeśli utworzysz nowego użytkownika o nazwie support, domyślnie nie będzie on mógł uzyskiwać dostępu do zawartości kontenera.

    SELECT * FROM azure_storage.blob_list('pgabs','dataverse');

    ERROR:  azure_storage: current user support is not allowed to use storage account pgabs

  3. Zezwalanie użytkownikowi support na używanie określonego konta usługi Azure Blob Storage

    Udzielenie uprawnienia jest tak proste, jak wywoływanie metody account_user_add.

    SELECT * FROM azure_storage.account_user_add('mystorageaccount', 'support');

    W danych wyjściowych account_listpolecenia widzimy dozwolonych użytkowników, którzy wyświetlają wszystkie konta z zdefiniowanymi kluczami dostępu.

    SELECT * FROM azure_storage.account_list();

     account_name     | allowed_users
------------------+---------------
 mystorageaccount | {support}
(1 row)

    Jeśli kiedykolwiek zdecydujesz, że użytkownik nie powinien już mieć dostępu. Wystarczy wywołać metodę account_user_remove.

    SELECT * FROM azure_storage.account_user_remove('mystorageaccount', 'support');

Następne kroki

Gratulacje. Wiesz już, jak załadować dane do usługi Azure Cosmos DB for PostgreSQL bezpośrednio z usługi Azure Blob Storage.

  • Dowiedz się, jak utworzyć pulpit nawigacyjny w czasie rzeczywistym za pomocą usługi Azure Cosmos DB for PostgreSQL.
  • Dowiedz się więcej o pg_azure_storage.
  • Dowiedz się więcej o obsłudze funkcji Postgres COPY.