Load Data

SynxDB integrates with external systems, including big data systems, using external tables. This approach leverages a variety of loading tools and protocols, allowing you to choose the most suitable method based on the data source. The system loads external data by creating an external table that references the source, and then uses SQL commands to read data from or write data to it.

Data loading process

The general process of loading data into SynxDB is as follows:

1. Evaluate the data loading scenario (such as data source location, data types, and data volume) and choose the appropriate loading tool.

2. Configure and activate the loading tool.

3. Create an external table, specifying the loading tool protocol, data source address, and data format in the CREATE EXTERNAL TABLE statement. For details, see Load External Data Using Foreign Tables.

4. Once the external table is created, you can directly query the data using the SELECT statement or load the data into a table using the INSERT INTO SELECT statement.

Loading tools and scenarios

SynxDB provides multiple data loading solutions, allowing you to choose different methods based on the data source.

Loading method/tool	Data source	Data format	Parallel loading
COPY	Local file system (Coordinator node for single files; Segment nodes for multiple files)	TXT, CSV, Binary	No
file:// protocol	Local file system (local Segment nodes, only accessible by superusers)	TXT, CSV	Yes
gpfdist	Local host files or files accessible over the intranet	TXT, CSV, any delimited text format supported by the FORMAT clause, XML and JSON (converted to text via YAML configuration)	Yes
Bulk loading using gpload (using gpfdist as the underlying component)	Local host files or files accessible over the intranet	TXT, CSV, any delimited text format supported by the FORMAT clause, XML and JSON (converted to text via YAML configuration)	Yes
Create external Web tables	Data fetched from web services or any source accessible via command line	TXT, CSV	Yes
Kafka FDW	Kafka	CSV, JSON	Yes
Data Lake Connector	Public cloud object storage (for example, Amazon S3), Hadoop storage	CSV, TEXT, ORC, PARQUET	Yes
Iceberg tables (via CREATE ICEBERG TABLE and datalake_fdw, with ACID read/write)	Object storage (S3), HDFS, Hive Metastore, Polaris	Apache Iceberg	Yes
Hive Connector with datalake_fdw	Hive data warehouse	CSV, TEXT, ORC, PARQUET, Iceberg, Hudi	Yes
PXF	HDFS, Hive, HBase, Relational databases via JDBC	Depends on the connector. Common formats include: Text, CSV, JSON, Avro, Parquet, ORC	Yes
mysql_fdw	MySQL	MySQL table data	Yes
cloudberry_fdw	Remote SynxDB cluster	Table data (via foreign table)	Yes