A database log analysis method, device and equipment

Abstract

The application provides a database log analysis method, device and equipment, belongs to the technical field of computer information processing, and solves the problems of resource waste, structure change blockage and low scanning efficiency caused by the traditional change data capture mechanism when monitoring multiple tables. The method comprises the following steps: obtaining a metadata table and log data of a target database; performing query grouping processing on the metadata table to obtain a monitoring table; performing analysis and row record extraction processing on the monitoring table to obtain row record cache data; performing grabbing and updating processing on the log data to obtain log records; performing grouping filtering processing on the log records to obtain business change logs; performing cutting and conversion processing on the business change logs according to the monitoring table and the row record cache data to obtain structured data; and determining log change data according to the structured data. The scheme realizes millisecond-level change capture of multiple tables through single scanning, and significantly reduces resource consumption and data delay.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of computer information processing, in particular to a database log analysis method, device and equipment. BACKGROUND

[0002] In the field of database change capture, the change data capture (CDC) provided by the relational database management system (SQL Server) is the mainstream technical solution. CDC creates independent change tables, captures data changes asynchronously through transaction log agents, and supports tracking of business table insertion, update and deletion operations. However, this solution has significant defects: first, a dedicated CDC table needs to be created for each monitored table, resulting in a doubling of disk space usage, and frequent write operations increase server I / O pressure, so resource consumption is too large; second, when the table structure changes, CDC must be disabled and then re-enabled, which can easily lose change data during the interruption period, and the configuration process requires a high-privilege account, increasing security risks and high operational complexity; finally, when monitoring the entire database, each table needs to be scanned, and in a massive table scenario, the scanning delay can reach minutes, which cannot meet real-time business needs.

[0003] Existing improved solutions (such as triggers and timestamp tracking) cannot balance efficiency and completeness, triggers exacerbate performance loss, and timestamps cannot capture deletion operations. Therefore, there is an urgent need for a lightweight, low-latency, non-intrusive log analysis method that can guarantee data integrity while breaking through resource and efficiency limitations. SUMMARY

[0004] The present application provides a database log analysis method, device and equipment, which solves the problems of resource waste, structure change blocking and low scanning efficiency caused by traditional change data capture mechanisms in multi-table monitoring.

[0005] To solve the above technical problems, the technical solutions of the present application are as follows:

[0006] The present application provides a database log analysis method, device and equipment, which solves the problems of resource waste, structure change blocking and low scanning efficiency caused by traditional change data capture mechanisms in multi-table monitoring.

[0007] Obtain the metadata table and log data of the target database;

[0008] Query and group the metadata table to obtain a monitoring table;

[0009] Analyze and extract the row records of the monitoring table to obtain row record cache data;

[0010] Capture and update the log data to obtain log records;

[0011] Group and filter the log records to obtain business change logs;

[0012] According to the monitoring table and the row record cache data, the service change log is cut and converted to obtain structured data;

[0013] According to the structured data, log change data is determined.

[0014] Optionally, the metadata table is queried and grouped to obtain a monitoring table, comprising:

[0015] The metadata table is queried to obtain an original table structure data set;

[0016] The original table structure data set is identified and extracted to obtain a mapping table;

[0017] According to the mapping table, the original table structure data set is grouped and sorted to obtain a monitoring table.

[0018] Optionally, the monitoring table is parsed and row record extraction is performed to obtain row record cache data, comprising:

[0019] The monitoring table is extracted according to a first preset field to obtain a plurality of data page triplets;

[0020] The plurality of data page triplets are parsed to obtain parsed data of a plurality of data pages;

[0021] The parsed data of the plurality of data pages is integrated to obtain row record cache data.

[0022] Optionally, the log data is captured and updated to obtain log records, comprising:

[0023] The log data is extracted according to a second preset field to obtain an original log block data set, the original log block data set comprising a plurality of row records;

[0024] Cursor data is obtained;

[0025] According to the cursor data, the original log block data set is captured to obtain log records.

[0026] Optionally, the log records are grouped and filtered to obtain service change logs, comprising:

[0027] The log records are grouped according to a third preset field to obtain grouped log data;

[0028] The grouped log data is filtered according to a preset condition to obtain filtered log data;

[0029] Extracting the filtered log data according to a fourth preset field to obtain a service change log.

[0030] Optionally, according to the monitoring table and the row record cache data, the service change log is subjected to cutting conversion processing to obtain structured data, including:

[0031] According to the service change log, the monitoring table is subjected to retrieval processing to obtain column metadata.

[0032] According to the service change log, conversion processing is performed to obtain binary stream data.

[0033] According to the column metadata, the cut data block is subjected to parsing processing to obtain structured data.

[0034]

[0035] Optionally, according to the structured data, log change data is determined, including:

[0036] The structured data is subjected to encapsulation processing according to a preset format to obtain log change data.

[0037] The embodiment of the application further provides a database log analysis device, including:

[0038] An acquisition module is configured to acquire a metadata table and log data of a target database.

[0039] A processing module is configured to perform query grouping processing on the metadata table to obtain a monitoring table, perform analysis and row record extraction processing on the monitoring table to obtain row record cache data, perform log record extraction and update processing on the log data to obtain log records, perform grouping and filtering processing on the log records to obtain a service change log, and perform cutting conversion processing on the service change log according to the monitoring table and the row record cache data to obtain structured data.

[0040] A determination module is configured to determine log change data according to the structured data.

[0041] The embodiment of the application further provides a computing device, including a processor and a memory storing a computer program, wherein the computer program is executed by the processor to perform the above method.

