A method, apparatus, device, and medium for inserting a dataset.

Abstract

This specification discloses a method, apparatus, device, and medium for inserting a dataset, including: parsing the target dataset into a specified format dataset when passing parameters in MySQL database connection information or local file transmission; outputting the specified format dataset through a specified interface and previewing and confirming the specified format dataset; iterating through and concatenating the previewed specified format dataset into an SQL execution statement, and storing the SQL execution statement in a pre-defined ClickHouse database to realize the insertion operation of the target dataset. This specification embodiment unifies datasets from different sources under ClickHouse for management, effectively utilizing ClickHouse's advantages of speed and functionality to improve dataset management efficiency. Furthermore, through the aforementioned multi-data source management method, this specification embodiment can format datasets from different files into highly organized and neat data with clear relationships and structures, making the use and management of this data very convenient.

Description

Technical Field

[0001] This specification relates to the field of computer technology, and in particular to a method, apparatus, device, and medium for inserting a dataset. Background Technology

[0002] ClickHouse is a column-oriented database for real-time data analytics. Each server can process hundreds of millions to billions of rows and tens of gigabytes of data per second. It features data compression, disk storage, SQL support, multi-core parallel processing, a vectorized engine, indexes, support for approximate calculations, a rich set of table engines, and data partitioning. It is recommended for scenarios where data added to the database is rarely modified, query concurrency is relatively low, column values ​​are relatively small (numeric or short strings), and there is no transaction processing, such as storing and analyzing data and statistics, recording and analyzing user behavior data, and log analysis.

[0003] In existing technologies, it is impossible to insert data from different sources into the ClickHouse database, thereby enabling unified management of data from different sources. Summary of the Invention

[0004] This specification provides one or more embodiments of a method, apparatus, device, and medium for inserting a dataset, which addresses the technical problems raised in the background art.

[0005] One or more embodiments of this specification employ the following technical solutions:

[0006] This specification provides a method for inserting a dataset according to one or more embodiments, including:

[0007] When passing parameters in MySQL database connection information or local file, parse the target dataset into a dataset of the specified format;

[0008] Output the dataset in the specified format through the specified interface, and preview and confirm the dataset in the specified format;

[0009] The dataset in the specified format, after being previewed and confirmed, is traversed and concatenated into an SQL execution statement, which is then stored in a pre-defined ClickHouse database to perform the insertion operation on the target dataset.

[0010] This specification provides an apparatus for inserting a dataset according to one or more embodiments, the apparatus comprising:

[0011] The parsing unit parses the target dataset into a specified format when passing parameters in MySQL database connection information or local file.

[0012] The output unit outputs the dataset in the specified format through a specified interface and previews and confirms the dataset in the specified format.

[0013] The insertion unit iterates through and concatenates the specified format dataset after preview confirmation into an SQL execution statement, and stores the SQL execution statement in a pre-defined ClickHouse database to realize the insertion operation of the target dataset.

[0014] This specification provides one or more embodiments of a dataset insertion device, comprising:

[0015] At least one processor; and,

[0016] A memory communicatively connected to the at least one processor; wherein,

[0017] The memory stores instructions executable by the at least one processor, which, when executed by the at least one processor, enable the at least one processor to:

[0018] When passing parameters in MySQL database connection information or local file, parse the target dataset into a dataset of the specified format;

[0019] The specified format dataset is output through the specified interface, and the specified format dataset is previewed and confirmed.

[0020] The dataset in the specified format, after being previewed and confirmed, is traversed and concatenated into an SQL execution statement, which is then stored in a pre-defined ClickHouse database to perform the insertion operation on the target dataset.

[0021] This specification provides one or more embodiments of a non-volatile computer storage medium storing computer-executable instructions, wherein the computer-executable instructions are configured as follows:

[0022] When passing parameters in MySQL database connection information or local file, parse the target dataset into a dataset of the specified format;

[0023] The specified format dataset is output through the specified interface, and the specified format dataset is previewed and confirmed.

[0024] The dataset in the specified format, after being previewed and confirmed, is traversed and concatenated into an SQL execution statement, which is then stored in a pre-defined ClickHouse database to perform the insertion operation on the target dataset.

[0025] The above-described at least one technical solution adopted in the embodiments of this specification can achieve the following beneficial effects:

[0026] This specification's embodiments unify datasets from different sources under ClickHouse for management, effectively leveraging ClickHouse's speed and functionality to improve dataset management efficiency. Furthermore, through the aforementioned multi-data source management method, these embodiments can format datasets from different files into highly organized and structured data with clear relationships, making the use and management of this data extremely convenient. Attached Figure Description

[0027] To more clearly illustrate the technical solutions in the embodiments or prior art of this specification, the drawings used in the description of the embodiments or prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments recorded in this specification. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort. In the drawings:

[0028] Figure 1 A flowchart illustrating a method for inserting a dataset as provided in one or more embodiments of this specification.

[0029] Figure 2 A flowchart illustrating the data processing of MySQL database connection information provided for one or more embodiments of this specification;

[0030] Figure 3 A flowchart illustrating the data source as local file processing information for one or more embodiments of this specification:

[0031] Figure 4 A schematic diagram of the structure of a dataset insertion device provided in one or more embodiments of this specification;

[0032] Figure 5 This is a schematic diagram of the structure of a dataset insertion device provided for one or more embodiments of this specification. Detailed Implementation

[0033] This specification provides a method, apparatus, device, and medium for inserting a dataset.

[0034] To enable those skilled in the art to better understand the technical solutions in this specification, the technical solutions in the embodiments of this specification will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this specification, and not all embodiments. Based on the embodiments of this specification, all other embodiments obtained by those skilled in the art without creative effort should fall within the scope of protection of this specification.

[0035] Figure 1 This diagram illustrates a method for inserting a dataset according to one or more embodiments of this specification. This process can be executed by a dataset insertion system. Certain input parameters or intermediate results in the process can be manually adjusted to help improve accuracy.

[0036] The method flow steps of the embodiments in this specification are as follows:

[0037] S102, when passing parameters in MySQL database connection information or local file, parses the target dataset into a dataset of a specified format.

[0038] In the embodiments described in this specification, the target dataset can be a local file and a MySQL database.

[0039] In the embodiments of this specification, when the target dataset originates from a local file, the file extension type of the local file can be determined first; then, based on the file extension type of the local file, the parsing method of the target dataset can be determined; finally, based on the parsing method of the target dataset, the target dataset can be parsed into a dataset of a specified format.

[0040] In the embodiments of this specification, the file extension type may include excel file type and csv file type; if the file extension type of the local file is excel file type, a first parsing method corresponding to the target dataset is determined; if the file extension type of the local file is csv file type, a second parsing method corresponding to the target dataset is determined.

[0041] In the embodiments of this specification, when determining the first parsing method corresponding to the target dataset, and when parsing the target dataset into a specified format dataset according to the parsing method of the target dataset, the HSSFWorkbook class can be used to parse .xls format files, and the XSSFWorkbook class can be used to parse .xlsx format files. The sheet page is specified by passing parameters for parsing. For files with empty sheet page parameters, the first sheet page is parsed by default. After obtaining the Sheet object, the header columns of the file are obtained by the getRow(0) method, and the total number of header columns is obtained by the getPhysicalNumberOfCells() method. The Row object is traversed, and the column name of each column is obtained in turn by getCellFormatValue(row.getCell(i)), and assembled into an array of column names. After obtaining the header column name array String[]head, the Sheet object is traversed according to the total number of rows of getLastRowNum() and the total number of columns of getPhysicalNumberOfCells(), and each row is read into a Map to obtain the specified format dataset.

[0042] In the embodiments of this specification, when determining the second parsing method corresponding to the target dataset, and when parsing the target dataset into a specified format dataset according to the parsing method of the target dataset, the BufferedReader class can be used to read the file into a buffer, and the first row of header information can be read by comma-separated by bufferedReader.readLine().split(",") to obtain the column name array; the readLine() method of BufferedReader is used to traverse the file to be parsed, and a regular expression is used to ignore the commas in the quotes when reading each value to avoid unnecessary parsing errors, so as to obtain the specified format dataset.

[0043] In the embodiments of this specification, when the target dataset source is a MySQL database, the connection address of the MySQL database can be constructed by concatenating the passed IP address, port number, and database name, and the connection to the MySQL database can be tested. If successful, the MySQL database can be accessed to obtain the table name, the field names within the table, and a conditional query statement can be constructed.

[0044] S104, output the specified format dataset through the specified interface, and preview and confirm the specified format dataset.

[0045] S106, after previewing and confirming, the specified format dataset is traversed and concatenated into an SQL execution statement, and the SQL execution statement is stored in the pre-set ClickHouse database to realize the insertion operation of the target dataset.

