Spark SQL processing method, device, storage medium and system

Abstract

The present application provides a Spark SQL processing method, device, storage medium and system. The method uses a Scala script to perform logic extraction and splitting processing on the Spark SQL development script in sequence to obtain multiple sub-logics; and divides each sub-logic to obtain at least multiple extraction modules SELECT, multiple source modules FROM and multiple filtering modules WHERE, thereby achieving the purpose of refining the Spark SQL development script. Finally, by loading all the extraction modules SELECT, source modules FROM and filtering modules WHERE, multiple loading results are obtained, and all the loading results are stored in the corresponding positions of the system code table to obtain the final system code table, thereby solving the problem that the complex SQL of the existing solution is time-consuming due to the complex logic, and is prone to cause job failure.

Description

Technical Field

[0001] The present application relates to the field of SQL processing technology, and more specifically, to a Spark SQL processing method, device, storage medium, and system. Background Art

[0002] With the continuous accumulation of bank data and the continuous improvement of information technology, big data applications are becoming more and more extensive. Fast general-purpose computing engines such as Spark, which are designed for massive data processing, have emerged. Spark SQL can simplify the development of its native RDD, but there are still common problems such as coarse parameter configuration granularity, complex logic that takes a long time, and even job failure.

[0003] That is, the existing solution has a complex SQL with complex logic, which takes a long time and easily causes the job to fail. Summary of the Invention

[0004] The main purpose of this application is to provide a Spark SQL processing method, device, storage medium and system to at least solve the problem that the existing solution has complex SQL with complex logic, which takes a long time and easily causes job failure.

[0005] In order to achieve the above-mentioned purpose, according to one aspect of the present application, a Spark SQL processing method is provided, which includes: obtaining a Spark SQL development script for characterizing the content of a data table library; using a Scala script to perform logic extraction processing and splitting processing on the Spark SQL development script in sequence to obtain multiple sub-logics; and dividing each of the sub-logics to obtain at least multiple extraction modules SELECT, multiple source modules FROM and multiple filtering modules WHERE; loading all of the extraction modules SELECT, the source modules FROM and the filtering modules WHERE to obtain multiple loading results, and storing all of the loading results in corresponding positions of the system code table to obtain a final system code table.

[0006] Optionally, a Scala script is used to perform logic extraction processing and splitting processing on the Spark SQL development script in sequence to obtain multiple sub-logics, including: using the Scala script to perform logic extraction processing on the Spark SQL development script to obtain the SQL logic as a whole; using the Scala script to split the SQL logic as a whole to obtain multiple sub-logics.

[0007] Optionally, after storing the loading result in a corresponding position of the system code table to obtain a final system code table, the method further includes: if there is a warning mark in the final system code table, storing all contents of the final system code table in an entity table, the warning mark being used to characterize the importance of the contents of the final system code table; if there is no warning mark in the final system code table, storing all contents of the final system code table in the entity table or storing all contents of the final system code table in a temporary view according to the data volume of the final system code table.

[0008] Optionally, based on the data volume of the final system code table, all contents of the final system code table are stored in the entity table, or all contents of the final system code table are stored in a temporary view, including: when the data volume of the final system code table is greater than or equal to a data volume threshold, based on the content duplication of the final system code table, all contents of the final system code table are stored in the entity table, or all contents of the final system code table are stored in the temporary view; when the data volume of the final system code table is less than the data volume threshold, all contents of the final system code table are stored in the temporary view.

[0009] Optionally, based on the content duplication of the final system code table, all contents of the final system code table are stored in the entity table, or all contents of the final system code table are stored in the temporary view, including: if the content duplication of the final system code table is greater than or equal to a duplication threshold, storing all contents of the final system code table in the temporary view; if the content duplication of the final system code table is less than the duplication threshold, storing all contents of the final system code table in the entity table.

[0010] Optionally, each of the sub-logics is divided and processed to obtain at least multiple extraction modules SELECT, multiple source modules FROM and multiple filtering modules WHERE, including: each of the sub-logics is divided and processed to obtain multiple extraction modules SELECT, multiple source modules FROM, multiple filtering modules WHERE, multiple grouping modules GROUP and multiple association modules JOIN, and the association module JOIN is two of the sub-logics that have an association relationship among all the sub-logics.

[0011] Optionally, after obtaining multiple extraction modules SELECT, multiple source modules FROM, multiple screening modules WHERE, multiple grouping modules GROUP and multiple association modules JOIN, the method further includes: when the type of the association module JOIN is left association, adding a master table flag to the back end of the front end position sub-logic of the association module JOIN, and adding a slave table flag to the back end of the back end position sub-logic of the association module JOIN, and the back end position sub-logic is located at the back end of the front end position sub-logic; when the type of the association module JOIN is right association, adding a slave table flag to the back end of the front end position sub-logic, and adding a master table flag to the back end of the back end position sub-logic; when the type of the association module JOIN is inner association, adding a master table flag to the back end of the front end position sub-logic and the back end position sub-logic respectively.

