Data import method and device, computer equipment and computer readable storage medium

Abstract

The invention discloses a data importing method and device, computer equipment and a computer readable storage medium. The method comprises the steps of generating a first SQL statement according to parameters of an import task of a preset database and a template of the import task, and filling a preset array variable with the first SQL statement; establishing connection with the database to obtain a cursor object for operating the database; generating a target date partition name set and a target date partition value set of the imported task in the template; based on the cursor object, querying a database according to the target date partition name set and the target date partition value set to obtain a target column name set of a target import table of the import task; and according to the array variable, the target date partition name set, the target date partition value set and the target column name set, the import task is generated, and the import task is input into the database, so that a large batch of data is rapidly imported into the database, and the efficiency of importing the data into the database is improved.

Description

Technical Field

[0001] The present application relates to the field of data processing technology, and in particular to a data import method, device, computer equipment and computer-readable storage medium. Background Art

[0002] StarRocks is an open source, next-generation, ultra-fast, full-scenario MPP database that uses an elastic MPP architecture and can efficiently support a variety of data analysis scenarios, including multi-dimensional analysis, real-time analysis, and high-concurrency analysis of large data volumes.

[0003] StarRocks database provides a Broker Loa import method based on the MySQL protocol, which can facilitate users to import large quantities of data from HDFS (Hadoop Distributed File System) into the StarRocks database. Broker Loa can support the import of multiple data files at a time, and can ensure the atomicity of a single import transaction, that is, multiple data files imported at a time are all successful or all failed, and there will be no partial import success and partial import failure. Although the Broker Loa import method is efficient, the StarRocks database does not support the automatic generation of Broker Loa import tasks, and when there are a large number of tables, such as hundreds or thousands of tables that need to be imported into the StarRocks database, a lot of manpower and time are required to develop the Broker Loa import task, which is inefficient. Summary of the invention

[0004] In view of the deficiencies in the prior art, the present application provides a data import method, apparatus, computer device and computer-readable storage medium, aiming to solve the technical problem of low efficiency of data import in the prior art.

[0005] To solve the above problems, in a first aspect, an embodiment of the present application provides a data import method, which includes:

[0006] Generate a first SQL statement for executing the import task according to the parameters of the preset database import task and the template of the import task, and fill the first SQL statement into the preset array variable;

[0007] Establishing a connection with the database and obtaining a cursor object for operating the database;

[0008] Generating a target date partition name set and a target date partition value set of the import task in a template of the import task;

[0009] Based on the cursor object, query the database according to the target date partition name set and the target date partition value set of the import task to obtain the target column name set of the target import table of the import task;

[0010] The import task is generated according to the array variable, the target date partition name set, the target date partition value set and the target column name set, and the import task is input into the database to execute the import task in the database to realize importing the target data into the database.

[0011] In a second aspect, the embodiment of the present application further provides a data import device, which includes:

[0012] A first generating unit, configured to generate a first SQL statement for executing the import task according to the parameters of the preset database import task and the template of the import task, and fill the first SQL statement into a preset array variable;

[0013] A connection unit, used to establish a connection with the database and obtain a cursor object for operating the database;

[0014] A second generating unit, configured to generate a target date partition name set and a target date partition value set of the import task in a template of the import task;

[0015] An acquisition unit, configured to query the database based on the cursor object and according to the target date partition name set and the target date partition value set of the import task, so as to acquire a target column name set of the target import table of the import task;

[0016] The third generating unit is used to generate the import task according to the array variable, the target date partition name set, the target date partition value set and the target column name set, and input the import task into the database to execute the import task in the database to realize importing the target data into the database.

[0017] In a third aspect, an embodiment of the present application further provides a computer device, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein when the processor executes the computer program, the data import method as described in the first aspect above is implemented.

[0018] In a fourth aspect, an embodiment of the present application further provides a computer-readable storage medium, wherein the computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the processor executes the data import method described in the first aspect above.

[0019] The data import method, device, computer equipment and computer-readable storage medium provided by the embodiments of the present application use the parameters of the import task of the database and the initial template to generate the target template of the import task, then parse the target template, and fill the target SQL statement parsed from the target template into the preset array variable, after which a connection can be established with the database and a cursor for operating the database can be opened, and a target date partition name set and a target date partition value set of the import task are generated in the template of the import task, and at the same time, a target column name set of the target data import table of the import task is obtained from the database through the cursor, and then the import task can be generated, thereby quickly importing large quantities of data into the database, greatly improving the efficiency of importing data into the database and reducing the labor cost of data import. BRIEF DESCRIPTION OF THE DRAWINGS

[0020] In order to more clearly illustrate the technical solutions of the embodiments of the present application, the drawings required for use in the description of the embodiments will be briefly introduced below. Obviously, the drawings described below are some embodiments of the present application. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying any creative work.

[0021] Figure 1 A flowchart of a data import method provided in an embodiment of the present application;

[0022] Figure 2 A schematic diagram of a process of a data import method provided in an embodiment of the present application;

[0023] Figure 3 Another schematic diagram of the data import process provided by the embodiment of the present application;

[0024] Figure 4 Another schematic diagram of the data import process provided by the embodiment of the present application;

[0025] Figure 5 Another schematic diagram of the data import process provided by the embodiment of the present application;

[0026] Figure 6 Another schematic diagram of the data import process provided by the embodiment of the present application;

[0027] Figure 7 Another schematic diagram of the data import process provided by the embodiment of the present application;

[0028] Figure 8 A schematic block diagram of a data import device provided in an embodiment of the present application;

[0029] Fig. 9A schematic block diagram of a computer device provided in an embodiment of the present application. DETAILED DESCRIPTION

[0030] The following will be combined with the drawings in the embodiments of the present application to clearly and completely describe the technical solutions in the embodiments of the present application. Obviously, the described embodiments are part of the embodiments of the present application, not all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of this application.

