A presto-based data loading method, device and electronic equipment

Abstract

The application belongs to the technical field of data processing, and particularly relates to a presto-based data loading method and device and electronic equipment. The method acquires a database SQL statement input by a user, configures a mapping relationship of a kafka schema according to the database SQL statement, wherein the kafka schema defines a mapping relationship of presto and kafka metadata, loads kafka metadata according to the database SQL statement, realizes presto dynamic loading of kafka data, avoids restarting a presto master node, and solves the problem that presto cannot provide external services due to restarting of the presto master node when presto loads kafka data.

Description

TECHNICAL FIELD

[0001] The application belongs to the technical field of data processing, and particularly relates to a presto-based data loading method and device and electronic equipment. BACKGROUND

[0002] Presto is an MPP (Massive Parallel Processing) SQL engine open-sourced by Facebook, and its concept originates from a parallel database called Volcano, which proposes a model for parallel execution of SQL. The database is designed to be used for high-speed, real-time data analysis. Presto is a SQL computing engine that separates the computing layer and the storage layer, does not store data, and accesses various data sources (storage) through Connector SPI. Kafka was initially developed by LinkedIn and is a distributed, partitioned, multi-replica, multi-subscriber, and zookeeper-coordinated distributed log system (which can also be used as an MQ system). It can be commonly used for web / nginx logs, access logs, message services, etc. LinkedIn contributed it to the Apache Foundation in 2010 and made it a top-level open source project.

[0003] At present, the existing presto queries data in Kafka is defined by a static configuration file JSON to define the kafka schema. The schema defines the mapping relationship between the field in the Kafka message data and the column in the presto table. The existing scheme has the following problems:

[0004] 1. If the schema changes, such as Kafka needs to add a field mapping or delete a field mapping, the configuration file needs to be edited to save the latest mapping relationship, and the presto master node instance also needs to be restarted to load the latest schema.

[0005] 2. If a certain topic of Kafka is offline, it means that the relative table in presto needs to be cleared. At this time, the presto master node instance needs to be restarted to refresh the metadata information in the memory.

[0006] For the above problems, the presto master node needs to be restarted, which means that the presto service needs to be stopped, and presto cannot provide services to the outside during this period, which is not user-friendly in terms of user experience. SUMMARY

[0007] Therefore, the present application aims to provide a presto-based data loading method, device and electronic equipment to solve the problem that presto cannot provide services externally due to restarting the presto master node when loading kafka data.

[0008] According to a first aspect of the embodiments of the present application, a presto-based data loading method is provided, comprising:

[0009] obtaining a database SQL statement input by a user, wherein the database SQL statement is used to configure a mapping relationship of a kafka schema, and the kafka schema is used to define a mapping relationship of presto and kafka metadata;

[0010] loading the kafka metadata according to the database SQL statement.

[0011] Preferably, the loading of the kafka metadata according to the database SQL statement comprises:

[0012] parsing the database SQL statement to determine syntax information, target table information, and / or additional information;

[0013] configuring the mapping relationship of the kafka schema according to the syntax information, the target table information, and / or the additional information;

[0014] loading the kafka metadata according to the mapping relationship.

[0015] Preferably, the configuring of the mapping relationship of the kafka schema according to the syntax information, the target table information, and / or the additional information comprises:

[0016] if the syntax information is a create table statement, assembling the target table information and the additional information into the mapping relationship of the kafka schema;

[0017] if the syntax information is an alter table statement, updating the mapping relationship of the kafka schema according to the determined target table information;

[0018] if the syntax information is a drop table statement, deleting the mapping relationship of the kafka schema according to the determined target table information.

[0019] Preferably, the target table information includes at least one of: a table name, column information; wherein the table name is stored in table, the table is used to store the table name of the mapping relationship of the kafka schema configuration, the column information is stored in columns, the columns are used to store the column information of the mapping relationship of the kafka schema configuration; the additional information is stored in properties, and the properties are used to store the additional information of the mapping relationship of the kafka schema configuration.

[0020] Preferably, the method comprises:

[0021] The mapping relationship is stored in redis; wherein the mapping relationship is JSON format data.

[0022] Preferably, the method further comprises:

[0023] Display the loaded kafka metadata. According to a second aspect of the embodiment of the present application, a presto-based data loading device is provided, comprising:

[0024] The acquisition module is configured to acquire a database SQL statement input by a user; wherein the database SQL statement is used to configure the mapping relationship of the kafka schema, and the kafka schema is used to define the mapping relationship of presto and kafka metadata;

