SQL console (RDBMS)

This article describes how to use the SQL console.

Prerequisite

• The database to be managed already added to NineData. How to add, see Managing Data Sources.

• The data source type must be one of the following: MySQL (Including MySQL series instances), SQL Server, PostgreSQL, Oracle, OceanBase Oracle, OceanBase MySQL, DB2, ClickHouse, Doris, SelectDB, DM (Da Meng), Kingbase, Klustron,StarRocks, SingleStore, DWS, openGauss, GuassDB, TiDB, GaiaDB, GaiaDB-X.

• User must have read-only or read-write permissions on the target data source.

  tip

  Read-only permissions only support viewing the database and SELECT 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

The SQL console of NineData provides several convenient functions to manage data sources efficiently.

Function	Description
Intelligent SQL	Provides natural language conversion functionality, allowing for quick conversion to SQL query statements through short natural language expressions.
AI features	: AI intelligently analyzes the SQL statement on the cursor's current line and provides optimization suggestions. : AI analyzes the structure of the selected SQL statement and performs formatting to enhance readability. : AI converts the current table's create table statement into create table statements for various database types. : AI converts the current table structure into various programming language codes. : AI converts the current stored procedure into a Java function.
Execute SQL Statements	Access data in the data source. Add, delete, and modify databases, tables, and data (records) in the data source. Create views, stored procedures, functions, triggers, and events in the data source. Authorize or revoke permissions to users.
Visual Table Structure Design	Create or edit tables in a visual way. For more information, see Design MySQL Table Structure, Design ClickHouse Table Structure.
SQL Smart Prompt	When typing SQL statements in the SQL console, the system will automatically prompt general object information such as keywords, functions, database, table, columnnames, etc. according to the semantics being input; in addition, the console also provides SQL templates (semantic structure body), grammar help/check and other convenient functions.
Save SQL | View SQL	For commonly used SQL, user can save SQL to NineData, and the saved SQL also supports viewing. And because the SQL is stored in the cloud, it is not limited by the browser or the host, so the saved SQL can be viewed and used anytime and anywhere.
Work Site Recovery	For scenarios such as unexpected browser exit or sudden downtime, just log in to the NineData console again and open the SQL console, and the previously opened data sources and executed SQL commands will be automatically loaded. Since the state of the page is saved in the cloud, it is not limited by the browser or the host, so user can resume the work site anytime and anywhere.
SQL Execution History	SQL will record all the SQL statements executed by the current account in the NineData console, and also supports quick retrieval based on data source, execution time, and SQL keywords. User can also copy/paste the SQL statements in the execution history and re-execute them in the SQL console.
SQL Development Policy Check	Under the organization modelThe organization model supports assigning different roles and resource management permissions to each user under the organization, suitable for multi-user collaborative development within the same organization, while ensuring data security and improving overall productivity., the system checks every SQL statement executed in the SQL console based on the SQL Development Policy configured by the . If a SQL statement does not comply with the development policy, it will be intercepted by the system to ensure database security. After interception, users can modify the SQL according to the SQL development policy and resubmit it, or directly submit a SQL task, depending on the approval process configured by the .

Steps

1. Log in to NineData console .

2. Click > in the left navigation bar .

   tip

   If user have logged in to the data source already and have not logged off yet, the data source page will show up automatically.

3. Click the text box under , the data source previously added will pop up, click the target data source, and click to jump to the SQL console.

   tip

   • If you haven't created data source before, a blank page will be displayed. In this case, please click on on the page.

   • If there are multiple data sources, user can enter keywords in the box for exact or fuzzy search. The fields that support searching are as follows:

     • Data source name

     • IP address

:::

4. Once the SQL console is open, user can perform SQL development operations on the data source. As shown in the figure below:

   For details on how to use the SQL console, please refer to the SQL console interface introduction .

   tip

   If a sensitive data column is added to the target data source , user will not be able to fully view the content of the sensitive column. To view, please apply for sensitive column permission first.

SQL console interface introduction

