DQL Practices: WEB Interface
Ⅰ Deploy DQL service
The startup program of DQL server is {installation directory}/esProc/bin/startDQLSERVER.bat (……startDQLSERVER.sh for Linux). Before starting the DQL server, first deploy the created metadata file TPCH.glmd on DQL server. The server configuration file is located in {installation directory}/esProc/esproc-services/, in which each folder corresponds to a DQL service. Copy the default datalogic service as a new service and name it TPCH:
Then edit server.xml to add the TPCH service. Server address and server port number are also configured:
Get into TPCH directory, copy the metadata file TPCH.glmd to TPCH/conf/, and edit TPCH/service.xml to configure the metadata file:
After the configuration is finished, run startDQLSERVER.bat, click “Start” button and the DQL server’s TPCH service is started:
Ⅱ Perform DQL queries manually
DQL JDBC
A WEB system usually connects to the database through JDBC and executes the SQL query to get data. Same way for executing DQL queries. Put DQL JDBC’s driver jar (esproc-ent-*.jar) in WEB-INF/lib/ and we can make queries in Java:
Connection con = null;
try {
String driverClass = "com.esproc.dql.jdbc.DQLDriver";
String url = "jdbc:esproc:dql://127.0.0.1:3368/TPCH";
String dql = "SELECT * FROM Orders";
Class.forName(driverClass);
con= DriverManager.getConnection(url,"root","root");
ResultSet resultSet = con.createStatement().executeQuery(dql);
while (resultSet.next()) {
System.out.println(resultSet.getObject(1)
+","+resultSet.getObject(2)
+","+resultSet.getObject(3));
}
} finally {
if(con != null)con.close();
}
Driver class: com.esproc.dql.jdbc.DQLDriver;
Connection URL: jdbc:esproc:dql://127.0.0.1:3368/TPCH, which is for connecting to TPCH service on the DQL server;
Query Orders table to print values of the first 3 columns on the console:
Query page
Write the following /raqsoft/dql/jsp/manualDqlQuery.jsp based on the above code to perform the DQL query manually. Use HTML form to submit DQL service name and DQL statement, execute the statement and get result resultHtmlString, and display the result on the query page:
<%@ page language="java" contentType="text/html; charset=UTF-8"pageEncoding="UTF-8"%>
<%@ page import="com.scudata.web.dql.esprocdql.*" %>
<%@ page import="java.sql.*" %>
<%
String dataSource = request.getParameter("dataSource");
String dql = request.getParameter("dql");
String cp = request.getContextPath();
if (dataSource == null) dataSource = "TPCH";
if (dql == null) dql = "select * from orders";
Connection con = null;
String resultHtmlString = null;
try {
String driverClass = "com.esproc.dql.jdbc.DQLDriver";
String url = "jdbc:esproc:dql://127.0.0.1:3368/"+dataSource;
Class.forName(driverClass);
con= DriverManager.getConnection(url,"root","root");
ResultSet rs = con.createStatement().executeQuery(dql);
resultHtmlString = DqlUtil.resultSetToHTMLString(rs);
} finally {
if(con != null)con.close();
}
%>
<html>
<head>
<link rel="stylesheet" href="<%=cp %>/raqsoft/dql/css/queryResult.css"type="text/css">
</head>
<body>
<div>
<form name="executeForm" method="post" accept-charset="UTF-8"
target="_self" action="<%=cp%>/raqsoft/dql/jsp/manualDqlQuery.jsp">
<span>Data Source </span><input name="dataSource" value="<%=dataSource%>" type="text">
<br>
<span>DQL </span><textarea name="dql"class="dql"><%=dql%></textarea>
<input type="button" onclick="executeForm.submit();" value="Execute">
</form>
</div>
<div><%=resultHtmlString %></div>
</body>
</html>
Try to execute Example 2 in DQL Practices: Metadata and Syntax:
Then try Example 5:
Ⅲ Visualize queries
If a query involves many fields and multiple tables having complex associations between them, it is inconvenient to write the query manually. Let’s implement a visual interface to display all candidate tables and fields and use an automatic or visualized method to edit table associations.
1 Get metadata
DQL offers query statement “metadata” that returns complete metadata information in JSON format. Execute the statement on the above query page and we get the following result:
Return detail information for tables, fields and dimensions:
{
"tables": [{ // A list of tables
"name": "Customer", // Table name
"type": "0", // Table type, entity table
"fields": [{ // A list of fields
"name": "CUSTKEY", //Field name
"type": 1, // Field data type
"pk": 1, // Whether it is primary key or not
"dim": "Customer", //Dimension name
"destTable": "Customer" // Target dimension
},
...
],
"fks": [{ // A list of foreign keys
"name": "fk1", // Foreign key name
"destTable": "Nation", // Target dimension table
"fields": ["NATIONKEY"] // Foreign key field(s); can be multiple fields
}],
"subTables": [{ // A list of subtables
"table":"Partsupp" //Subtable
}]
}],
"dims": [{ // A list of dimensions
"name": "Month", // Dimension name
"dt": "4", // Time dimension type
"exp": "", // Formatted expression
"table": "Month", //Dimension source table
"field": "Month", // Dimension source field
"destLevels": [{ // Dimension level
"name": "Year", // Level name
"dest": "Year.Year", // Target dimension
"formula": "int(?/100)" // Function to perform the computation
}]
}],
"annexTables": [ // Homo-dimension tables
[{
"name": "Customer",
"pks": ["CUSTKEY"]
}, {
"name": "CustomerAdd",
"pks": ["CUSTKEY"]
}]
]
}
With the metadata information, we can design and implement easy-to-use visual queries on the WEB page.
I want to emphasize that this set of operations is simply designed for general-purpose use. It isn’t necessarily suitable to be directly used on digital platforms of specific industries and sections. But we believe that once users learn about DQL model’s agile querying capability, it is convenient for them to renovate the existing data visualization platform or design a new one based on DQL model.
2 Query interface overview
Using /raqsoft/dql/jsp/dqlQuery.jsp we have achieved visualized DQL queries in WEB interfaces. Now let’s look at the basic query interface layout. There are three parts:
In the upper part there are icons, which are Undo, Redo, Save and Open in order. The right-most button “Query” is for executing the query;
In the left part there is the data structure tree that lists all available tables and fields. We can expand the multilevel foreign key association tree and click tables and fields to select them to the main query area on the right;
The selected fields are displayed in the main query area in the right part. Here we can also set conditions for the query. A DQL grouping & aggregate statement will be generated by selecting dimension fields and the aggregate fields. If multiple tables need to be aligned according to the same dimension for performing the aggregation, the query interface will clearly list each of these tables and their association relationships with dimensions and generate a DQL statement of multitable association:
Now we try to automatically generate DQL statements for examples in DQL Practices: Metadata and Syntax in the query interface. This will help you become more familiar with the DQL query functionality.
3 Single-table queries
1) DQL example 2 – Generalized field
Click fields of Orders table we need in order:
The auto-generated aliases for the selected fields may not be suitable, we can click them to change:
Click “Query” to pop up the result page /raqsoft/dql/jsp/queryResult.jsp where we can view the auto-generated DQL statement:
2) DQL example 3 – Grouping & Aggregation by dimension/dimension level
Select fields directly and we get their original values. Position the mouse point to a field and we can choose one of the five aggregate operations – sum, average, count, max and min – to query the aggregate value on the field:
Execute the generated DQL statement and view the result:
3) Primary-and-sub tables
A subtable’s aggregate information can be regarded as the extension of the primary table information. Here are a pair of primary-and-subtables (Orders-Lineitem):
Orders.ORDERKEY；
Orders.ORDERSTATUS；
Orders@Lineitem.sum(QUANTITY)；
Orders@Lineitem.min(DISCOUNT)；
They are displayed in the interface as follows:
The multitable association capability determines how flexible queries are. DQL classifies table association relationships into narrower groups and offers a special way for handling each type of association to maximize querying automation. DQL provides highly consistent models across its syntax and visual interface, allowing non-professional, ordinary users to understand them smoothly.
The above three DQL query types appear like single-table queries, but actually they will automatically associate the multiple involved data tables.
4 Alignment association
Besides those inexplicitly automatic associations, there are association types that require some thinking. When data is summarized based on multiple tables, DQL first performs grouping & aggregation on each table and then associates them by aligning them to the dimension value.
1) DQL example 4 – Alignment aggregation on two (multiple) tables by same dimension
Supplier table and Customer table do not have direct association. But both their suppliers and customers can be counted according to nation:
Execute the generated DQL statement and view the result:
2) Align and associate a single table to itself
Based on Lineitem table, we need to find the total quantity in each SHIPDATE year and compute the minimum discount in each RECEIPTDATE year. There are multiple DATEs in Lineitem table, first we perform the aggregations according to different year dimensions and then associate the result sets in alignment, which is the alignment association on Lineitem table itself.
Choose any of the year dimension field and perform aggregation on it:
Then drag the minimum discount onto Table2:
Now we have two different aggregations. In the drop-down year dimension field list of Table2, select the RECEIPTDATE year:
In the generated DQL statement, there are two aggregations based on different year dimensions:
5 Set filtering condition on detail data and aggregate values
We often need to filter detail data during a query. To do this, we need to set the filtering condition;
After data is grouped and summarized, it is probably that we set up filtering condition on the aggregate values to get target groups;
The two types of filtering operations use same syntax as SQL counterpart operations use. They are WHERE and HAVING and the interface is designed as follows:
Execute the generated DQL statement and view the result:
6 Source code
com.scudata.web.dql.esprocdql.DqlUtil.java encapsulates DQL JDBC query and performs preliminary processing of the query result;
/raqsoft/dql/jsp/dqlQuery.jsp is the visual query’s main interface;
/raqsoft/dql/jsp/queryResult.jsp displays query result interface;
Generate metadata tree according to the JSON string format DQL metadata. Operations of setting the main query area are implemented in JavaScript. The JavaScript code is stored in /raqsoft/dql/js/query.js.
There are dependent images in /raqsoft/dql/img/; and there are CSS files in /raqsoft/dql/css/.
You can download the Source Code and have a try yourself.
Ⅳ Summary
The essay mainly introduces how to implement flexible visual queries on WEB using DQL’s query ability. You need to think about how to employ the DQL capability on their own data platforms. Once you have a clear answer, it is natural that which WEB technology you need to choose to implement the capability and that how to design an interface suitable for your own business.
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_SPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL
Chinese version