NineData DSQL

NineData DSQL lets you query multiple data sources through one SQL interface and aggregate results into a unified result set. Use it when data is distributed across different databases, schemas, or database engines.

Background

Enterprise data is often split across departments, applications, and platforms. Querying that data is difficult when each system uses different storage models, schemas, or query languages. NineData DSQL provides a unified SQL workspace for cross-database queries across supported data sources.

What is NineData DSQL?

NineData DSQL is a cross-database query capability for homogeneous and heterogeneous database systems. Access multiple databases in a single query, retrieve distributed data, and aggregate the output into one result set.

NineData DSQL provides these capabilities:

• Connect to multiple database systems: Query data across supported database systems from different vendors or platforms.
• Result integration and output: Integrate query results from homogeneous and heterogeneous database systems and return them in a standard output format.
• Data privacy and security protection: Supports anonymization of sensitive data and control of access permissions to protect data privacy and security, preventing data leaks and unauthorized access.
• Scalability and flexibility: Add or remove data sources as query requirements change.
• Graphical interface support: Use a visual interface to browse DBLinks, write SQL, run queries, and inspect results.

Query syntax

In DSQL, use this three-part syntax to specify source data for both join queries and single-table queries: <DBLINK name>.<database|Schema name>.<table name>.

Example 1: Query data from DBLINK1 (MySQL) and DBLINK2 (Oracle).

SELECT *
  FROM DBLINK1.database_name.table_name a,
       DBLINK2.schema_name.table_name b
 WHERE a.id=b.id;

Example 2: Query data from one table in DBLINK1.

SELECT *
  FROM DBLINK1.database.table_name
 WHERE id=1;

tip

