Massive data lake-in method and device, electronic equipment and storage medium

Abstract

The present disclosure provides a large-scale data lake entry method and device, electronic equipment and storage medium, the method comprising: obtaining a system code input by a user; based on the system code, querying source end database information corresponding to the system code in a lake entry basic information table; based on the source end database type, the source end database information and the field information table associated with the source end database, generating a query statement of the source end database; based on the query statement, extracting corresponding query data from the source end database into a target table. The large-scale data lake entry method can improve the lake entry efficiency of the source end data and reduce errors in the lake entry process by linking the lake entry basic information table and the field information table, generating a query statement of the source end database, and extracting query data from the source end database into a target table based on the query statement.

Description

Technical Field

[0001] This disclosure pertains to the field of big data processing, specifically relating to methods and apparatuses for large-scale data import into lakes, electronic devices, and storage media. Background Technology

[0002] In today's rapidly developing information technology landscape, especially with the rise of data platforms, data lakes have become a mainstream big data processing technology. A data lake is a large warehouse storing a variety of raw data from an enterprise, which can be accessed, processed, analyzed, and transmitted. Data lakes support structured, semi-structured, and unstructured data. While improving data storage elasticity and quality, they can also reduce costs and data redundancy. Since data needs to be processed centrally in a data lake, data import becomes the first problem to solve. The difficulty of data import lies in the large number of tables in the source databases, the large volume of data in each table, the inconsistent table structures across different systems, and the fact that some are fact tables while others are dimension tables, making unified processing impossible. If currently available open-source data synchronization tools are used directly, many SQL (Structured Query Language) scripts for data extraction need to be written, and information about the source and target tables must be manually processed. Therefore, current data import methods suffer from low efficiency and are prone to errors. Summary of the Invention

[0003] This disclosure provides a method and apparatus for large-scale data import into lakes, electronic devices, and storage media to address the problems of low efficiency and error susceptibility in existing large-scale data import methods.

[0004] In a first aspect, this disclosure provides a method for large-scale data import into a lake, comprising: obtaining a system code input by a user; querying source database information corresponding to the system code in a basic information table for import into the lake based on the system code; generating a query statement for the source database based on the source database type, the source database information, and a field information table associated with the source database; and extracting corresponding query data from the source database into a target table based on the query statement.

[0005] In one embodiment of this disclosure, the field information table includes a source-end update time field. A method for generating a query statement for the source-end database based on the source-end database type, the source-end database information, and the field information table associated with the source-end database includes: determining the function type of the query statement based on the source-end database type; setting an index on the source-end update time field in the source-end database; performing format conversion on the source-end update time to obtain the converted source-end update time; and generating the query statement based on the function type, the converted source-end update time, the source-end database information, and the field information table associated with the source-end database.

[0006] In one embodiment of this disclosure, the source database information includes an extraction method. The method for generating the query statement based on the function type, the converted source database update time, the source database information, and the field information table associated with the source database includes: determining the extraction method based on the number of data rows in the source database and whether the target table has a primary key; the extraction method being incremental extraction or full extraction; when the extraction method is incremental extraction, generating the query statement based on the function type, the converted source database update time, the source database information, and the field information table associated with the source database; and when the extraction method is full extraction, generating the query statement based on the function type, the source database information, and the field information table associated with the source database.

[0007] In one embodiment of this disclosure, the method for extracting query data from the source database to a target table based on the query statement includes: periodically acquiring field change information of the source database; updating the field information table based on the field change information to obtain an updated field information table; updating the query statement based on the updated field information table to obtain an updated query statement; and extracting corresponding query data from the source database to the target table based on the updated query statement.

[0008] In one embodiment of this disclosure, the method for extracting corresponding query data from the source database to the target table based on the updated query statement includes: extracting corresponding query data from the source database based on the updated query statement; obtaining the data format corresponding to the query data in the target table based on a field mapping table; and writing the query data into the target table based on the data format.

[0009] In one embodiment of this disclosure, a method for generating a query statement for a source database based on the source database type, the source database information, and a field information table associated with the source database includes: establishing an initialization task, the initialization task being used to extract data from the source database into the target table; splitting the initialization task into subtasks based on the number of data entries and the creation date of the source database; and generating a query statement corresponding to the subtask based on the query date corresponding to the subtask, the source database type, the source database information, and the field information table associated with the source database.

[0010] In one embodiment of this disclosure, extracting corresponding query data from the source database to the target table based on the query statement includes: obtaining historical runtime data of the subtasks, and constructing a task duration mapping table based on the subtasks and the historical runtime data; sorting the subtasks in descending order of their historical runtime based on the task duration mapping table to obtain the sorted subtasks; assigning the sorted subtasks to the task group with the smallest current cumulative runtime, wherein the initial cumulative runtime of the task group is 0; randomly shuffling the task order within the task group to obtain a task group with a random task order; executing the task group with the random task order in a parallel manner between groups and a serial manner within groups; and extracting corresponding query data from the source database to the target table based on the tasks executed in the task group with the random task order and their corresponding query statements.

[0011] Secondly, embodiments of this disclosure provide a large-scale data import device, comprising: a code acquisition module for acquiring system codes input by a user; an information query module for querying source database information corresponding to the system codes in a basic information table for importing data; a statement generation module for generating a query statement for the source database based on the source database type, the source database information, and a field information table associated with the source database; and a data extraction module for extracting corresponding query data from the source database into a target table based on the query statement.

[0012] Thirdly, embodiments of this disclosure also provide a computer-readable storage medium having a computer program stored thereon. When executed by a processor, the computer program implements the large-scale data ingestion method described in any of the first aspects.

