ColumnStore provides several mechanisms to ingest data:

• cpimport provides the fastest performance for inserting data and ability to route data to particular PrimProc nodes. Normally, this should be the default choice for loading data .

• LOAD DATA INFILE provides another means of bulk inserting data.

  • By default, with autocommit on, it internally streams the data to an instance of the cpimport process.

  • In transactional mode, DML inserts are performed, which is significantly slower and also consumes both binlog transaction files and ColumnStore VersionBuffer files.

• DML, i.e. INSERT, UPDATE, and DELETE, provide row-level changes. ColumnStore is optimized towards bulk modifications, so these operations are slower than they would be in, for instance, InnoDB.

  • Currently ColumnStore does not support operating as a replication replica target.

• Using ColumnStore Bulk Write SDK or .

_{This page is: Copyright © 2025 MariaDB. All rights reserved.}

The ColumnStore Bulk Data API enables the creation of higher performance adapters for ETL integration and data ingestions. The Streaming Data Adapters are out of box adapters using these API for specific data sources and use cases.

• MaxScale CDC Data Adapter is integration of the MaxScale CDC streams into MariaDB ColumnStore.

• Kafka Data Adapter is integration of the Kafka streams into MariaDB ColumnStore.

MaxScale CDC Data Adapter

The MaxScale CDC Data Adapter allows streaming change data events (binary log events) from MariaDB Master hosting non-columnstore engines (InnoDB, MyRocks, MyISAM) to MariaDB ColumnStore. In other words, replicate data from a MariaDB master server to MariaDB ColumnStore. It acts as a CDC Client for MaxScale and uses the events received from MaxScale as input to MariaDB ColumnStore Bulk Data API to push the data to MariaDB ColumnStore.

It registers with MariaDB MaxScale as a CDC Client using the , receiving change data records from MariaDB MaxScale (that are converted from binlog events received from the Master on MariaDB TX) in a JSON format. Then, using the MariaDB ColumnStore bulk write SDK, it converts the JSON data into API calls and streams it to a MariaDB PM node. The adapter has options to insert all the events in the same schema as the source database table or insert each event with metadata as well as table data. The event meta data includes the event timestamp, the GTID, event sequence and event type (insert, update, delete).

• Download and install MaxScale CDC Connector API from .

• Download and install MariaDB ColumnStore bulk write SDK from columnstore-bulk-write-sdk.md.

To stream multiple tables, use the -f parameter to define a path to a TSV formatted file. The file must have one database and one table name per line. The database and table must be separated by a TAB character and the line must be terminated in a newline (\n).

Here is an example file with two tables, t1 and t2 both in the test database:

You can have the adapter automatically create the tables on the ColumnStore instance with the -an option. In this case, the user used for cross-engine queries will be used to create the table (the values in ColumnStore.CrossEngineSupport). This user requires CREATE privileges on all streamed databases and tables.

The -z option enables the data transformation mode. In this mode, the data is converted from historical, append-only data to the current version of the data. In practice, this replicates changes from a MariaDB master server to ColumnStore via the MaxScale CDC.

Download and install both and .

Copy the Columnstore.xml file from /usr/local/mariadb/columnstore/etc/ColumnStore.xml from one of the ColumnStore PrimProc nodes to the server where the adapter is installed.

Configure MaxScale according to the .

Create a CDC user by executing the following MaxAdmin command on the MaxScale server. Replace the <service> with the name of the avrorouter service and <user> and <password> with the credentials that are to be created.

Then we can start the adapter by executing the following command.

The <database> and <table> define the table that is streamed to ColumnStore. This table should exist on the master server where MaxScale is reading events from. If the table is not created on ColumnStore, the adapter will print instructions on how to define it in the correct way.

The <user> and <password> are the users created for the CDC user, <host> is the MaxScale address and <port> is the port where the CDC service listener is listening.

The -c flag is optional if you are running the adapter on the server where ColumnStore is located.

The Kafka data adapter streams all messages published to Apache Kafka topics in Avro format to MariaDB ColumnStore automatically and continuously - enabling data from many sources to be streamed and collected for analysis without complex code. The Kafka adapter is built using and the MariaDB ColumnStore bulk write SDK

A tutorial for the Kafka adapter for ingesting Avro formatted data can be found in the document.

Starting with MariaDB ColumnStore 1.1.4, a data adapter for Pentaho Data Integration (PDI) / Kettle is available to import data directly into ColumnStore’s WriteEngine. It is built on MariaDB’s rapid-paced Bulk Write SDK.

The plugin was designed for the following software composition:

• Operating system: Windows 10 / Ubuntu 16.04 / RHEL/CentOS 7+

