Shulou(Shulou.com)06/03 Report--

Overview: SQL functions and stored procedures running on JVM

It is well known that some databases do not have powerful analysis functions (eg. Mysql), some databases do not have stored procedures (eg. Vertica), when it comes to complex data calculation, it can only be realized through external scripts such as Python,R, but these script languages and mainstream engineering languages (Java) are not well integrated. If we directly use engineering languages to achieve functions similar to SQL functions and stored procedures, we often only write lengthy code for a certain computing requirement, and the code is almost unreusable.

In addition, even with powerful analysis functions, it is not easy to implement slightly complex logic, such as the following common business calculation to find out "the top n customers whose sales account for half and sort them by sales". The SQL implementation in Oracle is as follows:

With An as (selectCUSTOM,SALESAMOUNT,row_number () over (order by SALESAMOUNT) RANKINGfrom SALES) selectCUSTOM, SALESAMOUNTfrom (selectCUSTOM,SALESAMOUNT, sum (SALESAMOUNT) over (order by RANKING) AccumulativeAmountfrom A) where AccumulativeAmount > (select sum (SALESAMOUNT) / 2 from SALES) order by SALESAMOUNT descwith An as (selectCUSTOM,SALESAMOUNT,row_number () over (order by SALESAMOUNT) RANKINGfrom SALES) selectCUSTOM, SALESAMOUNTfrom (selectCUSTOM,SALESAMOUNT, sum (SALESAMOUNT) over (order by RANKING) AccumulativeAmountfrom A) where AccumulativeAmount > (select sum (SALESAMOUNT) / 2 from SALES) order by SALESAMOUNT desc

Description: according to the cumulative value of sales from small to large, through the cumulative value of more than "half of the sales" conditions, reverse to find out the customers who account for half of the sales. In order to avoid errors in the window function when calculating the cumulative value of the same sales value, the ranking is calculated by using the subquery.

Here is the code that implements the same logic with the aggregator:

As we can see from the above code, the aggregator replaces the logic that needs to be nested SQL+ window functions with a concise syntax and is generally consistent (consistent with any data source code).

Aggregator is a scripting language that runs on JVM to deal with structured data. Similar to using SQL functions and stored procedures, integration with Java can create portable, powerful and database-independent computing logic. The separation between the computing logic running in the middle layer and the data logic running in the database layer improves the scalability, flexibility and maintainability of the application.

Application scenario: report data preparation application structure

After integration, the aggregator is embedded in the report application layer, which is equivalent to the local logical database (no separate server deployment is needed). It serves as the report data preparation layer between the report and the data source to complete a variety of complex computing tasks.

How to integrate

The following describes how to integrate esProc as the data preparation layer with Vertica as the data source and Birt as the reporting tool.

(1) Birt development environment

1. Basic jar integration

The aggregator JDBC requires three basic jar packages, all of which can be found in the [esProc installation directory]\ esProc\ lib directory:

Dm.jar aggregator calculation engine and JDBC driver package jdom.jar parsing configuration file icu4j\ _ 3\ _ 4_5.jar handles internationalization dm.jar aggregator computing engine and JDBC driver package jdom.jar parsing configuration file icu4j\ _ 3\ _ 4_5.jar handles internationalization

In addition to the base package, there are some jar packages for specific functions. For example, if you want to use other databases as the data source of the aggregator in JDBC, you also need the driver jar package of the corresponding database. This article involves vertica, so add its JDBC driver package (with vertica9.1) at the same time. 0 as an example)

Vertica-jdbc-9.1.0-0.jar vertica can be downloaded from the official website.

After obtaining the above Jar, copy it to the Birt development environment [installation directory]\ plugins\ org.eclipse.birt.report.data.oda.jdbc_4.6.0.v20160607212.

Note: in the red section, different birt versions are slightly different.

2. Profile integration

RaqsoftConfig.xml, mainly including aggregator authorization, script file paths, other connection configurations as aggregator data sources, and so on.

You can find it in [esProc installation directory]\ esProc\ config. You need to copy it and place it under the classpath, and copy it to the Birt development environment [installation directory]\ plugins\ org.eclipse.birt.report.data.oda.jdbc_4.6.0.v20160607212.

Note: the configuration file name cannot be changed

(2) Birt application environment

1. Copy all the jar in (1) to the WEB-INF\ lib of the application

2. Copy raqsoftConfig.xml to the WEB-INF\ classes of the application

Example 1: general call

1. Sales fields and data description in Vertica (through vsql query, this test database has annual data in 2013-14-15)

2. Write and deploy esProc scripts

(1) add vertica JDBC driver package to esProc designer

Download the jdbc driver package (such as vertica-jdbc-9.1.0-0.jar) on the vertica official website and put it under [esProc installation directory]\ common\ jdbc

(2) add vertica data sources

Open the designer and add JDBC connection to Tool-Datasource connection

Click ok to save, then click connect to connect

When the data source name changes to pink, the connection is successful.

(3) write algorithm script (file: VerticaExternalProcedures.dfx)

File-New

(4) deployment script

Deploy the script file to the main directory of the script file in the raqsoftConfig.xml configuration.

3. Add verticaLink data source connection configuration to the esProc configuration file raqsoftConfig.xml.

4. Create a new report in the Birt development tool and add the esProc data source "esProcConnection".

Driver class is "com.esproc.jdbc.InternalDriver (v1.0)" and dm.jar will be used.

Database URL is "jdbc:esproc:local://"

5. Birt calls Vertica external stored procedure (esProc dataset)

Create a new "Data Sets", select the configured aggregator data source (esProcConnection), and the dataset type selection stored procedure (SQL Stored Procedure Query)

Next, query script (Query-Query Text) input: {call VerticaExternalProcedures ()}

Where: VerticaExternalProcedures is the file name of the esProc script

Finish, preview data (Preview Results)

At this point, you can see that the esProc script is used as an assistant for fetching Vertica external stored procedures.

The process of calculation.

6. Birt Web side presentation

Take a simple grid report as an example

The report is designed as follows

Web publish Preview

Example 2: parameter call

Here, "find out the first n customers whose order sales account for half and sort them by sales from large to small" is changed to require annual inquiry, that is, "query the top n customers whose order sales account for half by year, and sort them by sales from big to small." therefore, the parameter filtering function is added.

Let's take a look at the specific changes:

1. EsProc script adds annual parameters and filtering functions.

Open the esProc designer, Program-Parameter-Add

The parameter name is "qyear" (can be different from the report parameter name)

Script changes:

Note: A2 adds conditional filtering

2. Add annual parameters to the report.

The report adds an annual query entry parameter named "qyear"

The development tool opens the report, Data Explorer-Report Parameter-new patameter

Default value is the default value for qyear.

3. The report dataset adds data set parameters and binds to the report parameters

Edit VerticaExternalProcedures datasets within Data set

Change Query Text to "{call VerticaExternalProcedures (?)}",? For the import parameter year

Degree, which is set here with placeholders.

Select Parameters, add the dataset parameter qyear, and bind to the report parameter qyear.

Preview Results. Only 2013 data can be queried according to the default value of qyear.

After changing it to "2015"

4. Preview on Web

Query "2015" annual data

After modifying or introducing the qyear to "2013" years later

For more information on "birt Integration solution", please see http://c.raqsoft.com.cn/tag/Report

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.