NineData DSQL

NineData DSQL supports querying data from multiple different types of data sources and aggregating to generate global query results. This article explains how to use NineData DSQL.

Background Information

With the explosive growth of enterprise data, the need for data interaction across departments, applications, and platforms has become increasingly frequent. Traditional data query methods are no longer sufficient to meet these demands. Additionally, there are differences in data formats, storage methods, and query languages between different database systems, making cross-database queries difficult. Therefore, NineData DSQL was created.

What is NineData DSQL?

NineData DSQL is a way to perform cross-database queries on multiple heterogeneous or homogeneous database systems. You can access multiple databases in a single query, retrieve useful information scattered across these databases, and aggregate this information into a single query result. This allows for easy data querying across multiple databases, multiple data sources, and even across multiple heterogeneous data sources.

NineData DSQL has the following features:

• Connect to multiple database systems: Supports connecting to multiple database systems and ensuring compatibility between these systems. These database systems can come from different vendors or platforms, and there may be differences in data formats, storage methods, query languages, etc.
• Result integration and output: Integrates query results from both homogeneous and heterogeneous database systems and standardizes the output format, making it easy for you to obtain the desired information. You don't need to know where the data is stored; you can obtain the results you need with a single query.
• 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: It is highly scalable, supporting the dynamic addition and removal of data sources. It also supports various types of data sources, including relational databases, non-relational databases, data warehouses, etc.
• Graphical interface support: Provides an intuitive graphical interface for visual querying and management, improving your work efficiency.

Query Syntax

In DSQL, whether you are performing a join query or a single-table query, you need to use a three-part syntax to specify the source data, i.e., <DBLINK name>.<database|Schema name>.<table name>.

Example 1: Cross-heterogeneous source query, querying 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: Single-table query, querying data from DBLINK1.

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

tip

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

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

Use Cases

• Enterprise data integration: Enterprises often need to integrate data from multiple departments or systems to provide a more comprehensive view. With DSQL, enterprises can access multiple databases in a single query, retrieve useful information scattered across these databases, and integrate it for easier data analysis and decision-making.
• Data mining and analysis: Data mining and analysis require a large amount of data support. With DSQL, you can retrieve the needed data from multiple databases and then conduct in-depth research and analysis using mining and analysis tools.
• Metadata management: Metadata is data about data and is crucial for data management and understanding. With DSQL, you can retrieve metadata from multiple databases to better understand the distribution, content, and relationships of the data.
• Data warehousing: Data warehousing involves storing and managing a large amount of data. With DSQL, you can query and retrieve the needed data from data warehouses and then perform further analysis and processing.
• Shopping websites: Shopping websites typically need to query across multiple databases to retrieve user shopping cart information, order information, account information, etc. With DSQL, you can access multiple databases in a single query to retrieve the required information and improve query efficiency.

Prerequisites

• You have created or joined an organization, and this organization has subscribed to either or . 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 are using are MySQL, Oracle, and OceanBase Oracle.
• You have entered the data sources you need for DSQL into the NineData console. For more information, see Creating Data Sources.

Steps

1. Log in to the NineData console.

2. Click on > in the left navigation bar.

3. In the window, you can perform cross-database queries on multiple data sources. As shown in the image below:

   

   tip

   If sensitive columns have been added to the target data source, you will not be able to view the complete contents of the sensitive columns. If you need to view them, please apply for sensitive column permissions first.

   

4. The left side of the window displays all the data sources with DSQL functionality enabled in your organization that you have permission to view. This allows you to quickly query the DBLINK nameEvery data source entered into NineData is automatically enabled with DSQL functionality and is assigned a default DBLINK name for cross-database querying. The default DBLINK naming rule is DBLINK_<data source type>_<number>. You can manually change the DBLINK name., database name, Schema name, and table name.

   

   For more instructions on using DSQL, please see DSQL Interface Instructions.

DSQL Interface Instructions