• MariaDB ColumnStore >= 1.1.4

• MariaDB Java Database client* >= 2.2.1

• Java >= 8

*Only needed if you want to execute DDL.

The following steps are necessary to install the ColumnStore Data adapter (bulk loader plugin):

1. Build the plugin from or download it from our

2. Extract the archive mariadb-columnstore-kettle-bulk-exporter-plugin-*.zip into your PDI installation directory $PDI-INSTALLATION/plugins.

3. Copy mariadb-java-client-2.2.x.jar into PDI's lib directory $PDI-INSTALLATION/lib.

On Windows the installation of the is required.

Each MariaDB ColumnStore Bulk Loader block needs to be configured. On the one hand, it needs to know how to connect to the underlying Bulk Write SDK to inject data into ColumnStore, and on the other hand, it needs to have a proper JDBC connection to execute DDL.

Both configurations can be set in each block’s settings tab.

The database connection configuration follows PDI’s default schema.

By default, the plugin tries to use ColumnStore's default configuration /usr/local/mariadb/columnstore/etc/ColumnStore.xml to connect to the ColumnStore instance through the Bulk Write SDK. In addition, individual paths or variables can be used too.

Information on how to prepare the ColumnStore.xml configuration file can be found here.

Once a block is configured and all inputs are connected in PDI, the inputs have to be mapped to ColumnStore’s table format.

One can either choose “Map all inputs”, which sets target columns of adequate type, or choose a custom mapping based on the structure of the existing table.

The SQL button can be used to generate DDL based on the defined mapping and to execute it.

This plugin is a beta release.

In addition, it can't handle blob data types and only supports multiple inputs to one block if the input field names are equal for all input sources.

_{This page is: Copyright © 2025 MariaDB. All rights reserved.}

Overview

cpimport is a high-speed bulk load utility that imports data into ColumnStore tables in a fast and efficient manner. It accepts as input any flat file containing data that contains a delimiter between fields of data (i.e. columns in a table). The default delimiter is the pipe (‘|’) character, but other delimiters such as commas may be used as well. The data values must be in the same order as the create table statement, i.e. column 1 matches the first column in the table and so on. Date values must be specified in the format 'yyyy-mm-dd'.

cpimport – performs the following operations when importing data into a MariaDB ColumnStore database:

• Data is read from specified flat files.

• Data is transformed to fit ColumnStore’s column-oriented storage design.

• Redundant data is tokenized and logically compressed.

• Data is written to disk.

It is important to note that:

• The bulk loads are an append operation to a table, so they allow existing data to be read and remain unaffected during the process.

• The bulk loads do not write their data operations to the transaction log; they are not transactional in nature but are considered an atomic operation at this time. Information markers, however, are placed in the transaction log so the DBA is aware that a bulk operation did occur.

• Upon completion of the load operation, a high-water mark in each column file is moved in an atomic operation that allows for any subsequent queries to read the newly loaded data. It appends operation provides for consistent read but does not incur the overhead of logging the data.

There are two primary steps to using the cpimport utility:

1. Optionally create a job file that is used to load data from a flat file into multiple tables.

2. Run the cpimport utility to perform the data import.

The simplest form of cpimport command is

The full syntax is like this:

In this mode, you run the cpimport from your primary node (mcs1). The source file is located at this primary location and the data from cpimport is distributed across all the nodes. If no mode is specified, then this is the default.

Example:

In this mode, you run the cpimport from your primary node (mcs1). The source data is in already partitioned data files residing on the PMs. Each PM should have the source data file of the same name but containing the partitioned data for the PM

Example:

In this mode, you run cpimport from the individual nodes independently, which will import the source file that exists on that node. Concurrent imports can be executed on every node for the same table.

Example:

Note:

• The bulk loads are an append operation to a table, so they allow existing data to be read and remain unaffected during the process.

• The bulk loads do not write their data operations to the transaction log; they are not transactional in nature but are considered an atomic operation at this time. Information markers, however, are placed in the transaction log so the DBA is aware that a bulk operation did occur.

• Upon completion of the load operation, a high-water mark in each column file is moved in an atomic operation that allows for any subsequent queries to read the newly loaded data. It appends operation provides for consistent read but does not incur the overhead of logging the data.

Data can be loaded from STDIN into ColumnStore by simply not including the loadFile parameter

Example:

Similarly the AWS cli utility can be utilized to read data from an s3 bucket and pipe the output into cpimport allowing direct loading from S3. This assumes the aws cli program has been installed and configured on the host:

Example:

For troubleshooting connectivity problems remove the --quiet option which suppresses client logging including permission errors.

