Looking for breakthrough ideas for innovation challenges? Try Patsnap Eureka!

Table separation method based on sorting fields and time routing

A separation method and time technology, applied in the fields of structured data retrieval, instruments, finance, etc., can solve the problems of non-configuration, lack of flexibility, waste of resources, etc., and achieve convenient access, simplified manual operation, and labor cost saving. Effect

Active Publication Date: 2020-11-03
SICHUAN XW BANK CO LTD
View PDF7 Cites 2 Cited by
  • Summary
  • Abstract
  • Description
  • Claims
  • Application Information

AI Technical Summary

Problems solved by technology

[0005] 2. Non-configurable and lack of flexibility
It is necessary to develop new codes to realize these functions, which is time-consuming and laborious, resulting in a waste of various resources

Method used

the structure of the environmentally friendly knitted fabric provided by the present invention; figure 2 Flow chart of the yarn wrapping machine for environmentally friendly knitted fabrics and storage devices; image 3 Is the parameter map of the yarn covering machine
View more

Image

Smart Image Click on the blue labels to locate them in the text.
Viewing Examples
Smart Image
  • Table separation method based on sorting fields and time routing
  • Table separation method based on sorting fields and time routing
  • Table separation method based on sorting fields and time routing

Examples

Experimental program
Comparison scheme
Effect test

Embodiment 1

[0052] Hot table separation scenario: There is a js_access_log database table in the device fingerprint (a web application system developed in Java language), which enters hundreds of thousands of data every day, and tens of millions of data a month. A large amount of data will affect the read and write performance of the table. Therefore, it is necessary to separate the data in the js_access_log table for more than 60 days into the cold table on a quarterly basis, and trigger it every night at 1 am. After the js_access_log database table is separated by the above-mentioned table separation method of the present invention, the formed cold table format is: js_access_log_his_2020_1_3 (2020 is the year, corresponding to the dateFieldName parameter, 1_3 means that the data in the jss_access_log table exceeding 60 days from January to March exists js_access_log_his_2020_1_3 table), js_access_log_his_2020_4_5 and other cold tables.

Embodiment 2

[0054] Cold table separation scenario: Also for the js_access_log database table in the device fingerprint, there used to be a hard-coded scheduled task to write the data in the js_access_log database table for more than 60 days into the js_access_log_his cold table. Two years later, the data in the js_access_log_his cold table exceeded There are hundreds of millions of rows, reaching more than 300 G, and the query data is so slow that it is basically unusable. At this time, the cold table needs to be split into multiple small cold tables to prevent the single table from being too large to be used and maintained. After the timing task is configured, through the above-mentioned table separation method of the present invention, this large cold table can be split into several small cold tables, which can be used for query.

Embodiment 3

[0056] Data cleaning scenario: There is a temporary data table oper_ilog_con in the database, which enters hundreds of thousands of data every day, and the database performance is not good. Therefore, in order to achieve the required performance, only seven days of data need to be kept in the oper_ilog_con table. After configuring the scheduled task, perform step:

[0057] S1. From the database table corresponding to the tableName parameter (ie, the oper_ilog_con table), query the id list of the corresponding data according to the keepDays parameter (ie, seven days) configured in the data cleaning scene, and save the id list through the SQL statement in the xml file to a List of TableInfo objects in the idList parameter.

[0058] S2. Through the sql statement in the xml file, delete the corresponding data from the oper_ilog_con table according to the id stored in the idList parameter.

[0059] If there is any abnormality in the above steps, a prompt will be given.

[0...

the structure of the environmentally friendly knitted fabric provided by the present invention; figure 2 Flow chart of the yarn wrapping machine for environmentally friendly knitted fabrics and storage devices; image 3 Is the parameter map of the yarn covering machine
Login to View More

PUM

No PUM Login to View More

Abstract

The invention relates to a table separation method based on sorting fields and time routing, which comprises the following steps of: A, configuring required database parameters according to differenttypes of table separation scenes in a database, and setting a configuration file for operating the database; B, reading column information of the table according to the configured table name; C, if the scene is a heat meter separation scene, querying data configured in the table and before the storage days; if the scene is a cold table separation scene, querying table data; D, storing the querieddata in corresponding parameters; E, dividing granularity according to the configured date and time, and storing the data in the parameters into a specified object; and F, writing the data in the specified object into a separation table one by one, and deleting the successfully written data from the database table corresponding to the configured database table name. According to the invention, multiplexing and unified management can be carried out on the sub-table codes, repeated labor is greatly reduced, and data table separation does not need new development, test and online.

Description

technical field [0001] The invention relates to a data processing method in a database, specifically a table separation method based on sorting fields and time routing. Background technique [0002] In the database of the banking system, there are usually two types of database tables, hot tables and cold tables. A hot table refers to a table with data continuously written into it, and a cold table refers to a table with many rows of data and no new data to be written, such as a mysql table with 200 to 300 million rows of data. When the data in the hot table or cold table is too large, it is very slow to query the data in the table, so the hot table or cold table needs to be split into several small tables according to time, so that the query will be faster. [0003] At present, each system of the bank is based on its own business needs, and develops, tests and launches the tables that need to be separated. There is no common design scheme and common library, which causes a ...

Claims

the structure of the environmentally friendly knitted fabric provided by the present invention; figure 2 Flow chart of the yarn wrapping machine for environmentally friendly knitted fabrics and storage devices; image 3 Is the parameter map of the yarn covering machine
Login to View More

Application Information

Patent Timeline
no application Login to View More
IPC IPC(8): G06F16/81G06F16/22G06Q40/02
CPCG06F16/81G06F16/2282G06Q40/02
Inventor 王崟平
Owner SICHUAN XW BANK CO LTD
Who we serve
  • R&D Engineer
  • R&D Manager
  • IP Professional
Why Patsnap Eureka
  • Industry Leading Data Capabilities
  • Powerful AI technology
  • Patent DNA Extraction
Social media
Patsnap Eureka Blog
Learn More
PatSnap group products

Browse by: Latest US Patents, China's latest patents, Technical Efficacy Thesaurus, Application Domain, Technology Topic, Popular Technical Reports.

© 2024 PatSnap. All rights reserved.Legal|Privacy policy|Modern Slavery Act Transparency Statement|Sitemap|About US| Contact US: help@patsnap.com