Skip to Content

Wiki source code of Data pipeline - Mendix to SFTP

Last modified by Erik Bakker on 2024/02/21 21:53
Show last authors
1 {{container}}
2 {{container layoutStyle="columns"}}
3 (((
4
5 In this microlearning, we will learn how you can set up a data pipeline between Mendix and a random SFTP with the help of eMagiz.
6 With the help of such a data pipeline, you can transfer large volumes of data between Mendix and the SFTP for data warehousing / BI analytics purposes.
7
8 Should you have any questions, please contact [[academy@emagiz.com>>mailto:academy@emagiz.com]].
9
10 == 1. Prerequisites ==
11
12 * Basic knowledge of the eMagiz platform
13 * Basic knowledge of the Mendix platform
14
15 == 2. Key concepts ==
16
17 This microlearning centers around configuring a data pipeline that will transfer large volumes of data between Mendix and a random SFTP.
18
19 With data pipeline we mean: A integration pattern that can transfer large volumes of data between a specific set of source and sink systems
20 With SFTP we mean: A file protocol for transferring large files over the web in a secure manner
21
22 In this microlearning, we will learn how we can easily transport large quantities of data between Mendix and an SFTP
23
24 == 3. Data pipeline - Mendix to SFTP ==
25
26 Imagine you have a Mendix application that is used for communication with customers. An order management system for example. If you want to do trend analysis on the large amounts of data or if you want the large amounts of data to be separately stored on a different location for audit purposes you could transport this data from Mendix to an SFTP with the help of the data pipeline pattern in eMagiz.
27
28 As a data pipeline always transfers data from source to sink system we will start at the source of this data pipeline.
29 Afterward, we will continue with the transfer part and we will finish at the sink system.
30
31 === 3.1 Setting up Mendix ===
32
33 To let the data pipeline retrieve data from your Mendix application you need to publish your data via a Published OData service.
34
35 [[image:Main.Images.Microlearning.WebHome@intermediate-datapipelines-datapipeline-mendix-to-aws-redshift--publish-odata-service-mendix.png]]
36
37 The result of this action should look similar to this
38
39 [[image:Main.Images.Microlearning.WebHome@intermediate-datapipelines-datapipeline-mendix-to-aws-redshift--publish-odata-service-mendix-result.png]]
40
41 To add resources to this OData service you can press the Add icon on this screen or navigate to the domain model to add the relevant resources.
42 Regardless of the option that you choose, the end result should be something like this:
43
44 [[image:Main.Images.Microlearning.WebHome@intermediate-datapipelines-datapipeline-mendix-to-aws-redshift--publish-odata-service-mendix-added-resources.png]]
45
46 Now we have successfully published the relevant data that we need to answer our question.
47
48 === 3.2 Setting up eMagiz ===
49
50 Now that we have made the relevant information available we need to make sure we can retrieve the information via an eMagiz data pipeline.
51 Because we have two tables (Sales and Events) we need two data pipelines.
52
53 ==== 3.2.1 Design ====
54
55 Selecting the correct data pipeline can be done in Design. This way eMagiz will do all the heavy lifting for you.
56 Double click on the integration that you want to edit and select Import Entry Connector
57
58 [[image:Main.Images.Microlearning.WebHome@intermediate-datapipelines-datapipeline-mendix-to-sftp--import-entry-connector.png]]
59
60 The next step would be to select the correct entry connector that you want to import from the store. In this case the Mendix SFTP one.
61
62 [[image:Main.Images.Microlearning.WebHome@intermediate-datapipelines-datapipeline-mendix-to-sftp--import-entry-connector-select-mendix-sftp.png]]
63
64 To finish up our Design phase set the option Data pipeline to Yes. This will ensure that you only need to deploy the entry and not the onramp when you are in Deploy.
65 The result should look as follows.
66
67 [[image:Main.Images.Microlearning.WebHome@intermediate-datapipelines-datapipeline-mendix-to-sftp--import-entry-connector-select-result.png]]
68
69 Don't forget to do the same for the other data pipeline that you need to configure
70
71 ==== 3.2.1 Create ====
72
73 After you have transferred the data pipelines to Create you can edit the entry flows.
74 eMagiz will tell you that the information will be retrieved from the store.
75
76 [[image:Main.Images.Microlearning.WebHome@intermediate-datapipelines-datapipeline-mendix-to-sftp--import-from-store.png]]
77
78 Select Import from Store and let eMagiz do all the heavy lifting. The result of this action will be a flow that looks as follows:
79
80 [[image:Main.Images.Microlearning.WebHome@intermediate-datapipelines-datapipeline-mendix-to-sftp--import-from-store-result-create.png]]
81
82 As you can see the complete setup is already created for you and the various parts have been nicely organized and separated. Let us do a quick walkthrough:
83
84 * Starting at the top left corner we have the Job configuration.
85 * This configuration specifies the source system (Item reader), transformation (Item processor), and the sink system (Item writer). All of them are pre-filled for you
86 * Next to that you have all the support objects needed to run the flow.
87 * One down we have the job launch configuration.
88 * This configuration makes sure that the job, with certain parameters, is launched at a certain point in time. It is up to the user what this point of time is.
89 * In the bottom center we have some specific support objects that are relevant for this particular data pipeline implementation
90 * Last but not least, on the right-hand bottom corner we have the functionality that automatically cleans up the job dashboard.
91
92 The only thing you need to do is to rename the properties where it says message to the technical name of the integration that you have just added (i.e. sale, event). In this data pipeline there are four properties that you need to rename:
93
94 * dp.h2.message.database (Located in the component called support.h2-database)
95 * dp.sftp.message.filename.prefix (Located in the component called transform.job-launch-request)
96 * dp.odata.message.cron (Located in the component called receive.cron)
97 * dp.odata.message.url (Located in the step within the component called system.message.job)
98
99 ==== 3.2.2 Deploy ====
100
101 Although you don't need to build this flow by yourself you still need to fill in various property values to make sure that you retrieve the correct data from Mendix and send it to the correct SFTP.
102 A complete list of these properties can be found on the flow level (under Other -> Properties).
103
104 [[image:Main.Images.Microlearning.WebHome@intermediate-datapipelines-datapipeline-mendix-to-sftp--property-list-flow-level.png]]
105
106 Because the SFTP is managed by an external party we don't need to do any configuration beyond this point.
107
108 == 4. Key takeaways ==
109
110 * A data pipeline is useful when transferring large volumes of data without the need for transformation
111 * Data pipelines are a standardized piece of software in eMagiz that can be implemented with ease
112 * The sink system, in this case, the SFTP, can be used for further analysis of data
113
114 == 5. Suggested Additional Readings ==
115
116 If you are interested in this topic and want more information on it please read the help text provided by eMagiz.
117 )))
118
119 ((({{toc/}}))){{/container}}
120 {{/container}}