Patent Application
20250199776
The present disclosure generally relates to automated loading of data, and more specifically to customizable large data loading.
Clients engage in a variety of transactions, for example, by using financial institutions such as banks. For example, transactions can include requesting loans, such as commercial real estate loans, residential real estate loans, personal loans and the like. The banks process various large data files as inputs to be loaded into other data stores, for example, for validation, regulatory reporting, and so on.
In the drawings, which are not necessarily drawn to scale, like numerals may describe similar components in different views. Like numerals having different letter suffixes may represent different instances of similar components. The drawings illustrate generally, by way of example, but not by way of limitation, various embodiments discussed in the present document. Various ones of the appended drawings merely illustrate example embodiments of the present inventive subject matter and cannot be considered as limiting its scope.
Reference will now be made in detail to specific example embodiments for carrying out the inventive subject matter. Examples of these specific embodiments are illustrated in the accompanying drawings, and specific details are set forth in the following description in order to provide a thorough understanding of the subject matter. It will be understood that these examples are not intended to limit the scope of the claims to the illustrated embodiments. On the contrary, they are intended to cover such alternatives, modifications, and equivalents as may be included within the scope of the disclosure.
The techniques described herein solve various technical problems such as automatically analyzing large amounts of data (e.g., financial data having millions of records) to derive a storage format from the data itself. For example, a user-customizable component is provided for that is added into an integrated development environment (IDE), such as a computer program development IDE. The user-customizable component takes as input a large data file that is to be loaded into a cloud-based data store such as a relational database. The user-customizable component first partitions the data file into multiple smaller files using a round robin approach to more evenly distribute the records across the partitions.
The user-customizable component then converts the data into a desired format (e.g., JavaScript Object Notation (JSON)-based format) by automatically generating schemas based on the layout and data types in the input file. As the data is converted, the component generates control files for each partition containing record counts, as well as an overall summary file with total record counts.
The converted data (e.g., JSON-formatted data) is then validated to ensure it can be more properly read using the automatically generated schema. The user-customizable component also performs reconciliation by comparing record counts between the input data and output data to ensure no data was lost or corrupted during processing. Error checking is performed throughout the process. If any partition fails to load properly, the component can automatically restart and reprocess that partition. Accordingly, the user-customizable component efficiently partitions, converts, validates and loads large data files into the cloud data store while performing robust error checking and reconciliation.
Turning now to
In the depicted example, various entities 110, 112, 116 are operatively coupled to the cloud-based system 106 and the cloud data store 108. The entity 110 is additionally operatively coupled to the data store 114. The entities 110, 112, 116 can include financial institutions such as banks, brokerages, insurance institutions, regulatory institutions such as the Federal Deposit Insurance Corporation (FDIC), the Securities and Exchange Commission (SEC), the Federal Reserve Bank (FRB), private entities, and so on. In some examples, a set of source data (e.g., from data stores and/or files) 118 includes information for upload into the data stores 108, 114, such as financial records, regulatory information, ledger information, and so on. The source data 118 may be in various different formats. Further, the data includes large number of records, such as 1,000,000 or more records. It is also to be noted that the source data 118 include data stored in databases, such as relational databases.
The cloud loading system 104 includes a schema creation system 122 that automatically creates a data schema 120 for a given source 118. In one example, the schema creation system 122 reads one or more records in the source (e.g., file) to identify the overall layout and data types of the input file. For example, the schema creation system 122 will read some records in the file to identify if the file is columnar versus row-based. When the data is columnar, the schema creation system 122 identifies each column data type (string, integer, etc.), length, position, and so on, and creates a schema (e.g., JSON-based schema) 120. Likewise, when the file is row-based, the schema creation system 122 identifies row cells, cell data type, length, position, and so on, and creates a corresponding schema 120. In some examples, the file includes a file header with metadata describing the file layout. For example, the metadata may identify column names, column data types, lengths, cell names, and more generally, describe how data in the file is laid out. The schema creation system 122 then reads the metadata in the file header and creates a corresponding schema 120. Accordingly, each of the files will be processed via the schema creation system 122 to derive an equivalent schema 120.
As mentioned earlier, the schemas 120 are JSON-based schemas. For example, the schemas 120 include Apache Avro™ schemas that describe file layout and record information for serialization. Serialization refers to converting data objects or structures (e.g., programmatic objects, classes, structures, and so on) into a format suitable for transmission over a network. More specifically, serialization converts a data object, which can include a combination of code and data represented within a region of data storage, into a series of bytes that saves the state of the object in an easily transmittable form. Accordingly, objects that are part of currently executing computer program are able to be stored via serialization. The schema includes certain data types, such as primitive data types (null, Boolean, int, long, float, double, bytes, and string) and complex data types (record, enum, array, map, union, and fixed). The “record” data type is a named type that has a set of named fields, each with its own type. Names are used to define named schema objects such as records, enums, and fixed types. Namespaces are similar to namespaces in programming languages and help avoid name conflicts. Enums define a type with a limited set of symbols. Arrays are used to hold items of the same type, while maps are used to manage variable fields with string keys and values of the defined type. Unions allow the use of multiple types, letting a field hold data of different types.
In certain examples, the partitioning system 124 partitions or otherwise divides larges files, such as files exceeding certain number of bytes, into various smaller files using a round robin distribution. The partitioning system 124 creates partitioned files 126 and corresponding control files 140. The control files 140 contains the record count for that partition and enable data verification checking that verifies that no data was lost or rejected during processing of that partition. An overall control file is also generated that contains the aggregated record counts and summaries across all partitioned files 126. The overall control file 140 serves as a single source of truth for the total record count and other metadata for the entire source 118. The control file 140 can also include or be linked to one or more schema files 120 that define how the data in each source 118 will be stored when loaded via the cloud loading system 104.
During loading operations, a file writing system 128 will then, for each of the source data 118, load an equivalent schema 120, connect with the data store 108 and/or 114, and transfer the data inside of the source 118. In one example, the schema 120 is used to create a corresponding one or more data structures in the data stores 108 and/or 114. If partition files 126 were created due to large sources 118, the file writing system 128 then serializes data in the partitioned files 126 into the data stores 108, 114, for example, in parallel. That, is multiple serialized data streams are opened by the file writing system 128 and used simultaneously to upload data, e.g., via serialization, from the partitioned files 126 into the data stores 108 and/or 114. Otherwise, the file writing system 128 serializes data in the source data 118. As mentioned earlier, serialization includes the process of converting a data object, which can include a combination of code and data represented within a region of data storage (e.g., in the source 118), into a series of bytes that saves the state of the object in an easily transmittable form. In this serialized form, the data can be delivered to the data stores 108 and/or 114.
As the data is being written into the data stores 108, 114, an I/O logging system writes one or more log files 132 detailing number of records written, locations written into (e.g., data stores 108, 114), any errors encountered, and the like. The log files 132 can then be compared, for example, against the control files 140, to determine if the number of data records written match the number of records found in the source data 118. More specifically, a reconciliation process occurs that compares the data records written into respective data stores 108 and/or 114 and records that were found in the source data 118. Records that were missed are then reuploaded. Further, the file writing system 128 automatically restarts record loading during certain event occurrences (e.g., communication interruptions). For example, if a communication link goes down to the data store 108, 114, the file writing system 128 will pause the data load, “ping” and re-establish communications, and then continue loading data that has not yet been loaded by comparing already loaded records in the I/O log file 132 with total records in the control file 140. In this manner, data in the data sources 118 is more reliably and more efficiently uploaded.
The distributed computing system 102 also includes a memory 134 and one or more processor 138 suitable for storing and executing the cloud loading system 104. The cloud loading system 104 is also shown as interfacing with a user 136 via a graphical user interface (GUI) 142 that includes certain user-customizable programmatic features, as further described below.
In some examples, the user-configurable cloud loading component 204 includes all of the functionality of the cloud loading system 104. That is, the user-configurable cloud component 204 provides for partitioning of data (e.g., via the partition system 124), creation of schemas (e.g., JSON-based schemas via the schema creation system 122), writing of partitioned data (e.g., via the file writing system 128), and logging of operations (e.g., via the I/O logging system 130). In other examples, the treeview control 202 includes multiple components that provide for partitioning of data, creation of schemas, writing of partitioned data, and/or logging of operations that are then drag-and-dropped into the computer program 206. Certain outputs from the user-configurable cloud loading component 204 are provided to an output component 212. For example, logs, receipts verifying that certain data that has been uploaded, user names that uploaded certain data, dates of upload, times of upload, and so on, are then provided to the output component 212 for additional processing.
The user can enter user-configurable information for use by the user-configurable cloud loading component 204, for example, via a tabbed dialog box 214. For example, the user can right-click or otherwise activate the user-configurable cloud loading component 204 to show a pop-up menu that includes a selection to launch the dialog box 214. In some examples, the dialog box 214 includes various tabs, such as a parameters tab 216 enabling user-entered information. The parameters tab 216 is used to enter a block size for blocks in a serialized data stream or block size for a file to be uploaded, names and locations for control files 140, names and locations for I/O log files 132, secure credentials to use the data stores 108, 114, and so on.
A description tab 218 is provided, that includes a description of the user-configurable cloud loading component 204, including how to use the user-configurable cloud loading component 204 as part of a computer program, such as the computer program 206. A ports tab 220 is used to enter certain communication or data ports to use when reading and/or when writing data, and a condition tab 222 is used to enter certain conditions (e.g., logic and other variables) useful in developing with the user-configurable cloud loading component 204.
The user-configurable cloud loading component 204 is depicted as part of a visual computer program 206, such as a flowchart-based computer program that uses graphical icons and links to represent programming elements. By providing for a single drag-and-drop component 204 with schema-creation and serialization, the techniques described herein enable a more efficient software program development of data loading systems.
The “write input logs: block 308 will write to an input log of the I/O logs 132. More specifically, the “write input logs” block 308 will process each partitioned file via a “get input info per partition” block 312 to derive a record count, file size, retrieve any headers, and so on, for each partitioned file. A gather block 314 will then take in all of the information retrieved via the block 312 and provide the data to a rollup block 316. the rollup block 316 will group the information (e.g., grouped as a row of data) and then send the grouped information to a “write input log” block 318. The “write input log” block 318 will then write the partitioned file's information into the input log of the I/O logs 132.
The “prepare Avro” block 310 will prepare each partitioned file to include an Avro format. More specifically, a “reformat (rfmt) remove newline” block 320 reformats the partitioned file by removing newlines special characters and then passes the reformatted partitioned file to a “write Avro” block 322. The “write Avro” block 322 will then write the partitioned file as an Avro object container file. The Avro object container file will have a file header, followed by one or more file data blocks. The file header has four bytes, more specifically ASCII ‘O’, ‘b’, ‘j’, followed by the Avro version number which is 1 (0x01), e.g., binary values 0x4F 0x62 0x6A 0x01. The file header will also include file metadata, including the schema definition, e.g., JSON-based schema. The file header then includes a 16-byte, randomly generated sync marker for the file.
A “filter by expression” block 324 is used as the Avro object container file is being written. More specifically, the “filter by expression” block 324 filters out the header and then sends out to a gather block 326 to then provide certain data to a rollup block 328. The rollup block 328 will then group (e.g., into rows) values such as record counts, file size, and so on, of the Avro file being prepared. A “write TOC file” block 330 then writes, to a control file such as the control files 140, the data for validation at a later time. The control file includes a pset_name containing the name of pset or graph (e.g., computer code) to execute, a run condition whether to enable the component to generate Avro file while running in a sandbox mode or in an operational mode, an avro_file_path where Avro files are to be stored (Avro schema and target data manipulation languages (DMLs) will be placed at same location. The control file additionally includes a source. The source field can be used to place table name, Insert SQL file or Table DML of the target source. For example, “source”: “$AI_SQL/load_wrk_acct_addr_info.sql”, OR “source”: “$MDSS_PUB_MDSS_BASE_DB/TAB_NAME”, OR “source”: “$AI_DML/load_wrk_acct_addr_info.dml.” The control file also includes a dbc_file, so that in the case where source input value is a SQL file or a table, then dbc_file is mandatory to point to the database, and a logging field. The login field is used to capture input counts and validate output records and its count, input bytes read and capture filenames. Logging is then set to Boolean true to enable login, else to false. An example control file with values is as follows:
After preparing the Avro file via the “prepare Avro” block 310, the component 302 then uses a replicate block 332 to send the prepared Avro files to both a “write multiple files” block 334 and a “write output logs” block 336. The “write multiple files” block 334 will write, in parallel, the one or more Avro files prepared via the block 310 into a selected target repository, e.g., data sources 108, 114. The “write output logs” block 336 will then, via a replicate block 338, send the prepared Avro files to a second “write output logs” block 340 and to a “validate Avro schema and record count” block 342. The second “write output logs” block 340 will prepare data (e.g., record count data) for an output log of the I/O logs 132, while the “validate Avro schema and record count” block 342 will compare written records against a record count of an input Avro file to determine any discrepancies between written data and original data.
In the depicted example, a concatenate block 344 compares the original record count data with the written record count data and passes on a comparison to a gather block 346. The gather block 346 then passes on the comparison to a rollup block 348, which groups the data (e.g, in rows) for writing to the output log via a “write output log” block 350. In this manner, the component 302 provides for the creation and use of Avro files to load data.
The “optional RFMT” block 410 will then reformat the partitioned files, for example, by removing certain special characters, replacing certain characters with others, and so on, and pass the reformatted files to a gather block 412. The gather block 412 will then send the reformatted data to a replicate block 414, which will then send the reformatted data both to the user-configurable cloud loading component 302 as well as to a “TD_Load” block 416. The user-configurable cloud loading component 302 will create Avro files and use the Avro files to load data to the desired data source targets, e.g., data sources 108, 114. The “TD Load” block 416 will load the reformatted data in parallel to other desired table-based locations, for example, to keep a record of the reformatted data. The “TD Load” block 416 will additionally create logs 418 based on the reformatted data loaded.
The computer program 402 also shows certain cleanup operations being performed by certain blocks, such as blocks 420, 422. A “create period specific staging table” block 424 is also shown, that creates certain staging tables to be used, for example, as temporary data stores. A “init logs” block 426 performs certain log initiation operations, such as by setting flags and so on. Collecting database statistics provides the optimizer with the data demographics it needs to generate query plans. The more accurate and up to date the statistics, the better the optimizer can decide on plans and choose the fastest way to answer a query. A documentation block 430 is used to document the computer program 402 and the user-configurable cloud loading component 302. Accordingly, executing the computer program 402 will load certain source data, such as source data 118, into certain data sources, such as data sources 108, 114, via Avro files.
The process 500 at block 504, then partitions the large data file into a plurality of smaller partitioned data files, for example by using a round robin distribution to distribute the records via the partitioning system 124, resulting in the partitioned files 126. The process 500 then generates, for each partitioned data file, a data schema based on an automated analysis of each partitioned data file via the schema creation system 122. In some examples, the data schema is a JavaScript Object Notation (JSON)-based data schema. The data schema includes certain data types, such as primitive data types (null, Boolean, int, long, float, double, bytes, and string) and complex data types (record, enum, array, map, union, and fixed). The record data type is a named type that has a set of named fields, each with its own type. Names are used to define named schema objects such as records, enums, and fixed types. Namespaces are similar to namespaces in programming languages such as C# and help avoid name conflicts. Enums define a type with a limited set of symbols. Arrays are used to hold items of the same type, while maps are used to manage variable fields with string keys and values of the defined type. Unions allow the use of multiple types, letting a field hold data of different types.
The process 500 automatically generates, at block 506, the data schemas by reading one or more records in the data source (e.g., file) to identify the overall layout and data types of the input file. For example, the schema creation system 122 will read some records in the file to identify if the file is columnar versus row-based. When the data is columnar, the schema creation system 122 identifies each column data type (string, integer, etc.), length, position, and so on, and creates a schema (e.g., JSON-based schema) 120. Likewise, when the file is row-based, the schema creation system 122 identifies row cells, cell data type, length, position, and so on, and creates a corresponding schema 120. In some examples, the file includes a file header with metadata describing the file layout. For example, the metadata may identify column names, column data types, lengths, cell names, and more generally, describe how data in the file is laid out. The schema creation system 122 then reads the metadata in the file header and creates a corresponding schema 120.
The process 500 also generates, at block 508, a control file for each partitioned data file that contains a record count. That is, the control file stores a total number of records in each partitioned file to be used later for validation that the data uploaded includes all of the records in each partitioned data file. The process 500 then, at block 510, loads each partitioned data file into an external data store based on the data schema generated. For example, the file writing system 128 will load, for each of the source data 118, an equivalent schema 120, connect with the data store 108 and/or 114, and transfer the data inside of the source 118. In one example, the schema 120 is used to create a corresponding one or more data structures in the data stores 108 and/or 114. If partition files 126 were created due to large sources 118, the file writing system 128 then serializes data in the partitioned files 126 into the data stores 108, 114, for example, in parallel. That is, multiple data streams are opened by the file writing system 128 and used simultaneously to upload data, e.g., via serialization, from the partitioned files 126 into the data stores 108 and/or 114. Otherwise, the file writing system 128 serializes data in the source data 118. As mentioned earlier, serialization includes the process of converting a data object, which can include a combination of code and data represented within a region of data storage (e.g., in the source 118), into a series of bytes that saves the state of the object in an easily transmittable form. In this serialized form, the data can be delivered to the data stores 108 and/or 114.
During loading operations, certain conditions may occur that stop the loading, such as an interruption in communications to the data stores 108 and/or 114. The process 500 can automatically restart and reprocess any unloaded data. For example, the I/O log files 132 and the control files 140 can be used to determine the last record loaded, and thus the next record(s) to continue loading after a restart. The process 500 then validates, at block 512, that the external data store (e.g., data stores 108, 114) has received all records in each of the partitioned data files based on the control file.
The process 600, at block 606, displays an input connector on the cloud loading component. The input connector, such as connector 208, will be used to provide input data from various sources, including other components provided via the IDE 200. The process 600 will then, at block 608, connect the input connector to a data source. The data source can include data stores, such as the data stores 108, 114. The data source can also include other components (e.g., modules, classes, objects, and the like) that extract, transform, and/or load data via the input connector.
The process 600 then, at block 610, will compile or otherwise execute the computer program having the user-configurable cloud loading component to load the data. As mentioned earlier, the loading uses serialization to converts a data object, which can include a combination of code and data represented within a region of data storage, into a series of bytes that saves the state of the object in an easily transmittable form into the desired data store.
The machine 700 may include processors 704, memory 706, and input/output I/O components 708, which may be configured to communicate with each other via a bus 710. In an example, the processors 704 (e.g., a Central Processing Unit (CPU), a Reduced Instruction Set Computing (RISC) Processor, a Complex Instruction Set Computing (CISC) Processor, a Graphics Processing Unit (GPU), a Digital Signal Processor (DSP), an Application-Specific Integrated Circuit (ASIC), a Radio-Frequency Integrated Circuit (RFIC), another processor, or any suitable combination thereof) may include, for example, a processor 712 and a processor 714 that execute the instructions 702. The term “processor” is intended to include multi-core processors that may comprise two or more independent processors (sometimes referred to as “cores”) that may execute instructions contemporaneously. Although
The memory 706 includes a main memory 716, a static memory 718, and a storage unit 720, both accessible to the processors 704 via the bus 710. The main memory 716, the static memory 718, and storage unit 720 store the instructions 702 embodying any one or more of the methodologies or functions described herein. The instructions 702 may also reside, completely or partially, within the main memory 716, within the static memory 718, within machine-readable medium 722 within the storage unit 720, within at least one of the processors 704 (e.g., within the processor's cache memory), or any suitable combination thereof, during execution thereof by the machine 700.
The I/O components 708 may include a wide variety of components to receive input, provide output, produce output, transmit information, exchange information, capture measurements, and so on. The specific I/O components 708 that are included in a particular machine will depend on the type of machine. For example, portable machines such as mobile phones may include a touch input device or other such input mechanisms, while a headless server machine will likely not include such a touch input device. It will be appreciated that the I/O components 708 may include many other components that are not shown in
In further examples, the I/O components 708 may include biometric components 728, motion components 730, environmental components 732, or position components 734, among a wide array of other components. For example, the biometric components 728 include components to detect expressions (e.g., hand expressions, facial expressions, vocal expressions, body gestures, or eye-tracking), measure biosignals (e.g., blood pressure, heart rate, body temperature, perspiration, or brain waves), identify a person (e.g., voice identification, retinal identification, facial identification, fingerprint identification, or electroencephalogram-based identification), and the like. The motion components 730 include acceleration sensor components (e.g., accelerometer), gravitation sensor components, rotation sensor components (e.g., gyroscope).
The environmental components 732 include, for example, one or cameras (with still image/photograph and video capabilities), illumination sensor components (e.g., photometer), temperature sensor components (e.g., one or more thermometers that detect ambient temperature), humidity sensor components, pressure sensor components (e.g., barometer), acoustic sensor components (e.g., one or more microphones that detect background noise), proximity sensor components (e.g., infrared sensors that detect nearby objects), gas sensors (e.g., gas detection sensors to detection concentrations of hazardous gases for safety or to measure pollutants in the atmosphere), or other components that may provide indications, measurements, or signals corresponding to a surrounding physical environment. The position components 734 include location sensor components (e.g., a global positioning system (GPS) receiver component), altitude sensor components (e.g., altimeters or barometers that detect air pressure from which altitude may be derived), orientation sensor components (e.g., magnetometers), and the like.
Communication may be implemented using a wide variety of technologies. The I/O components 708 further include communication components 736 operable to couple the machine 700 to a network 738 or devices 740 via respective coupling or connections. For example, the communication components 736 may include a network interface component or another suitable device to interface with the network 738. In further examples, the communication components 736 may include wired communication components, wireless communication components, cellular communication components, Near Field Communication (NFC) components, Bluetooth® components (e.g., Bluetooth® Low Energy), Wi-Fi® components, and other communication components to provide communication via other modalities. The devices 740 may be another machine or any of a wide variety of peripheral devices (e.g., a peripheral device coupled via a universal serial bus (USB) port), internet-of-things (IoT) devices, and the like.
Moreover, the communication components 736 may detect identifiers or include components operable to detect identifiers. For example, the communication components 736 may include Radio Frequency Identification (RFID) tag reader components, NFC smart tag detection components, optical reader components (e.g., an optical sensor to detect one-dimensional bar codes such as Universal Product Code (UPC) bar code, multi-dimensional bar codes such as Quick Response (QR) code, Aztec code, Data Matrix, Dataglyph, MaxiCode, PDF417, Ultra Code, UCC RSS-2D bar code, and other optical codes), or acoustic detection components (e.g., microphones to identify tagged audio signals). In addition, a variety of information may be derived via the communication components 736, such as location via Internet Protocol (IP) geolocation, location via Wi-Fi® signal triangulation, location via detecting an NFC beacon signal that may indicate a particular location, and so forth.
The various memories (e.g., main memory 716, static memory 718, and memory of the processors 704) and storage unit 720 may store one or more sets of instructions and data structures (e.g., software) embodying or used by any one or more of the methodologies or functions described herein. These instructions (e.g., the instructions 702), when executed by processors 704, cause various operations to implement the disclosed examples.
The instructions 702 may be transmitted or received over the network 738, using a transmission medium, via a network interface device (e.g., a network interface component included in the communication components 736) and using any one of several well-known transfer protocols (e.g., hypertext transfer protocol (HTTP)). Similarly, the instructions 702 may be transmitted or received using a transmission medium via a coupling (e.g., a peer-to-peer coupling) to the devices 740.
The techniques described herein provide for analyzing large amounts of financial data to automatically provide for focused financial health advising. Financial health templates are created based on the financial data analysis, that include financial plans for reaching desired goals related to improving a consumer's spending habits, increasing credit scores, achieving savings goals, paying off one or more loans, and the like. In certain examples, the techniques described herein analyze financial data from consumer peers that have achieved similar improvements in financial health, thus improving the chances that a user can reach a desired financial goal.
