GBase 8a Load vs. Export Syntax Comparison

GBase 8a provides robust data loading (LOAD DATA INFILE) and data export (SELECT INTO OUTFILE) capabilities. While both share many syntactic elements, they also have distinct features. This article compares the core parameters side by side, with practical export examples for your gbase database.

Core Parameter Comparison

Attribute	Load	Export	Notes
Basic Syntax	LOAD DATA INFILE	SELECT INTO OUTFILE
File	file_list	file_name	Load supports multiple files; export only a single file name
Character Set	CHARACTER SET	CHARACTER SET
Data Format	DATA_FORMAT		Load‑only, e.g., DATA_FORMAT 6 for JSON
Data Format with Line Separator	HAVING LINES SEPARATOR
NULL Value	NULL_VALUE	NULL_VALUE	Load: ≤15 chars; Export: ≤32 chars, default \N
Field Delimiter	TERMINATED BY	TERMINATED BY	Load: ≤15 chars; Export: ≤10 chars
Field Enclosure	ENCLOSED BY	[OPTIONALLY] ENCLOSED BY	Single character; OPTIONALLY only for character types
Preserve Blanks	`PRESERVE [LEADING \	TRAILING] BLANKS`
Autofill	AUTOFILL
Fixed Length	LENGTH	LENGTH	Fixed‑width mode
Column Mapping	TABLE_FIELDS
Line Terminator	LINES TERMINATED BY	LINES TERMINATED BY	Max 10 chars
Max Bad Records	MAX_BAD_RECORDS
Date/Time Formats	DATETIME FORMAT, DATE FORMAT, TIMESTAMP FORMAT, TIME FORMAT		Load; export formats specified in TABLE_FIELDS
Error Logging	TRACE, TRACE_PATH
No File Split	NOSPLIT
Parallelism	PARALLEL, MAX_DATA_PROCESSORS, MIN_CHUNK_SIZE
Skip Missing Files	SKIP_BAD_FILE
Set Column Values	SET
Skip Header Lines	IGNORE NUM LINES
File Compression	FILE_FORMAT		UNDEFINED, UNCOMPRESSED, GZIP, SNAPPY, LZO
Escape Character		ESCAPED BY	Export; default \
BLOB Handling		BLOBMODE	Export: TYPE_TEXT, TYPE_BASE64, TYPE_URL
Line Prefix		LINES STARTING BY	Export
Double‑Enclosure Escaping		DOUBLE_ENCLOSED BY	Export
Export Destination		OUTFILEMODE BY	LOCAL or HDFS
Write Mode		WRITEMODE BY	NORMAL or OVERWRITES
Parallel File Count		FILECOUNT	HDFS export only
Max File Size		FILESIZE	Export; supports k/K/m/M/g/G suffix
Header Row		WITH HEAD	Optional for local exports

Export Examples

Add a Prefix to Each Line

SELECT * FROM test INTO OUTFILE '/home/gbase/test.txt'
FIELDS TERMINATED BY '|'
DOUBLE_ENCLOSED BY ','
ESCAPED BY '\\'
WRITEMODE BY OVERWRITES
LINES STARTING BY '----';

This prepends ---- to every output line, useful for embedding metadata.

Split Large Exports by File Size

When FILESIZE is specified, the export automatically splits into multiple files. The first file keeps the original name, and subsequent files get suffixes like _p1, _p2, etc. This is particularly useful for large datasets in a gbase database environment where downstream processing expects manageable file sizes.