Creating a Dataset Using Custom SQL Statements

Notes and Constraints

• A dataset can contain only one SQL statement.
• The SQL syntax for creating a custom dataset supports only SELECT, FROM, WHERE, GROUP BY, ORDER BY, SET, and LIMIT.

Prerequisites

• You have subscribed to DataArts Insight.
• A project has been created by referring to Creating a Project.

• A data source has been connected by referring to Creating a Data Source.

Creating a Dataset Using SQL Statements

1. Click in the upper left corner to select a region.
2. In the lower left corner of the navigation pane, select an enterprise project from Enterprise Project.
3. Click the name of the target project to access it.
4. Click Dataset.
5. Click Create Dataset in the upper right corner of the page.

6. Click Edit SQL Statement in the upper right corner of the dataset creation page.
7. Click on the SQL editing page to name the custom data table. If the custom data table is not named, the default data table name sql is used.
8. Enter SQL statements based on service requirements. SQL parameters can be used for dynamic query. After the editing is complete, click Run.

   Example: Sort data1, data2, and data3 in the data table into a dataset.

   SELECT
     *
   FROM
     data
   WHERE
     city IN ('data1', 'data2', 'data3') 
     and date > ${param}
   
   

9. After the running is successful, view the running result on the Running Results tab.
10. Click Finish and Exit to save the custom dataset.

Setting SQL Parameters

1. Use SQL parameters to dynamically edit SQL statements in an SQL script.
   • The parameter format is ${param}, in which param indicates the parameter name. You can obtain the value of a specified parameter by sequence number. Example: ${params[0]} can be used to obtain the first value in the params parameter list.
   • If a parameter is left empty, the related condition is set to 1=1 by default when the SQL statement is executed. In this way, data can be queried properly. You can also use <if test="param != null">Dynamic statement</if> to control the final SQL statement when the parameter is empty.
2. On the SQL editing page, click Parameter Settings in the upper right corner.

   Figure 1 Parameter setting page

   
   

   Table 1 Parameters

   Parameter	Description
   Variable	Variable name.
   Type	Three variable types are supported: text, number, and date.
   Default Value Range	The value can take effect globally or only on the editing page. Global: The query default value takes effect on the dashboard, large screen, and SQL editing page. Edit page only: The query default value takes effect only on the SQL editing page.
   Query Default Value	The parameter is optional. If the dataset is too large and cannot be saved, or if the dashboard query takes too long, it is recommended to adjust the default query value.

Modifying SQL Code

1. Click in the upper left corner to select a region.
2. In the lower left corner of the navigation pane, select an enterprise project from Enterprise Project.
3. Click the name of the target project to access it.
4. In the navigation pane, choose Datasets.
5. In the dataset list, click the dataset created using custom SQL statements to go to the data editing page.
   Note

   If a dataset is created using custom SQL statements, "SQL" is displayed next to the dataset name in the Name column on the Datasets page.

6. Click Edit SQL Statement. On the displayed page, edit the SQL statement.
   • The SQL editor provides a formatting tool to format the entered SQL statements.
   • You can set SQL parameters by referring to Setting SQL Parameters.

   Figure 2 Configuring fields

   
   
7. Click Run. After the running is successful, view the running result on the Running Results tab.
8. Click Finish and Exit to save the changes made to the dataset.