2025-09-28-flink-sql-ml-dataset-feature-etl

Ordiy Lv5
  2025-09-29 14:52:32 2025-09-29 14:52:32 Created   2026-03-23 15:48:10 2026-03-23 15:48:10 Updated  

Flink SQL ML Dataset 特征 ETL 实践

背景

使用的数据集合

1
2
3
4
5
6
7
8
9
10
"user_id","event_first_time","min_21003_time","diff_seconds","package_num","load_event_num","ua_agent_num","ip_num","domin_num","req_path_num","is_fraud_data"
"U2501205983333967043139970",1737378073,1737378074,1,1,0,1,1,1,1,1
"U2506241054676336904189854",1750801770,1750801771,1,1,0,1,1,1,1,1
"U2506242264675213829386795",1750790539,1750790540,1,1,0,1,1,1,1,1
"U2507193194887366162065514",1752912063,1752912064,1,1,0,1,1,1,1,1
"U2507197544884989968119222",1752888301,1752888301,0,1,0,1,1,1,1,1
"U2507288614968746450827527",1753725865,1753725867,2,1,0,2,2,1,1,1
"U2506257924678299668897301",1750821398,1750821398,0,1,0,1,1,1,1,1
"U2501201533330848791526523",1737346890,1737346892,2,1,0,1,1,1,1,1
"U2501215883341272363411252",1737451125,1737451126,1,1,0,1,1,1,1,1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
+----+--------------------------------+----------------------+----------------------+----------------------+----------------------+----------------+----------------------+----------------------+----------------------+----------------------+---------------+-------------------------+-------------------------+
| op |                        user_id |     event_first_time |       min_21003_time |         diff_seconds |          package_num | load_event_num |         ua_agent_num |               ip_num |            domin_num |         req_path_num | is_fraud_data |            window_start |              window_end |
+----+--------------------------------+----------------------+----------------------+----------------------+----------------------+----------------+----------------------+----------------------+----------------------+----------------------+---------------+-------------------------+-------------------------+
| +I |                                |           1758878424 |               <NULL> |               <NULL> |                 1031 |              0 |                 1188 |                14437 |                 1017 |                 1247 |             0 | 2025-09-26 09:20:00.000 | 2025-09-26 09:40:00.000 |
| +I |     U2408158291967808123349178 |           1758879200 |               <NULL> |               <NULL> |                    1 |              0 |                    1 |                    1 |                    1 |                    1 |             0 | 2025-09-26 09:20:00.000 | 2025-09-26 09:40:00.000 |
| +I |     U2410139862481893240500555 |           1758879526 |               <NULL> |               <NULL> |                    1 |              0 |                    1 |                    1 |                    1 |                    1 |             0 | 2025-09-26 09:20:00.000 | 2025-09-26 09:40:00.000 |
| +I |     U2412285193134036094654771 |           1758878667 |               <NULL> |               <NULL> |                    1 |              0 |                    1 |                    1 |                    1 |                    1 |             0 | 2025-09-26 09:20:00.000 | 2025-09-26 09:40:00.000 |
| +I |     U2501310123432026927452582 |           1758878480 |               <NULL> |               <NULL> |                    1 |              0 |                    1 |                    1 |                    1 |                    1 |             0 | 2025-09-26 09:20:00.000 | 2025-09-26 09:40:00.000 |
| +I |     U2503174453817799755943446 |           1758878536 |               <NULL> |               <NULL> |                    1 |              0 |                    1 |                    1 |                    1 |                    1 |             0 | 2025-09-26 09:20:00.000 | 2025-09-26 09:40:00.000 |
| +I |     U2504184264091453280877909 |           1758878826 |               <NULL> |               <NULL> |                    1 |              0 |                    1 |                    1 |                    1 |                    1 |             0 | 2025-09-26 09:20:00.000 | 2025-09-26 09:40:00.000 |
| +I |     U2504251294153162644881029 |           1758879058 |               <NULL> |               <NULL> |                    1 |              0 |                    1 |                    1 |                    1 |                    1 |             0 | 2025-09-26 09:20:00.000 | 2025-09-26 09:40:00.000 |
| +I |     U2504300164198557034990931 |           1758878621 |               <NULL> |               <NULL> |                    1 |              0 |                    1 |                    1 |                    1 |                    1 |             0 | 2025-09-26 09:20:00.000 | 2025-09-26 09:40:00.000 |
| +I |     U2505111794292405717848367 |           1758878904 |               <NULL> |               <NULL> |                    1 |              0 |                    1 |                    1 |                    1 |                    1 |             0 | 2025-09-26 09:20:00.000 | 2025-09-26 09:40:00.000 |
| +I |     U2505256274417522939123212 |           1758879540 |               <NULL> |               <NULL> |                    1 |              0 |                    1 |                    1 |                    1 |                    1 |             0 | 2025-09-26 09:20:00.000 | 2025-09-26 09:40:00.000 |
| +I |     U2506017554476961091074342 |           1758879534 |               <NULL> |               <NULL> |                    1 |              0 |                    1 |                    1 |                    1 |                    1 |             0 | 2025-09-26 09:20:00.000 | 2025-09-26 09:40:00.000 |
| +I |     U2506030584492718841993313 |           1758878668 |               <NULL> |               <NULL> |                    1 |              0 |                    1 |                    1 |                    1 |                    1 |             0 | 2025-09-26 09:20:00.000 | 2025-09-26 09:40:00.000 |
| +I |     U2506113694560941304456029 |           1758878684 |               <NULL> |               <NULL> |                    1 |              0 |                    1 |                    1 |                    1 |                    1 |             0 | 2025-09-26 09:20:00.000 | 2025-09-26 09:40:00.000 |
| +I |     U2506220274659720884514873 |           1758879145 |               <NULL> |               <NULL> |                    1 |              0 |                    1 |                    1 |                    1 |                    1 |             0 | 2025-09-26 09:20:00.000 | 2025-09-26 09:40:00.000 |
| +I |     U2506256264678270999883602 |           1758879032 |               <NULL> |               <NULL> |                    1 |              0 |                    1 |                    1 |                    1 |                    1 |             0 | 2025-09-26 09:20:00.000 | 2025-09-26 09:40:00.000 |
| +I |     U2506277594695454788271906 |           1758879464 |               <NULL> |               <NULL> |                    1 |              0 |                    1 |                    1 |                    1 |                    1 |             0 | 2025-09-26 09:20:00.000 | 2025-09-26 09:40:00.000 |
| +I |     U2507044054760537992881781 |           1758878901 |               <NULL> |               <NULL> |                    1 |              0 |                    1 |                    1 |                    1 |                    1 |             0 | 2025-09-26 09:20:00.000 | 2025-09-26 09:40:00.000 |

