Managing Doris Data Source through NineData SQL Console

This article explains how to use the SQL Console in NineData to manage Doris data sources.

Background Information

NineData is a cloud service that integrates Database DevOps, data replication, data backup, and data comparison modules. It supports enterprise data management in hybrid cloud (self-built database + cloud database) and multi-cloud (business architecture composed of multiple different cloud vendor databases) architectures, significantly reducing the difficulty and cost of data operation and maintenance for enterprises.

NineData's SQL Console allows you to manage Doris databases more easily. With NineData, you can easily view, edit, and manage Doris data. You can also create, delete, and modify Doris databases.

Prerequisites

• You have registered a NineData platform account. For more information, please refer to Registering a NineData Account.

• Under (organization mode), you must have read-only, DML, or DDL permissions for the target data source.

  tip

  Read-only permissions only support viewing databases and querying data operations.

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

  

Features

NineData's SQL Console provides several convenient features to help you manage your data sources.

Feature	Description
AI Intelligence	NineData provides AI services that support the following functions: SQL statement generation: Quickly convert short natural language expressions into SQL query statements. SQL query optimization: Help optimize SQL statements by providing the original SQL statement and telling the AI what needs to be optimized. Problem solving: Answer various questions you have.
AI Functions	: AI analyzes the SQL statement at the cursor or the selected SQL statement and provides optimization suggestions. : AI analyzes the structure of the selected SQL statement and performs formatting to enhance readability.
Execute SQL Statements	Access data in the data source. Perform insert, delete, and update operations on databases, tables, and data (records) in the data source. Create views in the data source. Grant or revoke user permissions. Note: In (organization modeOrganization mode supports assigning different roles and resource management permissions to each user in the organization, suitable for collaborative development by multiple users within the same organization. It improves overall production efficiency while ensuring data security.), you need the appropriate permissions to perform the above operations on the data source.
Generate Creation Scripts	Generate creation scripts for existing objects such as databases and tables in the data source, which can be used to replicate the same structure in other data sources.
SQL Intelligent Suggestions	While entering SQL statements in the SQL Console, the system automatically provides suggestions for keywords, functions, table and column names, and other common objects based on the semantics you enter. It also provides convenient features such as SQL templates (semantic structures) and syntax help.
Save SQL | View SQL	For frequently used SQL statements, you can save them in NineData. The saved SQL statements can also be viewed. Since the SQL statements are saved in the cloud, they are not limited by the browser or host, allowing you to view and use the saved SQL statements anytime, anywhere.
Workspace Recovery	In scenarios such as unexpected browser exits or sudden crashes, you only need to log in to the NineData console and open the SQL Console again. The previously opened data source and executed SQL commands will be automatically loaded. Since the page state is saved in the cloud, it is not limited by the browser or host, allowing you to restore your workspace anytime, anywhere.
Execution History	records all the SQL statements executed by the current account in the NineData console. It also supports quick retrieval based on data sources, execution time, SQL keywords, etc. You can also quickly copy and execute SQL statements from the execution history in the SQL Console.
SQL Standard Check (Enterprise Edition)	In (organization modeOrganization mode supports assigning different roles and resource management permissions to each user in the organization, suitable for collaborative development by multiple users within the same organization. It improves overall production efficiency while ensuring data security.), the system checks every SQL statement executed in the SQL Console based on the SQL development standards configured by . If a SQL statement does not comply with the standards, it will be intercepted by the system to ensure database security. After interception, according to the approval process configured by , users can modify the SQL statement according to the standards and resubmit it, or directly submit a SQL task.

Step 1: Adding a Doris Data Source

1. Log in to the NineData Console.

2. On the left navigation pane, click > .

