Use the Database Source component, to load data from a database table or view.
- Define the parameters for connecting to your database table as follows (for more information, see Allow Xplenty access to my database server):
- database connection - either click the drop-down arrow and select an existing connection, or click (create new) to create a new connection.
- access mode - select table to extract an entire table/view or query to execute a user query.
- source schema - the source table's schema. If empty, the default schema is used.
- source table - the table or view name from which the data will be imported.
- split by column/max connections (optional) - use these fields to specify the degree of parallelism used to import the source table (If you leave these fields blank, the source table data is imported in a single task).
- split by column - specify the column name that will be used as a criterion to split the import workload. The total range between the low and high values of this column will be evenly split between the number of connections (tasks) specified in
max connections. We recommend using a column that is uniformly distributed across its value range. We will use the primary key by default.
Note: Splitting by a textual column may result in a partial or duplicate records if your database sorts in a case-insensitive order.
- max connections - an integer specifying how many tasks to assign to the import process.
Note: Do not increase the number of tasks above what your database can reasonably support.
- split by column - specify the column name that will be used as a criterion to split the import workload. The total range between the low and high values of this column will be evenly split between the number of connections (tasks) specified in max connections. We recommend using a column that is uniformly distributed across its value range. We will use the primary key by default.
- where clause - optional. You can add predicates clauses to the WHERE clause as part of the SQL query that is built in order to get the data from the database. Make sure to skip the keyword WHERE. e.g.:
prod_category = 1 AND prod_color = 'red'
WHERE prod_category = 1 AND prod_color = 'red'
- or query - type in a SQL query. Make sure to name all columns uniquely. When using regular expressions, make sure to escape
\\gets transformed to
- pre-process action - You can select copy to copy the data from the database source to an intermediate storage before processing the data or select none to read the data from the database and apply transformations or store it into the destination immediately. copy may keep the database connections open for shorter periods of time, but using none would usually result in quicker job execution times.
Then click the Test Connection button to help check that the connection is good and that the source table exists.
- After defining the source settings you can use the green Auto-detect schema button to get the field names and data types or the Preview button to preview the data and fill in the fields manually.
- Define the columns you want to extract from the table as follows:
- Define the column name in the table.
- Define the alias you will use for the column as a field in the data-flow.
- Define the data type for the field. Use the following table when matching database data types to Xplenty data types.
|varchar, char, text, time, interval||varchar, nvarchar, text, time||varchar, nvarchar, text, ntext, time, datetimeoffset||longnvarchar, nchar, nvarchar, longvarchar, char, varchar, clob, nclob||String|
|smallint, int||bit, bool, tinyint, smallint, mediumint, int, integer||tinyint, smallint, int||tinyint, integer, smallint||Integer|
|decimal, real||decimal, float||decimal, numeric, float||float, binary float, real||Float|
|double precision||double||real||numeric, decimal, binary double||Double|
|timestamp, date||date, datetime, timestamp||datetime, date, datetime2, smalldatetime||date, time, timestamp, timestamptz, timestampltz||DateTime|
Note: The query is executed in the read-committed transaction isolation level.