Working with the xConnect hex identifiers

If you have gone into the xConnect shard databases and looked around, you will see that it is a lot different than the Mongo data structure from Sitecore 8.2. Namely the identifiers, contacts, contact facets, interactions and interaction facets are all in separate tables now. In 8.2 there were contact and interactions. And everything was a child property of them. I didn't mind it. But I am a fan of separation of data. Having it all in one, while convenient gets messy when you try and break it down.

With this new structure I find myself having to relearn how to go right at the database and find the info I am looking for. In this case I want to contact ID for a user who's email address I know. Me.

In 8.2 that Mongo query was

db.getCollection('Contacts').find({"Identifiers.Identifier":"chris.auer@gmail.com"})

But in 9.x, the identifiers are hexed. My guess is that this helps with data consistency and less chance of malformed data causing data issues.

Step one in this process is what is the hex and how do I see the true value of my identifiers? A quick trip to https://www.rapidtables.com/convert/number/hex-to-ascii.html and I was able to see that what I was looking at was just normal run on the mill hex characters. Sweet. I thought for sure there was going to be some encryption I was going to be fighting with.

So first query, just give me a normal SQL select where I can see the email addresses for my users. Note in this case, my source that I set in my code for my identifier was "emailaddress". You may be using the same or twitter, facebook, etc...

SELECT TOP (1000) [ContactId]
      ,CONVERT(VARCHAR(MAX), [Identifier]) as DecodedIdentifier
      ,[Source]
      ,[Identifier]
      ,[IdentifierHash]
      ,[IdentifierType]      
  FROM [xdb_collection].[ContactIdentifiers]
  WHERE Source = 'emailaddress'

This gives me my normal list but now I can see my email addresses in plain text.

Now how about finding a particular email address?

SELECT TOP (1000) [ContactId]
	  ,[Source]
      ,[Identifier]
      ,[IdentifierHash]
      ,[IdentifierType]	  
  FROM [xdb_collection].[ContactIdentifiers]
  WHERE Source = 'emailaddress'
  AND Identifier = CONVERT(VARBINARY(MAX), 'chris.auer@gmail.com')

Bam!

I have a lot more of these queries for go directly to the shard DB's to get your data. I'll post them in a bit.