Copia de datos de PostgreSQL con Azure Data Factory o Synapse Analytics

Artículo
04/24/2024

SE APLICA A: Azure Data Factory Azure Synapse Analytics

Sugerencia

Pruebe Data Factory en Microsoft Fabric, una solución de análisis todo en uno para empresas. Microsoft Fabric abarca todo, desde el movimiento de datos hasta la ciencia de datos, el análisis en tiempo real, la inteligencia empresarial y los informes. Obtenga información sobre cómo iniciar una nueva evaluación gratuita.

En este artículo se resume el uso de la actividad de copia en canalizaciones de Azure Data Factory y Synapse Analytics para copiar datos de una base de datos PostgreSQL. El documento se basa en el artículo de introducción a la actividad de copia que describe información general de la actividad de copia.

Importante

El nuevo conector de PostgreSQL proporciona compatibilidad nativa mejorada con PostgreSQL y un mejor rendimiento. Si usa la versión heredada del conector de PostgreSQL en la solución, que se admite tal cual solo en el caso de la compatibilidad con versiones anteriores, consulte el artículo Conector de PostgreSQL (heredado).

Funcionalidades admitidas

El conector PostgreSQL es compatible para las siguientes funcionalidades:

Funcionalidades admitidas	IR
Actividad de copia (origen/-)	① ②
Actividad de búsqueda	① ②

① Azure Integration Runtime ② Entorno de ejecución de integración autohospedado

Consulte la tabla de almacenes de datos compatibles para ver una lista de almacenes de datos que la actividad de copia admite como orígenes o receptores.

En concreto, este conector PostgreSQL es compatible con la versión 7.4 y posterior de PostgreSQL.

Prerrequisitos

Si el almacén de datos se encuentra en una red local, una red virtual de Azure o una nube privada virtual de Amazon, debe configurar un entorno de ejecución de integración autohospedado para conectarse a él.

Si el almacén de datos es un servicio de datos en la nube administrado, puede usar Azure Integration Runtime. Si el acceso está restringido a las direcciones IP que están aprobadas en las reglas de firewall, puede agregar direcciones IP de Azure Integration Runtime a la lista de permitidos.

También puede usar la característica del entorno de ejecución de integración de red virtual administrada de Azure Data Factory para acceder a la red local sin instalar ni configurar un entorno de ejecución de integración autohospedado.

Consulte Estrategias de acceso a datos para más información sobre los mecanismos de seguridad de red y las opciones que admite Data Factory.

El entorno de Integration Runtime proporciona un controlador de PostgreSQL integrado a partir de la versión 3.7, por lo tanto, no es necesario que instale uno manualmente.

Introducción

Para realizar la actividad de copia con una canalización, puede usar una de los siguientes herramientas o SDK:

Creación de un servicio vinculado a PostgreSQL mediante la interfaz de usuario

Siga estos pasos para crear un servicio vinculado a PostgreSQL en la interfaz de usuario de Azure Portal.

Vaya a la pestaña Administrar del área de trabajo de Azure Data Factory o Synapse y seleccione Servicios vinculados; luego haga clic en Nuevo:
- Azure Data Factory
- Azure Synapse
Busque PostgreSQL y seleccione el conector de PostgreSQL.
Configure los detalles del servicio, pruebe la conexión y cree el nuevo servicio vinculado.

Detalles de configuración del conector

Las secciones siguientes proporcionan detalles sobre las propiedades que se usan para definir entidades de Data Factory específicas del conector PostgreSQL.

Propiedades del servicio vinculado

Las siguientes propiedades son compatibles con el servicio vinculado PostgreSQL:

