ydb-importer

KO | EN

gitlite — search

#python #typescript #llm #golang #machine-learning #macos #nodejs #java #cli #kubernetes #awesome-list #pytorch #mcp #open-source

(★ 10)

A simple tool to import data from JDBC data sources to YDB

ydb-importer Latest Version Download

Download Latest Version (.zip)

// repository documentation

Was this content helpful?

★ 0 (0 ratings)

[![License](https://img.shields.io/badge/License-Apache%202.0-blue.svg)](https://github.com/ydb-platform/ydb-importer/blob/main/LICENSE)
[![Release](https://img.shields.io/github/v/release/ydb-platform/ydb-importer.svg?style=flat-square)](https://github.com/ydb-platform/ydb-importer/releases)

# Import data from JDBC data sources to YDB databases

This tool imports table structures and data records from a JDBC data source to YDB.

Right now the tested data sources include the following:

* [PostgreSQL](https://www.postgresql.org/),
* [MySQL](https://www.mysql.com/),
* [MariaDB](https://mariadb.org/) (including [ColumnStore](https://mariadb.com/docs/analytics/mariadb-columnstore)),
* [Oracle Database](https://www.oracle.com/database/),
* [Microsoft SQL Server](https://www.microsoft.com/sql-server/),
* [IBM Db2](https://www.ibm.com/products/db2),
* [IBM Informix](https://www.ibm.com/products/informix),
* [ClickHouse](https://clickhouse.com/),
* [SAP HANA](https://www.sap.com/products/data-cloud/hana.html),
* [Vertica](https://www.opentext.com/products/analytics-database),
* [Greenplum](https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/7.html).

Other data sources will probably work, too, as the tool uses the generic JDBC APIs to retrieve data and metadata.

Some data types and table structures are known to be unsupported:

* the embedded tables for Oracle Database,
* spatial data type for Microsoft SQL Server,
* object data types for Informix.

## 1. Operating instructions

The tool reads the XML configuration file on startup. The file name needs to be specified as a command line agrument when running the tool.

Settings in the configuration file define:
* source database type and connection preferences;
* YDB target database connection preferences;
* file name to save the generated YQL script with YDB target tables structure;
* the rules to filter source tables' names;
* the rules to generate target tables' names based on the names of the source tables;
* the degree of parallelism (which determines the reader and writer pool sizes, plus the connection pool sizes for both source and target databases).

Data import is performed as the following sequence of actions:
1. The tool connects to the source database, and determines the list of tables and custom SQL queries to be imported.
2. The structure of target YDB tables is generated, and optionally saved as YQL script.
3. Target database is checked for existence of the target tables. Missing tables are created, already existing ones may be left as is, or re-created.
4. Row data is imported by reading from the source database using the SELECT statements, and written into the target YDB database using the Bulk Upsert mechanizm.

All operations, including metadata extraction from the source database, table creation (and re-creation) in the target database, and row data import, are performed in parallel mode (thread-per-table), using multiple concurrent threads and multiple open connections to both source and target databases. The maximum degree of parallelizm is configurable, although the actual number of concurrent operations cannot exceed the number of tables being imported.

## 2. Running the tool

The compiled tool is available as the ZIP archive, which contains the configuration file examples as `*.xml`, sample tool startup script `ydb-importer.sh`, and the executable code for the tool and its dependencies (including the YDB Java SDK) as `lib/*.jar` files.

Before running the tool the required JDBC drivers should be put (as `*.jar` files) into the `lib` subdirectory of the tool installation directory.

The configuration file needs to be prepared based on one of the provided samples.

The example command to run the tool is provided in the [ydb-importer.sh](scripts/ydb-importer.sh) file, which can also be used to run the tool as shown below:

```bash
./ydb-importer.sh my-import-config.xml
```

The name of the configuration file is provided as the command line parameter to the tool.

## 3. Handling tables without the primary key

Each YDB table must have a primary key. If a primary key (or at least a unique index) is defined on the source table, the tool creates the primary key for the target YDB table with the columns and order defined by the original primary key. When having multiple unique indexes defined on the source table, the tool prefers the index with the smallest number of columns.

In case the source table has no primary key and no unique indexes, primary key columns can be configured using the import settings in the configuration file, as a sequence of `key-column` elements in the `table-ref` section (see below the example in the description of the configuration file format).

When there is no primary key defined anywhere, the tool automatically adds the column `ydb_synth_key` to the target table, and uses it as the primary key. The values of the synthetic primary key are computed as "SHA-256" hash code over the values of all columns of the row, except the columns of the BLOB type.

If the input table contains several rows with completely identical values, the destination table will have only one row per each set of duplicate input rows. This also means that there the tool cannot import the table in which all columns are of BLOB type.

## 4. BLOB and CLOB data import

For each BLOB field in the source database the import tool creates an additional YDB target table (BLOB supplemental table), having the following structure:
```sql
CREATE TABLE `blob_table`(
    `id` Int64,
    `pos` Int32,
    `val` String,
    PRIMARY KEY(`id`, `pos`)
)
```

A similar table is created for each CLOB field:
```sql
CREATE TABLE `clob_table`(
    `id` Int64,
    `pos` Int32,
    `val` Text,
    PRIMARY KEY(`id`, `pos`)
)
```

A CLOB table is created for source columns of types CLOB or NCLOB, as well as for text columns explicitly marked with `<clob-column>` in the `<table-ref>` section.

The name of the supplemental table is generated based on the `table-options` / `blob-name-format` setting in the configuration file.

Each source field value is saved as a sequence of records in the supplemental table.
A unique value of type `Int64` is generated for each source value, and stored in the `id` field.
This identifier is also stored in the "main" target table in the field having the same name as the source field.
Each BLOB record stores no more than 64K bytes in the `val` field, each CLOB record stores no more than 32768 characters. The order of blocks is defined by the values of the `pos` column.

## 5. YDB partitioning and parallel source reading

The tool independently manages two aspects.

- **YDB target table partitioning** via `<ydb-partition-count>`. Sets the number of partitions for the YDB table created via `PARTITION_AT_KEYS`.
- **Parallel source reading**. How many parts the source queries are split into: by range over `<split-by>`/`<split-count>`, or by the source native partitions (`<use-source-partitions>`).

Both modes create tasks that run in parallel, with a failed task retried on error (see `<retry-count>`).

See the "Configuration file format" section for the details of each tag.

### How the two modes interact

Reading and target partitioning are resolved independently.

**Reading.** With `<split-by>` reading goes by range over the split column (`<use-source-partitions>` then has no effect). Without it, each source native partition is read by a separate task.

**Target partitioning.** When `<ydb-partition-count>=auto`, if the source has partitions whose first key column ranges do not overlap, the YDB table copies their count and boundaries. If `<split-by>` is over the same first key column, read slices line up with the YDB partitions, so each batch lands entirely in one of them.

**When they do not line up.** `<use-partition-buffers>` regroups rows on the reader side into one batch per YDB partition. It requires an integer leading key and resolved `<ydb-partition-count>` boundaries. Otherwise it falls back to plain batching.

## 6. Configuration file format

Sample configuration files:
* [for PostgreSQL](scripts/sample-postgres.xml);
* [for MySQL](scripts/sample-mysql.xml);
* [for MariaDB](scripts/sample-mariadb.xml);
* [for Oracle Database](scripts/sample-oracle.xml);
* [for Microsoft SQL Server](scripts/sample-mssql.xml);
* [for IBM Db2](scripts/sample-db2.xml);
* [for IBM Informix](scripts/sample-informix.xml);
* [for ClickHouse](scripts/sample-clickhouse.xml);
* [for SAP HANA](scripts/sample-hana.xml);
* [for Vertica](scripts/sample-vertica.xml);
* [for Greenplum](scripts/sample-greenplum.xml).

Below is the definition of the configuration file structure:

```xml
<?xml version="1.0" encoding="UTF-8"?>
<ydb-importer>
    <workers>
        
        <reader-pool size="4"/>
        
        <writer-pool size="4"/>
        
        <buffer-count>4</buffer-count>
        
        <use-arrow>false</use-arrow>
    </workers>
    
    <source type="generic|postgresql|greenplum|mysql|mariadb|oracle|mssql|db2|informix|clickhouse|hana|vertica">
        
        <jdbc-class>driver-class-name</jdbc-class>
        
        <jdbc-url>jdbc-url</jdbc-url>
        <username>username</username>
        <password>password</password>
        
        <retry-count>10</retry-count>
        
        <fetch-size>10000</fetch-size>
    </source>
    
    <target type="ydb">
        
        <script-file>sample-database.yql.tmp</script-file>
        
        <connection-string>ydb-connection-string</connection-string>
        
        <auth-mode>ENV</auth-mode>
        
        
        <tls-certificate-file>ca.crt</tls-certificate-file>
        
        <static-login>username</static-login>
        <static-password>password</static-password>
        
        <sa-key-file>ydb-sa-keyfile.json</sa-key-file>
        
        <replace-existing>true</replace-existing>
        
        <load-data>true</load-data>
        
        <max-batch-rows>1000</max-batch-rows>
        
        <max-blob-rows>200</max-blob-rows>
    </target>
    
    <table-options name="default">
        
        <case-mode>ASIS</case-mode>
        
        <table-name-format>oraimp1/${schema}/${table}</table-name-format>
        
        <blob-name-format>oraimp1/${schema}/${table}_${field}</blob-name-format>
        
        <conv-date>DATE_NEW</conv-date>
        <conv-timestamp>DATE_NEW</conv-timestamp>
        
        <allow-custom-decimal>true</allow-custom-decimal>
        
        <skip-unknown-types>true</skip-unknown-types>
        
        <use-source-partitions>true</use-source-partitions>
        
        <use-partition-buffers>true</use-partition-buffers>
        
        <ydb-partition-count>auto</ydb-partition-count>
    </table-options>
    
    <table-map options="default">
        
        <include-schemas regexp="true">.*</include-schemas>
        
        <exclude-schemas>SOMESCHEMA</exclude-schemas>
        
    </table-map>
    
    <table-ref options="default">
        <schema-name>ora$sys</schema-name>
        <table-name>all_tables</table-name>
        
        <query-text>SELECT * FROM all_tables</query-text>
        
        <key-column>OWNER</key-column>
        <key-column>TABLE_NAME</key-column>
        <!-- Splits the table into split-count value ranges along the
             split-by column for parallel reads. Column types:
             integers (TINYINT/SMALLINT/INTEGER/BIGINT), DECIMAL/NUMERIC,
             REAL/FLOAT/DOUBLE, DATE, TIMESTAMP. Bounds are written as
             yyyy-MM-dd for DATE or yyyy-MM-dd [HH:mm:ss[.fraction]] for
             TIMESTAMP. Values below split-from or NULL go to the first
             split, values above split-to go to the last.

split-by also accepts the auto literal, meaning the first
             primary key column (resolved while reading metadata, if the
             table has no primary key or its leading column has an
             unsupported type, the split is disabled). split-by may be
             omitted when another split-* tag is set, it then defaults to auto.

split-count, split-from, split-to can be set to auto: then split-count
             comes from ydb-partition-count, and split-from/to from the source MIN/MAX.
         -->
        <split-by>created_at</split-by>
        <split-from>2020-01-01 00:00:00</split-from>
        <split-to>2026-01-01 00:00:00</split-to>
        <split-count>auto</split-count>
        
        <use-source-partitions>true</use-source-partitions>
        <use-partition-buffers>false</use-partition-buffers>
        <ydb-partition-count>16</ydb-partition-count>
        
        <ydb-partition-from>1</ydb-partition-from>
        <ydb-partition-to>1000000</ydb-partition-to>
        
        <clob-column>note</clob-column>
    </table-ref>
</ydb-importer>
```

## 7. Building the tool from the source code

Java 8 or higher is required to build and run the tool. Maven is required to build the tool (tested on version 3.8.6).

To build the tool run the following command in the directory with the source code:
```bash
mvn package
```

After build is complete, the `target` subdirectory will contain the file `ydb-importer-X.Y-SNAPSHOT-bin.zip`, where `X.Y` is the version number.

ydb-importer

ydb-importer Latest Version Download

Today's picks

Was this content helpful?

Recent Feedback

ydb-importer

ydb-importer Latest Version Download

Today's picks

Was this content helpful?

Recent Feedback

Download README