Calling R Procedure inside SQL Procedure

[Former Member]

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 :

             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 :

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

          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 :

               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!