[0042] The embodiment of the application further provides a computer readable storage medium storing instructions, wherein when the instructions are executed on a computer, the computer performs the above method.

[0043] The technical scheme of the application at least has the following effects: ​

[0044] The above scheme of the present application obtains the metadata table and log data of a target database, performs query grouping processing on the metadata table to obtain a monitoring table, performs parsing and row record extraction processing on the monitoring table to obtain row record cache data, performs grabbing and updating processing on the log data to obtain log records, performs grouping filtering processing on the log records to obtain business change logs, performs cutting and conversion processing on the business change logs according to the monitoring table and the row record cache data to obtain structured data, and determines log change data according to the structured data, so that multi-table millisecond-level change capture can be realized through single scanning, and resource consumption and data delay are reduced. BRIEF DESCRIPTION OF DRAWINGS

[0045] Figure 1 is a flowchart of the database log analysis method provided by the embodiment of the present application;

[0046] Figure 2 is a data processing process schematic diagram of the database log analysis method provided by the embodiment of the present application;

[0047] Figure 3 is a structural diagram of the database log analysis device provided by the embodiment of the present application;

[0048] Figure 4 is a structural schematic diagram of the computing device provided by the embodiment of the present application. DETAILED DESCRIPTION

[0049] Exemplary embodiments of the present application will be described in greater detail below with reference to the accompanying drawings. Although exemplary embodiments of the present application are shown in the drawings, it is understood that the present application can be implemented in various forms and should not be limited by the embodiments set forth herein. Rather, these embodiments are provided so that the present application can be more thoroughly understood and so that the scope of the present application can be completely conveyed to those skilled in the art.

[0050] As shown in Figure 1 the embodiment of the present application proposes a database log analysis method, which comprises:

[0051] Step 11, obtaining the metadata table and log data of a target database;

[0052] Step 12, performing query grouping processing on the metadata table to obtain a monitoring table;

[0053] Step 13, performing parsing and row record extraction processing on the monitoring table to obtain row record cache data;

[0054] Step 14, performing grabbing and updating processing on the log data to obtain log records;

[0055] Step 15, perform grouping filtering on the log records to obtain service change logs;

[0056] Step 16, perform cutting conversion processing on the service change logs according to the monitoring table and the row record cache data to obtain structured data;

[0057] Step 17, determine log change data according to the structured data.

[0058] In step 11 of this embodiment, the metadata information of the database is loaded from the system table of the target database by executing a specific SQL query. These metadata include table name, column name, column ID, data type, whether null, and other field information. The query obtains the required metadata from the system tables such as sys.tables, sys.schemas, and sys.columns. The sys.fn_dblog function provided by SQL Server is used to read log data from the active log. This function allows retrieval of log records based on LSN (Log Sequence Number) range, thereby obtaining all or part of the log data of the target database.

[0059] In step 12, the metadata table is grouped according to information such as SchemaName, AllocUnitId, TableName, and other fields in the metadata table. The purpose of grouping is to organize metadata information belonging to the same table together, facilitating subsequent monitoring and processing. After grouping, each table corresponds to a group of metadata information, which constitutes the basis for monitoring the table, i.e., the monitoring table.

[0060] In step 13, the column structure and data type of each table are parsed based on the metadata information in the monitoring table. Using the parsed table structure information, the data page query command (such as DBCC PAGE) is executed to extract row record data from the data page. During the extraction process, the original byte data is converted into meaningful field values based on the column offset (LeafOffset) and data type (SystemTypeId) information in the metadata. The extracted row record data is cached for subsequent steps.

[0061] In step 14, the sys.fn_dblog function is called in a loop, and the last LSN obtained in the previous step is passed as a parameter to achieve continuous capture of log data. This ensures that all newly added log records are obtained. During the capture process, de-duplication, merging, and other updating operations are performed on the obtained log records to ensure the accuracy and integrity of the log records. After capture and updating, the obtained log records contain all change operations of the target database within a certain period of time.

[0062] In step 15, the log records are grouped and filtered according to information such as Transaction ID and Operation in the log record. The business log usually starts with LOP_BEGIN_XACT and ends with LOP_ABORT_XACT or LOP_COMMIT_XACT, and all operations of the same transaction have the same transaction ID. By filtering out system logs and non-business operation logs (such as LOP_LOCK_XACT), a pure business change log can be obtained. After grouping and filtering, the business change log obtained only contains log records of operations such as insertion, update, and deletion on business tables.

[0063] In step 16, the business change log is cut and converted using the metadata information in the monitoring table and the row record cache data. Specifically, the corresponding monitoring table information is found according to the AllocUnitId in the log record, and then the original byte data in the log record is cut into specific field values according to the table structure information. For different types of operations (such as insertion, update, and deletion), different parsing logic is required to process the RowLog Contents field in the log record. After cutting and conversion, the business change log is converted into a structured data format, which is convenient for subsequent analysis and processing.

[0064] In step 17, based on the structured data, specific change information such as which tables and columns have changed, and what the values were before and after the change can be further extracted. These information constitutes the core content of the log change data. The determined log change data can be pushed to search engines such as Elasticsearch for indexing and storage, or loaded into real-time data warehouses for further analysis and processing.

[0065] The present application proposes the above technical solution, which directly reads the SQL Server transaction log, combines physical page data pre-caching and dynamic configuration loading technology, and achieves the following technical effects:

[0066] (1) Resource efficiency: Abandoning the traditional CDC multi-table independent storage mechanism, a single scan can capture all library multi-table changes, saving server resources and reducing disk space occupation;

