What is esProc?
Data File Processor | SQL Enhancer | Report Data Source | JAVA Computing Layer
What does esProc solve?
Data File Processor
| SQL Enhancer
| Report Data Source
| JAVA Computing Layer
|
Report Data Source
JAVA Computing Layer
Agile programming language
SPL（Structured Process Language)
Combined the common advantages of SQL and Java
- Adapt to set batch computation (SQL advantage)
- Good at step-by-step procedure computing (Java advantage)
Well designed rich library functions and consistency syntax
- Easier to master and better performance than Python
e.g.Count the longest consecutively rising trading days for a stock.
Python
| 1 | import pandas as pd |
|---|---|
| 2 | def iterate(col): |
| 3 | prev = 0; |
| 4 | res = 0; |
| 5 | val = 0; |
| 6 | for curr in col: |
| 7 | if curr – prev > 0: |
| 8 | res += 1; |
| 9 | else: |
| 10 | res = 0; |
| 11 | prev = curr; |
| 12 | if val < res: |
| 13 | val = res; |
| 14 | return val; |
| 15 | data = pd.read_excel(‘D:/Stock.xlsx’,sheet_name=0). sort_values(‘tradeDate’).groupby(‘Company’)[‘closePrice’].apply(iterate); |
SQL
| 1 | select max(continuousDays)-1 |
|---|---|
| 2 | from (select count(*) continuousDays |
| 3 | from (select sum(changeSign) over(order by tradeDate) unRiseDays |
| 4 | from (select tradeDate, |
| 5 | case when closePrice>lag(closePrice) over(order by tradeDate) |
| 6 | then 0 else 1 end changeSign |
| 7 | from stock) ) |
| 8 | group by unRiseDays) |
SPL
| A | |
|---|---|
| 1 | =stock.sort(tradeDate) |
| 2 | =0 |
| 3 | =A1.max(A2=if(closePrice>closePrice[-1],A2+1,0)) |
Syntax suitable for describing a natural way of thinking!
Professional syntax in the field of set operation, code is more concise for the same process!
APPLICATION
CASEAn insurance company – stored procedures outside the database
Vertica does not support stored procedures. Complex business logic can only be written in Java. Long code is hard to maintain.
User Evaluation
- The best use for us is to pass parameters to the Vertica database.
- Each cell becomes a data array that are easy to use, compare and manipulate. It is very logical and you have made it user friendly.
Application Effect
- Enable Vertica to support stored procedures outside the database
- The implementation of procedural algorithm is simple and friendly
- BIRT data preparation is simple and efficient
