Forum Discussion
Solved
UUID JSON export from Mongo DB
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 foll...
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 NoneWe 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.
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 NoneWe 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.