[0025] The loading module is configured to load kafka metadata according to the database SQL statement;

[0026] The storage module is configured to store the mapping relationship;

[0027] The display module is configured to display the loaded kafka metadata.

[0028] According to a third aspect of the embodiment of the present application, an electronic device is provided, comprising: the presto-based data loading device.

[0029] The technical scheme provided by the embodiment of the present application can include the following beneficial effects:

[0030] The mapping relationship of the kafka schema is configured according to the database SQL statement input by the user, wherein the kafka schema defines the mapping relationship of presto and kafka metadata, the kafka metadata is loaded according to the database SQL statement, presto dynamically loads kafka data, and the presto master node is restarted, thereby solving the problem that presto cannot provide external services when presto loads kafka data.

[0031] It should be understood that the foregoing general description and the following detailed description are only exemplary and explanatory, and are not limiting to the present application. BRIEF DESCRIPTION OF DRAWINGS

[0032] The accompanying drawings incorporated in and forming a part of the specification, illustrate embodiments consistent with the present application and, together with the description, serve to explain the principles of the application.

[0033] Figure 1 is a flow chart of a presto-based data loading method according to an exemplary embodiment;

[0034] Figure 2 is a flow chart of a presto-based data loading method according to another exemplary embodiment;

[0035] Figure 3 is a block diagram of a presto-based data loading device according to an exemplary embodiment. DETAILED DESCRIPTION

[0036] The exemplary embodiments will be described in detail herein with reference to the attached drawings; like numbers refer to like elements throughout. The following detailed description is not meant to limit the present application to all of the embodiments described herein, but rather to illustrate potential embodiments of the present application. Other embodiments not described can also be possible.

[0037] Reference will now be made to Figure 1 , Figure 1 is a flow chart of a presto-based data loading method according to an exemplary embodiment, as shown in Figure 1 , the presto-based data loading method comprises:

[0038] Step S11, obtaining a database SQL statement input by a user; wherein the database SQL statement is used to configure a mapping relationship of a kafka schema, and the kafka schema is used to define a mapping relationship of presto and kafka metadata;

[0039] Step S12, loading kafka metadata according to the database SQL statement.

[0040] It should be noted that in specific practice, the presto-based data loading method provided in the embodiment needs to be loaded in a controller of an electronic device for running.

[0041] The electronic device includes but is not limited to a mobile phone, a tablet computer, a desktop computer, and the like.

[0042] The "database SQL statement" mentioned in step S11 includes create table, delete a column in a table, and add a column in a table (alter table), and delete a table (drop table).

[0043] The "kafka schema" mentioned in step S11 is used to define a mapping relationship of presto and kafka metadata.

[0044] It can be understood that by obtaining a database SQL statement input by a user, a mapping relationship of a kafka schema is configured according to the database SQL statement, wherein the kafka schema defines a mapping relationship of presto and kafka metadata, and kafka metadata is loaded according to the database SQL statement, presto dynamically loads kafka data, and the problem that presto cannot provide services externally due to restarting of a presto master node when presto loads kafka data is solved.

[0045] In specific practice, the method further includes displaying the loaded kafka metadata.

[0046] In specific practice, a user can query the loaded kafka metadata by using a jdbc method. For example, the user can query and display the loaded metadata by using a SQL statement of select * from table name in a presto CLI.

[0047] In specific practice, step S12 of loading kafka metadata according to the database SQL statement includes:

[0048] 1. Parsing the database SQL statement to determine syntax information, target table information, and / or additional information.

[0049] It should be noted that the target table information includes at least one of the following: table name, column information; wherein the table name is stored in table, which is used to store the table name of the mapping relationship of the kafka schema configuration, the column information is stored in columns, which is used to store the column information of the mapping relationship of the kafka schema configuration; the additional information is stored in properties, which is used to store the additional information of the mapping relationship of the kafka schema configuration. The syntax information is used to explain the use of the database SQL statement, and the syntax information can include: create table statement, alter table statement and drop table statement.

[0050] 2. Configuring the mapping relationship of the kafka schema according to the syntax information, the target table information, and / or the additional information, specifically:

[0051] If the syntax information is a create table statement, the target table information and the additional information are assembled to configure the mapping relationship of the kafka schema.

[0052] In specific practice, taking the creation of the order table as an example, the create table statement is specifically: Create table order (id int, brand varchar, amount double, sku varchar) with (kafka_topic='order_topic', kafka_consume_mode='begin', kafka_fields_mapping='id:id,brand:brand,amount:amount,sku:sku');

