Use the Database Destination component to store the output of your package in a relational database table.
The following database platforms are supported: PostgreSQL, MySQL, Microsoft SQL Server.
Destination components are always the last component in a package.
To define the Database destination:
- Add a Database Destination component at the end of your dataflow.
- Open the component and name it.
- Define the parameters for connecting to your Database as follows (for more information, see Allow Xplenty access to my Database):
- database connection - either click the drop-down arrow and select an existing connection, or click (create new) to create a new connection.
- target schema - the target table's schema. If empty, the default schema is used.
- target table - the name of the target table in your database.
- create table - automatically create the table defined in the component if it doesn't exist. See below for more information.
- add columns - automatically add columns that are mapped in the destination and do not exist in the table. See below for more information.
- max connections - maximum number of concurrent connections to open when writing to the database.
- split by field - when using more than one connection, this field will be used to split the data to the different connections. Pick a field with low density (a unique key is best) to make sure that data split isn't skewed.
- batch size - number of records that are inserted to the database in each batch (default 100).
- transaction per batch - if checked, each batch will be committed on its own which may lead to partial data in the target table in case the job fails. Otherwise, each connection will use a single transaction.
- operation type - the method of data insertion
- insert - default behavior. Data will only be appended to the target table.
- truncate and insert - truncate the target table before the data flow executes.
- delete and insert - deletes all of the target table before the data flow executes. If a truncate statement can't be executed on the target table due to permissions or other constraints, you can use this instead.
- merge - data will be inserted and/or updated in the target table according to the defined key(s). See details of the merge process below.
Note - It's important to select the proper key columns to use in order to merge data correctly.
- pre action sql - sql code to execute before inserting the data into the target table. If a merge operation is selected, the sql code is executed before the staging table is created.
- post action sql - sql code to execute after inserting the data into the target table. If a merge operation is selected, the sql code is executed after the staging table is merged into the target table.
Then click the Test Connection button to help check that the connection is good and that the target table exists.
|String||varchar, char, text, interval||varchar, nvarchar, text,||varchar, nvarchar, text, ntext|
|Integer||smallint, int||bit, bool, tinyint, smallint, mediumint, int, integer||tinyint, smallint, int|
|Float||decimal, real||decimal, float||decimal, numeric, float|
|DateTime||timestamp, date, time||date, datetime, timestamp, time||datetime, datetime2, smalldatetime, date, time, datetimeoffset|
* Note - Use the ToDate function to cast a datetime string expression to datetime data type. Note that a datetime with timezone offset value will be adjusted to UTC when inserted into a database
Automatically creating and altering destination table
Xplenty can automatically create the destination table for you if it doesn't exist and it can also append columns to an existing table. If you define columns as key (regardless of the operation type), Xplenty defines them as the primary key in a new table. The data types in Xplenty are automatically mapped as follows. Note that since Xplenty doesn't have a notion of maximum string length, the string columns are created with the maximum length allowed in the database.
Merging data into an existing table
The merge operation is done in these steps:
- First transaction - A staging table is created with a primary key according to your key fields (in the same order) in the database's default schema with the target table's schema.
- Note: The incoming data must be unique according to the key fields you selected. You may use the aggregate component or the limit component to make sure that the key fields are indeed unique. A workaround for this kind of failure can be to use a Limit component and add the key field/s as a partition and limit it to 1 thus removing duplicates.
- Second transaction - The dataflow's output is bulk copied into the staging table.
- Third transaction - Rows with keys that exist in the bulk copied data are deleted from the target table. All rows in the staging table are inserted into the target table. The staging table is deleted.