[0067] (2) Lightweight operation and maintenance: Only basic account permissions are required, and the monitoring does not need to be disabled when the table structure changes, completely avoiding the risk of data omission due to configuration interruption;

[0068] (3) Millisecond delay: The data change parsing delay is compressed from minutes to milliseconds, improving the real-time response of business.

[0069] In an optional embodiment of the present application, step 12 can include:

[0070] Step 121, querying the metadata table to obtain an original table structure dataset;

[0071] Step 122, performing identification extraction processing on the original table structure dataset to obtain a mapping table;

[0072] Step 123, grouping and sorting the original table structure dataset according to the mapping table to obtain a monitoring table.

[0073] In step 121 of this embodiment, a SQL query is first executed to retrieve all relevant metadata information from the system tables of the target database. These system tables usually include sys.tables (which stores table information), sys.schemas (which stores schema / namespaces information), sys.columns (which stores column information), etc. By joining these tables, an original table structure dataset containing table name, column name, column ID, data type, whether nullable, and other key fields can be constructed. The specific SQL query constructs the original table structure dataset by joining multiple system tables and selecting specific fields. After executing the above SQL query, a result set containing all relevant table structure information, i.e., the original table structure dataset, is returned.

[0074] In step 122, after obtaining the original table structure dataset, the key information used to identify and distinguish different tables and columns is extracted. This information usually includes table name (TableName), allocation unit ID (AllocUnitId, which is the unique identifier of the table in storage), column ID (ColumnID), etc. Using the extracted identification information, a mapping table can be constructed, which takes table name and allocation unit ID as keys and column ID and other related column attributes (such as data type, whether nullable, etc.) as values. In this way, the information of each table and its columns is organized in a structured mapping table, facilitating subsequent grouping and sorting operations. After identification extraction processing, the obtained mapping table will serve as the basis for subsequent grouping and sorting operations.

[0075] In step 123, the original table structure dataset is grouped by using the table name and the allocation unit ID in the mapping table as the grouping basis. In this way, all column information belonging to the same table is organized in a group. In each group, the column information can be sorted according to the column ID or other related fields (such as the column name) to ensure the order of the column information. The sorting operation helps subsequent fast access and processing of the table structure. After the grouping and sorting operations, the original table structure dataset is converted into a structured monitoring table. The table contains all column information grouped by table, and the column information in each group is ordered. This monitoring table will serve as the basis for subsequent log analysis and processing, and is used to guide the monitoring and log analysis operations of a specific table.

[0076] In an optional embodiment of the present application, step 13 can include:

[0077] In step 131, the monitoring table is extracted according to a first preset field to obtain a plurality of data page triplets.

[0078] In step 132, the plurality of data page triplets are parsed to obtain parsed data of a plurality of data pages.

[0079] In step 133, the parsed data of the plurality of data pages is integrated to obtain row record cache data.

[0080] In step 131 of this embodiment, the first step is to determine the preset fields used to extract data page information. These fields include database ID (databaseId), file ID (fileId), and page ID (pageId), which together constitute the unique identifier of the data page in the database. Using the table name information in the monitoring table, combined with system stored procedures or dynamic management views (such as sys.dm_db_database_page_allocations), a SQL query is written to extract the data page information corresponding to each table. The query will return a result set containing databaseId, fileId, pageId, and other related page attributes (such as page type, allocation unit ID, etc.). From the query results, the unique identifier of each data page is extracted according to the three fields of databaseId, fileId, and pageId to form data page triplets. These triplets will serve as the basis for subsequent parsing processing.

