A data query method, device, computer equipment and readable storage medium

Abstract

The invention discloses a data query method, device, computer equipment and readable storage medium, including: hive, Presto and Hbase, hive is used to generate a wide table, the Hbase creates a second result table, the wide table data is inserted into the second result table in batches, the wide table is generated and stored in hbase, and rowkey is set in hive, hive integrates hbase, loads fields offline and stores the fields in business key fields and corresponding rowkeys, creates a secondary index to obtain a specified rowkey, and with the help of the rowkey, the field value to be queried can be quickly obtained in hbase. The hbase of the invention can store large wide tables, supports distributed storage, can set backup, has a disaster recovery mechanism, and after the secondary index is created, the rowkey is queried through the secondary index, and then the required data is queried according to the rowkey; the column family and column name can also be specified to query the specified field value, so as to improve the search speed.

Description

Technical Field

[0001] The present invention relates to the field of network query data, and in particular to a data query method, device, computer equipment and readable storage medium. Background Art

[0002] At present, when doing data query, the query is mainly performed on wide tables with large data volumes, and the query tools used are Oracle and MongoDB. However, when using Oracle and MongoDB to query data, the query speed is too slow, resulting in a long query time. If the amount of data to be queried is too large, the query device will be stuck directly and the query will not be able to be completed. Summary of the invention

[0003] In order to overcome the deficiencies of the prior art, the present invention provides a data query method, apparatus, computer equipment and readable storage medium, which can realize rapid query of wide tables.

[0004] The technical solution adopted by the present invention to solve the technical problem is: a data query method, the improvement of which is that it includes the following steps:

[0005] Step 1: Generate a wide table and store it in hbase;

[0006] Step 2: Set rowkey and column family in hbase;

[0007] Step 3: Integration of Hive and HBase;

[0008] Step 4: Load fields offline and store them in business-focused fields and corresponding rowkeys;

[0009] Step 5: Create a secondary index and get the specified rowkey;

[0010] Step 6: With the help of rowkey, you can quickly get the field value you need to query in hbase.

[0011] As a further improvement to the above solution, the specific steps to generate a wide table and store it in HBase are as follows:

[0012] Step 1: Hive joins multiple tables to obtain a wide table;

[0013] Step 2: Create the first result table in Hive and use it to map the second result table in Hbase.

[0014] As a further improvement to the above solution, the specific steps for setting rowkey and column family in HBase are as follows:

[0015] Step 1: Confirm commonly used business fields;

[0016] Step 2: Hive creates a third result table, which has multiple column families, and each column family has a column name;

[0017] Step 3: Set rowkey in hbase;

[0018] Step 4: Find the correspondence between commonly used business fields and rowkeys and store them in the third result table;

[0019] Step 5: Process the newly added relationship records offline every day and synchronize the records to the third result table.

[0020] As a further improvement of the above solution, the column names include the merchant's name, age, gender, mobile phone contact information, email address, salary and address information.

[0021] As a further improvement of the above solution, the newly added relationship records are processed offline every day and synchronized to the third result table. Hbase stores the data of t+1, where t is the specific day.

[0022] As a further improvement to the above solution, create a secondary index and get the specified rowkey. With the help of the rowkey, you can quickly get the field value you want to query in HBase. The steps are as follows:

[0023] Step 1: Build a presto cluster;

[0024] Step 2: Configure Presto and integrate Hive;

[0025] Step 3: Call the presto interface to query the third result table to obtain the required rowkey;

[0026] Step 4: After querying the corresponding rowkey, get the field value to be queried in HBase based on the rowkey.

[0027] A data query device, the improvement of which is that it includes: hive, Presto and Hbase, uses hive to generate a wide table, the Hbase creates a second result table, the wide table data is inserted into the second result table in batches, the wide table is generated and stored in hbase, and a rowkey is set in hive, hive integrates hbase, loads fields offline and stores the fields in business key fields and corresponding rowkeys, creates a secondary index to obtain a specified rowkey, and with the help of the rowkey, the field value to be queried can be quickly obtained in hbase.

[0028] A computer device comprises a memory, a processor and a computer program stored in the memory and executable on the processor, wherein the improvement lies in that the processor implements any one of the above methods when executing the computer program.

[0029] A computer-readable storage medium, wherein the improvement lies in that the computer-readable storage medium stores a computer program for executing any of the above methods.

[0030] The beneficial effects of the present invention are: HBase can store large wide tables, support distributed storage, set up backups, have a disaster recovery mechanism, and after creating a secondary index, query the rowkey through the secondary index, and then query the required data based on the rowkey; the column family and column name can also be specified to query the specified field value, thereby improving the search speed. BRIEF DESCRIPTION OF THE DRAWINGS