[0012] According to another aspect of the present application, a Spark SQL processing device is provided, the device comprising:

[0013] An acquisition unit, used to acquire a Spark SQL development script for representing the content of a data table library;

[0014] A first processing unit is configured to sequentially perform logic extraction and splitting processing on the Spark SQL development script using a Scala script to obtain a plurality of sub-logics; and to divide each of the sub-logics to obtain at least a plurality of extraction modules SELECT, a plurality of source modules FROM, and a plurality of screening modules WHERE;

[0015] The second processing unit is used to load all the extraction modules SELECT, the source modules FROM and the screening modules WHERE to obtain multiple loading results, and store all the loading results into corresponding positions of the system code table to obtain a final system code table.

[0016] According to another aspect of the present application, a computer-readable storage medium is provided, wherein the computer-readable storage medium includes a stored program, wherein when the program is executed, a device where the computer-readable storage medium is located is controlled to execute any one of the Spark SQL processing methods.

[0017] According to another aspect of the present application, a Spark SQL processing system is provided, comprising: one or more processors, a memory, and one or more programs, wherein the one or more programs are stored in the memory and configured to be executed by the one or more processors, and the one or more programs include a method for executing any one of the Spark SQL processing methods.

[0018] By applying the technical solution of the present application, the Spark SQL development script is sequentially subjected to logic extraction and splitting processing by using a Scala script to obtain multiple sub-logics; and each sub-logic is divided and processed to obtain at least multiple extraction modules SELECT, multiple source modules FROM, and multiple screening modules WHERE, respectively, thereby achieving the purpose of refining the Spark SQL development script, improving the processing speed of Spark SQL, and avoiding direct processing of more complex logic. Finally, multiple loading results are obtained by loading all the extraction modules SELECT, the source modules FROM, and the screening modules WHERE, and all the loading results are stored in the corresponding positions of the system code table to obtain the final system code table, so that the system code table can reflect the key information in the data table, thereby solving the problem that the complex SQL of the existing solution is time-consuming due to the complex logic, and is prone to job failure. BRIEF DESCRIPTION OF THE DRAWINGS

[0019] The drawings that constitute part of this application are used to provide a further understanding of this application. The illustrative embodiments of this application and their descriptions are used to explain this application and do not constitute an improper limitation on this application. In the drawings:

[0020] Figure 1 A schematic diagram of a Spark SQL processing method according to an embodiment of the present application is shown;

[0021] Figure 2 A schematic diagram of a process of another Spark SQL processing method provided according to an embodiment of the present application is shown;

[0022] Figure 3 The figure shows a structural block diagram of a Spark SQL processing device provided according to an embodiment of the present application. DETAILED DESCRIPTION

[0023] It should be noted that, in the absence of conflict, the embodiments and features of the embodiments in this application can be combined with each other. The present application will be described in detail below with reference to the accompanying drawings and in combination with the embodiments.

[0024] In order to enable those skilled in the art to better understand the present invention, the following will clearly and completely describe the technical solutions in the embodiments of the present invention in conjunction with the drawings in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments in the present invention, all other embodiments obtained by ordinary technicians in this field without making creative efforts should fall within the scope of protection of this application.

[0025] It should be noted that the terms "first", "second", etc. in the specification and claims of the present application and the above-mentioned drawings are used to distinguish similar objects and are not necessarily used to describe a specific order or sequential order. It should be understood that the data used in this way can be interchanged where appropriate, so that the embodiments of the present application described here. In addition, the terms "including" and "having" and any of their variations are intended to cover non-exclusive inclusions. For example, a process, method, system, product or device that includes a series of steps or units is not necessarily limited to those steps or units clearly listed, but may include other steps or units that are not clearly listed or inherent to these processes, methods, products or devices.

[0026] As introduced in the background technology, with the continuous accumulation of bank data and the continuous improvement of information technology, big data applications are becoming more and more extensive, and fast general-purpose computing engines such as Spark, which are designed for massive data processing, have emerged. Spark SQL can simplify the development of its native RDD, but there are still common problems such as coarse parameter configuration granularity, complex logic taking a long time, and even job failure. In order to solve the problem that the complex SQL of the existing solution takes a long time due to complex logic, and thus easily causes job failure, the embodiments of the present application provide a Spark SQL processing method, device, storage medium and system.

[0027] The technical solutions in the embodiments of the present invention will be described clearly and completely below with reference to the accompanying drawings in the embodiments of the present invention.

[0028] In this embodiment, a Spark SQL processing method is provided. It should be noted that the steps shown in the flowcharts of the accompanying drawings can be executed in a computer system, such as a set of computer-executable instructions. Moreover, although a logical order is shown in the flowcharts, in some cases, the steps shown or described can be executed in a different order than shown.

[0029] Figure 1 This is a flow chart of a Spark SQL processing method provided according to an embodiment of the present application. Figure 1 As shown, the method includes the following steps:

[0030] Step S101, obtaining a Spark SQL development script for representing the content of a data table library;

[0031] Step S102: Using a Scala script to sequentially perform logic extraction and splitting processing on the Spark SQL development script to obtain multiple sub-logics; and dividing each of the sub-logics to obtain at least multiple extraction modules SELECT, multiple source modules FROM, and multiple screening modules WHERE;

[0032] Specifically, the SQL script logic is obtained through Scala scripts to achieve the purpose of logic extraction and processing. Then, the overall SQL logic is split into multiple sub-logics according to the SQL specification. The overall SQL logic is relatively complex SQL logic. After splitting, the purpose of improving SQL processing speed is achieved. The sub-logic is split into a syntax tree that is simpler than the overall SQL logic. It is divided into multiple extraction modules SELECT, multiple source modules FROM, and multiple filtering modules WHERE. One sub-logic corresponds to multiple extraction modules SELECT, multiple source modules FROM, and multiple filtering modules WHERE;

[0033] SELECT is used to select the fields to be extracted, FROM is used to determine the source table of the data, WHERE is used to describe the data filtering conditions; GROUP is used to determine the basis for data grouping. The four modules can achieve different functions through different combinations.

[0034] In step S102, the Scala script is used to perform logic extraction processing and splitting processing on the above-mentioned Spark SQL development script in sequence to obtain multiple sub-logics, including: using the above-mentioned Scala script to perform logic extraction processing on the above-mentioned Spark SQL development script to obtain the SQL logic as a whole; using the above-mentioned Scala script to split the above-mentioned SQL logic as a whole to obtain multiple of the above-mentioned sub-logics.

[0035] Specifically, the SQL script logic is obtained through Scala scripts to achieve the purpose of logic extraction and processing. Then, the overall SQL logic is split into multiple sub-logics according to the SQL specification. The overall SQL logic is relatively complex SQL logic. After splitting, the purpose of improving SQL processing speed is achieved.

[0036] The step S102 of dividing each of the above sub-logics to obtain at least a plurality of extraction modules SELECT, a plurality of source modules FROM, and a plurality of screening modules WHERE includes: dividing each of the above sub-logics to obtain a plurality of extraction modules SELECT, a plurality of source modules FROM, a plurality of screening modules WHERE, a plurality of grouping modules GROUP, and a plurality of association modules JOIN, wherein the association module JOIN is a combination of two of the above sub-logics that have an association relationship among all the above sub-logics;

[0037] Specifically, the association module JOIN is used to indicate that there is an association between two sub-modules.

[0038] In the case where the type of the above-mentioned association module JOIN is left association, a master table flag is added to the back end of the front-end position sub-logic of the above-mentioned association module JOIN, and a slave table flag is added to the back end of the back-end position sub-logic of the above-mentioned association module JOIN, and the above-mentioned back-end position sub-logic is located at the back end of the above-mentioned front-end position sub-logic; in the case where the type of the above-mentioned association module JOIN is right association, a slave table flag is added to the back end of the above-mentioned front-end position sub-logic, and a master table flag is added to the back end of the above-mentioned back-end position sub-logic; in the case where the type of the above-mentioned association module JOIN is inner association, a master table flag is added to the back end of the above-mentioned front-end position sub-logic and the back end of the above-mentioned back-end position sub-logic respectively.

[0039] Specifically, it is convenient for subsequent staff to intuitively see the master table mark and the slave table representation, and convenient for subsequent staff to directly modify the corresponding master table or slave table.

[0040] Step S103 , loading all the above extraction modules SELECT, the above source modules FROM and the above screening modules WHERE to obtain multiple loading results, and storing all the above loading results into corresponding positions of the system code table to obtain a final system code table.

[0041] Load SELECT, FROM, and WHERE (marked as B), and concatenate the SQL statement "SELECT SUM(A.table name) FROM B WHERE A.table name = B.source table name AND B.restriction 1 AND B.restriction 2..." to query the system code table contents.

[0042] Specifically, restriction condition 1 may be customer type code IN ("retail", "enterprise"), and restriction condition 2 may be loan balance>0.

[0043] Load the WHERE module and JOIN module to obtain the association method (corresponding to the type of the JOIN module above).

[0044] Create a system code table (marked as A) to record the following information of the database table (taking the slice partition table as an example): table name, number of partitions (that is, the total number of data time partitions), number of rows of data in the latest partition, size of the latest partition file, high-frequency field name, high-frequency field duplication (that is, content duplication below), and whether there is a warning mark.