[0053] It should be noted that: here order is the table name, id, brand, amount, sku from presto, columns of the table; with additional information kafa topic indicates that presto table and kafka topic: order topic are mapped, kafka_consume_mode indicates that presto queries data from kafka order_topic, and generally is begin or last, kafka_fields_mapping indicates the mapping relationship between presto table: order column and kafka topic: topic_order field.

[0054] According to the create table statement, the target table information and the additional information, the mapping relationship of the kafka schema is assembled, and specifically, please refer to Figure 2 , Figure 2 is a flow chart of a presto-based data loading method according to another exemplary embodiment, as Figure 2 shown, presto master node receives create table order statement sent by presto cli, first performs parsing operation (according to modified parsing rule, attributes such as kafka_topic, kafka_consume_mode and kafka_fields_mapping can be recognized), parses out table and stores into ConnectorTableMetadata field table, parses out column and stores into ConnectorTableMetadata field columns, parses out with additional information kafka_topic, kafka_consume_mode and kafka_fields_mapping, and stores into ConnectorTableMetadata field properties.

[0055] Secondly, in the prestokafka connector component, the order table is taken out from the ConnectorTableMetadata field table, all column fields of the order table, i.e., id, brand, amount, and sku, are taken out from the ConnectorTableMetadata field columns. Then, additional information of with, i.e., kafka_topic, kafka_consume_mode, kafka_fields_mapping, and the like, is taken out from the ConnectorTableMetadata field properties. Then, the above information is combined to form a KafkaTopicDescription, and then, the key is order and the value is KafkaTopicDescription, which are saved in the tableDescriptions data structure as table metadata.

[0056] If the syntax information is an alter table statement, the mapping relationship of the kafka schema is updated according to the determined target table information;

[0057] In specific practice, updating the mapping relationship of the kafka schema includes adding column information and deleting column information. For example, a color field is added to the order table (color column information is added), and the type is varchar, and the statement is as follows: alter table order add column color varchar with (field_column_mapping = 'color:color');

[0058] The brand column of the order table is deleted, and the statement is as follows: delete column: alter table order drop column brand;

[0059] According to the alter table statement and the target table information, the mapping relationship of the kafka schema is updated. For details, please refer to Figure 2 , Figure 2 is a flowchart of a presto-based data loading method according to another exemplary embodiment, as shown in Figure 2As shown, after the prestomaster node receives the alter table order statement sent by the user through the prestocli, first, a parsing operation is performed (the kafka_fields_mapping attribute can be recognized according to the modified parsing rule), the table is parsed and stored in the ConnectorTableMetadata field table, the column is parsed and stored in the ConnectorTableMetadata field columns, and the with additional information kafka_fields_mapping (there is no such operation for deleting a column) is parsed and stored in the ConnectorTableMetadata field properties.

[0060] Then, for the operation of adding a column, the prestokafka connector component takes out the order table from the ConnectorTableMetadata field table, obtains the KafkaTopicDescription from the tableDescriptions data structure according to the table name order, then takes out the with additional information kafka_fields_mapping information from the ConnectorTableMetadata field properties, obtains the added column color and the data type varchar, updates the KafkaTopicDescription, and stores the updated KafkaTopicDescription again in the tableDescriptions data structure.

[0061] For the operation of deleting a column, the prestokafka connector component takes out the order table from the ConnectorTableMetadata field table, obtains the KafkaTopicDescription from the tableDescriptions data structure according to the table name order, then deletes the column brand from the KafkaTopicDescription, and then stores the updated KafkaTopicDescription again in the tableDescriptions data structure.

[0062] If the syntax information is a drop table statement, the mapping relationship of the kafkaschema is deleted according to the determined target table information.

[0063] In specific practice, taking deleting the order table as an example, the drop table statement is specifically: drop table order.

[0064] According to the drop table statement, the target table information deletes the mapping relationship of the kafka schema. Specifically, please refer to Figure 2 , Figure 2 is a flow chart of a presto-based data loading method according to another exemplary embodiment, as Figure 2 shown, after the presto master node receives the drop table order statement sent by the user through the presto cli, it first performs a parsing operation to parse the table into the ConnectorTableMetadata field table.

[0065] Then, the presto kafka connector component takes out the order table from the ConnectorTableMetadata field table, and deletes the corresponding KafkaTopicDescription from the tableDescriptions data structure according to the table name order.

[0066] 3. Load the kafka metadata according to the mapping relationship.

