|
1. Create SQL dataset There are two ways to enter the Create Dataset page: Click the icon at the top left of the dataset page to enter the new dataset page. Click SQL dataset to enter the creation page. On the connected SQL connection page, click Menu Bar>New Dataset to enter the SQL dataset creation page connected to the connection. 2. SQL Dataset Dataset Editing Area The SQL dataset editing area supports the source of the data obtained by editing the dataset. The specific functions are as follows: [td]Function | Explain | Connection | You can select the created connection from the existing connection drop-down list. | Edit Connection | Click the Edit connection icon to enter the configuration interface of the selected connection. | Select Table or View/New Custom SQL | Users can decide whether to use tables or views under the database directly or write SQL statements by themselves according to their needs. | Select a table or enter SQL | Set here according to the selection of Select Table or View/User defined SQL Statement. | Download prohibited | If you check Disable Download, the data used in this dataset will not be exported in any place that supports data or dashboard export. At the same time, the data.download.strict attribute is added, which is false by default. If you check "Prohibit downloading", you can export pictures and PDFs. If set to true, neither pictures nor PDFs can be exported. *When exporting data on the detailed data pop-up box, the page will pop up a message: The operation failed. Because the dependent dataset is not allowed to download data. | refresh data | When the user clicks the Refresh Data button, all data fields will be displayed in the right data area. If the data changes (add or delete), click this button to refresh the data synchronously. If the columns referenced in expressions, filters, and other locations change, when refreshing the data, you will be prompted "If the columns in the dataset change, the expression columns, levels, or row filters may not be available. Do you want to clear the expression columns, levels, and row filters?" Otherwise, you will not be prompted. By default, data segments of character type, string type, byte type, Boolean type, date type, time type and timestamp type are stored in the dimension directory, and other data segments are stored in the measurement directory. |
2.1 Select Table or View to Create SQL Dataset After selecting a table or view mode, all tables, views, analysis views (HANA), calculation views (HANA), attribute views (HANA), and Cube (Kylin) existing in the current database will be displayed in the lower blank area. Right click to refresh and search the tables, views and stored procedures in the connection (enter the search text in the upper right). Click the table you want to select, and click the Refresh Data button to see the required data in the data area on the right. 2.2 Select Custom SQL Statement to Create SQL Dataset After selecting a custom SQL statement, the lower blank area becomes two parts, the upper part is consistent with the selection table view, and the lower part becomes a text area. In the text field below, the user can refer to the parameter example to input SQL statements to query the data in the database to generate a dataset. Support adding common, date, text, and number related built-in parameters. Select from the drop-down options and display the corresponding parameters in the SQL statement text box area. Click the table in the table view to quickly enter the corresponding table name in the text field: When using the ACCESS database, there is a $character after the name of the table. When writing SQL statements, users need to use double quotation marks to enclose the table, such as select Sales from "Coffee _ Chain $". When using the driver and database provided by vividime Server product, you need to follow the SQL statement rules of this product when writing SQL statements. Double quotation marks need to be added when there are children in the referenced dataset, such as select Sales from "cloud/test. clqry". If there are no children, double quotation marks need not be added, such as select Sales from test.clqry Quotes are required when quoting keywords. If there is a Date field in dataset a.clqry, because the Date field is a keyword in the database, it needs to be quoted in double quotation marks. Select "Date" from test.clqry Note that when the assignment type is a string type, single quotation marks should be used instead of double quotation marks. For example, select Nation from test.clqry where Nation='China Note: The order by statement is not supported in the SYBASE database. Use the GaussDB200 connection to create SQL datasets, support writing Hint statements, and improve query performance. Hint writing method: refer to the Hint writing method supported by GaussDB200. Please also refer to GaussDB200 for the impact of Hint writing on queries. The difference between writing Hint in the Hint input box and SQL: 1) Hint input box contains Hint: If Hint is written in the Hint input box, the Hint content will be placed after the first Select of the SQL processed by the SQL parser for the SQL dataset and dashboard execution that depends on the SQL dataset (if not from the service dataset logic). The content in the Hint input box is entered by the user, and the format and content need to be written completely according to the Hint writing method. We are only responsible for splicing Hint into SQL, without any processing such as parsing and verification. 2) Hint in SQL statement: If Hint is written directly into the SQL statement, the sub query will not be attempted when Performance Optimization is checked, and the SQL generated by subsequent calculation will be processed by sub query. After completing the statement input, click Refresh to display the data in the right data area: 2.3 SQL Dataset Cache Settings Enabled: It is off by default. To enable the cache of SQL data, you must first enable the global cache settings in [Manage System>System Settings]. Refer to Management System>System Settings>Global Cache Settings. After the SQL dataset cache setting is enabled, you can set the same query to return cache results directly for a period of time, which can be set to 5 minutes, 30 minutes, 1 hour, 2 hours, 4 hours, 12 hours, and 24 hours. Auto Clear Cache Time: After clicking the Enable switch, you can select one or more hourly time periods in a day to automatically clear the cache in the SQL dataset data cache library. Manually clear the cache: click the Enable switch, and then click to manually clear the cache. The message "Clear the dataset cache successfully" appears on the upper right corner of the page, and you can manually clear the cache in the SQL dataset data cache library. 3. AI SQL Intelligent SQL uses the ability of big model to help users understand the meaning of SQL, optimise the performance of SQL, and assist users to write SQL.You need to make sure that you have configured AI service before you use the Intelligent SQL function, please refer to Manage System> System Setting > AI Service Configuration for specific configurations.The Intelligent SQL requires the Y+AIGC license. 3.1 SQL Interpretation If you want to know the meaning of the current SQL statement, you can click "SQL Explanation" at the top right of the SQL statement editing area, as shown below: It is also possible to edit the left SQL statement, and after editing, click the Refresh button in the SQL explanation to get the explanation again. 3.2 SQL Optimisation Suggestion If the SQL execution efficiency is not good, and you want to optimise the SQL statement, you can click "Optimisation Suggestion" at the top right of the SQL statement editing area, as shown below. You can refer to the content of the optimisation suggestions to optimise the SQL. It is also possible to edit the left SQL statement, and after editing, click the Refresh button in the optimisation suggestion to get the SQL optimisation suggestion again. 3.3 Natural Language to SQ If you are not familiar with SQL or want to improve the efficiency of SQL writing, you can directly click "Natural Language to SQL" at the top right of the SQL statement editing area, as shown below. Natural Language to SQL support helps users generate complete SQL statements through dialog. Support continuous Session: i.e., it supports contextual understanding, and SQL can be modified and improved through continuous session. Revert to here: Users can choose to revert to any step in the current session. Regenerate: If the generated SQL is not correct or satisfactory, you can click Regenerate and the system will regenerate the SQL according to the session intent. Reset Session: If you want to clear the effect of the previous session, i.e. clear the contextual relationship, then you can choose to reset the session and start over. Disambiguation: During the session if there are some ambiguous descriptions, the system will intelligently determine these ambiguities and allow the user to make a choice. As shown in the figure below, since there are "product name" and "product type" fields in the data, when the user proposes "product", a pop-up selection will appear, and the user can choose what he/she wants to query, and click "Confirm and Continue". Click "Confirm and Continue", or redo the process.
| 