[0046] In this embodiment, a JDBC interface can be used to connect to the ClickHouse database used to store data tables. When creating tables in ClickHouse, a primary key (primarykey) is specified. The key in any map within the preview result set of the specified format dataset is retrieved. It is determined whether an id column is present, and lists without an id column are added. The keys in the map are concatenated, and an id column is added. The concatenated table creation statement is executed using `connection.createStatement().executeQuery(sql)`, where the dataset name (tableName) is passed as the table name. The expression is: `sql = "createtable`" + tableName + "`" + col + "Engine = MergeTree() primary key id"`. An exception handling mechanism is implemented for tables already existing in the database, and the exception is returned. The specified format dataset is traversed, and the values ​​corresponding to each data record are concatenated to obtain a `values` array. Values ​​are assigned to the corresponding position in the id column using `generateUUIDv4()`. The concatenated insert statement `sql2 = "insert`` is executed using `connection.createStatement().executeQuery(sql2)`. The function `into`"+tableName+"`"+"(id,"+String.join(",",colunminsert)+")"+"values"+String.join(",",values)` is used to perform the insertion operation on the target dataset.

[0047] It's important to note that structured data, also known as quantitative data, is highly organized and neatly formatted data. It refers to information that can be represented using data or a uniform structure, such as numbers and symbols. In projects, this data is typically stored and managed in relational databases. When using Structured Query Language (SQL) or SQL, computer programs can easily search for these terms. The explicit relationships inherent in structured data make it very convenient to use.

[0048] This specification demonstrates how to read Excel files using the POI method. POI provides two implementation classes: HSSFWorkbook and XSSFWorkbook. HSSFWorkbook is for .xls files, and XSSFWorkbook is for .xlsx files. The main difference between POI and the JXL method is that JXL does not support .xlsx files, while POI does.

[0049] This specification aims to provide an interface method that uses JDBC to parse data from different sources into data tables and save them to the ClickHouse database for unified management. The data sources supported include MySQL databases, xls files, xlsx files, and csv files. Among them, the MySQL database supports filtering and multi-table joins to achieve structured and unified management of multiple data sources.

[0050] The logic of the embodiments in this specification is as follows: Figure 2 The data shown is a flowchart illustrating the processing of MySQL database connection information. Figure 3 The diagram shown illustrates the process of processing local file information as the data source:

[0051] The multi-data source management method in the embodiments of this specification accepts local files (supporting xls, xlsx, and csv formats) and single-table or multi-table conditional queries from MySQL databases.

[0052] (i) Parse the target dataset into a List by passing parameters via MySQL database connection information or local file. <map>The format is output from the interface for preview.

[0053] 1. When the dataset comes from a local file, the getOriginalFilename().endsWith() method of the File class is used to determine whether the file extension of the received file is xls, xlsx, txt or csv. The first two are processed using the Excel parsing method, and the latter two are processed using the CSV file parsing method.

[0054] If the data source is an Excel file (only single-row header is supported).

[0055] (1) Use the HSSFWorkbook class to parse .xls format files and the XSSFWorkbook class to parse .xlsx format files. You can specify the sheet page by passing parameters. For files with empty sheet page parameters, the first sheet page will be parsed by default.

[0056] (2) After obtaining the Sheet object, the header columns of the file are obtained by the getRow(0) method and the total number of header columns is obtained by the getPhysicalNumberOfCells() method. The Row object is traversed and the column name of each column is obtained in turn by getCellFormatValue(row.getCell(i)) and assembled into an array of column names.

[0057] (3) After obtaining the header column name array String[]head, iterate through the Sheet object according to the total number of rows in getLastRowNum() and the total number of columns in getPhysicalNumberOfCells(), read each row into a Map, and obtain the preview result set List. <map>.

[0058] If the data source is a CSV file (only single-row header is supported).

[0059] (1) First, use the BufferedReader class to read the file into the buffer, and use bufferedReader.readLine().split(",") to read the first row of header information separated by commas to get the column name array.

[0060] (2) The file to be parsed is traversed by the readLine() method of BufferedReader. When reading each value, regular expressions are used to ignore commas in quotes to avoid unnecessary parsing errors.

[0061] 2. When the dataset originates from a MySQL database.

[0062] (1) First, construct the MySQL database connection address by concatenating the IP address, port number, and database name: String url = "jdbc:mysql: / / "+ip+":"+port+" / "+database; test whether the database connection is successful using the DriverManager's getConnection(url,username,password) method.