[0045] In the above steps, the Spark SQL development script is subjected to logic extraction and splitting processing in sequence by using a Scala script to obtain multiple sub-logics; and each of the above sub-logics is divided and processed to obtain at least multiple extraction modules SELECT, multiple source modules FROM and multiple screening modules WHERE respectively, thereby achieving the purpose of refining the Spark SQL development script, improving the processing speed of Spark SQL, and avoiding direct processing of more complex logic. Finally, multiple loading results are obtained by loading all of the above extraction modules SELECT, the above source modules FROM and the above screening modules WHERE, and all of the above loading results are stored in the corresponding positions of the system code table to obtain the final system code table, so that the system code table can reflect the key information in the data table, thereby solving the problem that the complex SQL of the existing solution is time-consuming due to the complex logic, and is prone to job failure.

[0046] Specifically, data is stored in entity tables or temporary views based on different data scenarios, combining the ease of SQL language development and the efficiency of Scala syntax operator processing. At the same time, separate Spark optimization parameters can be set for the split sub-logics, significantly improving job operation efficiency and providing strong support for data needs in various business scenarios.

[0047] After step S103, that is, after the loading result is stored in the corresponding position of the system code table to obtain the final system code table, the method further includes: if there is a warning mark in the final system code table, storing all the contents of the final system code table in the entity table, the warning mark is used to indicate the importance of the contents of the final system code table; if there is no warning mark in the final system code table, storing all the contents of the final system code table in the entity table according to the data volume of the final system code table, or storing all the contents of the final system code table in the temporary view.

[0048] Specifically, manual early warning indicators make up for the lack of high-priority adjustments to the business.

[0049] In one embodiment of the present application, according to the data volume of the final system code table, storing all contents of the final system code table into the entity table, or storing all contents of the final system code table into a temporary view, includes:

[0050] When the data volume of the final system code table is greater than or equal to the data volume threshold, storing all contents of the final system code table into the entity table or storing all contents of the final system code table into the temporary view according to the content duplication of the final system code table;

[0051] In one embodiment of the present application, storing all contents of the final system code table in the entity table or storing all contents of the final system code table in the temporary view according to the content duplication degree of the final system code table includes: if the content duplication degree of the final system code table is greater than or equal to a duplication degree threshold, storing all contents of the final system code table in the temporary view; if the content duplication degree of the final system code table is less than the duplication degree threshold, storing all contents of the final system code table in the entity table.

[0052] Specifically, by setting a repetition threshold, all contents of the final system code table with a higher degree of repetition are stored in the above-mentioned temporary view (a temporary view must be constructed before storage, and the temporary view is only used temporarily, not permanently saved). There is no need for the contents with a higher degree of repetition to occupy a large amount of space in the entity table, and all contents of the final system code table with a lower degree of repetition are stored in the above-mentioned entity table.

[0053] When the data amount of the final system code table is less than the data amount threshold, all contents of the final system code table are stored in the temporary view.

[0054] Specifically, in order to achieve the goal of putting all the contents of the above-mentioned final system code table with a data volume less than 1 million into the temporary view, the size of the data is used as the judgment standard, thereby improving the processing speed.

[0055] In order to enable those skilled in the art to more clearly understand the technical solution of the present application, the implementation process of the Spark SQL processing method of the present application will be described in detail below with reference to specific embodiments.

[0056] This embodiment relates to a specific Spark SQL processing method, such as Figure 2 As shown, the following steps are included:

[0057] Step S1: Obtaining a Spark SQL development script for representing the content of a data table library;

[0058] Step S2: Using a Scala script to perform logic extraction processing on the Spark SQL development script to obtain the SQL logic overall; using a Scala script to split the SQL logic overall to obtain multiple sub-logics;

[0059] Step S3: Load all the extraction modules SELECT, source modules FROM, and screening modules WHERE to obtain multiple loading results, and store all the loading results into corresponding positions in the system code table to obtain the final system code table;

[0060] Step S4: If there is a warning mark in the final system code table, all the contents of the final system code table are stored in the entity table. The warning mark is used to represent the importance of the contents of the final system code table. If there is no warning mark in the final system code table, step S5 is performed:

[0061] Step S5: If the data volume of the final system code table is greater than or equal to the data volume threshold, proceed to step S6; if the data volume of the final system code table is less than the data volume threshold, store all contents of the final system code table into a temporary view;

[0062] Step S6: If the content duplication of the final system code table is greater than or equal to the duplication threshold, all contents of the final system code table are stored in the temporary view; if the content duplication of the final system code table is less than the duplication threshold, all contents of the final system code table are stored in the entity table.

[0063] It should be noted that the steps shown in the flowcharts of the accompanying drawings can be executed in a computer system such as a set of computer-executable instructions, and that, although a logical order is shown in the flowcharts, in some cases, the steps shown or described can be executed in an order different from that shown here.

[0064] The embodiments of the present application also provide a Spark SQL processing device. It should be noted that the Spark SQL processing device of the embodiments of the present application can be used to execute the Spark SQL processing method provided in the embodiments of the present application. The device is used to implement the above-mentioned embodiments and preferred implementation methods, and the details that have been explained will not be repeated. As used below, the term "module" can be a combination of software and / or hardware that implements a predetermined function. Although the devices described in the following embodiments are preferably implemented in software, implementation by hardware, or a combination of software and hardware, is also possible and conceivable.

