11.10 Data Ingestion and Export

Choose SQL, Append APIs, or file tools according to data volume and operational needs. This page covers selection and verification; see tool and SQL references for full options.

Choose an Input Method

Compare Paths and Tools

tagmetaimport does not ingest TAG measurements. See Command-Line Tools for exact options.

Use TAG or LOG for time-series/events requiring preservation of original data. Use TRANSACTION for relational changes, LOOKUP for small reference data, and VOLATILE for rebuildable in-memory caches. After choosing a table, select the input method based on expected counts, latency tolerance, retry scope, and duplicate policy.

SQL INSERT

Run this example in order, from creation through cleanup:

CREATE LOG TABLE integration_insert_demo (
    event_time DATETIME,
    sensor_id  VARCHAR(32),
    value      DOUBLE
);

INSERT INTO integration_insert_demo
VALUES (TO_DATE('2026-01-01 00:00:00'), 'TEMP-01', 25.3);

SELECT sensor_id, value
FROM integration_insert_demo;

DROP TABLE integration_insert_demo;

In applications, bind values as prepared parameters and check returned affected-row counts.

Append API

Append opens a table through an SDK-specific API, sends multiple rows, then flushes and closes. Match column order and types to the schema, and use a separate connection from ordinary queries. See SDK pages for complete language-specific examples.

Machbase DBMS 8.7.0 can select input columns or ARRAY elements at Append Open. Use SDK sparse ARRAY objects when positions vary by row. See Sparse ARRAY and Selected-Column Append API for selection criteria, APIs, and validation examples.

LOAD DATA INFILE

LOAD DATA INFILE loads server-accessible files through SQL. Paths are interpreted from the server process, so check:

• The file exists on the server host.
• The server process account can read it.
• Delimiters, quoting, encoding, and date format match the source.
• Log/error-row file locations are defined for identifying failed rows.

See LOAD DATA INFILE for syntax and supported options.

Prepare CSV Files

Decide whether the first row is a header, and keep column count and order consistent. Test NULLs, empty strings, strings with delimiters, line breaks, and DATETIME formats using sample files. Validate schemas and conversion rules on a small sample before loading a large file.

Import with machloader

Basic syntax:

"$MACHBASE_HOME/bin/machloader"   -s 127.0.0.1 -P 5656   -u APP_USER -p "$MACH_SAMPLE_PASSWORD"   -i -t SENSOR_LOG -d /data/sensor.csv   -l /data/sensor.log -b /data/sensor.bad

Use -H for headers, -D for a non-comma delimiter, and -F for a different date format. See machloader for all options.

Import with csvimport

csvimport simplifies commonly used machloader CSV options.

"$MACHBASE_HOME/bin/csvimport"   -s 127.0.0.1 -P 5656   -u APP_USER -p "$MACH_SAMPLE_PASSWORD"   -t SENSOR_LOG -d /data/sensor.csv -H   -l /data/sensor.log -b /data/sensor.bad

Automatic creation with -C may not assign the intended business type to every column. For production loads, create the table explicitly and verify its schema first.

Choose an Export Method

File Ownership and Paths

SAVE DATA INTO paths and permissions are relative to the server process. Files from machloader and csvexport use the OS account running the tool. Avoid relative paths, and check overwrite policy and available disk space first.

SAVE DATA INTO

Use this to export filtered SQL results. Before running against a production path, verify file creation, encoding, and headers with small results in a separate test location. See SAVE DATA INTO for full syntax.

Export with machloader

"$MACHBASE_HOME/bin/machloader"   -s 127.0.0.1 -P 5656   -u APP_USER -p "$MACH_SAMPLE_PASSWORD"   -o -t SENSOR_LOG -d /data/sensor-export.csv -H   -l /data/sensor-export.log

Export with csvexport

"$MACHBASE_HOME/bin/csvexport"   -s 127.0.0.1 -P 5656   -u APP_USER -p "$MACH_SAMPLE_PASSWORD"   -t SENSOR_LOG -d /data/sensor-export.csv -H   -l /data/sensor-export.log

Batch Processing

• Load-test batch sizes against row size and latency requirements.
• Record each batch’s source offset and successful target count.
• On partial failure, isolate and retry failed rows instead of the whole batch.
• Define business keys and duplicate policies for safe row retransmission.

Handle Bulk Ingestion Errors

1. Check the tool exit code and summary counts.
2. Find the server error code and first failure cause in logs.
3. Compare error-row column counts, types, NULLs, date formats, and encoding with the source.
4. Retest a small corrected file, then reload only failed rows.
5. Verify final table counts, time ranges, and sample rows.

Logs and error-row files may contain credentials or raw sensitive data. Set access permissions and retention periods.