Load MySQL data into BigQuery

Console

1. Go to the Data transfers page.

   Go to Data transfers

2. Click add Create transfer.

3. In the Source type section, for Source, select MySQL.

4. In the Data source details section, do the following:

   • For Network attachment, select an existing network attachment or click Create Network Attachment. For more information, see the Network connections section of this document.
   • For Host, enter the hostname or IP address of the MySQL database server.
   • For Port number, enter the port number for the MySQL database server.
   • For Database name, enter the name of the MySQL database.
   • For Username, enter the username of the MySQL user initiating the MySQL database connection.
   • For Password, enter the password of the MySQL user initiating the MySQL database connection.
   • For TLS Mode, select an option from the menu. For more information about TLS modes, see TLS configuration.
   • For Trusted PEM Certificate, enter the public certificate of the certificate authority (CA) that issued the TLS certificate of the database server. For more information, see Trusted Server Certificate (PEM).
   • For Enable legacy mapping, select true (default) to use the legacy data type mapping. Select false to use the updated data type mapping. If you are making an incremental transfer, this value must be false. For more information about the data type mapping updates, see March 16, 2027.
   • For Ingestion type, select Full or Incremental.
     • If you select Incremental (Preview), for Write mode, select either Append or Upsert. For more information about the different write modes, see Full or incremental transfers.

   • For MySQL objects to transfer, click Browse.

     Select any objects to be transferred to the BigQuery destination dataset. You can also manually enter any objects to include in the data transfer in this field.

     • If you have selected Append as your incremental write mode, you must select a column as the watermark column.
     • If you have selected Upsert as your incremental write mode, you must select a column as the watermark column, and then select one or more columns as the primary key.

5. In the Transfer config name section, for Display name, enter a name for the transfer. The transfer name can be any value that lets you identify the transfer if you need to modify it later.

6. In the Schedule options section, do the following:

   • Select a repeat frequency. If you select the Hours, Days (default), Weeks, or Months option, you must also specify a frequency. You can also select the Custom option to create a more specific repeat frequency. If you select the On-demand option, this data transfer only runs when you manually trigger the transfer.
   • If applicable, select either the Start now or Start at a set time option and provide a start date and run time.

7. In the Destination settings section, for Dataset, select the dataset that you created to store your data, or click Create new dataset and create one to use as the destination dataset.

8. Optional: In the Notification options section, do the following:

   • To enable email notifications, click the Email notifications toggle to the on position. When you enable this option, the transfer administrator receives an email notification when a transfer run fails.
   • To configure Pub/Sub run notifications for your transfer, click the Pub/Sub notifications toggle to the on position. You can select your topic name or click Create a topic to create one.

9. Click Save.

bq

Enter the bq mk command and supply the transfer creation flag --transfer_config:

bq mk
    --transfer_config
    --project_id=PROJECT_ID
    --data_source=DATA_SOURCE
    --display_name=DISPLAY_NAME
    --target_dataset=DATASET
    --params='PARAMETERS'

Replace the following:

• PROJECT_ID (optional): your Cloud de Confiance project ID. If the --project_id flag isn't supplied to specify a particular project, the default project is used.
• DATA_SOURCE: the data source, which is mysql.
• DISPLAY_NAME: the display name for the data transfer configuration. The transfer name can be any value that lets you identify the transfer if you need to modify it later.
• DATASET: the target dataset for the data transfer configuration.

• PARAMETERS: the parameters for the created transfer configuration in JSON format. For example: --params='{"param":"param_value"}'. The following are the parameters for a MySQL transfer:

  • connector.networkAttachment (optional): the name of the network attachment to connect to the MySQL database.
  • connector.database: the name of the MySQL database.
  • connector.endpoint.host: the hostname or IP address of the database.
  • connector.endpoint.port: the port number of the database.
  • connector.authentication.username: the username of the database user.
  • connector.authentication.password: the password of the database user. connector.connectionType
  • connector.connectionType (optional): the connection type to determine the connection URL. This can be SERVICE, SID, or TNS. When not provided, this defaults to SERVICE.
  • connector.legacyMapping: set to true (default) to use the legacy data type mapping. Set to false to use the updated data type mapping. If you are making an incremental transfer, this value must be false. For more information about the data type mapping updates, see March 16, 2027.
  • connector.tls.mode: specify a TLS configuration to use with this transfer:
    • ENCRYPT_VERIFY_CA_AND_HOST to encrypt data, and verify CA and hostname
    • ENCRYPT_VERIFY_CA to encrypt data, and verify CA only
    • ENCRYPT_VERIFY_NONE for data encryption only
    • DISABLE for no encryption or verification
  • connector.tls.trustedServerCertificate: (optional) provide one or more PEM-encoded certificates. Required only if connector.tls.mode is ENCRYPT_VERIFY_CA_AND_HOST or ENCRYPT_VERIFY_CA.
  • ingestionType: specify either full or incremental. Incremental transfers are supported in preview. For more information, see Full or incremental transfers.
  • writeMode: specify either WRITE_MODE_APPEND or WRITE_MODE_UPSERT.
  • watermarkColumns: specify columns in your table as watermark columns. This field is required for incremental transfers.
  • primaryKeys: specify columns in your table as primary keys. This field is required for incremental transfers.
  • assets: a list of the names of the MySQL tables to be transferred from the MySQL database as part of the transfer.

For example, the following command creates a MySQL transfer called My Transfer:

bq mk
    --transfer_config
    --target_dataset=mydataset
    --data_source=mysql
    --display_name='My Transfer'
    --params='{"assets":["DB1/DEPARTMENT","DB1/EMPLOYEES"],
        "connector.authentication.username": "User1",
        "connector.authentication.password":"ABC12345",
        "connector.encryptionMode":"FULL",
        "connector.database":"DB1",
        "connector.endpoint.host":"54.74.220.23",
        "connector.endpoint.port":3306
        "ingestionType":"incremental",
        "writeMode":"WRITE_MODE_APPEND",
        "watermarkColumns":["createdAt","createdAt"],
        "primaryKeys":[['dep_id'], ['report_by','report_title']],
        "connector.tls.mode": "ENCRYPT_VERIFY_CA_AND_HOST",
        "connector.tls.trustedServerCertificate": "PEM-encoded certificate"}'

When specifying multiple assets during an incremental transfer, the values of the watermarkColumns and primaryKeys fields correspond to the position of values in the assets field. In the following example, dep_id corresponds to the table DB1/USER1/DEPARTMENT, while report_by and report_title corresponds to the table DB1/USER1/EMPLOYEES.

      "primaryKeys":[['dep_id'], ['report_by','report_title']],
      "assets":["DB1/USER1/DEPARTMENT","DB1/USER1/EMPLOYEES"],
  

API

Use the projects.locations.transferConfigs.create method and supply an instance of the TransferConfig resource.