[0031] It should be understood that when used in this specification and the appended claims, the terms "include" and "comprises" indicate the presence of described features, integers, steps, operations, elements and / or components, but do not exclude the presence or addition of one or more other features, integers, steps, operations, elements, components and / or combinations thereof.

[0032] It should also be understood that the terms used in this application specification are only for the purpose of describing specific embodiments and are not intended to limit the application. As used in this application specification and the appended claims, unless the context clearly indicates otherwise, the singular forms "a", "an" and "the" are intended to include plural forms.

[0033] It should be further understood that the term “and / or” used in the specification and appended claims refers to any combination and all possible combinations of one or more of the associated listed items, and includes these combinations.

[0034] The data import method provided in the embodiment of the present application is applied to a terminal device, and the method executes the data import method through application software installed in the terminal device. The terminal device can be an electronic device such as a desktop computer, a laptop computer, a tablet computer or a mobile phone.

[0035] At the same time, the data import method provided in this application is mainly used to import large batches of data from HDFS into the StarRocks database, and the import task can be a Broker Loa import task. At the same time, the data import method provided in this application can be applicable to data import of databases that support SQL (Structured Query Language), and is not limited to importing large batches of data from HDFS into the StarRocks database.

[0036] It should be noted that the application scenario of the above embodiment is only an example. The services and scenarios described in the embodiment of this application are intended to more clearly illustrate the technical solution of the embodiment of this application, and do not constitute a limitation on the technical solution provided by the embodiment of this application. It is known to those skilled in the art that with the evolution of the system and the emergence of new business scenarios, the technical solution provided by the embodiment of this application is also applicable to similar technical problems. The following is a detailed description.

[0037] It should also be noted that the description order of the following embodiments is not intended to limit the preferred order of the embodiments. The data import method is described in detail below.

[0038] The following is a detailed description of the data import method. Figure 1 , Figure 1 A flowchart of a data import method provided in an embodiment of the present application.

[0039] like Figure 1 As shown, the method includes the following steps S110 to S150.

[0040] S110, generating a first SQL statement for executing the import task according to the parameters of the preset database import task and the template of the import task, and filling the first SQL statement into a preset array variable.

[0041] In this embodiment, the database can be a StarRocks database, and the import task can be a Broker Loa import task. The import task is used to import data from HDFS into the StarRocks database. The template of the import task can be pre-customized. The template includes a SQL statement (first SQL statement) for executing the import task and a number of definitions of parameters that define the import task. Therefore, it is only necessary to input the parameters of the import task into the pre-customized template, and the first SQL statement for executing the import task can be generated from the template, and then the first SQL statement can be filled into the preset array variable. Among them, the array variable can be regarded as an atomic operation, and the SQL operation for executing the import task can be completed by the data variable. The first SQL statement is used for the import task to import data into the database, for example, it can realize the import of data from HDFS into the StarRocks database.

[0042] In other inventive embodiments, Figure 2 As shown, step S110 includes steps S111, S112 and S113.

[0043] S111, constructing a template of the import task according to the data import rule of the database; wherein the template of the import task includes a first SQL statement for executing the import task and a definition of parameters of the import task;

[0044] S112, inputting the parameters of the import task into the template of the import task;

[0045] S113: Parse the template of the import task, and fill the parsed first SQL statement into a preset array variable.

[0046] In this embodiment, the template of the import task is constructed by the data import rules of the database. If the database is a StarRocks database, the template can be constructed according to the Broker Loa import scheme rules of the StarRocks database.

[0047] Specifically, several parameters defining the import task in the import task template include: table name table_name, path hdfs_location, deadline partition value inc_date, synchronization timestamp cur_time, date partition name inc_date_name, date partition value set inc_date_set, date partition type inc_type, multi-level partition name set inc_source_cols, multi-level partition path inc_source_path, etc.

[0048] The table name table_name refers to the specific name of the target import table whose target data needs to be synchronized from HDFS to the StarRocks database; the path hdfs_location is the storage location of the target data on HDFS, where the storage path of the target data does not contain specific date partition information; the cutoff date partition value inc_date is the date value of the most recent synchronization required for the date partition of the target import table; the synchronization timestamp cur_time is used to ensure that the import tag load label is unique in the StarRocks database; the date partition name inc_date_name is the name of the date partition on the HDFS path; the date partition value set inc_date_set is the value set corresponding to the date partition on the HDFS path, which is mainly used to control the synchronization range of the target data. Usually each value is in date format, such as daily YYYYMMDD, weekly YYYYWW, monthly YYYYMM, etc.); the date partition type inc_type is used to indicate whether the data synchronization is performed on a monthly, daily or weekly basis; the multi-level partition name set inc_source_cols is the name set of the second-level, third-level, etc. partitions on the HDFS path; the multi-level partition path inc_source_path is the path location of the second-level, third-level, etc. partitions on the HDFS.

[0049] Meanwhile, the parameters required for generating the import task may include several parameters defining the import task in the template of the import task, or several parameters defining the import task in the template of the partial import task. Among them, the parameters that must be input in the import task include: the database table name db_table_name, the path hdfs_location, the cut-off date partition value inc_date, and the date partition type inc_type. The parameters that can be input include the date offset inc_pers, the date partition name inc_date_name, and the multi-level partition name inc_level_name.

[0050] The database table name db_table_name is the specific database table name of the target import table, for example, it can be dm_abc.table_name_ab; the path hdfs_location can be, for example, hdfs: / / sfbd / hive / warehouse / dm / dm_abc / table_name_abc; the date partition type inc_type includes three types: DAY, MONTH, and WEEK; the cutoff date partition value inc_date is filled in according to the time parameter definition that conforms to Unix / Linux, which includes daily T-1 output number, monthly T-1 output number and weekly T-1 output number. The daily T-1 output number can be filled in with $[time(yyyyMMdd, -1d)], and the weekly T-1 output number can be filled in with $[time(yyyyMMdd, -7d)]. Among them, the weekly T-1 output number is filled with the daily date 7 days before the current day, and the monthly T-1 output number can be filled in with $[time(yyyyMM, -1M)].

