Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Records in MongoDB have the field _id
_id: UUID('ea265ecd-6d46-4af3-b41d-76fd11154574')
I'm using Data pipeline in Microsoft Fabric to Copy Mongo collection to Warehouse, as a result, I got these 2 columns:
how can I get my UUID back in the Warehouse?
Can I use Power Query (Dataflow Gen2 in Fabric) or transform it in Data Pipeline before/after Copy?
I've tried to convert it in PowerShell and it works as expected:
$b64 = "zV4m6kZt80q0HXb9ERVFdA=="
$bin = [System.Convert]::FromBase64String($b64)
$id= [System.Guid]::New($bin)
Write-Host $id
---> ea265ecd-6d46-4af3-b41d-76fd11154574
But how to do it in Fabric?
There is a similar post but the solution is for Spark which we don't use.
Solved! Go to Solution.
Yes, this query works fine. Thank you for your help. Also, I found one more solution by using Power Query.
Solution 1. SQL Function
First, create the function. Unfortunately, Fabric doesn't support (yet?) scalar functions, so I used table-valued one
CREATE FUNCTION dbo.Base64ToUUID (@base64String VARCHAR(MAX))
RETURNS TABLE
AS
RETURN(
SELECT cast(base64_decode(@base64String) AS UNIQUEIDENTIFIER) AS UUID
);
Then the function can be used as Ad-hoc transformation in queries:
select
"_id.$binary.base64" AS Original,
U.UUID AS Converted
from dbo.Data c
CROSS APPLY dbo.Base64ToUUID("_id.$binary.base64") AS u
or it can be used in Dataflows Gen2 to transform the data and store:
add the function under Get Data
let
Source = Fabric.Warehouse(null){[workspaceId = "bf28769e-c926-427d-b860-2e9999fe2fea"]}[Data]{[warehouseId = "33539h44-9bfc-48dd-93ba-73b0898ad6a9"]}[Data],
#"Navigation 1" = Source{[Schema = "dbo", Item = "Base64ToUUID"]}[Data]
in
#"Navigation 1"
then in other query use it:
// transformation
Result = Table.TransformColumns(Data,
{
{"Id", each Base64ToUUID(_)0}[UUID], type nullable text},
...
}),
Solution 2. Power Query
let
...
Result = Table.TransformColumns(Data, {
{"_id.$binary.base64", each GetGuid(_), type nullable text}
}),
GetGuid = (guidBase64 as nullable text) as nullable text =>
if guidBase64 = null then null
else
let
binaryGuid = Binary.FromText(guidBase64, BinaryEncoding.Base64),
textGuid = Text.FromBinary(binaryGuid),
guid = Guid.From(Binary.ToText(binaryGuid, 1))
in
TransformGuid(guid),
TransformGuid = (guid as text) as text =>
let
parts = Text.Split(guid, "-"),
transformedParts = List.Transform(parts, each SwapLetterPairs(_)),
finalGuid = Text.Combine({transformedParts{0}, transformedParts{1}, transformedParts{2}, parts{3}, parts{4}}, "-")
in
finalGuid,
SwapLetterPairs = (word as text) as text =>
let
pairs = List.Generate(
() => [i = 0, result = ""],
each [i] <= Text.Length(word),
each [i = [i] + 2, result = Text.Range(word, [i], 2) & [result]],
each [result]
)
in
List.Last(pairs)
in
Result
here you can see a little bit of magic. I'll try to explain. I don't know why but this Power Query code can return a UUID but the first part of that UUID has swapped symbols pair! Example:
guidBase64 = "zV4m6kZt80q0HXb9ERVFdA==" // which represents ea265ecd-6d46-4af3-b41d-76fd11154574
binaryGuid = Binary.FromText(guidBase64, BinaryEncoding.Base64),
textGuid = Text.FromBinary(binaryGuid),
guid = Guid.From(Binary.ToText(binaryGuid, 1))
// guid is : cd5e26ea-466d-f34a-b41d-76fd11154574
// Original: ea265ecd-6d46-4af3-b41d-76fd11154574
// problem is here here here
So I defined the SwapLetterPairs function.
Is it a bug or is it by design? It would be great to have a simpler solution but ...
I use Solution 1 because it is more native and less error-prone. Hope this will be helpful for those who have spent hours trying to get this to work 😉
Hi @Marusyk
Thanks for using Fabric Community.
At this time, we are reaching out to the internal team to get some help on this. We will update you once we hear back from them.
Thanks
Hi @Marusyk
You can do this in T-SQL in the warehouse.
select cast(base64_decode('zV4m6kZt80q0HXb9ERVFdA==') as uniqueidentifier)
Hope this helps. Please let me know if you have any further questions.
Hi @Marusyk
We haven’t heard from you on the last response and was just checking back to see if your query got resolved. Otherwise, will respond back with the more details and we will try to help.
Thanks
Yes, this query works fine. Thank you for your help. Also, I found one more solution by using Power Query.
Solution 1. SQL Function
First, create the function. Unfortunately, Fabric doesn't support (yet?) scalar functions, so I used table-valued one
CREATE FUNCTION dbo.Base64ToUUID (@base64String VARCHAR(MAX))
RETURNS TABLE
AS
RETURN(
SELECT cast(base64_decode(@base64String) AS UNIQUEIDENTIFIER) AS UUID
);
Then the function can be used as Ad-hoc transformation in queries:
select
"_id.$binary.base64" AS Original,
U.UUID AS Converted
from dbo.Data c
CROSS APPLY dbo.Base64ToUUID("_id.$binary.base64") AS u
or it can be used in Dataflows Gen2 to transform the data and store:
add the function under Get Data
let
Source = Fabric.Warehouse(null){[workspaceId = "bf28769e-c926-427d-b860-2e9999fe2fea"]}[Data]{[warehouseId = "33539h44-9bfc-48dd-93ba-73b0898ad6a9"]}[Data],
#"Navigation 1" = Source{[Schema = "dbo", Item = "Base64ToUUID"]}[Data]
in
#"Navigation 1"
then in other query use it:
// transformation
Result = Table.TransformColumns(Data,
{
{"Id", each Base64ToUUID(_)0}[UUID], type nullable text},
...
}),
Solution 2. Power Query
let
...
Result = Table.TransformColumns(Data, {
{"_id.$binary.base64", each GetGuid(_), type nullable text}
}),
GetGuid = (guidBase64 as nullable text) as nullable text =>
if guidBase64 = null then null
else
let
binaryGuid = Binary.FromText(guidBase64, BinaryEncoding.Base64),
textGuid = Text.FromBinary(binaryGuid),
guid = Guid.From(Binary.ToText(binaryGuid, 1))
in
TransformGuid(guid),
TransformGuid = (guid as text) as text =>
let
parts = Text.Split(guid, "-"),
transformedParts = List.Transform(parts, each SwapLetterPairs(_)),
finalGuid = Text.Combine({transformedParts{0}, transformedParts{1}, transformedParts{2}, parts{3}, parts{4}}, "-")
in
finalGuid,
SwapLetterPairs = (word as text) as text =>
let
pairs = List.Generate(
() => [i = 0, result = ""],
each [i] <= Text.Length(word),
each [i = [i] + 2, result = Text.Range(word, [i], 2) & [result]],
each [result]
)
in
List.Last(pairs)
in
Result
here you can see a little bit of magic. I'll try to explain. I don't know why but this Power Query code can return a UUID but the first part of that UUID has swapped symbols pair! Example:
guidBase64 = "zV4m6kZt80q0HXb9ERVFdA==" // which represents ea265ecd-6d46-4af3-b41d-76fd11154574
binaryGuid = Binary.FromText(guidBase64, BinaryEncoding.Base64),
textGuid = Text.FromBinary(binaryGuid),
guid = Guid.From(Binary.ToText(binaryGuid, 1))
// guid is : cd5e26ea-466d-f34a-b41d-76fd11154574
// Original: ea265ecd-6d46-4af3-b41d-76fd11154574
// problem is here here here
So I defined the SwapLetterPairs function.
Is it a bug or is it by design? It would be great to have a simpler solution but ...
I use Solution 1 because it is more native and less error-prone. Hope this will be helpful for those who have spent hours trying to get this to work 😉
User | Count |
---|---|
6 | |
6 | |
4 | |
3 | |
3 |
User | Count |
---|---|
14 | |
10 | |
7 | |
7 | |
6 |