Name: Data Intelligence 12 of 21 - Writing to HANA
Uploaded: 2023-9-6
Duration: 647 s

Share feedback

<p>Planned maintenance of Practice Systems</p>

Planned maintenance of Practice Systems

Why create an SAP account?

Enhance your learning experience

SAP Learning

Subscribe now

Learn more

Live Sessions

Hands-on Practice

Certification

My Learning

Ready to start learning?

<div><div><div>In this video series, we'll introduce some of the applications when using Data Intelligence. In this video, we'll go through using the Modeler to write to HANA.<br><br>0:00 - Introduction<br>1:20 - Operators in the Modeler<br>2:00 - Workflow Trigger operator<br>2:30 - Structured File Consumer operator<br>4:30 - Data Transform operator: projection, aggregation, data target<br>8:30 - Table Producer operator with SAP HANA Cloud connection<br>10:00 - Workflow Terminator operator<br><br><br>Please note:<br>                     This video was originally published on the SAP HANA Academy YouTube Channel                      and therefore may contain references to SAP HANA Academy and YouTube.                      The tech world is always evolving and the SAP HANA Academy YouTube channel                      is set to close on December 31, 2023.                      At that time, references will no longer be valid                      and will gradually be removed when the videos are updated.<br>Also, this video includes automatically generated captions                      and therefore might misrepresent the spoken content or display incorrect product names.</div></div></div>

Data Intelligence 12 of 21 - Writing to HANA

