How to automate with Jenkins the assembly and rolling-out of artifacts of the metadata model for tables in the repository

It all started with the fact that we were faced with the need to quickly and correctly form the structures of EDWEX, JSON, DDL and then roll them onto different contours of relational databases. By contours, I mean familiar abbreviations - DEV, TST, UAT, PRD.



At that time, we did almost everything manually: and generated DDL, and collected edwexes and jsones based on metadata from the Oracle database. Input parameters set. Miss one - and incorrectly form the essence. And since the whole process of formation was consistent and continuous, the error will be revealed only at the very end. Read about how we have automated everything and overcame errors under the cat.

Little about infrastructure

Before filling data into relational database tables, we need to accept them from the source - in any format, for example, in Excel. Data from the source using the in-house mechanism is transported to Hadoop (Data Lake). The Hiveop add-in is installed on Hive - with its help we can view the contents of files using an SQL-like syntax.

To transfer the data to Data Lake in the form of tables, we need json'y, which are formed on the basis of metadata. To parse the data in Excel format, we need to create EDWEX files. EDWEX (EDW_EXTRACTOR) files are a set of artifacts that contain sets of tables with the name, as well as sets of fields for each of these tables, formed on the basis of metadata. Depending on the model version and source ID, the field set is different. The formation of the DDL is necessary to create the tables themselves in the Hive database at the operational data level and in the Greenplum database at the detailed and aggregated data level. That is, the data is initially transferred to Hive, if necessary, filtered and transferred to the Greenplum for subsequent manipulations with the data and creating windows based on them.

How to automate

To solve the problem, we used:

• Jenkins - as an orchestrator and a tool for implementing the CI process.
  
• Python - it implements the functionality and unit tests.
  
• SQL - to access the metadata database.
  
• Shell script - for copying artifacts between directories and generating scripts in multijob projects running on a Jenkins server.
  

To begin with, we initially work with a large number of sources, so using the Shell script as a launch parameter, we transfer source IDs to SQL functions to identify them. The resulting SQL functions will subsequently be automatically executed in the metadata database. Based on the available metadata, these functions form a file with a list of new SQL functions for each of the source tables, so that we can subsequently call them in an executable program. The result of the execution of the generated functions is the transfer to the output parameter of the DDL, JSON or EDWEX values.

It connects Python, which is written all the executable functionality and unit-tests. Before running any of the modules for rolling out artifacts with the help of unit tests, the correct transfer of parameters for running python scripts is checked. Tests not only check the correctness of the input parameters, but also their presence in the metadata module, the size of the created files and the date they were created. Tests also monitor the number of new artifacts created and the number of existing artifacts. So we optimize the use of server resources, because we take for rolling out only new files and do not install existing models again.

And only after successful completion of all checks, a python program is executed that creates the necessary artifacts and lays out the result in the required project folders on the server. Python not only breaks down the generated json files into directories, but also structures them into Data Lake so that the data loads correctly. When generating DDL artifacts, they are not only saved for later analysis and reuse, but can also be immediately installed in the database using new models and structures specified in the metadata module. This allows you to create hundreds of tables in a short time without involving manual labor.

And where is Jenkins?

Jenkins enters when it is necessary to control all these processes visually using the interface.

This tool was chosen because it:

• fully covers the automation requirements of assembly and installation
  
• allows you to design a mechanism for assembling artifacts with the implementation of the autotesting process
  
• allows you to easily manage run jobs and performance monitoring to a person far from programming
  
• allows you to customize the logging mechanism in such a way that the result of the execution will be clear to anyone in the team. The problem in the assembly will obviously be indicated, or the process will be executed successfully.
  

To solve the tasks we have created several multijob projects. This type of project was used, since it can work in parallel with other jobs with a one-time start. Each of the jobs is responsible for the implementation of its block of functionality. So we replaced the sequential process of obtaining artifacts on stand-alone parallel ones. Everything runs separately: the formation of EDWEX, JSON, DDL, the formation of structure in HIVE, the installation of table structures in the database. We analyze the result obtained at different stages of artifact formation and proceed to launch subsequent actions if successful.

Jenkins-part is implemented without any special tricks. Input Jobs are given String or Run parameters to run the python code. StringThis parameter is a window for entering values ​​of the str type before launching. The Run parameter can be transferred on the fly to another job for execution; all you need to do is to specify from which project you need to pick up the obtained variable. A separate parameter is also passed to the node for execution. Here, the splitting of runtime environments into DEV, TST, UAT, PRD is implemented. Separate Jobs made the transfer of the received EDWEX files to the SVN with the revision number to be able to track the versions of the modified structures.

Jenkins interface example:



The result of the execution of jobs is the creation and installation of the necessary artifacts, their transfer to SVN and the generation of an HTML report that reflects the success of the unit tests and the results of the assembly and installation of the artifacts. Joby can be run either by hand separately or in automatic mode, having previously built a chain of execution.


Architecture of the assembly and installation mechanism

Summarize

Much work has been done to automate the formation of artifacts. Previously, you had to manually climb onto the server, run shell scripts, and then study and edit the data with your hands for a long time. Now it’s enough to just press the start button, specify the source system ID, model number and contour of execution. With Jenkins, we managed to structure and divide the entire assembly and installation mechanism of artifacts into independent stages. Necessary checks were added before launching the formation of artifacts and their integration. The resulting artifacts are automatically transferred to SVN, which simplifies working with them to adjacent teams of system analysts and data modelers. Implemented checks to avoid idle launches of artifact formation and to confirm their correctness.

As a result, we have reduced the time-consuming process of assembling and installing model artifacts from a few hours to a couple of minutes. And most importantly, eliminated the occurrence of errors due to the human factor, which inevitably arose in a complex routine process.