Bulk-load data into a database
Use the DB Loader node to load large amounts of data into a database table using the database's native bulk-loading functionality. Bulk loading is faster than row-by-row insertion for large datasets.
Supported databases include Hive, Impala, MySQL, PostgreSQL, and H2.
Before you begin
- Establish a database connection. See Connect to a database using a dedicated connector node.
- Ensure the target table already exists in the database. DB Loader does not create tables.
- Confirm that the column order and column names of the KNIME input table match the target database table.
Data integrity
Most databases do not perform data type or value checks during bulk loading. DB Loader verifies column order and names, but does not check column types or values. Ensure the KNIME table is compatible with the target database table before executing.
Load data
Configure the DB Loader node to bulk-load the connected KNIME table into the target database table.
- Connect the DB Connection output port to a DB Loader node.
- Connect the KNIME data table to the second input port.
- Click the node once to open its configuration.
- In the Options tab, select the Loader mode if the database supports multiple methods (for example, file-based or memory-based for MySQL and PostgreSQL).
- If the database uses a CSV file for the transfer (for example, MySQL and PostgreSQL), configure the CSV format in the Advanced tab.
- If the loading method requires uploading a file to a server, provide a file connection using any protocol supported by the file handling nodes and the database (for example, SSH/SCP or FTP).
- Click Apply and Execute.
TIP
If you do not have the rights to execute file-based loading, try the memory-based method instead.