[0051] The date offset inc_pers is used to determine the time range of the target data to be imported into this target import table. It can be filled in or not. If it is not filled in, the default value is 1, and according to the date partition type, the target data of 1 day, 1 week or 1 month can be imported into the StarRocks database by default; the date partition name inc_date_name can be filled in according to the actual date partition name of the table. If it is not filled in, a default value will be given according to the date partition type, such as inc_day (day), inc_week (week) and inc_month (month); the multi-level partition name inc_level_name is empty by default. If there are other partition levels besides the date partition, the partition name text can be filled in according to the comma separation rule, such as INC_A, INC_B, etc.

[0052] In other inventive embodiments, Figure 3 As shown, step S113 includes steps S1131 and S1132.

[0053] S1131, reading the template of the import task according to the file name of the template of the import task;

[0054] S1132. Extract the first SQL statement from the template of the import task, and fill the first SQL statement into the array variable.

[0055] Specifically, before parsing the template with the parameters of the import task, you need to pre-define the get_load_sqls function, pass the template name file_name as an input parameter, and pre-define the array variable sqls. After the parameters of the import task are input into the template, the template name can be directly passed as an input parameter, and the file content can be read through the read method. The split method uses a semicolon to separate each SQL statement in the template, and the strip method removes extra blank lines and spaces. Then, the first SQL statement for executing the import task can be extracted. After the first SQL statement is extracted, the append method can be used to fill it into the array variable sqls. Among them, the array variable can be regarded as an atomic operation, and the SQL operation of executing the import task can be completed by the data variable. The extracted first SQL statement can include multiple SQL statements.

[0056] S120: Establish a connection with the database and obtain a cursor object for operating the database.

[0057] In this embodiment, the database is a StarRocks database. When establishing a connection with the database, the connection can be established with the database through the Python third-party library pymysql, so that the cursor function in the database can be enabled, and then the cursor object for operating the database can be obtained, and then the operation on the database can be performed. Among them, the cursor is a data buffer opened by the system for the user, which stores the execution results of SQL statements. Each cursor area has a name. The user can use SQL statements to obtain records from the cursor one by one, assign them to the main variable, and hand them over to the main language for further processing.

[0058] S130: Generate a target date partition name set and a target date partition value set of the import task in a template of the import task.

[0059] Specifically, before generating the target date partition value set of the import task, the get_inc_date function needs to be defined in the template in advance, and the parameters str_inc_date, offset, and date partition type inc_type are passed in as input parameters. The get_inc_date function is used to process the date and generate the partition date name set. Among them, the parameter str_inc_date is assigned by the template required input parameter cutoff date partition value inc_date; the parameter offset is assigned by the template optional parameter date offset inc_pers through a for loop, and the step length of each loop is 1; at the same time, the default parameter date partition type inc_type = 'DAY'.

[0060] After entering the parameters of the import task into the template, you can call the get_inc_date function and pass the parameters str_inc_date, offset, and date partition type inc_type as input parameters. Then, calling the get_inc_date function can return the target date partition name set and target date partition value set of the import task. Among them, when the date partition type inc_type is week, an additional date partition value set inc_date_set_sr for the StarRocks database will be returned, that is, the set of weekly partition values. Weekly partition values ​​will be named in the YYYYWW (year-week) format on HDFS, where WW represents one of the week names from 01 to 52, but in the StarRocks database, the weekly name is named 02-53, so a +1 conversion is required here. At the same time, weekly partition values ​​are saved in the YYYYWW (year-week) format on HDFS, but the corresponding name format in the StarRocks database is YYYY_WW, so a format conversion process is also required to meet the naming standards in the StarRocks database.

[0061] The specific internal processing process of the get_inc_date function is as follows:

[0062] When the date partition type inc_type = 'DAY', that is, the date partition type is day, the datetime library function strptime method can be used to convert the parameter str_inc_date to the variable datecode in the %Y%m%d date format (year-month-day); then the datetime library function timedelta method's input parameter days = -1*offset is used for the variable datecode to calculate the offset date partition name, and the result is assigned to the variable inc_day, and the variable inc_day is converted to the %Y%m%d date format (year-month-day); finally, the variable inc_day is output as the function return value.

[0063] When the date partition type inc_type = 'WEEK', that is, the date partition type is week, the datetime library function strptime method can be used to convert str_inc_date to the variable datecode in the %Y%m%d date format (year, month, day); then the variable datecode is calculated using the input parameter weeks = -1*offset of the datetime library function timedelta method to the offset date partition name, and the result is assigned to the variable inc_week, and the variable inc_week is converted to the %Y%W date format (year-week); finally, the variable inc_week is output as the function return value.

[0064] When the date partition type inc_type = 'MONTH', that is, the date partition type is month, you can use the datetime library function strptime method to convert str_inc_date to the variable datecode in %Y%m date format (year and month), and then use the format function to concatenate the variables year and month, assign the result value to the variable inc_month, and finally output the variable inc_month as the function return value. Among them, if offset is less than the month attribute value of the variable datecode, the variable year is equal to the year attribute value of datecode, and the variable month is equal to the result value of the month attribute value of datecode minus offset; if offset is greater than the month attribute value of the variable datecode, the variable year is equal to the year attribute value of datecode minus one, minus the result value of the result of offset minus the month attribute value of datecode divided by 12, that is, year=date_code.year-1-(offset-date_code.month) / / 12, and the variable month is equal to the result value of 12 minus offset minus the month attribute value of datecode modulo 12, that is, month=12-(offset-date_code.month)%12.