3. Click  tab, and click  on the page. In the popup window for selecting the data source type, choose > **Doris**, and configure the parameters based on the table below on the page.
   tip

   If you make a mistake during the operation, you can click the icon at the top of the page to make a new selection.

   Click to expand table details

   Parameter	Description
   	Enter the name of the input data source. Please use a meaningful name for easy future lookup and management.
   	Choose the access method for the data source. Supports three methods: , , and SSH Tunnel. : Access the data source via public IP address. : Secure and efficient intranet access method provided by NineData. The host of the data source needs to be connected. Refer to add gateway for the connection method. SSH Tunnel : Access the data source via SSH tunnel.
   Configuration options for	: Enter the public connection address and port number of the data source.
   Configuration options for	: Choose the NineData gateway installed on the host where the data source resides. : Can be set as localhost (data source is on the local machine) or the intranet IP of the data source's host.
   Configuration options for SSH Tunnel	: Enter the public IP or domain name of the target data source's server, along with the corresponding port number (default port for SSH service is 22). : Choose the SSH authentication method. : Connect using (server login name) and (server login password). (recommended): Connect using and . : Enter the login username of the target data source's server. : Upload the private key file by clicking . This is the key file without an extension. If you haven't created it yet, refer to generate SSH Tunnel key file. : Enter the passphrase set during key file generation. Leave it blank if no passphrase was set during key generation. Note: After configuring SSH, you need to click the on the right. Two outcomes are possible: prompt: Indicates the successful establishment of the SSH Tunnel. Error message prompt: Indicates connection failure. Troubleshoot the issue based on the error message and retry.
   	Data source username.
   	Data source password.
   	Choose the region closest to the location of your data source to effectively reduce network latency.
   	Select the actual business purpose of this data source as the environmental identifier. Default options include and , and you can also create custom environments. Note: In organization mode, database environments can also be applied to permission policy management. For example, the default role only supports accessing data sources in the environment and cannot access data sources in other environments. For more information, see manage roles.
   	Choose whether to use SSL encryption for accessing the data source (default: on). If the data source requires SSL encryption, you must enable this option, otherwise the connection will fail. Click the switch on the right to toggle encryption on or off. Click on the left of to expand detailed configuration. : Choose from two options: : Automatically detect the SSL status of the server. If the server has SSL enabled, connect using SSL; otherwise, use a non-SSL connection. : Always use SSL to connect to the data source. If the server does not support this method or cannot establish an SSL connection for other reasons, the connection will fail. : Specify the SSL-Cipher encryption algorithm. : If the Doris server uses a self-signed CA certificate, upload the root certificate of that CA here. : Check this to verify the name and IP address of the server to ensure that the connection is made to the genuine Doris server and avoid Man-in-the-Middle attacks. : If the Doris server requires the client to provide a certificate, upload the client's certificate and key here. The Doris server will verify the uploaded information to ensure the security of the connection. Note: In most cases, if the Doris server supports SSL encrypted connections, you only need to select without configuring other options. The system will automatically generate keys for the connection. For more SSL configuration methods, refer to the official documentation: Configure Doris encrypted connections.

4. After completing all the configurations, click the button, and the data source will be added once the connection test succeeds. Otherwise, please recheck the connection settings until the connection test succeeds.

Step 2: Using the SQL Console

1. Log in to the NineData console.

2. In the left navigation pane, click > .

   tip

   If you have previously logged in to a data source and did not close it, you will be automatically redirected to the data source page.

3. Click on the text box below , and a list of available data sources will pop up. Click on the desired data source and click to enter the SQL Console.

   tip

   If you have multiple data sources, you can enter keywords (partial or complete) in the box to perform an exact or fuzzy search. The searchable fields include:

   • Data source name
   • IP address

4. Once the SQL Console is open, you can perform data management operations on the data source. The following image shows an example:

   

5. For detailed instructions on how to use the SQL Console, please refer to the SQL Console Interface Introduction section.

   tip

   In (Organization Mode), if sensitive columns are configured in the target data source, you will not be able to view the complete content of the sensitive columns. To view them, please apply for sensitive column access permission first.

Appendix: Introduction to the SQL Console Interface