[0065] The following introduces the Spark SQL processing device provided in the embodiment of the present application.

[0066] Figure 3 This is a structural block diagram of a Spark SQL processing device provided according to an embodiment of the present application. Figure 3 As shown, the device includes:

[0067] An acquisition unit 31 is used to acquire a Spark SQL development script for representing the content of a data table library;

[0068] The first processing unit 32 is configured to sequentially perform logic extraction and splitting processing on the Spark SQL development script using a Scala script to obtain multiple sub-logics; and to divide each of the sub-logics to obtain at least multiple extraction modules SELECT, multiple source modules FROM, and multiple screening modules WHERE;

[0069] The second processing unit 33 is configured to load all of the above extraction modules SELECT, the above source modules FROM, and the above screening modules WHERE to obtain multiple loading results, and store all of the above loading results into corresponding positions of the system code table to obtain a final system code table.

[0070] In the above-mentioned device, the Scala script is used to perform logic extraction and splitting processing on the above-mentioned Spark SQL development script in sequence to obtain multiple sub-logics; and each of the above-mentioned sub-logics is divided and processed to obtain at least multiple extraction modules SELECT, multiple source modules FROM and multiple screening modules WHERE respectively, thereby achieving the purpose of refining the Spark SQL development script, improving the processing speed of Spark SQL, and avoiding direct processing of more complex logic. Finally, by loading all of the above-mentioned extraction modules SELECT, the above-mentioned source modules FROM and the above-mentioned screening modules WHERE, multiple loading results are obtained, and all of the above-mentioned loading results are stored in the corresponding positions of the system code table to obtain the final system code table, so that the system code table can reflect the key information in the data table, thereby solving the problem that the complex SQL of the existing solution is time-consuming due to the complex logic, and thus easily causes the job to fail.

[0071] In one embodiment of the present application, the first processing unit includes a first processing module and a second processing module. The first processing module is used to use the above-mentioned Scala script to perform logic extraction processing on the above-mentioned Spark SQL development script to obtain the SQL logic as a whole; the second processing module is used to use the above-mentioned Scala script to split the above-mentioned SQL logic as a whole to obtain multiple of the above-mentioned sub-logics.

[0072] In one embodiment of the present application, the device further includes a third processing module and a fourth processing module. After storing the above-mentioned loading result in the corresponding position of the system code table to obtain the final system code table, the third processing module is used to store all the contents of the above-mentioned final system code table into the entity table if there is a warning mark in the above-mentioned final system code table, and the warning mark is used to indicate the importance of the contents of the above-mentioned final system code table; the fourth processing module is used to store all the contents of the above-mentioned final system code table into the above-mentioned entity table or store all the contents of the above-mentioned final system code table into the temporary view according to the data volume of the above-mentioned final system code table if there is no warning mark in the above-mentioned final system code table.

[0073] In one embodiment of the present application, the fourth processing module includes a first processing submodule and a second processing submodule. The first processing submodule is used to store all contents of the above-mentioned final system code table into the above-mentioned entity table or store all contents of the above-mentioned final system code table into the above-mentioned temporary view according to the content duplication of the above-mentioned final system code table when the data volume of the above-mentioned final system code table is greater than or equal to the data volume threshold; the second processing submodule is used to store all contents of the above-mentioned final system code table into the above-mentioned temporary view when the data volume of the above-mentioned final system code table is less than the above-mentioned data volume threshold.

[0074] In one embodiment of the present application, the first processing submodule includes a third processing submodule and a fourth processing submodule. The third processing submodule is used to store all contents of the above-mentioned final system code table into the above-mentioned temporary view when the content repetition degree of the above-mentioned final system code table is greater than or equal to the repetition degree threshold; and the fourth processing submodule is used to store all contents of the above-mentioned final system code table into the above-mentioned entity table when the content repetition degree of the above-mentioned final system code table is less than the above-mentioned repetition degree threshold.

[0075] In one embodiment of the present application, the first processing unit includes a fifth processing module, which is used to divide and process each of the above sub-logics to obtain multiple extraction modules SELECT, multiple source modules FROM, multiple screening modules WHERE, multiple grouping modules GROUP and multiple association modules JOIN, and the above-mentioned association module JOIN is two of the above-mentioned sub-logics that have an association relationship among all the above-mentioned sub-logics.

