Articles in this section

Client-side bulk load into SQL Server, PostgreSQL, and MySQL

  • Updated
Follow

Overview

Starting with version 9.9.9, Etlworks can bulk load staged CSV files into SQL Server, PostgreSQL, and MySQL using client-side bulk loaders: the staged file is streamed through the Etlworks application (or Integration Agent) and the destination JDBC connection, using each database's native bulk-ingestion API.

The key difference from traditional bulk-load SQL is where the file must be visible:

Database-executed SQL Client-side provider
Examples SQL Server BULK INSERT, PostgreSQL server-side COPY FROM, MySQL LOAD DATA INFILE SQLSERVER_BULK_COPY, POSTGRES_COPY, MYSQL_LOAD_DATA_LOCAL
Who reads the staged file The database server; it needs filesystem access to the resolved path (or cloud credentials) Etlworks; it streams the bytes over the JDBC connection
Works with cloud staging (S3, Azure Storage, Google Cloud Storage) Only if the database can access the object Yes — the database never sees the object or its credentials
Ingestion mechanism The SQL statement you write Native driver API: Microsoft JDBC bulk copy, PostgreSQL COPY ... FROM STDIN, Connector/J LOAD DATA LOCAL INFILE

Both approaches use the same flows and the same Bulk Load SQL field. Ordinary database SQL in that field continues to run exactly as before; a recognized leading directive switches the load to a client-side provider. The Bulk Load Builder generates either form for you.

Where it works

Staging can be Server Storage, Amazon S3, Azure Storage, or Google Cloud Storage. The staging format must be CSV. The existing insert, MERGE, and CDC MERGE behavior, mappings, scheduling, logging, and error handling all continue to apply after the staged data is loaded.

The established warehouse-specific bulk-load flows (Snowflake, Redshift, Synapse, BigQuery, Databricks, Oracle, Greenplum, Vertica) are unchanged and remain the right choice for those destinations.

Directive syntax

Enter the directive in the Bulk Load SQL field:

DIRECTIVE WITH (
    OPTION=value,
    ...
)

Directive and option names are case-insensitive. For the built-in providers, an unknown option is rejected with an actionable error. Anything that does not begin with a registered directive is executed as ordinary database SQL — full backward compatibility.

Note: Client-provider directives do not use the {PATH}/{FILE_TO_LOAD} file tokens — the staged CSV stream is supplied to the provider directly.

SQL Server Bulk Copy

SQLSERVER_BULK_COPY WITH (
    BATCH_SIZE=10000,
    TABLE_LOCK=AUTO,
    KEEP_NULLS=TRUE,
    KEEP_IDENTITY=FALSE,
    CHECK_CONSTRAINTS=FALSE,
    FIRE_TRIGGERS=FALSE,
    TIMEOUT=0
)

Streams the staged CSV through the Microsoft JDBC driver's bulk-copy API. It does not use BULK INSERT, and SQL Server never needs to see the staged file.

Option What it does
BATCH_SIZE Rows sent in each bulk-copy batch.
TABLE_LOCK AUTO enables a table lock when the load can safely use it.
KEEP_NULLS Preserve explicit null values instead of destination column defaults.
KEEP_IDENTITY Preserve identity values supplied by the staged CSV.
CHECK_CONSTRAINTS Check destination constraints during the load.
FIRE_TRIGGERS Fire destination insert triggers during the load.
TIMEOUT Bulk-copy timeout in seconds; 0 means no timeout.

Progress and completion row counts are written to the execution log.

Important: The SQL Server CSV reader is line-oriented. Values containing physical line breaks inside quoted fields may not be interpreted correctly; Etlworks logs a warning when the staging format enables multiline records.

PostgreSQL Copy

POSTGRES_COPY WITH (
    BUFFER_SIZE=65536,
    HEADER=AUTO,
    NULL_STRING=''
)

Streams the staged CSV with the PostgreSQL JDBC Copy API (COPY ... FROM STDIN) through the application connection — not server-side COPY FROM '/server/path', so the PostgreSQL server needs no access to the staging filesystem or cloud object.

Option What it does
BUFFER_SIZE Stream buffer size in bytes.
HEADER AUTO follows the CSV format's header/first-row configuration.
NULL_STRING The staged representation mapped to SQL NULL.

MySQL Load Data Local

MYSQL_LOAD_DATA_LOCAL WITH (
    DUPLICATE_ACTION=IGNORE,
    HEADER=AUTO,
    TIMEOUT=0
)

Streams the staged CSV through Connector/J LOAD DATA LOCAL INFILE — not server-side LOAD DATA INFILE against a server path.