[0065] In one embodiment, if the parameters for generating the import task include the multi-level partition name inc_level_name, it is also necessary to generate a multi-level partition name set, a multi-level partition column name set and a multi-level partition HDFS path address of the import task in the template, and then the import task can be generated according to the group variable, the target date partition name set, the target date partition value set, the target column name set, the multi-level partition name set, the multi-level partition column name set and the multi-level partition HDFS path address.

[0066] Among them, before generating the name set of the multi-level partitions of the import task, the column name set of the multi-level partitions, and the HDFS path address of the multi-level partitions, the get_inc_source function needs to be defined in advance. The get_inc_source function is used to process multi-level partitions except date partitions. The multi-level partition name inc_level_name is passed in as an input parameter, and the get_inc_source function is called to generate the name set of the multi-level partitions of the import task, the column name set of the multi-level partitions, and the HDFS path address of the multi-level partitions.

[0067] The specific process of the get_inc_source function is as follows:

[0068] When the multi-level partition name inc_level_name is None, the multi-level partition name set inc_source_cols is set to an empty array, the multi-level partition path inc_source_path and the variable inc_source_etl are set to empty strings, and they are output as the function return values; when the multi-level partition name inc_level_name is not None, the split method is used to process the multi-level partition name inc_level_name, and the separated values ​​are saved in the multi-level partition name set inc_source_cols, and then the multi-level partition name set inc_source_cols is iterated, and then the join method is used to concatenate the strings, and the generated results are saved in the multi-level partition path inc_source_path and the variable inc_source_etl respectively, and finally the multi-level partition name set inc_source_cols, the multi-level partition path inc_source_path and the variable inc_source_etl are output as the function return values. For example, the multi-level partition name inc_level_name = INC_A, INC_B, the multi-level partition name set inc_source_cols = ['INC_A', 'INC_B'], the multi-level partition path inc_source_path = 'INC_A = * / INC_B = *', and the variable inc_source_etl = 'INC_A = INC_A, INC_B = INC_B'.

[0069] S140. Based on the cursor object, query the database according to the target date partition name set and the target date partition value set of the import task to obtain a target column name set of the target import table of the import task.

[0070] In this embodiment, the target column name set is a set of all column names of the target import table, and the target column name set can be obtained from the database through a cursor object. Specifically, after establishing a connection with the database, the cursor function in the database can be enabled, and then a cursor object can be obtained, so that the execute() method in the cursor object can be used to execute the target date partition name set and the target date partition value set of the import task to operate the database, and then the target column name set of the target import table of the import task can be obtained.

[0071] In other inventive embodiments, Figure 4 As shown, step S140 includes steps S141 and S142.

[0072] S141, based on the cursor object, operating the database according to the target date partition name set and the target date partition value set of the import task, so as to obtain an initial column name set of the target import table of the import task;

[0073] S142. Delete the partition column names in the initial column name set to obtain a target column name set of the target import table of the import task; wherein the partition column names include date partition column names and multi-level partition column names.

[0074] In this embodiment, the initial column name set of the target import table of the import task obtained from the database using a cursor object usually cannot be directly used as the target column name set of the target import table of the import task. The initial column name set usually includes date partition column names and multi-level partition column names. Therefore, after obtaining the initial column name set of the target import table of the import task, the date partition column names and multi-level partition column names in the initial column name set need to be deleted, and then the target column name set of the target import table of the import task can be obtained.

[0075] S150, generating the import task according to the array variable, the target date partition name set, the target date partition value set and the target column name set, and inputting the import task into the database to execute the import task in the database to import the target data into the database.

[0076] In this embodiment, the first SQL statement in the array variable sqls is extracted and assigned to the variable load_sql in the template, and then the target date partition name set, the target date partition value set and the target column name set are combined into a tuple list in the template, and the tuple list is iterated in a key-value manner. During the iteration process, the content of the variable load_sql is replaced by the replace method according to the key value key, so as to replace the predefined variables in the content with specific actual values, until the iteration is completed, and finally the execute() method in the cursor object is used to execute the updated variable load_sql in the database, thereby realizing the import of the target data into the database.

[0077] In other embodiments of the invention, the template of the import task further includes a second SQL statement for performing data cleanup on the target data table, such as Figure 5 As shown, before step S150, steps S150a and S150b are also included.

[0078] S150a, extracting the second SQL statement from the array variable;

[0079] S150b. Based on the cursor object, and according to the target date partition value set, use the second SQL statement in the database to clean up data on the target import table.

[0080] Specifically, before the target data is imported into the database, the corresponding data is still stored in the date partition corresponding to the target import table. Therefore, in the process of constructing the template, the SQL statement (second SQL statement) for data cleanup of the target import table needs to be configured in the template. Then, before generating the import task, the cursor object can be used to implement the iteration operation on the target date partition value set inc_date_set until the iteration is completed, thereby implementing data cleanup of the target import table in the database. Among them, each iteration specifically performs a deletion operation on the data corresponding to the date partition of the import table. The detailed process of the iteration is: extract the second SQL statement in the array variable sqls, then use the replace function to replace the predefined table name table_name and the cut-off date partition value inc_date in the second SQL statement, and convert it to a specific table name and a specific date partition value, then assign the result to the variable truncate_sql in the template, and use the execute() method in the cursor object to execute the variable truncate_sql in the template in the database to complete the deletion operation of the target date partition data in the target import table.

[0081] It should be noted that when the date partition type inc_type is week, the date partition value set inc_date_set_sr is iterated until the iteration is completed.

[0082] In other inventive embodiments, Figure 6 As shown, step S150 includes steps S151 and S152.

[0083] S151, obtaining the current timestamp, and extracting the first SQL statement of the import task from the array variable;