[0067] In specific practice, the presto master node will further obtain the corresponding column and data type information of kafka according to the target table name obtained by parsing and the mapping relationship of presto and kafka metadata defined in the kafka schema corresponding to the target table name.

[0068] 4. Store the mapping relationship in redis.

[0069] In specific practice, the mapping relationship is JSON format data. It can be understood that storing the mapping relationship in redis can obtain the metadata from redis again and update it to the memory after the presto master service is restarted.

[0070] Please refer to Figure 3 , Figure 3 is a block diagram of a presto-based data loading device according to an exemplary embodiment, as Figure 3 shown, the presto-based data loading device 100 comprises:

[0071] The acquisition module 101 is configured to acquire a database SQL statement input by a user; wherein the database SQL statement is used to configure the mapping relationship of the kafka schema, and the kafka schema is used to define the mapping relationship of presto and kafka metadata.

[0072] The loading module 102 is configured to load the kafka metadata according to the database SQL statement.

[0073] The storage module 103 is configured to store the mapping relationship.

[0074] The display module 104 is configured to display the loaded kafka metadata.

[0075] The obtaining module 101, the loading module 102, the storage module 103, and the display module 104 of the presto-based data loading device 100 in the embodiment perform the specific manners of the above method, which have been described in detail in the embodiment related to the method, and will not be described in detail here.

[0076] It can be understood that the technical solution provided by the embodiment acquires the database SQL statement input by the user, configures the mapping relationship of the kafka schema according to the database SQL statement, wherein the kafka schema defines the mapping relationship between presto and kafka metadata, loads the kafka metadata according to the database SQL statement, realizes presto dynamic loading of kafka data, avoids restarting the presto master node, and solves the problem that presto cannot provide services externally due to restarting the presto master node when loading kafka data.

[0077] According to an example embodiment, an electronic device is shown, which includes the presto-based data loading device described above.

[0078] The electronic device including the presto-based data loading device in the above embodiment performs the specific manner of the above method by the presto-based data loading device, which has been described in detail in the embodiment related to the method, and will not be described in detail here.

[0079] In specific practice, the electronic device can be a mobile phone, a tablet computer, a desktop computer, etc. installed with the presto-based data loading device.

[0080] It can be understood that the technical scheme provided by the embodiment includes the presto-based data loading device, and the presto-based data loading device loads the kafka data dynamically by obtaining the database SQL statement input by the user, configuring the mapping relationship of the kafka schema according to the database SQL statement, loading the kafka metadata according to the database SQL statement, and defining the mapping relationship of presto and kafka metadata, thereby avoiding restarting the presto master node, and solving the problem that the presto master node cannot provide services externally when loading the kafka data.

[0081] In addition, the present application provides a computer readable storage medium, which stores computer instructions for making a computer execute the steps of any of the above methods. The storage medium can be a magnetic disk, an optical disk, a read-only memory (ROM), a random access memory (RAM), a flash memory, a hard disk drive (HDD) or a solid-state drive (SSD), etc. The storage medium can also include a combination of the above types of memories.

[0082] It can be understood that the same or similar parts in the above embodiments can be mutually referred to, and the content not described in detail in some embodiments can refer to the same or similar content in other embodiments.

[0083] It should be noted that in the description of the present application, the terms "first", "second" and the like are only used for descriptive purposes, and cannot be understood as indicating or implying relative importance. In addition, in the description of the present application, unless otherwise specified, the meaning of "a plurality of" or "multiple" is at least two.

[0084] It should be understood that when an element is referred to as "fixed to" or "disposed on" another element, it can be directly on the other element or there can be a middle element; when an element is referred to as "connected to" another element, it can be directly connected to the other element or there can be a middle element, in addition, "connected" used herein can include wireless connection; the phrase "and / or" used herein includes any unit and all combinations of the associated listed items.

[0085] Any processes or methods described in the flowcharts or elsewhere in this specification can be understood as representing one or more modules, segments, or portions of code that includes executable instructions for performing specific logical functions or steps, and the various embodiments of the application can include additional or fewer steps performing the same or equivalent functions in the same or equivalent order as those described and shown in the figures. The various embodiments of the application can be implemented in software, hardware, firmware, or a combination thereof.

[0086] It should be understood that portions of the application can be implemented in hardware, software, firmware, or combinations thereof. In the above embodiments, the various steps or methods can be implemented in software or firmware that is stored in memory and executed by a suitable instruction execution system. For example, if implemented in hardware, and in another embodiment, any of the following technologies, known in the art, or their combinations can be used: discrete logic circuitry having logic gates for implementing logic functions on data signals, application specific integrated circuits having appropriate combinational logic gates, programmable gate arrays (PGA), field programmable gate arrays (FPGA), and the like.

