Data Export

NineData supports data export function, which can export data from your data source in various formats to your local computer for convenient data analysis and processing.

Feature Introduction

In many cases, businesses need to extract data from databases for analysis to make decisions based on the data. Therefore, the exported data must be complete and accurate. Here are the main features of NineData's data export function:

• Support for multiple types of data sources: Currently, supported database types include MySQL, PostgreSQL, Oracle, OceanBase Oracle, OceanBase MyQL, Db2, DaMeng, ClickHouse, TiDB, Redis, MongoDB, GreatSQL, GaiaDB, GaiaDB-X, TDSQL MySQL, AnalyticDB PostgreSQL, Greenplum, Doris, lindorm.
• Flexible export methods: support exporting via SQL statements or exporting the entire database or entire tables.
• Support for exporting multiple file formats: support exporting data in SQL, CSV, EXCEL, and other formats to the local computer. You can choose the appropriate file format based on your needs.
• Support for exporting large fields: support exporting TEXT, BINARY, and BLOB fields.
• Support for exporting large amounts of data: there is no limit on data volume and supports fast export of all data.
• Data security guarantee: under the organizational modelThe organizational model supports assigning different roles and resource management permissions to each user under the organization, which is suitable for multi-user collaborative development under the same organization. It ensures data security while improving overall production efficiency., strict access control is supported for data, and data export requires an approval process to ensure data security.

Use Cases

• Data backup and migration: export data to the local computer for backup or to other platforms for migration.
• Data analysis and reporting: export data in multiple formats for data analysis and report generation to better understand the data.
• Data processing: export data to the local computer for processing to meet business requirements.

Precautions

In the commercial versions (DevOps Pro, DevOps Enterprise), please ensure that your monthly/yearly subscription is not expired, as it may result in the inability to use the Database DevOps service. You can quickly check the remaining quota and expiration date at the top right corner of the NineData console page.

Usage Restrictions

• After the status of data export task is changed to Approved, please execute the task within 24 hours, otherwise the task will be invalid. After it becomes invalid, you need to submit the application again.
• After the status of data export task is changed to Success, please download the data file within 24 hours, otherwise the download link will be invalid. Additionally, after downloading more than 10 times, the download link will also be invalid. After it becomes invalid, you need to submit the application again.

Submitting a Data Export Task

Prerequisites

You have permission to access the target data source. If you do not have permission, you can apply for it. For more information, see Permission Application and Approval.

Steps

1. Log in to the NineData Console.

2. Click DevOps > Data Export on the left navigation bar.

3. On the Data Export page, click Create Export in the upper right corner of the page.

4. On the Create Export page, configure the settings according to the table below:

   Parameter	Description
   Name	Enter the name of the data export task. For easy searching and management later, try to use a meaningful name. Supports up to 64 characters.
   Datasource/Database	Select the data source and database where the table to be exported is located. Note: If you do not have permission for the data source or database, a prompt will appear on the page. You can click Apply Permission in the prompt box to apply for permission. For more information, see Permission Application and Approval.
   Export Method	Select the export method for the data. Two methods are supported: SQL Statement (ClickHouse, MongoDB, Redis only support this item): Exports data based on the SQL statement you provide. For example: SELECT * FROM employees, exports all data in the employees table. DB/Table: Supports selecting all tables in the current database, or selecting specific tables for export.
   Export Objects	Select when Export Method is DB/Table: All Tables: Exports all tables in the target database. Customize: Manually select the tables to export. If you select this option, you also need to click Add to manually select the tables to export. You can select specific fields in the table, or enter filtering conditions to filter the data.
   Export Content	Supports three types of content: Data, Schema, and Schema + Data. Data: Exports only the data content in the table, including field names but not field attributes. Schema: Exports only the target table structure, not the data. Schema + Data: Exports both the table structure and data.
   Export Type	Select the format for exporting data, which supports the following formats: SQL: Contains statements used to create and insert database objects, making it easy to migrate database structure and data to other data sources. CSV: A common text file format in which data fields are separated by commas. Exporting data as a CSV file makes it easy to use in other applications. Only available when Export Content is set to Data. EXCEL (Redis fixed to this item): Presented in tabular form and providing various chart and calculation functions. Exporting data as an Excel file is suitable for reporting, statistics, and analysis of data. Only available when Export Content is set to Data. JSON: Only applicable to MongoDB export.
   File Encoding	Choose the encoding used when exporting data. Supports UTF-8 and GBK.
   Export Reason	Enter the reason for export, only required in organization mode for approval processes.
   Executor	The task executor for the task approved by the current export task approval process needs to be selected only under the organizational mode. Note: The options in the Executor list depend on two situations: If the current data source has configured development specifications, the options in the list are based on the Export Executor Config rule configured in the current specification. This rule is located on the SQL Task & Console tab. For information on how to configure development specifications, see Editing Specifications. If the current data source has not configured development specifications, the options in the list are users who have the Data Export - Exec permission for the current data source. For more information on permissions, see Configuring User Permissions.
   Advanced	Export Big Key (optional only when Export Content contains Data): Choose whether to export large fields. The type of large fields varies depending on the data source type. SQL Script Extensions: Generate DROP statement before CREATE (optional only when Export Content is set to Schema + Data): Automatically generates the corresponding DROP statement for each created table before the SQL statement to ensure that if a table with the same name already exists, it will be deleted and recreated to avoid errors caused by the table already existing during data import. Generate TRUNCATE statement before INSERT (optional only when Export Content contains Data): Automatically generates the corresponding TRUNCATE statement for each table before the SQL statement to clear all data in the table before each data import, avoiding problems with duplicate data insertion. Compress Insert Statement (optional only when Export Content contains Data): When generating the SQL script, compress multiple INSERT statements into a larger INSERT statement to significantly improve the efficiency of inserting data. Other Objects (optional only when Export Content contains Schema): Choose whether to export other non-table objects, which are not exported by default.