[0063] (2) After a successful connection, access the database through the Statement interface: Statement statement = con.createStatement().

[0064] (3) Use the ResultSet resultSet = statement.executeQuery(sql) statement to execute the SQL statement, which will be used to get the database table name, get the field names in the table, and concatenate the condition query statement.

[0065] a. In retrieving the database table name, execute the "Show tables" statement and iterate through the result set using while(rs.next()) to retrieve the table name;

[0066] b. In retrieving table column names, iterate through the array of table names (String[]table) obtained in step a, and execute the statement "select * from " + table[i] + "limit 1" for each table. Obtain the table structure using ResultSetMetaData rsmd = rs.getMetaData(), and iterate through the result set based on the column count obtained by rsmd.getColumnCount(), using rsmd.getColumnName(int column) and...

[0067] rsmd.getColumnTypeName(int column) retrieves the column name and column data type respectively;

[0068] c. In conditional query statements, all fields in the target data table are filtered by default. When concatenating filter conditions, two checks are required based on the column names and data types obtained from the MySQL table in b. First, it is determined whether the parameter after the relational join operator is a field in the data table. If not, the data type of the field before the relational join operator needs to be obtained through the data table column name and data type interface in b. If it is a varchar or similar type, single quotes "'" need to be added before and after when concatenating it into the SQL statement. Otherwise, it is directly concatenated into the statement.

[0069] (ii) The List after preview confirmation <map>Dataset traversal, concatenation into SQL statements, execution, and storage in the ClickHouse database are among the operations performed.

[0070] (1) First, the ClickHouse database used to store data tables is connected via the JDBC interface.

[0071] (2) When creating a table in ClickHouse, a primary key needs to be specified, therefore a List needs to be retrieved. <map>For any key in the preview result set's map, check if it contains an id column. Add the key to the list if it doesn't have an id column. Here's an example with no id column:

[0072] (3) Retrieve the key from the map, concatenate it with backticks "`" and the string, and add an id column so that it can be directly used when concatenating the table creation statement.

[0073] (4) Execute the concatenated table creation statement using the connection.createStatement().executeQuery(sql) statement, where the dataset name tableName is passed as the table name, sql = "create table`"+

[0074] The function `tableName+"`+col+"Engine=MergeTree()primary key id` is used to handle exceptions for tables that already exist in the database and returns the result.

[0075] (5) Traversing the List <map>The values ​​array is obtained by concatenating the values ​​corresponding to each data entry. The value is then assigned to the corresponding position in the id column using generateUUIDv4(). To avoid insertion failure caused by single quote injection, the single quotes are replaced with two single quotes.

[0076] (6) The concatenated insert statement sql2 = "insert into`"+tableName+"`"+"(id,"+String.join(",",colunminsert)+")"+"values"+String.join(",",values) is executed by connection.createStatement().executeQuery(sql2) to perform the insert operation on the target dataset.

[0077] The ClickHouse implementation in this specification surpasses the performance of most columnar storage databases and supports SQL-like queries. By unifying datasets from different sources under ClickHouse through the aforementioned multi-data source management method, the advantages of ClickHouse's speed and functionality can be effectively utilized, improving dataset management efficiency. Furthermore, this specification's implementation method allows datasets from different files to be formatted into highly organized and structured data with clear relationships, making data use and management very convenient. In addition, the above method supports connecting to the database via the user interface for multi-table joins, and after confirming the preview result set, tables are created and inserted into the ClickHouse database, avoiding errors that may occur from directly writing SQL statements, and eliminating the need for intermediate tables to summarize data.

[0078] It should be noted that the embodiments in this specification support connecting to a MySQL database for multi-table conditional queries and using local Excel, CSV, and other files as dataset sources. The result dataset is uniformly stored using the columnar storage database ClickHouse, and a dataset preview interface is provided for previewing and confirming the result set. Multi-table conditional queries on the MySQL database are performed by concatenating table name parameters and query condition parameters passed from the user interface using the JDBC interface. Unformatted files with extensions .xls and .xlsx are parsed and formatted using the HSSFWorkbook and XSSFWorkbook implementation classes provided by POI. Datasets from the MySQL database and local files are stored as a List. <map>The dataset is provided in a format that allows for previewing. After previewing and confirming, the dataset is used to create tables and insert them into the ClickHouse database via JDBC, leveraging the advantages of columnar databases—fast query speed and high data compression ratio—to achieve unified dataset management.