[0087] Those skilled in the art can understand that all or part of the steps carried out by the above-mentioned embodiments can be completed by programs instructing related hardware, and the programs can be stored in a computer readable storage medium, and when executed, include one or a combination of steps of the method embodiments.

[0088] In addition, the functional units in each embodiment of the present application can be integrated into one processing module, or each unit can be physically present separately, or two or more units can be integrated into one module. The above-mentioned integrated module can be realized in the form of hardware or in the form of a software function module. When the integrated module is realized in the form of a software function module and sold or used as an independent product, it can also be stored in a computer readable storage medium.

[0089] The storage medium mentioned above can be a read-only memory, a magnetic disk or an optical disk, etc.

[0090] In the description of the present specification, the description of the terms "one embodiment", "some embodiments", "example", "specific example", or "some examples" and the like means that the specific features, structures, materials or characteristics described in conjunction with the embodiment or example are included in at least one embodiment or example of the present application. In the present specification, the illustrative description of the above terms does not necessarily refer to the same embodiment or example. Moreover, the specific features, structures, materials or characteristics described can be combined in any one or more embodiments or examples in a suitable manner.

[0091] Although the embodiments of the present application have been shown and described above, it is understood that the above-described embodiments are exemplary and are not to be construed as limiting the present application, and that those skilled in the art can make changes, modifications, substitutions and variations to the above-described embodiments within the scope of the present application.

Claims

1. A data loading method based on Presto, characterized in that, include: Obtain the database SQL statement input by the user; wherein, the database SQL statement is used to configure the Kafka schema, and the Kafka schema is used to define the mapping relationship between Presto and Kafka metadata; Load Kafka metadata based on the database SQL statement, including: The SQL statements in the database are parsed to determine syntax information, target table information, and / or additional information; The mapping relationship of the Kafka schema is configured according to the syntax information, target table information, and / or the additional information, including: if the syntax information is a `CREATE TABLE` statement, then the mapping relationship of the Kafka schema is assembled by combining the target table information and the additional information; if the syntax information is an `ALTER TABLE` statement, then the mapping relationship of the Kafka schema is updated according to the determined target table information; if the syntax information is a `DROP TABLE` statement, then the mapping relationship of the Kafka schema is deleted according to the determined target table information; wherein, the target table information includes at least one item: table name and column information; wherein, the table name is stored in `table`, and `table` is used to store the table name configured for the Kafka schema mapping relationship, and the column information is stored in `columns`, and `columns` is used to store the column information configured for the Kafka schema mapping relationship; the additional information is stored in `properties`, and `properties` is used to store the additional information configured for the Kafka schema mapping relationship; The Kafka metadata is loaded according to the mapping relationship.

2. The method according to claim 1, characterized in that, include: The mapping relationship is stored in Redis; wherein the mapping relationship is JSON format data.

3. The method according to any one of claims 1-2, characterized in that, Also includes: Displays the loaded Kafka metadata.

4. A data loading device based on Presto, characterized in that, include: The acquisition module is used to acquire database SQL statements input by the user; wherein, the database SQL statements are used to configure the Kafka schema, and the Kafka schema is used to define the mapping relationship between Presto and Kafka metadata; The loading module is used to load Kafka metadata based on the database SQL statement; specifically, it is used to parse the database SQL statement to determine syntax information, target table information, and / or additional information. The mapping relationship of the Kafka schema is configured according to the syntax information, target table information, and / or the additional information, including: if the syntax information is a `CREATE TABLE` statement, then the mapping relationship of the Kafka schema is assembled by combining the target table information and the additional information; if the syntax information is an `ALTER TABLE` statement, then the mapping relationship of the Kafka schema is updated according to the determined target table information; if the syntax information is a `DROP TABLE` statement, then the mapping relationship of the Kafka schema is deleted according to the determined target table information; wherein, the target table information includes at least one item: table name and column information; wherein, the table name is stored in `table`, and `table` is used to store the table name configured for the Kafka schema mapping relationship, and the column information is stored in `columns`, and `columns` is used to store the column information configured for the Kafka schema mapping relationship; the additional information is stored in `properties`, and `properties` is used to store the additional information configured for the Kafka schema mapping relationship; Load the Kafka metadata according to the mapping relationship; A storage module is used to store the mapping relationship; The display module is used to display the loaded Kafka metadata.

5. An electronic device, characterized in that, include: The data loading device based on Presto as described in claim 4.

Images