[0013] Fourthly, embodiments of this disclosure also provide an electronic device. The electronic device includes: a memory storing a computer program; and a processor communicatively connected to the memory, which, when the computer program is invoked, executes the large-scale data import method described in any of the first aspects.

[0014] The aforementioned large-scale data import method, apparatus, electronic devices, and storage media have the following beneficial effects:

[0015] By linking the basic information table and field information table for data entry into the lake, a query statement is generated for the source database, and query data is extracted from the source database into the target table based on the query statement. This data entry method can improve the efficiency of data entry into the lake from the source and the entry process is less prone to errors. Attached Figure Description

[0016] Figure 1 The diagram shown is a structural schematic of a computer terminal according to an embodiment of this disclosure.

[0017] Figure 2 The flowchart shown is a large-scale data ingestion method for lakes according to an embodiment of this disclosure.

[0018] Figure 3 This is a flowchart illustrating an implementation method for generating a query statement for the source database based on the source database type, the source database information, and a field information table associated with the source database, according to an embodiment of this disclosure.

[0019] Figure 4 The flowchart shown is an embodiment of the present disclosure of a method for generating the query statement based on the function type, the converted source update time, the source database information, and the field information table associated with the source database.

[0020] Figure 5 The flowchart shown is an embodiment of the present disclosure of a method for extracting query data from the source database to the target table based on the query statement.

[0021] Figure 6 The flowchart shown is an implementation method of retrieving corresponding query data from the source database to the target table based on the updated query statement according to an embodiment of this disclosure.

[0022] Figure 7 This is a flowchart illustrating an implementation method for generating a query statement for the source database based on the source database type, the source database information, and a field information table associated with the source database, according to an embodiment of this disclosure.

[0023] Figure 8 The diagram shown is a structural schematic of a data entry device for a lake according to an embodiment of this disclosure.

[0024] Figure 9 The diagram shown is a structural schematic of an electronic device according to an embodiment of this disclosure. Detailed Implementation

[0025] The following specific examples illustrate the implementation of this disclosure. Those skilled in the art can easily understand other advantages and effects of this disclosure from the content disclosed in this specification. This disclosure can also be implemented or applied through other different specific embodiments, and various details in this specification can also be modified or changed based on different viewpoints and applications without departing from the spirit of this disclosure. It should be noted that, unless otherwise specified, the following embodiments and features in the embodiments can be combined with each other.

[0026] It should be noted that the illustrations provided in the following embodiments are only schematic representations of the basic concept of this disclosure. Therefore, the illustrations only show the components related to this disclosure and are not drawn according to the number, shape and size of the components in actual implementation. In actual implementation, the form, quantity and proportion of each component can be changed according to the actual situation, and the layout of the components may also be more complex.

[0027] The technical solutions of the present disclosure embodiments will be described in detail below with reference to the accompanying drawings. The large-scale data import method provided in the embodiments of the present disclosure can be run on computer terminals or similar devices. Taking the operation on the computer terminal as an example... Figure 1 This is a hardware structure block diagram of the computer terminal, such as... Figure 1 As shown, the computer terminal 10 may include a processor 110 and a memory 120. The processor 110 may be a central processing unit, and the memory 120 is used to store data. Figure 1 The computer terminal 10 in the example is for illustrative purposes only and is not intended to limit the specific structure of the computer terminal 10.

[0028] In some embodiments, the computer terminal 10 may further include a communication transmission device 130 and an input / output device 140. The input / output device 140 may include a display screen 1410, a camera 1420, and buttons 1430.

[0029] In some embodiments, the memory 120 may be used to store computer programs, such as application software programs and modules. The memory 120 may include high-speed random access memory (RAM) and non-volatile memory, such as one or more magnetic storage devices, flash memory, or other non-volatile solid-state memory. In some instances, the memory 120 may further include memory remotely located relative to the processor, which can be connected to the computer terminal 10 via a network. Examples of such networks include, but are not limited to, the Internet, intranets, local area networks (LANs), mobile communication networks, and combinations thereof.

[0030] In some embodiments, the communication transmission device 130 can be used to receive or send data via a network, which may include a wireless network provided by the communication provider of the computer terminal 10. The communication transmission device 130 may include a NIC (Network Interface Controller), which can be connected to other network devices via a base station to communicate with the Internet.

[0031] The following will describe in detail the principles and implementation methods of the large-scale data lake ingestion method, apparatus, electronic equipment, and storage medium disclosed herein, so that those skilled in the art can understand the large-scale data lake ingestion method, apparatus, electronic equipment, and storage medium disclosed herein without creative effort.

[0032] Figure 2 This is a flowchart illustrating a large-scale data ingestion method according to an embodiment of the present disclosure. Figure 2 As shown, the large-scale data ingestion method provided in this embodiment can be executed by a processor, and the method includes the following steps S11 to S14.

[0033] Step S11: Obtain the system code input by the user.

[0034] Optionally, the system code may refer to the system code of the source end. The source end may have several systems, each with a corresponding database, and the system code is used to identify different systems.

[0035] Step S12: Based on the system code, query the source database information corresponding to the system code in the lake entry basic information table.

[0036] Optionally, the fields of the basic information table for entering the lake may include: system code, database user, table name, primary key, system name, update time field, table data volume, extraction method, etc.

[0037] Optionally, the source database information may include basic information such as database user, table name, primary key, incremental timestamp field, table data volume, and extraction method.

[0038] Optionally, the extraction method can be incremental extraction or full extraction. The extraction method can be flexibly set according to the amount of table data and the primary key situation. This embodiment will not elaborate on this.

[0039] Optionally, the source database type can be Oracle, MySQL, or SQL Server, etc.

[0040] Step S13: Generate a query statement for the source database based on the source database type, the source database information, and the field information table associated with the source database.