[0079] Figure 4 This is a schematic diagram of a dataset insertion device provided in one or more embodiments of this specification. The device includes: a parsing unit 402, an output unit 404, and an insertion unit 406.

[0080] Parsing unit 402 parses the target dataset into a specified format dataset when passing parameters in MySQL database connection information or local file;

[0081] Output unit 404 outputs the specified format dataset through a specified interface and previews and confirms the specified format dataset;

[0082] Insertion unit 406 traverses and concatenates the specified format dataset after preview confirmation into an SQL execution statement, and stores the SQL execution statement in a pre-set ClickHouse database to realize the insertion operation of the target dataset.

[0083] Figure 5 A schematic diagram of the structure of a dataset insertion device provided for one or more embodiments of this specification includes:

[0084] At least one processor; and,

[0085] A memory communicatively connected to the at least one processor; wherein,

[0086] The memory stores instructions executable by the at least one processor, which, when executed by the at least one processor, enable the at least one processor to:

[0087] When passing parameters in MySQL database connection information or local file, parse the target dataset into a dataset of the specified format;

[0088] The specified format dataset is output through the specified interface, and the specified format dataset is previewed and confirmed.

[0089] The dataset in the specified format, after being previewed and confirmed, is traversed and concatenated into an SQL execution statement, which is then stored in a pre-defined ClickHouse database to perform the insertion operation on the target dataset.

[0090] This specification provides one or more embodiments of a non-volatile computer storage medium storing computer-executable instructions, wherein the computer-executable instructions are configured as follows:

[0091] When passing parameters in MySQL database connection information or local file, parse the target dataset into a dataset of the specified format;

[0092] The specified format dataset is output through the specified interface, and the specified format dataset is previewed and confirmed.

[0093] The dataset in the specified format, after being previewed and confirmed, is traversed and concatenated into an SQL execution statement, which is then stored in a pre-defined ClickHouse database to perform the insertion operation on the target dataset.

[0094] The various embodiments in this specification are described in a progressive manner. Similar or identical parts between embodiments can be referred to mutually. Each embodiment focuses on describing the differences from other embodiments. In particular, the embodiments of apparatus, devices, and non-volatile computer storage media are basically similar to the method embodiments, so the descriptions are relatively simple; relevant parts can be referred to the descriptions of the method embodiments.

[0095] The foregoing has described specific embodiments of this specification. Other embodiments are within the scope of the appended claims. In some cases, the actions or steps recited in the claims may be performed in a different order than that shown in the embodiments and may still achieve the desired result. Furthermore, the processes depicted in the drawings do not necessarily require the specific or sequential order shown to achieve the desired result. In some embodiments, multitasking and parallel processing are possible or may be advantageous.

[0096] The above description is merely one or more embodiments of this specification and is not intended to limit this specification. Various modifications and variations can be made to the one or more embodiments of this specification by those skilled in the art. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principle of one or more embodiments of this specification should be included within the scope of the claims of this specification.< / map> < / map> < / map> < / map> < / map> < / map>

Claims

