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 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 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	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, 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	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, it will be intercepted by the system to ensure database security. After interception, according to the approval process configured by Administrator, 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 Datasource > Datasource.

3. Click **Datasource** tab, and click **Create Datasource** on the page. In the popup window for selecting the data source type, choose **Database**> **Doris**, and configure the parameters based on the table below 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 the name of the input data source. Please use a meaningful name for easy future lookup and management.
   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. 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 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, refer to 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, you need to click the Connection Test on the right. Two outcomes are possible: Connection Successfully 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.
   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 actual business purpose of this data source as the environmental identifier. Default options include PROD and DEV, 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 Prod Admin role only supports accessing data sources in the PROD environment and cannot access data sources in other environments. For more information, see manage roles.
   Encryption	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 Encryption to expand detailed configuration. SSL Options: Choose from 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): 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. Authenticate Client: 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 Require 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 Create Datasource 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 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 on the text box below SQL Console, and a list of available data sources will pop up. Click on the desired data source and click Query 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 DevOps Enterprise (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: 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 Columns 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 the following 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 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 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).

Related Links

Accessing Doris Data Sources through the Gateway