[0041] Optionally, the fields in the field information table include: system code, database user, database table name, field name, field type, field length, field precision, etc. The query statement is used to query the tables in the source database.

[0042] Step S14: Extract the corresponding query data from the source database to the target table based on the query statement.

[0043] Optionally, the method for extracting query data from the source database to the target table based on the query statement includes: obtaining the query data from the source database based on the query statement; and writing the query data into the target table. The target table refers to a data lake target table used to store data extracted from the source database. The corresponding query data may refer to the data retrieved from the source database according to the query statement.

[0044] Optionally, the method for extracting corresponding query data from the source database to the target table based on the query statement further includes: when the target table has not yet been created, generating a table creation statement for the target table based on the field information table, and creating the target table based on the table creation statement; and extracting the query data from the source database to the target table based on the query statement.

[0045] As described above, the large-scale data import method includes: obtaining system code input by the user; querying source database information corresponding to the system code in the basic information table for import; generating a query statement for the source database based on the source database type, the source database information, and a field information table associated with the source database; and extracting corresponding query data from the source database into a target table based on the query statement.

[0046] By linking the basic information table and field information table for data entering the lake, a query statement is generated for the source database. Based on the query statement, query data is extracted from the source database and added to the target table. This improves the efficiency of data entering the lake from the source and makes the process less prone to errors.

[0047] Figure 3 This is a flowchart illustrating a method for generating a query statement for the source database based on the source database type, the source database information, and a field information table associated with the source database, according to an embodiment of this disclosure. Figure 3 As shown, the method includes the following steps:

[0048] Step S21: Determine the function type of the query statement based on the source database type.

[0049] Optionally, the function type can be an Oracle function type, a MySQL function type, or a SQL Server function type, depending on the source database type. For example, if the source database type is MySQL, the function type can be a MySQL function type. This embodiment will not elaborate further on this.

[0050] Step S22: Set an index for the source update time field in the source database.

[0051] Step S23: Convert the source update time to obtain the converted source update time.

[0052] Optionally, converting the source update time into a format can refer to using the to_date function. For example, if the source update time is 2026-01-22, the converted source update time can be represented as to_date('2026-01-22', 'yyyy-mm-dd').

[0053] Step S24: Generate the query statement based on the function type, the converted source update time, the source database information, and the field information table associated with the source database.

[0054] For example, the query statement can be expressed as: select compid,ownerid,purdeptid,cstid,lastpurdate,firstpurdate,abc,firstnorevdate,lastpaydate,purlock,lockreason,createuser,createdate,modifyuser,modifydate,stopflag,createtype,sysoptdatefrom db.tablename where (sysoptdate >= to_date('2026-01-22', 'yyyy-mm-dd')).

[0055] Because the source table contains a large amount of data, the source table's update time (sysoptdate) needs to be indexed; otherwise, query execution will be very slow. Date conversion is used to ensure the index takes effect.

[0056] Figure 4 This is a flowchart illustrating a method for generating the query statement based on the function type, the converted source-side update time, the source-side database information, and a field information table associated with the source-side database, according to an embodiment of this disclosure. Figure 4 As shown, the method includes the following steps:

[0057] Step S31: Determine the extraction method based on the number of data entries in the source database and whether the target table has a primary key. The extraction method is incremental extraction or full extraction.

[0058] Optionally, the extraction method is determined based on the number of data rows in the source database and whether the target table has a primary key. The method for implementing the extraction method as incremental extraction or full extraction includes: if the number of data rows in the source database is greater than a preset number and the target table has a primary key, then the extraction method is incremental extraction; otherwise, the extraction method is full extraction.

[0059] Optionally, for tables with a large amount of source data, full extraction not only impacts the source database and bandwidth but also reduces extraction efficiency. Therefore, tables with more than 500,000 records will use incremental extraction, while those with less than 500,000 records will be extracted in full. During incremental extraction, the update logic in the database is based on the table's primary key. Data with a later update time will overwrite data with a later update time in the database. For example, for a table with the structure id, price, and sysoptdate, yesterday's data in the database was id = 1, price = 12, and sysoptdate = 2025-09-03 09:20:40. Today's extracted data is 1, 16, and 2025-09-04 10:20:40. Therefore, the data in the database will become the latest data: 1, 16, and 2025-09-04 10:20:40. Thus, incremental extraction is only suitable for tables with a primary key, updating the most recent data based on the primary key. For tables without a primary key, daily full deletion and insertion are possible without updating.

[0060] Step S32: When the extraction method is incremental extraction, the query statement is generated based on the function type, the converted source update time, the source database information, and the field information table associated with the source database.

[0061] Optionally, the query statement is a query statement under the incremental extraction, and the query statement includes the transformed source update time.

[0062] Step S33: When the extraction method is full extraction, the query statement is generated based on the function type, the source database information, and the field information table associated with the source database.

[0063] Optionally, the query statement is the query statement under the full extraction, and the query statement does not include the transformed source update time.

[0064] Figure 5 This is a flowchart illustrating an embodiment of the present disclosure of a method for extracting query data from the source database to a target table based on the query statement. For example... Figure 5 As shown, the method includes the following steps:

[0065] Step S41: Periodically obtain field change information from the source database.

[0066] Optionally, the field change information may be a field added to the source database or a field deleted from the source database.

[0067] Optionally, the method for periodically obtaining field change information from the source database includes: periodically obtaining field change information from the source database through a source database probing program. The source probing program can be flexibly configured according to actual conditions, and will not be elaborated upon in this embodiment.

[0068] Step S42: Update the field information table based on the field change information to obtain the updated field information table.

