extensión pg_azure_storage
SE APLICA A: 
Azure Cosmos DB for PostgreSQL (con tecnología de la extensión de base de datos de Citus en PostgreSQL)
La extensión pg_azure_storage permite cargar datos en varios formatos de archivo directamente desde Azure Blob Storage al clúster de Azure Cosmos DB for PostgreSQL. Al habilitar la extensión también se desbloquean nuevas funcionalidades del comando COPY. Los contenedores con el nivel de acceso "Privado" o "Blob" requieren agregar una clave de acceso privada.
Puede crear la extensión ejecutando:
SELECT create_extension('azure_storage');
azure_storage.account_add
La función permite agregar acceso a una cuenta de almacenamiento.
azure_storage.account_add
(account_name_p text
,account_key_p text);
Argumentos
account_name_p
Una cuenta de Azure Blob Storage (ABS) contiene todos los objetos ABS: blobs, archivos, colas y tablas. La cuenta de almacenamiento proporciona un espacio de nombres único para la instancia de ABS que es accesible desde cualquier lugar del mundo a través de HTTPS.
account_key_p
Las claves de acceso de Azure Blob Storage (ABS) son similares a una contraseña raíz de la cuenta de almacenamiento. Siempre debe proteger las claves de acceso. Use Azure Key Vault para administrar y rotar las claves de forma segura. La clave de cuenta se almacena en una tabla a la que puede acceder el superusuario postgres, azure_storage_admin y todos los roles concedidos a esos permisos de administrador. Para ver qué cuentas de almacenamiento existen, use la función account_list.
azure_storage.account_remove
La función permite revocar el acceso de la cuenta a la cuenta de almacenamiento.
azure_storage.account_remove
(account_name_p text);
Argumentos
account_name_p
La cuenta de Azure Blob Storage (ABS) contiene todos los objetos ABS: blobs, archivos, colas y tablas. La cuenta de almacenamiento proporciona un espacio de nombres único para la instancia de ABS que es accesible desde cualquier lugar del mundo a través de HTTPS.
azure_storage.account_user_add
La función permite agregar acceso a un rol en una cuenta de almacenamiento.
azure_storage.account_user_add
( account_name_p text
, user_p regrole);
Argumentos
account_name_p
Una cuenta de Azure Blob Storage (ABS) contiene todos los objetos ABS: blobs, archivos, colas y tablas. La cuenta de almacenamiento proporciona un espacio de nombres único para la instancia de ABS que es accesible desde cualquier lugar del mundo a través de HTTPS.
user_p
Rol creado por el usuario visible en el clúster.
Nota:
Las funciones account_user_add,account_add,account_remove,account_user_remove requieren la configuración de permisos para cada nodo individual del clúster.
azure_storage.account_user_remove
La función permite quitar el acceso de un rol a una cuenta de almacenamiento.
azure_storage.account_user_remove
(account_name_p text
,user_p regrole);
Argumentos
account_name_p
Una cuenta de Azure Blob Storage (ABS) contiene todos los objetos ABS: blobs, archivos, colas y tablas. La cuenta de almacenamiento proporciona un espacio de nombres único para la instancia de ABS que es accesible desde cualquier lugar del mundo a través de HTTPS.
user_p
Rol creado por el usuario visible en el clúster.
azure_storage.account_list
La función enumera la cuenta y el rol que tienen acceso a Azure Blob Storage.
azure_storage.account_list
(OUT account_name text
,OUT allowed_users regrole[]
)
Returns TABLE;
Argumentos
account_name
La cuenta de Azure Blob Storage (ABS) contiene todos los objetos ABS: blobs, archivos, colas y tablas. La cuenta de almacenamiento proporciona un espacio de nombres único para la instancia de ABS que es accesible desde cualquier lugar del mundo a través de HTTPS.
allowed_users
Enumera los usuarios que tienen acceso a Azure Blob Storage.
Tipo de valor devuelto
TABLE
azure_storage.blob_list
La función enumera los archivos de blob disponibles dentro de un contenedor de usuarios con sus propiedades.
azure_storage.blob_list
(account_name text
,container_name text
,prefix text DEFAULT ''::text
,OUT path text
,OUT bytes bigint
,OUT last_modified timestamp with time zone
,OUT etag text
,OUT content_type text
,OUT content_encoding text
,OUT content_hash text
)
Returns SETOF record;
Argumentos
account_name
La storage account name proporciona un espacio de nombres único para los datos de Azure Storage que es accesible desde cualquier lugar del mundo mediante HTTPS.
container_name
Un contenedor organiza un conjunto de blobs, de forma parecida a un directorio en un sistema de archivos. Una cuenta de almacenamiento puede contener un número ilimitado de contenedores y un contenedor puede almacenar un número ilimitado de blobs. Un nombre de contenedor debe ser un nombre DNS válido, ya que forma parte del URI único que se usa para direccionar el contenedor o sus blobs. Siga estas reglas al asignar un nombre a un contenedor:
- Los nombres de contenedor pueden tener entre 3 y 63 caracteres.
- Los nombres de contenedor deben comenzar por una letra o un número, y solo pueden contener letras en minúscula, números y el carácter de guión (-).
- En los nombres de contenedor no se permiten dos o más guiones consecutivos.
El identificador URI de un contenedor es similar a: https://myaccount.blob.core.windows.net/mycontainer
prefix
Devuelve el archivo del contenedor de blobs con iniciales de cadena coincidentes.
path
Ruta de acceso completa del directorio de blobs de Azure.
bytes
Tamaño del objeto de archivo en bytes.
last_modified
Cuándo se modificó por última vez el contenido del archivo.
ETag
Una propiedad ETag se usa para la simultaneidad optimista durante las actualizaciones. No es una marca de tiempo, ya que hay otra propiedad denominada Marca de tiempo que almacena la última vez que se actualizó un registro. Por ejemplo, si carga una entidad y quiere actualizarla, la etiqueta ETag debe coincidir con lo que está almacenado actualmente. Establecer la etiqueta ETag adecuada es importante porque, si tiene varios usuarios editando el mismo elemento, no querrá que sobrescriban los cambios entre sí.
content_type
El objeto Blob representa un blob, que es un objeto similar a un archivo de datos inmutables y sin procesar. Se pueden leer como datos binarios o de texto, o convertirse en ReadableStream para que sus métodos se puedan usar para procesar los datos. Los blobs pueden representar datos que no tienen necesariamente un formato nativo de JavaScript.
content_encoding
Azure Storage permite definir la propiedad Content-Encoding en un blob. En el caso del contenido comprimido, puede establecer la propiedad en GZIP. Cuando el explorador accede al contenido, descomprime automáticamente el contenido.
content_hash
Este hash se utiliza para comprobar la integridad del blob durante el transporte. Cuando se especifica este encabezado, el servicio de almacenamiento compara el hash que ha llegado con el que se envió. Si ambos valores de hash no coinciden, la operación produce un error con el código de estado 400 (solicitud incorrecta).
Tipo de valor devuelto
Registro SETOF
Nota:
Permisos Ahora puede enumerar los contenedores establecidos en los niveles de acceso Privado y Blob para ese almacenamiento, pero solo como el usuario citus user, que tiene el rol azure_storage_admin concedido a él. Si crea un nuevo usuario denominado support, no podrá acceder al contenido del contenedor de forma predeterminada.
azure_storage.blob_get
La función permite cargar el contenido del archivo o archivos desde dentro del contenedor, con compatibilidad agregada sobre el filtrado o la manipulación de datos, antes de la importación.
azure_storage.blob_get
(account_name text
,container_name text
,path text
,decoder text DEFAULT 'auto'::text
,compression text DEFAULT 'auto'::text
,options jsonb DEFAULT NULL::jsonb
)
RETURNS SETOF record;
Hay una versión sobrecargada de la función, que contiene el parámetro rec que permite definir convenientemente el registro de formato de salida.
azure_storage.blob_get
(account_name text
,container_name text
,path text
,rec anyelement
,decoder text DEFAULT 'auto'::text
,compression text DEFAULT 'auto'::text
,options jsonb DEFAULT NULL::jsonb
)
RETURNS SETOF anyelement;
Argumentos
account
La cuenta de almacenamiento proporciona un espacio de nombres único para los datos de Azure Storage que es accesible desde cualquier lugar del mundo mediante HTTPS.
contenedor
Un contenedor organiza un conjunto de blobs, de forma parecida a un directorio en un sistema de archivos. Una cuenta de almacenamiento puede contener un número ilimitado de contenedores y un contenedor puede almacenar un número ilimitado de blobs. Un nombre de contenedor debe ser un nombre DNS válido, ya que forma parte del URI único que se usa para direccionar el contenedor o sus blobs.
path
Nombre del blob existente en el contenedor.
rec
Defina la estructura de salida del registro.
decodificador
Especifique que el descodificador de formato de blobs se puede establecer en automático (valor predeterminado) o en cualquiera de los valores siguientes
descripción del descodificador
| Formato | Descripción |
|---|---|
| csv | Formato de valores separados por comas usado por COPY de PostgreSQL |
| tsv | Valores separados por tabulaciones, el formato COPY predeterminado de PostgreSQL |
| binary | Formato COPY de PostgreSQL binario |
| text | Un archivo que contiene un único valor de texto (por ejemplo, JSON grande o XML) |
compression
Define el formato de compresión. Las opciones disponibles son auto, gzip y none. El uso de la opción auto (valor predeterminado) adivina la compresión en función de la extensión de archivo (.gz == gzip). La opción none obliga a omitir la extensión y no a intentar descodificar. Mientras que gzip fuerza el uso del descodificador gzip (para cuando tenga un archivo comprimido en gzip con una extensión no estándar). Actualmente, no se admiten otros formatos de compresión para la extensión.
opciones
Para controlar encabezados personalizados, separadores personalizados, caracteres de escape, etc., options funciona de forma similar al comando COPY en PostgreSQL, el parámetro utiliza la función blob_get.
Tipo de valor devuelto
SETOF Record / anyelement
Nota:
Hay cuatro funciones de utilidad, que se llaman como parámetros, dentro de blob_get que ayudan a crear valores para ella. Cada función de utilidad se designa para el decodificador que coincide con su nombre.
azure_storage.options_csv_get
La función actúa como una función de utilidad a la que se llama como un parámetro dentro de blob_get, lo que resulta útil para descodificar el contenido csv.
azure_storage.options_csv_get
(delimiter text DEFAULT NULL::text
,null_string text DEFAULT NULL::text
,header boolean DEFAULT NULL::boolean
,quote text DEFAULT NULL::text
,escape text DEFAULT NULL::text
,force_not_null text[] DEFAULT NULL::text[]
,force_null text[] DEFAULT NULL::text[]
,content_encoding text DEFAULT NULL::text
)
Returns jsonb;
Argumentos
delimiter
Especifica el carácter que separa las columnas de cada fila (línea) del archivo. El valor predeterminado es un carácter de tabulación en formato de texto, una coma en formato CSV. Debe ser un carácter de un solo byte.
null_string
Especifica la cadena que representa un valor nulo. El valor predeterminado es \N (barra diagonal inversa-N) en formato de texto y una cadena vacía sin comillas en formato CSV. Es posible que prefiera una cadena vacía incluso en formato de texto para los casos en los que no desea distinguir valores nulos de cadenas vacías.
header
Especifica que el archivo contiene una línea de encabezado con los nombres de cada columna del archivo. En la salida, la primera línea contiene los nombres de columna de la tabla.
comillas
Especifica el carácter de comillas que se va a usar cuando un valor de datos se pone entre comillas. El valor predeterminado es comillas dobles. Debe ser un carácter de un solo byte.
escape
Especifica el carácter que debe aparecer antes de un carácter de datos que coincida con el valor QUOTE. El valor predeterminado es el mismo que el valor QUOTE (por lo que el carácter de comillas se duplica si aparece en los datos). Debe ser un carácter de un solo byte.
force_not_null
No coincide con los valores de las columnas especificadas con la cadena nula. En el caso predeterminado en el que la cadena nula esté vacía, esto significa que los valores vacíos se leen como cadenas de longitud cero en lugar de valores nulos, incluso cuando no están entre comillas.
force_null
Coincide con los valores de las columnas especificadas con la cadena nula, incluso si se puesto entre comillas y se encuentra una coincidencia, establezca el valor como un valor nulo. En el caso predeterminado en el que la cadena nula esté vacía, convierte una cadena vacía entre comillas en un valor nulo.
content_encoding
Especifica que el archivo está codificado en el encoding_name. Si se omite la opción, se usa la codificación del cliente actual.
Tipo de valor devuelto
jsonb
azure_storage.options_copy
La función actúa como una función de utilidad a la que se llama como parámetro dentro de blob_get.
azure_storage.options_copy
(delimiter text DEFAULT NULL::text
,null_string text DEFAULT NULL::text
,header boolean DEFAULT NULL::boolean
,quote text DEFAULT NULL::text
,escape text DEFAULT NULL::text
,force_quote text[] DEFAULT NULL::text[]
,force_not_null text[] DEFAULT NULL::text[]
,force_null text[] DEFAULT NULL::text[]
,content_encoding text DEFAULT NULL::text
)
Returns jsonb;
Argumentos
delimiter
Especifica el carácter que separa las columnas de cada fila (línea) del archivo. El valor predeterminado es un carácter de tabulación en formato de texto, una coma en formato CSV. Debe ser un carácter de un solo byte.
null_string
Especifica la cadena que representa un valor nulo. El valor predeterminado es \N (barra diagonal inversa-N) en formato de texto y una cadena vacía sin comillas en formato CSV. Es posible que prefiera una cadena vacía incluso en formato de texto para los casos en los que no desea distinguir valores nulos de cadenas vacías.
header
Especifica que el archivo contiene una línea de encabezado con los nombres de cada columna del archivo. En la salida, la primera línea contiene los nombres de columna de la tabla.
comillas
Especifica el carácter de comillas que se va a usar cuando un valor de datos se pone entre comillas. El valor predeterminado es comillas dobles. Debe ser un carácter de un solo byte.
escape
Especifica el carácter que debe aparecer antes de un carácter de datos que coincida con el valor QUOTE. El valor predeterminado es el mismo que el valor QUOTE (por lo que el carácter de comillas se duplica si aparece en los datos). Debe ser un carácter de un solo byte.
force_quote
Obliga a que se usen comillas para todos los valores que no son nulos en cada columna especificada. La salida nula nunca se pone entre comillas. Si se especifica *, los valores que no son nulos se citan en todas las columnas.
force_not_null
No coincide con los valores de las columnas especificadas con la cadena nula. En el caso predeterminado en el que la cadena nula esté vacía, esto significa que los valores vacíos se leen como cadenas de longitud cero en lugar de valores nulos, incluso cuando no están entre comillas.
force_null
Coincide con los valores de las columnas especificadas con la cadena nula, incluso si se puesto entre comillas y se encuentra una coincidencia, establezca el valor como un valor nulo. En el caso predeterminado en el que la cadena nula esté vacía, convierte una cadena vacía entre comillas en un valor nulo.
content_encoding
Especifica que el archivo está codificado en el encoding_name. Si se omite la opción, se usa la codificación del cliente actual.
Tipo de valor devuelto
jsonb
azure_storage.options_tsv
La función actúa como una función de utilidad a la que se llama como parámetro dentro de blob_get. Resulta útil para descodificar el contenido de tsv.
azure_storage.options_tsv
(delimiter text DEFAULT NULL::text
,null_string text DEFAULT NULL::text
,content_encoding text DEFAULT NULL::text
)
Returns jsonb;
Argumentos
delimiter
Especifica el carácter que separa las columnas de cada fila (línea) del archivo. El valor predeterminado es un carácter de tabulación en formato de texto, una coma en formato CSV. Debe ser un carácter de un solo byte.
null_string
Especifica la cadena que representa un valor nulo. El valor predeterminado es \N (barra diagonal inversa-N) en formato de texto y una cadena vacía sin comillas en formato CSV. Es posible que prefiera una cadena vacía incluso en formato de texto para los casos en los que no desea distinguir valores nulos de cadenas vacías.
content_encoding
Especifica que el archivo está codificado en el encoding_name. Si se omite la opción, se usa la codificación del cliente actual.
Tipo de valor devuelto
jsonb
azure_storage.options_binary
La función actúa como una función de utilidad a la que se llama como parámetro dentro de blob_get. Resulta útil para descodificar el contenido binario.
azure_storage.options_binary
(content_encoding text DEFAULT NULL::text)
Returns jsonb;
Argumentos
content_encoding
Especifica que el archivo está codificado en el encoding_name. Si se omite esta opción, se usa la codificación del cliente actual.
Tipo de valor devuelto
jsonb
Nota:
Permisos Ahora puede enumerar los contenedores establecidos en los niveles de acceso Privado y Blob para ese almacenamiento, pero solo como el usuario citus user, que tiene el rol azure_storage_admin concedido a él. Si crea un nuevo usuario denominado "support" ("asistencia"), no podrá acceder al contenido del contenedor de forma predeterminada.
Ejemplos
Los ejemplos usados usan una cuenta de almacenamiento de Azure de ejemplo (pgquickstart) con archivos personalizados cargados para agregar a la cobertura de diferentes casos de uso. Podemos empezar creando una tabla que se usa en el conjunto de ejemplo utilizado.
CREATE TABLE IF NOT EXISTS public.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 without time zone
);
Adición de la clave de acceso de la cuenta de almacenamiento (obligatoria para el nivel de acceso = privado)
En el ejemplo, se muestra cómo agregar la clave de acceso para la cuenta de almacenamiento para obtener acceso con el fin de realizar consultas desde una sesión en el clúster de Azure Cosmos DB for Postgres.
SELECT azure_storage.account_add('pgquickstart', 'SECRET_ACCESS_KEY');
Sugerencia
En la cuenta de almacenamiento, abra Claves de acceso. Copie el Nombre de la cuenta de almacenamiento y copie la sección Clave de key1 (primero debe seleccionar Mostrar junto a la clave).
Eliminación de la clave de acceso de la cuenta de almacenamiento
En el ejemplo, se muestra cómo quitar la clave de acceso de una cuenta de almacenamiento. Esta acción daría lugar a la eliminación del acceso a los archivos hospedados en un cubo privado en el contenedor.
SELECT azure_storage.account_remove('pgquickstart');
Adición de acceso para un rol a Azure Blob Storage
SELECT * FROM azure_storage.account_user_add('pgquickstart', 'support');
Enumeración de todos los roles con acceso en Azure Blob Storage
SELECT * FROM azure_storage.account_list();
Eliminación de los roles con acceso en Azure Blob Storage
SELECT * FROM azure_storage.account_user_remove('pgquickstart', 'support');
Enumeración de los objetos dentro de un contenedor public
SELECT * FROM azure_storage.blob_list('pgquickstart','publiccontainer');
Enumeración de los objetos dentro de un contenedor private
SELECT * FROM azure_storage.blob_list('pgquickstart','privatecontainer');
Nota:
La adición de la clave de acceso es obligatoria.
Enumeración de los objetos con iniciales de cadena específicas dentro del contenedor público
SELECT * FROM azure_storage.blob_list('pgquickstart','publiccontainer','e');
O bien
SELECT * FROM azure_storage.blob_list('pgquickstart','publiccontainer') WHERE path LIKE 'e%';
Lectura del contenido de un objeto en un contenedor
La función blob_get recupera un archivo de Blob Storage. Para que blob_get sepa cómo analizar los datos, puede pasar un valor (NULL::table_name), que tiene el mismo formato que el archivo.
SELECT * FROM azure_storage.blob_get
('pgquickstart'
,'publiccontainer'
,'events.csv.gz'
, NULL::events)
LIMIT 5;
Como alternativa, podemos definir explícitamente las columnas de la cláusula FROM.
SELECT * FROM azure_storage.blob_get('pgquickstart','publiccontainer','events.csv')
AS res (
event_id BIGINT
,event_type TEXT
,event_public BOOLEAN
,repo_id BIGINT
,payload JSONB
,repo JSONB
,user_id BIGINT
,org JSONB
,created_at TIMESTAMP WITHOUT TIME ZONE)
LIMIT 5;
Uso de la opción de descodificador
El ejemplo muestra el uso de la opción decoder. Por lo general, el formato se deduce de la extensión del archivo, pero, cuando el contenido del archivo no tiene una extensión coincidente, puede pasar el argumento de descodificador.
SELECT * FROM azure_storage.blob_get
('pgquickstart'
,'publiccontainer'
,'events'
, NULL::events
, decoder := 'csv')
LIMIT 5;
Uso de la compresión con la opción de descodificador
En el ejemplo, se muestra cómo aplicar el uso de la compresión gzip en un archivo comprimido gzip sin una extensión .gz estándar.
SELECT * FROM azure_storage.blob_get
('pgquickstart'
,'publiccontainer'
,'events-compressed'
, NULL::events
, decoder := 'csv'
, compression := 'gzip')
LIMIT 5;
Importar contenido filtrado y modificar antes de cargar desde el objeto de formato csv
En el ejemplo se muestra la posibilidad de filtrar y modificar el contenido que se importa desde el objeto en el contenedor antes de cargarlo en una tabla SQL.
SELECT concat('P-',event_id::text) FROM azure_storage.blob_get
('pgquickstart'
,'publiccontainer'
,'events.csv'
, NULL::events)
WHERE event_type='PushEvent'
LIMIT 5;
Consulta del contenido del archivo con encabezados, separadores personalizados, caracteres de escape
Puede usar separadores personalizados y caracteres de escape al pasar el resultado de azure_storage.options_copy al argumento options.
SELECT * FROM azure_storage.blob_get
('pgquickstart'
,'publiccontainer'
,'events_pipe.csv'
,NULL::events
,options := azure_storage.options_csv_get(delimiter := '|' , header := 'true')
);
Consulta de agregación sobre el contenido de un objeto en el contenedor
De este modo, puede consultar datos sin importarlos.
SELECT event_type,COUNT(1) FROM azure_storage.blob_get
('pgquickstart'
,'publiccontainer'
,'events.csv'
, NULL::events)
GROUP BY event_type
ORDER BY 2 DESC
LIMIT 5;