CN109408580B - Cross-data-source SQL compiling device and method - Google Patents
Cross-data-source SQL compiling device and method Download PDFInfo
- Publication number
- CN109408580B CN109408580B CN201811294111.2A CN201811294111A CN109408580B CN 109408580 B CN109408580 B CN 109408580B CN 201811294111 A CN201811294111 A CN 201811294111A CN 109408580 B CN109408580 B CN 109408580B
- Authority
- CN
- China
- Prior art keywords
- sql
- query
- group
- expression
- user group
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Active
Links
- 238000000034 method Methods 0.000 title claims abstract description 19
- 238000003066 decision tree Methods 0.000 claims description 4
- 230000009471 action Effects 0.000 description 4
- 238000004458 analytical method Methods 0.000 description 4
- 230000006399 behavior Effects 0.000 description 2
- 238000007405 data analysis Methods 0.000 description 2
- 238000012986 modification Methods 0.000 description 2
- 230000004048 modification Effects 0.000 description 2
- 230000009286 beneficial effect Effects 0.000 description 1
- 230000007547 defect Effects 0.000 description 1
- 238000013461 design Methods 0.000 description 1
- 238000010586 diagram Methods 0.000 description 1
- 238000010195 expression analysis Methods 0.000 description 1
- 238000003780 insertion Methods 0.000 description 1
- 230000037431 insertion Effects 0.000 description 1
- 210000001503 joint Anatomy 0.000 description 1
- 238000004806 packaging method and process Methods 0.000 description 1
- 238000005192 partition Methods 0.000 description 1
- 230000008569 process Effects 0.000 description 1
- 238000012216 screening Methods 0.000 description 1
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F8/00—Arrangements for software engineering
- G06F8/40—Transformation of program code
- G06F8/41—Compilation
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F8/00—Arrangements for software engineering
- G06F8/30—Creation or generation of source code
- G06F8/31—Programming languages or programming paradigms
Landscapes
- Engineering & Computer Science (AREA)
- Software Systems (AREA)
- General Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Physics & Mathematics (AREA)
- General Physics & Mathematics (AREA)
- Computing Systems (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention discloses a cross-data-source SQL compiling device and a method. The invention encapsulates the query condition through JSON, analyzes the condition and forms SQL to submit to PRESTODB, and can realize the real-time query function of various scenes (conditions).
Description
Technical Field
The invention relates to the technical field of big data, in particular to a cross-data-source SQL compiling device and method based on PRESTODB, which are particularly suitable for condition scenes which are combined randomly and contain user behaviors and user portrayal.
Background
The PRESTODB is a distributed real-time computing engine, is mainly applied to the real-time computing analysis requirement in the field of big data, has excellent capabilities in the aspects of ETL, data analysis, real-time query and the like, simultaneously supports multi-data source joint query and flexible self-defined connector functions, can realize the butt joint of different data sources through the self-defined connector, and is widely applied to big data OLAP analysis due to the advantages. At present, a PRESTODB-based SQL compiling device and method are still lacking.
Disclosure of Invention
Aiming at the defects of the prior art, the invention aims to provide a cross-data-source SQL compiling device and method, which can realize the real-time query function of various scenes (conditions) by packaging query conditions through JSON, analyzing the conditions, forming SQL and submitting the SQL to PRESTODB and utilizing the advantages of the PRESTODB.
An apparatus for cross-data source SQL compilation, comprising:
JSON inquiry condition generating module: query conditions for generating a JSON structure;
SQL compiling tool: analyzing the query condition generated by the JSON query condition generation module to form query SQL;
the SQL submitting module: the system is used for submitting the query SQL to the PRESTODB cluster, and the PRESTODB executes the query SQL to obtain a query result and encapsulates the query result into an object to be returned.
Further, the JSON query condition generating module generates a user group expression of a JSON structure, and the query condition is packaged in the user group expression;
the user group expression comprises fields of group _ id, entity _ english _ name, type, prev _ group _ expression and group _ expression, wherein:
the group _ id is used for recording the id of a target user group to be queried;
the entity _ english _ name is used for recording the name of the entity and acquiring the name of the table to be inquired through the name of the entity;
the type is used for recording the type of a target user group to be inquired;
the group _ expression is used for encapsulating a query condition obtained by freely combining three fields of group _ id, entity _ english _ name and type; the SQL compiling tool analyzes the group _ expression to obtain a similar SQL;
the prev _ group _ expression is used for recording a user group expression of the preposed user group; the preposed user group is a user group which is depended by a target user group to be queried currently, and a user group expression of the preposed user group is of a JSON structure and also comprises fields of group _ id, entry _ english _ name, type, prev _ group _ expression and group _ expression; the SQL compiling tool analyzes the group _ expression of the preposed user group to obtain the preposed user group query SQL;
the cross-data-source SQL compiling device further comprises a final query SQL generating module, wherein the final query SQL generating module is used for acquiring an intersection between a similar SQL obtained by an SQL compiling tool and a front-end user group query SQL to obtain a final query SQL; the query SQL submitted by the SQL submitting module is the final query SQL obtained by the final query SQL generating module.
Furthermore, the types of the user groups are 9 types in total, and are all user groups all, common user groups common, special interest user groups special, report user groups report, external user groups input, funnel user groups channel, funnel flow user groups channel _ lost, decision tree user groups decision, and life journey user groups life.
Furthermore, in the user group expression of the pre-user group, the types of the user groups described in the type are all user groups, and do not depend on other user groups, the content of the prev _ group _ expression field is empty, and the group _ expression field includes the authority configuration information of the pre-user group.
Further, the cross-data-source SQL compiling device further includes a request cache implementation module: the system comprises a query SQL submitting module, a PRESTODB cluster and a cache module, wherein the query SQL submitting module is used for submitting the finally generated query SQL to the PRESTODB cluster, realizing request caching, namely temporarily storing all query requests submitted to the PRESTODB cluster, and deleting corresponding query requests after query results are returned; every time a new query request occurs, whether the query request exists or not is judged, and if the query request exists, the existing query request is waited for returning data.
The cross-data-source SQL compiling method realized by using the cross-data-source SQL compiling device of any claim, which comprises the following steps:
s1, generating a query condition of the JSON structure by using a JSON query condition generation module;
s2, analyzing the query conditions of the JSON structure obtained in the step S1 by an SQL compiling tool to form query SQL;
s3, the SQL submitting module submits the query SQL to the PRESTODB cluster for query, and the query result is returned after the PRESTODB cluster executes.
Further, in step S1, the JSON query condition generating module generates a user group expression of a JSON structure, and the query condition is encapsulated in the user group expression;
the user group expression comprises fields of group _ id, entity _ english _ name, type, prev _ group _ expression and group _ expression, wherein:
the group _ id is used for recording the id of a target user group to be queried;
the entity _ english _ name is used for recording the name of the entity, and the name of the table to be inquired can be obtained through the name of the entity;
the type is used for recording the type of a target user group to be inquired;
the group _ expression is used for encapsulating a query condition obtained by freely combining three fields of group _ id, entity _ english _ name and type;
the prev _ group _ expression is used for recording a user group expression of the preposed user group; the preposed user group is a user group which is depended by a target user group to be queried currently, and a user group expression of the preposed user group is of a JSON structure and also comprises fields of group _ id, entry _ english _ name, type, prev _ group _ expression and group _ expression;
in step S2, the SQL compiling tool parses the group _ expression to obtain a similar SQL, and parses the group _ expression of the pre-user group to obtain a pre-user group query SQL; then the final query SQL generating module acquires an intersection of the similar SQL obtained by the SQL compiling tool and the front user group query SQL to obtain the final query SQL;
in step S3, the SQL submitting module submits the final query SQL obtained by the final query SQL generating module to the PRESTODB cluster for querying.
Further, in step S3, after the SQL submission module submits the query SQL to the PRESTODB cluster, a request cache implementation module implements request cache, that is, temporarily stores all query requests submitted to the PRESTODB cluster, and deletes the corresponding query request when the query result is returned; every time a new query request occurs, whether the query request exists or not is judged, and if the query request exists, the existing query request is waited for returning data.
The invention has the beneficial effects that:
1. JSON is a very simple and efficient data format, the device and the method of the invention package the query condition through JSON, analyze the condition and form query SQL and submit to the PRESTODB cluster, and can realize the real-time query function of various scenes (conditions) by utilizing the advantages of the PRESTODB.
2. The invention encapsulates the query conditions through the user group expression of the JSON structure, can contain various query condition combinations, and meets more query requirements.
Drawings
Fig. 1 is a schematic structural diagram of an apparatus according to an embodiment of the present invention.
Detailed Description
The present invention will be further described with reference to the accompanying drawings, and it should be noted that the present embodiment is based on the technical solution, and the detailed implementation and the specific operation process are provided, but the protection scope of the present invention is not limited to the present embodiment.
As shown in fig. 1, the SQL compiling apparatus across data sources provided in this embodiment includes:
JSON inquiry condition generating module: query conditions for generating a JSON structure;
in this embodiment, the JSON generating module specifically generates a user group expression, and the query condition is encapsulated in the user group expression.
The user group expression comprises fields of group _ id, entity _ english _ name, type, prev _ group _ expression and group _ expression, wherein:
the group _ id is used for recording the id of a target user group to be queried, and whether the corresponding user group has a cache or not can be queried through the id of the user group;
the entity _ english _ name is used for recording the name of the entity, and the name of the table to be inquired can be obtained through the name of the entity;
the type is used for recording the type of a target user group to be inquired; in this embodiment, the types of the user groups are 9 types in total, and are respectively all user groups all, common user groups common, special attention user groups special, report user groups report, external user groups input, funnel user groups channel, funnel flow user groups channel _ lost, decision tree user groups decision, and life journey user groups life;
the group _ expression is used for encapsulating the condition of the current free combination; analyzing the group _ expression through an SQL compiling tool to obtain SQL-like;
the prev _ group _ expression is used for recording an expression of a preposed user group, wherein the preposed user group is a user group which is depended by the current user group, is of a JSON structure, and also comprises fields of group _ id, entry _ english _ name, type, prev _ group _ expression and group _ expression; in the expression of the preposed user group, the types of the user groups recorded in the type are all user groups and do not depend on other user groups, the content of a prev _ group _ expression field is empty, the group _ expression field contains authority configuration information of the preposed user group, and the group _ expression of the preposed user group is analyzed through an SQL compiling tool, so that the query SQL of the preposed user group can be obtained; the structure of SQL may be:
SELECT user id FROM database WHERE client code IN ('X') means that all users who have accessed a client with client code X are queried FROM the database.
For example:
SELECT super_id FROM kudu.default.action WHERE appkey IN(‘7927cb5436fd14263a121bfd907127b1’)；
indicating that all people who visited 7927cb5436fd14263a121bfd907127b1 (client code, which may refer to APP or website, such as hundredths, etc.) clients are queried.
And a final query SQL generation module: and the method is used for obtaining the intersection of the SQL class obtained by group _ expression analysis of the expression of the user group and the SQL query of the front user group to obtain the final query SQL.
In this embodiment, the final query SQL is as follows:
SELECT prev. super _ id FROM (SELECT super _ id FROM kudu. default. action worker overlay IN (' 7927cb5436fd14263a121bfd907127b1 ')) prev JOIN (SELECT t. super _ id FROM hi. default. tag. wheel IN (' scholar ', ' master ', ' doctor ')) t JOIN (SELECT super _ id FROM hi. default. action worker l _ date [ ' 2018-08-26 ' AND _ date > ' 2018-02-28 ' AND overlay 7927cb5436fd14263a121b bfd907127b1 ' p _ page _ text _ 0. sub _ overlay _ id. see also > 0. sub _ task _ overlay _ n _ id. master;
note that: from all scholars, masters, and doctors, a person who viewed a stock in 26.8.2018 (main _ page _ smartcheck indicates the number of times the stock was viewed) was queried.
SQL compiling tool: and the SQL server is used for analyzing the query conditions to form query SQL.
In this embodiment, the structure of the SQL compiling tool includes:
colummdata class: the metadata information of each table is packaged, and the SQL composition of each table is judged through the metadata management.
sqlAssembler class: the SQL key words are the basic class of SQL, the combination logic of each SQL key word is realized by self-definition, and each key word is combined into a complete SQL through the class.
sqlAssembleUtil class: the system is a supplementary tool class of the SqlAssembler and provides assistance for the SqlAssembler.
sqlConstants class: constant pool providing all SQL keywords
sqlIdentifier class: the type of complex SQL generation logic for realizing the self-definition generates SQL by analyzing JSON, and is mainly used for SQL composition of the self-definition report.
SqlSuppliers class: the function of the method is similar to that of the SqlIdentifier, and mainly realizes some customized condition analysis.
The SqlAssembler core class is described below:
1. SELECT key specification:
SELECT key, representing a search. All information that is desired to be queried needs to be called using SELECT.
The implementation code is as follows:
2. and realizing keywords such as COUNT, OVER, ROW _ NUMBER ().
The key words of COUNT, OVER and ROW _ NUMBER () use the character splicing function carried by JAVA to splice different parameters with COUNT, OVER and ROW _ NUMBER into legal SQL sentences.
COUNT splices columns (content to be retrieved) into a format of "COUNT (discrete column)"
ROW _ NUMBER AND OVER concatenates columns (contents to be retrieved) into a format of "ROW _ NUMBER () OVER (partition BY columns)"
The implementation code is as follows:
3. implementation of WHERE keywords
The WHERE key provides a screening function. For example, when a person whose age (age) is equal to 15 is to be found, a WHERE keyword is used, that is, the WHERE keyword combines "WHERE" and "age 15" into "WHERE age 15".
The implementation code is as follows:
4. implementation of AND keywords
The AND key provides the function of "AND". Mainly used after the WHERE key.
For example, when it is desired to find that age (age) is equal to 15 years old AND gender (sex) is female, two conditions need to be combined together using AND key, that is, SQL "age 15AND sex female" is implemented using AND (sex female) "
The implementation code is as follows:
5. using a way to illustrate, the code is as follows:
example (c): firstly, a SqlAssembler object needs to be created, AND then a SELECT keyword, an FROM keyword, a WHERE keyword, an AND keyword AND the like need to be spliced with reference to the grammar of SQL. If SQL is needed, only SQL in the SqlAssembler object needs to be output.
The SQL submitting module: the query SQL server is used for submitting the finally generated query SQL to a PRESTODB cluster, and the query result is returned after the PRESTODB cluster executes the query;
a request cache implementation module: the method is used for realizing request caching after the SQL submitting module submits the finally generated query SQL to the PRESTODB, namely temporarily storing all the query requests SQL submitted to the PRESTODB, and deleting the query requests after the query result is returned. Every time a new query request occurs, judging whether the query request exists, and if so, waiting for the existing request to return data; the structure of the request cache implementation module comprises:
QueryTask class: the SQL of the query, the request time createTime, the query result and the like are packaged, and the HashCode and equials methods are rewritten by the SQL.
QueryBufferThread type: timing thread class, executing once per hour task, deleting overdue request, failure request and illegal request, etc.
QueryBuffer type: the request cache core logic implements the class. The method comprises the logic of request insertion, result updating, result obtaining and the like.
The code is realized as follows:
in this embodiment, when the user group type described by the type of the user group expression is common, in this embodiment, the group _ expression structure is as follows:
in json, whose meaning is group _ expression, two conditions are described, i.e., a person who has viewed stocks between 28/2/2018 and 26/8/2018; the second is a scholar, master or doctor.
The SQL-like expression can be obtained by analyzing the group _ expression of the user group expression through an SQL compiling tool, wherein the SQL-like expression comprises the following steps:
SELECT t.super _ id FROM (SELECT super _ id FROM live, default, tag where IN) (' student ', ' master ', ' doctor ')) t JOIN (SELECT super _ id FROM live, action where l _ date [ -2018-08-26 ' AND l _ date > -2018-02-28 ' AND appkey ═ 7927cb5436fd14263a121bfd907127b1 ' AND main _ page _ smarttstock >0) ac ON t.super _ id ═ ac.;
the meaning of the SQL-like is that people who view a certain stock between 2018, 2 and 28 months and 2018, 8 and 26 months are inquired from all scholars, major and doctors (main _ page _ smartscock represents the number of times to view the stock).
In the present embodiment, when the user group type of the user group expression is special attention to the user group special, the group _ expression is represented by uuid (a randomly non-repeating character string)Only, the user group with special interest is inquired through group _ id; the following examples are presented:
the SQL obtained by analyzing the group _ expression through the SQL compiling tool is SQL
SELECT super_id FROM kudu.default.group_special WHERE group_id＝’c24247801dcb64fb2829fb6e94109ecd’；
The above-mentioned SQL can realize that all persons in the user group of special interest (the output is super _ id of the user) with id (c24247801dcb64fb2829fb6e94109 ecc) are inquired from the kudu data.
When the user group type of the user group expression is the external user group input, the data of the file _ id is encapsulated in the group _ expression in the user group expression, as follows:
wherein the file _ id represents the id of the file, i.e. the circumscribed user group is stored in 4 files (their id is 1, 2, 4, 6).
The SQL obtained by parsing the group _ expression is:
SELECT super_id FROM kudu.default.group_input WHERE file_id in(’1’，’2’，’4’，’6’)；
all persons with file ids 1, 2, 4 and 6 can be queried through the SQL.
The external user group is a group matched with some groups obtained by importing the id of the group.
The report user group refers to a group of people screened out by the user-defined combined behavior event condition, and when the user group type of the user group expression is the report user group report, the group _ expression is shown as follows.
In json of the above group _ expression, all the persons who visited the personal detail page (selected _ stk _ vw _ stk _ detail) on the 9 th day between 7/month 1 in 2018 and 7/month 31 in 2018 and whose page code (stk _ code) is equal to 245 are described.
The SQL-like data obtained by analyzing the SQL compiling tool is as follows:
SELECT dimensionSql.super_id FROM(SELECT profile.super_id FROM(SELECTgid FROM hive.deep_creator.mas_events WHERE 1＝1and l_date＝'2018-07-10'andappkey＝'7927cb5436fd14263a121bfd907127b1'AND actionname＝'selected_stk_vw_stk_detail'AND(regexp_like(stk_code,'245')))dimension INNER JOIN(SELECT gid,super_id FROM kudu.default.mas_userprofile WHERE 1＝1and appkey＝'7927cb5436fd14263a121bfd907127b1'and super_id is not null GROUP BY gid,super_id)profileON dimension.gid＝profile.gid GROUP BY profile.super_id)dimensionSql INNERJOIN(SELECT profile0.super_id FROM(SELECT gid FROM hive.deep_creator.mas_events WHERE 1＝1AND l_date<＝'2018-07-31'AND l_date>＝'2018-07-01'and appkey＝'7927cb5436fd14263a121bfd907127b1'AND actionname＝'selected_stk_vw_stk_detail'AND(regexp_like(stk_code,'245')))event0 INNER JOIN(SELECT gid,super_idFROM kudu.default.mas_userprofile WHERE 1＝1and appkey＝'7927cb5436fd14263a121bfd907127b1'and super_id is not null GROUP BY gid,super_id)profile0ONevent0.gid＝profile0.gid GROUP BY profile0.super_id HAVING count(1)>0)actionSql ON dimensionSql.super_id＝actionSql.super_id；
the above-described SQL-like query can inquire that, of all the persons who accessed the personal detail page (selected _ stk _ vw _ stk _ detail) between 7/1/2018 and 31/2018/7/245 and whose page code (stk _ code) is equal to 245, the person whose personal detail page number is greater than 0 was accessed on 10/2018/7.
In addition, user groups such as a funnel user group, a funnel outflow user group, a decision tree user group, a life journey user group and the like are also included, and the JSON condition packaged by the user groups can be analyzed into corresponding SQL through an SQL compiling tool.
In this embodiment, the JDBC implementation code for the query request submission PRESTODB is as follows:
the meaning of the code is that firstly, a Connection (Connection) is created in a JDBC mode of the PRESTODB, SQL is submitted to the PRESTODB cluster through the Connection, and the PRESTODB executes the SQL to obtain a query result and encapsulates the query result into an object to be returned.
The cross-data-source SQL compiling method realized by using the cross-data-source SQL compiling device of any claim, which comprises the following steps:
s1, generating a query condition of the JSON structure by using a JSON query condition generation module;
s2, analyzing the query conditions of the JSON structure obtained in the step S1 by an SQL compiling tool to form query SQL;
s3, the SQL submitting module submits the query SQL to the PRESTODB cluster for query, and the query result is returned after the PRESTODB cluster executes.
Further, in step S1, the JSON query condition generating module generates a user group expression of a JSON structure, and the query condition is encapsulated in the user group expression;
the user group expression comprises fields of group _ id, entity _ english _ name, type, prev _ group _ expression and group _ expression, wherein:
the group _ id is used for recording the id of a target user group to be queried;
the entity _ english _ name is used for recording the name of the entity, and the name of the table to be inquired can be obtained through the name of the entity;
the type is used for recording the type of a target user group to be inquired;
the group _ expression is used for encapsulating a query condition obtained by freely combining three fields of group _ id, entity _ english _ name and type;
the prev _ group _ expression is used for recording a user group expression of the preposed user group; the preposed user group is a user group which is depended by a target user group to be queried currently, and a user group expression of the preposed user group is of a JSON structure and also comprises fields of group _ id, entry _ english _ name, type, prev _ group _ expression and group _ expression;
in step S2, the SQL compiling tool parses the group _ expression to obtain a similar SQL, and parses the group _ expression of the pre-user group to obtain a pre-user group query SQL; then the final query SQL generating module acquires an intersection of the similar SQL obtained by the SQL compiling tool and the front user group query SQL to obtain the final query SQL;
in step S3, the SQL submitting module submits the final query SQL obtained by the final query SQL generating module to the PRESTODB cluster for querying.
Further, in step S3, after the SQL submission module submits the query SQL to the PRESTODB cluster, a request cache implementation module implements request cache, that is, temporarily stores all query requests submitted to the PRESTODB cluster, and deletes the corresponding query request when the query result is returned; every time a new query request occurs, whether the query request exists or not is judged, and if the query request exists, the existing query request is waited for returning data.
In this embodiment, in step S3, a specific method for submitting the finally generated SQL to PRESTODB for query includes:
firstly, a Connection (Connection) is created in a JDBC mode of the PRESTODB, SQL is submitted to the PRESTODB cluster through the Connection, and the PRESTODB executes the SQL to obtain a query result and encapsulates the query result into an object to be returned.
In the present embodiment, databases connected by PRESTODB include HIVE, MYSQL, KUDU, and the like.
In the actual big data analysis service, the analysis of real-time data is inevitable, and the patent stores the real-time (current day) data through a KUDU database, namely all current day data are inquired from the KUDU.
The historical (yesterday ago) data is daily transferred from the KUDU to the HIVE data repository by script, where a large amount of historical data is stored, queried by PRESTODB.
MYSQL stores some system configurations and cached data, and the data can be used in any query scene, and the query requesting multiple data sources is realized through the design of the patent.
Various corresponding changes and modifications can be made by those skilled in the art based on the above technical solutions and concepts, and all such changes and modifications should be included in the protection scope of the present invention.
Claims (7)
1. An apparatus for cross-data source SQL compilation, comprising:
JSON inquiry condition generating module: query conditions for generating a JSON structure;
SQL compiling tool: analyzing the query condition generated by the JSON query condition generation module to form query SQL;
the SQL submitting module: the system comprises a PRESTODB cluster, a query SQL server and a database, wherein the PRESTODB cluster is used for submitting the query SQL to the PRESTODB cluster, and the PRESTODB executes the query SQL to obtain a query result which is packaged into an object to be returned;
wherein,
the JSON query condition generating module generates a user group expression of a JSON structure, and the query condition is packaged in the user group expression;
the user group expression comprises fields of group _ id, entity _ english _ name, type, prev _ group _ expression and group _ expression, wherein:
the group _ id is used for recording the id of a target user group to be queried;
the entity _ english _ name is used for recording the name of the entity and acquiring the name of the table to be inquired through the name of the entity;
the type is used for recording the type of a target user group to be inquired;
the group _ expression is used for encapsulating a query condition obtained by freely combining three fields of group _ id, entity _ english _ name and type; the SQL compiling tool analyzes the group _ expression to obtain a similar SQL;
the prev _ group _ expression is used for recording a user group expression of the preposed user group; the preposed user group is a user group which is depended by a target user group to be queried currently, and a user group expression of the preposed user group is of a JSON structure and also comprises fields of group _ id, entry _ english _ name, type, prev _ group _ expression and group _ expression; the SQL compiling tool analyzes the group _ expression of the preposed user group to obtain the preposed user group query SQL;
the cross-data-source SQL compiling device further comprises a final query SQL generating module, wherein the final query SQL generating module is used for acquiring an intersection between a similar SQL obtained by an SQL compiling tool and a front-end user group query SQL to obtain a final query SQL; the query SQL submitted by the SQL submitting module is the final query SQL obtained by the final query SQL generating module.
2. The cross-data-source SQL compiling device according to claim 1, wherein the types of the user groups are 9 big types in total, and are all user groups, common user groups, special interest user groups, report user groups, external user groups input, funnel user groups, funnel-flow user groups, decision tree user groups, life trip user groups, respectively.
3. The SQL compiling apparatus across data sources according to claim 1, wherein in the user group expression of the front user group, the types of the user group described in the type are all user groups, and do not depend on other user groups, the content of the prev _ group _ expression field is empty, and the group _ expression field contains the permission configuration information of the front user group.
4. The cross-data-source SQL compiling device according to claim 1, further comprising a request cache implementation module: the system comprises a query SQL submitting module, a PRESTODB cluster and a cache module, wherein the query SQL submitting module is used for submitting the finally generated query SQL to the PRESTODB cluster, realizing request caching, namely temporarily storing all query requests submitted to the PRESTODB cluster, and deleting corresponding query requests after query results are returned; every time a new query request occurs, whether the query request exists or not is judged, and if the query request exists, the existing query request is waited for returning data.
5. The cross-data-source SQL compiling method realized by using the cross-data-source SQL compiling device according to one of claims 1 to 4, characterized by comprising the following steps:
s1, generating a query condition of the JSON structure by using a JSON query condition generation module;
s2, analyzing the query conditions of the JSON structure obtained in the step S1 by an SQL compiling tool to form query SQL;
s3, the SQL submitting module submits the query SQL to the PRESTODB cluster for query, and the query result is returned after the PRESTODB cluster executes.
6. The method according to claim 5, wherein in step S1, the JSON query condition generating module generates a user group expression of JSON structure, and the query condition is encapsulated in the user group expression;
the user group expression comprises fields of group _ id, entity _ english _ name, type, prev _ group _ expression and group _ expression, wherein:
the group _ id is used for recording the id of a target user group to be queried;
the entity _ english _ name is used for recording the name of the entity, and the name of the table to be inquired can be obtained through the name of the entity;
the type is used for recording the type of a target user group to be inquired;
the group _ expression is used for encapsulating a query condition obtained by freely combining three fields of group _ id, entity _ english _ name and type;
the prev _ group _ expression is used for recording a user group expression of the preposed user group; the preposed user group is a user group which is depended by a target user group to be queried currently, and a user group expression of the preposed user group is of a JSON structure and also comprises fields of group _ id, entry _ english _ name, type, prev _ group _ expression and group _ expression;
in step S2, the SQL compiling tool parses the group _ expression to obtain a similar SQL, and parses the group _ expression of the pre-user group to obtain a pre-user group query SQL; then the final query SQL generating module acquires an intersection of the similar SQL obtained by the SQL compiling tool and the front user group query SQL to obtain the final query SQL;
in step S3, the SQL submitting module submits the final query SQL obtained by the final query SQL generating module to the PRESTODB cluster for querying.
7. The method according to claim 5, wherein in step S3, after the SQL submission module submits the query SQL to the PRESTODB cluster, the request cache implementation module implements request cache, that is, temporarily stores all query requests submitted to the PRESTODB cluster, and deletes the corresponding query request when the query result is returned; every time a new query request occurs, whether the query request exists or not is judged, and if the query request exists, the existing query request is waited for returning data.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201811294111.2A CN109408580B (en) | 2018-10-31 | 2018-10-31 | Cross-data-source SQL compiling device and method |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201811294111.2A CN109408580B (en) | 2018-10-31 | 2018-10-31 | Cross-data-source SQL compiling device and method |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN109408580A CN109408580A (en) | 2019-03-01 |
| CN109408580B true CN109408580B (en) | 2020-10-20 |
Family
ID=65471143
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201811294111.2A Active CN109408580B (en) | 2018-10-31 | 2018-10-31 | Cross-data-source SQL compiling device and method |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN109408580B (en) |
Families Citing this family (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN110399125A (en) * | 2019-07-25 | 2019-11-01 | 南京数睿数据科技有限公司 | A kind of dynamic query method of the various dimensions many condition based on Java technology |
| CN110597847A (en) * | 2019-09-19 | 2019-12-20 | 深圳前海微众银行股份有限公司 | SQL statement automatic generation method, device, equipment and readable storage medium |
| CN111708750A (en) * | 2019-12-27 | 2020-09-25 | 山东鲁能软件技术有限公司 | Big data platform based storage adaptation method, system, equipment and readable storage medium |
| CN113407812A (en) * | 2021-07-16 | 2021-09-17 | 首约科技(北京)有限公司 | Cross-data-source data pushing method and device |
Family Cites Families (6)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN105404665B (en) * | 2015-11-12 | 2019-04-26 | 南威软件股份有限公司 | A kind of inquiry management system of JSON formatted data |
| CN105426467B (en) * | 2015-11-16 | 2018-11-20 | 北京京东尚科信息技术有限公司 | A kind of SQL query method and system for Presto |
| CN105550284B (en) * | 2015-12-10 | 2020-03-27 | 北京京东尚科信息技术有限公司 | Method and device for mixed use of memory and temporary table space in Presto computing node |
| US10614066B2 (en) * | 2016-09-01 | 2020-04-07 | Amazon Technologies, Inc. | Selecting resource configurations for query execution |
| CN107133267B (en) * | 2017-04-01 | 2021-01-26 | 北京京东尚科信息技术有限公司 | Method and device for querying elastic search cluster, electronic equipment and readable storage medium |
| CN107729428A (en) * | 2017-09-28 | 2018-02-23 | 南威软件股份有限公司 | A kind of SQL query method based on Presto and Elasticsearch |
-
2018
- 2018-10-31 CN CN201811294111.2A patent/CN109408580B/en active Active
Also Published As
| Publication number | Publication date |
|---|---|
| CN109408580A (en) | 2019-03-01 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN109408580B (en) | Cross-data-source SQL compiling device and method | |
| US10275540B2 (en) | Methods and apparatus for querying a relational data store using schema-less queries | |
| Szekely et al. | Building and using a knowledge graph to combat human trafficking | |
| US8396894B2 (en) | Integrated repository of structured and unstructured data | |
| US9009201B2 (en) | Extended database search | |
| US8832147B2 (en) | Relational meta-model and associated domain context-based knowledge inference engine for knowledge discovery and organization | |
| US9165061B2 (en) | Identifying information related to a particular entity from electronic sources, using dimensional reduction and quantum clustering | |
| US20180060410A1 (en) | System and method of applying globally unique identifiers to relate distributed data sources | |
| US11222013B2 (en) | Custom named entities and tags for natural language search query processing | |
| US8086592B2 (en) | Apparatus and method for associating unstructured text with structured data | |
| US11860914B1 (en) | Natural language database generation and query system | |
| JP2022033825A (en) | Generating, accessing and displaying lineage metadata | |
| WO2017100544A1 (en) | Query processor | |
| US20120102391A1 (en) | Identifiers for web font templates | |
| CN111046041B (en) | Data processing method and device, storage medium and processor | |
| JP2018067279A (en) | Device, program, and method for recognizing data property | |
| US20200372014A1 (en) | Full-stack system and method for blockchain analytics | |
| US20110145005A1 (en) | Method and system for automatic business content discovery | |
| CN107122486B (en) | Multi-element big data fusion method and system supporting BLOB | |
| Chortaras et al. | D2RML: Integrating Heterogeneous Data and Web Services into Custom RDF Graphs. | |
| CN116795859A (en) | Data analysis method, device, computer equipment and storage medium | |
| Abid et al. | Towards a smart city ontology | |
| Nayrolles et al. | BUMPER: a tool for coping with natural language searches of millions of bugs and fixes | |
| US9069816B2 (en) | Distributed multi-step abstract queries | |
| US12067366B1 (en) | Generative text model query system |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant | ||
| CP03 | Change of name, title or address | ||
| CP03 | Change of name, title or address |
Address after: 100081 No.101, 1st floor, building 14, 27 Jiancai Chengzhong Road, Haidian District, Beijing Patentee after: Beijing PERCENT Technology Group Co.,Ltd. Address before: 100081 16 / F, block a, Beichen Century Center, building 2, courtyard 8, Beichen West Road, Chaoyang District, Beijing Patentee before: BEIJING BAIFENDIAN INFORMATION SCIENCE & TECHNOLOGY Co.,Ltd. |