[0076] In one embodiment of the present application, the first processing unit includes a sixth processing module, a seventh processing module and an eighth processing module. After obtaining multiple extraction modules SELECT, multiple source modules FROM, multiple screening modules WHERE, multiple grouping modules GROUP and multiple association modules JOIN, the sixth processing module is used to add a master table flag to the back end of the front end position sub-logic of the above-mentioned association module JOIN when the type of the above-mentioned association module JOIN is left association, and add a slave table flag to the back end of the back end position sub-logic of the above-mentioned association module JOIN, and the above-mentioned back end position sub-logic is located at the back end of the above-mentioned front end position sub-logic; the seventh processing module is used to add a slave table flag to the back end of the above-mentioned front end position sub-logic when the type of the above-mentioned association module JOIN is right association, and add a master table flag to the back end of the above-mentioned back end position sub-logic; the eighth processing module is used to add a master table flag to the back end of the above-mentioned front end position sub-logic and the back end position sub-logic respectively when the type of the above-mentioned association module JOIN is inner association.

[0077] The Spark SQL processing device includes a processor and memory. The acquisition unit, first processing unit, and second processing unit are stored in the memory as program units. The processor executes the program units stored in the memory to implement the corresponding functions. The modules are all located in the same processor; alternatively, the modules can be located in different processors in any combination.

[0078] The processor contains a kernel, which retrieves the corresponding program unit from memory. One or more kernels can be configured, and kernel parameters can be adjusted to address the problem of complex SQL statements in existing solutions, which can lead to long execution times and job failures due to complex logic.

[0079] The memory may include non-permanent memory in a computer-readable medium, random access memory (RAM) and / or non-volatile memory, such as read-only memory (ROM) or flash RAM, and the memory includes at least one memory chip.

[0080] An embodiment of the present invention provides a computer-readable storage medium, wherein the computer-readable storage medium includes a stored program, wherein when the program is executed, the device where the computer-readable storage medium is located is controlled to execute the SparkSQL processing method.

[0081] An embodiment of the present invention provides a processor, which is used to run a program, wherein the Spark SQL processing method is executed when the program is run.

[0082] An embodiment of the present invention provides a device comprising a processor, a memory, and a program stored in the memory and executable on the processor. When the processor executes the program, the device performs at least the following steps: obtaining a Spark SQL development script for representing the contents of a database table library; performing logic extraction and splitting processing on the Spark SQL development script using a Scala script to obtain multiple sub-logics; and dividing each of the sub-logics to obtain at least multiple extraction modules SELECT, multiple source modules FROM, and multiple filtering modules WHERE; loading all of the above extraction modules SELECT, source modules FROM, and filtering modules WHERE to obtain multiple loading results, and storing all of the above loading results in corresponding locations in a system code table to obtain a final system code table. The device herein may be a server, a PC, a PAD, a mobile phone, etc.

[0083] The present application also provides a computer program product, which, when executed on a data processing device, is suitable for executing a program that is initialized with at least the following method steps: obtaining a Spark SQL development script for representing the content of a data table library; using a Scala script to perform logic extraction processing and splitting processing on the above Spark SQL development script in sequence to obtain multiple sub-logics; and dividing each of the above sub-logics to obtain at least multiple extraction modules SELECT, multiple source modules FROM and multiple filtering modules WHERE; loading all of the above extraction modules SELECT, the above source modules FROM and the above filtering modules WHERE to obtain multiple loading results, and storing all of the above loading results in corresponding positions of the system code table to obtain a final system code table.

[0084] The present application also provides a Spark SQL processing system, which includes: one or more processors, a memory, and one or more programs, wherein the one or more programs are stored in the memory and configured to be executed by the one or more processors, and the one or more programs include a method for executing any of the above-mentioned Spark SQL processing methods. By using a Scala script to sequentially perform logic extraction and splitting processing on the Spark SQL development script, multiple sub-logics are obtained; and each of the above-mentioned sub-logics is divided and processed to obtain at least multiple extraction modules SELECT, multiple source modules FROM, and multiple screening modules WHERE, respectively, thereby achieving the purpose of refining the Spark SQL development script, improving the processing speed of Spark SQL, and avoiding direct processing of more complex logic. Finally, by loading all of the above-mentioned extraction modules SELECT, the above-mentioned source modules FROM, and the above-mentioned screening modules WHERE, multiple loading results are obtained, and all of the above-mentioned loading results are stored in corresponding positions of the system code table to obtain a final system code table, so that the system code table can reflect the key information in the data table, thereby solving the problem that the complex SQL of the existing solution is time-consuming due to the complex logic, which easily causes the job to fail.

[0085] Obviously, those skilled in the art will appreciate that the various modules or steps of the present invention described above can be implemented using a general-purpose computing device, can be centralized on a single computing device, or can be distributed across a network of multiple computing devices. They can be implemented using program code executable by the computing device, and thus, can be stored in a storage device and executed by the computing device. In some cases, the steps shown or described herein can be performed in a different order than that shown, or can be fabricated as separate integrated circuit modules, or multiple modules or steps can be fabricated as a single integrated circuit module. Thus, the present invention is not limited to any particular combination of hardware and software.

[0086] Those skilled in the art will appreciate that the embodiments of the present application can be provided as methods, systems, or computer program products. Therefore, the present application can adopt the form of a complete hardware embodiment, a complete software embodiment, or an embodiment in combination with software and hardware. Moreover, the present application can adopt the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to magnetic disk storage, CD-ROM, optical storage, etc.) that contain computer-usable program code.