[0069] Optionally, the method for updating the field information table based on the field change information to obtain the updated field information table includes: when the field change information is that a field has been added to the source database, the name, type, precision, and whether it is a primary key of the new field are inserted into the field information table to obtain the updated field information table; when the field change information is that a field has been deleted from the source database, the deletion status of the deleted field in the field information table is set to deleted to obtain the updated field information table.

[0070] Optionally, the field change information may also be a change in the field type of the source database. The method for updating the field information table based on the field change information to obtain the updated field information table includes: when the field type of the source database changes, setting the type of the original field in the field information table to the newly changed field type to obtain the updated field information table.

[0071] Step S43: Update the query statement based on the updated field information table to obtain an updated query statement.

[0072] Optionally, the method for updating the query statement based on the updated field information table to obtain an updated query statement includes: when the query statement contains the deleted field from the source database, the updated query statement does not contain the deleted field from the source database; when the source database adds a field, the updated query statement adds the newly added field from the source database.

[0073] Step S44: Based on the updated query statement, extract the corresponding query data from the source database to the target table.

[0074] Optionally, the corresponding query data refers to the data retrieved from the source database based on the updated query statement.

[0075] Figure 6This is a flowchart illustrating an implementation method of retrieving corresponding query data from the source database and adding it to the target table based on the updated query statement, according to an embodiment of this disclosure. Figure 6 As shown, the method includes the following steps:

[0076] Step S51: Extract the corresponding query data from the source database based on the updated query statement.

[0077] Step S52: Obtain the data format corresponding to the queried data in the target table based on the field mapping table.

[0078] Optionally, the field mapping table is used to map field data formats between different databases. For example, if a field has a data format of bpchar (whitespace-filled character type) in database A and a data format of string (string type) in database B, then "bpchar": "string" is the field mapping between database A and database B.

[0079] Optionally, the field mapping table can be represented as:

[0080] CONVERT_COLUMN_TYPE_MYSQL = {"bpchar":"string","int": "bigint","int2": "bigint", "float": "double", "float4": "double","number": "decimal", "bigint": "bigint", "double": "double",

[0081] "tinyint": "bigint", "smallint": "bigint",

[0082] "varchar2": "string", "char": "string", "nchar": "string", "nvarchar2": "string",

[0083] "blob": "string", 'clob': "string", "varchar": "string", "text": "string",

[0084] "timestamp": "timestamp", "timestamp(6)": "timestamp", "date": "timestamp",

[0085] "datetime": "timestamp", "longtext": "string", "numeric": "double", "time_stamp": "timestamp"

[0086] , "int4": "bigint", "int8": "bigint", "bool": "boolean","mediumtext":"string","mediumint":"int","timestamptz":"timestamp","jsonb":"string"}

[0087] CONVERT_COLUMN_TYPE_ORACLE = {"int": "bigint", "int2":"bigint","float": "double","float4": "double", "number": "decimal", "bigint": "bigint","double": "double",

[0088] "tinyint": "bigint", "smallint": "bigint",

[0089] "varchar2": "string", "char": "string", "nchar": "string", "nvarchar2": "string","jsonb":"string",

[0090] "blob": "string", 'clob': "string", "varchar": "string", "text": "string",

[0091] "timestamp": "timestamp", "timestamp(6)": "timestamp", "date": "timestamp",

[0092] "datetime": "timestamp", "longtext": "string", "numeric": "double", "time_stamp": "timestamp"

[0093] , "int4": "bigint", "int8": "bigint", "bool": "boolean","bpchar":"string","mediumtext":"string","mediumint":"int","timestamptz":"timestamp"}

[0094] CONVERT_COLUMN_TYPE_SQLSERVER = {"bpchar":"string","int": "bigint","int2": "bigint", "float": "double", "float4": "double","number": "decimal", "bigint": "bigint", "double": "double",

[0095] "tinyint": "bigint", "smallint": "bigint",

[0096] "varchar2": "string", "char": "string", "nchar": "string", "nvarchar2": "string",

[0097] "blob": "string", 'clob': "string", "varchar": "string", "text": "string",

[0098] "timestamp": "timestamp", "timestamp(6)": "timestamp", "date": "timestamp",

[0099] "datetime": "timestamp", "longtext": "string", "numeric": "double", "time_stamp": "timestamp"

[0100] , "int4": "bigint", "int8": "bigint", "bool":"boolean"}

[0101] Step S53: Write the query data into the target table based on the data format.

[0102] Figure 7 This is a flowchart illustrating an embodiment of the present disclosure of a method for generating a query statement for the source database based on the source database type, the source database information, and a field information table associated with the source database. For example... Figure 7 As shown, the method includes the following steps:

[0103] Step S61: Establish an initialization task, which is used to extract data from the source database into the target table.

[0104] Step S62: Based on the number of data entries and creation date of the source database, the initialization task is split into sub-tasks.

[0105] Optionally, the method for splitting the initialization task into subtasks based on the number of data entries and creation date of the source database includes: when the number of data entries in the source database is greater than a preset number of data entries, the initialization task is split into subtasks based on the number of months between the creation date and the current date. For example, if there are 5 months between the creation date and the current date, there are 5 subtasks, each used to query data for its corresponding month. The preset number of data entries can be flexibly set according to actual conditions, such as 10 million entries, which will not be elaborated further in this embodiment.

[0106] Step S63: Generate a query statement corresponding to the subtask based on the query date corresponding to the subtask, the source database type, the source database information, and the field information table associated with the source database.

