CD2 and Canvas Live Events format entity ids differently: are there any plans to address the discrep

Question

While I was originally happy to see that the long ids in Canvas Data 1 (e.g. of the type 64750000000043215 ) had been removed in Canvas Data 2, it eventually came time for me to re-write all the queries we have in place for various uses. These queries mostly join Canvas Live Events with 1 or more Canvas Data tables.  With Canvas Data 1 this was relatively easy to do, as the long ids were present in both CD1 and also Canvas Live Events. However, the migration to CD2 therefore presents the additional challenge that the long ids remain in Canvas Live Events.  
While it's not impossible to convert one to match the other (just for the purposes of running queries), it is definitely not convenient. In addition, it would make sense for Instructure to use the same convention in both data sources, of course. Are there any plans to do this?

pgo586 · Answer

Interesting point @KeithSmith_au about the multiple Canvas instances (we have only one, and although I was aware that the long ids include a reference to a shard, I had completely forgotten when they could be useful). It seems that your solution involves quite a bit of work and it might not be the best one for those institutions having only one instance though. In my case, I've resorted to augment/calculate for now an extra field for the live events stream instead, that gives me the local reference  (then I can join using the local reference) but I can see why you may need to augment CD2 tables though. I am still interested in hearing from Instructure though, both in terms of their thoughts/plans to make their handling more consistent across data sources, and also to see if they recommend a particular best practice to deal with the inconsistency (I imagine they must have considered this point as they moved from the long ids to the local ids in CD2).

KeithSmith_au · Answer

For most users this is a moot point, but you do need to understand that their is a real meaning behind this.  If you only have a single Canvas instance, then all the the id references are local (to entities in the same instance).  If you are a large organisation and running a Consortia (multiple instance of Canvas tied together with a trust relationship to allow for large scale), then entity references can either be local (same instance) or to entities in a different instance.  In this case, the long form is necessary to determine the actual entity, since the ids are not unique across the instances.

The ways in which this is handled are not always consistent (as you are discovering).  Working with the large amounts of data that comes with having a Consortia (we have 12 Canvas instances in a Consortia in production), the most efficient way I have found to deal with this is augment the tables on ingestion with the relevant long (sharded) reference for all columns where a non-local reference is possible (many things cannot refence across instances, e.g. a course section cannot be in a different instance to the course, but an enrolled user can be), and then always use a globally unique long reference to do the joining.