Note: Descriptions are shown in the official language in which they were submitted.
DATA PROCESSING QUERY METHOD AND DEVICE BASED ON OLAP PRE-
CALCULATION MODEL
BACKGROUND OF THE INVENTION
Technical Field
[0001] The present invention relates to big data technologies, and more
particularly to a data
processing query method based on an OLAP pre-computation model and an
apparatus
implementing the method.
Description of Related Art
[0002] In the era of the Internet where data expand rapidly, enterprises
collect data in an
increasingly large scale and with increasingly sophisticated classification,
making it an
issue to be addressed that how to effectively use collected data by
investigating into
possible patterns therein and providing prospective enterprise-specific
advices.
[0003] OLAP, or online analytical processing, is a data processing technology
helps analysts
make the most use of data by providing efficient, rapid, consistent and
accurate insight to
data from all aspects. OLAP features for its ability to directly imitating
human in terms of
multi-perspective thinking and to build multi-dimensional data models for
users in advance.
For example, this technology may be particularly helpful to sales data
analysis, in which
case time cycles, product categories, distribution channels, geographical
distributions, and
customer groups may each form a dimension. A multi-dimension data model such
developed allows a user to rapidly acquire desired parts of data from
different analytic
perspectives and to dynamically switch among these perspectives for integrated
analysis,
thus providing high flexibility in analysis applications.
[0004] While the conventional pre-computation models employ OLAP-based
approaches, they
have relatively fixed dimension combinations, making them rigid in form and
demanding
in storage resources. In an example where a known pre-computation model has 10
1
Date Recue/Date Received 2021-11-15
dimensions, the only result it provides is from computation using all the 10
dimensions. In
other words, only one combination is supported by the model, and for pre-
computation of
different combinations among the 10 dimensions, pre-computation models
specific to these
individual combinations have to be developed.
SUMMARY OF THE INVENTION
[0005] The objective of the present invention is to provide a data processing
query method
based on an OLAP pre-computation model and an apparatus implementing the
method,
which are capable of flexibly supporting pre-computation tasks with various
dimension
combinations, thus advantageously having flexibility in form and saving both
computing
resources and storage resources.
[0006] To achieve the foregoing objective, in a first aspect, the present
invention provides a
data processing query method based on an OLAP pre-computation model. The
method
comprises:
[0007] extracting a plurality of raw data entries from a data warehouse, and
cleaning the raw
data entries so as to obtain metadata entries that are then cached into a
public cluster;
[0008] based on a dimension list in the pre-computation model, performing
dimension encoding
on every said metadata entry in the public cluster and transferring the
metadata entries to a
compute engine; and
[0009] converting query metrics acquired from a report system into query
conditional
statements in consistence with dimensions in the dimension encoding, and
searching for
the metadata entries in the compute engine that meet the query conditional
statements and
returning the searched metadata entries as query results.
[0010] Preferably, the method of "extracting a plurality of raw data entries
from a data
warehouse, and cleaning the raw data entries so as to obtain metadata entries
that are then
cached into a public cluster" comprises:
[0011] extracting the raw data entries from the data warehouse through an OLAP
system, and
caching the metadata entries into a hive table in the public cluster;
[0012] periodically transferring the hive table in the public cluster into an
OLAP-specific
cluster through the OLAP system; and
2
Date Recue/Date Received 2021-11-15
[0013] periodically processing the hive table in the OLAP-specific cluster
into a parquet file in
an OLAP-specific cluster through the OLAP system.
[0014] More preferably, the method of "performing dimension encoding on every
said metadata
entry in the public cluster and transferring the metadata entries to a compute
engine"
comprises:
[0015] periodically processing the parquet file of the OLAP-specific cluster
into a druid
compute engine through the OLAP system.
[0016] Preferably, the dimension list includes a plurality of dimension fields
arranged in
sequence, or includes a dimension code corresponding to the dimension field
one-to-one at
the same time.
[0017] More preferably, the method of "performing dimension encoding on every
said metadata
entry in the public cluster" comprises:
[0018] matching each dimension value field in the metadata entries with the
dimension fields
in the dimension list, and when there is a value in the dimension value field
that matches
the dimension field, making a digit sequentially corresponding to the
dimension field be 1
during said dimension encoding, or when there is not a value in the dimension
value field
matching the dimension field, making the digit be 0 during said dimension
encoding; and
[0019] assembling and arranging the digits in series so as to generate the
dimension code
corresponding to said metadata entry one-to-one.
[0020] Further, the method of "converting query metrics acquired from a report
system into
query conditional statements in consistence with dimensions in the dimension
encoding,
and searching for the metadata entries in the compute engine that meet the
query
conditional statements and returning the searched metadata entries as query
results"
comprises:
[0021] selecting one or more query dimensions at the report system to form a
dimension query
combination;
[0022] according to the dimension query combination, generating, by the OLAP
system, a
query code in consistence with the dimensions of the dimension code, while
adding a filter
conditional in the query statement based on the query code; and
3
Date Recue/Date Received 2021-11-15
[0023] converting, by the OLAP system, the query statement into a query json,
and searching
for the metadata entries in the compute engine that meet the query conditional
statements
and returning the searched metadata entries as the query results.
[0024] Further, the method of "searching for the metadata entries in the
compute engine that
meet the query conditional statements" comprises:
[0025] according to the query code, searching, in the compute engine, for the
metadata entries
in consistence with the dimensions of the dimension code, and then computing
and
returning the query results.
[0026] As compared to the prior art, the disclosed data processing query
method has the
following beneficial effects.
[0027] In the disclosed data processing query method, a business system
acquires raw data from
an upstream data warehouse and cleans the raw data following predetermined
operational
rules so as to obtain metadata. The metadata is then stored in a public
cluster (e.g., an
HDFS). Afterward, based on a dimension list in the pre-computation model,
dimension
encoding is performed on the stored metadata. The cleaned metadata is
transferred to a
compute engine (e.g., a druid). When a user uses a report system to make a
query, query
metrics a are converted into query conditional statements in consistent with
the dimensions
of the dimension code. Then, according to the query conditional statements,
relevant
metadata in the compute engine is identified through matching and computed so
as to
generate query results that are at last returned to the user.
[0028] Since all the metadata entries in the report are encoded in the order
as that of the
dimension fields in the dimension list, metadata queries can be realized
according to
dimension codes with increased speed. Besides, with the use of the compute
engine, the
overall query efficiency can be further improved. The dimension list in the
report includes
plural common dimension fields, and in practical use, a user can add more
fields or delete
some fields according to his/her needs. Since the report summarizes all the
dimensions,
only one report specific to the currently used pre-computation model is
sufficient to satisfy
query needs for various dimension query combinations. In contrast with a
traditional pre-
computation model for which one dimension combination requires one report, the
present
4
Date Recue/Date Received 2021-11-15
invention is capable of flexibly supporting pre-computation tasks with various
dimension
combinations, thus advantageously having flexibility in form and saving both
computing
resources and storage resources.
[0029] In a second aspect, the present invention provides a data processing
query apparatus
based on a OLAP pre-computation model. The apparatus implements the data
processing
query method as disclosed above, and comprises:
[0030] a data extracting unit, for extracting a plurality of raw data entries
from a data warehouse,
and cleaning the raw data entries so as to obtain metadata entries that are
then cached into
a public cluster;
[0031] a data processing unit, for based on a dimension list in the pre-
computation model,
performing dimension encoding on every said metadata entry in the public
cluster and
transferring the metadata entries to a compute engine; and
[0032] a data querying unit, for converting query metrics acquired from a
report system into
query conditional statements in consistence with dimensions in the dimension
encoding,
and searching for the metadata entries in the compute engine that meet the
query
conditional statements and returning the searched metadata entries as query
results.
[0033] Preferably, the data querying unit comprises:
[0034] a querying module, for selecting one or more query dimensions at the
report system to
form a dimension query combination;
[0035] a query statement-reforming module, for according to the dimension
query combination,
generating, by the OLAP system, a query code in consistence with the
dimensions of the
dimension code, while adding a filter conditional in the query statement based
on the query
code; and
[0036] a query statement converting module, for converting, by the OLAP
system, the query
statement into a query json, and searching for the metadata entries in the
compute engine
that meet the query conditional statements and returning the searched metadata
entries as
the query results.
[0037] As compared to the prior art, the disclosed data processing query
apparatus has the same
beneficial effects as those of the previously described data processing query
method, and
Date Recue/Date Received 2021-11-15
therefore no repetition is made herein.
[0038] In a third aspect, the present invention provides a computer-readable
storage medium,
in which a computer program is stored. The computer program, when executed by
a
processor, can perform the steps of the data processing query method as
described above.
[0039] As compared to the prior art, the disclosed computer-readable storage
medium has the
same beneficial effects as those of the previously described data processing
query method,
and therefore no repetition is made herein.
BRIEF DESCRIPTION OF THE DRAWINGS
[0040] The accompanying drawings are provided herein for better understanding
of the present
invention and form a part of this disclosure. The illustrative embodiments and
their
descriptions are for explaining the present invention and by no means folln
any improper
limitation to the present invention, wherein:
[0041] FIG. 1 is a flowchart of a data processing query method based on an
OLAP pre-
computation model according to one embodiment of the present invention
embodiment.
DETAILED DESCRIPTION OF THE INVENTION
[0042] To make the foregoing objectives, features, and advantages of the
present invention
clearer and more understandable, the following description will be directed to
some
embodiments as depicted in the accompanying drawings to detail the technical
schemes
disclosed in these embodiments. It is, however, to be understood that the
embodiments
referred herein are only a part of all possible embodiments and thus not
exhaustive. Based
on the embodiments of the present invention, all the other embodiments can be
conceived
without creative labor by people of ordinary skill in the art, and all these
and other
embodiments shall be embraced in the scope of the present invention.
Embodiment 1
[0043] Referring to FIG. 1, the present embodiment provides a data processing
query method
based on an OLAP pre-computation model. The method comprises:
[0044] extracting a plurality of raw data entries from a data warehouse, and
cleaning the raw
data entries so as to obtain metadata entries that are then cached into a
public cluster; based
6
Date Recue/Date Received 2021-11-15
on a dimension list in the pre-computation model, performing dimension
encoding on every
said metadata entry in the public cluster and transferring the metadata
entries to a compute
engine; and converting query metrics acquired from a report system into query
conditional
statements in consistence with dimensions in the dimension encoding, and
searching for
the metadata entries in the compute engine that meet the query conditional
statements and
returning the searched metadata entries as query results.
[0045] In the disclosed data processing query method based on an OLAP pre-
computation
model, a business system acquires raw data from an upstream data warehouse and
cleans
the raw data following predetermined operational rules so as to obtain
metadata. The
metadata is then stored in a public cluster (e.g., an HDFS). Afterward, based
on a dimension
list in the pre-computation model, dimension encoding is performed on the
stored metadata.
The cleaned metadata is transferred to a compute engine (e.g., a druid). When
a user uses
a report system to make a query, query metrics a are converted into query
conditional
statements in consistent with the dimensions of the dimension code. Then,
according to the
query conditional statements, relevant metadata in the compute engine is
identified through
matching and computed so as to generate query results that are at last
returned to the user.
[0046] Since all the metadata entries in the report are encoded in the order
as that of the
dimension fields in the dimension list, metadata queries can be realized
according to
dimension codes with increased speed. Besides, with the use of the compute
engine, the
overall query efficiency can be further improved. The dimension list in the
report includes
plural common dimension fields, and in practical use, a user can add more
fields or delete
some fields according to his/her needs. Since the report summarizes all the
dimensions,
only one report specific to the currently used pre-computation model is
sufficient to satisfy
query needs for various dimension query combinations. In contrast with a
traditional pre-
computation model for which one dimension combination requires one report, the
present
invention is capable of flexibly supporting pre-computation tasks with various
dimension
combinations, thus advantageously having flexibility in form and saving both
computing
resources and storage resources.
[0047] A traditional pre-computation model is usually limited to a relatively
fixed dimension
7
Date Recue/Date Received 2021-11-15
combination, such as a dimension combination of A, B, and C. By contrast, the
pre-
computation model used in the present embodiment can work with arbitrary
dimension
combinations according to needs in practical business scenarios. For example,
it performs
pre-computation with a dimension combination of A, B, and C for some day, and
with a
dimension combination of A and C or B and C or A and C for another day, so as
to realize
pre-computation with various dimension combinations.
[0048] In the foregoing embodiment, the method of "extracting a plurality of
raw data entries
from a data warehouse, and cleaning the raw data entries so as to obtain
metadata entries
that are then cached into a public cluster" comprises:
[0049] extracting the raw data entries from the data warehouse through an OLAP
system, and
caching the metadata entries into a hive table in the public cluster;
periodically transferring
the hive table in the public cluster into an OLAP-specific cluster through the
OLAP system;
and periodically processing the hive table in the OLAP-specific cluster into a
parquet file
in an OLAP-specific cluster through the OLAP system. periodically processing
the parquet
file of the OLAP-specific cluster into a druid compute engine through the OLAP
system.
[0050] In a particular implementation, in the process of developing a pre-
computation model,
metadata information of this pre-computation model is created at the same
time. The
metadata information primarily includes a dimension list, which contains a
plurality of
dimension fields arranged in sequence, and optionally contains a dimension
code whose
each digit corresponds to one said dimension field. The pre-computation model
is an off-
line timing aggregation model. Early in the morning every day, it processes
the business
data of yesterday according to the dimension list. The measurements of data
are all pre-
aggregated. Every day a copy of data is processed into the druid compute
engine.
Optionally, the processing is realized by having an IDE task management system
periodically processes spark tasks.
[0051] In the foregoing embodiment, the method of "performing dimension
encoding on every
said metadata entry in the public cluster" comprises:
[0052] matching each dimension value field in the metadata entries with the
dimension fields
in the dimension list, and when there is a value in the dimension value field
that matches
8
Date Recue/Date Received 2021-11-15
the dimension field, making a digit sequentially corresponding to the
dimension field be 1
during said dimension encoding, or when there is not a value in the dimension
value field
matching the dimension field, making the digit be 0 during said dimension
encoding; and
assembling and arranging the digits in series so as to generate the dimension
code
corresponding to said metadata entry one-to-one.
[0053] In a particular implementation, the dimension code is a cuboid. Every
metadata entry is
compared to the dimension fields in the dimension list successively. For a
dimension field
of the metadata that has a dimension value, the digit in the dimension code
sequentially
corresponding thereto is written as 1. For a dimension field of the metadata
that does not
have a dimension value, the digit in the dimension code sequentially
corresponding thereto
is written as 0. Based on this rule, every metadata entry is compared from the
first
dimension field to the last dimension field successively, so as to eventually
generate a
dimension code corresponding the metadata.
[0054] Generally speaking, every metadata entry shall have the time field so
as to ensure the
correct time sequence of the data. Additionally, the respective dimension
fields in
individual metadata entries may be the same or may be different, partially
depending on
the business traits. For instance, a finance-related pre-computation model has
its all
dimension fields related to finance, while a logistics-related pre-computation
model has its
all dimension fields related to logistics.
[0055] For example, in the dimension list of a pre-computation model as
disclosed herein, the
dimension fields are main time, str_prop cd, cros str_prop cd, chnl cd, city
cmpy cd,
and area cd in sequence.
[0056] If main time, str_prop cd, and chnl cd have corresponding dimension
values, while
cros str_prop cd, city cmpy cd, and area cd do not have corresponding
dimension values,
the resulting combination of dimension codes is 110100.
[0057] If main time, str_prop cd, and cros str_prop cd have values, while chnl
cd,
city cmpy cd, and area cd do not have corresponding dimension values, the
resulting
combination of dimension codes is 111000.
[0058] In the foregoing embodiment, the method of "converting query metrics
acquired from a
9
Date Recue/Date Received 2021-11-15
report system into query conditional statements in consistence with dimensions
in the
dimension encoding, and searching for the metadata entries in the compute
engine that
meet the query conditional statements and returning the searched metadata
entries as query
results" comprises:
[0059] selecting one or more query dimensions at the report system to form a
dimension query
combination; According to the dimension query combination, generating, by the
OLAP
system, a query code in consistence with the dimensions of the dimension code,
while
adding a filter conditional in the query statement based on the query code;
and converting,
by the OLAP system, the query statement into a query json, and searching for
the metadata
entries in the compute engine that meet the query conditional statements and
returning the
searched metadata entries as the query results.
[0060] The method of "searching for the metadata entries in the compute engine
that meet the
query conditional statements" comprises: according to the query code,
searching, in the
compute engine, for the metadata entries in consistence with the dimensions of
the
dimension code, and then computing and returning the query results.
[0061] In a particular implementation, a user may select query dimensions in
the report system
to form a dimension query combination. For example, two query dimensions, main
time
and str_prop cd, are selected. Then the dimension codes corresponding to the
two
dimension fields are denoted by 1. Since there are six dimension fields in the
dimension
list, meaning the resulting combination shall be a 6-digital numeral
combination. The
OLAP system automatically generates the code of this dimension query as
110000. It
dynamically reforms the spark logic plan of the query statement, and adds a
filter
specifying that the code is 110000 in the query statement. Then the OLAP
system
according to the reformed spark plan, generates a query json for the druid
through
conversion. The query json contains filters. The druid compute engine based on
the query
code of 110000 search for metadata entries in consistence with the dimension
code, and
then computes and returns the query results. Therein, conversion of the query
json is
realized using a spark-druid open-source plug-in that maps the plan into query
api of the
druid. Through conversion of the filter of the dimension query combination of
the plan into
Date Recue/Date Received 2021-11-15
the process conditions of the druid query json, and addition of the filter for
the dimension
query combination, the druid can screen out data fast and accurately with
significantly
improved performance.
[0062] On the contrary, the traditional pre-computation model that is fixed to
a single
dimension combination when performing pre-computation and requires more pre-
computation models for more dimension combinations, leading to repeated
storage and
waste of storage space.
[0063] Exemplarily, the OLAP system may use Spark Version 2.3 as its SQL
processing engine.
SQL is analyzed and converted by spark parse into LogicalPlan objects.
LogicalPlan is a
tree-like structure. The OLAP system can analyze the LogicalPlan objects, and
identify the
dimension query GroupByAttributeNames as time, str_prop cd, cros str_prop cd
and so
on.
Embodiment 2
[0064] The present embodiment provides a data processing query apparatus based
on OLAP
pre-computation model. The apparatus comprises:
[0065] a data extracting unit, for extracting a plurality of raw data entries
from a data warehouse,
and cleaning the raw data entries so as to obtain metadata entries that are
then cached into
a public cluster;
[0066] a data processing unit, for based on a dimension list in the pre-
computation model,
performing dimension encoding on every said metadata entry in the public
cluster and
transferring the metadata entries to a compute engine; and
[0067] a data querying unit, for converting query metrics acquired from a
report system into
query conditional statements in consistence with dimensions in the dimension
encoding,
and searching for the metadata entries in the compute engine that meet the
query
conditional statements and returning the searched metadata entries as query
results.
[0068] Preferably, the data querying unit comprises:
[0069] a querying module, for selecting one or more query dimensions at the
report system to
form a dimension query combination;
11
Date Recue/Date Received 2021-11-15
[0070] a query statement-reforming module, for according to the dimension
query combination,
generating, by the OLAP system, a query code in consistence with the
dimensions of the
dimension code, while adding a filter conditional in the query statement based
on the query
code; and
[0071] a query statement converting module, for converting, by the OLAP
system, the query
statement into a query json, and searching for the metadata entries in the
compute engine
that meet the query conditional statements and returning the searched metadata
entries as
the query results.
[0072] As compared to the prior art, the disclosed data processing query
apparatus based on
OLAP pre-computation model provides beneficial effects that are similar to
those provided
by the disclosed data processing query method based on OLAP pre-computation
model as
enumerated above, and thus no repetitions are made herein.
Embodiment 3
[0073] The present embodiment provides a computer-readable storage medium, in
which a
computer program is stored. The computer program, when executed by a
processor, can
perform the steps of the data processing query method based on OLAP pre-
computation
model as described above.
[0074] As compared to the prior art, the disclosed computer-readable storage
medium provides
beneficial effects that are similar to those provided by the disclosed data
processing query
method based on OLAP pre-computation model as enumerated above, and thus no
repetitions are made herein.
[0075] As will be appreciated by people of ordinary skill in the art,
implementation of all or a
part of the steps of the method of the present invention as described
previously may be
realized by having a program instruct related hardware components. The program
may be
stored in a computer-readable storage medium, and the program is about
performing the
individual steps of the methods described in the foregoing embodiments. The
storage
medium may be a ROM/RAM, a disk, a compact disk, a memory card or the like.
12
Date Recue/Date Received 2021-11-15
[0076] The present invention has been described with reference to the
preferred embodiments
and it is understood that the embodiments are not intended to limit the scope
of the present
invention. Moreover, as the contents disclosed herein should be readily
understood and can
be implemented by a person skilled in the art, all equivalent changes or
modifications
which do not depart from the concept of the present invention should be
encompassed by
the appended claims. Hence, the scope of the present invention shall only be
defined by the
appended claims.
13
Date Recue/Date Received 2021-11-15