[0107] In one embodiment of this disclosure, extracting corresponding query data from the source database to the target table based on the query statement includes: obtaining historical runtime data of the subtasks, and constructing a task duration mapping table based on the subtasks and the historical runtime data; sorting the subtasks in descending order of their historical runtime based on the task duration mapping table to obtain the sorted subtasks; assigning the sorted subtasks to the task group with the smallest current cumulative runtime, the initial cumulative runtime of the task group being 0; randomly shuffling the task order within the task group to obtain a task group with a random task order; executing the task group with a random task order in a parallel manner between groups and a serial manner within groups; and extracting corresponding query data from the source database to the target table based on the tasks executed in the task group with a random task order and their corresponding query statements. The tasks executed in the task group with a random task order and their corresponding query statements can refer to the query statements used during the execution of the task. The cumulative runtime of the task group can refer to the sum of the cumulative runtimes of all tasks in the task group, and the historical runtime of a task can refer to the duration for which the task has run.

[0108] Optionally, the task order within the task group is randomly shuffled to obtain a task group with random task order; this includes: generating a random number sequence equal to the number of tasks in the task group; and mapping each task in the task group to a corresponding random number position to obtain the task group with random task order.

[0109] In one embodiment of this disclosure, a data ingestion method is provided, comprising two parts. The first part is metadata management, primarily maintained by the database administrator. After obtaining basic information such as the source database's connection and username / password, the database administrator runs a source-finding program. This program not only obtains the table names and field names of the source database but also deeply collects data on field precision, indexing, data distribution histograms, and the total amount of data in the table. Then, based on the number of data rows and the presence of a primary key, it determines whether to perform a full or incremental extraction. This configuration information constitutes the basic elements for generating ingestion tasks. The second part is a task generator. Using the information provided by the ingestion list, the script generator generates a JSON script executable by a computer program according to certain rules. The generated script can be executed on the DolphinScheduler.

[0110] The main function of metadata management is to obtain metadata from the source database through program probing, preparing for data synchronization and extraction. Metadata mainly consists of three parts:

[0111] 1. Source Tracker: The source tracker is a tool developed by the database administrator that can automatically retrieve metadata from the source database. Written in Python, the source tracker connects to the source database at a set time every morning (around 1 AM) to retrieve the basic information of the tables we want to include in the database and saves it to the relevant tables.

[0112] 2. The second part is the basic information table for data entering the lake. This table mainly maintains information that needs to be specified, such as system code, database user, table name, primary key, system name, update time field, table data volume, and data extraction method.

[0113] 3. The third part is the field information table. This table maintains information needed to generate query and table creation statements, such as system code, database user, database table name, field name, field type, field length, and field precision. The field information table and the basic information table for entering the lake are linked through system code and database table name.

[0114] 4. The source tracking program runs daily to obtain the latest DDL (Data Definition Language) changes from the source. If a new field is added to the source, information such as the new field name, field type, field precision, and whether it is a primary key will be inserted into the field information table. If a field is deleted from the source, the deletion status field of that field will be set to 'Y' in the field information table, so that deleted fields will not be referenced when generating tasks.

[0115] 5. The following is pseudocode for retrieving the number of data entries from the source:

[0116] def get_tab_segsize(check_sql, dbid, dbcur, subpgconn, subpgcur):

[0117] try:

[0118] dbcur.execute(check_sql)

[0119] outdata = dbcur.fetchall()

[0120] data1 = pd.DataFrame(outdata)

[0121] print(str(dbid) + " Data table segment size information successfully retrieved")

[0122] except Exception as err:

[0123] print(str(dbid) + " Failed to retrieve data table segment size information, %s" % err)

[0124] errsql = 'insert into meta_source_fetch_log values ​​(\'' +dbid + '\',\'Get Table Segsize failed\',\'' + str(

[0125] err) + '\',now())'

[0126] subpgcur.execute(errsql)

[0127] subpgconn.commit()

[0128] try:

[0129] output = StringIO()

[0130] data1.to_csv(output, sep='\t', index=False, header=False)

[0131] strdata = output.getvalue()

[0132] subpgcur.copy_from(StringIO(strdata), 'meta_source_tab_segsize',

[0133] columns=['dbid','dbowner','tablename','segsize'])

[0134] subpgconn.commit()

[0135] except Exception as err:

[0136] print(str(dbid) + " PG data table segment size information insertion failed, %s" % err)

[0137] errsql = 'insert into meta_source_fetch_log values ​​(\'' +dbid + '\',\'Insert Table Segsize failed\',\'' + str(

[0138] err) + '\',now())'

[0139] subpgcur.execute(errsql)

[0140] subpgconn.commit()

[0141] subpgconn.rollback()

[0142] Task generator:

[0143] Since we use the open-source DolphinScheduler platform for scheduling and the open-source data synchronization tool DataX for data synchronization, the tasks we generate need to be able to execute normally on DolphinScheduler and conform to the script format of DataX.

[0144] The task generator generates corresponding tasks based on the data maintained in the lake entry list and according to the algorithm rules. It then calls the interface of a third-party scheduling platform to generate executable lake entry scheduling tasks on the platform. The main process is as follows:

[0145] 1. Based on the system code generated according to the user's input, query the basic information of the system in the lake entry list table maintained in the first step. Generate the corresponding query statement based on the source database type; if it's Oracle, use Oracle functions; otherwise, use functions specific to that database.

[0146] 2. Based on the source fields in the data lake information table, generate table creation statements for the destination side that conform to the data lake format. If the target table has not yet been created, it will be created; otherwise, it will not be created.

[0147] 3. Lake entry scheduling typically begins in the early morning or late at night. However, if too many tables are extracted simultaneously, it will not only affect the source database but also consume network bandwidth and may even cause the database to crash. Therefore, we added a concurrency parameter when generating tasks to flexibly configure the extraction concurrency and avoid impacting the source database.

