Data extraction method and device, electronic equipment and computer readable storage medium
By determining the reasonable number of target shards and uniform data volume, combined with Apache Spark memory calculation, the data tilt problem in the data extraction process in the big data data warehouse is solved, the extraction speed and efficiency are improved, and it is suitable for data source tables of various table structures.
Patent Information
- Application Number
- CN202410135650.0
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-01-30
- Publication Date
- 2025-08-01
AI Technical Summary
The prior art has data tilt problems during the data extraction process in the big data data warehouse, resulting in slow extraction speed, low efficiency and long task waiting time, especially in large tables or databases and tables scenarios.
Determine a reasonable number of target shards based on the data information of the data source table and the storage information of the target table, and set a uniform data amount for each target shard. Use Apache Spark to perform memory calculations to avoid data skew and improve decimation speed and efficiency.
It reduces the possibility of data skew, improves data extraction speed and efficiency, reduces task waiting time, and is suitable for data source tables of various table structures, reducing human processing costs.
Smart Images
Figure CN120407556A_ABST
Abstract
Description
Technical Field
[0001] The present disclosure relates to the field of big data technology, and particularly to a data extraction method and apparatus, an electronic device, and a computer-readable storage medium. Background Art
[0002] Big data technology has been deeply involved in the market for many years. There are various open-source components and architecture solutions emerging in an endless stream, and the community is very active. There are a large number of R & D personnel constantly contributing. Therefore, big data technology is updated very quickly. However, data needs to be stored in specific locations, such as in a big data warehouse.
[0003] The purpose of a big data warehouse is to build an integrated data environment for analysis to provide decision support for enterprises. It is created for the purpose of analytical reports and decision support. The data warehouse itself does not "produce" any data, nor does it need to "consume" any data. The data comes from the outside and is open to external applications. The data tables in the source system of the data warehouse are usually stored intact, which is called the data preparation area or the data source layer, and is the source of the subsequent data processing in the data warehouse.
[0004] The data source layer of a big data warehouse usually extracts data from the data source using the Sqoop tool. Summary of the Invention
[0005] The present disclosure provides a data extraction method and apparatus, an electronic device, and a computer-readable storage medium.
[0006] In a first aspect, the present disclosure provides a data extraction method, which includes: determining the number of target shards based on the data information of the data source table and the storage information of the target table in the data warehouse, where the target shards are used to extract data from the data source table, the data information of the data source table includes the total amount of data in the data source table and the data row size of the data source table, and the storage information of the target table includes the data block size of the target table and the table compression ratio of the target table; determining the amount of data corresponding to each target shard based on the total amount of data in the data source table and the number of target shards; extracting data from the data source table through the target shards according to the amount of data corresponding to each target shard, and storing the extracted data in the target table.
[0007] In a second aspect, the present disclosure provides a data extraction device, which includes: a calculation module, configured to determine the number of target shards based on the data information of a data source table and the storage information of a target table in a data warehouse, and determine the data volume corresponding to each target shard based on the total data volume of the data source table and the number of target shards, where the target shards are used to extract data from the data source table, the data information of the data source table includes the total data volume of the data source table and the data row size of the data source table, and the storage information of the target table includes the data block size of the target table and the table compression ratio of the target table; and a data extraction module, configured to extract data from the data source table through the target shards according to the data volume corresponding to each target shard, and store the extracted data in the target table.
[0008] In a third aspect, the present disclosure provides an electronic device, which includes: at least one processor; and a memory communicatively connected to the at least one processor; wherein, the memory stores one or more computer programs executable by the at least one processor, and the one or more computer programs are executed by the at least one processor, so that the at least one processor can execute the above data extraction method.
[0009] In a fourth aspect, the present disclosure provides a computer-readable storage medium, on which a computer program is stored, wherein the computer program implements the above data extraction method when executed by a processor / processing core.
[0010] The data extraction method provided by the embodiments of the present disclosure reduces the possibility of data skew, improves the data extraction speed and efficiency, and reduces the task waiting time.
[0011] It should be understood that the content described in this part is not intended to identify the key or important features of the embodiments of the present disclosure, nor is it used to limit the scope of the present disclosure. Other features of the present disclosure will become easily understandable through the following description. BRIEF DESCRIPTION OF THE DRAWINGS
[0012] The drawings are used to provide a further understanding of the present disclosure, and constitute a part of the specification. They are used to explain the present disclosure together with the embodiments of the present disclosure, and do not constitute a limitation to the present disclosure. By referring to the drawings to describe the detailed exemplary embodiments, the above and other features and advantages will become more obvious to those skilled in the art. In the drawings:
[0013] Figure 1 Schematically shows an application scenario diagram of the data extraction method and device provided by the embodiments of the present disclosure
[0014] Figure 2 Is a flowchart of a data extraction method provided by an embodiment of the present disclosure;
[0015] Figure 3 Another flowchart of a data extraction method provided by an embodiment of the present disclosure;
[0016] Figure 4 A block diagram of a data extraction device provided by an embodiment of the present disclosure;
[0017] Figure 5 A block diagram of an electronic device provided by an embodiment of the present disclosure. Detailed implementation manners
[0018] To enable those skilled in the art to better understand the technical solutions of the present disclosure, the following provides descriptions of exemplary embodiments of the present disclosure with reference to the accompanying drawings. Various details of the embodiments of the present disclosure are included to facilitate understanding, and they should be considered merely exemplary. Therefore, those of ordinary skill in the art should recognize that various changes and modifications can be made to the embodiments described herein without departing from the scope and spirit of the present disclosure. Similarly, for clarity and conciseness, descriptions of well-known functions and structures are omitted in the following.
[0019] Without conflict, the embodiments of the present disclosure and the features in the embodiments can be combined with each other.
[0020] As used herein, the term "and / or" includes any and all combinations of one or more of the associated listed items.
[0021] The terms used herein are only for describing specific embodiments and are not intended to limit the present disclosure. As used herein, the singular forms "a" and "the" are also intended to include the plural forms unless the context clearly indicates otherwise. It will also be understood that when the terms "include" and / or "consist of" are used in this specification, the specified features, wholes, steps, operations, elements, and / or components are present, but one or more other features, wholes, steps, operations, elements, components, and / or their groups are not excluded. "Connection" or "connected" and other similar terms are not limited to physical or mechanical connections, but may include electrical connections, whether direct or indirect.
[0022] Unless otherwise defined, all terms (including technical and scientific terms) used herein have the same meaning as commonly understood by those of ordinary skill in the art. It will also be understood that terms such as those defined in common dictionaries should be interpreted as having a meaning consistent with their meaning in the context of the relevant art and the present disclosure, and will not be interpreted as having an idealized or overly formal meaning unless clearly defined herein.
[0023] The Apache framework Hadoop is an increasingly general-purpose distributed computing environment mainly used to process big data. As cloud providers utilize this framework, more users transfer datasets between Hadoop and traditional databases, and tools that can assist in data transfer become more important. This is how Apache Sqoop came into being. It is mainly used for batch data migration between Hadoop and traditional relational databases (RDBMS) such as MySQL and Oracle. It can import structured data from a relational database (such as MySQL, Oracle, etc.) into the Hadoop Distributed File System (HDFS), or export data from HDFS into a relational database.
[0024] Under the hood, Sqoop uses MapReduce programs to implement data extraction, transformation, and loading. The inherent characteristics of MapReduce programs ensure parallelization and high fault tolerance. Moreover, compared with traditional ETL tools such as Kettle, tasks run on the Hadoop cluster, reducing the usage of ETL server resources. In specific scenarios, there will be a significant performance improvement in the data extraction process. Figure 2 The schematic diagram of the MapReduce program processing data is shown.
[0025] Based on the underlying mechanism of Sqooq for data extraction is the MapReduce mechanism. MapReduce is the core working principle of Hadoop, which is divided into the Map stage and the Reduce stage. Data is extracted in the Map stage and aggregated in the Reduce stage.
[0026] Whether in the Map stage or the Reduce stage of Sqoop, data will be written to disk, so disk input / output (IO) will increase. Network IO needs to be consumed during serialization and deserialization. Because the data needs to be sorted and organized, it needs to be read and written to disk repeatedly, so the requirements for disk IO and network IO are very high, and the efficiency of processing data in terms of time is relatively low.
[0027] In the Map stage, during the process of Sqoop extracting data, the parallelism and the number of shards are controlled by the num-mappers parameter, and the data splitting field is controlled by the split-by parameter, thus achieving the parallelization of data extraction.
[0028] When Sqoop extracts data, it is necessary to customize the split-by parameter. Sqoop splits the data according to different split-by parameters, and then assigns the split areas (i.e., shards) to different maps. Each map then processes the values obtained from the database row by row and writes them to HDFS. Split-by has different splitting methods depending on the parameter type, for example, the int type. In the case of the int type, Sqoop will obtain the maximum split-by field and the minimum split-by field, and then determine the number of regions to divide according to the num-mappers parameter. For example, assuming that the maximum split-by field and the minimum split-by field are 1000 and 1 respectively, and the num-mappers parameter is 2, it will divide the data into two regions, namely (1,500) and (501,1000), and assign two SQL statements to the two maps for import operations. Finally, each map obtains the data in its own SQL statement for import.
[0029] When controlling the data segmentation fields using the split-by parameter, the num-mappers parameter is used to start N maps to import data in parallel. For example, the default is 4. The split-by parameter is used to split the table's work units according to a specific column, which usually specifies the primary key for segmentation. However, the following two situations may occur in the data source table: one is that the field contains a JSON string, and the data length of the JSON string is not fixed, or it can be understood that the primary key is discontinuous (due to the deletion of some data); the other is that the primary key is not indexed. Both of these situations will lead to slower data extraction and / or memory overflow issues. When either problem occurs, it will be difficult to extract data regardless of setting the split-by parameter to change the segmentation method. In addition, when the amount of data distributed to each map is severely uneven, the uneven mapping can easily lead to data skew, causing the data extraction for some maps to be always in progress. For example, when the amount of data allocated to one part of the map is large and the amount of data allocated to another part of the map is small, when the part of the map with large data volume is processed, due to the excessive number of files with small data volume, the execution nodes are concentrated on one or a small number of nodes, causing the amount of calculation to increase exponentially, aggravating the calculation and making it impossible to obtain the calculation results.
[0030] On the other hand, because Sqoop's internal core is based on the MapReduce mechanism, its data extraction efficiency is relatively slow, especially when encountering large tables or the data source is a sharded database. It takes a while to continue processing subsequent work. This is especially time-consuming when the data warehouse needs to periodically initialize a full copy of the data for some internal data comparison or to troubleshoot data problems.
[0031] To solve the above problems of data extraction based on Sqoop, the present disclosure proposes a data extraction method.
[0032] The data extraction method proposed by the present disclosure can determine the reasonable number of target shards based on the data information of the data source table and the storage information of the target table, and set a uniform data volume for each target shard based on the total data volume of the data source table and the number of target shards, thereby avoiding uneven data volumes allocated to each target shard, reducing the possibility of data skew, improving the data extraction speed and efficiency, and reducing the task waiting time.
[0033] Figure 1 Schematically shows an application scenario diagram of the data extraction method and device provided by the embodiments of the present disclosure.
[0034] As Figure 1 shown, the application scenario of the embodiments of the present disclosure may include a terminal device 101, a network 103, and a server 102. The network 103 is used to provide a medium for a communication link between the terminal device 101 and the server 102. The network 103 may include various connection types, such as wired, wireless communication links, or fiber optic cables, etc.
[0035] The user may use the terminal device 101 to interact with the server 102 through the network 103 to receive or send messages, etc. Various communication client applications may be installed on the terminal device 101, such as shopping applications, web browser applications, search applications, instant messaging tools, email clients, social platform software, etc. (only as examples).
[0036] The terminal device 101 may be various electronic devices having a display screen and supporting web browsing, including but not limited to smart phones, tablet computers, laptop portable computers, and desktop computers, etc.
[0037] The server 102 may be a server providing various services, such as a background management server (only as an example) that supports the websites browsed by the user using the terminal device 101. The background management server may analyze and process data such as user requests received, and feedback the processing results (such as web pages, information, or data, etc. obtained or generated according to user requests) to the terminal device.
[0038] It should be noted that the data extraction method and apparatus provided in the embodiments of the present disclosure may be executed by the server 102. Correspondingly, the data extraction method and apparatus provided in the embodiments of the present disclosure may be provided in the server 102. The data extraction method and apparatus provided in the embodiments of the present disclosure may also be executed by a server or a server cluster different from the server 102 and capable of communicating with the terminal device 101 and / or the server 102. Correspondingly, the data extraction method and apparatus provided in the embodiments of the present disclosure may also be provided in a server or a server cluster different from the server 102 and capable of communicating with the terminal device 101 and / or the server 102.
[0039] It should be understood that Figure 1 the numbers of the terminal devices, networks, and servers in
[0040] Figure 2 FIG. shows a flowchart of the data extraction method provided in the embodiments of the present disclosure. The data extraction method includes steps S201 to S203.
[0041] In step S201, the number of target shards is determined based on the data information of the data source table and the storage information of the target table in the data warehouse.
[0042] The target table is used in the data warehouse to store the structure of the data source tables from various data sources. The target shard is used to extract data from the data source table and may represent a mechanism for extracting data from the data source table. The data information of the data source table may include the total amount of data in the data source table and the data row size of the data source table, and the storage information of the target table may include the data block size of the target table and the table compression ratio of the target table. The total amount of data in the data source table may be the total number of rows in the data source table, and the data row size of the data source table may be the average storage space size of all rows in the data source table. For example, the data row size of the data source table may be obtained from the meta-database of the data source table.
[0043] For example, when the number of target shards is too small, the amount of data that each target shard needs to extract increases. In this case, the computational amount of the data extraction task increases exponentially, burdening the computation and easily resulting in the inability to obtain the computation result and the data extraction task getting stuck. When the number of target shards is too large, the amount of data that each target shard needs to extract decreases. Due to limitations such as the execution environment and execution resources, it may not be possible to execute the tasks of extracting data from all target shards in parallel. In this case, it is necessary for some target shards to wait while other target shards are extracting data, and after the extraction of the some target shards is completed, the other target shards can then perform data extraction, resulting in an overly long task waiting time.
[0044] According to an embodiment of the present disclosure, based on the total amount of data in the data source table, the data row size, and the data block size and table compression ratio of the target table, a reasonable number of shards can be determined according to the characteristics of the data source table and the target table itself, so as to set the number of data extraction tasks (i.e., the number of target shards) applicable to the specific data source table and target table, reduce the task waiting time, and lay a foundation for evenly allocating the amount of data to each target shard subsequently.
[0045] Step S201 may include: calculating a first number of shards based on the total amount of data in the data source table, the data row size of the data source table, the data block size of the target table, and the table compression ratio of the target table, and determining the number of target shards based on the first number of shards and the default number of shards corresponding to the total amount of data in the data source table.
[0046] For example, the default data block size of the target table may be 128M or 256M, and the default table compression ratio of the target table may be 7, that is, the compression ratio from the data source table to the target table is 7:1. However, the default data block size and table compression ratio are only examples, and the user can set the default data block size and table compression ratio as needed, set the data block size and table compression ratio during each data extraction, etc., and the embodiments of the present disclosure do not limit this.
[0047] According to an embodiment of the present disclosure, the first number of shards can be calculated by rounding up the value calculated by the following equation: the total amount of data in the data source table / (the data block size of the target table / (the data row size of the data source table * the table compression ratio of the target table)).
[0048] According to an embodiment of the present disclosure, the user can pre-set different default numbers of shards for data source tables of different sizes. For example, when the total amount of data in the data source table is less than or equal to 10,000, the default number of shards is 1; when the total amount of data in the data source table is greater than 10,000 and less than or equal to 100,000, the default number of shards is 5; when the total amount of data in the data source table is greater than 100,000 and less than or equal to 1,000,000, the default number of shards is 10; when the total amount of data in the data source table is greater than 1,000,000 and less than or equal to 10,000,000, the default number of shards is 50; when the total amount of data in the data source table is greater than 10,000,000 and less than or equal to 100,000,000, the default number of shards is 100; when the total amount of data in the data source table is greater than 100,000,000, the default number of shards is 200. The user can set the default number of shards according to needs or the environment for executing the data extraction method provided by the embodiments of the present disclosure, and the embodiments of the present disclosure do not limit this.
[0049] According to an embodiment of the present disclosure, determining the number of target shards based on the number of first shards and the default number of shards corresponding to the total amount of data in the data source table may include: comparing the number of first shards with the default number of shards; in the case where the number of first shards is less than or equal to the default number of shards, determining the number of first shards as the number of target shards; and in the case where the number of first shards is greater than the default number of shards, determining the default number of shards as the number of target shards.
[0050] According to an embodiment of the present disclosure, the number of target shards may also be specified by a user. In the case where the user specifies the number of target shards, there is no need to determine the number of target shards based on the data information of the data source table and the storage information of the target table, but directly extract data according to the number of target shards specified by the user.
[0051] In step S202, determine the amount of data corresponding to each target shard based on the total amount of data in the data source table and the number of target shards.
[0052] According to an embodiment of the present disclosure, after determining the number of target shards based on the total amount of data in the data source table, the data row size, the data block size of the target table, and the table compression ratio, the amount of data allocated to each target shard may be determined based on the total amount of data in the data source table and the number of target shards.
[0053] That is to say, the amount of data allocated to each target shard takes into account the total amount of data in the data source table, the data row size, the data block size of the target table, and the table compression ratio, so as to set a uniform amount of data for each target shard.
[0054] For example, the same amount of data may be allocated to each target shard according to the total amount of data in the data source table and the number of target shards.
[0055] Step S202 may include: calculating the value obtained by dividing the total amount of data in the data source table by the number of target shards, and determining the amount of data corresponding to each target shard based on the calculated value.
[0056] According to an embodiment of the present disclosure, in the case where the value obtained by dividing the total amount of data in the data source table by the number of target shards is an integer, the value may be determined as the amount of data corresponding to each target shard.
[0057] According to an embodiment of the present disclosure, in the case where the value obtained by dividing the total amount of data in the data source table by the number of target shards is not an integer, the integer obtained by rounding up the value may be determined as the amount of data corresponding to each target shard.
[0058] According to an embodiment of the present disclosure, after determining the number of target shards and the amount of data corresponding to each target shard, the boundary of the extracted data is determined for each target shard, so that each target shard can extract a uniform amount of data from the data source table.
[0059] Through step S202, the data amounts corresponding to each target shard can be made consistent with each other, so that the data amounts extracted through each target shard are consistent and uniform subsequently, thereby avoiding, for example, the problem that Sqoop divides the data amounts corresponding to each target shard based on a possibly discontinuous primary key, resulting in uneven data amounts corresponding to different target shards, and further avoiding the problem of data skew during the data extraction process due to the large difference in the data amounts corresponding to different target shards.
[0060] In step S203, data is extracted from the data source table through the target shards according to the data amounts corresponding to each target shard, and the extracted data is stored in the target table.
[0061] Through step S203, data can be extracted from the data source table in parallel through multiple target shards and the extracted data can be stored in the target table.
[0062] By considering the total amount of data in the data source table, the data row size, the data block size of the target table, and the table compression ratio to determine the number of target shards and the data amounts corresponding to each target shard, it is possible to set a reasonable number of target shards according to the characteristics of the data source table and the target table involved in the data extraction and achieve a high degree of uniformity among the data amounts of each target shard. Thus, when extracting data from the data source table based on the target shards subsequently, the possibility of data skew can be reduced, the data extraction speed and efficiency can be improved, and the task waiting time can be reduced.
[0063] The data extraction method according to an embodiment of the present disclosure may further include: matching the table structure of the target table in the data warehouse with the table structure of the data source table.
[0064] According to an embodiment of the present disclosure, the data source table is, for example, a data table of a relational database (such as MySQL, Oracle, etc.), and the target table is, for example, a data table of a Hadoop Distributed File System (HDFS), such as a Hive data table and a Hbase data table.
[0065] That is to say, there are differences in the table structures between the data source table and the target table due to their different types. Since extracting data actually stores the data in the data source table into the target table, when the table structure of the data source table does not match that of the target table, the data extraction task (or target shard) cannot determine where to place the data extracted from the data source table in the target table, which may lead to the failure of data extraction. For example, in the case where a field in the data source table is not included in the fields of the target table, after the data extraction task (or target shard) extracts data from this field of the data source table, it cannot store it in the target table. Another example is that in the case where the field type of a field in the target table does not correspond to the field type of the same field in the data source table, after the data extraction task (or target shard) extracts data from this field of the data source table and stores it in the corresponding field of the target table, due to the non-correspondence of the field types, the data stored in the target table may be inconsistent with the original data in the data source table.
[0066] According to the embodiments of the present disclosure, before formally extracting data, the data source table and the target table can be checked to determine whether the table structure of the target table matches that of the data source table. In the case of non-matching, the table structure of the target table can be processed to make the table structure of the target table match that of the data source table, thereby avoiding the subsequent failure of data extraction due to the non-matching of the two table structures.
[0067] The table structure can at least include fields and field types. In addition, the table structure can also include field comments.
[0068] Matching the table structure of the target table in the data warehouse with the table structure of the data source table can include: obtaining the table structures of the target table and the data source table, determining whether the table structure of the target table matches that of the data source table, in the case where the fields of the target table do not match (for example, are inconsistent) with the fields of the data source table, modifying the fields of the target table to be consistent with the fields of the data source table, and in the case where the field types of the target table do not match (for example, do not meet the predetermined corresponding conditions) with the field types of the data source table, modifying the field types of the target table to correspond to (that is, meet the predetermined corresponding conditions) the field types of the data source table.
[0069] Matching the table structure of the target table in the data warehouse with the table structure of the data source table can also include: in the case where the field comments of the target table do not match the field comments of the data source table, modifying the field comments of the target table to be consistent with or corresponding to the field comments of the data source table.
[0070] The predetermined corresponding conditions may include: the field types date, time, year, datetime, timestamp, char, varchar, tinyblob, tinytext, blob, text, mediumblob, mediumtext, longblob, longtext, json, bit of the data source table correspond to the field type string of the target table; the field types tinyint, samllint, mediumint, int, integer of the data source table correspond to the field type int of the target table; the field type bigint of the data source table corresponds to the field type bigint of the target table; the field types decimal, float, and double of the data source table correspond to the field types decimal, float, and double of the target table respectively; other field types of the data source table correspond to the field type string of the target table. The above corresponding relationships can be set according to user needs, and the embodiments of the present disclosure do not limit the above corresponding relationships and the case sensitivity of the table field types of the data source table.
[0071] For example, in the case where the field type of a certain field in the data source table is CHAR or VARCHAR, if the field type of the same field in the target table is not string, the field type of this field in the target table is set to string according to the above corresponding relationship.
[0072] Matching the table structure of the target table in the data warehouse with the table structure of the data source table may further include: determining whether the target table exists in the data warehouse. If the target table does not exist, the table structure of the data source table is obtained, and a target table with a table structure matching that of the data source table is created according to the table structure of the data source table.
[0073] According to the embodiments of the present disclosure, by matching the table structures of the data source table and the target table, the matching of the table structure of the target table and the table structure of the data source table is achieved. Thus, in the case of the addition and / or deletion of fields, the change of field types, and the change of field annotations in the data source table, the table structure of the target table can always be matched with the table structure of the data source table, thereby avoiding the subsequent failure of data extraction due to the mismatch between the table structures of the target table and the data source table. Correspondingly, the data extraction method according to the embodiments of the present disclosure is not limited by the table structure of the data source table and can be applied to data source tables with various structures.
[0074] According to the embodiments of the present disclosure, the parallelism of parallel extraction of data by multiple target shards can also be set.
[0075] The data extraction method according to an embodiment of the present disclosure further includes: determining the parallelism based on the number of target shards, and controlling the number of target shards for simultaneously extracting data through the parallelism.
[0076] According to an embodiment of the present disclosure, a user can set a default maximum parallelism according to needs or the environment for executing the data extraction method provided by the embodiment of the present disclosure. For example, the default maximum parallelism can be 10.
[0077] Determining the parallelism based on the number of target shards may include: in the case where the number of target shards is less than or equal to the default maximum parallelism, determining the number of target shards as the parallelism; in the case where the number of target shards is greater than the default maximum parallelism, determining the default maximum parallelism as the parallelism.
[0078] By setting the parallelism, resources can be maximally utilized to extract data in parallel, and it can be ensured that the tasks of extracting data in parallel do not exceed the maximum tolerance of the execution environment.
[0079] Taking the consumer finance scenario as an example, the data source includes the main process media for advertising access to the market, with data volume reaching tens of billions per day. The accounting core needs to permanently store data, and there are also external data sources introducing data, data of the company's internal application systems. Even the data is sourced from Hbase and ELK (log storage system). There are two core indicators reflected here. One is the large data scale, and the other is the diversification of the data types of the data sources, and some data is stored directly in JSON format without being scattered. In order for the data warehouse to enable downstream marketplaces and business analysts to use this data, diverse data extraction strategies are required to meet these scenarios.
[0080] Through the data extraction method according to an embodiment of the present disclosure, the data source table and the target table can be matched, so that the data extraction method can be applicable to data source tables with various table structures, and compatibility processing is performed on data source tables with various table structures. As a result, the link of the data warehouse for extracting data becomes more diverse, reducing the manual processing of data warehouse personnel and solving the problem that the data warehouse cannot extract data due to changes in the table structure of the data source table, greatly reducing the costs of data extraction and manual intervention.
[0081] Through the data extraction method according to the embodiments of the present disclosure, the number of target shards can be determined based on the data information of the data source table (i.e., the total amount of data, the data row size) and the storage information of the data warehouse (i.e., the data block size of the target table, the table compression ratio of the target table), and a uniform amount of data can be set for each target shard. For example, assuming that the total amount of data in the data source is nine hundred thousand, the number of target shards determined according to the embodiments of the present disclosure is nine, and the amount of data corresponding to each target shard is one hundred thousand. Therefore, it is possible to achieve the homogenization of the amount of data corresponding to each target shard, that is, different target shards extract the same amount of data, thereby improving the data extraction speed and efficiency and reducing the task waiting time, avoiding the data skew problem that may exist when extracting data through multiple target shards, and at the same time avoiding the problems of slow data extraction speed and / or memory overflow.
[0082] The data extraction method according to the embodiments of the present disclosure can be implemented through Apache Spark. For example, it can be implemented through a Python program running in Apache Spark.
[0083] Apache Spark is a general computing engine designed specifically for big data processing. Spark has the advantages of MapReduce, but different from MapReduce, the intermediate output results of tasks can be cached in memory, so there is no need to read and write HDFS anymore, reducing disk data interaction. Therefore, Spark can better adapt to iterative algorithms such as machine learning and data mining. Spark provides technical components such as Spark RDD, Spark SQL, Spark Streaming, Spark MLlib, and Spark GraphX, which can complete common tasks in the field of big data such as offline batch processing, interactive query, streaming computing, machine learning, and graph computing in one stop. This is the feature of Spark's one-stop development.
[0084] Apache Spark performs lazy loading based on in-memory operations (data preprocessing is stored in memory), and all calculations are in memory without the need to repeatedly write to disk. The data is only written to disk after the data extraction is completed. Based on this feature of Apache Spark, the embodiments of the present disclosure use a Python program running in Apache Spark to calculate the data extraction process in memory and write the data to the target table after the data extraction is completed, avoiding repeated reading and writing of the disk and greatly improving the processing efficiency.
[0085] Figure 3 Another flowchart showing the data extraction method provided by the embodiments of the present disclosure is shown. The data extraction method includes steps S301 to S308. Steps S304 and S305 are respectively related to the reference Figure 2The described steps S201 and S202 are the same. For the sake of brevity, the repeated description thereof is omitted.
[0086] In step S301, the table structure of the target table in the data warehouse is matched with the table structure of the data source table.
[0087] In step S302, it is determined whether the user specifies the number of shards. If the user specifies the number of shards (yes in S302), then in step S303, the number of shards specified by the user is used as the number of target shards for extracting data from the data source table.
[0088] If the user does not specify the number of shards (no in S302), then in step S304, the number of target shards for extracting data from the data source table is determined based on the data information of the data source table and the storage information of the target table.
[0089] In step S305, the amount of data corresponding to each target shard is determined based on the total amount of data in the data source table and the number of target shards.
[0090] In step S306, it is determined whether the user specifies the degree of parallelism. If the user does not specify the degree of parallelism (no in S306), then in step S307, the degree of parallelism is determined based on the number of target shards. Subsequently, in step S308, data is extracted from the data source table through the target shards, the number of target shards for simultaneously extracting data is controlled by the degree of parallelism, and the extracted data is stored in the target table.
[0091] If the user specifies the degree of parallelism (yes in S306), then in step S308, based on the number of target shards and the amount of data corresponding to each target shard, data is extracted from the data source table through the target shards, the number of target shards for simultaneously extracting data is controlled by the degree of parallelism, and the extracted data is stored in the target table.
[0092] Through the data extraction method according to the embodiments of the present disclosure, it is possible to facilitate extracting data from a data source table with various table structures and setting the number of target shards suitable for the table structure of the data source table, equalize the amount of data allocated to each target shard, set a reasonable degree of parallelism, thereby being compatible with various types of data source tables, improving the data extraction speed and efficiency, and reducing the task waiting time.
[0093] The data extraction method according to the embodiments of the present disclosure can be implemented by Apache Spark. For example, it can be implemented by a Python program running in Apache Spark, thereby completing the extraction of data in memory, avoiding repeated reading and writing to the disk, and further improving the processing efficiency.
[0094] It can be understood that, without violating the principle logic, the above-mentioned method embodiments mentioned in the present disclosure can be combined with each other to form combined embodiments. Due to space limitations, the present disclosure will not elaborate further. Those skilled in the art can understand that in the above methods of the specific implementation manner, the specific execution order of each step should be determined according to its function and possible internal logic.
[0095] In addition, the present disclosure also provides a data extraction device, an electronic device, and a computer-readable storage medium, all of which can be used to implement any data extraction method provided by the present disclosure. The corresponding technical solutions and descriptions can be referred to the corresponding records in the method part and will not be elaborated further.
[0096] Figure 4 It is a block diagram of a data extraction device provided by an embodiment of the present disclosure.
[0097] Referring to Figure 4 , an embodiment of the present disclosure provides a data extraction device 400, and the data extraction device 400 includes: a calculation module 401 and an extraction module 402.
[0098] The calculation module 401 is configured to determine the number of target shards based on the data information of the data source table and the storage information of the target table in the data warehouse, and determine the data volume corresponding to each target shard based on the number of target shards. The target shards are used to extract data from the data source table. The data information of the data source table includes the total amount of data in the data source table and the data row size of the data source table. The storage information of the target table includes the data block size of the target table and the table compression ratio of the target table.
[0099] The extraction module 402 is configured to extract data from the data source table through the target shards according to the data volume corresponding to each target shard, and store the extracted data in the target table.
[0100] The data extraction device 400 according to an embodiment of the present disclosure may further include a matching module, and the matching module is configured to match the table structure of the target table with the table structure of the data source table. The table structure includes at least fields and field types.
[0101] The calculation module 401 is further configured to determine the degree of parallelism based on the number of target shards, and control the number of target shards for simultaneously extracting data through the degree of parallelism.
[0102] For the detailed functions of the matching module, the calculation module 402, and the extraction module 403, reference may be made to the data extraction method described above, and details will not be elaborated here.
[0103] Each module in the above data extraction device 400 can be implemented in whole or in part by software, hardware, or a combination thereof. Each of the above modules can be embedded in the processor of the computer device in hardware form or independent of the processor, or stored in the memory of the computer device in software form, so that the processor can call and execute the operations corresponding to each of the above modules.
[0104] Figure 5 It is a block diagram of an electronic device provided by an embodiment of the present disclosure.
[0105] Refer to Figure 5 , an embodiment of the present disclosure provides an electronic device, which includes: at least one processor 501; at least one memory 502, and one or more I / O interfaces 503 connected between the processor 501 and the memory 502; wherein, the memory 502 stores one or more computer programs executable by at least one processor 501, and the one or more computer programs are executed by at least one processor 501, so that at least one processor 501 can execute the above data extraction method.
[0106] Each module in the above electronic device can be implemented in whole or in part by software, hardware, or a combination thereof. Each of the above modules can be embedded in the processor of the computer device in hardware form or independent of the processor, or stored in the memory of the computer device in software form, so that the processor can call and execute the operations corresponding to each of the above modules.
[0107] An embodiment of the present disclosure also provides a computer-readable storage medium, on which a computer program is stored, wherein the computer program implements the above data extraction method when executed by a processor / processing core. The computer-readable storage medium can be a volatile or non-volatile computer-readable storage medium.
[0108] An embodiment of the present disclosure also provides a computer program product, including computer-readable code, or a non-volatile computer-readable storage medium carrying the computer-readable code. When the computer-readable code runs in the processor of the electronic device, the processor in the electronic device executes the above data extraction method.
[0109] Those of ordinary skill in the art will understand that all or some of the steps in the methods disclosed above, and the functional modules / units in systems and devices, can be implemented as software, firmware, hardware, and appropriate combinations thereof. In a hardware implementation, the division of functional modules / units mentioned above does not necessarily correspond to the division of physical components; for example, one physical component can have multiple functions, or one function or step can be executed by several physical components in cooperation. Some or all physical components can be implemented as software executed by a processor, such as a central processing unit, a digital signal processor, or a microprocessor, or implemented as hardware, or implemented as an integrated circuit, such as an application-specific integrated circuit. Such software can be distributed on a computer-readable storage medium, which can include a computer storage medium (or non-transitory medium) and a communication medium (or transitory medium).
[0110] As is well known to those of ordinary skill in the art, the term computer storage medium includes volatile and non-volatile, removable and non-removable media implemented in any method or technology for storing information, such as computer-readable program instructions, data structures, program modules, or other data. Computer storage media includes but is not limited to random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM), static random access memory (SRAM), flash memory or other memory technologies, portable compact disc read-only memory (CD-ROM), digital versatile disc (DVD) or other optical disc storage, magnetic cassettes, magnetic tape, magnetic disk storage or other magnetic storage devices, or any other medium that can be used to store the desired information and can be accessed by a computer. In addition, it is well known to those of ordinary skill in the art that a communication medium typically contains computer-readable program instructions, data structures, program modules, or other data in a modulated data signal such as a carrier wave or other transmission mechanism, and can include any information delivery medium.
[0111] The computer-readable program instructions described herein can be downloaded from a computer-readable storage medium to various computing / processing devices, or downloaded to an external computer or external storage device through a network, such as the Internet, a local area network, a wide area network, and / or a wireless network. The network can include copper transmission cables, fiber optic transmissions, wireless transmissions, routers, firewalls, switches, gateway computers, and / or edge servers. The network adapter or network interface in each computing / processing device receives the computer-readable program instructions from the network and forwards the computer-readable program instructions for storage in the computer-readable storage medium in each computing / processing device.
[0112] The computer program instructions for performing the operations of the present disclosure may be assembly instructions, instruction set architecture (ISA) instructions, machine instructions, machine-related instructions, microcode, firmware instructions, state-setting data, or source code or object code written in any combination of one or more programming languages, including object-oriented programming languages such as Smalltalk, C++, etc., and conventional procedural programming languages such as the "C" language or similar programming languages. The computer-readable program instructions may be executed entirely on the user's computer, partially on the user's computer, executed as a stand-alone software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In the case of a remote computer, the remote computer may be connected to the user's computer through any type of network, including a local area network (LAN) or a wide area network (WAN), or may be connected to an external computer (e.g., through the Internet using an Internet service provider). In some embodiments, by using the status information of the computer-readable program instructions to customize an electronic circuit, such as a programmable logic circuit, a field-programmable gate array (FPGA), or a programmable logic array (PLA), the electronic circuit may execute the computer-readable program instructions to implement various aspects of the present disclosure.
[0113] The computer program product described herein may be implemented specifically in the form of hardware, software, or a combination thereof. In an alternative embodiment, the computer program product is specifically embodied as a computer storage medium. In another alternative embodiment, the computer program product is specifically embodied as a software product, such as a Software Development Kit (SDK), etc.
[0114] Aspects of the present disclosure are described herein with reference to the flowcharts and / or block diagrams of methods, apparatuses (systems), and computer program products according to embodiments of the present disclosure. It should be understood that each block of the flowcharts and / or block diagrams, and the combinations of blocks in the flowcharts and / or block diagrams, can be implemented by computer-readable program instructions.
[0115] These computer-readable program instructions can be provided to a processor of a general-purpose computer, special-purpose computer, or other programmable data processing apparatus to produce a machine such that the instructions, when executed by the processor of the computer or other programmable data processing apparatus, create means for implementing the functions / acts specified in one or more boxes of the flowchart and / or block diagram. These computer-readable program instructions may also be stored in a computer-readable storage medium that causes a computer, a programmable data processing apparatus, and / or other devices to function in a particular manner, such that the computer-readable medium storing the instructions comprises a manufacture including instructions that implement various aspects of the functions / acts specified in one or more boxes of the flowchart and / or block diagram.
[0116] The computer-readable program instructions may also be loaded onto a computer, other programmable data processing apparatus, or other devices to cause a series of operational steps to be performed on the computer, other programmable data processing apparatus, or other devices to produce a computer-implemented process such that the instructions executed on the computer, other programmable data processing apparatus, or other devices implement the functions / acts specified in one or more boxes of the flowchart and / or block diagram.
[0117] The flowcharts and block diagrams in the figures illustrate the architecture, functionality, and operation of possible implementations of systems, methods, and computer program products according to various embodiments of the present disclosure. In this regard, each block in the flowchart or block diagram may represent a module, a segment of code, or a portion of an instruction, and the module, segment of code, or portion of an instruction may include one or more executable instructions for implementing the specified logical function. In some alternative implementations, the functions noted in the boxes may occur out of the order noted in the figures. For example, two consecutive blocks may in fact be executed substantially in parallel, or they may sometimes be executed in the reverse order, depending on the functions involved. It should also be noted that each block in the block diagrams and / or flowcharts, and combinations of blocks in the block diagrams and / or flowcharts, can be implemented by a dedicated hardware-based system that performs the specified functions or acts, or by a combination of dedicated hardware and computer instructions.
[0118] Example embodiments have been disclosed herein, and although specific terms are employed, they are used in a generic and descriptive sense only and not for purposes of limitation. In some instances, it will be apparent to those skilled in the art that, unless otherwise expressly stated, the features, characteristics, and / or elements described in connection with a particular embodiment may be used singly or in combination with those described in connection with other embodiments. Accordingly, it will be understood by those skilled in the art that various forms and details may be changed without departing from the scope of the present disclosure as set forth in the appended claims.
Claims
1. A data extraction method, characterized in that, Including: Determining the number of target shards based on the data information of the data source table and the storage information of the target table in the data warehouse, wherein the target shards are used to extract data from the data source table, the data information of the data source table includes the total amount of data in the data source table and the data row size of the data source table, and the storage information of the target table includes the data block size of the target table and the table compression ratio of the target table; Determining the amount of data corresponding to each target shard based on the total amount of data in the data source table and the number of target shards; Extracting data from the data source table through the target shards according to the amount of data corresponding to each target shard, and storing the extracted data in the target table.
2. The data extraction method according to claim 1, wherein The determining the number of target shards based on the data information of the data source table and the storage information of the target table in the data warehouse includes: Calculating a first number of shards based on the total amount of data in the data source table, the data row size of the data source table, the data block size of the target table, and the table compression ratio of the target table, and determining the number of target shards based on the first number of shards and the default number of shards, where the default number of shards is determined according to the total amount of data in the data source table.
3. The data extraction method according to claim 2, wherein The determining the number of target shards based on the first number of shards and the default number of shards includes: Comparing the first number of shards with the default number of shards; In the case where the first number of shards is less than or equal to the default number of shards, determining the first number of shards as the number of target shards, and in the case where the first number of shards is greater than the default number of shards, determining the default number of shards as the number of target shards.
4. The data extraction method according to claim 1, wherein: The determining the amount of data corresponding to each target shard based on the total amount of data in the data source table and the number of target shards includes: Calculating the value obtained by dividing the total amount of data in the data source table by the number of target shards; Determining the amount of data corresponding to each target shard based on the calculated value.
5. The data extraction method according to claim 1, further including: Determining the degree of parallelism based on the number of target shards; Controlling the number of target shards for extracting data through the degree of parallelism.
6. The data extraction method according to claim 5, wherein The determining the degree of parallelism based on the number of target shards includes: In the case where the number of target shards is less than or equal to the default maximum degree of parallelism, determining the number of target shards as the degree of parallelism, In the case where the number of target shards is greater than the default maximum degree of parallelism, determining the default maximum degree of parallelism as the degree of parallelism.
7. The data extraction method according to claim 1, wherein Before extracting data from the data source table through the target shards, the method further includes: Matching the table structure of the target table with the table structure of the data source table, wherein the table structure at least includes fields and field types.
8. A data extraction device, characterized in that, Including: A calculation module, configured to determine the number of target shards based on the data information of the data source table and the storage information of the target table in the data warehouse, and determine the data volume corresponding to each target shard based on the total data volume of the data source table and the number of target shards, wherein the target shards are used to extract data from the data source table, the data information of the data source table includes the total data volume of the data source table and the data row size of the data source table, and the storage information of the target table includes the data block size of the target table and the table compression ratio of the target table; and A data extraction module, configured to extract data from the data source table through the target shards according to the data volume corresponding to each target shard, and store the extracted data in the target table.
9. An electronic device, characterized in that, Comprising: At least one processor; and A memory communicatively connected to the at least one processor; wherein, The memory stores one or more computer programs executable by the at least one processor, and the one or more computer programs are executed by the at least one processor, so that the at least one processor can execute the data extraction method according to any one of claims 1-7.
10. A computer-readable storage medium having a computer program stored thereon, characterized in that, The computer program, when executed by the processor, implements the data extraction method according to any one of claims 1-7.