[0084] S152: Generate the import task according to the first SQL statement of the import task, the current timestamp, the target date partition name set, the target date partition value set, and the target column name set.

[0085] In this embodiment, before generating the import task, it is necessary to obtain the current timestamp in advance and save it to the variable cur_time of the template, then extract the first SQL statement in the array variable sqls and assign it to the variable load_sql in the template, then combine the target date partition name set, the target date partition value set and the target column name set into a tuple list in the template, and iterate the tuple list in a key-value manner, and during the iteration process, use the replace method to replace the content of the variable load_sql according to the key value key, so as to replace the predefined variables in the content with specific actual values, until the iteration is completed, and finally use the execute() method in the cursor object to execute the updated variable load_sql in the database, thereby realizing the import of the target data into the database.

[0086] In other inventive embodiments, Figure 7 As shown, after step S150, steps S160 and S170 are also included.

[0087] S160, generating a fourth SQL statement for monitoring the status of the import task according to the import task and the third SQL statement for operating the database with the cursor object;

[0088] S170: Based on the initial time and the end time of the import task, use the fourth SQL statement to monitor the status of the import task.

[0089] This embodiment is mainly used to monitor the Broker Loa import task. Since Broker Loa is an asynchronous import method, when Broker Loa is used to import data, it will asynchronously import the data into the StarRocks database, and it is impossible to directly query the log of the Broker Loa import task. At this time, it is necessary to log in to the StarRocks database and use the SHOW LOAD statement to view the results of the Broker Loa import task, which makes it impossible to intuitively monitor the import process. To this end, this application directly generates a fourth SQL statement for monitoring the status of the import task based on the third SQL statement of the import task and the cursor object to operate the database; then based on the current timestamp of the import task and the timestamp of the initial operation of the import task, the fourth SQL statement can be directly used to monitor the status of the import task.

[0090] In this embodiment, the fourth SQL statement is an SQL statement executed by the execute() method in the cursor object. The fourth SQL statement includes a label variable label, which is formed by the table name table_name, the deadline partition value inc_date, and the parameter cur_time. At the same time, the initial time and the end time of the import task are obtained by the now method of the datetime library function. After obtaining the initial time and the end time of the import task, the initial time and the end time are saved in the initial time variable start_time and the end time variable end_time respectively, and the time difference between the initial time and the end time is calculated. After determining that the time difference is less than 120min (7200s), the state of the import task is queried every 30 seconds to obtain the query result of the import task. If the query result is abnormal information, connect to the database and roll back the entire import task; if the query result is normal information, connect to the database and submit the entire import task.

[0091] The specific query process is as follows: use the execute() method in the cursor object to execute the show load command in the database connection, and query the records that meet the import label variable label, then assign the query result to the array variable row, and use the now method of the datetime library function to update the end time variable end_time, and use the value of the subscript 2 in the array variable row, that is, row[2] to determine the status of the import task. When row[2] is equal to the 'FINISHED' value, it means that the import task is successful and exits the loop; when row[2] is equal to the 'CANCELLED' value, it means that the import task fails. At this time, it is necessary to print the full amount of information of row in the log, and after an exception is raised to the program, exit the loop. When row[2] is neither equal to the 'FINISHED' value nor the 'CANCELLED' value, the program sleeps for 30 seconds and then uses the execute() method in the cursor object to execute the show load command in the database connection again, and queries the records that meet the import label variable label. The query result is then assigned to the array variable row, and the now method of the datetime library function is used to update the end time variable end_time. The value with subscript 2 in the array variable row, that is, row[2] is used to determine the status of the import task until the 7200-second constraint is exceeded.

[0092] In the data import method provided in the embodiment of the present application, a first SQL statement for executing the import task is generated according to the parameters of the import task of a preset database and the template of the import task, and the first SQL statement is filled into a preset array variable; a connection is established with the database to obtain a cursor object for operating the database; a target date partition name set and a target date partition value set of the import task are generated in the template of the import task; based on the cursor object, the database is queried according to the target date partition name set and the target date partition value set of the import task to obtain a target column name set of the target import table of the import task; the import task is generated according to the array variable, the target date partition name set, the target date partition value set and the target column name set, and the import task is input into the database to execute the import task in the database to realize the import of target data into the database, thereby realizing the rapid import of large quantities of data into the database, greatly improving the efficiency of importing data into the database, and reducing the labor cost of data import.

[0093] The embodiment of the present application also provides a data importing device 100, which is used to execute any embodiment of the aforementioned data importing method.

[0094] Specifically, see Figure 8 , Figure 8 It is a schematic block diagram of the data importing device 100 provided in an embodiment of the present application.

[0095] like Figure 8 As shown, the data import device 100 includes: a first generating unit 110, a connecting unit 120, a second generating unit 130, an acquiring unit 140 and a third generating unit 150.

[0096] The first generating unit 110 is used to generate a first SQL statement for executing the import task according to the parameters of the preset database import task and the template of the import task, and fill the first SQL statement into a preset array variable.

[0097] In other embodiments of the invention, the first generation unit 110 is specifically used to construct a template for the import task according to the data import rules of the database; wherein the template for the import task includes a first SQL statement for executing the import task and a definition of parameters of the import task; the parameters of the import task are input into the template for the import task; the template for the import task is parsed, and the parsed first SQL statement is filled into a preset array variable.

[0098] In other embodiments of the invention, when the first generation unit 110 is used to parse the template of the import task and fill the parsed first SQL statement into a preset array variable, the following steps are specifically performed: reading the template of the import task according to the file name of the template of the import task; extracting the first SQL statement from the template of the import task, and filling the first SQL statement into the array variable.

[0099] The connection unit 120 is used to establish a connection with the database and obtain a cursor object for operating the database.

[0100] The second generating unit 130 is configured to generate a target date partition name set and a target date partition value set of the import task in the template of the import task.