Option What it does
DUPLICATE_ACTION Duplicate-row behavior for the load (for example, IGNORE).
HEADER AUTO follows the CSV format's header setting.
TIMEOUT Database operation timeout in seconds; 0 means no timeout.

Etlworks converts the configured Java date/time patterns into MySQL STR_TO_DATE expressions, and configured blank/null markers become SQL NULL — so blank temporal fields do not turn into invalid 0000-00-00 00:00:00 values under strict SQL modes.

Important — external prerequisites:

  • The Etlworks MySQL connection must include allowLoadLocalInfile=true (connection parameters).
  • The MySQL server must have local_infile=ON. In containers and managed installations, set it in the persistent server startup configuration — a runtime SET GLOBAL local_infile=ON is lost when the server restarts.

CSV staging requirements

Client-side providers require a CSV staging format with a non-empty delimiter, readable source metadata, at least one visible source field, a resolvable staged object name, and a stream accessible through the staging connection. Provider-specific restrictions on delimiter, enclosure, header, encoding, null values, and date/time formats are validated before or during the load.

Etlworks also validates known client directives when you save the flow: the staging connection must be Server Storage, S3, Azure Storage, or Google Cloud Storage; the staging format must be CSV; and the destination driver must match the provider. Ordinary SQL and unknown custom directives are not blocked by these checks.

Direct load and staging-table load

When the selected action allows it, the provider loads the destination table directly. For insert, MERGE, CDC MERGE, or any action that needs post-load mapping, the provider first loads a generated staging table, then Etlworks runs the established final insert/MERGE/CDC SQL with only the mapped destination fields, and cleans the staging object up afterward.

In the dedicated bulk-load flow, the staging table contains one column for every physical CSV column — including columns disabled in the mapping. Disabled fields stay out of the final operation (even when the destination has a matching column); enabled fields keep their mapped destination names. This prevents positional-CSV errors such as PostgreSQL's extra data after last expected column when the source file has more columns than the final mapping.

Parallel loading

Parallel loading of staged files is opt-in and controlled per flow type:

  • ETL flows: Use Parallel Threads for Wildcard Sources parallelizes independent objects matched by a wildcard; Use Parallel Threads for Split Files parallelizes the partitions produced when one extracted file is split (Maximum number of rows in file on the CSV format). The two are deliberately separate controls — wildcard files and split partitions are different work.
  • Bulk load files into the database: Use Parallel Threads for Files Loaded into the Same Table loads several matched files for one destination table concurrently. Maximum Number of Parallel Loads (default 10) bounds the concurrency.

Each parallel worker uses an independent destination JDBC connection: a successful worker commits, a failed worker rolls back. Connection-local temporary tables cannot be shared between workers, so:

  • SQL Server can use a global temporary table shared across workers and stays eligible for parallel load.
  • PostgreSQL and MySQL split-file parallel loads use a uniquely named physical staging table when a connection-local temporary table would hide data from other workers.
  • If the provider is sequential, the maximum is 1, or the target is a non-shareable temporary table, Etlworks logs the reason and loads sequentially.

Note on performance: a single client-side stream is not automatically faster than ordinary JDBC loading — in the engineering evaluation, the material improvement came from splitting large staged files and loading the partitions in parallel. Actual results depend on schema width, indexes, constraints, triggers, driver and database versions, network, staging storage, thread count, and database resources. Benchmark with your own data.

Custom providers (plugin SPI)

Additional client-side loaders can be added without changing Etlworks: implement the engine interface ClientBulkLoadExecutor and register the class in the engine configuration:

etl.client.bulk.load.executor.<lowercase_directive>=fully.qualified.ProviderClass

A registered class adds a new directive or overrides a built-in one. Contract highlights: do not close the supplied JDBC connection; return a row count when the database API provides one; declare parallel support only when one stateless instance can safely serve concurrent loads on independent connections (and temporary-table sharing only when independent connections can see the same table); read through the supplied staging stream rather than assuming a local file path.

Note: The Bulk Load Builder and save-time validation know only the built-in providers — a custom provider works at execution time but does not automatically appear in the Builder.

Limitations

  • Built-in client providers accept CSV staging only.
  • SQL Server: multiline quoted CSV fields are a documented line-oriented parser limitation.
  • MySQL: local_infile and the Connector/J local-infile permission are external prerequisites.
  • There is no ClickHouse client-side provider.
  • Large MySQL CDC MERGE (DELETE/INSERT) against tables without indexes can spend far longer in the post-load SQL than in staging. Select the Insert action when CDC MERGE semantics are not required.

Related articles