All database connectors and datasets use JDBC. In addition to the JDBC parameters used to connect to the database, you might need to specify additional configuration parameters depending on the type of connection or dataset you are creating (Input/Output).
JDBC parameters for datasets created from a Query
The database query and table types are not compatible as you cannot use a query type database as a Destination dataset. Therefore if you try to change the database configuration to another type after saving it, a check will be triggered on your pipeline to see whether this operation is possible.
| Display Name | Description | Default value | Enabled if |
|---|---|---|---|
| SQL query | A valid read-only query if the source type is Query | - | Always enabled |
| Display Name | Description | Default value | Enabled if |
|---|---|---|---|
| Fetch size | Gives the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed for ResultSet objects generated by this Statement. If the value specified is zero, then the hint is ignored. | 1000 | Always enabled |
JDBC parameters for datasets created from a database Table
The database query and table types are not compatible as you cannot use a query type database as a Destination dataset. Therefore if you try to change the database configuration to another type after saving it, a check will be triggered on your pipeline to see whether this operation is possible.
| Display Name | Description | Default value | Enabled if |
|---|---|---|---|
| Table name | The name of the database table | - | Always enabled |
| Display Name | Description | Default value | Enabled if |
|---|---|---|---|
| Fetch size | Gives the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed for ResultSet objects generated by this Statement. If the value specified is zero, then the hint is ignored. | 1000 | Always enabled |
JDBC parameters for Output datasets (Destinations)
| Display Name | Description | Default value | Enabled if |
|---|---|---|---|
| Action | The action on data to be performed | - | Always enabled |
| Create table if not exists | Creates the database table if it does not exist yet | false | Action is equal to INSERT or UPSERT or BULK_LOAD |
| Varchar columns Length | The length of varchar types. This value will be used to create varchar columns in this table. -1 means that the max supported length of the targeted database will be used. | -1 | Create table if not exists is equal to true |
| Operation keys | List of columns to be used as keys for this operation | 0 | Create table if not exists is equal to true |
| Do not update columns | List of columns to be ignored from update | 0 | Action is equal to UPDATE or UPSERT |
| Distribution strategy | Defines the distribution strategy of a Redshift table | AUTO |
All of the following conditions are met:
|
| Distribution keys | List of columns to be used as distribution keys for Amazon Redshift | 0 |
All of the following conditions are met:
|
| Sort keys | List of columns to be used as sort keys for Amazon Redshift | 0 |
All of the following conditions are met:
|
| S3 connection | S3 connection list for Amazon Redshift bulk load | - |
All of the following conditions are met:
|
| S3 bucket | S3 bucket for Amazon Redshift bulk load | - |
All of the following conditions are met:
|
| Path | S3 upload directory path for Amazon Redshift bulk load | - |
All of the following conditions are met:
|
| Input validation | Checks the validity of the data file without actually loading the data. Use the NOLOAD parameter to make sure that your data file will load without any errors before running the actual data load. Running COPY with the NOLOAD parameter is much faster than loading the data because it only parses the files. | false |
All of the following conditions are met:
|
| Invalid character | Activates the invalid character replacement | - |
All of the following conditions are met:
|
| ACCEPTINVCHARS instructs COPY to replace each invalid character with a specified valid character and continue with the load operation. You can specify any valid ASCII character, except NULL, as the replacement character. The default replacement character is a question mark ( ? ). COPY replaces multibyte characters with a replacement string of equal length. For example, a 4-byte character would be replaced with '????' | - | Invalid character is equal to true | |
| Vacuum & Analyse | A vacuum recovers the space from deleted rows and restores the sort order | true |
All of the following conditions are met:
|
| Map input column names to output | Ensures that the input and output field names are identical | true |
| Display Name | Description | Default value | Enabled if |
|---|---|---|---|
| Rewrite batched statements | Rewrites batched statements to execute one statement per batch combining values in the SQL query | true |
One of these conditions is met:
|