Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
As per recent update from Azure monitor, from nov 1,2018 onwards, the log data will be pushed into blob in json lines format.
We use powerBI to load and analyse those json data from azure blob.
Till now, we have been using json.Document method to parse the current json format.
But now, the json.Document method doesn't seem to be parsing the json line format. It throws 'We found extra characters at the end of JSON input'.
So, I happened to find from other posts that to solve this, I had to parse it as a text file and then convert it to json.
-------------------------------------------------------------------------------------------------------------
let
FxFixJson = (jsonfilepathname as text) =>
let
Source = AzureStorage.Blobs("https://MYBLOBSTOREACCT.blob.core.windows.net/"),
#"Expanded Data" = Table.ExpandTableColumn(Source, "Data", {"Content", "Name"}, {"Data.Content", "Data.Name"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Data", each ([Data.Name] = jsonfilepathname)),
#"Data Content" = #"Filtered Rows"{0}[Data.Content],
#"Imported Text" = Table.FromColumns({Lines.FromBinary(#"Data Content",null,null,1252)}),
#"Parsed JSON" = Table.TransformColumns(#"Imported Text",{},Json.Document),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Parsed JSON", "Column1", {"deviceId", "messageId"}, {"deviceId", "messageId"})
in
#"Expanded Column1"
in
FxFixJson
-------------------------------------------------------------------------------------------------------------
My concern is that my current parsing logic should support both current json format and upcoming json lines format.
I dont see anything to distinguish between both the formats (for eg, the extension is same for both the formats).
I am wondering if this has to be a trial and error method.
Like Parse with json.Document and if it fails, then parse it as text and convert to json and it might impact performance.
Is there a cleaner way of parsing of both the json formats? I dont see json lines format is natively supported by powerBI yet.
@V55,
Is there any possibility that you download the JSON file to local drive , then use JSON connector in Power BI Desktop to connect to it?
Also you can share the JSON file here, I will test it in my Desktop.
Regards,
Lydia
Hi @v-yuezhe-msft,
Thanks for the response.
I have actually downloaded the json to local drive and tried with json connector. It did not work, giving same error - "we found extra characters at the end of json input".
Just to give context, our customers are directly fetching the data from azure blobs. We use blob connector to fetch json data and parse. Now, after recent azure monitor change, it looks like we need to have two kind of parsing to support current json format and jsonline format (to support backward compatibility).
Current json format:
{ "records": [ { "time": "2016-01-05T01:32:01.2691226Z", "resourceId": "/SUBSCRIPTIONS/361DA5D4-A47A-4C79-AFDD-XXXXXXXXXXXX/RESOURCEGROUPS/CONTOSOGROUP/PROVIDERS/MICROSOFT.KEYVAULT/VAULTS/CONTOSOKEYVAULT", "operationName": "VaultGet", "operationVersion": "2015-06-01", "category": "AuditEvent", "resultType": "Success", "resultSignature": "OK", "resultDescription": "", "durationMs": "78", "callerIpAddress": "104.40.82.76", "correlationId": "", "identity": { "claim": { "http://schemas.microsoft.com/identity/claims/objectidentifier": "d9da5048-2737-4770-bd64-XXXXXXXXXXXX", "http://schemas.xmlsoap.org/ws/2005/05/identity/claims/upn": "live.com#username@outlook.com", "appid": "1950a258-227b-4e31-a9cf-XXXXXXXXXXXX" } }, "properties": { "clientInfo": "azure-resource-manager/2.0", "requestUri": "https://control-prod-wus.vaultcore.azure.net/subscriptions/361da5d4-a47a-4c79-afdd-XXXXXXXXXXXX/resourcegroups/contosoresourcegroup/providers/Microsoft.KeyVault/vaults/contosokeyvault?api-version=2015-06-01", "id": "https://contosokeyvault.vault.azure.net/", "httpStatusCode": 200 } }, { "time": "2016-01-05T01:33:56.5264523Z", "resourceId": "/SUBSCRIPTIONS/361DA5D4-A47A-4C79-AFDD-XXXXXXXXXXXX/RESOURCEGROUPS/CONTOSOGROUP/PROVIDERS/MICROSOFT.KEYVAULT/VAULTS/CONTOSOKEYVAULT", "operationName": "VaultGet", "operationVersion": "2015-06-01", "category": "AuditEvent", "resultType": "Success", "resultSignature": "OK", "resultDescription": "", "durationMs": "83", "callerIpAddress": "104.40.82.76", "correlationId": "", "identity": { "claim": { "http://schemas.microsoft.com/identity/claims/objectidentifier": "d9da5048-2737-4770-bd64-XXXXXXXXXXXX", "http://schemas.xmlsoap.org/ws/2005/05/identity/claims/upn": "live.com#username@outlook.com", "appid": "1950a258-227b-4e31-a9cf-XXXXXXXXXXXX" } }, "properties": { "clientInfo": "azure-resource-manager/2.0", "requestUri": "https://control-prod-wus.vaultcore.azure.net/subscriptions/361da5d4-a47a-4c79-afdd-XXXXXXXXXXXX/resourcegroups/contosoresourcegroup/providers/Microsoft.KeyVault/vaults/contosokeyvault?api-version=2015-06-01", "id": "https://contosokeyvault.vault.azure.net/", "httpStatusCode": 200 } } ] }
Json line format:
{"time": "2016-01-05T01:32:01.2691226Z","resourceId": "/SUBSCRIPTIONS/361DA5D4-A47A-4C79-AFDD-XXXXXXXXXXXX/RESOURCEGROUPS/CONTOSOGROUP/PROVIDERS/MICROSOFT.KEYVAULT/VAULTS/CONTOSOKEYVAULT","operationName": "VaultGet","operationVersion": "2015-06-01","category": "AuditEvent","resultType": "Success","resultSignature": "OK","resultDescription": "","durationMs": "78","callerIpAddress": "104.40.82.76","correlationId": "","identity": {"claim": {"http://schemas.microsoft.com/identity/claims/objectidentifier": "d9da5048-2737-4770-bd64-XXXXXXXXXXXX","http://schemas.xmlsoap.org/ws/2005/05/identity/claims/upn": "live.com#username@outlook.com","appid": "1950a258-227b-4e31-a9cf-XXXXXXXXXXXX"}},"properties": {"clientInfo": "azure-resource-manager/2.0","requestUri": "https://control-prod-wus.vaultcore.azure.net/subscriptions/361da5d4-a47a-4c79-afdd-XXXXXXXXXXXX/resourcegroups/contosoresourcegroup/providers/Microsoft.KeyVault/vaults/contosokeyvault?api-version=2015-06-01","id": "https://contosokeyvault.vault.azure.net/","httpStatusCode": 200}} {"time": "2016-01-05T01:33:56.5264523Z","resourceId": "/SUBSCRIPTIONS/361DA5D4-A47A-4C79-AFDD-XXXXXXXXXXXX/RESOURCEGROUPS/CONTOSOGROUP/PROVIDERS/MICROSOFT.KEYVAULT/VAULTS/CONTOSOKEYVAULT","operationName": "VaultGet","operationVersion": "2015-06-01","category": "AuditEvent","resultType": "Success","resultSignature": "OK","resultDescription": "","durationMs": "83","callerIpAddress": "104.40.82.76","correlationId": "","identity": {"claim": {"http://schemas.microsoft.com/identity/claims/objectidentifier": "d9da5048-2737-4770-bd64-XXXXXXXXXXXX","http://schemas.xmlsoap.org/ws/2005/05/identity/claims/upn": "live.com#username@outlook.com","appid": "1950a258-227b-4e31-a9cf-XXXXXXXXXXXX"}},"properties": {"clientInfo": "azure-resource-manager/2.0","requestUri": "https://control-prod-wus.vaultcore.azure.net/subscriptions/361da5d4-a47a-4c79-afdd-XXXXXXXXXXXX/resourcegroups/contosoresourcegroup/providers/Microsoft.KeyVault/vaults/contosokeyvault?api-version=2015-06-01","id": "https://contosokeyvault.vault.azure.net/","httpStatusCode": 200}}
I have made changes as per below logic.
Parse data with json parser
-> If success, then expand json data and get the records.
-> if fails, then parse using custom function to convert to json -> then expand data and get the records
Let me know if there is any issue with the below code.
//This query fetches data from the storage account blobs and converts it into expected data format let ParseJsonLine = (#"input content" as binary) => let #"Imported Text" = Table.FromColumns({Lines.FromBinary(#"input content",null,null,1252)}), #"Parsed JSON" = Table.TransformColumns(#"Imported Text",{},Json.Document) in #"Parsed JSON", //StorageAccount is a global user given parameter which specifies the storage account where the data is present Source = AzureStorage.Blobs(#"Storage Account"), //ContainerName is a local parameter which is fixed for every user Container = Source{[Name=Parameters[ContainerName]]}[Data], //Content column contains the JSON we are interested in RemoveOtherColumns = Table.SelectColumns(Container,{"Content"}), //Expand each JSON content value with encoding as 1252 ReadAllJsonBlobs = Table.AddColumn(RemoveOtherColumns , "JSONContent", each Json.Document([Content],1252)), // current Json format will parse - rows without error will return that json format CurrentJsonFormatTable = Table.RemoveRowsWithErrors(ReadAllJsonBlobs, {"JSONContent"}), //Expand JSON record to a list for each blob ExpandJSON = Table.ExpandRecordColumn(CurrentJsonFormatTable, "JSONContent", {"records"}, {"records"}), //Expand each list of records to individual records ExpandRecords = Table.ExpandListColumn(ExpandJSON , "records"), CurrentJsonFormatRecords = Table.RemoveColumns(ExpandRecords,{"Content"}), // json line format will fail with json parser - rows without error will return that json format JsonLineFormatTable = Table.SelectRowsWithErrors(ReadAllJsonBlobs, {"JSONContent"}), ReadAllJsonBlobs1 = Table.AddColumn(JsonLineFormatTable, "JSONContent1", each ParseJsonLine([Content])), RemoveOtherColumns1 = Table.RemoveColumns(ReadAllJsonBlobs1,{"Content", "JSONContent"}), JsonLineFormatRecords = Table.ExpandTableColumn(RemoveOtherColumns1, "JSONContent1", {"Column1"}, {"records"}), // Combining both current format and jsonline format records CombinedTable = Table.Combine({CurrentJsonFormatRecords, JsonLineFormatRecords}), //Expand each record to Shoebox data format ExpandProperties= Table.ExpandRecordColumn(CombinedTable, "records", {"properties", "time", "resourceId", "operationName", "category", "resultType"}, {"properties", "AsOnDateTime", "resourceId", "operationName", "category", "resultType"}) in ExpandProperties
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
48 | |
19 | |
13 | |
11 |