Using SAP River with OData

Introduction

SAP River is intended to make the life an SAP HANA developer easier. However, one area of SAP River development which is still quite tricky, is OData. Specifically – handling CRUD with associations.

This topic is covered very comprehensively in the SAP River documentation and tutorials, available on the SAP River – SAP Help Portal Page. However, as we have had some questions about this, I decided to extract this information into a mini-tutorial, which is covered by this blog post.

This blog post, the 1st in a series (hopefully), will focus on RAW HTTP calls using a REST client. It shows how to create a new entity instance , including its associated entities (step1), and then how to create a new instance for whom the associations already exist (steps 2 and 3).

Future blogs will cover calling an SAP River backend from a client application, in languages such as HTML/JS or Python.

I am assuming that you have some level of familiarity with how to create basic instances of SAP River entities via OData calls. If not, I recommend you review our documentation and complete one of more of the self guided tutorials , or view some of the SAP HANA Academy videos that cover SAP River.

Scenario

The scenario for this demo is based on the series of cartoons featuring Wile E. Coyote and the Road Runner. It was inspired by a tutorial developed by my colleague Thomas Jung (thanks Thomas !!!).

Its a nice way of introducing SAP River and OData, partly because it’s fun, but mainly because it includes a number of 1:Many and Many:Many relationships.

The data model for the application is provided below.

The core entity is an episode of the cartoon series. There are also names of contributors to the cartoon (e.g. “Chuck Jones”),  and the ACME items that the Coyote uses (unsuccessfully) to catch the road runner (e.g. “Rocket-Powered Roller skates”) .

A contributor can appear in multiple episodes, in the role of  writer and/or director. For these we use a regular River “association to…”.

In addition, a single episode can have multiple ACME items, and the same ACME item can appear in multiple episodes. This many:many relationship is resolved with an intermediate entity, Item Appearance, using the SAP River “association… via” syntax to capture this. Here is the SAP River source code:

@OData

application DevPackage.RoadRunner {

       entity Episode {

              key episodeId : Integer;

              name : LargeString;

              myWriter : association to Contributor ;

              myDirector : association to Contributor;

              myItems : association [0..*] to ACMEItem via entity ItemAppearance;

                                       

       }

    

       entity Contributor {

              key contributorId : Integer;

              name : LargeString;

       }

    

       entity ACMEItem {

              key itemId : Integer;

              name : LargeString;

       }          

                         

       entity ItemAppearance {

              episode : association to Episode;

              acmeItem : association to ACMEItem;

       }

           

}

NOTE :

OData Step 1 – Creating an episode including its associations

Once my code is activated, I would like to start loading data into the database.

My first OData call will create a single episode :

I am calling the url :

http:<myserver>:<port>/<myPackage>/odata/RoadRunner/Episode

using a POST call (not forgetting to initiate a GET call first, in order to retrieve a valid X-CSRF Token),

and passing the following JSON object in the body :

{ “episodeId”: 1,

  “name”: “Fast and Furry-ous”,

  “myWriter”: { “contributorId”: 1, “name”: “Michael Maltese”} ,

  “myDirector”: { “contributorId”: 2, “name”: “Chuck Jones”} ,

  “myItems”: [{ “itemId”: 1, “name”: “ACME Super Outfit”} ]}

Note that for myItems I used a set of square brackets [] around the data, as this is an array of multiple items (even though in this example there is only one item), whereas for myWriter and myDirector I am not, as there is always a single Writer of Director per episode.

Here is the result :

However, this is just a “flattened” view of the data for display purposes, not how the underlying data is stored. In practice, River not only created the episode entity, but also the associated entities, so for example, in OData, the episode entity actually looks like this :

{

    “d”: {

        “results”: {

            “__metadata”: {

                “uri”: “/DevPackage/odata/DevPackage.RoadRunner/Episode(1)”,

                “type”: “DevPackage.RoadRunner.Episode_entityType”,

                “etag”: “W/\”457883B8ED452F9C199311D927B6DF29C4539FA5734B0966867811F0E4CC5EEF\””,

                “properties”: {

                    “myWriter”: {

                        “associationuri”: “/DevPackage/odata/DevPackage.RoadRunner/Episode(1)/$links/myWriter”

                    },

                    “myDirector”: {

                        “associationuri”: “/DevPackage/odata/DevPackage.RoadRunner/Episode(1)/$links/myDirector”

                    },

                    “myItems”: {

                        “associationuri”: “/DevPackage/odata/DevPackage.RoadRunner/Episode(1)/$links/myItems”

                    }

                }

            },

            “episodeId”: 1,

            “name”: “Fast and Furry-ous”,

            “myWriter”: {

                “__deferred”: {

                    “uri”: “/DevPackage/odata/DevPackage.RoadRunner/Episode(1)/myWriter”

                }

            },

            “myDirector”: {

                “__deferred”: {

                    “uri”: “/DevPackage/odata/DevPackage.RoadRunner/Episode(1)/myDirector”

                }

            },

            “myItems”: {

                “__deferred”: {

                    “uri”: “/DevPackage/odata/DevPackage.RoadRunner/Episode(1)/myItems”

                }

            }

        }

    }

}

and the Contributor entity looks like this

