Thanks to the comprehensive information available on SCN, integrating R with HANA is very easy .However, I could not find any information on SCN about calling an R procedure from within an SQL Procedure. This led to the idea of my first blog on SCN!

This blog intends to demonstrate passing data from SQL Procedure to a R Procedure, perform Analytics on the data and return the result to the calling SQL procedure.

            Let’s consider the following situation :


/wp-content/uploads/2015/06/screenshot1_730835.jpg

             The table (“MY_SCHEMA”.”INPUT_DATA”) consists of data related to parameters of flowers.

              Suppose there is a requirement to perform Clustering on the dataset, based on Sepal Length. The Column on which Clustering needs to be performed, has to be selected to a temporary variable and passed to the R Procedure as in the following screenshot :

Capture.JPG

        

           The R Procedure to perform the analytics will be as in the below screenshot :

Capture.JPG

          The Column “Sepal Length” is clustered into 10 Clusters.

          Output from R should be captured in a table type (“OUTPUT_FROM_R”  in the screenshot)

          The Output table type should consist of all the columns  which will be needed after  clustering.

          The output of the SELECT statement in the SQL Procedure will be as below :


/wp-content/uploads/2015/06/screenshot1_730835.jpg


               We can analyze how each of the species fall in different clusters based on the Sepal Length.

   Compiling and calling the procedures should be done in the following order :

               1. Compile R Procedure.

               2. Compile SQL Procedure.

               3. Call R Procedure inside the SQL Procedure.

               4. Call the SQL Procedure.

  Hope this helps the community!

To report this post you need to login first.

11 Comments

You must be Logged on to comment or reply to a post.

  1. Varun Kohli

    I must say, it’s an amazing article Amit !

    Very articulate and packed up with loads of helpful knowledge.

    Keep writing and keep sharing 🙂

    (0) 
    1. Amit Goutham Post author

      Hi Madara,

      INPUT_TO_R is a table type which will contain all the columns on which Analytics need to be performed. It will contain the result of the SELECT statement from the SQL Procedure!

      Thanks!

      (0) 

Leave a Reply