[0087] The present application is described with reference to the flowcharts and / or block diagrams of the methods, devices (systems), and computer program products according to the embodiments of the present application. It should be understood that each process and / or box in the flowchart and / or block diagram, as well as the combination of the processes and / or boxes in the flowchart and / or block diagram, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, a special-purpose computer, an embedded processor, or other programmable data processing device to produce a machine, so that the instructions executed by the processor of the computer or other programmable data processing device generate instructions for implementing the steps in the process. Figure 1 a process or multiple processes and / or boxes Figure 1 A device that provides the functions specified in a block or multiple blocks.

[0088] These computer program instructions may also be stored in a computer readable memory that can direct a computer or other programmable data processing device to work in a specific manner, so that the instructions stored in the computer readable memory produce an article of manufacture comprising an instruction device, which implements the process Figure 1 a process or multiple processes and / or boxes Figure 1 The function specified in one or more boxes.

[0089] These computer program instructions can also be loaded onto a computer or other programmable data processing device so that a series of operational steps are executed on the computer or other programmable device to produce a computer-implemented process, thereby providing the instructions executed on the computer or other programmable device for implementing the process. Figure 1 a process or multiple processes and / or boxes Figure 1 The steps for the function specified in one or more boxes.

[0090] In a typical configuration, a computing device includes one or more processors (CPUs), input / output interfaces, network interfaces, and memory.

[0091] The memory may include non-permanent memory in a computer-readable medium, random access memory (RAM) and / or non-volatile memory in the form of read-only memory (ROM) or flash RAM. The memory is an example of a computer-readable medium.

[0092] Computer-readable media includes permanent and non-permanent, removable and non-removable media that can be implemented by any method or technology to store information. The information can be computer-readable instructions, data structures, program modules or other data. Examples of computer storage media include, but are not limited to, phase change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technology, compact disc read-only memory (CD-ROM), digital versatile disc (DVD) or other optical storage, magnetic cassettes, magnetic tape, magnetic disk storage or other magnetic storage devices or any other non-transmission media that can be used to store information that can be accessed by a computing device. As defined herein, computer-readable media does not include transitory computer-readable media (transitory media), such as modulated data signals and carrier waves.

[0093] It should also be noted that the terms "comprises," "includes," or any other variations thereof are intended to encompass non-exclusive inclusion, such that a process, method, commodity, or apparatus that includes a series of elements includes not only those elements but also other elements not explicitly listed, or includes elements inherent to such process, method, commodity, or apparatus. In the absence of further limitations, an element defined by the phrase "comprises a ..." does not exclude the presence of other identical elements in the process, method, commodity, or apparatus that includes the element.

[0094] From the above description, it can be seen that the above embodiments of the present application achieve the following technical effects:

[0095] 1) The Spark SQL processing method of the present application uses Scala scripts to perform logic extraction and splitting processing on the above-mentioned Spark SQL development scripts in sequence to obtain multiple sub-logics; and each of the above-mentioned sub-logics is divided and processed to at least obtain multiple extraction modules SELECT, multiple source modules FROM and multiple screening modules WHERE respectively, thereby achieving the purpose of refining the Spark SQL development script, improving the processing speed of Spark SQL, and avoiding direct processing of more complex logic. Finally, by loading all of the above-mentioned extraction modules SELECT, the above-mentioned source modules FROM and the above-mentioned screening modules WHERE, multiple loading results are obtained, and all of the above-mentioned loading results are stored in the corresponding positions of the system code table to obtain the final system code table, so that the system code table can reflect the key information in the data table, thereby solving the problem that the complex SQL of the existing solution is time-consuming due to the complex logic, and thus easily causes the job to fail.

[0096] 2) The Spark SQL processing device of the present application uses Scala scripts to perform logic extraction and splitting processing on the above-mentioned Spark SQL development scripts in sequence to obtain multiple sub-logics; and divides and processes each of the above-mentioned sub-logics to obtain at least multiple extraction modules SELECT, multiple source modules FROM and multiple screening modules WHERE respectively, thereby achieving the purpose of refining the Spark SQL development script, improving the processing speed of Spark SQL, and avoiding direct processing of more complex logic. Finally, by loading all of the above-mentioned extraction modules SELECT, the above-mentioned source modules FROM and the above-mentioned screening modules WHERE, multiple loading results are obtained, and all of the above-mentioned loading results are stored in the corresponding positions of the system code table to obtain the final system code table, so that the system code table can reflect the key information in the data table, thereby solving the problem that the complex SQL of the existing solution is time-consuming due to the complex logic, and thus easily causes the job to fail.

[0097] The above description is merely a preferred embodiment of the present application and is not intended to limit the present application. Various modifications and variations are possible for those skilled in the art. Any modifications, equivalent substitutions, or improvements made within the spirit and principles of the present application shall be included within the scope of protection of the present application.

