Plantilla de PostgreSQL a BigQuery

La plantilla de PostgreSQL a BigQuery es una canalización por lotes que copia los datos de una tabla de PostgreSQL a una tabla de BigQuery existente. Esta canalización usa JDBC para conectarse a PostgreSQL. Para obtener una capa adicional de protección, también puedes pasar una clave de Cloud KMS junto con un nombre de usuario, contraseña y parámetros de string de conexión codificados en Base64 encriptados con la clave de Cloud KMS. Para obtener más información sobre la encriptación de tus parámetros de nombre de usuario, contraseña y string de conexión, consulta el extremo de encriptación de la API de Cloud KMS.

Requisitos de la canalización

  • La tabla de BigQuery debe existir antes de ejecutar la canalización.
  • La tabla de BigQuery debe tener un esquema compatible.
  • La base de datos relacional debe ser accesible desde la subred en la que se ejecuta Dataflow.

Parámetros de la plantilla

Parámetros obligatorios

  • driverJars: Lista separada por comas de los archivos JAR del controlador. (Ejemplo: gs://your-bucket/driver_jar1.jar,gs://your-bucket/driver_jar2.jar).
  • driverClassName: El nombre de la clase del controlador de JDBC. (Ejemplo: com.mysql.jdbc.Driver).
  • connectionURL: la cadena de URL de conexión de JDBC. Por ejemplo, jdbc:mysql://some-host:3306/sampledb Puedes pasar este valor como una cadena encriptada con una clave de Cloud KMS y, luego, codificada en Base64. Quita los caracteres de espacio en blanco de la cadena codificada en base64. Ten en cuenta la diferencia entre una cadena de conexión de base de datos de Oracle que no es de RAC (jdbc:oracle:thin:@some-host:<port>:<sid>) y una cadena de conexión de base de datos de Oracle RAC (jdbc:oracle:thin:@//some-host[:<port>]/<service_name>). (Ejemplo: jdbc:mysql://some-host:3306/sampledb).
  • outputTable: la ubicación de la tabla de salida de BigQuery. (Ejemplo: <PROJECT_ID>:<DATASET_NAME>.<TABLE_NAME>).
  • bigQueryLoadingTemporaryDirectory: el directorio temporal para el proceso de carga de BigQuery (Ejemplo: gs://your-bucket/your-files/temp_dir).

Parámetros opcionales

  • connectionProperties: la cadena de propiedades para usar en la conexión de JDBC. El formato de la cadena debe ser [propertyName=property;]*. Para obtener más información, consulta Propiedades de la configuración (https://dev.mysql.com/doc/connector-j/8.1/en/connector-j-reference-configuration-properties.html) en la documentación de MySQL. (Ejemplo: unicode=true;characterEncoding=UTF-8).
  • username: el nombre de usuario que se utilizará en la conexión de JDBC. Puedes pasar este valor como una cadena encriptada con una clave de Cloud KMS y, luego, codificada en Base64. Quita los caracteres de espacio en blanco de la cadena codificada en base64.
  • password: la contraseña para usar en la conexión de JDBC. Puedes pasar este valor como una cadena encriptada con una clave de Cloud KMS y, luego, codificada en Base64. Quita los caracteres de espacio en blanco de la cadena codificada en base64.
  • query: la consulta que se ejecutará en la fuente para extraer los datos. Ten en cuenta que, aunque algunos tipos de SQL de JDBC y BigQuery comparten el mismo nombre, tienen algunas diferencias. Estas son algunas asignaciones de tipos importantes de SQL -> BigQuery que se deben tener en cuenta: DATETIME --> TIMESTAMP

Es posible que se requiera la conversión de tipos si tus esquemas no coinciden. (Ejemplo: selecciona * de sampledb.sample_table).

  • KMSEncryptionKey: la clave de encriptación de Cloud KMS que se usa para desencriptar el nombre de usuario, la contraseña y la cadena de conexión. Si pasas una clave de Cloud KMS, también debes encriptar el nombre de usuario, la contraseña y la cadena de conexión. (Ejemplo: projects/your-project/locations/global/keyRings/your-keyring/cryptoKeys/your-key).
  • useColumnAlias: si se configura como true, la canalización usa el alias de la columna (AS) en lugar desu nombre para asignar las filas a BigQuery. La configuración predeterminada es false.
  • isTruncate: si se configura como true, la canalización se trunca antes de cargar los datos en BigQuery. El valor predeterminado es false, lo que hace que la canalización agregue datos.
  • partitionColumn: si este parámetro se proporciona con el nombre de table definido como parámetro opcional, JdbcIO lee la tabla en paralelo ejecutando varias instancias de la consulta en la misma tabla (subconsulta) usando rangos. Por el momento, solo admite columnas de partición Long.
  • table: la tabla desde la que se debe leer cuando se usan particiones. Este parámetro también acepta una subconsulta entre paréntesis (Por ejemplo: [selecciona ID, nombre de Persona] como subconsulta).
  • numPartitions: La cantidad de particiones. Con el límite inferior y superior, este valor forma particiones de segmentación para las expresiones de cláusula WHERE generadas que se usan para dividir la columna de partición de manera uniforme. Cuando la entrada es menor que 1, se establece el número en 1.
  • lowerBound: el límite inferior que se usará en el esquema de partición. Si no se proporciona, Apache Beam infiere este valor de manera automática para los tipos compatibles.
  • upperBound: el límite superior que se usará en el esquema de partición. Si no se proporciona, Apache Beam infiere este valor de manera automática para los tipos compatibles.
  • fetchSize: la cantidad de filas que se recuperarán de la base de datos a la vez. No se usa para lecturas particionadas. La configuración predeterminada es 50,000.
  • createDisposition: la CreateDisposition de BigQuery que se usará. Por ejemplo, CREATE_IF_NEEDED o CREATE_NEVER. Configuración predeterminada: CREATE_NEVER.
  • bigQuerySchemaPath: La ruta de Cloud Storage para el esquema JSON de BigQuery. Si createDisposition se establece como CREATE_IF_NEEDED, se debe especificar este parámetro. (Ejemplo: gs://your-bucket/your-schema.json).
  • disabledAlgorithms: algoritmos separados por comas que se deben inhabilitar. Si este valor se establece como ninguno, no se inhabilita ningún algoritmo. Ten cuidado con este parámetro, ya que los algoritmos inhabilitados de forma predeterminada podrían tener vulnerabilidades o problemas de rendimiento. (Ejemplo: SSLv3, RC4).
  • extraFilesToStage: Rutas de Cloud Storage separadas por comas o Secrets de Secret Manager para los archivos que se deben almacenar en etapa intermedia en el trabajador. Estos archivos se guardan en el directorio /extra_files en cada trabajador. (Ejemplo: gs://
  • defaultLogLevel: configura el nivel de registro en los trabajadores. Las opciones admitidas son OFF, ERROR, WARN, INFO, DEBUG, TRACE. Configuración predeterminada: INFO.
  • useStorageWriteApi: si es true, la canalización usa la API de BigQuery Storage Write (https://cloud.google.com/bigquery/docs/write-api). El valor predeterminado es false. Para obtener más información, consulta Usa la API de Storage Write (https://beam.apache.org/documentation/io/built-in/google-bigquery/#storage-write-api).
  • useStorageWriteApiAtLeastOnce: cuando usas la API de Storage Write, se especifica la semántica de escritura. Para usar una semántica de al menos una vez (https://beam.apache.org/documentation/io/built-in/google-bigquery/#at-least-once-semantics), configura el parámetro en true. Para usar una semántica de una y solo una vez, configura el parámetro en false. Este parámetro se aplica solo cuando useStorageWriteApi es true. El valor predeterminado es false.

Ejecuta la plantilla

Console

  1. Ve a la página Crear un trabajo a partir de una plantilla de Dataflow.
    2. Ir a Crear un trabajo a partir de una plantilla
  2. En el campo Nombre del trabajo, ingresa un nombre de trabajo único.
  3. Opcional: Para Extremo regional, selecciona un valor del menú desplegable. La región predeterminada es us-central1.

    Para obtener una lista de regiones en las que puedes ejecutar un trabajo de Dataflow, consulta Ubicaciones de Dataflow.

  4. En el menú desplegable Plantilla de Dataflow, selecciona the PostgreSQL to BigQuery template.
  5. En los campos de parámetros proporcionados, ingresa los valores de tus parámetros.
  6. Haga clic en Ejecutar trabajo.

gcloud

En tu shell o terminal, ejecuta la plantilla:

gcloud dataflow flex-template run JOB_NAME \
    --project=PROJECT_ID \
    --region=REGION_NAME \
    --template-file-gcs-location=gs://dataflow-templates-REGION_NAME/VERSION/flex/PostgreSQL_to_BigQuery \
    --parameters \
connectionURL=JDBC_CONNECTION_URL,\
query=SOURCE_SQL_QUERY,\
outputTable=PROJECT_ID:DATASET.TABLE_NAME,
bigQueryLoadingTemporaryDirectory=PATH_TO_TEMP_DIR_ON_GCS,\
connectionProperties=CONNECTION_PROPERTIES,\
username=CONNECTION_USERNAME,\
password=CONNECTION_PASSWORD,\
KMSEncryptionKey=KMS_ENCRYPTION_KEY

Reemplaza lo siguiente:

  • JOB_NAME: Es el nombre del trabajo que elijas
  • VERSION: Es la versión de la plantilla que deseas usar.

    Puedes usar los siguientes valores:

    • latest para usar la última versión de la plantilla, que está disponible en la carpeta superior non-dated en el bucket gs://dataflow-templates-REGION_NAME/latest/
    • el nombre de la versión, como 2023-09-12-00_RC00, para usar una versión específica de la plantilla, que se puede encontrar anidada en la carpeta superior con fecha correspondiente en el bucket gs://dataflow-templates-REGION_NAME/
  • REGION_NAME: La región en la que deseas implementar tu trabajo de Dataflow, por ejemplo, us-central1
  • JDBC_CONNECTION_URL: Es la URL de conexión de JDBC
  • SOURCE_SQL_QUERY: La consulta en SQL que se ejecutará en la base de datos de origen.
  • DATASET: Es el conjunto de datos de BigQuery.
  • TABLE_NAME: Es el nombre de la tabla de BigQuery.
  • PATH_TO_TEMP_DIR_ON_GCS: Es la ruta de acceso de Cloud Storage al directorio temporal.
  • CONNECTION_PROPERTIES: Las propiedades de conexión de JDBC, si es necesario.
  • CONNECTION_USERNAME: Es el nombre de usuario de la conexión de JDBC.
  • CONNECTION_PASSWORD: Es la contraseña de la conexión de JDBC.
  • KMS_ENCRYPTION_KEY: La clave de encriptación de Cloud KMS.

API

Para ejecutar la plantilla con la API de REST, envía una solicitud POST HTTP. Para obtener más información de la API y sus permisos de autorización, consulta projects.templates.launch.

POST https://dataflow.googleapis.com/v1b3/projects/PROJECT_ID/locations/LOCATION/flexTemplates:launch
{
  "launchParameter": {
    "jobName": "JOB_NAME",
    "containerSpecGcsPath": "gs://dataflow-templates-LOCATION/VERSION/flex/PostgreSQL_to_BigQuery"
    "parameters": {
      "connectionURL": "JDBC_CONNECTION_URL",
      "query": "SOURCE_SQL_QUERY",
      "outputTable": "PROJECT_ID:DATASET.TABLE_NAME",
      "bigQueryLoadingTemporaryDirectory": "PATH_TO_TEMP_DIR_ON_GCS",
      "connectionProperties": "CONNECTION_PROPERTIES",
      "username": "CONNECTION_USERNAME",
      "password": "CONNECTION_PASSWORD",
      "KMSEncryptionKey":"KMS_ENCRYPTION_KEY"
    },
    "environment": { "zone": "us-central1-f" }
  }
}

Reemplaza lo siguiente:

  • PROJECT_ID: El ID del proyecto de Google Cloud en el que deseas ejecutar el trabajo de Dataflow.
  • JOB_NAME: Es el nombre del trabajo que elijas
  • VERSION: Es la versión de la plantilla que deseas usar.

    Puedes usar los siguientes valores:

    • latest para usar la última versión de la plantilla, que está disponible en la carpeta superior non-dated en el bucket gs://dataflow-templates-REGION_NAME/latest/
    • el nombre de la versión, como 2023-09-12-00_RC00, para usar una versión específica de la plantilla, que se puede encontrar anidada en la carpeta superior con fecha correspondiente en el bucket gs://dataflow-templates-REGION_NAME/
  • LOCATION: La región en la que deseas implementar tu trabajo de Dataflow, por ejemplo, us-central1
  • JDBC_CONNECTION_URL: Es la URL de conexión de JDBC
  • SOURCE_SQL_QUERY: La consulta en SQL que se ejecutará en la base de datos de origen.
  • DATASET: Es el conjunto de datos de BigQuery.
  • TABLE_NAME: Es el nombre de la tabla de BigQuery.
  • PATH_TO_TEMP_DIR_ON_GCS: Es la ruta de acceso de Cloud Storage al directorio temporal.
  • CONNECTION_PROPERTIES: Las propiedades de conexión de JDBC, si es necesario.
  • CONNECTION_USERNAME: Es el nombre de usuario de la conexión de JDBC.
  • CONNECTION_PASSWORD: Es la contraseña de la conexión de JDBC.
  • KMS_ENCRYPTION_KEY: La clave de encriptación de Cloud KMS.

¿Qué sigue?