Using XID Prefix with PTCP Publishers

[Wingnut2.0]

After testing XID prefix on PTCP Publishers I noticed when changing the prefix additional datapoints are created (with the new prefix) on the receiving data source. Easy enough to delete the data points with the original prefix.

With Publish point attribute changes enabled on the publisher and Accept point settings updates enabled on the data source will there be any impact on the historical data?

Are there any additional design considerations when incorporating the prefix in a could/data warehouse environment one should be aware of?

Thank you.

[phildunlap]

Hi Wingnut2.0,

After testing XID prefix on PTCP Publishers I noticed when changing the prefix additional datapoints are created (with the new prefix) on the receiving data source. Easy enough to delete the data points with the original prefix.

Correct, they are perceived as different / new points by the receiver. If you want to keep the points' old data then you can do some SQL with the REPLACE function based off the dataSourceId column , or the old prefix. Best to do that with the data source disabled (and before the points have been created with the new XIDs).

With Publish point attribute changes enabled on the publisher and Accept point settings updates enabled on the data source will there be any impact on the historical data?

They are not related. The publishing of point attributes is for the unreliability flag for the most part. Perhaps in the future there will be more attributes in common use, but that's really it right now. Publishing that information will not affect historical data.

While I can't quite recall testing it, I would think you may delete all data on a received data point if you changed its data type on the publisher (thus deleting all historical data there, also). But, that seems uncommon to do.

Are there any additional design considerations when incorporating the prefix in a could/data warehouse environment one should be aware of?

There is a 100 character restriction on XID sizes, so don't make the prefix too long! Other than that, I think it's personal preference how one uses XIDs.

[Wingnut2.0]

Thanks Phillip. So in a situation where a publisher previously did not have a prefix, by adding one I will effectively be losing all historical data on that data source unless this prefix is also added via SQL?

You mention SQL REPLACE as I was asking about changing prefixes, but could you please provide a SQL example of how this would be done (UPDATE?) if going from no prefix to adding a prefix?

[phildunlap]

You mention SQL REPLACE as I was asking about changing prefixes, but could you please provide a SQL example of how this would be done (UPDATE?) if going from no prefix to adding a prefix?

Certainly!

1. Disable receiver.
2. Add prefix on publisher.
3. Run SQL,
   UPDATE dataPoints SET xid=CONCAT('NewPrefix_', xid) WHERE dataSourceId=(SELECT id FROM dataSources WHERE xid='DS_Receiver_XID_Here');
4. Re-enable receiver.

I guess it was more portable to use concat. I was originally thinking replace because regex is so handy, but I think it'd have to rely on the points having begun with 'DP_' to use REPLACE, something like,

UPDATE dataPoints SET xid=REPLACE(xid, 'OldPrefix_', 'NewPrefix_') WHERE dataSourceId=(SELECT id FROM dataSources WHERE xid='DS_Receiver_XID_Here');

Ah, REGEXP_REPLACE would still be portable to MySQL and H2, but these two should get it done.

[Wingnut2.0]

Thanks again Phillip. Both examples are very helpful.