1. A method for inserting a dataset, characterized in that, The method includes: When passing parameters in MySQL database connection information or local file, parse the target dataset into a dataset of the specified format; Output the dataset in the specified format through the specified interface, and preview and confirm the dataset in the specified format; The dataset in the specified format, after being previewed and confirmed, is traversed and concatenated into an SQL execution statement, and the SQL execution statement is stored in a pre-set ClickHouse database to realize the insertion operation of the target dataset; The step of parsing the target dataset into a specified format dataset includes: When the source of the target dataset is a local file, determine the file extension type of the local file; The parsing method for the target dataset is determined based on the file extension type of the local file; Based on the parsing method of the target dataset, the target dataset is parsed into a dataset of a specified format; The file extension types include excel file type and csv file type; If the file extension of the local file is the excel file type, determine the first parsing method corresponding to the target dataset; If the file extension of the local file is CSV, determine the second parsing method corresponding to the target dataset; The steps of determining the first parsing method corresponding to the target dataset and parsing the target dataset into a specified format dataset according to the parsing method of the target dataset include: The HSSFWorkbook class is used to parse .xls format files, and the XSSFWorkbook class is used to parse .xlsx format files. The sheet page is specified by passing parameters. For files with no sheet page parameter passed, the first sheet page is parsed by default. After obtaining the Sheet object, the header columns of the file are obtained through the getRow(0) method and the total number of header columns is obtained through the getPhysicalNumberOfCells() method. The Row object is traversed and the column name of each column is obtained in turn through getCellFormatValue(row.getCell(i)), and then assembled into an array of column names. After obtaining the header column name array String[] head, the Sheet object is traversed according to the total number of rows in getLastRowNum() and the total number of columns in getPhysicalNumberOfCells(), and each row is read into a Map to obtain the specified format dataset. The step of determining the second parsing method corresponding to the target dataset, and the step of parsing the target dataset into a specified format dataset according to the parsing method of the target dataset, includes: Use the BufferedReader class to read the file into the buffer, and use bufferedReader.readLine().split(",") to read the first row of header information separated by commas to get the column name array; The BufferedReader's readLine() method is used to traverse the file to be parsed. When reading each value, a regular expression is used to ignore commas in quotes to avoid unnecessary parsing errors, thus obtaining the dataset in the specified format. The step of iterating through and concatenating the specified format dataset after preview confirmation into an SQL execution statement, and storing the SQL execution statement in a pre-defined ClickHouse database to implement the insertion operation of the target dataset includes: Connect to the ClickHouse database used to store data tables via the JDBC interface; When creating the table in ClickHouse, specify the primary key (primarykey), retrieve any key from any map in the preview result set of the dataset in the specified format, determine whether it contains an id column, and add the id column to the list if it does not. Retrieve the keys from the map, concatenate them, and add an id column; The concatenated table creation statement is executed through the connection.createStatement().executeQuery(sql) statement, where the dataset name is passed as the table name, sql = " create table `" + tableName + "`"+ col + " Engine= MergeTree() primary key id", and exceptions for tables that already exist in the database are caught and returned. Iterate through the specified format dataset, concatenate the values ​​corresponding to each data entry to obtain the values ​​array, and assign the value to the corresponding position in the id column using generateUUIDv4(). The concatenated insert statement `sql2 = "insert into `" + tableName + "`" + "(id," + String.join(",",colunminsert) + ")" + " values ​​" + String.join(",", values)` is executed using `connection.createStatement().executeQuery(sql2)` to perform the insert operation on the target dataset.

2. The method according to claim 1, characterized in that, The step of parsing the target dataset into a specified format dataset includes: When the target dataset originates from a MySQL database, the connection address of the MySQL database is constructed by concatenating the passed IP address, port number, and database name, and the connection to the MySQL database is tested to see if it is successful. If so, access the MySQL database, obtain the table names of the MySQL database, obtain the field names within the MySQL database tables, and construct the conditional query statement.

3. A device for inserting a dataset, characterized in that, The device includes: The parsing unit parses the target dataset into a specified format when passing parameters in MySQL database connection information or local file. The output unit outputs the dataset in the specified format through a specified interface and previews and confirms the dataset in the specified format. The insertion unit iterates through and concatenates the specified format dataset after preview confirmation into an SQL execution statement, and stores the SQL execution statement in a pre-set ClickHouse database to realize the insertion operation of the target dataset; The step of parsing the target dataset into a specified format dataset includes: When the source of the target dataset is a local file, determine the file extension type of the local file; The parsing method for the target dataset is determined based on the file extension type of the local file; Based on the parsing method of the target dataset, the target dataset is parsed into a dataset of a specified format; The file extension types include excel file type and csv file type; If the file extension of the local file is the excel file type, determine the first parsing method corresponding to the target dataset; If the file extension of the local file is CSV, determine the second parsing method corresponding to the target dataset; The steps of determining the first parsing method corresponding to the target dataset and parsing the target dataset into a specified format dataset according to the parsing method of the target dataset include: The HSSFWorkbook class is used to parse .xls format files, and the XSSFWorkbook class is used to parse .xlsx format files. The sheet page is specified by passing parameters. For files with no sheet page parameter passed, the first sheet page is parsed by default. After obtaining the Sheet object, the header columns of the file are obtained through the getRow(0) method and the total number of header columns is obtained through the getPhysicalNumberOfCells() method. The Row object is traversed and the column name of each column is obtained in turn through getCellFormatValue(row.getCell(i)), and then assembled into an array of column names. After obtaining the header column name array String[] head, the Sheet object is traversed according to the total number of rows in getLastRowNum() and the total number of columns in getPhysicalNumberOfCells(), and each row is read into a Map to obtain the specified format dataset. The step of determining the second parsing method corresponding to the target dataset, and the step of parsing the target dataset into a specified format dataset according to the parsing method of the target dataset, includes: Use the BufferedReader class to read the file into the buffer, and use bufferedReader.readLine().split(",") to read the first row of header information separated by commas to get the column name array; The BufferedReader's readLine() method is used to traverse the file to be parsed. When reading each value, a regular expression is used to ignore commas in quotes to avoid unnecessary parsing errors, thus obtaining the dataset in the specified format. The step of iterating through and concatenating the specified format dataset after preview confirmation into an SQL execution statement, and storing the SQL execution statement in a pre-defined ClickHouse database to implement the insertion operation of the target dataset includes: Connect to the ClickHouse database used to store data tables via the JDBC interface; When creating the table in ClickHouse, specify the primary key (primarykey), retrieve any key from any map in the preview result set of the dataset in the specified format, determine whether it contains an id column, and add the id column to the list if it does not. Retrieve the keys from the map, concatenate them, and add an id column; The concatenated table creation statement is executed through the connection.createStatement().executeQuery(sql) statement, where the dataset name is passed as the table name, sql = " create table `" + tableName + "`"+ col + " Engine= MergeTree() primary key id", and exceptions for tables that already exist in the database are caught and returned. Iterate through the specified format dataset, concatenate the values ​​corresponding to each data entry to obtain the values ​​array, and assign the value to the corresponding position in the id column using generateUUIDv4(). The concatenated insert statement `sql2 = "insert into `" + tableName + "`" + "(id," + String.join(",",colunminsert) + ")" + " values ​​" + String.join(",", values)` is executed using `connection.createStatement().executeQuery(sql2)` to perform the insert operation on the target dataset.

