server<\/a>, and in HDFS.<\/p>\nIn each transformation based on the default template, you can create your own script in the PDL language, which resembles shell scripting. <\/p>\n
With the PDL language, you can extend the functionality of transformations and, in particular, you can dynamically (at runtime) generate arbitrary code fragments depending on runtime parameters.<\/p>\n
Ab Initio also has well-developed integration with the OS via shell. Specifically, Linux ksh is used at Sberbank. You can exchange variables with the shell and use them as graph parameters. You can invoke the execution of Ab Initio graphs from the shell and administer Ab Initio.<\/p>\n
In addition to Ab Initio GDE, many other products are included in the delivery. There is a Co>Operation System that claims to be an operating system. There is a Control>Center, where you can schedule and monitor data loading streams. There are products for development at a more primitive level than what Ab Initio GDE allows.<\/p>\n
Description of the MDW framework and work on its fine-tuning for GreenPlum.<\/h3>\n \nAlong with its products, the vendor provides the MDW (Metadata Driven Warehouse) product, which is a graph configurator designed to assist with typical tasks related to populating data warehouses or data vaults.<\/p>\n
It contains user-specific (project-specific) metadata parsers and ready-made code generators 'out of the box.'<\/p>\n
In the cases of Hive and RDBMS, different graphs are generated for initializing and incrementally updating data.<\/p>\n
In the case of Hive, the incoming delta data is joined using Ab Initio Join with the data that was in the table before the update. The data loaders in MDW (both in Hive and RDBMS) not only insert new data from the delta but also close the validity periods of the data by the primary keys for which the delta was received. Additionally, it may be necessary to rewrite the unchanged part of the data. However, this is necessary because Hive does not support delete or update operations.<\/p>\n
Thus, we conclude that in the case of Hive, MDW must resort to rewriting the entire table because Hive lacks update functionality. There is no better solution for data updates than complete rewriting. In contrast, for RDBMS, the product creators deemed it necessary to delegate the joining and updating of tables to SQL usage.<\/p>\n
For a project at Sberbank, we created a new reusable database loader implementation for GreenPlum. This was based on the version generated by MDW for Teradata. Teradata, rather than Oracle, was the best fit since it is also an MPP system. The working methods and syntax of Teradata and GreenPlum turned out to be similar.<\/p>\n
Examples of critical differences for MDW between various RDBMS are as follows. In GreenPlum, unlike Teradata, when creating tables, you need to write the clause<\/p>\n
distributed by<\/code><\/pre>\n\nIn Teradata, you write<\/p>\n
delete <table> all<\/code><\/pre>\n\n, while in GreenPlum you write<\/p>\n
delete from <table><\/code><\/pre>\n\nIn Oracle, for optimization purposes, you write<\/p>\n
delete from t where rowid in ()<\/code><\/pre>\n\n, whereas in Teradata and GreenPlum you write<\/p>\n
delete from t where exists (select * from delta where delta.pk=t.pk)<\/code><\/pre>\n\nIt should also be noted that for Ab Initio to work with GreenPlum, it was necessary to install the GreenPlum client on all nodes of the Ab Initio cluster. This is because we connected to GreenPlum from all nodes of our cluster simultaneously. To ensure that reading from GreenPlum was parallel and each parallel Ab Initio thread read its batch of data from GreenPlum, it was necessary to place an understood by Ab Initio construct in the SQL query's 'where' section.<\/p>\n
where ABLOCAL()<\/code><\/pre>\n\nand define the value of this construct by specifying a parameter for the transformation reading from the database<\/p>\n
ablocal_expr=\"string_concat(\"mod(t.\", string_filter_out(\"{$TABLE_KEY}\",\"{}\"), \",\", (decimal(3))(number_of_partitions()),\"=\", (decimal(3))(this_partition()))\"<\/code><\/pre>\n\n, which compiles to something like<\/p>\n
mod(sk,10)=3<\/code><\/pre>\n\n, meaning it is necessary to inform GreenPlum of the explicit filter for each partition. For other databases (Teradata, Oracle), Ab Initio can perform this parallelization automatically.<\/p>\n
Comparative performance characteristics of Ab Initio working with Hive and GreenPlum.<\/h3>\n \nAn experiment was conducted at Sberbank to compare the performance of the generated MDW graphs for Hive and GreenPlum. In the experiment, Hive had 5 nodes on the same cluster as Ab Initio, while GreenPlum had 4 nodes on a separate cluster. Hence, Hive had a slight advantage over GreenPlum in terms of hardware.<\/p>\n
Two pairs of graphs were examined, performing the same task of data updating in Hive and GreenPlum. The graphs generated by the MDW configurator were run:<\/p>\n
\ninitial load + incremental load of randomly generated data into a Hive table<\/li>\n initial load + incremental load of randomly generated data into the same GreenPlum table<\/li>\n<\/ul>\n\nIn both cases (Hive and GreenPlum), we executed loads in 10 parallel streams on the same Ab Initio cluster. Intermediate data for Ab Initio calculations was stored in HDFS (in terms of Ab Initio, an MFS layout using HDFS was used). One line of randomly generated data occupied 200 bytes in both cases.<\/p>\n
The result was as follows:<\/p>\n
Hive:<\/b><\/p>\n
Initial load in Hive<\/b><\/p>\n
Inserted rows
Duration of initial load
Incremental load in Hive<\/b><\/p>\n
Number of rows that were in
Number of delta rows applied to
Duration of incremental2 541<\/b><\/p>\n
\nGreenPlum:<\/b><\/p>\n
Initial load in GreenPlum<\/b><\/p>\n
Inserted rows
Duration of initial load
Incremental load in GreenPlum<\/b><\/p>\n
Number of rows that were in
Number of delta rows applied to
Duration of incremental321<\/b><\/p>\n
\nWe see that the speed of the initial load in both Hive and GreenPlum is linearly dependent on the volume of data, and due to better hardware, it is somewhat faster for Hive than for GreenPlum.<\/p>\n
The incremental load in Hive also depends linearly on the volume of previously loaded data in the target table and occurs quite slowly as the volume increases. This is due to the need to completely rewrite the target table. This means that applying small changes to large tables is not a very good use case for Hive.<\/p>\n
In contrast, the incremental load in GreenPlum is weakly dependent on the volume of previously loaded data in the target table and occurs quite quickly. This is thanks to SQL Joins and the architecture of GreenPlum, which allows for delete operations.<\/p>\n
Thus, GreenPlum applies the delta using delete+insert, while Hive does not have delete or update operations, so the entire data array had to be rewritten completely during incremental updates. It's most illustrative to compare the highlighted cells, as they correspond to the most common use case for resource-intensive loads. We see that GreenPlum outperformed Hive in this test by 8 times.<\/p>\n
Ab Initio's operation with GreenPlum in Near Real Time mode.<\/h3>\n \nIn this experiment, we will test the ability of Ab Initio to update the GreenPlum table with randomly generated batches of data in a mode close to real-time. We will consider the GreenPlum table dev42_1_db_usl.TESTING_SUBJ_org_finval, which will be worked on.<\/p>\n
We will use three Ab Initio graphs to work with it:<\/p>\n
1) Graph Create_test_data.mp \u2013 creates data files in HDFS with 6,000,000 rows in 10 parallel streams. The data is random, structured for insertion into our table.<\/p>\n
\ngenerate 6,000,000 test rows<\/li>\n perform an initializing load by inserting 6,000,000 test rows into an empty table<\/li>\n repeat the incremental load 5 times\n\ngenerate 6,000,000 test rows<\/li>\n perform the incremental insertion of 6,000,000 test rows into the table (marking old data with a valid_to_ts expiration time and inserting fresher data with the same primary key).<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\nThis scenario emulates the real operation mode of a business system \u2013 a sufficiently large batch of new data appears in real-time and is immediately fed into GreenPlum.<\/p>\n
Now let's look at the log of the scenario's execution:<\/p>\n
Start Create_test_data.input.pset at 2020-06-04 11:49:11
Here is the picture:<\/p>\n
Graph
Create_test_data.input.pset
mdw_load.day_one.current.
Create_test_data.input.pset
mdw_load.regular.current.
Create_test_data.input.pset
mdw_load.regular.current.
Create_test_data.input.pset
mdw_load.regular.current.
Create_test_data.input.pset
mdw_load.regular.current.
Create_test_data.input.pset
mdw_load.regular.current.
\nWe can see that 6,000,000 incremental rows are processed in 3 minutes, which is quite fast.
select valid_from_ts, valid_to_ts, count(1), min(sk), max(sk) from dev42_1_db_usl.TESTING_SUBJ_org_finval group by valid_from_ts, valid_to_ts order by 1,2;<\/code><\/pre>\n\n
Conclusion<\/h2>\n \nCurrently, Ab Initio is used in Sberbank to build a Unified Semantic Data Layer (USDL). This project entails creating a unified version of the states of various banking business entities. Information comes from various sources, which are prepared as replicas in Hadoop. Based on business needs, a data model is created, and data transformations are described. Ab Initio loads information into the USDL, and the loaded data not only holds intrinsic value for the business but also serves as a source for building data marts. The product's functionality allows various systems (Hive, Greenplum, Teradata, Oracle) to be used as recipients, enabling easy preparation of data for the business in various required formats.<\/p>\n
Ab Initio's capabilities are vast; for example, the accompanying MDW framework allows for building technical and business data lineage 'out of the box.' For developers, Ab Initio provides the opportunity not to 'reinvent the wheel' but to utilize a multitude of existing functional components, which essentially act as libraries needed for data handling.<\/p>\n
Author \u2014 an expert of the Sberbank professional community SberProfi DWH\/BigData. The SberProfi DWH\/BigData professional community is responsible for developing competencies in areas such as the Hadoop ecosystem, Teradata, Oracle DB, GreenPlum, as well as BI tools Qlik, SAP BO, Tableau, and others.<\/p><\/blockquote>\n
Source: habr.com<\/a> <\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"\u041d\u0435\u043a\u043e\u0442\u043e\u0440\u043e\u0435 \u0432\u0440\u0435\u043c\u044f \u043d\u0430\u0437\u0430\u0434 \u043f\u0435\u0440\u0435\u0434 \u043d\u0430\u043c\u0438 \u0432\u0441\u0442\u0430\u043b \u0432\u043e\u043f\u0440\u043e\u0441 \u0432\u044b\u0431\u043e\u0440\u0430 ETL-\u0441\u0440\u0435\u0434\u0441\u0442\u0432\u0430 \u0434\u043b\u044f \u0440\u0430\u0431\u043e\u0442\u044b \u0441 BigData. \u0420\u0430\u043d\u0435\u0435 \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0432\u0448\u0435\u0435\u0441\u044f \u0440\u0435\u0448\u0435\u043d\u0438\u0435 Informatica BDM \u043d\u0435 \u0443\u0441\u0442\u0440\u0430\u0438\u0432\u0430\u043b\u043e \u043d\u0430\u0441 \u0438\u0437-\u0437\u0430 \u043e\u0433\u0440\u0430\u043d\u0438\u0447\u0435\u043d\u043d\u043e\u0439 \u0444\u0443\u043d\u043a\u0446\u0438\u043e\u043d\u0430\u043b\u044c\u043d\u043e\u0441\u0442\u0438. \u0415\u0451 \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u0435 \u0441\u0432\u0435\u043b\u043e\u0441\u044c \u043a \u0444\u0440\u0435\u0439\u043c\u0432\u043e\u0440\u043a\u0443 \u043f\u043e \u0437\u0430\u043f\u0443\u0441\u043a\u0443 \u043a\u043e\u043c\u0430\u043d\u0434 spark-submit. \u041d\u0430 \u0440\u044b\u043d\u043a\u0435 \u0438\u043c\u0435\u043b\u043e\u0441\u044c \u043d\u0435 \u0442\u0430\u043a \u043c\u043d\u043e\u0433\u043e \u0430\u043d\u0430\u043b\u043e\u0433\u043e\u0432, \u0432 \u043f\u0440\u0438\u043d\u0446\u0438\u043f\u0435 \u0441\u043f\u043e\u0441\u043e\u0431\u043d\u044b\u0445 \u0440\u0430\u0431\u043e\u0442\u0430\u0442\u044c \u0441 \u0442\u0435\u043c \u043e\u0431\u044a\u0451\u043c\u043e\u043c \u0434\u0430\u043d\u043d\u044b\u0445, \u0441 \u043a\u043e\u0442\u043e\u0440\u044b\u043c \u043c\u044b \u0438\u043c\u0435\u0435\u043c \u0434\u0435\u043b\u043e \u043a\u0430\u0436\u0434\u044b\u0439 \u0434\u0435\u043d\u044c. […]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":87610,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-87609","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-administrirovanie"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t