[0031] The present invention is further described below in conjunction with the accompanying drawings and embodiments.

[0032] Figure 1 It is a flow chart of the present invention. DETAILED DESCRIPTION

[0033] The following will clearly and completely describe the concept, specific structure and technical effects of the present invention in combination with the embodiments and drawings, so as to fully understand the purpose, characteristics and effects of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of them. Based on the embodiments of the present invention, other embodiments obtained by technicians in this field without creative work are all within the scope of protection of the present invention. In addition, all the connection / connection relationships involved in the patent do not refer to the direct connection of components, but refer to the formation of a better connection structure by adding or reducing connection accessories according to the specific implementation situation. The various technical features in the invention can be combined interchangeably without conflicting with each other.

[0034] Reference Figure 1 The present invention discloses a data query method, comprising the following steps:

[0035] Step 1: Generate a wide table and store it in hbase;

[0036] Step 2: Set rowkey and column family in hbase;

[0037] Step 3: Integration of Hive and HBase;

[0038] Step 4: Load fields offline and store them in business-focused fields and corresponding rowkeys;

[0039] Step 5: Create a secondary index and get the specified rowkey;

[0040] Step 6: With the help of rowkey, you can quickly get the field value you need to query in hbase.

[0041] In the above embodiment, Hbase is a distributed, column-oriented open source database, and Hive is a data warehouse tool based on Hadoop, which can map structured data files into a database table and provide complete SQL query functions. The present invention first generates a wide table and stores the wide table in Hbase, and then sets multiple rowkeys in Hbase, each rowkey is unique to avoid duplication and confusion, integrates Hbase with Hive, loads the fields of the Hive external table into Hive offline, stores the business key fields and the corresponding rowkeys, creates a secondary index to obtain the specified rowkey, and then uses the rowkey to quickly obtain the field value to be queried in Hbase.

[0042] Furthermore, the specific steps to generate a wide table and store it in HBase are as follows:

[0043] Step 1: Hive joins multiple tables to obtain a wide table;

[0044] Step 2: Create the first result table in Hive and use it to map the second result table in Hbase.

[0045] In the above steps, first, Hive multiple tables are associated to obtain a wide table, and the first result table is created internally in Hive, and the first result table is used to map the second result table of Hbase. The data in Hive will be automatically synchronized to Hbase, sharing the same data. Among them, Hive maps external tables, and can be processed offline when it is necessary to associate other tables. Since Hbase supports real-time insertion and query, it can store data in real time. In order to prevent exceeding the maximum memory allocated by the cluster, the data of the wide table is selected to be inserted in batches and stored in the second result table.

[0046] Going further, the specific steps to set rowkey and column family in hbase are as follows:

[0047] Step 1: Confirm commonly used business fields;

[0048] Step 2: Hive creates a third result table. The third result table has multiple column families, which can store data in a distributed manner and back up data to prevent data loss. Each column family has a column name, which includes the merchant's name, age, gender, mobile phone contact information, email address, salary, address and other related data information;

[0049] Step 3: Set rowkey in hbase;

[0050] Step 4: Find the correspondence between commonly used business fields and rowkeys and store them in the third result table;

[0051] Step 5: Offline processing of newly added relationship records every day and synchronization of records to the third result table Offline processing of newly added relationship records every day and synchronization of records to the third result table. Hbase stores data of t+1, where t is the specific number of days.

[0052] In the above steps, the rowkey is set according to the business, which cannot be repeated to form a unique key. For example, multiple field values ​​are concatenated to find the corresponding relationship between the commonly used business fields (a small number of fields) and the rowkey and store them in the third result table newly created by hive. The hbase also supports real-time deletion and update of data. When updating, the rowkey is specified. The column family and column name can specify the update of a certain field. In addition, hbase stores t+1 data, and the data of the day can be queried in MongoDB. The hbase table mainly supports fast and accurate query of historical large batches of data. It is worth noting that the business fields here need to be determined in advance. After determination, hive can create the third result table. Later, business fields can be added or reduced according to the specific business.

[0053] Finally, create a secondary index and get the specified rowkey. With the help of the rowkey, you can quickly get the field value you need to query in HBase. The steps are as follows:

[0054] Step 1: Build a presto cluster;

[0055] Step 2: Configure Presto and integrate Hive;

[0056] Step 3: Call the presto interface to query the third result table to obtain the required rowkey;