[0081] In step 132, for each data page triple, the corresponding parsing logic needs to be prepared. This involves using the DBCC PAGE command (or similar underlying command) to read the physical content of the data page. The DBCC PAGE command can return detailed content of the specified data page, including page header, row data, and slot array, etc. The DBCC PAGE command is executed programmatically (such as using PowerShell scripts, C# programs, or SQL CLR integration) and the returned results are parsed. During parsing, row record data needs to be extracted according to the page type and data structure. For data pages containing row data, the specific content of each row needs to be further parsed, including the values of each column and the row header information. After parsing, each data page triple is converted into parsed data containing row record data. These parsed data exist in a structured format (such as JSON, XML, or custom class instances) for subsequent integration and processing.

[0082] In step 133, after obtaining the parsed data of multiple data pages, an integration logic needs to be designed to combine these data into a unified row record cache dataset. This usually involves deduplicating and merging duplicate row records according to their unique identifiers (such as row ID or primary key value). Traverse all parsed data of data pages, for each row record, check if it already exists in the cache dataset. If it exists, decide whether to overwrite, merge, or ignore the newly parsed row record according to business requirements; if it does not exist, add the new row record to the cache dataset. During integration, column value conversion and formatting issues need to be handled to ensure data consistency in the cache dataset. After integration, all parsed data of data pages are combined into a unified row record cache dataset. This dataset can be used as the basis for subsequent log parsing, data synchronization, or data analysis tasks. At the same time, to facilitate subsequent use, this cache dataset can be stored in an in-memory database (such as Redis), distributed file system, or relational database.

[0083] In an optional embodiment of the present application, step 14 can include:

[0084] In step 141, the log data is extracted according to a second preset field to obtain an original log block dataset, and the original log block dataset includes multiple row records.

[0085] In step 142, cursor data is obtained.

[0086] In step 143, the original log block dataset is grabbed according to the cursor data to obtain log records.

[0087] In step 141 of the embodiment, for the extraction of log data, fields that can uniquely identify or categorize log records need to be selected as the basis for extraction. The second preset fields specifically include log sequence number (LSN, Log Sequence Number), transaction ID (Transaction ID), operation type (Operation), etc. These fields help to filter out log records related to specific transactions or operations from a large amount of log data. Using the system functions provided by SQL Server (such as sys.fn_dblog) or log reading tools, queries are written according to the second preset fields to extract log data. The query will return a result set containing the selected fields and other related log information (such as timestamp, table name, column name, value before and after change, etc.), which constitutes the original log block data set. The original log block data set contains all relevant log records extracted from the log file, and each record contains detailed information required when performing specific operations (such as insertion, update, deletion).

[0088] In step 142, the cursor data refers to an identifier used to track the progress of log processing in this context, which is usually a value related to the log sequence number (LSN). The cursor is used to mark the position of the log records that have been processed, so that it can continue from the last interruption in subsequent processing. Before starting log processing, the cursor value at the end of the last processing needs to be read from, such as a database table, a configuration file or a memory variable. If it is the first processing, the cursor value may be initialized to the starting position of the log file or a specific initial value.

[0089] In step 143, according to the obtained cursor data, the unprocessed log records starting from the cursor position are filtered out from the original log block data set. This involves comparing the LSN in the log record with the cursor value, and only selecting records with LSN greater than the cursor value for processing. After the grabbing process, a batch of log records to be processed is obtained. These records contain change information that needs to be parsed and applied to the target database or data warehouse. After processing a batch of log records, the cursor data needs to be updated according to the LSN of the last processed record. In this way, when processing next time, it can continue to process the remaining log records from the new cursor position, ensuring that no records are repeated or missed. Updating the cursor data usually involves writing the new LSN value to persistent storage, so that it can continue processing after system restart or fault recovery.

[0090] In an optional embodiment of the present application, step 15 can include:

[0091] Step 151, grouping the log records according to the third preset field to obtain grouped log data;

[0092] Step 152, filtering the grouped log data according to preset conditions to obtain filtered log data;

[0093] Step 153, extracting the filtered log data according to a fourth preset field to obtain service change logs.

[0094] In step 151 of this embodiment, in the grouping processing stage, a field that can reflect the association or common characteristics between log records needs to be selected as the grouping basis. The third preset field is selected as the transaction ID (Transaction ID). The transaction ID is a unique identifier that identifies a database transaction, and all operations within the same transaction share the same transaction ID. The transaction ID is used as the grouping key to group the log records. In this way, all log records belonging to the same transaction are classified into the same group. Grouping processing can be achieved by writing SQL queries or using grouping functions in programming languages. After grouping processing, the original log records are organized into multiple groups, each group containing all related log records within a transaction. These grouped log data provides a basis for subsequent filtering processing.

[0095] In step 152, the purpose of filtering processing is to filter out log records directly related to service changes from the grouped log data. The preset conditions usually include two aspects: one is to distinguish between business logs and system logs, and the other is to identify specific operation types. Business logs are usually related to user transactions, starting with LOP_BEGIN_XACT and ending with LOP_ABORT_XACT or LOP_COMMIT_XACT, and all operations within the same transaction share the same transaction ID. System logs record internal maintenance operations or system-level events of the database, which are not directly related to business operations. According to the requirements, specific operation types such as insertion (LOP_INSERT_ROWS), update (LOP_MODIFY_ROW, LOP_MODIFY_COLUMNS) and deletion (LOP_DELETE_ROWS) can be further filtered out.

[0096] According to the preset conditions, filtering logic is written to filter the grouped log data. This can be achieved through the WHERE clause in SQL queries or conditional judgment in programming languages. The filtered log data is obtained: after filtering processing, only the grouped data containing log records related to service changes is obtained. These data provide a simplified input for subsequent extraction of service change logs.

[0097] In step 153, in the extraction business change log stage, fields that can directly reflect the change of business data need to be selected as the extraction basis. According to the log parsing instructions in the technical document, these fields usually include RowLog Contents 0 to RowLog Contents variable length fields, which store specific log information of the operation content change. For each filtered log record, the corresponding RowLog Contents field value is extracted according to its operation type (insert, update, delete). For insert and delete operations, the contents of RowLog Contents 0 are usually directly extracted for parsing; for update operations, the relevant fields in RowLog Contents 0 to RowLog Contents need to be extracted according to the operation type (single or multiple modifications) for parsing. After extraction processing, structured business change log data is obtained. These data contain detailed information of business data changes, such as pre-change and post-change values, change time, change operation type, etc. These business change logs can be further pushed to search engines such as Elasticsearch for indexing and storage, or loaded into real-time data warehouses for further analysis and processing.

[0098] In an optional embodiment of the present application, step 16 can include:

[0099] Step 161, retrieving and processing the monitoring table according to the business change log to obtain column metadata;

[0100] Step 162, converting and processing the business change log to obtain binary stream data;

[0101] Step 163, cutting the binary stream data in a predetermined order to obtain cut data blocks;

[0102] Step 164, parsing and processing the cut data blocks according to the column metadata to obtain structured data.

[0103] In step 161 of this embodiment, the business change log records the change operation of the table, but lacks detailed column-level metadata information (such as column name, data type, length, etc.). By retrieving the monitoring table, these key column metadata can be obtained, providing a basis for subsequent parsing. According to the table name involved in the business change log (which can be obtained from the AllocUnitId or table name information of the log), the corresponding table structure information is searched in the monitoring table. The monitoring table should contain metadata such as Schema name, table name, column ID, column name, data type, length, etc. The retrieval result is a set of column metadata, including the column name, data type, length, whether it can be null, etc. These metadata will be used to parse the binary stream data in the subsequent steps.

[0104] In step 162, the RowLog Contents field in the business change log stores the binary representation of the changed data. In order to parse these data, it is necessary to convert them into a unified binary stream data format. According to the operation type (insert, update, delete) of the business change log, the value of the RowLog Contents field is extracted from the log. For insert and delete operations, RowLog Contents 0 is usually directly extracted; for update operations, the relevant fields in RowLog Contents 0 to RowLog Contents 4 may need to be extracted according to the update type (modify single column or multiple columns). The extracted binary data is merged into a continuous binary stream. The converted binary stream data contains the original binary representation of the business change, providing input for subsequent cutting and parsing.

[0105] In step 163, the binary stream data may contain change information of multiple columns, which needs to be cut into independent data blocks according to the order and length of the columns for subsequent parsing. According to the column metadata (column order, data type, length, etc.) obtained in step 161, the starting position and ending position of each column in the binary stream data are located. According to the order of the columns, the binary stream data is cut into multiple data blocks, each data block corresponding to the change data of a column. The cut data blocks are in binary format, and each data block contains the change information of a single column, whose length and format are consistent with the definition in the column metadata.

[0106] In step 164, the cut data blocks are still in binary format and need to be parsed into readable structured data (such as strings, numbers, dates, etc.) according to the column metadata. Each cut data block is traversed, and according to the data type and length information in the column metadata, the corresponding parsing method (such as parsing integer, floating point number, string, date, etc.) is used to convert the binary data into structured data. For variable length data types (such as varchar, nvarchar), the actual length of the data also needs to be determined according to the length information or terminator in the metadata. The parsed structured data is represented in the form of key-value pairs, each key corresponds to a column name, and each value corresponds to the change data of the column. These structured data can be further pushed into search engines such as Elasticsearch for indexing and storage, or loaded into real-time data warehouses for further analysis and processing.

[0107] In an optional embodiment of the present application, step 17 can include:

[0108] In step 171, the structured data is encapsulated according to a preset format to obtain log change data.

[0109] In this embodiment, the parsed structured data (such as column name, pre / post change value, operation type, etc.) is converted into a standardized log change data format for subsequent storage, indexing, and analysis. The unified data format ensures consistent representation of change data for different tables and operations, improving data readability and processing efficiency.

[0110] (1) Preset format design

[0111] According to the log parsing logic in the technical document, the preset format needs to include the following core fields:

[0112] Metadata fields:

[0113] SchemaName: Schema name of the table.

[0114] TableName: Table name of the change.

[0115] Operation: Operation type (LOP_INSERT_ROWS, LOP_DELETE_ROWS, LOP_MODIFY_ROW, LOP_MODIFY_COLUMNS).

[0116] TransactionID: Transaction ID, used to associate operations within the same transaction.

[0117] Timestamp: Timestamp of the change (derived from the Current LSN of the log or system time).

[0118] Change data fields:

[0119] ColumnChanges: Array form, containing the change details of each column:

[0120] ColumnName: Column name.

[0121] OldValue: Pre-change value (may be empty for delete operations).

[0122] NewValue: Post-change value (may be empty for insert operations).

[0123] DataType: Column data type (such as int, varchar).

[0124] Context information:

[0125] AllocUnitId: Unique identifier of the table in the transaction log.

[0126] PageID: Data page ID, locating the physical location of the change.

[0127] LockInformation: Lock information (used to troubleshoot concurrency issues).

[0128] (2) Packaging Process Flow

[0129] Traverse structured data: For each structured record parsed (e.g., {'USER_BASE_ID':'230899501000013', 'B2B_BILL_ID':None}), group changes by column name.

[0130] Fill metadata fields: Extract SchemaName, TableName, Operation, etc. from monitoring tables or log context.

[0131] Generate timestamp (e.g., current system time or time information from logs).

[0132] Build ColumnChanges array: For each column change, create an object {ColumnName:'USER_BASE_ID', OldValue:null, NewValue:'230899501000013', DataType:'bigint'}.

[0133] Update operation needs to merge LOP_MODIFY_ROW or LOP_MODIFY_COLUMNS parsing results (e.g., replace historical values).

[0134] Add context information: Extract AllocUnitId, PageID, LockInformation, etc. from log records.

[0135] Serialize to JSON / Elasticsearch format: Convert packaged data to JSON string or directly construct Elasticsearch document.

[0136] Example output:

[0137] json

[0138] {

[0139] "SchemaName":"dbo",

[0140] "TableName":"Customer",

[0141] "Operation":"LOP_INSERT_ROWS",

[0142] "TransactionID":"0x0000001A0000004D",

[0143] "Timestamp":"2025-04-26T10:45:00Z",

[0144] "ColumnChanges":[

[0145] {

[0146] "ColumnName":"USER_BASE_ID",

[0147] "OldValue":null,

[0148] "NewValue":"230899501000013",

[0149] "DataType":"bigint"

[0150] },

[0151] {

[0152] "ColumnName":"CUST_NAME",

[0153] "OldValue":null,

[0154] "NewValue":"*** Clinic",

[0155] "DataType":"nvarchar"

[0156] }

[0157] ],

[0158] "AllocUnitId":"0x0000001A0000004D",

[0159] "PageID":"0x1234",

[0160] "LockInformation":"(ebc880b24455)"

[0161] }

[0162] (3) Output and Application

[0163] Stored in Elasticsearch: The encapsulated log change data can be directly pushed to Elasticsearch, supporting fast retrieval and analysis.

[0164] Load into real-time data warehouse: converted into wide table or fact table format for real-time monitoring or offline analysis.

[0165] Cache reuse: cache the parsed column metadata or binary stream data to an in-memory database (such as Redis) to improve subsequent processing efficiency.

[0166] As shown in Figure 2 A specific embodiment of the database log parsing method provided by the embodiment of the present application is as follows:

[0167] Step 1, load data table configuration information, load specific table monitoring configuration information

[0168] 1) Execute the whole library table structure loading sql, group according to SchemaName, AllocUnitId, TableName, ColumnID, respectively use AllocUnitId, TableName as key value, combine configuration information, value value storage is each column in the table configuration information, mainly including ColumnID, SystemTypeId, LeafOffset, Scale and the like;

