Help Center/ DataArts Insight/ User Guide/ Creating a Dataset/ Creating a Dataset/ Creating a Dataset Using SQL Statements
Updated on 2025-05-20 GMT+08:00
View PDF
Share

Creating a Dataset Using SQL Statements

When analyzing data, you can create data tables using custom SQL statements for complex logic or models. These statements can be customized with variable settings to accommodate more complex and dynamic analysis scenarios.

Notes and Constraints

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

Prerequisites

Procedure

  1. Log in to the DataArts Insight console.
  2. Click in the upper left corner of the management console to select a region. Then, select an enterprise project in the upper right corner.
  3. On the top menu of the console, click Project. On the displayed My Projects page, click the name of the desired project.
  4. Choose Data Management > Datasets. On the displayed page, click Create Dataset.
  1. Click Edit SQL Statement in the upper right corner of the page.
  2. 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.
  3. 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}
  4. After the running is successful, view the running result on the Running Results tab.
  5. Click Finish and Exit to save the custom dataset.

Setting SQL Parameters

During data analysis, if you need to upload parameters to SQL when viewing dashboards or large screens, you can set SQL parameters. This section describes how to set SQL parameters.

  1. Use SQL parameters to dynamically edit SQL statements in a 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 nonempty.
  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

    Options: Text, Numeric, Date, and System variable.

    • Text: Common text type. When assembling and executing SQL, the input value is enclosed in single quotation marks, like 'value'.
    • Numeric: Integer or decimal type. When assembling and executing SQL, the input value is directly concatenated.
    • Date: Year, quarter, month, day, hour, minute, and second date type data. When assembling and executing SQL, the data is formatted as a string according to the specified date format and enclosed in single quotation marks. Supported types include:
      • YEARREGION: Year;
      • QUARTERREGION: Year and quarter;
      • MONTHREGION: Year and month;
      • DATEREGION: Year, month, and day;
      • YMDHMSREGION: Year, month, day, hour, minute, and second.
    • System variable: System-level variable. Specific values cannot be specified and are provided by the system backend, for example, the current access username is used as the value to assemble and execute SQL.
      NOTE:

      System variables are not displayed in the query control of the dashboard.

    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

As the analysis scenario changes, there may be a need to make corresponding adjustments to the SQL code to accommodate new data requirements.

  1. Log in to the DataArts Insight console.
  2. Click in the upper left corner of the management console to select a region. Then, select an enterprise project in the upper right corner.
  3. On the top menu of the console, click Project. On the displayed My Projects page, click the name of the desired project.
  4. Choose Data Management > Datasets.
  5. Click the dataset name.

    If a dataset is created using a custom SQL statement, the dataset name is appended with SQL.

  1. 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
  2. Click Run. Once the running is successful, view the running result on the Running Results tab.
  3. Click Finish and Exit to save the changes made to the dataset.
Parent topic: Creating a Dataset