Is it possible to build a consistent online-offline SQL computing platform through this project

[yfclark]

I am a data engineer, and my daily work involves a lot of feature engineering. I usually use Python pandas to build feature engineering, and then translate it into streaming calculations for online deployment. I am delighted to have found this project, and I am considering whether it's possible to construct a similar syntax in SQL. This would allow me to compute features offline using ClickHouse for model training and use the same set of SQL syntax to directly extract features for models during online deployment

[jovezhong]

Hi @yfclark , thanks for starting the discussion.

Yes, one of the key strength of Proton is to provide the SQL-based analysis for both streaming data and historical data, or even processing both of them in a single SQL with CTE/subquery/JOIN/etc.

Many of our users or customers are in the fintech area and they need to run so-called backtest to verify a certain pattern with massive historical data, as well as need to apply the similar logic/strategy for real-time, so that they can identify buy/sell signals with low latency. Before using Proton, they have to use Python or other batch framework for the "backtest" and refine the strategy. Once the design is confirmed, they may have to implement again with Flink or custom C++ code to run this in real-time. With Proton, they can build the same SQL for both backtest and streaming processing. This approach is very similar to what you described. You can also check the blog from out CTO at https://www.timeplus.com/post/real-time-machine-learning

Be more specific, for example you have build a complex SQL for feature engineering and save this as a view: build_features. Here are a few ways to run this in different mode:

• select * from build_features will process new data and calculate the features. You can save them to a Kafka topic or ClickHouse
• select * from table(build_features) will run the calculation logic for the entire historical data
• select * from build_features where _tp_time>now()-30d will rewind to 30 days ago, process all historical ldata since then, and smoothly transit to process new data from Kafka/ApiPush

Happy to discuss more in this thread, or in our Community Slack: https://timeplus.com/slack

[yfclark]

Thank you for your response. I've carefully reviewed the documentation, and it seems that many operators in the current project only support streaming computation and not historical computation. Additionally, local UDFs currently only support JavaScript. It can be anticipated that developing such a system would require more operator development, especially for more convenient UDF development. I'm particularly eager for UDFs that can be used in both streaming and historical computation. Personally, I often use Python, C++, and Rust. Since the project is using C++, I wonder if there are tutorials available for C++ UDF development. Also, I'd like to inquire about any future plans regarding UDF support in the project.

[jovezhong]

Hi @yfclark , when you build a UDF and apply it in a query, it doesn't matter whether the data is from historical or from the kakfa topic as fresh data. The query engine just feeds the data to the UDF and UDF returns the results after processing. So it supports both streaming and historical data.

Regarding building the UDF with C++, Rust, or Python, we are evaluating those options. The hard part is Proton is core engine in Timeplus Cloud, so we'd like to have a sandbox to run custom code. Google V8 provides such as a sandbox. You may consider building a HTTP based service as "Remote UDF", even it runs locally. But you're right, to get best possible performance, try to avoid HTTP traffic. Besides JS UDF and remote UDF, we also built a local-process UDF, which is to call a binary in the same machine but that's not documented yet. Let me know if you want to try that. WASM could be an option, but it's not mature for Python.