[0148] 4. Generate different inbound scripts based on the table extraction method. If it's a full extraction, add a statement to the script to clear the table: `truncate {db.table}`. This statement is used to clear the target table before rewriting. If it's incremental, this statement is not needed. During incremental extraction, the incremental timestamp in the inbound list is used to perform incremental queries and synchronization from the source table.

[0149] 5. Regardless of whether we perform full or incremental extraction, we must extract all data into the data lake when extracting from the source. Therefore, we will generate two scripts for incremental extraction: one for initialization and one for incremental extraction. The initialization task only needs to be executed once, while the incremental extraction task is scheduled periodically at a certain frequency.

[0150] 6. Our target tables are primarily analytical databases such as Hive and Hudi, while our source tables are relational databases such as Oracle, MySQL, and SQL Server. Therefore, many table field types are different. For example, Oracle's special field types such as Clob (Character Large Object) and Blob (Binary Large Object) are not available in other databases. To address this data type inconsistency, we have implemented field mapping. Please refer to the definitions above for the specific mapping logic: CONVERT_COLUMN_TYPE_MYSQL, CONVERT_COLUMN_TYPE_ORACLE, and CONVERT_COLUMN_TYPE_SQLSERVER.

[0151] 7. Some source fact tables have extremely large data volumes, with most core fact tables containing over 10 million records, and some even reaching hundreds of millions. In such cases, initializing everything at once would be very slow, impacting not only the source side but sometimes also causing termination due to expired source table snapshots. To address this, we've optimized the process by setting a data volume parameter in the code before generating the task. If the number of records in the source side exceeds this parameter, the initialization task is split. The splitting is based on the creation date provided by the source side, querying the data monthly from the current date to 2012, with each month constituting a task. Since most of the source side's business data is from 2012 onwards, the 2012 data is considered a single task, with the query condition being `createdate >= '2012-01-01'`.

[0152] 8. Due to network and database failures in the source system being beyond our control, failures in the data import task caused by these reasons can only be addressed by manually restarting the failed tasks after the source system resolves these issues. This not only increases our operational workload but also affects data consistency with downstream ETL (Extract-Transform-Load) computation tasks. To address this problem, we adopted two methods: First, we re-execute failed tasks every hour during the day until they succeed. Second, we set dynamic time conditions when generating tasks, querying the database for the latest success time of the task and then incrementally extracting data from the source system based on this date. This avoids data loss and maintains data consistency.

[0153] 9. Flexible scheduling can be configured based on the task's computation frequency. Some data ingestion tasks are executed once an hour, some once a day, and others once a month. The cron (scheduled task) expression for daily scheduling is as follows: 0 05 00 * * ? *, indicating that the data ingestion task starts at 5:00 AM every day.

[0154] 10. Task code saving: The generated task is connected to the scheduling platform via an interface, and the task is created directly on the scheduling platform to complete the scheduling task creation.

[0155] 11. The following are some of the parameter settings for generating the task:

[0156] db_links = 'oracle_xxx_xxx_link'.upper() # xxx_xxx is the system name to generate the task, oracle indicates that the source database is an Oracle database, and upper() is used to match the case sensitivity of the database metadata;

[0157] table_split_cnt = 20000000 # Indicates the upper limit of the data volume. If the number of table rows is greater than 20000000, it will be split when the initialization task is generated.

[0158] table_names = ['table_a','table_b'] # Setting this value will only generate tasks for the specified tables 'table_a' and 'table_b'. If not set, it will generate inbound tasks for all tables in the entire system.

[0159] group_cnt = 5 # Concurrency level, the number of data extraction tasks executed simultaneously. According to the DBA's recommendation, the concurrency level for most systems is 5.

[0160] To address the issue of significant differences in task execution time due to varying data volumes and extraction complexities among tables during large-scale data ingestion into a lake, where hundreds of tables are extracted in parallel on a scheduling platform, this embodiment also provides a load balancing algorithm for ingestion tasks based on a greedy strategy. Specifically, it includes the following steps:

[0161] a) Obtain historical runtime data for each lake entry task from the scheduling platform and construct a task-duration mapping table;

[0162] b) Calculate the total runtime of all tasks. If the total runtime exceeds a preset threshold (e.g., 60 minutes), dynamically increase the number of concurrent groups.

[0163] c) Sort the tasks in descending order of their historical runtime, that is, process the task with the longest runtime first;

[0164] d) Initialize k empty task groups, with each group having a cumulative runtime of 0;

[0165] e) Traverse the sorted task list and assign the current task to the task group with the shortest cumulative runtime each time. This step is essentially a greedy approximation algorithm for LPT (Longest Processing Time) to solve the problem of minimizing the maximum makespan.

[0166] f) Randomly shuffle the order of tasks within each task group to avoid a large number of tasks accessing the same source database at the same time, which could cause excessive load on the source database.

[0167] g) Submit the grouping results to the scheduling platform and execute them in a parallel manner between groups and a serial manner within groups.

[0168] The pseudocode for the algorithm is as follows:

[0169] def balance_task_groups(self,task_times, group_count=5):

[0170] sorted_tasks = sorted(task_times.items(), key=lambda item: item[1],reverse=True)

[0171] groups = [[] for _ in range(group_count)]

[0172] group_durations = [0.0] * group_count

[0173] for task_name, duration in sorted_tasks:

[0174] if math.isnan(duration):

[0175] closest_group = random.randint(0, group_count-1)

[0176] duration = 0

[0177] else:

[0178] closest_group = np.argmin(group_durations)

[0179] groups[closest_group].append(task_name)

[0180] group_durations[closest_group] += duration

[0181] print('group_durations=',group_durations)

