How to backup data to sql server from pi server
use api? and where can i get and how to use ?
Thank you very much~~
Based on your use case i would suggest PI Business Integrator to export data from PI system to SQL.
Machine Learning : Hands-on Labs
PI World 2018 - Apply Predictive Machine Learning Models to Operations
UC 2017 Hands-on Lab: Apply Data Science and Machine Learning to PI System Data for Predictive Analytics
Integrator : PI Integrator for Business Analytics - Roadmap Details which supports IOT hub and Event hub in Azure. Same can be used to inject data to SQL and use ML in Azure for predictive analysis + power BI for visualisation.
PI Web API : You can combination of REST API and python to inject data to PI Event hubs and use ML in Azure.
Could you please let us know the reason why you would like to backup data from PI server to SQL server? PI has its own backup subsystem which takes backup of the archive files which can be restored in case of any corruption.
If you are looking for export of data then you can use PI integrator to export the data view to SQL server and SSIS package to export data from PI OLEDB to SQL server. To backup complete data i would recommend PI backup using pi backup subsystem and schedule task.
KB01032 - PI Data Archive Backup Best Practices
For AF you can take backup of SQL and restore whenever it is required.
Hello Lal Babu Shaik,
first, thank you for your reply. I am engaged in the petrochemical industry manufacturing industry. We need to analyze the data that has been stored in the pi data archive for more than ten years. Because of the large amount of data, how to use python(or caffe) to do machine learning, build models and not affect the online pi server operation. The way we think of is to back up the data to an empty sql server and then do python analysis on another server. Question 1: How can I back up the data from pi data archive to sql server or other database or other ways? Question 2: Is there any other more convenient analysis? Not visual presentations or BI, but building predictive models, etc.
Thank you, Lal Babu Shaik,
your link is very useful!
If you need to know how to do it, I would suggest you need more knowledge of the PI System as a whole.
Numerous ways to get data out but it wouldn't represent a true backup (as Lal points out).
You need to explain a little more.
Hello Rhys Kirk,
First, thank you for your reply. I am engaged in the petrochemical industry manufacturing industry. We have a lot of data. In addition to tens of thousands of tags, we also want to build a machine learning model for analysis. Most importantly, online work is not affected when training model. The way we think of is to back up all data to another server and training model.
Hey Jarvis...ah, okay, so you want to export the data not backup. Plenty of ways to achieve that and it is fairly common practice these days. It depends on your proficiency but you could write something yourself (to cleanse the data as you need for you models), or use an off-the-shelf export mechanism.
Anyway, take care with the data quality you export. I saw a nice presentation from Brandon Perry from PI World in Barcelona on Better Data Quality for Better Data Science. Well worth a watch: Better Data Quality for Better Data Science with the PI System
Backup implies a need to restore from that backup at some point in the future. SQL Server (or any other relational database) is not really a suitable target to backup PI data. Restoring data to PI from a SQL Server 'backup' would come with all sorts of issues and caveats. As has been previously mentioned, this wouldn't represent a true backup (especially not from a disaster recovery perspective).
As Rhys stated, you need to explain a little more what your intention is.
Hello John Messinger,
First, thank you for your reply. I am engaged in the petrochemical industry manufacturing industry. We have a lot of data. In addition to tens of thousands of tags, we also want to build a machine learning model for analysis. Most importantly, online work is not affected when training model. The way we think of is to back up all data to another server and training model. Any suggestions? In fact, it is not necessarily to back up to sql server, we just have one not used, maybe we can use hadoop, etc. But how to back up pi data archive to another server?
In my case what we did was built a backup server(Installed PI DA) and using online backup(piartool -backup "path" -archive "number" ) moved all the online backed up archives to backup server and cloned a PI system from backups created used KB arcticle (2651OSI8 - How to restore a PI system or clone a PI system from backups created from pibackup.bat ).
We had like 2000 archives which we backed up without unregistering and moved to new server in 3-4 days.
This way worked for me, if there is any better approach Lal Babu Shaik,Rhys,John may suggest.
What Rhys said
The export mechanism you choose will be influenced partially by the target system for your machine learning models, and as was mentioned your proficiency in data extraction as well as the tools available to you.
I'm involved in two projects to extract mass amounts of PI data for export to a cloud platform. There i relatively simply, pull data from PI using recordedvalues calls in the AFSDK in a C# application. Trick here is to query all events for a short period of time, such that filesystem caching is used optimal. Next challenge is to stream this data out, as the storage requirements easily mutliply 10-fold. So yes, it's doable.
Now i'm not an expert in platforms for machine learning, but i have my doubts if SQLserver is suitable for large amounts of data. The 10 years of data you need is not the real problem, i think the # of events and PI Points could be. So having any number of how many events we are talking about, makes all the difference in this discussion.
Retrieving data ...