[0101] The acquisition unit 140 is used to query the database based on the cursor object and the target date partition name set and the target date partition value set of the import task to obtain the target column name set of the target import table of the import task.

[0102] In other embodiments of the invention, the acquisition unit 140 is specifically used to operate the database based on the cursor object and according to the target date partition name set and the target date partition value set of the import task to obtain the initial column name set of the target import table of the import task; delete the partition column names in the initial column name set to obtain the target column name set of the target import table of the import task; wherein the partition column names include date partition column names and multi-level partition column names.

[0103] The third generating unit 150 is used to generate the import task according to the array variable, the target date partition name set, the target date partition value set and the target column name set, and input the import task into the database to execute the import task in the database to realize importing the target data into the database.

[0104] In other inventive embodiments, the data importing device 100 further includes: an extraction unit and a data cleaning unit.

[0105] An extraction unit is used to extract the second SQL statement from the array variable; a data cleaning unit is used to clean the data of the target import table in the database using the second SQL statement based on the cursor object and the target date partition value set.

[0106] In other embodiments of the invention, the third generation unit 150 is specifically used to obtain the current timestamp and extract the first SQL statement of the import task from the array variable; generate the import task according to the first SQL statement of the import task, the current timestamp, the target date partition name set, the target date partition value set and the target column name set.

[0107] In other embodiments of the invention, the data importing device 100 further includes: a fourth generating unit and a monitoring unit.

[0108] The fourth generating unit is used to generate a fourth SQL statement for monitoring the status of the import task according to the import task and the third SQL statement for operating the database with the cursor object; the monitoring unit is used to monitor the status of the import task using the fourth SQL statement based on the initial time and end time of the import task.

[0109] The data import device 100 provided in the embodiment of the present application is used to execute the above-mentioned parameters of the import task of the preset database and the template of the import task to generate the first SQL statement for executing the import task, and fill the first SQL statement into the preset array variable; establish a connection with the database to obtain a cursor object for operating the database; generate a target date partition name set and a target date partition value set for the import task in the template of the import task; based on the cursor object, query the database according to the target date partition name set and the target date partition value set of the import task to obtain the target column name set of the target import table of the import task; generate the import task according to the array variable, the target date partition name set, the target date partition value set and the target column name set, and input the import task into the database to execute the import task in the database to realize importing the target data into the database.

[0110] It should be noted that those skilled in the art can clearly understand that the specific implementation process of the above-mentioned data import device 100 and each unit can refer to the corresponding description in the aforementioned method embodiment, and for the convenience and brevity of description, it will not be repeated here.

[0111] The above-mentioned data import device can be implemented in the form of a computer program. The computer program can be Fig. 9 Runs on the computer device shown.

[0112] See also Fig. 9 , Fig. 92 is a schematic block diagram of a computer device provided in an embodiment of the present application. The electronic device 200 may be a terminal, wherein the terminal may be a computer device with communication functions such as a smart phone, a tablet computer, a laptop computer, a desktop computer, a personal digital assistant, and a wearable device.

[0113] See also Fig. 9 The electronic device 200 includes a processor 202 , a memory and a network interface 205 connected via a system bus 201 , wherein the memory may include a non-volatile storage medium 203 and an internal memory 204 .

[0114] The non-volatile storage medium 203 can store an operating system 2031 and a computer program 2032. The computer program 2032 includes program instructions, and when the program instructions are executed, the processor 202 can execute a data import method.

[0115] The processor 202 is used to provide computing and control capabilities to support the operation of the entire electronic device 200 .

[0116] The internal memory 204 provides an environment for the operation of the computer program 2032 in the non-volatile storage medium 203. When the computer program 2032 is executed by the processor 202, the processor 202 can execute a data import method.

[0117] The network interface 205 is used to communicate with other devices over the network. Those skilled in the art will appreciate that Fig. 9 The structure shown in the figure is merely a block diagram of a partial structure related to the scheme of the present application, and does not constitute a limitation on the electronic device 200 to which the scheme of the present application is applied. The specific electronic device 200 may include more or fewer components than those shown in the figure, or combine certain components, or have a different arrangement of components.

[0118] The processor 202 is used to run a computer program 2032 stored in a memory to implement the following steps: generating a first SQL statement for executing the import task according to the parameters of the preset database import task and the template of the import task, and filling the first SQL statement into a preset array variable; establishing a connection with the database to obtain a cursor object for operating the database; generating a target date partition name set and a target date partition value set for the import task in the template of the import task; based on the cursor object, querying the database according to the target date partition name set and the target date partition value set of the import task to obtain a target column name set of the target import table of the import task; generating the import task according to the array variable, the target date partition name set, the target date partition value set and the target column name set, and inputting the import task into the database to execute the import task in the database to import the target data into the database.

[0119] In one embodiment, when the processor 202 generates the first SQL statement for executing the import task according to the parameters of the import task of a preset database and the template of the import task, and fills the first SQL statement into a preset array variable, it specifically implements the following steps: constructing the template of the import task according to the data import rules of the database; wherein the template of the import task includes the definition of the first SQL statement for executing the import task and the parameters of the import task; inputting the parameters of the import task into the template of the import task; parsing the template of the import task, and filling the parsed first SQL statement into the preset array variable.

[0120] In one embodiment, when the processor 202 implements the parsing of the template of the import task and fills the parsed first SQL statement into a preset array variable, it specifically implements the following steps: reading the template of the import task according to the file name of the template of the import task; extracting the first SQL statement from the template of the import task, and filling the first SQL statement into the array variable.

[0121] In one embodiment, when the processor 202 implements the query of the database based on the cursor object according to the target date partition name set and the target date partition value set of the import task to obtain the target column name set of the target import table of the import task, the processor 202 specifically implements the following steps: based on the cursor object, the database is operated according to the target date partition name set and the target date partition value set of the import task to obtain the initial column name set of the target import table of the import task; the partition column names in the initial column name set are deleted to obtain the target column name set of the target import table of the import task; wherein the partition column names include date partition column names and multi-level partition column names.