[0182] groups = [np.array(random.sample(group, len(group))) for group ingroups]

[0183] return groups

[0184] Through the above steps, the total execution time of each task group is made as balanced as possible, and the maximum completion time (Makespan) is significantly reduced. Validated in a real production environment, the overall efficiency of data entry into the lake is improved by more than 30%, while avoiding load fluctuations caused by a large number of tasks accessing the source database simultaneously.

[0185] As described above, the data import method solves the problems of cumbersome and error-prone data synchronization task generation in previous methods. This solution mainly consists of two parts: metadata management and a task generator. Metadata management primarily provides the metadata information required by the scripts, while the task generator is responsible for retrieving data from the metadata and generating data synchronization tasks. The main benefits of this solution are:

[0186] 1. With numerous source systems and inconsistent table structures, maintaining this information using Excel is inefficient and inconvenient to manage. However, with metadata management, these problems are eliminated; simply configuring the source connection information allows for automatic detection of changes to the source database.

[0187] 2. It can reduce the time spent writing scripts manually, especially when the source table fields and types are unknown, or the SQL functions of the database are unknown, automatically generating query SQL and avoiding query errors.

[0188] 3. The generated tasks have a consistent structure and style, making the entire project easier to maintain and reducing errors caused by manual maintenance.

[0189] 4. For "large-scale" scenarios with hundreds of millions of data points, this invention changes the traditional, brute-force approach of fetching the entire dataset. By using a sharding algorithm based on creation date and timestamp, the system can automatically break down a large task into multiple parallel subtasks (channels) and dynamically adjust the concurrency based on the load of the source database. This not only avoids the problems of source database timeouts or table locking caused by long transaction queries, but also maximizes the utilization of network bandwidth, significantly improving the efficiency of ingesting massive amounts of data into the data lake.

[0190] 5. Conditional parameters are dynamically set to avoid inconsistencies with downstream data caused by multiple consecutive days of task failures.

[0191] 6. Integrate with third-party cloud platforms to directly write the generated code into the cloud platform for convenient subsequent scheduling.

[0192] Figure 8 This is a schematic diagram illustrating the structure of the data ingress device 800 according to an embodiment of this disclosure. Figure 8 As shown, the data import device 800 includes:

[0193] The code acquisition module 810 is used to acquire system codes input by the user.

[0194] The information query module 820 is used to query the source database information corresponding to the system code in the basic information table of lake entry based on the system code.

[0195] The statement generation module 830 is used to generate a query statement for the source database based on the source database type, the source database information, and the field information table associated with the source database.

[0196] The data extraction module 840 is used to extract corresponding query data from the source database to the target table based on the query statement.

[0197] In the data import device 800, the code acquisition module 810 and Figure 2 In the data import method, step S11 corresponds one-to-one, the information query module 820 corresponds one-to-one with step S12, the statement generation module 830 corresponds one-to-one with step S13, and the data extraction module 840 corresponds one-to-one with step S14.

[0198] Figure 9 The diagram shown is a structural schematic of an electronic device according to an embodiment of this disclosure. Figure 9 As shown, the electronic device provided in this embodiment includes a processor and a memory. The memory is configured to store an executable program. The processor is configured to execute the program to cause the electronic device to perform the large-scale data ingestion method according to any of the preceding claims.

[0199] Preferably, the processor can be a general-purpose processor, including a central processing unit (CPU), a network processor (NP), etc.; it can also be a digital signal processor (DSP), an application-specific integrated circuit (ASIC), a field-programmable gate array (FPGA), or other programmable logic devices, discrete gate or transistor logic devices, or discrete hardware components. The memory can be implemented by any type of volatile or non-volatile storage device or a combination thereof, such as static random access memory (SRAM), electrically erasable programmable read-only memory (EEPROM), erasable programmable read-only memory (EPROM), programmable read-only memory (PROM), read-only memory (ROM), magnetic storage, flash memory, magnetic disk, or optical disk.

[0200] This embodiment also includes one or more of the following: multimedia components, input / output (I / O) interfaces, and communication components.

[0201] The multimedia component may include a screen and an audio component. The screen may be, for example, a touchscreen, and the audio component is configured to output and / or input audio signals. For example, the audio component may include a microphone configured to receive external audio signals. The received audio signals may be further stored in memory or transmitted via a communication component. The audio component also includes at least one speaker configured to output audio signals. The I / O interface provides an interface between the processor and other interface modules, such as a keyboard, mouse, buttons, etc. These buttons may be virtual or physical buttons. The communication component is configured for wired or wireless communication between the device and other devices. Wireless communication may include Wi-Fi, Bluetooth, Near Field Communication (NFC), 2G, 3G, or 4G, or a combination thereof; therefore, the corresponding communication component may include a Wi-Fi module, a Bluetooth module, or an NFC module.

[0202] In the several embodiments provided in this disclosure, it should be understood that the disclosed systems, apparatuses, or methods can be implemented in other ways. For example, the apparatus embodiments described above are merely illustrative; for instance, the division of modules / units is only a logical functional division, and in actual implementation, there may be other division methods. For example, multiple modules or units may be combined or integrated into another system, or some features may be ignored or not executed. Furthermore, the coupling or direct coupling or communication connection shown or discussed may be through some interfaces; the indirect coupling or communication connection of apparatuses or modules or units may be electrical, mechanical, or other forms.

[0203] The modules / units described as separate components may or may not be physically separate. The components shown as modules / units may or may not be physical modules; that is, they may be located in one place or distributed across multiple network units. Some or all of the modules / units can be selected to achieve the objectives of the embodiments of this disclosure, depending on actual needs. For example, the functional modules / units in the various embodiments of this disclosure may be integrated into one processing module, or each module / unit may exist physically separately, or two or more modules / units may be integrated into one module / unit.