Number	Function	Description
1	DSQL Tab	Represents the currently open DSQL tabs. You can switch between multiple DSQL tabs by clicking on the tab, and you can close a DSQL tab by clicking the X on the right of the tab (the first DSQL tab cannot be closed). The right-click menu supports the following actions: : Opens a new DSQL Tab. : Closes the current tab.
2	New DSQL Tab	Opens a new DSQL tab.
3	Function Buttons	: Executes the selected DSQL query statement. : : Choose between or . (default): New result sets will overwrite the previous execution results. : Generates a new tab to display the new result set. : Change the theme of the DSQL Tab, with options for and . : Adjust the font size of SQL statements in the DSQL Tab by dragging the slider. Default values are as follows: Windows: 16 MacOS: 13 : More Options : Adjust the structure of the selected SQL statement for improved readability. : Save all SQL statements in the current DSQL Tab to the SQL list for future use. : View saved SQL statements. You can insert the target SQL into the current DSQL Tab . Additionally, you can or the target SQL. : View the execution history of SQL for the target data source. You can filter the execution history for a specific time period by date or search the execution history by keywords (SQL statement, database name, error message). : displays the DSQL Tab in full screen (browser size). After going full screen, you can click the at the top right to exit full screen and return to the original DSQL Tab size.
4	DBLink Search|Filter|Favorite	To quickly locate the target DBLink (data source) through the following methods: Search: Enter the DBLink name or data source name in the search box to find the DBLink. Filter: Filter the DBLink by data source type and the environment to which the data source belongs. Favorite: Quickly switch between the and tabs to find the favorite DBLink.
5	DBLink List	Display all data sources (DBLinks) that you have permission to view in the current organization. Place the cursor over the target DBLink, click on the icon on the right to bookmark the DBLink. Right-click on the DBLink name to perform data source-level operations: : Display detailed information about the current data source. : Change the DBLink name of the current data source. Right-click on the database name to perform database-level operations: : Display detailed information about the current database. : Automatically generate SQL scripts to create the current database. Right-click on the table name to perform table-level operations: : Query the first 100 records of the current table. : Display detailed information about the current table. : Automatically generate SQL scripts to create the current table. Right-click on the view name to perform view-level operations: : Query the first 100 records of the current view. : Automatically generate SQL scripts to create the current view. Right-click on any object to perform global operations: : Create a new DSQL window. : Refresh the DBLink list.
6	DSQL Editor	Supports the following features: Execute cross-database queries: Supports single statements or batch execution. Interrupt execution: You can click to interrupt the execution of SQL during cross-database query execution. Syntax highlighting: Different syntax elements are displayed in different colors for improved code readability. SQL auto-suggestions: Suggestions are automatically displayed while typing based on the semantics of your input. Right-click menu: : Executes the SQL statement at the cursor position. : Adjusts the structure of the selected SQL statement for improved readability. Common operations: Including , , , and . : Selects the SQL statement at the cursor position.
7	Execution Information, Result Set	Display the execution information and result set of DSQL. Execution information: Includes execution time, executed SQL statement, execution status, execution duration, and other information. Result set: Displays the returned results of DSQL. You can perform the following operations: Click : Supports exporting the data table in various formats. You can export the current page or all data (up to 10,000 entries). Click : Scales the result set to the bottom of the page. Click to restore. Click : Supports locking, hiding, showing, and other operations on columns in the result set. Result set search: Supports and . Click the small arrow next to the column name to sort the result set. : Right-click on the target cell, select , to copy the entire row of data where the cell is located. : Right-click on the target cell, select , to copy the entire column of data where the cell is located. : Right-click on the target cell, select , to copy the current cell. : Right-click on the target cell, select , to copy the data of the row where the cell is located in multiple formats. : Right-click on any cell, select , to export the current result set in multiple formats. You can export the current page or all data (up to 10,000 entries). View SQL Corresponding to the Result Set: Hover over the tab of the target result set to display the SQL statement corresponding to that result set.

Appendix: Closing DSQL

The data sources you enter in the NineData console have DSQL functionality enabled by default. If you no longer need this functionality, you can manually disable it.

1. Log in to the NineData console.
2. Click on >.
3. Find the data source in the data source list for which you want to disable DSQL functionality and click on the data source's ID.
4. On the page, click on to expand more information.
5. Find the option and click the slider to the right to turn it off.

Once turned off, the data source will no longer appear in the list on the left side of the window.