Claims

1. A Spark SQL processing method, characterized in that: include: Get the Spark SQL development script that represents the contents of the database table. Using a Scala script to sequentially perform logic extraction and splitting processing on the Spark SQL development script to obtain multiple sub-logics; and dividing each of the sub-logics to obtain at least multiple extraction modules SELECT, multiple source modules FROM, and multiple screening modules WHERE; Load all the extraction modules SELECT, the source modules FROM, and the screening modules WHERE to obtain multiple loading results, and store all the loading results into corresponding positions in the system code table to obtain a final system code table; If there is a warning mark in the final system code table, all contents of the final system code table are stored in the entity table, and the warning mark is used to indicate the importance of the contents of the final system code table; When the warning mark does not exist in the final system code table, all contents of the final system code table are stored in the entity table or in a temporary view according to the data volume of the final system code table.

2. The method according to claim 1, characterized in that The Spark SQL development script is sequentially subjected to logic extraction and splitting processing using a Scala script to obtain multiple sub-logics, including: Using the Scala script to perform logic extraction processing on the Spark SQL development script to obtain the SQL logic structure; The Scala script is used to split the overall SQL logic to obtain multiple sub-logics.

3. The method according to claim 1, characterized in that According to the data amount of the final system code table, all contents of the final system code table are stored in the entity table, or all contents of the final system code table are stored in a temporary view, including: When the data volume of the final system code table is greater than or equal to the data volume threshold, storing all contents of the final system code table into the entity table or storing all contents of the final system code table into the temporary view according to the content duplication of the final system code table; When the data amount of the final system code table is less than the data amount threshold, all contents of the final system code table are stored in the temporary view.

4. The method according to claim 3, characterized in that According to the content duplication of the final system code table, storing all the contents of the final system code table into the entity table, or storing all the contents of the final system code table into the temporary view, includes: When the content repetition degree of the final system code table is greater than or equal to the repetition degree threshold, storing all the content of the final system code table into the temporary view; When the content repetition degree of the final system code table is less than the repetition degree threshold, all the contents of the final system code table are stored in the entity table.

5. The method according to claim 1, wherein Each of the sub-logics is divided and processed to obtain at least a plurality of extraction modules SELECT, a plurality of source modules FROM, and a plurality of screening modules WHERE, including: Each of the sub-logics is divided and processed to obtain multiple extraction modules SELECT, multiple source modules FROM, multiple screening modules WHERE, multiple grouping modules GROUP and multiple association modules JOIN. The association module JOIN is for two of the sub-logics that have an association relationship among all the sub-logics.

6. The method according to claim 5, characterized in that After obtaining a plurality of extraction modules SELECT, a plurality of source modules FROM, a plurality of screening modules WHERE, a plurality of grouping modules GROUP, and a plurality of association modules JOIN, the method further includes: In the case where the type of the association module JOIN is left association, a master table flag is added to the back end of the front end position sub-logic of the association module JOIN, and a slave table flag is added to the back end position sub-logic of the association module JOIN, and the back end position sub-logic is located at the back end of the front end position sub-logic; In the case where the type of the association module JOIN is right association, a slave table flag is added to the back end of the front-end position sub-logic, and a master table flag is added to the back end of the back-end position sub-logic; In the case where the type of the association module JOIN is inner association, a main table flag is added to the back end of the front-end position sub-logic and the back end of the back-end position sub-logic respectively.

7. A Spark SQL processing device, characterized in that: include: An acquisition unit, used to acquire a Spark SQL development script for representing the content of a data table library; A first processing unit is configured to sequentially perform logic extraction and splitting processing on the Spark SQL development script using a Scala script to obtain a plurality of sub-logics; and to divide each of the sub-logics to obtain at least a plurality of extraction modules SELECT, a plurality of source modules FROM, and a plurality of screening modules WHERE; A second processing unit is configured to load all of the extraction modules SELECT, the source modules FROM, and the screening modules WHERE to obtain a plurality of loading results, and store all of the loading results into corresponding positions of a system code table to obtain a final system code table; A third processing module stores all contents of the final system code table into an entity table if there is a warning flag in the final system code table, where the warning flag is used to indicate the importance of the contents of the final system code table; The fourth processing module stores all contents of the final system code table into the entity table or stores all contents of the final system code table into a temporary view according to the data volume of the final system code table when the warning mark does not exist in the final system code table.

8. A computer-readable storage medium, characterized in that The computer-readable storage medium includes a stored program, wherein when the program is executed, the device where the computer-readable storage medium is located is controlled to execute the Spark SQL processing method according to any one of claims 1 to 6.

9. A Spark SQL processing system, characterized in that: include: One or more processors, a memory, and one or more programs, wherein the one or more programs are stored in the memory and configured to be executed by the one or more processors, and the one or more programs include a processing method for executing the Spark SQL according to any one of claims 1 to 6.

Images