Exporting Data to a Database

Find here all information for exporting data to a database.

INFO

Spectrum does not support all dialects. Databases that are not listed but that are compatible with a supported dialect can be requested from support.

Relational databases include Oracle, DB2, and MySQL.

Prerequisites

Having the Database Prepared

INFO

The database to which you want to export must be prepared according to Spectrum's individual needs. Please check with the respective vendor.

Having the Database Driver Installed

INFO

Make sure you have the respective database driver installed in the 'Admin' tab.

Having the Connection Configured

INFO

Make sure you have configured the respective Spectrum connection.

Exporting a Job to a Database

Exporting to Amazon Redshift - Fast Load

Exporting to Amazon Redshift - Fast Load

To export a worksheet using the Amazon Redshift (Fast Load) connector:

1. Select the "+" at the top left of the "File Browser" and select "Export Job" or right-click on the Workbook to export from and select "Add to New Export Job".
2. Select the Workbook to export from the browser.
3. Select the worksheet to export from the drop down menu.
4. Select the Amazon Redshift (Fast load) connector.

5. To export to an existing table on Amazon Redshift, select the radio button "Use existing". Choose a schema and table name from the drop-down list.

   To export to a new table on the database, select the radio button "Create new if not exist". Choose a schema from the drop-down list and enter a new table name.

   Select a record replacement policy:

   • Replace records - All records from the existing table are deleted and the new records are written to the table. No table properties are modified.

   • Append records - The records append (added in addition) to the existing table.

   • Incremental - Reduce your resources required to process data sets by computing against a smaller subset of the data.

6. Preview how the data to is being mapped to the Redshift table and select how to handle invalid data.

7. Select when to run the export job. This can be done manually by a user, run each time the Workbook in Spectrum is calculated, or at specifically scheduled times.

   INFO: Custom properties can be added in the advanced section as well as logging specifications.

8. Enter a description and click "Save".

Exporting to Google BigQuery

Exporting to Google BigQuery

A Google BigQuery connection with Spectrum must be created before exporting data.

Exporting data to Google BigQuery

To export a worksheet through a Google BigQuery connector:

1. Click the "+" button and choose "Export Job" or right-click in the "File Browser" and select "Create New" → "Export Job". The 'New Export Job' tab appears in the menu bar.
2. Click "Select" to select the required Workbook from the drop-down. *The 'Select Workbook' dialog opens.
3. Select the required Workbook from the dialog and confirm with "Select".
4. Confirm with "Next".

5. Select the required sheet from the pre-selected Workbook from the dropdown and confirm with "Next". The tab 'Connection' opens.

   INFO: Only kept sheets are available for the export. If the drop-down doesn't display the sheet, change the Workbook setting to keep that sheet.

6. Click on "Select". The 'Select Connection' dialog opens.

7. Click on the required connection and confirm with "Select".
8. Confirm with "Next". The tab 'Data Details' opens.
9. Enter the dataset name in 'Dataset'.

10. Enter the table name in 'Table'.

    INFO: A new Google BigQuery table will be created if one with the specified name does not exist.

    INFO: Allowed characters for the table name are upper or lower case characters, digits and underscores. The table name must not start with a digit.

11. Select the write preference from the drop-down and confirm with "Next". The tab 'Mapping' opens.

    INFO: 'Overwrite table' erases all existing data in a table before writing the new data. 'Append table' appends the data to the end of the table. 'Write if empty' writes the data only if the table is empty.

12. Have a look a the preview and decide which columns will be included and whether empty data is being accepted.

    INFO: All checkboxes are activated by default.

13. Decide how to handle invalid data and confirm with "Next". The 'Schedule' tab opens.

14. Select the trigger method for the export and confirm with "Next". The 'Save' tab opens.

15. If needed, add a description and activate the checkbox 'Start export immediately after save' if the export job shall start right after the save.

    INFO: This option is available when the full data for the Workbook is ready to be processed.

16. If needed, enter an email address for notifications, and confirm with "Save". The 'Save Export Job' dialog opens.

17. Click on the required folder where the export shall be saved, enter an export name job and confirm with "Save". The export job settings are saved. Configuring an export job is finished.

Exporting to Maria DB

Exporting to MariaDB

Find here all information and how-to export to MariaDB here.

Configuring Export Jobs to Maria DB

To export a worksheet through a MariaDB connector:

1. Click the "+" button and choose "Export Job" or right-click in the File Browser and select "Create New" → "Export Job". The 'New Export Job' tab appears in the menu bar.
2. Click "Select to select the Workbook from the File Browser. The dialog "Select Workbook" opens.
3. Select the Workbook and confirm with "Select". The name of the Workbook appears in the Workbook field.
4. Confirm with "Next".

5. Select a worksheet from the drop-down and confirm with "Next". A preview of the selected sheet is displayed below. The 'Connection' tab opens.

   INFO: Only kept sheets are available for export. If the drop-down is missing the sheet you want to export, modify the Workbook settings to keep the sheet.

