topic Re: UUID JSON export from Mongo DB in Pipelines

UUID JSON export from Mongo DB

Andy_S — Mon, 26 Jun 2023 10:35:03 GMT

We are exporting JSON Files from a Mongo DB with the Data Factory. The UUID in the MongoDB is represented like this "5aa399c0-2701-401e-8e83-26b5ce82abc7". In the exported JSON File whe geht the following values:

"_id": { "$binary": "wJmjWgEnHkCOgya1zoKrxw==", "$type": "03" },

Any ideas how I can get the representation as a GUID in the export file instead the "ugly" binary value?

I already tried modifying the connection string to the MongoDB but nothing worked. And there is no setting to configure the driver used by the data factory to read the values.

Re: UUID JSON export from Mongo DB

GraceGu — Mon, 10 Jul 2023 05:55:38 GMT

Would you mind filling a support case for this? We'll need engineering to track this one down. Thank you!

Re: UUID JSON export from Mongo DB

Marusyk — Thu, 11 Apr 2024 14:24:24 GMT

I faced the same problem. any solutions?

Re: UUID JSON export from Mongo DB

Andy_S — Fri, 12 Apr 2024 05:37:07 GMT

The 'ugly' string is base64 encoded.
We get the MongoDB file with a copy task from the and write it to a blob storage. Afterwards we import it with databricks and decode the base64.

The function we use:

def base64ToGUID(b64str): try: s = bitstring.BitArray(bytes=base64.b64decode(b64str)).hex def rev2(s_): def chunks(n): for i in range(0, len(s_), n): yield s_[i:i+n] return "".join(list(chunks(2))[::-1]) return "-".join([rev2(s[:8]),rev2(s[8:][:4]),rev2(s[12:][:4]),s[16:][:4],s[20:]]) except: return None

We read the JSON to a spark dataframe, convert it to a pandas dataframe and apply the decoding:

# Create pandas dataframe df_pandas = df_spark.toPandas() #iterate over rows to decode the columns for index, row in df_pandas.iterrows(): row["row_name_1"] = base64ToGUID(row["row_name_1"]) row["row_name_2"] = base64ToGUID(row["row_name_2"]) row["row_name_n"] = base64ToGUID(row["row_name_n"])

Hope this helps.

Re: UUID JSON export from Mongo DB

Marusyk — Fri, 12 Apr 2024 09:14:23 GMT

Thanks for your reply. I'm looking for another way because we don't use spark.

GraceGu could you suggest anything? Can I use Power Query (Dataflow Gen2 in Fabric) or transform it in Data Pipeline before/after Copy?

Re: UUID JSON export from Mongo DB

Marusyk — Mon, 15 Apr 2024 19:38:41 GMT

Alternative solutions can be found here: Convert UUID from MongoDb to UNIQUEIDENTIFIER