Standard in can also be used to directly pipe the output from an arbitrary SELECT statement into cpimport. The select statement may select from non-columnstore tables such as or . In the example below, the db2.source_table is selected from, using the -N flag to remove non-data formatting. The -q flag tells the mysql client to not cache results which will avoid possible timeouts causing the load to fail.

Example:

Let's create a sample ColumnStore table:

Now let's create a sample products.json file like this:

We can then bulk load data from JSON into Columnstore by first piping the data to and then to using a one-line command.

Example:

In this example, the JSON data is coming from a static JSON file, but this same method will work for, and output streamed from any datasource using JSON such as an API or NoSQL database. For more information on 'jq', please view the manual here .

There are two ways multiple tables can be loaded:

1. Run multiple cpimport jobs simultaneously. Tables per import should be unique or for each import should be unique if using mode 3.

2. Use colxml utility: colxml creates an XML job file for your database schema before you can import data. Multiple tables may be imported by either importing all tables within a schema or listing specific tables using the -t option in colxml. Then, using cpimport, that uses the job file generated by colxml. Here is an example of how to use colxml and cpimport to import data into all the tables in a database schema

The following tables comprise a database name ‘tpch2’:

1. First, put delimited input data file for each table in /usr/local/mariadb/columnstore/data/bulk/data/import. Each file should be named .tbl.

2. Run colxml for the load job for the ‘tpch2’ database as shown here:

Now actually run cpimport to use the job file generated by the colxml execution

If there are some differences between the input file and table definition then the colxml utility can be utilized to handle these cases:

• Different order of columns in the input file from table order

• Input file column values to be skipped / ignored.

• Target table columns to be defaulted.

In this case run the colxml utility (the -t argument can be useful for producing a job file for one table if preferred) to produce the job xml file and then use this a template for editing and then subsequently use that job file for running cpimport.

Consider the following simple table example:

This would produce a colxml file with the following table element:

If your input file had the data such that hire_date comes before salary then the following modification will allow correct loading of that data to the original table definition (note the last 2 Column elements are swapped):

The following example would ignore the last entry in the file and default salary to it's default value (in this case null):

• IgnoreFields instructs cpimport to ignore and skip the particular value at that position in the file.

• DefaultColumn instructs cpimport to default the current table column and not move the column pointer forward to the next delimiter.

Both instructions can be used independently and as many times as makes sense for your data and table definition.

It is possible to import using a binary file instead of a CSV file using fixed length rows in binary data. This can be done using the '-I' flag which has two modes:

• -I1 - binary mode with NULLs accepted Numeric fields containing NULL will be treated as NULL unless the column has a default value

• -I2 - binary mode with NULLs saturated NULLs in numeric fields will be saturated

The following table shows how to represent the data in the binary format:

For NULL values the following table should be used:

The spare bits in the Date struct "must" be set to 0x3E.

As of version 1.4, cpimport uses the /var/lib/columnstore/bulk folder for all work being done. This folder contains:

1. Logs

2. Rollback info

3. Job info

4. A staging folder

The log folder typically contains:

A typical log might look like this:

Prior to version 1.4, this folder was located at /usr/local/mariadb/columnstore/bulk.

_{This page is: Copyright © 2025 MariaDB. All rights reserved.}

Description

The ColumnStore Insert Cache is a performance optimization feature that improves INSERT operations in MariaDB ColumnStore, especially for write-heavy workloads. It uses a hidden Aria table that serves as a fast front‑end buffer before data is flushed to ColumnStore's columnar storage.

When this feature is enabled, inserts are first written to a lightweight internal Aria table (the cache table), and later flushed in batches to the ColumnStore storage engine. Because Aria is a built-in, memory‑optimized storage engine, inserts into its cache table are considerably faster than inserting directly into ColumnStore. This significantly improves insert performance by reducing the overhead of writing directly to ColumnStore for each row.

A write-heavy workload consists of:

• High volumes of INSERT statements

• Occasional DELETE, UPDATE, or SELECT statements

This feature was originally developed for deployments, in which an InnoDB primary replicates to a ColumnStore replica (including single- and multi-node deployments), allowing the ColumnStore replica to maintain with the InnoDB primary's high insert rates.

This insert cache feature is not limited to HTAP environments; it can also be enabled in standalone ColumnStore deployments.

By default, the insert cache is disabled. When enabled, the insert cache significantly improves insert throughput.

is designed for analytical queries and large-scale batch workloads. However, workloads that rely on frequent small inserts may see lower throughput when writing directly to ColumnStore.

The insert cache feature addresses this by:

• Buffering inserts in a lightweight storage engine (Aria)

