Model-mapping configuration: complex scenarios and SQL examples
1. Model-mapping event table
1.1 Event time
Common ways of recording time information and how to handle them:
| Example field name | Example value | Data type | Remarks |
|---|---|---|---|
| event_time | 2025-10-15 08:12:34 | Time | Content & type both correct |
Page configuration
- Value Acquisition Method = Read column value
- event time column = event_time
| Example field name | Example value | Data type | Remarks |
|---|---|---|---|
| time_str_a | '2025-10-15 08:12:34' | Text | Content correct Convert the type |
Page configuration
- Value Acquisition Method = custom SQL
try_cast( time_str_a as timestamp )
| Example field name | Example value | Data type | Remarks |
|---|---|---|---|
time_str_b | '15/Oct/2025 13:56:36.864' | Text | Special format Day-month-year_hour-minute-second_millisecond Use a parsing function |
Page configuration
- Value Acquisition Method = custom SQL
try( date_parse( time_str_b, '%d/%b/%Y %H:%i:%s.%f' ) )
For more format specifiers, see: SQL manual
| Example field name | Example value | Data type | Remarks |
|---|---|---|---|
| time_str_c | '25/10/15 08:12:34' | Text | Special format Year-month-day_hour-minute-second Use a parsing function |
Page configuration
- Value Acquisition Method = custom SQL
try( date_parse( time_str_c, '%y/%m/%d %H:%i:%s' ) )
For more format specifiers, see: SQL manual
| Example field name | Example value | Data type | Remarks |
|---|---|---|---|
| ts_10 | 1760506518 | Numeric | 10-digit timestamp Use a parsing function *The time zone is the installation time zone of the query engine |
Page configuration
- Value Acquisition Method = custom SQL
try( from_unixtime( ts_10 ) )
| Example field name | Example value | Data type | Remarks |
|---|---|---|---|
| ts_10 | 1760506518 | Numeric | 10-digit timestamp Use a parsing function *The time zone is the installation time zone of the query engine |
| tz | Asia/Kolkata | Text | *The time zone at the time of data reporting, which needs to be restored to local time |
Page configuration
- Value Acquisition Method = custom SQL
try( from_unixtime( ts_10, tz ) )
| Example field name | Example value | Data type | Remarks |
|---|---|---|---|
| ts_13 | 1760506518123 | Numeric | 13-digit timestamp *The time zone is the installation time zone of the query engine |
Page configuration
- Value Acquisition Method = custom SQL
try( from_unixtime( ts_13 / 1000.000 ) )
1.2 Event time zone
Common ways of recording time zones:
| Example field name | Example value | Data type | Remarks |
|---|---|---|---|
| TIMEZONE | 8 | Numeric | Content & type both correct |
Page configuration
- Value Acquisition Method = Read column value
- event time zone column = TIMEZONE
| Example field name | Example value | Data type | Remarks |
|---|---|---|---|
| TIMEZONE_A | '8' | Text | Content correct Use type conversion |
Page configuration
- Value Acquisition Method = custom SQL
try_cast( TIMEZONE_A as double )
| Example field name | Example value | Data type | Remarks |
|---|---|---|---|
| TIMEZONE_B | 'Asia/Shanghai' | Text | Time zone as a geographic name String type Convert by calculating the offset |
Page configuration
- Value Acquisition Method = custom SQL
try(
date_diff( /*Subtract one moment from the other*/
'minute', /*Calculate the number of minutes*/
cast( '2018-01-01 00:00:00'||"TIMEZONE_B" as timestamp with time zone ),
timestamp'2018-01-01 00:00:00+00:00'
)
/60.0 /*Convert to hours, with decimals*/
)
| Example field name | Example value | Data type | Remarks |
|---|---|---|---|
| TIMEZONE_C | '08:00' | Text | Time zone as a clock offset String type Convert by calculating the offset |
- Value Acquisition Method = custom SQL
try(
date_diff( /*Subtract one moment from the other*/
'minute', /*Calculate the number of minutes*/
cast( '2018-01-01 00:00:00'||"TIMEZONE_C" as timestamp with time zone ),
timestamp'2018-01-01 00:00:00+00:00'
)
/60.0 /*Convert to hours, with decimals*/
)
1.3 Partitioning methods and pushdown logic
1.3.1 Basics
When an analysis model runs a query, the front-end page asks the user to select the date range to query, and the system filters the data based on that range
Usually, the underlying table has a partition field recorded by date. Filtering out unneeded data based on this field first improves query performance
| part_date | data | ... |
|---|---|---|
| 2025-10-01 | ... | ... |
| 2025-10-02 | ... | ... |
| 2025-10-03 | ... | ... |
| 2025-10-04 | ... | ... |
| 2025-10-05 | ... | ... |
Example: the underlying table has a part_date partition column
The user selects a date range on the page
| part_date | data | ... |
|---|---|---|
| 2025-10-01 | ... | ... |
| 2025-10-02 | ... | ... |
| 2025-10-03 | ... | ... |
| 2025-10-04 | ... | ... |
| 2025-10-05 | ... | ... |
The system queries only the necessary data
Partition filter logic that the front end sends to the back end
part_date between
date'2025-10-01' /*Start date*/
and
date'2025-10-03' /*End date*/
The variable parts are as follows:
part_date between
${start_date} /*Start date*/
and
${end_date} /*End date*/
/* The data type of the variables is date */
/* This SQL is the pushdown logic. During queries, the system appends it to the WHERE clause */
Because time zone offsets are involved, the partition pushdown logic is used only as the first filtering step
After that, the data is filtered a second time based on the actual Event Time
1.3.2 Partitioning method examples
| part_date | data | ... |
|---|---|---|
| '2025-10-01' | ... | ... |
| '2025-10-02' | ... | ... |
| '2025-10-03' | ... | ... |
| '2025-10-04' | ... | ... |
| '2025-10-05' | ... | ... |
- The content is year, month, and day separated by hyphens, and the type is String
- Consistent with the structure of the AE system
- Just select the Date column and Format
| part_date | data | ... |
|---|---|---|
| '2025/10/01' | ... | ... |
| '2025/10/02' | ... | ... |
| '2025/10/03' | ... | ... |
| '2025/10/04' | ... | ... |
| '2025/10/05' | ... | ... |
- The content is year, month, and day separated by slashes, and the type is String
- Just select the Date column and Format
| part_date | data | ... |
|---|---|---|
| 20251001 | ... | ... |
| 20251002 | ... | ... |
| 20251003 | ... | ... |
| 20251004 | ... | ... |
| 20251005 | ... | ... |
- The content is year, month, and day as a number, and the type is Number
Also applies when the type is String
- Just select the Date column and Format
| part_date | data | ... |
|---|---|---|
| 1759248000 | ... | ... |
| 1759334400 | ... | ... |
| 1759420800 | ... | ... |
| 1759507200 | ... | ... |
| 1759593600 | ... | ... |
- The content is a 10-digit timestamp of midnight each day, and the type is Number
If the type is String, convert it to Number first
For a 13-digit timestamp, multiply by one thousand after converting the variable to a timestamp
- Select Custom Pushdown Logic and set the SQL
part_date between
try( to_unixtime( ${start_date} ) )
and
try( to_unixtime( ${end_date} ) )
| year_p | month_p | day_p | data | ... |
|---|---|---|---|---|
| 2025 | 10 | 1 | ... | ... |
| 2025 | 10 | 2 | ... | ... |
| 2025 | 10 | 3 | ... | ... |
| 2025 | 10 | 4 | ... | ... |
| 2025 | 10 | 5 | ... | ... |
- 3 partition columns record the year, month, and day separately, and the type is Number
- Select Custom Pushdown Logic and set the SQL
array_position( transform(sequence( ${start_date} , ${end_date} ), x -> year(x) ) , "year_p" ) > 0
and array_position( transform(sequence( ${start_date} , ${end_date} ), x -> month(x) ) , "month_p" ) > 0
and array_position( transform(sequence( ${start_date} , ${end_date} ), x -> day(x) ) , "day_p" ) > 0
/*Consider cases that span months and years*/
2. Value acquisition methods in the asset list
Assume that the original column names are
prop1,prop2,prop3...Long SQL in screenshots may be cut off. The code block in each section shows the complete SQL.
2.1 Type conversion
| Gold coins obtained (raw data) | Gold coins obtained (converted) |
|---|---|
| '1024' | 1024 |
- The "Gold coins obtained" column in the raw data uses the String type
- It can't be used in numeric calculations
- It needs to be converted to the Number type
- Set the SQL in Value Acquisition Method
try_cast( prop1 as double )
2.2 String concatenation
| Server ID (raw data) | User ID (raw data) | Unique user ID (converted) |
|---|---|---|
| SVAP | U01 | SVAPU01 |
- User IDs in the raw data may be duplicated across servers
- The server ID is needed to determine the unique user ID
- The two need to be concatenated
- Set the SQL in Value Acquisition Method
try( concat( "prop1", "prop2" ) )
2.3 String splitting
| Ad delivery information (raw data) | Region (converted) | Group (converted) | Member (converted) |
|---|---|---|---|
| East China,Group 1,Zhang San | East China | Group 1 | Zhang San |
- The text in the raw data contains multiple pieces of information
- You want to split it and use each part separately
- Set the SQL in Value Acquisition Method
try( split_part( "prop1" , ',', 1 ) )
/*Split by comma and take part 1. Note the single and double quotes*/
try( split_part( "prop1" , ',', 2 ) )
/*Split by comma and take part 2. Note the single and double quotes*/
try( split_part( "prop1" , ',', 3 ) )
/*Split by comma and take part 3. Note the single and double quotes*/
2.4 Extract a child property from an object
| Item information (raw data) | Item price (converted) |
|---|---|
| {"item_id":"apple","item_price":88} | 88 |
- The object in the raw data contains the item ID and the item price
- You want to extract the item price from it
- Set the SQL in Value Acquisition Method
try( "prop1"."item_price" )
2.5 Convert text to an object
| Item information text (raw data) | Item information (converted) |
|---|---|
| '{"item_id":"apple","item_price":88}' | {"item_id":"apple","item_price":88} |
- The raw data records item information as JSON text
- You want to convert it to the Row data type
- Set the SQL in Value Acquisition Method
try_cast( json_parse( "prop1" ) as row( item_id varchar, item_price double ) )