If a database or table name starts with a number or uses a reserved keyword, enclose the name in double quotes (") to avoid query failure.

For example: SELECT * FROM dblink_mysql_3451."9zdbtest3".sbtest1;

Use cases

• Enterprise data integration: Query data from multiple departments or systems and combine the results for analysis and reporting.
• Data analysis: Retrieve data from multiple databases before using downstream analysis or BI tools.
• Metadata management: Query metadata from multiple databases to understand data distribution, content, and relationships.
• Data warehousing: Query data warehouses and operational databases from one interface before further analysis or processing.
• E-commerce operations: Retrieve shopping cart, order, account, or inventory data that is distributed across multiple databases.

Before you begin

• You have created or joined an organization, and this organization has subscribed to either DevOps Pro or DevOps Enterprise. Please ensure that your annual or monthly subscription is still active. For more information, please refer to Manage Organizations.
• Your current account has switched to the target organization. For more information, please refer to Switching to an Organization.

• The data sources you use for DSQL are MySQL, Oracle, or OceanBase Oracle.
• Add the required data sources to the NineData console. See Creating Data Sources.

Procedure

1. Sign in to the NineData Console.

2. Go to DevOps > DSQL.

3. In the DSQL window, run cross-database queries on multiple data sources.

   

   tip

   If sensitive columns are configured for the target data source, masked values are displayed by default. To view complete values, apply for sensitive column permissions first.

   

4. The left side of the window displays all DSQL-enabled data sources in your organization that you have permission to view. Use this list to find the DBLINK nameEvery data source entered into NineData has DSQL enabled by default and is assigned a default DBLINK name for cross-database querying. The default DBLINK naming rule is DBLINK_<data source type>_<number>. The DBLINK name can be changed manually., database name, schema name, and table name.

   

   For details about the DSQL window, see DSQL interface reference.

Result

DSQL executes the cross-database query and displays the execution information and result set in the DSQL window. Review the selected DBLink, database or schema name, table name, and returned result set before exporting or reusing the output.

DSQL interface reference

Number	Feature	Description
1	DSQL Tab	Represents the currently open DSQL tabs. Click a tab to switch between DSQL tabs. Click X on a tab to close it. The first DSQL tab cannot be closed. The right-click menu supports these actions: New Query: Open a new DSQL tab. Close Tab: Close the current tab.
2	New DSQL Tab	Opens a new DSQL tab.
3	Function Buttons	: Execute Execute the selected DSQL query statement. : Settings Results: Choose Overwrite or Append. Overwrite (default): Replace the previous execution results with the new result set. Append: Create a new tab for the new result set. Theme: Change the DSQL tab theme to Light or Dark. Font Size: Drag the slider to adjust the SQL statement font size. Default values: Windows: 16 MacOS: 13 : More Options Format: Format the selected SQL statement for readability. Save SQL: Save all SQL statements in the current DSQL tab to the SQL list for later use. View SQL: View saved SQL statements. Insert the target SQL with Insert into the current DSQL tab. Edit or delete the target SQL with Edit or Delete. History: View SQL execution history for the target data source. Filter execution history by date or search by keyword, such as SQL statement, database name, or error message. : Full Screen Open the DSQL tab in full-screen mode. After you enter full-screen mode, click in the toolbar to restore the original size.
4	DBLink Search|Filter|Favorite	Locate the target DBLink (data source): Search: Enter the DBLink name or data source name in the search box. Filter: Filter DBLinks by data source type or environment. Favorite: Switch between All Datasource and My Favorites to find favorite DBLinks.
5	DBLink List	Displays all data sources (DBLinks) that you have permission to view in the current organization. Hover over the target DBLink and click the icon to bookmark it. Right-click the DBLink name to perform data source-level operations: Instance Details: View detailed information about the current data source. Rename DBLink: Change the DBLink name of the current data source. Right-click the database name to perform database-level operations: Database Details: View detailed information about the current database. Generate DDL: Generate SQL scripts that create the current database. Right-click the table name to perform table-level operations: Select Rows: Query the first 100 records of the current table. Table Details: View detailed information about the current table. Generate DDL: Generate SQL scripts that create the current table. Right-click the view name to perform view-level operations: Select Rows: Query the first 100 records of the current view. Generate DDL: Generate SQL scripts that create the current view. Right-click any object to perform global operations: New Query: Create a new DSQL window. Refresh: Refresh the DBLink list.
6	DSQL Editor	Supports these features: Execute cross-database queries: Run a single SQL statement or multiple statements. Interrupt execution: Click Stop to interrupt a running cross-database query. Syntax highlighting: Display different syntax elements in different colors for readability. SQL auto-suggestions: Show suggestions based on SQL context as you type. Right-click menu: Execute: Execute the SQL statement at the cursor position. Format: Format the selected SQL statement for readability. Common operations: Cut, Copy, Paste, and Select All. Select Current SQL: Select the SQL statement at the cursor position.
7	Execution Information, Result Set	Displays DSQL execution information and results. Execution information: Includes execution time, executed SQL statement, execution status, execution duration, and other information. Result set: Displays returned DSQL results. These actions are available: Click : Export the data table in multiple formats. Export the current page or all data, up to 10,000 entries. Click : Move the result set to the bottom of the page. Click to restore it. Click : Lock, hide, show, or otherwise manage columns in the result set. Result set search: Supports Fuzzy and Exact. Click the arrow next to the column name to sort the result set. Copy Row: Right-click the target cell and select Copy Row to copy the entire row that contains the cell. Copy Column: Right-click the target cell and select Copy Column to copy the entire column that contains the cell. Copy Field: Right-click the target cell and select Copy Field to copy the current cell. Copy as: Right-click the target cell and select Copy as to copy the row that contains the cell in multiple formats. Export: Right-click any cell and select Export to export the current result set in multiple formats. Export the current page or all data, up to 10,000 entries. View SQL for the result set: Hover over the target result set tab to view the corresponding SQL statement.

Appendix: Disable DSQL

Data sources added to the NineData console have DSQL enabled by default. If you no longer need DSQL for a data source, disable it manually.

1. Sign in to the NineData Console.

2. Click Datasource > Datasource.
3. Find the data source for which you want to disable DSQL, then click the data source's ID.
4. On the Details page, click More to expand more information.
5. Find the DSQL option and click the slider to the right to turn it off.

After DSQL is disabled, the data source does not appear in the list on the left side of the DSQL window.