# JDBC to JDBC template

Use the Managed Service for Apache Spark JDBC to JDBC
template to extract data from JDBC to JDBC.

This template supports the following databases:

- MySQL
- PostgreSQL
- Microsoft SQL Server
- Oracle

## Use the template

Run the template using the gcloud CLI or Managed Service for Apache Spark
API.

### gcloud


Before using any of the command data below,
make the following replacements:

- <var class="edit" scope="PROJECT_ID" translate="no">PROJECT_ID</var>: Required. Your Google Cloud project ID listed in the [IAM Settings](https://console.cloud.google.com/iam-admin/settings/project).
- <var class="edit" scope="REGION" translate="no">REGION</var>: Required. [Compute Engine region](https://docs.cloud.google.com/compute/docs/regions-zones.md.txt#available).
- <var class="edit" scope="SUBNET" translate="no">SUBNET</var>: Optional. If a subnet is not specified, the subnet in the specified REGION in the `default` network is selected.


  **Example:**
  `projects/PROJECT_ID/regions/REGION/subnetworks/SUBNET_NAME`
- <var class="edit" scope="TEMPLATE_VERSION" translate="no">TEMPLATE_VERSION</var>: Required. Specify `latest` for the latest template version, or the date of a specific version, for example, `2023-03-17_v0.1.0-beta` (visit [gs://dataproc-templates-binaries](https://console.cloud.google.com/storage/browser/dataproc-templates-binaries) or run `gcloud storage ls gs://dataproc-templates-binaries` to list available template versions).
- <var class="edit" scope="INPUT_JDBC_CONNECTOR_CLOUD_STORAGE_PATH" translate="no"> INPUT_JDBC_CONNECTOR_CLOUD_STORAGE_PATH</var> and <var class="edit" scope="OUTPUT_JDBC_CONNECTOR_CLOUD_STORAGE_PATH" translate="no">OUTPUT_JDBC_CONNECTOR_CLOUD_STORAGE_PATH</var>: Required. The full Cloud Storage path, including the filename, where the input and output JDBC connector jars are stored.


  **Note:** If input and output jars are the same, setting only
  <var translate="no">INPUT_JDBC_CONNECTOR_CLOUD_STORAGE_PATH</var> will suffice.


  You can use the following commands to download JDBC connectors for uploading to
  Cloud Storage:
  - **MySQL:**

          wget http://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-5.1.30.tar.gz

  - **PostgreSQL:**

          wget https://jdbc.postgresql.org/download/postgresql-42.2.6.jar

  - **Microsoft SQL Server:**

          wget https://repo1.maven.org/maven2/com/microsoft/sqlserver/mssql-jdbc/6.4.0.jre8/mssql-jdbc-6.4.0.jre8.jar

  - **Oracle:**

          wget https://repo1.maven.org/maven2/com/oracle/database/jdbc/ojdbc8/21.7.0.0/ojdbc8-21.7.0.0.jar

  <br />

- The following variables are used to construct the **required** input JDBC URL:
  - <var class="edit" scope="INPUT_JDBC_HOST" translate="no">INPUT_JDBC_HOST</var>
  - <var class="edit" scope="INPUT_JDBC_PORT" translate="no">INPUT_JDBC_PORT</var>
  - <var class="edit" scope="INPUT_JDBC_DATABASE" translate="no">INPUT_JDBC_DATABASE</var>, or, for Oracle, <var class="edit" scope="INPUT_JDBC_SERVICE" translate="no">INPUT_JDBC_SERVICE</var>
  - <var class="edit" scope="INPUT_JDBC_USERNAME" translate="no">INPUT_JDBC_USERNAME</var>
  - <var class="edit" scope="INPUT_JDBC_PASSWORD" translate="no">INPUT_JDBC_PASSWORD</var>

  Construct the <var class="edit" scope="INPUT_JDBC_CONNECTION_URL" translate="no">INPUT_JDBC_CONNECTION_URL</var> using one of the following
  connector-specific formats:
  - **MySQL:**

        jdbc:mysql://INPUT_JDBC_HOST:INPUT_JDBC_PORT/INPUT_JDBC_DATABASE?user=INPUT_JDBC_USERNAME&password=INPUT_JDBC_PASSWORD

  - **PostgreSQL:**

        jdbc:postgresql://INPUT_JDBC_HOST:INPUT_JDBC_PORT/INPUT_JDBC_DATABASE?user=INPUT_JDBC_USERNAME&password=INPUT_JDBC_PASSWORD

  - **Microsoft SQL Server:**

        jdbc:sqlserver://INPUT_JDBC_HOST:INPUT_JDBC_PORT;databaseName=INPUT_JDBC_DATABASE;user=INPUT_JDBC_USERNAME;password=INPUT_JDBC_PASSWORD

  - **Oracle:**

        jdbc:oracle:thin:@//INPUT_JDBC_HOST:INPUT_JDBC_PORT/INPUT_JDBC_SERVICE?user=INPUT_JDBC_USERNAME&password=INPUT_JDBC_PASSWORD

- The following variables are used to construct the **required** output JDBC URL:
  - <var class="edit" scope="OUTPUT_JDBC_HOST" translate="no">OUTPUT_JDBC_HOST</var>
  - <var class="edit" scope="OUTPUT_JDBC_PORT" translate="no">OUTPUT_JDBC_PORT</var>
  - <var class="edit" scope="OUTPUT_JDBC_DATABASE" translate="no">OUTPUT_JDBC_DATABASE</var>, or, for Oracle, <var class="edit" scope="OUTPUT_JDBC_SERVICE" translate="no">OUTPUT_JDBC_SERVICE</var>
  - <var class="edit" scope="OUTPUT_JDBC_USERNAME" translate="no">OUTPUT_JDBC_USERNAME</var>
  - <var class="edit" scope="OUTPUT_JDBC_PASSWORD" translate="no">OUTPUT_JDBC_PASSWORD</var>

  Construct the <var class="edit" scope="OUTPUT_JDBC_CONNECTION_URL" translate="no">OUTPUT_JDBC_CONNECTION_URL</var> using one of the following
  connector-specific formats:
  - **MySQL:**

        jdbc:mysql://OUTPUT_JDBC_HOST:OUTPUT_JDBC_PORT/OUTPUT_JDBC_DATABASE?user=OUTPUT_JDBC_USERNAME&password=OUTPUT_JDBC_PASSWORD

  - **PostgreSQL:**

        jdbc:postgresql://OUTPUT_JDBC_HOST:OUTPUT_JDBC_PORT/OUTPUT_JDBC_DATABASE?user=OUTPUT_JDBC_USERNAME&password=OUTPUT_JDBC_PASSWORD

  - **Microsoft SQL Server:**

        jdbc:sqlserver://OUTPUT_JDBC_HOST:OUTPUT_JDBC_PORT;databaseName=OUTPUT_JDBC_DATABASE;user=OUTPUT_JDBC_USERNAME;password=OUTPUT_JDBC_PASSWORD

  - **Oracle:**

        jdbc:oracle:thin:@//OUTPUT_JDBC_HOST:OUTPUT_JDBC_PORT/OUTPUT_JDBC_SERVICE?user=OUTPUT_JDBC_USERNAME&password=OUTPUT_JDBC_PASSWORD

- <var class="edit" scope="INPUT_JDBC_TABLE" translate="no">INPUT_JDBC_TABLE</var>: Required. Input JDBC table name or SQL query on the JDBC input table.


  **Example (the SQL query should be within parentheses):**
  `(select * from TABLE_NAME) as ALIAS_TABLE_NAME`
- <var class="edit" scope="OUTPUT_JDBC_TABLE" translate="no">OUTPUT_JDBC_TABLE</var>: Required. JDBC table where output will be stored.
- <var class="edit" scope="INPUT_DRIVER" translate="no">INPUT_DRIVER</var> and <var class="edit" scope="OUTPUT_DRIVER" translate="no">OUTPUT_DRIVER</var>: Required. The JDBC input and output driver that is used for the connection:
  - **MySQL:**

        com.mysql.cj.jdbc.Driver

  - **PostgreSQL:**

        org.postgresql.Driver

  - **Microsoft SQL Server:**

        com.microsoft.sqlserver.jdbc.SQLServerDriver

  - **Oracle:**

        oracle.jdbc.driver.OracleDriver

- <var class="edit" scope="INPUT_PARTITION_COLUMN" translate="no">INPUT_PARTITION_COLUMN</var>, <var class="edit" scope="LOWERBOUND" translate="no">LOWERBOUND</var>, <var class="edit" scope="UPPERBOUND" translate="no">UPPERBOUND</var>, <var class="edit" scope="NUM_PARTITIONS" translate="no">NUM_PARTITIONS</var>: Optional. If used, all of the following parameters must be specified:
  - **INPUT_PARTITION_COLUMN**: JDBC input table partition column name.
  - **LOWERBOUND**: JDBC input table partition column lower bound used to determine the partition stride.
  - **UPPERBOUND**: JDBC input table partition column upper bound used to determine the partition stride.
  - **NUM_PARTITIONS**: The maximum number of partitions that can be used for parallelism of table reads and writes. If specified, this value is used for the JDBC input and output connection.
- <var class="edit" scope="FETCHSIZE" translate="no">FETCHSIZE</var>: Optional. How many rows to fetch per round trip.
- <var class="edit" scope="BATCH_SIZE" translate="no">BATCH_SIZE</var>: Optional. Number of records to insert per round trip. Default: `1000`.
- <var class="edit" scope="MODE" translate="no">MODE</var>: Optional. Write mode for JDBC output. Options: `Append`, `Overwrite`, `Ignore`, or `ErrorIfExists`.
- <var class="edit" scope="TABLE_PROPERTIES" translate="no">TABLE_PROPERTIES</var>: Optional. This option allows setting of database-specific table and partition options when creating the output table.
- <var class="edit" scope="PRIMARY_KEY" translate="no">PRIMARY_KEY</var>: Optional. Primary key column for output table. Column mentioned should not contain duplicate values, otherwise an error will be thrown.
- <var class="edit" scope="JDBC_SESSION_INIT" translate="no">JDBC_SESSION_INIT</var>: Optional. Session initialization statement to read Java templates.
- <var class="edit" scope="LOG_LEVEL" translate="no">LOG_LEVEL</var>: Optional. Level of logging. Can be one of `ALL`, `DEBUG`, `ERROR`, `FATAL`, `INFO`, `OFF`, `TRACE`, or `WARN`. Default: `INFO`.
- <var class="edit" scope="TEMP_VIEW" translate="no">TEMP_VIEW</var> and <var class="edit" scope="TEMP_QUERY" translate="no">TEMP_QUERY</var>: Optional. You can use these two optional parameters to apply a Spark SQL transformation while loading data into Cloud Storage. <var translate="no">TEMP_VIEW</var> must be the same as table name used in query, and <var translate="no">TEMP_QUERY</var> is the query statement.
- <var class="edit" scope="SERVICE_ACCOUNT" translate="no">SERVICE_ACCOUNT</var>: Optional. If not provided, the [default Compute Engine service account](https://docs.cloud.google.com/compute/docs/access/service-accounts.md.txt#default_service_account) is used.
- <var class="edit" scope="PROPERTY_NAME" translate="no">PROPERTY</var> and <var class="edit" scope="PROPERTY_VALUE" translate="no">PROPERTY_VALUE</var>: Optional. Comma-separated list of [Spark property](https://docs.cloud.google.com/managed-spark/docs/concepts/properties)=`value` pairs.
- <var class="edit" scope="LABEL" translate="no">LABEL</var> and <var class="edit" scope="LABEL_VALUE" translate="no">LABEL_VALUE</var>: Optional. Comma-separated list of `label`=`value` pairs.
- <var class="edit" scope="KMS_KEY" translate="no">KMS_KEY</var>: Optional. The Cloud Key Management Service key to use for encryption. If a key is not specified, data is [encrypted at rest](https://docs.cloud.google.com/docs/security/encryption/default-encryption.md.txt) using a Google-owned and Google-managed encryption key.

  **Example:**
  `projects/PROJECT_ID/regions/REGION/keyRings/KEY_RING_NAME/cryptoKeys/KEY_NAME`


Execute the

following

command:

#### Linux, macOS, or Cloud Shell

> [!NOTE]
> **Note:** Ensure you have initialized the Google Cloud CLI with authentication and a project by running either [gcloud init](https://docs.cloud.google.com/sdk/gcloud/reference/init); or [gcloud auth login](https://docs.cloud.google.com/sdk/gcloud/reference/auth/login.md.txt) and [gcloud config set project](https://docs.cloud.google.com/sdk/gcloud/reference/config/set.md.txt).

```bash
gcloud dataproc batches submit spark \
    --class=com.google.cloud.dataproc.templates.main.DataProcTemplate \
    --project="PROJECT_ID" \
    --region="REGION" \
    --version="2.3" \
    --jars="gs://dataproc-templates-binaries/TEMPLATE_VERSION/java/dataproc-templates.jar,INPUT_JDBC_CONNECTOR_CLOUD_STORAGE_PATH,OUTPUT_JDBC_CONNECTOR_CLOUD_STORAGE_PATH" \
    --subnet="SUBNET" \
    --kms-key="KMS_KEY" \
    --service-account="SERVICE_ACCOUNT" \
    --properties="PROPERTY=PROPERTY_VALUE" \
    --labels="LABEL=LABEL_VALUE" \
    -- --template JDBCTOJDBC \
    --templateProperty project.id="PROJECT_ID" \
    --templateProperty log.level="LOG_LEVEL" \
    --templateProperty jdbctojdbc.input.url="INPUT_JDBC_CONNECTION_URL" \
    --templateProperty jdbctojdbc.input.driver="INPUT_DRIVER" \
    --templateProperty jdbctojdbc.input.table="INPUT_JDBC_TABLE" \
    --templateProperty jdbctojdbc.output.url="OUTPUT_JDBC_CONNECTION_URL" \
    --templateProperty jdbctojdbc.output.driver="OUTPUT_DRIVER" \
    --templateProperty jdbctojdbc.output.table="OUTPUT_JDBC_TABLE" \
    --templateProperty jdbctojdbc.input.fetchsize="FETCHSIZE" \
    --templateProperty jdbctojdbc.input.partitioncolumn="INPUT_PARTITION_COLUMN" \
    --templateProperty jdbctojdbc.input.lowerbound="LOWERBOUND" \
    --templateProperty jdbctojdbc.input.upperbound="UPPERBOUND" \
    --templateProperty jdbctojdbc.numpartitions="NUM_PARTITIONS" \
    --templateProperty jdbctojdbc.output.mode="MODE" \
    --templateProperty jdbctojdbc.output.batch.size="BATCH_SIZE" \
    --templateProperty jdbctojdbc.output.primary.key="PRIMARY_KEY" \
    --templateProperty jdbctojdbc.output.create.table.option="TABLE_PROPERTIES" \
    --templateProperty jdbctojdbc.sessioninitstatement="JDBC_SESSION_INIT" \
    --templateProperty jdbctojdbc.temp.view.name="TEMP_VIEW" \
    --templateProperty jdbctojdbc.sql.query="TEMP_QUERY"
```

#### Windows (PowerShell)

> [!NOTE]
> **Note:** Ensure you have initialized the Google Cloud CLI with authentication and a project by running either [gcloud init](https://docs.cloud.google.com/sdk/gcloud/reference/init); or [gcloud auth login](https://docs.cloud.google.com/sdk/gcloud/reference/auth/login.md.txt) and [gcloud config set project](https://docs.cloud.google.com/sdk/gcloud/reference/config/set.md.txt).

```bash
gcloud dataproc batches submit spark `
    --class=com.google.cloud.dataproc.templates.main.DataProcTemplate `
    --project="PROJECT_ID" `
    --region="REGION" `
    --version="2.3" `
    --jars="gs://dataproc-templates-binaries/TEMPLATE_VERSION/java/dataproc-templates.jar,INPUT_JDBC_CONNECTOR_CLOUD_STORAGE_PATH,OUTPUT_JDBC_CONNECTOR_CLOUD_STORAGE_PATH" `
    --subnet="SUBNET" `
    --kms-key="KMS_KEY" `
    --service-account="SERVICE_ACCOUNT" `
    --properties="PROPERTY=PROPERTY_VALUE" `
    --labels="LABEL=LABEL_VALUE" `
    -- --template JDBCTOJDBC `
    --templateProperty project.id="PROJECT_ID" `
    --templateProperty log.level="LOG_LEVEL" `
    --templateProperty jdbctojdbc.input.url="INPUT_JDBC_CONNECTION_URL" `
    --templateProperty jdbctojdbc.input.driver="INPUT_DRIVER" `
    --templateProperty jdbctojdbc.input.table="INPUT_JDBC_TABLE" `
    --templateProperty jdbctojdbc.output.url="OUTPUT_JDBC_CONNECTION_URL" `
    --templateProperty jdbctojdbc.output.driver="OUTPUT_DRIVER" `
    --templateProperty jdbctojdbc.output.table="OUTPUT_JDBC_TABLE" `
    --templateProperty jdbctojdbc.input.fetchsize="FETCHSIZE" `
    --templateProperty jdbctojdbc.input.partitioncolumn="INPUT_PARTITION_COLUMN" `
    --templateProperty jdbctojdbc.input.lowerbound="LOWERBOUND" `
    --templateProperty jdbctojdbc.input.upperbound="UPPERBOUND" `
    --templateProperty jdbctojdbc.numpartitions="NUM_PARTITIONS" `
    --templateProperty jdbctojdbc.output.mode="MODE" `
    --templateProperty jdbctojdbc.output.batch.size="BATCH_SIZE" `
    --templateProperty jdbctojdbc.output.primary.key="PRIMARY_KEY" `
    --templateProperty jdbctojdbc.output.create.table.option="TABLE_PROPERTIES" `
    --templateProperty jdbctojdbc.sessioninitstatement="JDBC_SESSION_INIT" `
    --templateProperty jdbctojdbc.temp.view.name="TEMP_VIEW" `
    --templateProperty jdbctojdbc.sql.query="TEMP_QUERY"
```

#### Windows (cmd.exe)

> [!NOTE]
> **Note:** Ensure you have initialized the Google Cloud CLI with authentication and a project by running either [gcloud init](https://docs.cloud.google.com/sdk/gcloud/reference/init); or [gcloud auth login](https://docs.cloud.google.com/sdk/gcloud/reference/auth/login.md.txt) and [gcloud config set project](https://docs.cloud.google.com/sdk/gcloud/reference/config/set.md.txt).

```bash
gcloud dataproc batches submit spark ^
    --class=com.google.cloud.dataproc.templates.main.DataProcTemplate ^
    --project="PROJECT_ID" ^
    --region="REGION" ^
    --version="2.3" ^
    --jars="gs://dataproc-templates-binaries/TEMPLATE_VERSION/java/dataproc-templates.jar,INPUT_JDBC_CONNECTOR_CLOUD_STORAGE_PATH,OUTPUT_JDBC_CONNECTOR_CLOUD_STORAGE_PATH" ^
    --subnet="SUBNET" ^
    --kms-key="KMS_KEY" ^
    --service-account="SERVICE_ACCOUNT" ^
    --properties="PROPERTY=PROPERTY_VALUE" ^
    --labels="LABEL=LABEL_VALUE" ^
    -- --template JDBCTOJDBC ^
    --templateProperty project.id="PROJECT_ID" ^
    --templateProperty log.level="LOG_LEVEL" ^
    --templateProperty jdbctojdbc.input.url="INPUT_JDBC_CONNECTION_URL" ^
    --templateProperty jdbctojdbc.input.driver="INPUT_DRIVER" ^
    --templateProperty jdbctojdbc.input.table="INPUT_JDBC_TABLE" ^
    --templateProperty jdbctojdbc.output.url="OUTPUT_JDBC_CONNECTION_URL" ^
    --templateProperty jdbctojdbc.output.driver="OUTPUT_DRIVER" ^
    --templateProperty jdbctojdbc.output.table="OUTPUT_JDBC_TABLE" ^
    --templateProperty jdbctojdbc.input.fetchsize="FETCHSIZE" ^
    --templateProperty jdbctojdbc.input.partitioncolumn="INPUT_PARTITION_COLUMN" ^
    --templateProperty jdbctojdbc.input.lowerbound="LOWERBOUND" ^
    --templateProperty jdbctojdbc.input.upperbound="UPPERBOUND" ^
    --templateProperty jdbctojdbc.numpartitions="NUM_PARTITIONS" ^
    --templateProperty jdbctojdbc.output.mode="MODE" ^
    --templateProperty jdbctojdbc.output.batch.size="BATCH_SIZE" ^
    --templateProperty jdbctojdbc.output.primary.key="PRIMARY_KEY" ^
    --templateProperty jdbctojdbc.output.create.table.option="TABLE_PROPERTIES" ^
    --templateProperty jdbctojdbc.sessioninitstatement="JDBC_SESSION_INIT" ^
    --templateProperty jdbctojdbc.temp.view.name="TEMP_VIEW" ^
    --templateProperty jdbctojdbc.sql.query="TEMP_QUERY"
```

### REST


Before using any of the request data,
make the following replacements:

- <var class="edit" scope="PROJECT_ID" translate="no">PROJECT_ID</var>: Required. Your Google Cloud project ID listed in the [IAM Settings](https://console.cloud.google.com/iam-admin/settings/project).
- <var class="edit" scope="REGION" translate="no">REGION</var>: Required. [Compute Engine region](https://docs.cloud.google.com/compute/docs/regions-zones.md.txt#available).
- <var class="edit" scope="SUBNET" translate="no">SUBNET</var>: Optional. If a subnet is not specified, the subnet in the specified REGION in the `default` network is selected.


  **Example:**
  `projects/PROJECT_ID/regions/REGION/subnetworks/SUBNET_NAME`
- <var class="edit" scope="TEMPLATE_VERSION" translate="no">TEMPLATE_VERSION</var>: Required. Specify `latest` for the latest template version, or the date of a specific version, for example, `2023-03-17_v0.1.0-beta` (visit [gs://dataproc-templates-binaries](https://console.cloud.google.com/storage/browser/dataproc-templates-binaries) or run `gcloud storage ls gs://dataproc-templates-binaries` to list available template versions).
- <var class="edit" scope="INPUT_JDBC_CONNECTOR_CLOUD_STORAGE_PATH" translate="no"> INPUT_JDBC_CONNECTOR_CLOUD_STORAGE_PATH</var> and <var class="edit" scope="OUTPUT_JDBC_CONNECTOR_CLOUD_STORAGE_PATH" translate="no">OUTPUT_JDBC_CONNECTOR_CLOUD_STORAGE_PATH</var>: Required. The full Cloud Storage path, including the filename, where the input and output JDBC connector jars are stored.


  **Note:** If input and output jars are the same, setting only
  <var translate="no">INPUT_JDBC_CONNECTOR_CLOUD_STORAGE_PATH</var> will suffice.


  You can use the following commands to download JDBC connectors for uploading to
  Cloud Storage:
  - **MySQL:**

          wget http://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-5.1.30.tar.gz

  - **PostgreSQL:**

          wget https://jdbc.postgresql.org/download/postgresql-42.2.6.jar

  - **Microsoft SQL Server:**

          wget https://repo1.maven.org/maven2/com/microsoft/sqlserver/mssql-jdbc/6.4.0.jre8/mssql-jdbc-6.4.0.jre8.jar

  - **Oracle:**

          wget https://repo1.maven.org/maven2/com/oracle/database/jdbc/ojdbc8/21.7.0.0/ojdbc8-21.7.0.0.jar

  <br />

- The following variables are used to construct the **required** input JDBC URL:
  - <var class="edit" scope="INPUT_JDBC_HOST" translate="no">INPUT_JDBC_HOST</var>
  - <var class="edit" scope="INPUT_JDBC_PORT" translate="no">INPUT_JDBC_PORT</var>
  - <var class="edit" scope="INPUT_JDBC_DATABASE" translate="no">INPUT_JDBC_DATABASE</var>, or, for Oracle, <var class="edit" scope="INPUT_JDBC_SERVICE" translate="no">INPUT_JDBC_SERVICE</var>
  - <var class="edit" scope="INPUT_JDBC_USERNAME" translate="no">INPUT_JDBC_USERNAME</var>
  - <var class="edit" scope="INPUT_JDBC_PASSWORD" translate="no">INPUT_JDBC_PASSWORD</var>

  Construct the <var class="edit" scope="INPUT_JDBC_CONNECTION_URL" translate="no">INPUT_JDBC_CONNECTION_URL</var> using one of the following
  connector-specific formats:
  - **MySQL:**

        jdbc:mysql://INPUT_JDBC_HOST:INPUT_JDBC_PORT/INPUT_JDBC_DATABASE?user=INPUT_JDBC_USERNAME&password=INPUT_JDBC_PASSWORD

  - **PostgreSQL:**

        jdbc:postgresql://INPUT_JDBC_HOST:INPUT_JDBC_PORT/INPUT_JDBC_DATABASE?user=INPUT_JDBC_USERNAME&password=INPUT_JDBC_PASSWORD

  - **Microsoft SQL Server:**

        jdbc:sqlserver://INPUT_JDBC_HOST:INPUT_JDBC_PORT;databaseName=INPUT_JDBC_DATABASE;user=INPUT_JDBC_USERNAME;password=INPUT_JDBC_PASSWORD

  - **Oracle:**

        jdbc:oracle:thin:@//INPUT_JDBC_HOST:INPUT_JDBC_PORT/INPUT_JDBC_SERVICE?user=INPUT_JDBC_USERNAME&password=INPUT_JDBC_PASSWORD

- The following variables are used to construct the **required** output JDBC URL:
  - <var class="edit" scope="OUTPUT_JDBC_HOST" translate="no">OUTPUT_JDBC_HOST</var>
  - <var class="edit" scope="OUTPUT_JDBC_PORT" translate="no">OUTPUT_JDBC_PORT</var>
  - <var class="edit" scope="OUTPUT_JDBC_DATABASE" translate="no">OUTPUT_JDBC_DATABASE</var>, or, for Oracle, <var class="edit" scope="OUTPUT_JDBC_SERVICE" translate="no">OUTPUT_JDBC_SERVICE</var>
  - <var class="edit" scope="OUTPUT_JDBC_USERNAME" translate="no">OUTPUT_JDBC_USERNAME</var>
  - <var class="edit" scope="OUTPUT_JDBC_PASSWORD" translate="no">OUTPUT_JDBC_PASSWORD</var>

  Construct the <var class="edit" scope="OUTPUT_JDBC_CONNECTION_URL" translate="no">OUTPUT_JDBC_CONNECTION_URL</var> using one of the following
  connector-specific formats:
  - **MySQL:**

        jdbc:mysql://OUTPUT_JDBC_HOST:OUTPUT_JDBC_PORT/OUTPUT_JDBC_DATABASE?user=OUTPUT_JDBC_USERNAME&password=OUTPUT_JDBC_PASSWORD

  - **PostgreSQL:**

        jdbc:postgresql://OUTPUT_JDBC_HOST:OUTPUT_JDBC_PORT/OUTPUT_JDBC_DATABASE?user=OUTPUT_JDBC_USERNAME&password=OUTPUT_JDBC_PASSWORD

  - **Microsoft SQL Server:**

        jdbc:sqlserver://OUTPUT_JDBC_HOST:OUTPUT_JDBC_PORT;databaseName=OUTPUT_JDBC_DATABASE;user=OUTPUT_JDBC_USERNAME;password=OUTPUT_JDBC_PASSWORD

  - **Oracle:**

        jdbc:oracle:thin:@//OUTPUT_JDBC_HOST:OUTPUT_JDBC_PORT/OUTPUT_JDBC_SERVICE?user=OUTPUT_JDBC_USERNAME&password=OUTPUT_JDBC_PASSWORD

- <var class="edit" scope="INPUT_JDBC_TABLE" translate="no">INPUT_JDBC_TABLE</var>: Required. Input JDBC table name or SQL query on the JDBC input table.


  **Example (the SQL query should be within parentheses):**
  `(select * from TABLE_NAME) as ALIAS_TABLE_NAME`
- <var class="edit" scope="OUTPUT_JDBC_TABLE" translate="no">OUTPUT_JDBC_TABLE</var>: Required. JDBC table where output will be stored.
- <var class="edit" scope="INPUT_DRIVER" translate="no">INPUT_DRIVER</var> and <var class="edit" scope="OUTPUT_DRIVER" translate="no">OUTPUT_DRIVER</var>: Required. The JDBC input and output driver that is used for the connection:
  - **MySQL:**

        com.mysql.cj.jdbc.Driver

  - **PostgreSQL:**

        org.postgresql.Driver

  - **Microsoft SQL Server:**

        com.microsoft.sqlserver.jdbc.SQLServerDriver

  - **Oracle:**

        oracle.jdbc.driver.OracleDriver

- <var class="edit" scope="INPUT_PARTITION_COLUMN" translate="no">INPUT_PARTITION_COLUMN</var>, <var class="edit" scope="LOWERBOUND" translate="no">LOWERBOUND</var>, <var class="edit" scope="UPPERBOUND" translate="no">UPPERBOUND</var>, <var class="edit" scope="NUM_PARTITIONS" translate="no">NUM_PARTITIONS</var>: Optional. If used, all of the following parameters must be specified:
  - **INPUT_PARTITION_COLUMN**: JDBC input table partition column name.
  - **LOWERBOUND**: JDBC input table partition column lower bound used to determine the partition stride.
  - **UPPERBOUND**: JDBC input table partition column upper bound used to determine the partition stride.
  - **NUM_PARTITIONS**: The maximum number of partitions that can be used for parallelism of table reads and writes. If specified, this value is used for the JDBC input and output connection.
- <var class="edit" scope="FETCHSIZE" translate="no">FETCHSIZE</var>: Optional. How many rows to fetch per round trip.
- <var class="edit" scope="BATCH_SIZE" translate="no">BATCH_SIZE</var>: Optional. Number of records to insert per round trip. Default: `1000`.
- <var class="edit" scope="MODE" translate="no">MODE</var>: Optional. Write mode for JDBC output. Options: `Append`, `Overwrite`, `Ignore`, or `ErrorIfExists`.
- <var class="edit" scope="TABLE_PROPERTIES" translate="no">TABLE_PROPERTIES</var>: Optional. This option allows setting of database-specific table and partition options when creating the output table.
- <var class="edit" scope="PRIMARY_KEY" translate="no">PRIMARY_KEY</var>: Optional. Primary key column for output table. Column mentioned should not contain duplicate values, otherwise an error will be thrown.
- <var class="edit" scope="JDBC_SESSION_INIT" translate="no">JDBC_SESSION_INIT</var>: Optional. Session initialization statement to read Java templates.
- <var class="edit" scope="LOG_LEVEL" translate="no">LOG_LEVEL</var>: Optional. Level of logging. Can be one of `ALL`, `DEBUG`, `ERROR`, `FATAL`, `INFO`, `OFF`, `TRACE`, or `WARN`. Default: `INFO`.
- <var class="edit" scope="TEMP_VIEW" translate="no">TEMP_VIEW</var> and <var class="edit" scope="TEMP_QUERY" translate="no">TEMP_QUERY</var>: Optional. You can use these two optional parameters to apply a Spark SQL transformation while loading data into Cloud Storage. <var translate="no">TEMP_VIEW</var> must be the same as table name used in query, and <var translate="no">TEMP_QUERY</var> is the query statement.
- <var class="edit" scope="SERVICE_ACCOUNT" translate="no">SERVICE_ACCOUNT</var>: Optional. If not provided, the [default Compute Engine service account](https://docs.cloud.google.com/compute/docs/access/service-accounts.md.txt#default_service_account) is used.
- <var class="edit" scope="PROPERTY_NAME" translate="no">PROPERTY</var> and <var class="edit" scope="PROPERTY_VALUE" translate="no">PROPERTY_VALUE</var>: Optional. Comma-separated list of [Spark property](https://docs.cloud.google.com/managed-spark/docs/concepts/properties)=`value` pairs.
- <var class="edit" scope="LABEL" translate="no">LABEL</var> and <var class="edit" scope="LABEL_VALUE" translate="no">LABEL_VALUE</var>: Optional. Comma-separated list of `label`=`value` pairs.
- <var class="edit" scope="KMS_KEY" translate="no">KMS_KEY</var>: Optional. The Cloud Key Management Service key to use for encryption. If a key is not specified, data is [encrypted at rest](https://docs.cloud.google.com/docs/security/encryption/default-encryption.md.txt) using a Google-owned and Google-managed encryption key.

  **Example:**
  `projects/PROJECT_ID/regions/REGION/keyRings/KEY_RING_NAME/cryptoKeys/KEY_NAME`


HTTP method and URL:

```
POST https://dataproc.googleapis.com/v1/projects/PROJECT_ID/locations/REGION/batches
```


Request JSON body:

```
{
  "environmentConfig": {
    "executionConfig": {
      "subnetworkUri": "SUBNET",
      "kmsKey": "KMS_KEY",
      "serviceAccount": "SERVICE_ACCOUNT"
    }
  },
  "labels": {
    "LABEL": "LABEL_VALUE"
  },
  "runtimeConfig": {
    "version": "2.3",
    "properties": {
      "PROPERTY": "PROPERTY_VALUE"
    }
  },
  "sparkBatch": {
    "mainClass": "com.google.cloud.dataproc.templates.main.DataProcTemplate",
    "args": [
      "--template","JDBCTOJDBC",
      "--templateProperty","log.level=LOG_LEVEL",
      "--templateProperty","project.id=PROJECT_ID",
      "--templateProperty","jdbctojdbc.input.url=INPUT_JDBC_CONNECTION_URL",
      "--templateProperty","jdbctojdbc.input.driver=INPUT_DRIVER",
      "--templateProperty","jdbctojdbc.input.table=INPUT_TABLE",
      "--templateProperty","jdbctojdbc.output.url=OUTPUT_JDBC_CONNECTION_URL",
      "--templateProperty","jdbctojdbc.output.driver=OUTPUT_DRIVER",
      "--templateProperty","jdbctojdbc.output.table=OUTPUT_TABLE",
      "--templateProperty","jdbctojdbc.input.fetchsize=FETCHSIZE",
      "--templateProperty","jdbctojdbc.input.partitioncolumn=INPUT_PARTITION_COLUMN",
      "--templateProperty","jdbctojdbc.input.lowerbound=LOWERBOUND",
      "--templateProperty","jdbctojdbc.input.upperbound=UPPERBOUND",
      "--templateProperty","jdbctojdbc.numpartitions=NUM_PARTITIONS",
      "--templateProperty","jdbctojdbc.output.mode=MODE",
      "--templateProperty","jdbctojdbc.output.batch.size=BATCH_SIZE",
      "--templateProperty","jdbctojdbc.output.primary.key=PRIMARY_KEY",
      "--templateProperty","jdbctojdbc.output.create.table.option=TABLE_PROPERTIES",
      "--templateProperty","jdbctojdbc.sessioninitstatement=JDBC_SESSION_INIT",
      "--templateProperty","jdbctojdbc.temp.view.name=TEMP_VIEW",
      "--templateProperty","jdbctojdbc.sql.query=TEMP_QUERY"
    ],
    "jarFileUris": [
      "gs://dataproc-templates-binaries/TEMPLATE_VERSION/java/dataproc-templates.jar",
      "INPUT_JDBC_CONNECTOR_CLOUD_STORAGE_PATH",
      "OUTPUT_JDBC_CONNECTOR_CLOUD_STORAGE_PATH"
    ]
  }
}
```

To send your request, expand one of these options:

#### curl (Linux, macOS, or Cloud Shell)

> [!NOTE]
> **Note:** The following command assumes that you have logged in to the `gcloud` CLI with your user account by running [`gcloud init`](https://docs.cloud.google.com/sdk/gcloud/reference/init) or [`gcloud auth login`](https://docs.cloud.google.com/sdk/gcloud/reference/auth/login.md.txt) , or by using [Cloud Shell](https://docs.cloud.google.com/shell/docs.md.txt), which automatically logs you into the `gcloud` CLI . You can check the currently active account by running [`gcloud auth list`](https://docs.cloud.google.com/sdk/gcloud/reference/auth/list.md.txt).


Save the request body in a file named `request.json`,
and execute the following command:

```
curl -X POST \
     -H "Authorization: Bearer $(gcloud auth print-access-token)" \
     -H "Content-Type: application/json; charset=utf-8" \
     -d @request.json \
     "https://dataproc.googleapis.com/v1/projects/PROJECT_ID/locations/REGION/batches"
```

#### PowerShell (Windows)

> [!NOTE]
> **Note:** The following command assumes that you have logged in to the `gcloud` CLI with your user account by running [`gcloud init`](https://docs.cloud.google.com/sdk/gcloud/reference/init) or [`gcloud auth login`](https://docs.cloud.google.com/sdk/gcloud/reference/auth/login.md.txt) . You can check the currently active account by running [`gcloud auth list`](https://docs.cloud.google.com/sdk/gcloud/reference/auth/list.md.txt).


Save the request body in a file named `request.json`,
and execute the following command:

```
$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
    -Method POST `
    -Headers $headers `
    -ContentType: "application/json; charset=utf-8" `
    -InFile request.json `
    -Uri "https://dataproc.googleapis.com/v1/projects/PROJECT_ID/locations/REGION/batches" | Select-Object -Expand Content
```

You should receive a JSON response similar to the following:

```
{
  "name": "projects/PROJECT_ID/regions/REGION/operations/OPERATION_ID",
  "metadata": {
    "@type": "type.googleapis.com/google.cloud.dataproc.v1.BatchOperationMetadata",
    "batch": "projects/PROJECT_ID/locations/REGION/batches/BATCH_ID",
    "batchUuid": "de8af8d4-3599-4a7c-915c-798201ed1583",
    "createTime": "2023-02-24T03:31:03.440329Z",
    "operationType": "BATCH",
    "description": "Batch"
  }
}
```

<br />