{

    “d”: {

        “results”: [

            {

                “__metadata”: {

                    “uri”: “/DevPackage/odata/DevPackage.RoadRunner/Contributor(1)”,

                    “type”: “DevPackage.RoadRunner.Contributor_entityType”,

                    “etag”: “W/\”C2D0323C73325B1C177E6915852A9421AFC4AADF8740E819E1BFE0223B9AF359\””

                },

                “contributorId”: 1,

                “name”: “Michael Maltese”

            },

            {

                “__metadata”: {

                    “uri”: “/DevPackage/odata/DevPackage.RoadRunner/Contributor(2)”,

                    “type”: “DevPackage.RoadRunner.Contributor_entityType”,

                    “etag”: “W/\”D819CB0707A48A78C9DD2616A3546027A810C7BF1C070E466667E78D64F148D9\””

                },

                “contributorId”: 2,

                “name”: “Chuck Jones”

            }

        ]

    }

}

OData Step 2 – Creating an episode with existing associations

The 2nd Episode is a bit trickier, as the writer and director names are the same as in the 1st Episode. If I use the same approach as in step 1, I will end up with duplicate records.

There are two options to create a record that it associated with existing records. In this example I will show one method, and in the next example, I will show another, slightly longer method, which I think is conceptually simpler.

The 1st method is to call the same URL as in the previous step, but rather than specifying the associated record, I am using a URI to point to an existing record.

Essentially, I am calling the URL via POST :

http://<myServer>:<port>/<myPackage>/odata/RoadRunner/Episode

and passing the following JSON body

{

    “episodeId”: 2,

    “name”: “Beep Beep”,

    “myWriter”: {“__metadata”: {“uri”: “/DevPackage/odata/DevPackage.RoadRunner/Contributor(1)”}},

    “myDirector”: {“__metadata”: {“uri”: “/DevPackage/odata/DevPackage.RoadRunner/Contributor(2)”}},

    “myItems”: [{“itemId”: 2,”name”: “Aspirin”},{“itemId”: 3,”name”: “Matches”},{“itemId”: 4,”name”: “Roller Skates”}]

}

Notice that I am using a uri to refer to existing instances of contributors (in the role of writers and directors), whereas I am inserting new instances of ACME items (3 in fact).

The ‘flattened ‘ result is as follows :

But of course I haven’t created a 2nd instance of Michael Maltese or Chuck Jones, I am simply pointing to the existing instance.

To see the hierarchical view, I can call the URL :

http://<myServer>:<port>/<myPackage>/odata/RoadRunner/Episode(episodeId=2)?$expand=myDirector

{

    “d”: {

        “results”: {

            “__metadata”: {

                “uri”: “/DevPackage/odata/DevPackage.RoadRunner/Episode(2)”,

                “type”: “DevPackage.RoadRunner.Episode_entityType”,

                “etag”: “W/\”457BD75B4D33D0F041E17BFBE5AB7CE74563298103D7BA1DC682AC1F6D9A2F9C\””,

                “properties”: {

                    “myWriter”: {

                        “associationuri”: “/DevPackage/odata/DevPackage.RoadRunner/Episode(2)/$links/myWriter”

                    },

                    “myDirector”: {

                        “associationuri”: “/DevPackage/odata/DevPackage.RoadRunner/Episode(2)/$links/myDirector”

                    },

                    “myItems”: {

                        “associationuri”: “/DevPackage/odata/DevPackage.RoadRunner/Episode(2)/$links/myItems”

                    }

                }

            },

            “episodeId”: 2,

            “name”: “Beep Beep”,

            “myWriter”: {

                “__deferred”: {

                    “uri”: “/DevPackage/odata/DevPackage.RoadRunner/Episode(2)/myWriter”

                }

            },

            “myDirector”: {

                “__metadata”: {

                    “uri”: “/DevPackage/odata/DevPackage.RoadRunner/Contributor(2)”,

                    “type”: “DevPackage.RoadRunner.Contributor_entityType”,

                    “etag”: “W/\”D819CB0707A48A78C9DD2616A3546027A810C7BF1C070E466667E78D64F148D9\””

                },

                “contributorId”: 2,

                “name”: “Chuck Jones”

            },

            “myItems”: {

                “__deferred”: {

                    “uri”: “/DevPackage/odata/DevPackage.RoadRunner/Episode(2)/myItems”

                }

            }

        }

    }

}

This also demonstrates an additional OData feature, which is the $expand query parameter. This allows me to expand the myDirector association (in Bold) all the way to the end record.

OData Step 3 – Creating an episode with existing associations – Option 2

The 2nd method is a two step process. In the 1st step, I create the new record, while ignoring any associations to existing records.

Calling this URL via POST :

http:<myserver>:<port>/<myPackage>/odata/RoadRunner/Episode

and passing this body :

{

    “episodeId”: 3,

    “name”: “Going! Going! Gosh!”,

    “myItems”: [{“itemId”: 5,”name”: “an anvil”},

                     {“itemId”: 6,”name”: ” a weather balloon”},

                     {“itemId”: 7,”name”: “a street cleaner’s bin”},

                     {“itemId”: 8,”name”: “a fan”}]

}

Here is the interim result :

Then, I use the $links option, to associate an existing contributor, name or item with this new episode.

For example, calling :

http://<myServer>:<port>/<myPackage>/odata/RoadRunner/Episode(episodeId=3)/$links/myWriter

as a POST with JSON :

{ “uri”: “/DevPackage/odata/DevPackage.RoadRunner/Contributor(1)”}

After doing the same for director as well, the result now looks as follows :

In Summary

What we have seen, is how to insert records into an SAP River data model that involves complex 1:Many and Many:Many associations, either by creating the associated entities, or by linking to existing records. We also saw how the $expand option can be used to view the structure of the data in OData.