Serial number	Function	Description
1	SQL console tab	The currently open data source SQL Console displays multiple SQL Consoles that can be switched by clicking on the tabs. Clicking the X on the right side of the tab will close the corresponding SQL Console. The right-click menu supports the following operations: : Open the current data source details window. : Open a SQL Console for the current data source. : Open a new data source SQL Console. : Close the current tab. : Close all other SQL Consoles except the current one. : Close all SQL Consoles to the left of the current SQL Console. : Close all SQL Consoles to the right of the current SQL Console. : Close all SQL Consoles.
2	Open data source	Select and open the new data source SQL console.
3	Data source information	Display the data source information of the current SQL console, including the environment, IP address and port number.
4	Favorite|Refresh	Favorite: Clicking the favorite icon adds the current data source to , allowing for quick selection in a new SQL Console after addition. Refresh: Refresh the data in the current data source.
5	Database name	Check the target database to perform the SQL operation. The effect is equivalent to the USEstatement.
6	Function	: Execute the selected SQL statement. : AI intelligently analyzes the SQL statement on the cursor line and provides optimization suggestions. : : Adjust the structure of the selected SQL statement to enhance readability. : : View the execution plan of the selected SQL statement. : : Save all SQL statements in the current SQL Console to the SQL list for easy access next time. : : View saved SQL statements. You can insert the target SQL into the current SQL Console. Additionally, you can or the target SQL. : : View the execution history of SQL on the target data source. You can choose to filter out the execution history of a certain time period or search the execution history by keywords (SQL statement, database name, error message). : (MySQL only): Enable this feature to ensure that the current SQL Console maintains a continuous connection with the data source without establishing a new connection. It is suitable for scenarios that require session persistence, such as passing parameters or storing intermediate results in a series of queries. : Choose or . (default): The new result set will overwrite the previous execution result. : Generate a new tab to display the new result set. : Change the theme of the SQL Console, choose between and . : Drag the slider to adjust the font size of SQL statements in the SQL Console. The default values are as follows: Windows: 16 MacOS: 13 : (MySQL, ClickHouse, SQL Server, PostgreSQL, Oracle, OceanBase Oracle): Display information about all active connections on the current database server and the queries being executed by these connections. also supports automatic refresh functionality, with options to automatically refresh every 5, 10, 20, or 30 seconds. : | Only can operate. After enabling this mode, all SQL executed in the SQL Console will not be subject to standardization or approval flows and can be executed directly. : Generate a test dataset in the current data source (only supported in test data sources). : Switch editor modes (Oracle not supported yet). : Switch intelligent SQL mode (Oracle not supported yet). For more information, please refer to Managing Data Sources with AI. : Display the SQL Console in full screen (browser size). After going full screen, click to restore the original SQL Console size.
7	Search	Search for the object name, select the object on the left, and then search for the corresponding object name on the right. This supports databases, tables, columns, views, functions, stored procedures, triggers, and events. Currently, object search supports MySQL, PostgreSQL, Oracle, DAMENG. For other data sources, you can input the table name to quickly locate the target data table.
8	Database list	Display all databases and tables in the current data source. The supported functionalities vary depending on the type of data source: MySQL|OceanBase|PolarDB MySQL|AnalyticDB MySQL|TDSQL-C MySQL|GaussDB for MySQL|Aurora MySQL|GaiaDB|GaiaDB-X|GreatSQL : Right-click any database, click , enter the , , and in the pop-up dialog box, click to visually create a database. : Right-click the target table, click to convert the current table's DDL statement into DDL statements for various database types using AI. : Right-click the target table, click to convert the current table structure into code for various programming languages using AI. : Right-click the target stored procedure or function, click > Programming Language to convert the current stored procedure or function into code for the target programming language using AI. Supported languages: Java, Python, C#, C++, Golang, Rust, NodeJS. : Right-click the target database or any table in the database, click to visually create a data table. For more information, see Designing MySQL Table Structures. : Right-click the target table, click to edit the structure of the target table. For more information, see Designing MySQL Table Structures. Create Non-Table Objects: Right-click on the target database, select > Non-Table Object Name to visually create non-table objects. Supported non-table objects include views, stored procedures, triggers, events, and functions. For more information, see Creating Non-Table Objects in MySQL Visually. Manage Non-Table Objects: Right-click on the name of the non-table object under the target database to execute operations such as create, edit, rename, copy, and delete for non-table objects. Supported non-table objects include views, stored procedures, triggers, events, and functions. : Right-click the target database or table, click to export the data table in various formats. : Right-click the target database or table, click to import data into the target database table using various methods. : Right-click the at the bottom of the database list, click to view all user information in the current data source. Oracle|OceanBase Oracle | DaMeng : Right-click the target table, click to convert the current table's DDL statement into DDL statements for various database types using AI. : Right-click the target table, click to convert the current table structure into code for various programming languages using AI. : Right-click the target function, click > Programming Language to convert the current stored procedure or function into code for the target programming language using AI. Supported languages: Java, Python, C#, C++, Golang, Rust, NodeJS. SQL Server : Right-click the target table, click to convert the current table's DDL statement into DDL statements for various database types using AI. : Right-click the target table, click to convert the current table structure into code for various programming languages using AI. : Right-click the target stored procedure or function, click > Programming Language to convert the current stored procedure or function into code for the target programming language using AI. Supported languages: Java, Python, C#, C++, Golang, Rust, NodeJS. ClickHouse : Right-click the target database or any table in the database, click to visually create a data table. For more information, see Designing ClickHouse Table Structures. : Right-click the target table, click to edit the structure of the target table. For more information, see Designing ClickHouse Table Structures. PostgreSQL|Klustron|DWS|openGauss|GaussDB : Right-click any database, click , and enter the required parameters in the pop-up dialog box, then click to visually create a database. : Right-click the target database or any schema, click to visually create a schema. : Right-click on any schema, click to visually edit a schema. (only for PostgreSQL): Right-click the target library, Schema, or any table in the library, click , you can visually create a data table. For more information, please see Designing PostgreSQL Table Structure. (only for PostgreSQL): Right-click the target table, click , you can edit the structure of the target table. For more information, please see Designing PostgreSQL Table Structure. : Right-click Login/Group Roles or the target role, click , enter the required parameters in the pop-up dialog box, then click to visually create a role. : Right-click the target role, click to visually edit the role. TiDB : Right-click any database, click , in the popped-up dialog box, enter , , and the corresponding , click , to visually create the database. : Right-click the target database or any table in the database, click , to visually create the table. : Right-click the target table, click , to edit the structure of the target table. Common Functions or : Right-click on the target database or Schema, click on or to open the or page. Here you can view details of the target database (PostgreSQL, KingBase, Klustron, GBase is currently not supported). : Right-click the target table, click to automatically query the data in the table. By default, it returns 100 rows. : Right-click the target table, click to open the page. You can view all the , , and of the target table (GBase is currently not supported). : Right-click the target view, click to automatically query the data in the view. By default, it returns 100 rows. : Right-click the target object, click to generate the SQL statement for creating the object (not supported for some object types depending on the data source). : Open a SQL Console for the current data source. : Right-click any object, click to refresh the database list. View Object Details: Hover the mouse over the target table name, column name, or index name to display the corresponding detailed information.
9	SQL editor	The following features are supported: SQL Execution: Supports single statement or batch execution. Interrupt Execution: Click to interrupt the execution of SQL during execution. Syntax Highlighting: Different syntax elements are displayed in different colors to improve code readability. SQL IntelliSense: Automatically pops up suggestions based on the semantics you enter while typing commands. 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, etc. You only need to enter the preceding command to select the corresponding template. For example: entering CREATE allows you to choose CREATE TABLE, CREATE VIEW, etc. templates. Right-click Menu: : Execute the SQL statement at the cursor position. : Adjust the structure of the selected SQL statement to enhance readability. : AI intelligently analyzes the SQL statement on the cursor line and provides optimization suggestions. : View the execution plan of the selected SQL statement. : Save all SQL statements in the current SQL Console to the SQL list for easy access next time. : View saved SQL statements. You can insert the target SQL into the current SQL Console. Additionally, you can or the target SQL. : View the execution history of SQL on the target data source. You can choose to filter out the execution history of a certain time period or search the execution history by keywords (SQL statement, database name, error message). (MySQL, ClickHouse, SQL Server, PostgreSQL, Oracle, OceanBase Oracle, GreatSQL): Display information about all active connections on the current database server and the queries being executed by these connections. also supports automatic refresh functionality, with options to automatically refresh every 5, 10, 20, or 30 seconds. General Operations: Including , , , and . : Select the SQL statement at the cursor position. : AI analyzes the currently selected content to provide information such as the semantics of the target SQL syntax, the definition of the target table's table structure, and an overall explanation of the SQL statement logic.
10	Execution information and result set	Display execution information and result set for SQL query. Execution Information: Includes execution time, executed SQL statement, execution status, execution duration, and other details. Result Set: Displays the returned results of the SQL query. You can perform the following actions: Edit Result Set (Conditions: Single table query, includes primary key column or unique key column) Click , , or modify data directly: You can visually perform insert, delete, and update operations on the database. : Right-click on the target cell, select , and edit all fields in the row of the cell. : Right-click on the target cell, select , and upload a file to fill the current cell. : Right-click on the target cell, select , and paste copied row information over the current row. : Right-click on the target cell, select , and overwrite the current cell with the information from the clipboard. : Right-click on the target cell, select , and set the current cell to NULL (only available if the field is not set as NOT NULL). : Right-click on the target cell, select , and set the cell to an empty value. : Right-click on the target cell, select , and delete the row containing the current cell. Common Actions Click : Display the result set in column mode. Click : Display the result set in row mode. Click : Supports exporting the data table in various formats, exporting the current page or all data (up to 10000 rows). Click : Scrolls the result set to the bottom of the page. Clicking restores the view. Click : Allows operations like locking, hiding, and showing columns in the result set. Result set search: Supports and . Click the small arrow on the right side of column names to sort the result set. : Right-click on the target cell, select , and copy the entire row of the cell. : Right-click on the target cell, select , and copy the entire column of the cell. : Right-click on the target cell, select , and copy the current cell. : Right-click on the target cell, select , and choose to copy the row data in multiple formats. : Right-click on any cell, select , and export the current result set in various formats, exporting the current page or all data (up to 10000 rows). View SQL Statement Corresponding to Result Set: Hover over the tab of the target result set to display the SQL statement corresponding to that result set.

FAQ

• Question: what should I do if an error is reported when opening the MySQL data source through the SQL console The user specified as a definer (‘mysql.infoschema‘@‘localhost‘) does not exist?

  Answer: to create a user through an account in the local MySQL client mysql.infoschemaand grant privileges, the commands as follow:

  1. create:mysql> CREATE USER 'mysql.infoschema'@'localhost' IDENTIFIED BY '<Password>';

     tip

     Example:mysql> CREATE USER 'mysql.infoschema'@'localhost' IDENTIFIED BY '12345';

  2. Authorization:mysql> GRANT ALL PRIVILEGES ON *.* TO 'mysql.infoschema'@'localhost';