...
When exporting tables to a database, the following data details may have to be defined:
| Topic | Description |
|---|
| 'Database' | - select the name of the database, the export shall be executed to
|
| 'Table' | - select between 'Use existing' and 'Create new if not exist'
|
| 'Name' | - name of the newly created table in the database if there is no existing table - if a table with the same name already exists, it will be reused
|
| 'Table Output Format' | - select from the available output formats - the new table will be created with the selected file format; if the table exists, the file format will be ignored
|
| 'Record Replacement Policy' | - select between 'Drop and create new table', 'Overwrite records' and 'Append records'
| Info |
|---|
| Use existing If the 'Use existing' option is selected Datameer X doesn't create a primary key column with name "ID". (i.e., The workbook must be aware of the ID column. All columns from the destination table (can't be null) must be mapped to a column of the workbook.) - 'Append Mode': the records append to the existing table
- 'Overwrite Mode': all records from the existing table are deleted while new records are written to the destination table
| Tip |
|---|
| | Tip: always select 'Use existing' for tables created outside of Datameer X e.g., Netezza, and never select 'Use existing' for tables created via Datameer |
Create new if not exist If the 'Create new if not exist' option is selected Datameer X creates an additional column with name "ID" (primary key). When the database supports auto increment, the database computes the value of this column. Example: Netezza doesn't support auto increment. In this case Datameer X uses a sequence database object to generate the ID. Specific for Netezza: the name of the sequence used is <tablename>_seq. - 'Append Mode': the table is created if it doesn't already exists, otherwise the existing table is re-used without changing any schema; the records are appended to the existing table
- 'Overwrite Mode': any previous tmp table is first dropped; a tmp table is created; the records are written to the tmp table; the entire destination table is dropped if one exists; the tmp table is renamed to the destination table; the tmp table is dropped
| Tip |
|---|
| | Tip: always select 'Create new if not exists' for tables created via Datameer X and never select 'Create new if not exists' for tables created outside of Datameer X e.g. Netezza |
Common problems - Export a workbook into a table via 'Create new if not exists'.
- A new table is created with an additional primary key column with name "ID".
- Export a second workbook with Use existing into the same table.
- This fails because the ID is not filled. (See description above)
|
|
| 'Advanced' | - Setting the 'Maximum number of concurrent data base connections' gives control of database parallelism. The export job can't launch more than the set number of parallel tasks. Each task opens one database connection.
- Setting the 'Maximum records per transaction' exports approximately this number of records within a single database transaction.
- Setting the 'Rows per batch' establishs a limit for the number of rows inserted for each transaction within a task.
INFO: Tuning this value can influence speed and avoid potential timeouts. Very large batches can overwhelm some databases and limiting the number of rows returned at a time can reduce strain.
|
Exporting Through a JDBC Connection
...
- Right-click the export job and select "Delete" or highlight the export job and select "Edit" → "Delete" in the menu bar. The dialog 'Delete Export Job:<export job name>' opens.
- Confirm with "Delete". The export job is deleted.
Priorizing an Export Job in the Job Scheduler
| Info |
|---|
|
| If needed, you can execute priorized export jobs by bypassing the Job Scheduler's queue. For that, according role capabilities are needed as well as cluster resources. |
To priorize an export job when configuring it in the export job wizard, simply mark the check box "Bypass Internal Job Queue". The export job will be executed right after finishing the import job wizard.
Image Added
Knowledge Base Articles on Exporting Data
...