使用session window

1
2
3
4
5
6
7
8
9
10
11

+----+--------------------------------+----------------------+-------------------+----------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+---------------+-------------------------+-------------------------+-------------------------+----------------------+
| op |                        user_id |     event_first_time | install_event_num | load_event_num |       min_21003_time |         diff_seconds |          package_num |         ua_agent_num |               ip_num |            domin_num |         req_path_num | is_fraud_data |            window_start |              window_end |         session_rowtime |                  cnt |
+----+--------------------------------+----------------------+-------------------+----------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+---------------+-------------------------+-------------------------+-------------------------+----------------------+
| +I |     U2509272985497206532676420 |           1759010476 |                 1 |              1 |           1759010481 |                    5 |                    1 |                    1 |                    1 |                    1 |                    1 |             0 | 2025-09-27 22:01:16.000 | 2025-09-27 22:11:21.000 | 2025-09-27 22:11:20.999 |                    2 |
| +I |     U2509273695497207164128674 |           1759010479 |                 1 |              1 |           1759010485 |                    6 |                    1 |                    1 |                    1 |                    1 |                    1 |             0 | 2025-09-27 22:01:19.000 | 2025-09-27 22:11:25.000 | 2025-09-27 22:11:24.999 |                    2 |
| +I |     U2509272035497207488950412 |           1759010486 |                 1 |              0 |           1759010486 |                    0 |                    1 |                    1 |                    1 |                    1 |                    1 |             0 | 2025-09-27 22:01:26.000 | 2025-09-27 22:11:26.000 | 2025-09-27 22:11:25.999 |                    1 |
| +I |     U2509275485497206804487695 |           1759010476 |                 1 |              1 |           1759010486 |                   10 |                    1 |                    1 |                    1 |                    1 |                    1 |             0 | 2025-09-27 22:01:16.000 | 2025-09-27 22:11:26.000 | 2025-09-27 22:11:25.999 |                    2 |
| +I |     U2509274995497207397997555 |           1759010490 |                 1 |              0 |           1759010490 |                    0 |                    1 |                    1 |                    1 |                    1 |                    1 |             0 | 2025-09-27 22:01:30.000 | 2025-09-27 22:11:30.000 | 2025-09-27 22:11:29.999 |                    1 |
| +I |     U2509276095497207913043727 |           1759010490 |                 1 |              0 |           1759010490 |                    0 |                    1 |                    1 |                    1 |                    1 |                    1 |             0 | 2025-09-27 22:01:30.000 | 2025-09-27 22:11:30.000 | 2025-09-27 22:11:29.999 |                    1 |
| +I |     U2509279885497196267929321 |           1759010492 |                 1 |              0 |           1759010492 |                    0 |                    1 |                    1 |                    1 |                    1 |                    1 |             0 | 2025-09-27 22:01:32.000 | 2025-09-27 22:11:32.000 | 2025-09-27 22:11:31.999 |                    1 |
  • Title: 2025-09-28-flink-sql-ml-dataset-feature-etl
  • Author: Ordiy
  • Created at : 2025-09-29 14:52:32
  • Updated at : 2026-03-23 15:48:10
  • Link: https://ordiy.github.io/posts/2025-09-28-flink-sql-ml-dataset-feature-etl/
  • License: This work is licensed under CC BY-NC-SA 4.0.
Comments
On this page
2025-09-28-flink-sql-ml-dataset-feature-etl
  1. Flink SQL ML Dataset 特征 ETL 实践
    1. 背景
    2. flink TUMBLE TVF 处理的结果
    3. 使用session window