Welcome to the SAP HANA Academy. My name is Bob, and in this series of videos by Digital Partner Engineering and the SAP HANA Academy, we'll be looking at Data Intelligence 3.1. In this video, we're going to look at writing to HANA when using the Modeler. In the previous video we used the Data Intelligence Modeler to connect to an S3 bucket, and take a series of files and consolidate them into two single files. One of them here is called Configuration, and the other one here is called Performance. The structure of both files is exactly the same, and what they contain is data from an IoT device, a date, an ID and some values. The difference between the two files is one of the files show what the actual values of the devices were on a specific date. Where the other file, the configuration.csv file shows what the values should be on a specific date for a specific device. Now what we want to do in this video is quite simply extract the data from our DI data lake and put it into an SAP HANA table. So we're going to do this of course in the modeler. So because we want to merge the two files created in the previous video and ultimately store the results in a HANA database, we're going to look at using structured data operators as well as something called data workflow operators. Now unlike the list files operator that we used in the previous video, many other operators like the structured data operators require an input in order to begin the pipeline execution. So because of this we need to include what's called a workflow trigger operator to the graph. So I'll search for it, so I'll do a search for Workflow and we're going to select that workflow trigger operator and place it on the canvas. Again, to find out what the workflow trigger operator does, all we need to do is right click, go to open documentation, and as it says the workflow trigger operator sends a start signal. So now to read the files, so we need to select what's called a structured file consumer. So if I do a search for structured, we can't really tell which one it is. But if you hover over the icon, you'll see also if you change it to a file view, it's easier to see what the form, what the full name of the operator is. So next, we just need to join the output to the input of the structured file consumer. And then we'll need to configure that structured file consumer. The first thing we'll need to change is the storage type, so I'll choose SDL for semantic data lake. Then we'll have to change that SDL connection. So I'm going to change, go to the Configuration Manager, and I'm going to choose my DI data lake and click on Save. Next we'll need to choose the file. So again, if I go to my Shared folder, go into the SHA folder if I scroll down and choose SHA. Here I'm going to choose that Performance.csv file. Now that we've chosen the file, what we can do is run a data preview just to make sure that the connection works and reviewing the right data. So the only other thing we need to do is make sure fail on string truncation is set to true. Now why we set it to true is because the operator reads a small sample of the data set to infer the length of a string data type. When the parameter is set to true the reader will exit and fail if a string truncation has happened. Now this behavior happens when the length of a string in the metadata is smaller than the actual data in the source file. So now that we've finished configuring, we can just save the graph. So I'm going to call this one SHA.MergeCellStatus. Of course, we can also add a description if we want based on that name. I'll just paste in merge to cell status. After saving we know we've not got any issues now. It says it's got to run but we've not really finished the configuration yet. What we're going to have to do now is deal with data transformation. So to do this we can use a Data Transform operator. So I'll do a search for Data Transform and we'll select that and we'll drag it onto the canvas. Next we'll need to connect our structured file consumer to our Data Transform. Now, when we do that, what will happen is another window will open up which enables us to deal with different operations such as aggregation, joining, projection and union. We'll start off with a projection and we'll place it to the right of our input. To configure this we can just double click on our projection. It's quite straightforward. We've got the source columns on the left hand side and the output on the right hand side. We can automap all of those columns so of course all of them can be included. But of course we can also rename the output columns. So I'm going to rename them as follows. So C0 we know is a date. C1 is our device ID but I'm going to call it cell ID and that is going to be an integer 32. This is going to be called key 1 and it's a float 32. And this is going to be called key 2 and again it's a float 32. Like so. So now we can exit out of the projection and we have to deal with now an aggregation. So I'll select the aggregation on the screen. Of course, I'll join the projection to the aggregation, and we'll configure the aggregation. So again, we've got the target, and again we'll want to have all of our columns. But now we're going to have to deal with some form of aggregation. So for the keys, what I'm going to say is I want everything to be averaged. So I'm going to do an average of the key 1, and I'll also do an average of key 2. As we've not configured the other columns, they'll be automatically part of the group by statement. So now we can go out of the aggregation, and what we want to do is set up one more projection. So I'll join the aggregation to my projection, and we're going to use this to add some extra columns. So what I'll do again, I'll automap, but we're going to add two columns. The first column is going to be from the other file. So that is going to be the nominal value. So, we're going to call this nominal key 1. Again, we'll make it a float 32. And then we'll add another column and we'll call it nominal key 2. Again, we'll make this a float 32. Now, at the moment we have no data for these two columns. We'll get this from performing by performing a join. So, for now we can just put in some dummy values. So, how do we do that? Well, we select the column and for the expression I'm just going to put in the value zero for each of those two columns. Lastly, we can exit the projection and what we'll need to do is what's called create a data target. So we can do this by right-clicking on the projection and we can select create data target which essentially outputs the data from this operator. So now that we've done that we can rearrange the screen and with that data target if we go to and configure the outputs we can see the columns that will be outputted from this data transform. So now we can exit the data transform. Now what we want to do is write this data to HANA. So how do we do that? Well, if I do a search for table producer, so we've got this object called the table producer which I'll place below in the canvas. And then if I go to the documentation, you can see it reads from any structured operators and produces a table as the output. So what we'll do is we'll connect the data transform to the table producer. And then if I right click on the table producer and go to configuration, here we can configure. So for the label I'm going to change this to HANA cell status. And then we just need to configure the client so I'm going to choose HANA. We'll then choose the connection that we want to write to. So if I click on the drop down and we go to our connection manager, we've got a connection already built which connects to a HANA cloud database called HANA underscore cc cloud. And then we just need to configure our HANA target table. So I'm going to put it in a schema called SAP IT dot, and I'm going to give it a name SHA underscore cell status like so. Which of course means the data from that performance CSV file will go into that HANA table. Now to finish the graph we need to use what's called a workflow terminator because we used a workflow trigger. If I open the documentation you can see it shuts down the current graph once a signal is received. And of course we'll connect the two together. So we could save and execute this graph and it would work. The problem is that of course we want to join this to the configuration.csv file. And we can do that in the data transform and I'll show you how to do that in the next video. You can find more tutorials on our YouTube channel. And if you'd like to be informed as soon as new video tutorials are published, then please subscribe. Thanks for watching.