• Flushing cached data in larger batches

• Reducing write amplification and SQL layer overhead

This makes ColumnStore particularly well-suited for write-heavy workloads, HTAP deployments, replication scenarios where data is moved from InnoDB to ColumnStore, and environments that involve frequent INSERT statements or LOAD DATA INFILE operations.

When this feature is enabled, the performance of the following types of inserts is improved:

• Single-row INSERT statements

• Multi-row INSERT statements

• LOAD DATA INFILE statements

Other DML operations such as UPDATE, DELETE, and INSERT ... SELECT do not benefit from the insert cache.

When columnstore_cache_inserts=ON:

1. A hidden Aria table (cache table) is automatically created internally when a new ColumnStore table is created.

2. This Aria table acts as a front-end for incoming inserts, writing operations rapidly to the cache table using MariaDB’s lightweight storage engine.

3. Cached rows are flushed into the actual ColumnStore table when one of the following event occurs:

The flush operation writes accumulated rows to ColumnStore in bulk.

Ideal use cases for ColumnStore include:

• Write-heavy workloads

• Environments with high-frequency insert operations

• Replication from InnoDB to ColumnStore

• HTAP deployments

Avoid enabling when:

• Strict transactional behavior with rollback support are required

• Workloads are update-heavy rather than insert-heavy

• Flush fragmentation caused by cpimport is undesirable

The insert cache is disabled by default. The following system variable manages the insert cache:

1. To enable the insert cache, add the following to your MariaDB configuration file:

1. After modifying the setting, restart the MariaDB server.

2. Once the server is restarted, verify the setting with:

This setting applies globally to all ColumnStore tables created while it is enabled.

Defines the number of cached rows that trigger an automatic flush to ColumnStore.

When set to lower values:

• More frequent flushes

• Potentially reduced performance

When set to higher values:

• Fewer flushes

• Larger batch writes

The optimal value depends on workload characteristics.

When enabled, cache flushes use cpimport instead of the internal batch SQL processing mode.

Default Behavior (OFF)

• Flush uses ColumnStore's internal batch processing mode

• Writes go through the SQL layer

When Enabled (ON)

• Flush uses cpimport

• Bypasses the ColumnStore SQL layer

• Significantly improves flush performance for large batches

Important Consideration

When columnstore_cache_use_import=ON is enabled, frequent flushes can cause disk fragmentation in database files. This occurs because each cpimport operation begins inserting data at a new block boundary, creating fragmentation (“holes”) with every flush.

DBAs should evaluate workload characteristics before enabling this option.

Scope: Global Dynamic: No (requires restart) Default: OFF Description: Enables or disables the insert cache feature globally

Scope: Global / Session Default: 500000 Description: Maximum number of rows to cache before automatic flush

Scope: Global Default: OFF Description: Use cpimport for cache flush instead of batch processing mode

For comprehensive details on system configuration variables, refer to .

Internal benchmarking showed significant improvements in insert throughput:

• ~4642x improvement for 50,000 single-row inserts

• ~4763x improvement for 100,000 single-row inserts

These benchmarks were performed before the introduction of columnstore_cache_use_import. Enabling import-based flush mode may provide additional improvements depending on workload.

Actual performance gains depend on:

• Insert batch size

• Flush threshold configuration

• Use of replication

• Storage configuration

The insert cache was originally designed for HTAP deployments where:

• An InnoDB master manages transactional workloads

• A ColumnStore replica handles analytical queries

• High insert rates must be maintained on the replica

With this configuration, the insert cache enables the ColumnStore replica to handle replication traffic more efficiently.

The feature is supported for:

• Single-node ColumnStore deployments

• Multi-node ColumnStore clusters

It is not limited to replication scenarios and can be used in standalone ColumnStore environments.

The insert cache is a global setting. It is not configurable per table. All ColumnStore tables created while the cache is enabled will use the insert cache.

ColumnStore tables created with the insert cache enabled create corresponding hidden Aria cache tables. These tables must be dropped while the insert cache remains enabled. Dropping such tables with columnstore_cache_inserts=OFF may leave orphaned hidden Aria tables.

Rows that remain unflushed in the insert cache are not visible to queries executed after the cache is disabled; they only become accessible once the cache is re‑enabled and flushed.

Because the cache uses Aria at the front-end, which is a non-transactional storage engine:

• ROLLBACK is not supported for cached inserts

• The insert cache is not suitable for transactional workloads that require rollback support

The insert cache feature has certain limitations, including:

• No rollback support for cached inserts

• Global-only configuration (not per-table)

• Potential file fragmentation when using import mode

• Non-insert DML operations such as INSERT..SELECT