5. Click on Create Export, and the page will automatically redirect to the Details page.

   • If you are in personal modeBy default, after registering for a NineData account, you are in personal mode, which allows you to use the four modules provided by NineData without any barriers. Personal mode is suitable for individual developers, such as backing up, restoring, synchronizing, and comparing their own databases, as well as daily development work with SQL Console., the export task will start automatically. After the task is completed, click on Download in the upper right corner of the page to download the data file to your local device and complete the steps.
   • If you are in organization modeOrganization mode supports assigning different roles and resource management permissions to each user under the organization, which is suitable for collaborative development by multiple users in the same organization, while ensuring data security and improving overall productivity., please continue with the following steps.

6. In the Pre-check node, wait for the task status to change to Pending, and then click on Submit Approval in the upper right corner of the page to enter the Submit Approval node. In the Pre-check node, you can also perform the following operations:

   • Click on the icon in the upper right corner of the page to refresh the task status.
   • Click on Check Again in the upper right corner of the page to perform the check again.
   • Click on Withdraw in the upper right corner of the page to directly withdraw the task or edit the task and submit it again.
   • Click on Duplicate in the upper right corner of the page to copy a new task with the same configuration.
   tip

   • The Pre-check will automatically check the export task, including syntax error check (SQL Statement based export scenario), estimated row check, and database permission check. If any issues are found during the process, specific information will be prompted to facilitate your location and modification.
   • If the approval process is not configured for the data source, the task will automatically skip the Pre-check and Submit Approval nodes and come to the Execute Task node. The task status will be changed to Approved, and you can directly perform Step 9.

7. Click on Submit Approval in the upper right corner of the page, select the process approver(s) in the pop-up window, and click on OK.

   tip

   • Depending on the configuration of the approval process, the number of approvers to be selected here may be different. Please choose according to the actual situation.

   • If the Not Specifying Approvers feature is enabled in the current approval process, this section will display After submission, all approvers can review. Please check the results in the approval process.. You do not need to manually specify an approver; all personnel authorized to approve the current work order will receive approval notifications and be able to approve it.

8. The task status has been changed to Pending Approval. Before approval, you can perform the following operations:

   • Click the icon in the upper right corner of the page to refresh the task status.
   • Click Withdraw in the upper right corner of the page to directly withdraw the task or edit and submit it again.
   • Click Transfer in the upper right corner of the page to change the approver of the task.
   • Click Duplicate in the upper right corner of the page to copy a new task with the same configuration.

9. The task status has been changed to Approved. Click Execute in the upper right corner of the page, select Execution Method, and click OK.

   • Execute Now: Execute the data export task immediately.

   • Scheduled Execution: Schedule the data export task for a specific time.

     caution

     The selected execution time must be within 24 hours. If it exceeds 24 hours, the task will be unable to execute, and you will need to resubmit the request.

10. The task status has been changed to Success. Click Download in the upper right corner of the page to download the data file to your local machine.

    caution

    Please make sure to download the file within 24 hours, otherwise the download link will be invalid. Additionally, after downloading the file 10 times, the download link will also be invalid. After it becomes invalid, you need to submit the application again.

Approve a Data Export Task

Prerequisites

• An organization has been created or joined. For more information, see Managing Organizations.
• The NineData console is in organization mode. How to switch from personal mode to organization mode, see Switching to Organization.
• The status of the data export task is Pending Approval.
• You are the Administrator or the Approver of the target data export task.

Procedure

1. Log in to the NineData Console.

2. Click DevOps > Data Export in the left navigation pane.

3. On the Data Export page, find the target task, and click the task ID or Details in the Actions column on the right side of the task.

   tip

   You can also hover your mouse over the icon at the top of the page and click on the target task in the Todo tab.

4. On the Details page, review the work order content and select Transfer, Approved, or Rejected according to the actual situation.

   Operation	Description
   Transfer	Transfer the work order to other users for approval.
   Approved	Approve the application. After clicking Approved, you can enter Reason as needed and click OK.
   Rejected	Reject the application. After clicking Rejected, you also need to enter Reason for rejection and click OK

Adding a Digital Watermark to Exported Excel Files

NineData supports embedding watermarks in exported Excel files, which include the user’s name, helping to trace the party responsible for leaked data, thereby making accountability easier. Additionally, data watermarks can serve as a reminder mechanism, embedding the user’s identity information into the page, making data users more cautious and reducing improper conduct.

Prerequisites

• You have created or joined an organization. For more information, see Managing Organizations.
• NineData Console is in organization mode. To switch from personal mode to organization mode, see Switch to Organization Mode.
• You are an Administrator.

Steps

1. Log in to the NineData Console.

2. In the left navigation bar, click DevOps > Policy & Process.
3. Click on the SQL Dev Policy tab, then click on the associated Name for the target data source.
4. The console will redirect to the Details page, click on the Data Export tab, find the Adding digital watermarks to Excel files rule, and turn on the switch under the Status column to enable it.

Encrypting Exported Files

NineData supports encrypting the compressed package of exported data files and provides a key viewing option on the task page. Only authorized users can obtain the key to decrypt the exported data files.

Prerequisites

• You have created or joined an organization. For more information, see Managing Organizations.
• NineData Console is in organization mode. To switch from personal mode to organization mode, see Switch to Organization Mode.
• You are an Administrator.

Steps

1. Log in to the NineData Console.

2. In the left navigation bar, click DevOps > Policy & Process.
3. Click the SQL Dev Policy tab, then click the Name associated with the target data source.
4. The console will navigate to the Details page. Click the Data Export tab, find the Data export file encryption rule, and toggle the switch under the Status column to enable it.