[0122] In one embodiment, the template of the import task also includes a second SQL statement for performing data cleansing on the target data table. Before the processor 202 generates the import task according to the array variable, the target date partition name set, the target date partition value set, and the target column name set, it specifically implements the following steps: extracting the second SQL statement from the array variable; based on the cursor object, using the second SQL statement in the database to perform data cleansing on the target import table according to the target date partition value set.

[0123] In one embodiment, when the processor 202 generates the import task according to the array variable, the target date partition name set, the target date partition value set, and the target column name set, it specifically implements the following steps: obtaining the current timestamp and extracting the first SQL statement of the import task from the array variable; generating the import task according to the first SQL statement of the import task, the current timestamp, the target date partition name set, the target date partition value set, and the target column name set.

[0124] In one embodiment, after the processor 202 generates the import task according to the array variable, the target date partition name set, the target date partition value set, and the target column name set, and inputs the import task into the database to execute the import task in the database to import the target data into the database, it specifically implements the following steps: generating a fourth SQL statement for monitoring the status of the import task according to the import task and the third SQL statement for operating the database with the cursor object; and using the fourth SQL statement to monitor the status of the import task based on the initial time and end time of the import task.

[0125] It should be understood that in the embodiment of the present application, the processor 202 may be a central processing unit (CPU), and the processor 202 may also be other general-purpose processors, digital signal processors (DSP), application-specific integrated circuits (ASIC), field-programmable gate arrays (FPGA) or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, etc. Among them, the general-purpose processor may be a microprocessor or the processor may also be any conventional processor, etc.

[0126] It can be understood by those skilled in the art that all or part of the processes in the method for implementing the above embodiment can be completed by instructing the relevant hardware through a computer program. The computer program includes program instructions, and the computer program can be stored in a storage medium, which is a computer-readable storage medium. The program instructions are executed by at least one processor in the computer system to implement the process steps of the embodiment of the above method.

[0127] Therefore, the present application also provides a storage medium. The storage medium can be a computer-readable storage medium. The storage medium stores a computer program, wherein the computer program includes program instructions. When the program instructions are executed by the processor, the processor performs the following steps: generating a first SQL statement for executing the import task according to the parameters of the preset database import task and the template of the import task, and filling the first SQL statement into the preset array variable; establishing a connection with the database to obtain a cursor object for operating the database; generating a target date partition name set and a target date partition value set of the import task in the template of the import task; based on the cursor object, querying the database according to the target date partition name set and the target date partition value set of the import task to obtain the target column name set of the target import table of the import task; generating the import task according to the array variable, the target date partition name set, the target date partition value set and the target column name set, and inputting the import task into the database to execute the import task in the database to realize importing the target data into the database.

[0128] In one embodiment, when the processor executes the program instructions to generate the first SQL statement for executing the import task according to the parameters of the import task of a preset database and the template of the import task, and fills the first SQL statement into a preset array variable, the following steps are specifically implemented: constructing the template of the import task according to the data import rules of the database; wherein the template of the import task includes the definition of the first SQL statement for executing the import task and the parameters of the import task; inputting the parameters of the import task into the template of the import task; parsing the template of the import task, and filling the parsed first SQL statement into the preset array variable.

[0129] In one embodiment, when the processor executes the program instructions to parse the template of the import task and fills the parsed first SQL statement into a preset array variable, the following steps are specifically implemented: reading the template of the import task according to the file name of the template of the import task; extracting the first SQL statement from the template of the import task, and filling the first SQL statement into the array variable.

[0130] In one embodiment, when the processor executes the program instructions to implement the querying of the database based on the cursor object according to the target date partition name set and the target date partition value set of the import task to obtain the target column name set of the target import table of the import task, the processor specifically implements the following steps: based on the cursor object, the database is operated according to the target date partition name set and the target date partition value set of the import task to obtain the initial column name set of the target import table of the import task; the partition column names in the initial column name set are deleted to obtain the target column name set of the target import table of the import task; wherein the partition column names include date partition column names and multi-level partition column names.

[0131] In one embodiment, the template of the import task also includes a second SQL statement for performing data cleansing on the target data table. Before the processor executes the program instructions to generate the import task according to the array variable, the target date partition name set, the target date partition value set, and the target column name set, it specifically implements the following steps: extracting the second SQL statement from the array variable; based on the cursor object, using the second SQL statement in the database to perform data cleansing on the target import table according to the target date partition value set.

[0132] In one embodiment, when the processor executes the program instructions to generate the import task according to the array variable, the target date partition name set, the target date partition value set, and the target column name set, the processor specifically implements the following steps: obtaining the current timestamp and extracting the first SQL statement of the import task from the array variable; generating the import task according to the first SQL statement of the import task, the current timestamp, the target date partition name set, the target date partition value set, and the target column name set.

[0133] In one embodiment, the processor executes the program instructions to generate the import task according to the array variable, the target date partition name set, the target date partition value set and the target column name set, and inputs the import task into the database to execute the import task in the database to import the target data into the database, and then specifically implements the following steps: generating a fourth SQL statement for monitoring the status of the import task according to the import task and the third SQL statement for operating the database with the cursor object; and using the fourth SQL statement to monitor the status of the import task based on the initial time and end time of the import task.

[0134] The storage medium may be a USB flash drive, a mobile hard disk, a read-only memory (ROM), a magnetic disk, or an optical disk, etc., which are computer-readable storage media that can store program codes.