Propiedad	Descripción	Obligatorio
type	La propiedad type debe establecerse en: PostgreSqlV2.	Sí
server	Especifica el nombre de host (y, opcionalmente, el puerto) en el que se ejecuta PostgreSQL.	Sí
port	Puerto TCP del servidor PostgreSQL.	No
database	Base de datos PostgreSQL a la que se va a conectar.	Sí
username	Nombre de usuario con el que se va a conectar. No es necesario si se usa IntegratedSecurity.	Sí
password	Contraseña con la que se va a conectar. No es necesario si se usa IntegratedSecurity.	Sí
sslMode	Controla si se usa SSL, en función de la compatibilidad con el servidor. - Disable: SSL está deshabilitado. Si el servidor requiere SSL, se producirá un error en la conexión. - Allow: se prefieren conexiones que no son SSL si el servidor las permite, pero permiten conexiones SSL. - Prefer: se prefieren conexiones SSL si el servidor las permite, pero se permiten conexiones sin SSL. - Require: se produce un error en la conexión si el servidor no admite SSL. - Verify-ca: se produce un error en la conexión si el servidor no admite SSL. También comprueba el certificado de servidor. - Verify-full: se produce un error en la conexión si el servidor no admite SSL. También comprueba el certificado de servidor con el nombre del host. Opciones: Disable (0) / Allow (1) / Prefer (2) (valor predeterminado) / Require (3) / Verify-ca (4) / Verify-full (5)	No
authenticationType	Tipo de autenticación para conectarse a la base de datos. Solo admite Básico.	Sí
connectVia	El entorno Integration Runtime que se usará para conectarse al almacén de datos. Obtenga más información en la sección Requisitos previos. Si no se especifica, se usará Azure Integration Runtime.	No
Propiedades de conexión adicionales:
esquema	Establece la ruta de acceso de búsqueda del esquema.	No
pooling	Indica si se debe usar la agrupación de conexiones.	No
connectionTimeout	El tiempo de espera (en segundos) al intentar establecer una conexión antes de finalizar el intento y generar un error.	No
commandTimeout	Tiempo de espera (en segundos) al intentar ejecutar un comando antes de finalizar el intento y generar un error. Establézcalo en cero para que sea infinito.	No
trustServerCertificate	Si se confía en el certificado de servidor sin validarlo.	No
sslCertificate	Ubicación de un certificado de cliente que se va a enviar al servidor.	No
sslKey	Ubicación de una clave de cliente para que un certificado de cliente se envíe al servidor.	No
sslPassword	Contraseña de una clave para un certificado de cliente.	No
readBufferSize	Determina el tamaño del búfer interno que Npgsql usa al leer. Aumentar puede mejorar el rendimiento si se transfieren valores grandes de la base de datos.	No
logParameters	Cuando se habilita, los valores de parámetro se registran cuando se ejecutan comandos.	No
timezone	Obtiene o establece la zona horaria de la sesión.	No
encoding	Obtiene o establece la codificación de .NET que se usará para codificar o descodificar datos de cadena de PostgreSQL.	No

Nota

Para tener la comprobación completa de SSL a través de la conexión ODBC al usar el Integration Runtime autohospedado, debe usar una conexión de tipo ODBC en lugar del conector postgreSQL explícitamente y completar la siguiente configuración:

Configure el DSN en cualquier servidor SHIR.
Coloque el certificado adecuado de PostgreSQL en C:\Windows\ServiceProfiles\DIAHostService\AppData\Roaming\postgresql\root.crt en los servidores SHIR. Aquí es donde el controlador ODBC busca > para el certificado SSL con el objetivo de comprobar cuándo se conecta a la base de datos.
En la conexión de la factoría de datos, use una conexión de tipo ODBC, con la cadena de conexión que apunta al DSN que creó en los servidores SHIR.

Ejemplo:

{
    "name": "PostgreSqlLinkedService",
    "properties": {
        "type": "PostgreSqlV2",
        "typeProperties": {
            "server": "<server>",
            "port": 5432,
            "database": "<database>",
            "username": "<username>",
            "password": {
                "type": "SecureString",
                "value": "<password>"
            },
            "sslmode": <sslmode>,
            "authenticationType": "Basic"
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Ejemplo: Almacenamiento de la contraseña en Azure Key Vault

{
    "name": "PostgreSqlLinkedService",
    "properties": {
        "type": "PostgreSqlV2",
        "typeProperties": {
            "server": "<server>",
            "port": 5432,
            "database": "<database>",
            "username": "<username>",
            "password": {
                "type": "AzureKeyVaultSecret",
                "store": { 
                    "referenceName": "<Azure Key Vault linked service name>",
                    "type": "LinkedServiceReference"
                },
                "secretName": "<secretName>"
            }
            "sslmode": <sslmode>,
            "authenticationType": "Basic"
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Propiedades del conjunto de datos

Si desea ver una lista completa de las secciones y propiedades disponibles para definir conjuntos de datos, consulte el artículo sobre conjuntos de datos. En esta sección se proporciona una lista de las propiedades que admite el conjunto de datos de PostgreSQL.

Para copiar datos de PostgreSQL, se admiten las propiedades siguientes:

Propiedad	Descripción	Obligatorio
type	La propiedad type del conjunto de datos se debe establecer en: PostgreSqlV2Table.	Sí
esquema	Nombre del esquema.	No (si se especifica "query" en el origen de la actividad)
table	Nombre de la tabla.	No (si se especifica "query" en el origen de la actividad)

Ejemplo

{
    "name": "PostgreSQLDataset",
    "properties":
    {
        "type": "PostgreSqlV2Table",
        "linkedServiceName": {
            "referenceName": "<PostgreSQL linked service name>",
            "type": "LinkedServiceReference"
        },
        "annotations": [],
        "schema": [],
        "typeProperties": {
            "schema": "<schema name>",
            "table": "<table name>"
        }
    }
}

Si estaba usando un conjunto de datos de tipo RelationalTable, todavía se admite tal cual, aunque se aconseja usar el nuevo en el futuro.

Propiedades de la actividad de copia

Si desea ver una lista completa de las secciones y propiedades disponibles para definir actividades, consulte el artículo sobre canalizaciones. En esta sección se proporciona una lista de las propiedades que admite el origen de PostgreSQL.

PostgreSQL como origen

Para copiar datos desde PostgreSQL, en la sección source de la actividad de copia se admiten las propiedades siguientes:

Propiedad	Descripción	Obligatorio
type	La propiedad type del origen de la actividad de copia debe establecerse en: PostgreSqlV2Source.	Sí
Query	Use la consulta SQL personalizada para leer los datos. Por ejemplo: `"query": "SELECT * FROM \"MySchema\".\"MyTable\""`.	No (si se especifica "tableName" en el conjunto de datos)

Nota

Los nombres de esquema y tabla distinguen mayúsculas de minúsculas. Incluya los nombres entre comillas dobles "" en la consulta.

Ejemplo:

"activities":[
    {
        "name": "CopyFromPostgreSQL",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "<PostgreSQL input dataset name>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<output dataset name>",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "PostgreSqlV2Source",
                "query": "SELECT * FROM \"MySchema\".\"MyTable\""
            },
            "sink": {
                "type": "<sink type>"
            }
        }
    }
]

Si estaba usando un origen de tipo RelationalSource, todavía se admite tal cual, aunque se aconseja usar el nuevo en el futuro.

Asignación de tipos de datos para PostgreSQL

Al copiar datos desde PostgreSQL, se utilizan las siguientes asignaciones de tipos de datos de PostgreSQL a los tipos de datos provisionales usados internamente por el servicio. Consulte el artículo sobre asignaciones de tipos de datos y esquema para información sobre cómo la actividad de copia asigna el tipo de datos y el esquema de origen al receptor.

Tipo de datos de PostgreSql	Tipo de datos de servicio provisional	Tipo de datos de servicio provisional para PostgreSQL (heredado)
`SmallInt`	`Int16`	`Int16`
`Integer`	`Int32`	`Int32`
`BigInt`	`Int64`	`Int64`
`Decimal` (Precisión <= 28)	`Decimal`	`Decimal`
`Decimal` (Precisión > 28)	No compatible	`String`
`Numeric`	`Decimal`	`Decimal`
`Real`	`Single`	`Single`
`Double`	`Double`	`Double`
`SmallSerial`	`Int16`	`Int16`
`Serial`	`Int32`	`Int32`
`BigSerial`	`Int64`	`Int64`
`Money`	`Decimal`	`String`
`Char`	`String`	`String`
`Varchar`	`String`	`String`
`Text`	`String`	`String`
`Bytea`	`Byte[]`	`Byte[]`
`Timestamp`	`DateTime`	`DateTime`
`Timestamp with time zone`	`DateTime`	`String`
`Date`	`DateTime`	`DateTime`
`Time`	`TimeSpan`	`TimeSpan`
`Time with time zone`	`DateTimeOffset`	`String`
`Interval`	`TimeSpan`	`String`
`Boolean`	`Boolean`	`Boolean`
`Point`	`String`	`String`
`Line`	`String`	`String`
`Iseg`	`String`	`String`
`Box`	`String`	`String`
`Path`	`String`	`String`
`Polygon`	`String`	`String`
`Circle`	`String`	`String`
`Cidr`	`String`	`String`
`Inet`	`String`	`String`
`Macaddr`	`String`	`String`
`Macaddr8`	`String`	`String`
`Tsvector`	`String`	`String`
`Tsquery`	`String`	`String`
`UUID`	`Guid`	`Guid`
`Json`	`String`	`String`
`Jsonb`	`String`	`String`
`Array`	`String`	`String`
`Bit`	`Byte[]`	`Byte[]`
`Bit varying`	`Byte[]`	`Byte[]`
`XML`	`String`	`String`
`IntArray`	`String`	`String`
`TextArray`	`String`	`String`
`NumbericArray`	`String`	`String`
`DateArray`	`String`	`String`
`Range`	`String`	`String`
`Bpchar`	`String`	`String`

Propiedades de la actividad de búsqueda

Para obtener información detallada sobre las propiedades, consulte Actividad de búsqueda.

Actualización del servicio vinculado de PostgreSQL

Estos son los pasos que le ayudarán a actualizar el servicio vinculado de PostgreSQL:

Cree un nuevo servicio vinculado de PostgreSQL y configúrelo haciendo referencia a las propiedades del servicio vinculado.
La asignación de tipos de datos para el servicio vinculado de PostgreSQL más reciente es diferente de aquella de la versión heredada. Para obtener información sobre la asignación de tipos de datos más reciente, consulte Asignación de tipos de datos para PostgreSQL.

Diferencias entre PostgreSQL y PostgreSQL (heredado)

En la tabla siguiente se muestran las diferencias de asignación de tipos de datos entre PostgreSQL y PostgreSQL (heredado).

Tipo de datos de PostgreSQL	Tipo de datos de servicio provisional para PostgreSQL	Tipo de datos de servicio provisional para PostgreSQL (heredado)
Money	Decimal	Cadena
Timestamp with time zone	DateTime	Cadena
Time with time zone	DateTimeOffset	Cadena
Intervalo	TimeSpan	Cadena
BigDecimal	No admitida. Como alternativa, use la función `to_char()` para convertir BigDecimal en String.	Cadena

Para obtener una lista de almacenes de datos que la actividad de copia admite como orígenes y receptores, vea Almacenes de datos que se admiten.