[0169] 2) Process the table information that needs to be monitored, extract the AllocUnitId corresponding to the table name according to the configured table name;

[0170] Step 2, data preheating

[0171] 1) Extract table data page information;

[0172] 2) Read table record information:

[0173] Extract parameters fileId=row['file_id'], pageId=row['page_id'], databaseId= row['database_id']

[0174] Execute data page query command

[0175] DBCCPAGE ('{databaseId}', {fileId}, {pageId}, 3) with table results, no_infomsgs;

[0176] The running result and the information that needs to be processed are as follows:

[0177] Data files need to be extracted and merged;

[0178] The positioning information of each record is the same as the content in the Lock Information;

[0179] Step 3, get real-time log information

[0180] Use sys.fn_dblog('0x{currentLSN}', NULL) to loop to get log information, where currentLSN is the last currentLSN after each log acquisition, and the latest cursor is saved to the cache;

[0181] Current LSN is a globally unique cursor ID, Transaction ID is a transaction ID, AllocUnitId is the ID of the table used in the transaction log, Lock Information is the locking information of the table record with primary key, RowLog Contents0~4 is the storage of this operation content change specific log information, different operations have different meanings; Operation is the transaction operation category; Page ID is the data page ID where the data exists;

[0182] Step 4, log filtering and classification