[0057] Step 4: After querying the corresponding rowkey, get the field value to be queried in HBase based on the rowkey.

[0058] In the above steps, presto is a data query engine developed by Facebook. The link between presto and hive is established. The presto interface is called to query the third result table to obtain the required rowkey. This is more than 10 times faster than directly querying hive. If the presto interface is used directly to query the mapped hbase table, errors will also occur. After the rowkey is obtained by querying, the field value to be queried can be quickly obtained in hbase by the rowkey.

[0059] A data query device includes: hive, Presto and Hbase, using hive to generate a wide table, the Hbase creates a second result table, the wide table data is inserted into the second result table in batches, the wide table is generated and stored in hbase, and rowkey is set in hive, hive integrates hbase, loads fields offline and stores the fields in business key fields and corresponding rowkeys, creates a secondary index to obtain a specified rowkey, and with the help of the rowkey, the field value to be queried can be quickly obtained in hbase.

[0060] In the above embodiment, Hbase is a distributed, column-oriented open source database, and Hive is a data warehouse tool based on Hadoop, which can map structured data files into a database table and provide complete SQL query functions. The present invention first generates a wide table and stores the wide table in Hbase, and then sets multiple rowkeys in Hbase, each rowkey is unique to avoid duplication and confusion, integrates Hbase with Hive, loads the fields of the Hive external table into Hive offline, stores the business key fields and the corresponding rowkeys, creates a secondary index to obtain the specified rowkey, and then uses the rowkey to quickly obtain the field value to be queried in Hbase.

[0061] A computer device comprises a memory, a processor and a computer program stored in the memory and executable on the processor, wherein the processor implements any one of the above methods when executing the computer program.

[0062] A computer-readable storage medium stores a computer program for executing any of the above methods.

[0063] The beneficial effects of the present invention are: HBase can store large wide tables, support distributed storage, set up backups, have a disaster recovery mechanism, and after creating a secondary index, query the rowkey through the secondary index, and then query the required data based on the rowkey; the column family and column name can also be specified to query the specified field value, thereby improving the search speed.

[0064] The above is a specific description of the preferred implementation of the present invention, but the invention is not limited to the embodiments. Those skilled in the art can make various equivalent modifications or substitutions without violating the spirit of the present invention. These equivalent modifications or substitutions are all included in the scope defined by the claims of this application.

Claims

1. A data query method, characterized in that: The following steps are involved: Step 1: Generate a wide table and store it in hbase; The specific steps of generating a wide table and storing it in HBase are as follows: Step 1: Hive performs multi-table association to obtain a wide table; Step 2: Hive creates a first result table internally and uses the first result table to map the second result table of HBase; Step 2: Set rowkey and column family in hbase; The specific steps for setting rowkey and column family in hbase are as follows: Step 1: Confirm the commonly used business fields; Step 2: Hive creates the third result table, which has multiple column families, and each column family has a column name; Step 3: Set rowkey in hbase; Step 4: Find the corresponding relationship between the commonly used business fields and rowkeys every day and store them in the third result table; Step 5: Offline process the newly added relationship records every day and synchronize the records to the third result table; Said columns include the merchant’s name, age, gender, mobile phone contact information, email address, salary, and address information; Process the newly added relationship records offline every day and synchronize them to the third result table. Hbase stores the data of t+1, where t is the specific day. Step 3: Integration of Hive and HBase; Step 4: Load fields offline and store them in business-focused fields and corresponding rowkeys; Step 5: Create a secondary index and get the specified rowkey; Step 6: With the help of rowkey, you can quickly get the field value you need to query in hbase; Create a secondary index and get the specified rowkey. With the help of the rowkey, you can quickly get the field value you need to query in hbase. The steps are as follows: Step 1: Build a presto cluster; Step 2: Configure presto and integrate hive; Step 3: Call the presto interface to query the third result table to get the required rowkey; Step 4: After querying the corresponding rowkey, get the field value you need to query in hbase based on the rowkey.

2. A data query device, characterized in that: The device is used to implement a data query method as claimed in claim 1, including: hive, Presto and Hbase, using hive to generate a wide table, the Hbase creates a second result table, the wide table data is inserted into the second result table in batches, the wide table is generated and stored in hbase, and rowkey is set in hive, hive integrates hbase, loads fields offline and stores the fields in business key fields and corresponding rowkeys, creates a secondary index to obtain a specified rowkey, and with the help of the rowkey, the field value to be queried can be quickly obtained in hbase.

3. 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 method of claim 1 is implemented.

4. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores a computer program for executing the method of claim 1.

Images