Manage Doris with NineData SQL Console

NineData SQL Console lets you connect to Doris, browse databases, execute SQL, inspect objects, and manage common data operations from a browser workspace.

Use this guide to add a Doris data source and open it in SQL Console. The page also summarizes the SQL Console controls that are useful when working with Doris.

Overview

SQL Console is part of NineData Database DevOps. For Doris, it provides a governed workspace for SQL execution, object browsing, execution history, saved SQL, AI-assisted SQL operations, and enterprise controls such as SQL standards and approval workflows.

Before you begin

• Register a NineData platform account. See Registering a NineData Account.

• Under DevOps Enterprise (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 (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.

  

Key capabilities

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

Feature	Description
AI Intelligence	NineData provides AI services for SQL statement generation, SQL query optimization, and troubleshooting. SQL statement generation: Convert short natural language prompts into SQL query statements. SQL query optimization: Provide the original SQL statement and optimization goal so AI can suggest improvements. Problem solving: Ask questions about SQL, objects, or database operations.
AI Functions	SQL Optimize: AI analyzes the SQL statement at the cursor or the selected SQL statement and provides optimization suggestions. AI Format: 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 DevOps Enterprise (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. You can use the scripts to recreate 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	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 DevOps Enterprise (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 Administrator. If a SQL statement does not comply with the standards, the system blocks the execution to protect database security. After interception, users can revise the SQL and resubmit it, or submit a SQL task through the approval process configured by Administrator.

Procedure

Add a Doris data source

1. Log in to the NineData Console.

2. On the left navigation pane, click Datasource > Datasource.

3. Click the Datasource tab, then click Create Datasource. In the data-source type dialog, choose Database > Doris. Configure the fields on the Create Datasource page.

   tip

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

   Click to expand table details

   Parameter	Description
   Name	Enter a meaningful data source name so you can find and manage it later.
   Connection	Choose the access method for the data source. Supports three methods: IP Address, Gateway, and SSH Tunnel. IP Address: Access the data source via public IP address. Gateway: Secure and efficient intranet access method provided by NineData. Connect the host where the data source resides first. See add gateway. SSH Tunnel: Access the data source via SSH tunnel.
   Configuration options for IP Address	Host: Enter the public connection address and port number of the data source.
   Configuration options for Gateway	Gateway: Choose the NineData gateway installed on the host where the data source resides. Host: 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	SSH Host: 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). SSH Authentication Method: Choose the SSH authentication method. Password: Connect using SSH Username (server login name) and Password (server login password). Key (recommended): Connect using SSH Username and Key File. SSH Username: Enter the login username of the target data source's server. Key File: Upload the private key file by clicking Upload. This is the key file without an extension. If you haven't created it yet, see generate SSH Tunnel key file. Password: Enter the passphrase set during key file generation. Leave it blank if no passphrase was set during key generation. Note: After configuring SSH, click Connection Test on the right. Two outcomes are possible: Connection Successfully: The SSH Tunnel is available. Error message: The connection failed. Use the error message to troubleshoot and retry.
   DB Account	Data source username.
   DB Password	Data source password.
   Access Region	Choose the region closest to the location of your data source to effectively reduce network latency.
   Environment	Select the environment that matches the business purpose of this data source. Default options include PROD and DEV. You can also create custom environments. Note: In organization mode, database environments can also be used in permission policy management. For example, the default Prod Admin role can access only data sources in the PROD environment. See manage roles.
   Encryption	Select whether to use SSL encryption for the data source (default: on). If the data source requires SSL encryption, enable this option, or the connection will fail. Use the switch on the right to turn encryption on or off. Click the arrow next to Encryption to expand detailed configuration. SSL Options: Choose one of two options: If Available: Automatically detect the SSL status of the server. If the server has SSL enabled, connect using SSL; otherwise, use a non-SSL connection. Require: 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. SSL Cipher: Specify the SSL-Cipher encryption algorithm. Verify Server Certificate (SSL CA): If the Doris server uses a self-signed CA certificate, upload the root certificate of that CA here. Verify Server Identity (SSL Identify): Select this option to verify the server name and IP address so the connection is made to the genuine Doris server and helps prevent man-in-the-middle attacks. Authenticate Client: If the Doris server requires a client certificate, upload the client certificate and key here. The Doris server verifies the uploaded information to secure the connection. Note: In most cases, if the Doris server supports SSL encrypted connections, select Require without configuring other options. The system automatically generates keys for the connection. For more SSL configuration methods, see the official documentation: Configure Doris encrypted connections.

4. Click Create Datasource. NineData adds the data source after the connection test succeeds. If the test fails, review the connection settings and try again.

Open Doris in SQL Console

1. Log in to the NineData Console.

2. In the left navigation pane, click DevOps > SQL Console.

   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 the text box below SQL Console. In the data source list, select the target Doris data source, then click Query.

   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. The SQL Console opens for the Doris data source. Use it to run queries, inspect objects, edit data, and manage database operations permitted by your role.

   

5. For detailed instructions, see SQL Console interface.

   tip

   In DevOps Enterprise (Organization Mode), if sensitive columns are configured in the target data source, you cannot view the complete content of those columns until you obtain sensitive column access permission.

Result

The Doris data source is available in NineData SQL Console. You can open it from the SQL workspace, run SQL operations within your permissions, use AI-assisted SQL features, and review execution history.

SQL Console interface

Number	Feature	Description
1	SQL Tab	Represents the currently open SQL Console for a data source. Use the tabs to switch between multiple SQL Consoles, and click the X on the right of a tab to close that SQL Console. Right-clicking a tab supports these actions: Instance Details: Open the details window for the current data source. New Query: Open a new SQL Console for a data source. Close Tab: 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: Instance Details: Show the details of the current data source, including username, connection address, runtime, and connection count. Refresh: 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: Execute the selected SQL statement. : SQL Optimize: AI analyzes the SQL statement on the current cursor position and provides optimization suggestions. : Settings Results: Can be Overwrite or Append. Overwrite (default): The new result set will replace the previous execution result. Append: Generate a new tab to display the new result set. Theme: Change the theme of the SQL console, with options of Light and Dark. Font Size: 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 Format: Adjust the structure of the selected SQL statement for enhanced readability. AI Format: AI analyzes and adjusts the structure of the selected SQL statement for better formatting effects. Execution Plan: View the execution plan of the selected SQL statement. Save SQL: Save all SQL statements in the current SQL console to the SQL list for future use. View SQL: View saved SQL statements. You can insert the desired SQL into the current SQL console using Insert. You can also edit (Edit) or delete (Delete) the target SQL. History: 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). : Editor: Switch to editor mode. : AI Robot: Switch to Intelligent SQL mode. For more information, see Using AI to Manage Data Sources. : Enable Admin Mode|Disable Admin Mode Only accessible by Administrator. When this mode is enabled, all SQL executed in the SQL console is unrestricted by conventions or approval processes and can be executed directly. : Full Screen: Display the SQL console in full-screen mode (based on browser size). After entering full-screen mode, click the Exit 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. Create Database: Right-click any database, click Create Database, enter Name, Character, and the corresponding Collation in the pop-up dialog box, and click OK to create the database visually. Database Details: Right-click the target database, click Database Details to open the Database Details page. You can view the details of the target database here. Select Rows: Right-click the target table, click Select Rows to automatically query the data in the table, returning up to 100 rows by default. Table Details: Right-click the target table, click Table Details to open the Database Details page. You can view all Column and Indexes of the target table here. Select Rows: Right-click the target view, click Select Rows to automatically query the data in the view, returning up to 100 rows by default. Generate DDL: Right-click the target object, click Generate DDL, to generate the SQL statement for creating the object. New Query: Right-click any object, click New Query, to open a new SQL Console tab. Refresh: Right-click any object, click Refresh, to refresh the database list.
9	SQL Editor	Supports these features: SQL Execution: Supports executing a single statement or batch execution. Interrupt Execution: Click Stop 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 > Help 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: Execute the SQL statement at the cursor position. Format: Adjust the structure of the selected SQL statement for better readability. AI Format: AI intelligently adjusts the structure of the selected SQL statement for better readability, providing better results compared to machine formatting. SQL Optimize: AI intelligently analyzes the SQL statement at the cursor position and provides optimization suggestions. General Operations: Include Cut, Copy, Paste, and Select All. Select Current SQL: Select the SQL statement at the cursor position. Help: 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 these 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 Fuzzy and Exact. Click the small arrow next to the column name to sort the result set. Copy Row: Right-click the target cell, select Copy Row to copy the entire row of data where the cell is located. Copy Field: Right-click the target cell, select Copy Field to copy the current cell. Copy as: Right-click the target cell, select Copy as to choose from multiple formats to copy the data in the row where the cell is located. Export: Right-click any cell, select Export to export the current result set in multiple formats. You can export the current page or all data (up to 10,000 rows).

Next steps

• Accessing Doris Data Sources through the Gateway
• Use the SQL Console
• Manage SQL development policies