Number	Feature	Description
1	SQL Tab	Represents the currently open SQL Console for a data source. Multiple SQL Consoles can be switched by clicking on the tabs, and clicking the X on the right of the tab will close the SQL Console. Right-clicking on the tab supports the following actions: : Open the details window for the current data source. : Open a new SQL Console for a data source. : Close the current tab.
2	Open Data Source	Select and open a new SQL Console for a data source.
3	Data Source Information	Displays the data source information for the current SQL Console, including environment, IP address, and port number. Right-click menu: : Show the details of the current data source, including username, connection address, runtime, and connection count. : Refresh the data source information.
4	Refresh	Refresh the data in the current data source.
5	Database Name	Select the target database to perform SQL operations.
6	Function Buttons	: : Execute the selected SQL statement. : : AI analyzes the SQL statement on the current cursor position and provides optimization suggestions. : : Can be or . (default): The new result set will replace the previous execution result. : Generate a new tab to display the new result set. : Change the theme of the SQL console, with options of and . : Adjust the font size of the SQL statement in the SQL console by dragging the slider. Default values are as follows: Windows: 16 MacOS: 13 : More options : Adjust the structure of the selected SQL statement for enhanced readability. : AI analyzes and adjusts the structure of the selected SQL statement for better formatting effects. : View the execution plan of the selected SQL statement. : Save all SQL statements in the current SQL console to the SQL list for future use. : View saved SQL statements. You can insert the desired SQL into the current SQL console using . You can also edit () or delete () the target SQL. : View the execution history of SQL for the target data source. You can filter out the execution history for a specific time period by selecting a date or search the execution history using keywords (SQL statement, database name, error message). : : Switch to editor mode. : : Switch to Intelligent SQL mode. For more information, see Using AI to Manage Data Sources. : | Only accessible by . When this mode is enabled, all SQL executed in the SQL console is unrestricted by conventions or approval processes and can be executed directly. : : Display the SQL console in full-screen mode (based on browser size). After entering full-screen mode, click the in the top right corner to restore the original size of the SQL console.
7	Search Table Name	Quickly search for a target data table by entering the table name.
8	Database List	Displays all databases and data tables in the current data source. : Right-click any database, click , enter , , and the corresponding in the pop-up dialog box, and click to create the database visually. : Right-click the target database, click to open the page. You can view the details of the target database here. : Right-click the target table, click to automatically query the data in the table, returning up to 100 rows by default. : Right-click the target table, click to open the page. You can view all and of the target table here. : Right-click the target view, click to automatically query the data in the view, returning up to 100 rows by default. : Right-click the target object, click , to generate the SQL statement for creating the object. : Right-click any object, click , to open a new SQL Console tab. : Right-click any object, click , to refresh the database list.
9	SQL Editor	Supports the following features: SQL Execution: Supports executing a single statement or batch execution. Interrupt Execution: Click to interrupt the execution of SQL statements. Syntax Highlighting: Displays different syntax elements in different colors to improve code readability. SQL Auto-Completion: Automatically provides suggestions based on the syntax as you type. View SQL Syntax Help: After entering an SQL keyword, select the keyword and right-click > to view the corresponding help information. SQL Templates: Provides common templates for CREATE, SELECT, INSERT, UPDATE, DELETE statements, where you only need to enter the prefix command and select the corresponding template. For example, typing "CREATE" will allow you to choose between "CREATE TABLE" and "CREATE VIEW" templates. Right-click menu: : Execute the SQL statement at the cursor position. : Adjust the structure of the selected SQL statement for better readability. : AI intelligently adjusts the structure of the selected SQL statement for better readability, providing better results compared to machine formatting. : AI intelligently analyzes the SQL statement at the cursor position and provides optimization suggestions. General Operations: Include , , , and . : Select the SQL statement at the cursor position. : AI analyzes the currently selected content and provides information such as the semantics of the target SQL syntax, the table structure definition of the target table, and the overall explanation of the SQL statement logic.
10	Execution Information, Result Set	Displays the execution information and result set of the SQL. Execution Information: Includes execution time, executed SQL statement, execution status, and execution duration. Result Set: Displays the result of the SQL. Depending on the data source type, you can perform the following actions: Click : Supports exporting data tables in multiple formats. You can export the current page or all data (up to 10,000 rows). Click : Scroll the result set to the bottom of the page. Click to restore. Click : Allows you to lock, hide, or show 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 the target cell, select to copy the entire row of data where the cell is located. : Right-click the target cell, select to copy the current cell. : Right-click the target cell, select to choose from multiple formats to copy the data in the row where the cell is located. : Right-click 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 rows).

Related Links

• Accessing Doris Data Sources through the Gateway
• Ninedata Documentation