6. Click on "Select Connection" to select the MariaDB connection. The dialog 'Select Connection' opens.

   INFO: If you haven't create the connection yet, you can do it here by clicking on "New Connection".

7. Select the relevant MariaDB connection and confirm with "Next". The selected connection is displayed under 'Connection'.

8. Confirm with "Next". The 'Data Details' tab opens.

9. Select the schema from the drop-down and decide if to use an existing table or create a new one.

   INFO: If the data is being exported to a new table, enter the new table name below.

   INFO: If the data is being exported to an existing file path, the previous data is written over by the current export.

10. Decide if the export job can overwrite the existing database table or keep the existing data and append rows to the table.

11. If needed, adjust the default values for the maximum number of concurrent database connections, maximum records per transaction, rows per batch and the configuration for case sensitivity and confirm with "Next". The 'Mapping' tab opens.

12. Have a look a the preview and decide which columns shall be included and be nullable.

    INFO: All checkboxes are activated by default.

13. Decide how to handle invalid data and confirm with "Next". The 'Schedule' tab opens.

14. Select the schedule method for when the export job shall be run and confirm with "Next". The 'Save' tab opens.

15. If needed, add a description.

    INFO: Deactivating the checkbox 'Start export immediately after save' will prevent the export starting immediately after saving.

16. If needed, add your email address in section 'Notification' to receive possible error notifications and confirm with "Save". The dialog 'Save Export Job' opens.

    INFO: Spectrum can only send email notifications, if an email server has been configured.

17. Select the place to save the export job to and name the export job, confirm with "Save". The export job is shown in the File Browser.

Exporting to Netezza

Exporting to Netezza

Find here all information and how-to export to Netezza here.

Prerequisites

INFO

Make sure you have established a connection to your Netezza instance.

Configuring Export Jobs to Netezza

To export a worksheet through a Netezza connector:

1. Click the "+" button and choose "Export Job or right-click in the File Browser and select "Create New" → "Export Job". The 'New Export Job' tab appears in the menu bar.
2. Click "Select" to select the Workbook from the File Browser. The dialog "Select Workbook" opens.
3. Select the Workbook and confirm with "Select". The name of the Workbook appears in the Workbook field.
4. Confirm with "Next".

5. Select a worksheet from the drop-down and confirm with "Next". A preview of the selected sheet is displayed below. The 'Connection' tab opens.

   INFO: Only kept sheets are available for export. If the drop-down is missing the sheet you want to export, modify the Workbook settings to keep the sheet.

6. Click on "Select Connection" to select the Netezza connection. The dialog 'Select Connection' opens.

   INFO: If you haven't create the connection yet, you can do it here by clicking on "New Connection".

7. Select the relevant Netezza connection and confirm with "Next". The selected connection is displayed under 'Connection'.

8. Confirm with "Next". The 'Data Details' tab opens.

9. Select the schema from the drop-down and decide if to use an existing table or create a new one.

   INFO: If the data is being exported to a new table, enter the new table name below.

   INFO: If the data is being exported to an existing file path, the previous data is written over by the current export.

10. Decide if the export job can overwrite the existing database table or keep the existing data and append rows to the table.

11. If needed, adjust the default values for the maximum number of concurrent database connections, maximum records per transaction, rows per batch and the configuration for case sensitivity and confirm with "Next". The 'Mapping' tab opens.

12. Have a look a the preview and decide which columns shall be included and be nullable.

    INFO: All checkboxes are activated by default.

13. Decide how to handle invalid data and confirm with "Next". The 'Schedule' tab opens.

14. Select the schedule method for when the export job shall be run and confirm with "Next". The 'Save' tab opens.

15. If needed, add a description.

    INFO: Deactivating the checkbox 'Start export immediately after save' will prevent the export starting immediately after saving.

16. If needed, add your email address in section 'Notification' to receive possible error notifications and confirm with "Save". The dialog 'Save Export Job' opens.

    INFO: Spectrum can only send email notifications, if an email server has been configured.

17. Select the place to save the export job to and name the export job, confirm with "Save". The export job is shown in the File Browser.

Export Custom Properties

INFO

Custom properties can be set especially for export jobs or during the cluster configuration.

Available custom properties for the export jobs are:

• 'export.jdbc.field.%s.varchar-length': default value = 50

  • property can be set on the cluster settings page or within an export job configuration (when set in both settings, the settings on the cluster setting page are dismissed)
  • '%' must be replaced by the field name respectively
  • property defines the length of a varchar field

• 'export.jdbc.dialect.%s.varchar-length': default value = 100000

  • property can be set on the cluster settings page or within an export job configuration (when set in both settings, the settings on the cluster setting page are dismissed)
  • '%' must be replaced by the dialect name respectively
  • property defines the varchar length within a varchar column

• 'export.jdbc.varchar-length': default value = 112

  • property can be set on the cluster settings page or within an export job configuration (when set in both settings, the settings on the cluster setting page are dismissed)
  • property defines the varchar length within jdbc export jobs