3 Replies Latest reply on Apr 10, 2018 5:19 PM by atchapda

    How to insert a R dataframe into OSI PI.

    ujjwal

      Hi ,

       

      I want to write the values of a dataframe into osi pi. I am using RJDBC package and PIJDBC driver for connection.

      my dataframe has two columns i.e DATE and POWER.

      I tried using insert query but it inserts only one row at a time.

      Is there a way to write the dataframe directly into PI.

        • Re: How to insert a R dataframe into OSI PI.
          vkaufmann

          Hi Ujjwal,

           

          Would it be possible for you to paste in the R code that you are running to write data back to PI? There are 2 main methods of writing data from R to PI that I can think of off the top of my head:

          1. Calling the PI JDBC driver in your R code

          2. Using the some HTTP methods within R to utilize the PI Web API.

           

          Either method should work, but it is unclear to me how you are currently writing your query.

           

          --Vince

          • Re: How to insert a R dataframe into OSI PI.
            atchapda

            Ujjwal,

            Here is a quick example of how you can create a .NET libraries (in C#) on top of PI AF SDK for using wih rClr, then call the methods to read from PI or write to PI directly from R.

             

            //Add references here

            namespace AccessPI

            {

                public static class PI

                {

                     public static bool WriteToPI(string myPI, string tagName, Object dftCol, Object dfvCol)

                    {

                        bool success = false;

                        //Connect to the PI Server

                        Connect(myPI); 

                        //Cast the R dataframe column holding the timestamps to an arry of string

                        string[] ts = (string[])dftCol;

                        //Cast the R dataframe column holding the values to the corresponding array type

                        double[] val = (double[])dfvCol;

                        //Retrieve the PI Point to write to

                        PIPoint myPIPoint = PIPoint.FindPIPoint(myPIServer, tagName);

                        AFTime time;

                        AFValue myVal;

                        try

                        {

                            for (int i = 0; i < ts.Length; i++)

                            {

                                //Convert the strings to AFTime type, Format accordingly

                                time = new AFTime(ts[i]);

                                //Create AFValue from current row

                                myVal = new AFValue(val[i], time);

                                //Add value to PIPoint

                                myPIPoint.UpdateValue(myVal, AFUpdateOption.InsertNoCompression);

                            }

                            success = true;

                        }

                        catch (Exception ex)

                        {

                            Console.WriteLine("The following exception occurred: {0}", ex);

                            success = false;

                        }

                        return success;

                    }

                 }

            }

             

            Compile the C# code with output as Class Library.

            Copy the generated DLL together with OSIsoft.AFSDK.dll to the bin directory R.

            Launch R

            Install the package rClr (Releases · jmp75/rClr · GitHub)

            Load the rClr package:

            >library(rClr)

             

            Load the OSIsoft.AFSDK.dll and the newly created libraries

            > clrLoadAssembly("C:/Program Files/R/R-3.4.3/bin/i386/OSIsoft.AFSDK.dll")

            > clrLoadAssembly("C:/Program Files/R/R-3.4.3/bin/i386/AccessPI.dll")

            Create a dataframe. Make sure you name the columns. If there is a column of strings, set stringsAsFactors = FALSE

            >df <- data.frame(ts=c("10-apr-2018 10:30:00", "10-apr-2018 10:35:00", "10-apr-2018 10:40:00"), val=c(93.7, 95.1, 96.4), stringsAsFactors = FALSE)

            Call the function and write to PI

            > clrCallStatic('AccessPI.PI,AccessPI', "WriteToPI", "ATchapda-DA1", "MyTestTag", df$ts, df$val)

            [1] TRUE