import ChangeLog from '../changelog/connector-jdbc.md';

DuckDB

JDBC DuckDB Source Connector

Description

Read external data source data through JDBC.

Support DuckDB Version

• 0.8.x/0.9.x/0.10.x/1.x

Support Those Engines

Spark

Flink

SeaTunnel Zeta

Using Dependency

For Spark/Flink Engine

1. You need to ensure that the jdbc driver jar package has been placed in directory ${SEATUNNEL_HOME}/plugins/.

For SeaTunnel Zeta Engine

1. You need to ensure that the jdbc driver jar package has been placed in directory ${SEATUNNEL_HOME}/lib/.

Key Features

• batch
• stream
• exactly-once
• column projection
• parallelism
• support user-defined split

supports query SQL and can achieve projection effect.

Supported DataSource Info

Data Type Mapping

Source Options

                                                                                                                          |

| table_list | Array | No | - | The list of tables to be read, you can use this configuration instead of table_path example: [{ table_path = "main.table1"}, {table_path = "main.table2", query = "select * id, name from main.table2"}] | | where_condition | String | No | - | Common row filter conditions for all tables/queries, must start with where. for example where id > 100 | | split.size | Int | No | 8096 | The split size (number of rows) of table, captured tables are split into multiple splits when read of table. | | common-options | | No | - | Source plugin common parameters, please refer to Source Common Options for details |

Parallel Reader

The JDBC Source connector supports parallel reading of data from tables. SeaTunnel will use certain rules to split the data in the table, which will be handed over to readers for reading. The number of readers is determined by the parallelism option.

Split Key Rules:

1. If partition_column is not null, It will be used to calculate split. The column must in Supported split data type.
2. If partition_column is null, seatunnel will read the schema from table and get the Primary Key and Unique Index. If there are more than one column in Primary Key and Unique Index, The first column which in the supported split data type will be used to split data. For example, the table have Primary Key(nn guid, name varchar), because guid id not in supported split data type, so the column name will be used to split data.

Supported split data type:

• String
• Number(int, bigint, decimal, ...)
• Date

Options Related To Split

split.size

How many rows in one split, captured tables are split into multiple splits when read of table.

partition_column [string]

The column name for split data.

partition_upper_bound [BigDecimal]

The partition_column max value for scan, if not set SeaTunnel will query database get max value.

partition_lower_bound [BigDecimal]

The partition_column min value for scan, if not set SeaTunnel will query database get min value.

partition_num [int]

Not recommended for use, The correct approach is to control the number of split through split.size

How many splits do we need to split into, only support positive integer. default value is job parallelism.

tips

If the table can not be split(for example, table have no Primary Key or Unique Index, and partition_column is not set), it will run in single concurrency.

Use table_path to replace query for single table reading. If you need to read multiple tables, use table_list.

Task Example

Simple

This example queries 'user_events' table in your test database in single parallel and queries all of its fields. You can also specify which fields to query for final output to the console.

# Defining the runtime environment
env {
  parallelism = 4
  job.mode = "BATCH"
}
source{
    Jdbc {
        url = "jdbc:duckdb:/tmp/test.db"
        driver = "org.duckdb.DuckDBDriver"
        connection_check_timeout_sec = 100
        username = "duckdb"
        password = ""
        query = "select * from user_events limit 16"
    }
}

transform {
    # If you would like to get more information about how to configure seatunnel and see full list of transform plugins,
    # please go to https://seatunnel.apache.org/docs/transform-v2/sql
}

sink {
    Console {}
}

parallel by partition_column

env {
  parallelism = 4
  job.mode = "BATCH"
}
source {
    Jdbc {
        url = "jdbc:duckdb:/tmp/test.db"
        driver = "org.duckdb.DuckDBDriver"
        connection_check_timeout_sec = 100
        username = "duckdb"
        password = ""
        query = "select * from user_events"
        partition_column = "id"
        split.size = 10000
        # Read start boundary
        #partition_lower_bound = ...
        # Read end boundary
        #partition_upper_bound = ...
    }
}

sink {
  Console {}
}

parallel by Primary Key or Unique Index

Configuring table_path will turn on auto split, you can configure split.* to adjust the split strategy

env {
  parallelism = 4
  job.mode = "BATCH"
}
source {
    Jdbc {
        url = "jdbc:duckdb:/tmp/test.db"
        driver = "org.duckdb.DuckDBDriver"
        connection_check_timeout_sec = 100
        username = ""
        password = ""
        table_path = "main.user_events"
        query = "select * from main.user_events"
        split.size = 10000
    }
}

sink {
  Console {}
}

Parallel Boundary

It is more efficient to specify the data within the upper and lower bounds of the query It is more efficient to read your data source according to the upper and lower boundaries you configured

source {
    Jdbc {
        url = "jdbc:duckdb:/tmp/test.db"
        driver = "org.duckdb.DuckDBDriver"
        connection_check_timeout_sec = 100
        username = "duckdb"
        password = ""
        # Define query logic as required
        query = "select * from user_events"
        partition_column = "id"
        # Read start boundary
        partition_lower_bound = 1
        # Read end boundary
        partition_upper_bound = 500
        partition_num = 10
        properties {
         threads=4
         memory_limit="4GB"
        }
    }
}

Multiple table read

Configuring table_list will turn on auto split, you can configure split.* to adjust the split strategy

env {
  job.mode = "BATCH"
  parallelism = 4
}
source {
  Jdbc {
    url = "jdbc:duckdb:/tmp/test.db"
    driver = "org.duckdb.DuckDBDriver"
    connection_check_timeout_sec = 100
    username = "duckdb"
    password = ""

    table_list = [
      {
        table_path = "main.table1"
      },
      {
        table_path = "main.table2"
        # Use query filetr rows & columns
        query = "select id, name from main.table2 where id > 100"
      }
    ]
    #where_condition= "where id > 100"
    #split.size = 8096
  }
}

sink {
  Console {}
}

Change Log