After completing Data Connections Overview, users can create datasets. It is possible to create a dataset directly from an original table, generate a dataset through multi-table join or union, or use custom SQL for more complex logic. The following describes how to create a dataset using a data table or custom SQL.

(1) Click Data Preparation -> Dataset, click the plus sign on the left, and select "New Dataset"

(2) Select the target data source from the existing data connections. If there is no data connection, you can create one in Data Connections.

(3) Choose between table creation and SQL creation

• On the model configuration page, select Custom SQL or a table from the database on the left, and drag it to the canvas on the right.

• After dragging Custom SQL to the canvas, you can enter SQL statements in the SQL editor area below.
  
• You can also click a table on the left, and in the pop-up window, click "Insert query statement" to quickly generate an SQL query statement for that table.

• Click the "Save" button above the SQL editor box shown below to save the SQL statement; select extraction mode or direct connection mode, and click the Save button in the upper right corner.

3.1 Differences between table creation and SQL creation #

3.2 Quickly create SQL statements #

SQL statements can be created by manually entering them in the SQL input box; you can also select a database and data table from the tables on the left, hover the mouse over the data table, and choose to insert the table name, insert field name, or insert a query statement to quickly create an SQL statement.

• Click Insert Table Name, and the table name will appear in the Custom SQL box.

• Click Insert Field, and the field name will appear in the Custom SQL box.

• Click Insert Query Statement, and the query statement for that table will appear in the Custom SQL box (format: select field A, field B from DB.T_table).

3.3 Custom SQL panel #

Provides formatting, parsing, saving, preview display, data preview, field names, and extraction settings functions.

• Formatting: Click to format the current statement to enhance the readability of the SQL statement.
• Parse: Validate SQL syntax and display the reason for any syntax issues.
• Save: Save the current statement. After modifying the SQL statement, it is recommended to click Save. You must click Save before saving the dataset.
• Show preview: Preview the result of this SQL statement.
• Data preview: Display the execution result of the SQL statement on the left in the right panel. You can adjust the number of preview rows in "Show rows".
• Field names: Click to directly view the field names and data types of the SQL result.
• Extraction settings: Click to configure extraction parameters. For parameter explanations, please refer to Extraction Parameters

Data source limitations: Currently, ADB2.0, ADB3.0, DB2, Phoenix, Vertica, Teradata, PostgreSQL, and Greenplum do not support parameter configuration. Other data sources that allow custom SQL creation support this setting.

• Rename and full screen: In the top bar of the custom SQL, click to rename the SQL dataset, or click the full screen button to make the SQL dataset full screen.

3.4 Synchronize multiple days #

To synchronize data for multiple days each time, such as specifying to synchronize data from 1, 3, or 7 days ago into this dataset, you need to use custom SQL to create the dataset.
Operation method
(1) Create a custom SQL dataset as described above.
(2) Click the synchronization settings at the top to enter the synchronization settings page, then click Advanced settings.

(3) In Advanced settings, there is a switch for synchronizing multiple days. Enable synchronization for multiple days, and there will be a prompt below for setting parameters for synchronizing multiple days.
(4) After enabling synchronization for multiple days, return to the model configuration page and write the SQL according to the writing specifications. Then, configure and save the dataset as usual.
Feature description
In the custom SQL panel, click Variable description in the lower left corner to see the default variable prompts. This means that date variables can be introduced in the custom SQL to implement dynamic date queries.

• ${DATE} Business date, format: yyyy-MM-dd, for example: 2015-05-1
• ${date} Business date, format: yyyyMMdd, for example: 20150526
• ${DATE+n} or ${DATE-n}: Use DATE as the anchor point to move forward (+) or backward (-) n days.
• ${date+n} or ${date-n}: Use date as the anchor point to move forward (+) or backward (-) n days.

SQL writing specifications

• The SQL statement must specify a date field named p_date as the partition field (place it at the end).
• Explicitly specify synchronization requirements in the WHERE clause.

Example 1:
Synchronize data for the first day and for days earlier than the seventh day.

select
user_id,country,city,gender,
date as p_date #指定分区字段
from table A
where date in ('${date}','${date-6}') #指定同步第1天及第7天前数据

Example 2:
Synchronize data for the most recent 7 days.

select
user_id,country,city,gender,
date as p_date #Specify partition fields
from table A
where date >= ${date-6} #Specify the synchronization of data from the past seven days

Notes
If the dataset has already been created, enabling multi-day synchronization and then clicking Synchronize may result in a prolonged waiting state due to dependencies.

• When enabling multi-day synchronization midway, ensure that the dataset already contains partitions covering more than the multi-day synchronization range.
  • For example: If multi-day synchronization is set to synchronize the previous 30 days, but the current dataset only contains partitions for the past 7 days, enabling multi-day synchronization will result in a prolonged waiting state due to dependencies.
  • Solution: First disable multi-day synchronization, backfill data for at least the past 30 days of partitions, and then enable multi-day synchronization again.
  • Note: For datasets created directly by table extraction, after disabling multi-day synchronization, remember to return to the model configuration to modify the partition settings. When multi-day synchronization is enabled, the partition settings are automatically updated. After disabling multi-day synchronization, the partition field will retain the multi-day synchronization settings. If not manually modified, this will cause a single partition in the dataset to synchronize data from N partitions in the upstream table.