4. A dataset insertion device, characterized in that, include: At least one processor; as well as, A memory communicatively connected to the at least one processor; wherein, The memory stores instructions executable by the at least one processor, which, when executed by the at least one processor, enable the at least one processor to: When passing parameters in MySQL database connection information or local file, parse the target dataset into a dataset of the specified format; Output the dataset in the specified format through the specified interface, and preview and confirm the dataset in the specified format; The dataset in the specified format, after being previewed and confirmed, is traversed and concatenated into an SQL execution statement, and the SQL execution statement is stored in a pre-set ClickHouse database to realize the insertion operation of the target dataset; The step of parsing the target dataset into a specified format dataset includes: When the source of the target dataset is a local file, determine the file extension type of the local file; The parsing method for the target dataset is determined based on the file extension type of the local file; Based on the parsing method of the target dataset, the target dataset is parsed into a dataset of a specified format; The file extension types include excel file type and csv file type; If the file extension of the local file is the excel file type, determine the first parsing method corresponding to the target dataset; If the file extension of the local file is CSV, determine the second parsing method corresponding to the target dataset; The steps of determining the first parsing method corresponding to the target dataset and parsing the target dataset into a specified format dataset according to the parsing method of the target dataset include: The HSSFWorkbook class is used to parse .xls format files, and the XSSFWorkbook class is used to parse .xlsx format files. The sheet page is specified by passing parameters. For files with no sheet page parameter passed, the first sheet page is parsed by default. After obtaining the Sheet object, the header columns of the file are obtained through the getRow(0) method and the total number of header columns is obtained through the getPhysicalNumberOfCells() method. The Row object is traversed and the column name of each column is obtained in turn through getCellFormatValue(row.getCell(i)), and then assembled into an array of column names. After obtaining the header column name array String[] head, the Sheet object is traversed according to the total number of rows in getLastRowNum() and the total number of columns in getPhysicalNumberOfCells(), and each row is read into a Map to obtain the specified format dataset. The step of determining the second parsing method corresponding to the target dataset, and the step of parsing the target dataset into a specified format dataset according to the parsing method of the target dataset, includes: Use the BufferedReader class to read the file into the buffer, and use bufferedReader.readLine().split(",") to read the first row of header information separated by commas to get the column name array; The BufferedReader's readLine() method is used to traverse the file to be parsed. When reading each value, a regular expression is used to ignore commas in quotes to avoid unnecessary parsing errors, thus obtaining the dataset in the specified format. The step of iterating through and concatenating the specified format dataset after preview confirmation into an SQL execution statement, and storing the SQL execution statement in a pre-defined ClickHouse database to implement the insertion operation of the target dataset includes: Connect to the ClickHouse database used to store data tables via the JDBC interface; When creating the table in ClickHouse, specify the primary key (primarykey), retrieve any key from any map in the preview result set of the dataset in the specified format, determine whether it contains an id column, and add the id column to the list if it does not. Retrieve the keys from the map, concatenate them, and add an id column; The concatenated table creation statement is executed through the connection.createStatement().executeQuery(sql) statement, where the dataset name is passed as the table name, sql = " create table `" + tableName + "`"+ col + " Engine= MergeTree() primary key id", and exceptions for tables that already exist in the database are caught and returned. Iterate through the specified format dataset, concatenate the values ​​corresponding to each data entry to obtain the values ​​array, and assign the value to the corresponding position in the id column using generateUUIDv4(). The concatenated insert statement `sql2 = "insert into `" + tableName + "`" + "(id," + String.join(",",colunminsert) + ")" + " values ​​" + String.join(",", values)` is executed using `connection.createStatement().executeQuery(sql2)` to perform the insert operation on the target dataset.