[0183] 1) Extract business table log change information according to transaction ID, separate business log and sqlserver system log, and only process business log;

[0184] 2) How to distinguish business log and system log, extract Operation and Transaction ID information, business log generally starts with LOP_BEGIN_XACT and ends with LOP_ABORT_XACT, and the Transaction ID value of the same transaction is the same; Others are system logs and are not processed;

[0185] 3) Only process logs with Operation values of LOP_MODIFY_ROW, LOP_MODIFY_COLUMNS, LOP_INSERT_ROWS, and LOP_DELETE_ROWS;

[0186] Step 5, parse log

[0187] 1) Basic parsing

[0188] According to the table configuration file, extract column configuration information ColumnID, LeafOffset, SystemTypeId, arrange in descending order according to ColumnID, use LeafOffset as data segmentation index, and separate fixed-length data and variable-length data to specific column values, and then parse into specific type data according to SystemTypeId; Here, fixed-length data refers to date, numerical value, etc. Type data, variable-length data refers to varchar, nvarcher, etc. Type data.

[0189] 2) Log parsing instructions

[0190] LOP_INSERT_ROWS, LOP_DELETE_ROWS type log, directly extract the content of RowLog Contents 0 for parsing, and the parsing result is the data change log this time;

[0191] LOP_MODIFY_ROW type log, for modifying a place type log

[0192] Need to use Offset in Row, RowLog Contents 0~1 and Lock Information content parsing;

[0193] For example: first use the lock information "(ebc880b24455)" to extract the historical cache information, then use "7bf4" to replace "6ae6" in the historical information, the coordinates are "44", and the replacement result is obtained after the basic analysis. Complete the changed log;

[0194] LOP_MODIFY_COLUMNS type log, for modifying multiple places type log, need to use RowLog Contents 0, 1, 4 and Lock Information content parsing;

[0195] For example: first use the lock information "(e9069d930a93)" to extract the historical cache information, then use the content length of RowLog Contents 0 divided by 8 to get the number of modified columns, then respectively through RowLog Contents 1 Get the index position information, and then get the replacement information from RowLog Contents 4 for replacement, and finally the replacement result is obtained after the basic analysis. Complete the changed log;

[0196] Step 6, push and cache the log parsing result

[0197] The log parsing result is assembled into an Elasticsearch message format and pushed to es and real-time data warehouse;

[0198] The parsed "3000ec005853503" type file is cached as column data in Elasticsearch for next time parsing.

[0199] The database log parsing method provided by the application directly reads the SQLServer transaction log, combines physical page data pre-caching and dynamic configuration loading technology, and achieves the following technical effects:

[0200] (1) Resource efficiency: abandon the traditional CDC multi-table independent storage mechanism, a single scan can capture the whole database multi-table changes, save server resources, reduce 5 disk space occupation;

[0201] (2) Operation and maintenance lightness: only basic account permissions are required, and the monitoring does not need to be disabled when the table structure is changed, thereby completely avoiding the risk of data omission caused by configuration interruption;

[0202] (3) Delay millisecond level: the data change analysis delay is compressed from minutes to milliseconds, improving the real-time response of the business.

[0203] As shown in Figure 3 The embodiment of the application also provides a database log analysis device 30, which comprises:

[0204] An acquisition module 31 is configured to acquire a metadata table and log data of a target database;