[0135] Those of ordinary skill in the art will appreciate that the units and algorithm steps of each example described in conjunction with the embodiments disclosed herein can be implemented in electronic hardware, computer software, or a combination of the two. In order to clearly illustrate the interchangeability of hardware and software, the composition and steps of each example have been generally described in terms of function in the above description. Whether these functions are performed in hardware or software depends on the specific application and design constraints of the technical solution. Professional and technical personnel can use different methods to implement the described functions for each specific application, but such implementation should not be considered to be beyond the scope of this application.

[0136] In the several embodiments provided in the present application, it should be understood that the disclosed devices and methods can be implemented in other ways. For example, the device embodiments described above are only schematic. For example, the division of each unit is only a logical function division, and there may be other division methods in actual implementation. For example, multiple units or components can be combined or integrated into another system, or some features can be ignored or not executed.

[0137] The steps in the method of the embodiment of the present application can be adjusted in order, combined and deleted according to actual needs. The units in the device of the embodiment of the present application can be combined, divided and deleted according to actual needs. In addition, the functional units in the various embodiments of the present application can be integrated into one processing unit, or each unit can exist physically separately, or two or more units can be integrated into one unit.

[0138] If the integrated unit is implemented in the form of a software functional unit and sold or used as an independent product, it can be stored in a storage medium. Based on this understanding, the technical solution of the present application is essentially or the part that contributes to the prior art, or all or part of the technical solution can be embodied in the form of a software product, and the computer software product is stored in a storage medium, including a number of instructions for an electronic device (which can be a personal computer, terminal, or network device, etc.) to perform all or part of the steps of the method described in each embodiment of the present application.

[0139] The above is only a specific implementation of the present application, but the protection scope of the present application is not limited thereto. Any technician familiar with the technical field can easily think of various equivalent modifications or replacements within the technical scope disclosed in the present application, and these modifications or replacements should be included in the protection scope of the present application. Therefore, the protection scope of the present application shall be based on the protection scope of the claims.

Claims

1. A data import method, characterized in that: include: Generate a first SQL statement for executing the import task according to the parameters of the preset database import task and the template of the import task, and fill the first SQL statement into the preset array variable; Establishing a connection with the database and obtaining a cursor object for operating the database; Generating a target date partition name set and a target date partition value set of the import task in a template of the import task; Based on the cursor object, query the database according to the target date partition name set and the target date partition value set of the import task to obtain the target column name set of the target import table of the import task; The import task is generated according to the array variable, the target date partition name set, the target date partition value set and the target column name set, and the import task is input into the database to execute the import task in the database to realize importing the target data into the database.

2. The data import method according to claim 1, characterized in that: The step of generating a first SQL statement for executing the import task according to the parameters of the import task of the preset database and the template of the import task, and filling the first SQL statement into the preset array variable includes: Constructing a template for the import task according to the data import rules of the database; wherein the template for the import task includes a first SQL statement for executing the import task and a definition of parameters of the import task; Inputting the parameters of the import task into the template of the import task; The template of the import task is parsed, and the parsed first SQL statement is filled into a preset array variable.

3. The data import method according to claim 2, characterized in that: The step of parsing the template of the import task and filling the parsed first SQL statement into a preset array variable includes: Reading the template of the import task according to the file name of the template of the import task; The first SQL statement is extracted from the template of the import task, and the first SQL statement is filled into the array variable.

4. The data import method according to claim 1, characterized in that: The step of querying the database based on the cursor object and according to the target date partition name set and the target date partition value set of the import task to obtain the target column name set of the target import table of the import task includes: Based on the cursor object, the database is operated according to the target date partition name set and the target date partition value set of the import task to obtain an initial column name set of the target import table of the import task; The partition column names in the initial column name set are deleted to obtain a target column name set of the target import table of the import task; wherein the partition column names include date partition column names and multi-level partition column names.

5. The data import method according to claim 1, characterized in that: The template of the import task also includes a second SQL statement for performing data cleanup on the target data table; Before generating the import task according to the array variable, the target date partition name set, the target date partition value set and the target column name set, the method further includes: Extract the second SQL statement in the array variable; Based on the cursor object, the second SQL statement is used in the database to clean up data on the target import table according to the target date partition value set.

6. The data import method according to claim 1, characterized in that: Generating the import task according to the array variable, the target date partition name set, the target date partition value set, and the target column name set includes: Obtain the current timestamp, and extract the first SQL statement of the import task from the array variable; The import task is generated according to the first SQL statement of the import task, the current timestamp, the target date partition name set, the target date partition value set, and the target column name set.

7. The data import method according to claim 1, characterized in that: After generating the import task according to the array variable, the target date partition name set, the target date partition value set, and the target column name set, and inputting the import task into the database, so as to execute the import task in the database to import the target data into the database, the method further includes: Generate a fourth SQL statement for monitoring the status of the import task according to the import task and the third SQL statement for operating the database with the cursor object; Based on the initial time and the end time of the import task, the fourth SQL statement is used to monitor the status of the import task.

8. A data import device, characterized in that: include: A first generating unit, configured to generate a first SQL statement for executing the import task according to the parameters of the preset database import task and the template of the import task, and fill the first SQL statement into a preset array variable; A connection unit, used to establish a connection with the database and obtain a cursor object for operating the database; A second generating unit, configured to generate a target date partition name set and a target date partition value set of the import task in a template of the import task; An acquisition unit, configured to query the database based on the cursor object and according to the target date partition name set and the target date partition value set of the import task, so as to acquire a target column name set of the target import table of the import task; The third generating unit is used to generate the import task according to the array variable, the target date partition name set, the target date partition value set and the target column name set, and input the import task into the database to execute the import task in the database to realize importing the target data into the database.

9. A computer device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that: When the processor executes the computer program, the data import method according to any one of claims 1 to 7 is implemented.

10. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores a computer program, which, when executed by a processor, enables the processor to perform the data import method according to any one of claims 1 to 7.

Images