5. A non-volatile computer storage medium, characterized in that, The computer-executable instructions are stored thereon and are configured as follows: When passing parameters in MySQL database connection information or local file, parse the target dataset into a dataset of the specified format; Output the dataset in the specified format through the specified interface, and preview and confirm the dataset in the specified format; The dataset in the specified format, after being previewed and confirmed, is traversed and concatenated into an SQL execution statement, and the SQL execution statement is stored in a pre-set ClickHouse database to realize the insertion operation of the target dataset; The step of parsing the target dataset into a specified format dataset includes: When the source of the target dataset is a local file, determine the file extension type of the local file; The parsing method for the target dataset is determined based on the file extension type of the local file; Based on the parsing method of the target dataset, the target dataset is parsed into a dataset of a specified format; The file extension types include excel file type and csv file type; If the file extension of the local file is the excel file type, determine the first parsing method corresponding to the target dataset; If the file extension of the local file is CSV, determine the second parsing method corresponding to the target dataset; The steps of determining the first parsing method corresponding to the target dataset and parsing the target dataset into a specified format dataset according to the parsing method of the target dataset include: The HSSFWorkbook class is used to parse .xls format files, and the XSSFWorkbook class is used to parse .xlsx format files. The sheet page is specified by passing parameters. For files with no sheet page parameter passed, the first sheet page is parsed by default. After obtaining the Sheet object, the header columns of the file are obtained through the getRow(0) method and the total number of header columns is obtained through the getPhysicalNumberOfCells() method. The Row object is traversed and the column name of each column is obtained in turn through getCellFormatValue(row.getCell(i)), and then assembled into an array of column names. After obtaining the header column name array String[] head, the Sheet object is traversed according to the total number of rows in getLastRowNum() and the total number of columns in getPhysicalNumberOfCells(), and each row is read into a Map to obtain the specified format dataset. The step of determining the second parsing method corresponding to the target dataset, and the step of parsing the target dataset into a specified format dataset according to the parsing method of the target dataset, includes: Use the BufferedReader class to read the file into the buffer, and use bufferedReader.readLine().split(",") to read the first row of header information separated by commas to get the column name array; The BufferedReader's readLine() method is used to traverse the file to be parsed. When reading each value, a regular expression is used to ignore commas in quotes to avoid unnecessary parsing errors, thus obtaining the dataset in the specified format. The step of iterating through and concatenating the specified format dataset after preview confirmation into an SQL execution statement, and storing the SQL execution statement in a pre-defined ClickHouse database to implement the insertion operation of the target dataset includes: Connect to the ClickHouse database used to store data tables via the JDBC interface; When creating the table in ClickHouse, specify the primary key (primarykey), retrieve any key from any map in the preview result set of the dataset in the specified format, determine whether it contains an id column, and add the id column to the list if it does not. Retrieve the keys from the map, concatenate them, and add an id column; The concatenated table creation statement is executed through the connection.createStatement().executeQuery(sql) statement, where the dataset name is passed as the table name, sql = " create table `" + tableName + "`"+ col + " Engine= MergeTree() primary key id", and exceptions for tables that already exist in the database are caught and returned. Iterate through the specified format dataset, concatenate the values ​​corresponding to each data entry to obtain the values ​​array, and assign the value to the corresponding position in the id column using generateUUIDv4(). The concatenated insert statement `sql2 = "insert into `" + tableName + "`" + "(id," + String.join(",",colunminsert) + ")" + " values ​​" + String.join(",", values)` is executed using `connection.createStatement().executeQuery(sql2)` to perform the insert operation on the target dataset.

Images