[0205] A processing module 32 is configured to perform query grouping processing on the metadata table to obtain a monitoring table, perform analysis and row record extraction processing on the monitoring table to obtain row record cache data, perform grabbing and updating processing on the log data to obtain log records, perform grouping filtering processing on the log records to obtain business change logs, and perform cutting and conversion processing on the business change logs according to the monitoring table and the row record cache data to obtain structured data.

[0206] A determination module 33 is configured to determine log change data according to the structured data.

[0207] Optionally, the processing module 32 is specifically configured to:

[0208] perform query processing on the metadata table to obtain an original table structure data set;

[0209] perform identification extraction processing on the original table structure data set to obtain a mapping table;

[0210] perform grouping and sorting on the original table structure data set according to the mapping table to obtain a monitoring table.

[0211] Optionally, the processing module 32 is further specifically configured to:

[0212] perform extraction processing on the monitoring table according to a first preset field to obtain a plurality of data page triplets;

[0213] perform analysis processing on the plurality of data page triplets to obtain analysis data of the plurality of data pages;

[0214] perform integration processing on the analysis data of the plurality of data pages to obtain row record cache data.

[0215] Optionally, the processing module 32 is further configured to:

[0216] extracting the log data according to a second preset field to obtain a raw log block data set, the raw log block data set including a plurality of row records;

[0217] acquire cursor data;

[0218] According to the cursor data, the raw log block data set is processed to obtain log records.

[0219] Optionally, the processing module 32 is further configured to:

[0220] grouping the log records according to a third preset field to obtain grouped log data;

[0221] filtering the grouped log data according to a preset condition to obtain filtered log data;

[0222] extracting the filtered log data according to a fourth preset field to obtain a business change log.

[0223] Optionally, the processing module 32 is further configured to:

[0224] According to the business change log, the monitoring table is searched to obtain column metadata;

[0225] According to the business change log, the monitoring table is searched to obtain column metadata;

[0226] cutting the binary stream data according to a preset order to obtain cut data blocks;

[0227] According to the column metadata, the cut data blocks are parsed to obtain structured data.

[0228] Optionally, the determining module 33 is configured to:

[0229] According to the column metadata, the cut data blocks are parsed to obtain structured data.

[0230] It should be noted that the device corresponds to the above method, and all implementation manners in the above method embodiment are applicable to this embodiment, and the same technical effects can be achieved.

[0231] As Figure 4As shown, the embodiment of the present application further provides a computing device 40, which comprises a processor 41, a memory 42, a program or instruction stored in the memory 42 and executable on the processor 41, the program or instruction is executed by the processor 41 to realize the processes of the above-mentioned database log analysis method embodiment and achieve the same technical effects. To avoid repetition, it will not be described here. It should be noted that the computing device in the embodiment of the present application includes the above-mentioned mobile electronic device and non-mobile electronic device.

[0232] Those skilled in the art can understand that the units and algorithm steps of the examples described in combination with the embodiments disclosed herein can be realized in electronic hardware or a combination of computer software and electronic hardware. Whether the functions are realized in hardware or software depends on the specific application and design constraints of the technical solution. Those skilled in the art can use different methods to implement the described functions for each specific application, but such implementation should not be considered beyond the scope of the present application.

[0233] Those skilled in the art can clearly understand that, for the convenience and brevity of description, the specific working processes of the above-mentioned system, device and unit can refer to the corresponding processes in the foregoing method embodiments, which will not be described here.

[0234] In the embodiments provided by the present application, it should be understood that the disclosed device and method can be implemented by other ways. For example, the device embodiments described above are only schematic, and the division of units is only a logical function division, and there can be another division way in actual implementation, for example, a plurality of units or components can be combined or integrated into another system, or some features can be ignored or not executed. In addition, the coupling or direct coupling or communication connection between the units shown or discussed can be indirect coupling or communication connection through some interface, device or unit, and can be electrical, mechanical or other forms.

[0235] The units described as separate components may or may not be physically separate, and the components shown as units may or may not be physical units, that is, they can be located in one place, or can be distributed on a plurality of network units. Part or all of the units can be selected according to actual needs to achieve the purpose of the embodiment of the present application.

[0236] In addition, each functional unit in each embodiment of the present application can be integrated into a processing unit, or each unit can exist physically, or two or more units can be integrated into one unit.

[0237] If the functions are implemented in the form of software function units and sold or used as independent products, they can be stored in a computer readable storage medium. Based on this understanding, the technical solutions of the present application essentially or the parts that contribute to the prior art or parts of the technical solutions can be embodied in the form of a software product. The computer software product is stored in a storage medium and includes a plurality of instructions for causing a computer device (which can be a personal computer, a server, or a network device, etc.) to execute all or part of the steps of the embodiments of the present application. The aforementioned storage medium includes: a U disk, a mobile hard disk, a ROM, a RAM, a magnetic disk or an optical disk, and various program code storage media.

[0238] In addition, it should be noted that in the device and method of the present application, it is obvious that the components or steps can be decomposed and / or recombined. These decompositions and / or recombination should be considered as equivalent solutions of the present application. Moreover, the steps of performing the above series of processes can naturally be executed in time sequence according to the order of description, but do not necessarily have to be executed in time sequence. Some steps can be executed in parallel or independently of each other. It can be understood by those skilled in the art that all or any steps or components of the method and device of the present application can be implemented in hardware, firmware, software or a combination thereof in any computing device (including processors, storage media, etc.) or network of computing devices, which can be implemented by those skilled in the art using their basic programming skills after reading the description of the present application.

