Add custom query

Overview

When the query capability of the analysis model cannot meet business requirements and the data analyst has a certain SQL editing ability, it is recommended to use the "Custom Query" module to complete the implementation and construction of complex business logic, independently write SQL statements, which can meet complex and ever-changing business scenarios, achieve data query and flexible analysis.

Operation entrance

There are two entry points for adding custom query results to the business market:

Entrance 1: "Create a model" in the dataset;

Entrance 2: Analyze the entrance and find "Business Analysis ->Custom Query"

Add custom query

1.Write the SQL statement in the custom query, obtain the query results, and click the "Add to Business Market" button to add it to the business market

2.When adding, it will verify whether there are query results. If there are no results in the query, it cannot be added;

3.Support saving 1 million after setting the business model name and comments row data to report;

4.Supports two types of storage methods: offline data and real-time view. The real-time type is controlled by a switch and can be turned on as needed.

Business Model Configuration Information

When the results of the customized query are published to the business market storage, the system has already helped users complete the configuration information by default, and there is no need to manually set it. You can directly view/re edit the configuration information in the business market, as shown in the following figure:

Configuration interface detailed functions please refer to the following table:

	Category	Name	Function
1	Basic Information	Business Model Name	Business model name, globally unique within the project and cannot be duplicated.
		Group	The group where the business model is located
		Space Type	Divided into: public space and personal space The business model of personal space supports lightweight scheduling, helping users verify model data; The business model of public space as a stable data asset empowers the use of public report services business
		Data scheduling mode	Divided into: offline, real-time, and real-time aggregation
		Remarks	Detailed description of the business model
2	Business Settings	master time	The datetime type field that exists in the current SQL can be specified as the main time. Once the main time is specified, the current SQL will incrementally update according to this time field Once the main time is set and saved, it cannot be changed again to ensure the stable operation of the data If there is no datetime type field in the current SQL, the main time and incremental update cannot be specified
		Data Range	Obtain data from the current time interval during data updates
3	Update Settings	Update Method	Supports two update methods: Routine update: The system automatically updates the data according to the configuration information at a fixed frequency. Manual Update: Manually trigger the update task for this model
		Update Type	Supported update types: Full Update: It refers to updating all the data corresponding to this model in the database to the business dataset Incremental update: Each update is incrementally updated according to the increase in main time, preserving historical existing data Full update can be changed to incremental update through the main time field setting, and incremental update cannot be changed to full update to ensure stable and traceable data
		Update Frequency	Execution frequency of routine update tasks
4	Permission settings	data export	Allow members with data permissions to export business model data, and the exported data scope is consistent with the data permissions of the member
		Management Permission	Add accounts, roles, or positions to manage this business model together with the creator. For specific configuration details, please refer to:Business Model Authorization Configuration Management permissions include: editing/deleting business models, viewing data, viewing configurations, configuring charts, and updating data
		Data permissions	Objects with data permissions can view business model data within a specified range and use this model to configure charts For specific configuration details, please refer to:Business Model Authorization Configuration

Real time aggregation mode

Impala's Live Aggregation mode can support incremental, real-time aggregation queries on Kudu tables or mixed views containing Kudu tables (such as events), controlled by switches and enabled on demand, as shown in the following figure:

The implementation principle mainly relies on Kudu's SetDiffScan for incremental scanning, avoiding the need to re-scan the entire table with each query, while the SQL runs for a long time, keeping the aggregation state in memory, and printing when the aggregation changes

Grammar rules to note:

Only a single table GROUP BY is supported
When writing SQL, ensure that the group by statement is filled in, and the indicator must be equipped with an aggregation function

Display and Preview

Custom Query ResultsAfter successfully saving, add a new line of model information on the list page of the business market and remind the user to "add successfully", as shown in the following figure:
After the data update is successful, enter the business model details page to view it. According to the user's actual data permissions, 100 sample details data will be loaded by default
Simultaneously displaying the query statements of the current model, supporting replicability
Based on the data content of each field in the query results, the report will determine the data type of the field