[0204] Those skilled in the art will further recognize that the units and algorithm steps of the various examples described in conjunction with the embodiments disclosed herein can be implemented in electronic hardware, computer software, or a combination of both. To clearly illustrate the interchangeability of hardware and software, the components and steps of the various examples have been generally described in terms of functionality in the foregoing description. Whether these functions are implemented in hardware or software depends on the specific application and design constraints of the technical solution. Those skilled in the art can use different methods to implement the described functions for each specific application, but such implementation should not be considered beyond the scope of this disclosure.

[0205] This disclosure also provides a computer-readable storage medium storing a computer program thereon. When executed by a processor, the computer program implements the large-scale data lake ingestion method, apparatus, electronic device, and storage medium provided in this disclosure. Those skilled in the art will understand that all or part of the steps in the methods of the above embodiments can be implemented by a program instructing a processor. The program can be stored in a computer-readable storage medium, which is a non-transitory medium, such as random access memory, read-only memory, flash memory, hard disk, solid-state drive, magnetic tape, floppy disk, optical disk, and any combination thereof. The storage medium can be any available medium accessible to a computer or a data storage device such as a server or data center that integrates one or more available media. The available medium can be a magnetic medium (e.g., floppy disk, hard disk, magnetic tape), an optical medium (e.g., digital video disc (DVD)), or a semiconductor medium (e.g., solid-state disk (SSD)).

[0206] The above embodiments are merely illustrative of the principles and effects of this disclosure and are not intended to limit this disclosure. Any person skilled in the art can modify or alter the above embodiments without departing from the spirit and scope of this disclosure. Therefore, all equivalent modifications or alterations made by those skilled in the art without departing from the spirit and technical concept disclosed in this disclosure should still be covered by the claims of this disclosure.

Claims

1. A method for large-scale data import into a lake, characterized in that, include: The system code that retrieves user input; Based on the system code, query the source database information corresponding to the system code in the lake entry basic information table; Based on the source database type, the source database information, and the field information table associated with the source database, a query statement for the source database is generated; Based on the query statement, the corresponding query data is extracted from the source database and added to the target table.

2. The data import method for a lake according to claim 1, characterized in that, The field information table includes a source update time field. The method for generating a query statement for the source database based on the source database type, the source database information, and the field information table associated with the source database includes: The function type of the query statement is determined based on the source database type; Set an index on the source update time field in the source database; The source update time is format-converted to obtain the converted source update time. The query statement is generated based on the function type, the converted source update time, the source database information, and the field information table associated with the source database.

3. The data import method for the lake according to claim 2, characterized in that, The source database information includes the extraction method. The method for generating the query statement based on the function type, the converted source update time, the source database information, and the field information table associated with the source database includes: The extraction method is determined based on the number of data entries in the source database and whether the target table has a primary key. The extraction method is either incremental extraction or full extraction. When the extraction method is incremental extraction, the query statement is generated based on the function type, the converted source update time, the source database information, and the field information table associated with the source database. When the extraction method is full extraction, the query statement is generated based on the function type, the source database information, and the field information table associated with the source database.

4. The data import method for a lake according to claim 3, characterized in that, The method for extracting query data from the source database to the target table based on the query statement includes: Periodically retrieve field change information from the source database; The field information table is updated based on the field change information to obtain the updated field information table; The query statement is updated based on the updated field information table to obtain the updated query statement; Based on the updated query statement, the corresponding query data is extracted from the source database and added to the target table.

5. The data import method according to claim 4, characterized in that, The method for extracting corresponding query data from the source database to the target table based on the updated query statement includes: Based on the updated query statement, extract the corresponding query data from the source database; The data format corresponding to the queried data in the target table is obtained based on the field mapping table; The query data is written into the target table based on the data format.

6. The data import method for a lake according to claim 1, characterized in that, The method for generating a query statement for the source database based on the source database type, the source database information, and the field information table associated with the source database includes: An initialization task is established, which is used to extract data from the source database into the target table; Based on the number of data entries and creation date of the source database, the initialization task is split into subtasks. Based on the query date corresponding to the subtask, the source database type, the source database information, and the field information table associated with the source database, a query statement corresponding to the subtask is generated.

7. The data import method for a lake according to claim 6, characterized in that, Extracting corresponding query data from the source database to the target table based on the query statement includes: Obtain the historical runtime data of the subtask, and construct a task duration mapping table based on the subtask and the historical runtime data; Based on the task duration mapping table, sort the subtasks in descending order of their historical runtime to obtain the sorted subtasks. The subtasks sorted in descending order are assigned to the task group with the smallest current cumulative runtime, and the initial cumulative runtime of the task group is 0. The task order within the task group is randomly shuffled to obtain a task group with a random task order. The task groups with random task order are executed in a parallel manner between groups and in a serial manner within groups. Based on the tasks executed in the task group with the random task order and their corresponding query statements, the corresponding query data is extracted from the source database and added to the target table.

8. A large-scale data import device for a lake, characterized in that, include: The code acquisition module is used to acquire system codes input by the user. The information query module is used to query the source database information corresponding to the system code in the basic information table of lake entry based on the system code; The statement generation module is used to generate a query statement for the source database based on the source database type, the source database information, and the field information table associated with the source database. The data extraction module is used to extract corresponding query data from the source database and insert it into the target table based on the query statement.

9. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the computer program is executed by the processor, it implements the data ingestion method according to any one of claims 1 to 7.

10. An electronic device, characterized in that, include: The memory is configured to store a computer program; The processor is electrically coupled to the memory and configured to perform the data ingestion method according to any one of claims 1 to 7.

Images