[0239] Therefore, the object of the present application can also be achieved by running a program or a set of programs on any computing device. The computing device can be a commonly known general-purpose device. Therefore, the object of the present application can also be achieved by providing a program product containing program code for implementing the method or device. That is, such a program product also constitutes the present application, and a storage medium storing such a program product also constitutes the present application. Obviously, the storage medium can be any commonly known storage medium or any storage medium developed in the future. It should be noted that in the device and method of the present application, it is obvious that the components or steps can be decomposed and / or recombined. These decompositions and / or recombination should be considered as equivalent solutions of the present application. Moreover, the steps of performing the above series of processes can naturally be executed in time sequence according to the order of description, but do not necessarily have to be executed in time sequence. Some steps can be executed in parallel or independently of each other.

[0240] The above is the preferred embodiment of the present application. It should be noted that for those skilled in the art, without departing from the principles of the present application, a number of improvements and refinements can be made, which should also be considered within the scope of protection of the present application.

Claims

1. A database log parsing method, characterized by, The method comprises the following steps: obtaining a metadata table and log data of a target database; performing query grouping processing on the metadata table to obtain a monitoring table; performing parsing and row record extraction processing on the monitoring table to obtain row record cache data; performing grabbing and updating processing on the log data to obtain log records; performing grouping and filtering processing on the log records to obtain business change logs; performing cutting and conversion processing on the business change logs according to the monitoring table and the row record cache data to obtain structured data; determining log change data according to the structured data; wherein the parsing and row record extraction processing on the monitoring table to obtain the row record cache data comprises: performing extraction processing on the monitoring table according to a first preset field to obtain a plurality of data page triplets; performing parsing processing on the plurality of data page triplets to obtain parsed data of the plurality of data pages; performing integration processing on the parsed data of the plurality of data pages to obtain the row record cache data; wherein the grabbing and updating processing on the log data to obtain the log records comprises: performing extraction processing on the log data according to a second preset field to obtain an original log block data set, the original log block data set comprising a plurality of row records; obtaining cursor data; performing grabbing processing on the original log block data set according to the cursor data to obtain the log records; wherein the grouping and filtering processing on the log records to obtain the business change logs comprises: performing grouping processing on the log records according to a third preset field to obtain grouped log data; performing filtering processing on the grouped log data according to a preset condition to obtain filtered log data; performing extraction processing on the filtered log data according to a fourth preset field to obtain the business change logs; wherein the cutting and conversion processing on the business change logs according to the monitoring table and the row record cache data to obtain the structured data comprises: performing retrieval processing on the monitoring table according to the business change logs to obtain column metadata; performing conversion processing on the business change logs to obtain binary stream data; performing cutting processing on the binary stream data according to a preset order to obtain cut data blocks; performing parsing processing on the cut data blocks according to the column metadata to obtain the structured data.

2. The database log parsing method of claim 1, wherein, The query grouping processing on the metadata table to obtain the monitoring table comprises: performing query processing on the metadata table to obtain an original table structure data set; performing identification extraction processing on the original table structure data set to obtain a mapping table; performing grouping and sorting on the original table structure data set according to the mapping table to obtain the monitoring table.

3. The database log parsing method of claim 1, wherein, The determination of the log change data according to the structured data comprises: performing encapsulation processing on the structured data according to a preset format to obtain the log change data.

4. A database log parsing apparatus characterized by comprising: The method comprises the following steps: an obtaining module, configured to obtain a metadata table and log data of a target database; a processing module, configured to perform query grouping processing on the metadata table to obtain a monitoring table; performing parsing and row record extraction processing on the monitoring table to obtain row record cache data; performing grabbing and updating processing on the log data to obtain log records; The log record is subjected to grouping filtering processing to obtain a service change log; the service change log is subjected to cutting conversion processing according to the monitoring table and the row record cache data to obtain structured data; A determining module is configured to determine log change data according to the structured data; The monitoring table is subjected to parsing and row record extraction processing to obtain row record cache data, including: The monitoring table is subjected to extraction processing according to a first preset field to obtain a plurality of data page triplets; The plurality of data page triplets are subjected to parsing processing to obtain parsed data of a plurality of data pages; The parsed data of the plurality of data pages are subjected to integration processing to obtain row record cache data; The log data is subjected to grabbing updating processing to obtain log records, including: The log data is subjected to extraction processing according to a second preset field to obtain an original log block data set, the original log block data set including a plurality of row records; A cursor data is obtained; The original log block data set is subjected to grabbing processing according to the cursor data to obtain log records; The log records are subjected to grouping filtering processing to obtain a service change log, including: The log records are subjected to grouping processing according to a third preset field to obtain grouped log data; The grouped log data is subjected to filtering processing according to a preset condition to obtain filtered log data; The filtered log data is subjected to extraction processing according to a fourth preset field to obtain a service change log; The service change log is subjected to cutting conversion processing according to the monitoring table and the row record cache data to obtain structured data, including: The monitoring table is subjected to retrieval processing according to the service change log to obtain column metadata; The service change log is subjected to conversion processing to obtain binary stream data; The binary stream data is subjected to cutting processing according to a preset order to obtain cut data blocks; The cut data blocks are subjected to parsing processing according to the column metadata to obtain structured data.

5. A computing device, comprising: including: A processor and a memory storing a computer program, the computer program being executed by the processor to perform the method of any one of claims 1 to 3.

6. A computer-readable storage medium, characterized in that, An instruction stored in a computer, when the instruction is executed on the computer, causes the computer to perform the method of any one of claims 1 to 3.

Images