Forum Discussion

prabhatnath's avatar
prabhatnath
Advocate III
1 year ago
Solved

Need help to parse JSON string that has escape chars

Hi Friends, I am working on a PySpark notebook in Fabric that has below code fragment and the initialized value for log_data is  good working example where the code gives outptu as: JSON is valid! ...
workspaces
  • v-pnaroju-msft's avatar
    v-pnaroju-msft
    1 year ago

    Hi prabhatnath,

    Thank you for reaching out to the Microsoft Fabric Community Forum.

    Please find attached the screenshot and the relevant log_data, which may assist in resolving the issue:


    log_data = "[{\"ApplicationName\": \"Ap001\",\"WorkspaceId\": \"af00f007-7e26-4654-803c-d82f11108b79\",\"Environment\": \"DEV\",\"Level\": \"ERROR\",\"Severity\": \"SEV 4\",\"Component\": \"PL_People\",\"Operation\": \"ETL\",\"Run_Id\": \"f5f780ba-b5b7-452c-80e7-77b05d898799\",\"SessionId\": \"\",\"Message\": \"[DEV] - Ap001 - Pipeline Execution Failed - 20250313\",\"Status\": \"Success\",\"Details\": \"Pipeline Name: PL_People\\nRun Id: f5f780ba-b5b7-452c-80e7-77b05d898799\\nError Message: Notebook Error Message: An error occurred while calling o4570.load.\n: Invalid URI The ABFS endpoint for host: mydomain.dfs.fabric.microsoft.com1 is not supported. It should match one of the valid configured endpoints [fabric.microsoft.com, data.microsoft.com, pbidedicated.windows.net, core.windows.net]\n\tat org.apache.hadoop.fs.azurebfs.AzureBlobFileSystemStore.validateHostnameEndpointsIfRequired(AzureBlobFileSystemStore.java:503)\n\tat org.apache.hadoop.fs.azurebfs.AzureBlobFileSystemStore.getURIBuilder(AzureBlobFileSystemStore.java:455)\n\tat org.apache.hadoop.fs.azurebfs.AzureBlobFileSystemStore.initializeClient(AzureBlobFileSystemStore.java:1649)\n\tat org.apache.hadoop.fs.azurebfs.AzureBlobFileSystemStore.<init>(AzureBlobFileSystemStore.java:258)\n\tat org.apache.hadoop.fs.azurebfs.AzureBlobFileSystem.initialize(AzureBlobFileSystem.java:192)\n\tat com.microsoft.vegas.vfs.VegasFileSystem.initialize(VegasFileSystem.java:133)\n\tat org.apache.hadoop.fs.FileSystem.createFileSystem(FileSystem.java:3469)\n\tat org.apache.hadoop.fs.FileSystem.access$300(FileSystem.java:174)\n\tat org.apache.hadoop.fs.FileSystem$Cache.getInternal(FileSystem.java:3574)\n\tat org.apache.hadoop.fs.FileSystem$Cache.get(FileSystem.java:3521)\n\tat org.apache.hadoop.fs.FileSystem.get(FileSystem.java:540)\n\tat org.apache.hadoop.fs.Path.getFileSystem(Path.java:365)\n\tat org.apache.spark.sql.delta.DeltaTableUtils$.findDeltaTableRoot(DeltaTable.scala:178)\n\tat org.apache.spark.sql.delta.sources.DeltaDataSource$.parsePathIdentifier(DeltaDataSource.scala:357)\n\tat org.apache.spark.sql.delta.catalog.DeltaTableV2.x$1$lzycompute(DeltaTableV2.scala:73)\n\tat org.apache.spark.sql.delta.catalog.DeltaTableV2.x$1(DeltaTableV2.scala:68)\n\tat org.apache.spark.sql.delta.catalog.DeltaTableV2.timeTravelByPath$lzycompute(DeltaTableV2.scala:68)\n\tat org.apache.spark.sql.delta.catalog.DeltaTableV2.timeTravelByPath(DeltaTableV2.scala:68)\n\tat org.apache.spark.sql.delta.catalog.DeltaTableV2.$anonfun$timeTravelSpec$1(DeltaTableV2.scala:122)\n\tat scala.Option.orElse(Option.scala:447)\n\tat org.apache.spark.sql.delta.catalog.DeltaTableV2.timeTravelSpec$lzycompute(DeltaTableV2.scala:122)\n\tat org.apache.spark.sql.delta.catalog.DeltaTableV2.timeTravelSpec(DeltaTableV2.scala:118)\n\tat org.apache.spark.sql.delta.catalog.DeltaTableV2.snapshot$lzycompute(DeltaTableV2.scala:126)\n\tat org.apache.spark.sql.delta.catalog.DeltaTableV2.snapshot(DeltaTableV2.scala:125)\n\tat org.apache.spark.sql.delta.catalog.DeltaTableV2.toBaseRelation(DeltaTableV2.scala:200)\n\tat org.apache.spark.sql.delta.sources.DeltaDataSource.$anonfun$createRelation$5(DeltaDataSource.scala:230)\n\tat org.apache.spark.sql.delta.metering.DeltaLogging.recordFrameProfile(DeltaLogging.scala:141)\n\tat org.apache.spark.sql.delta.metering.DeltaLogging.recordFrameProfile$(DeltaLogging.scala:139)\n\tat org.apache.spark.sql.delta.sources.DeltaDataSource.recordFrameProfile(DeltaDataSource.scala:49)\n\tat org.apache.spark.sql.delta.sources.DeltaDataSource.createRelation(DeltaDataSource.scala:188)\n\tat org.apache.spark.sql.execution.datasources.DataSource.resolveRelation(DataSource.scala:346)\n\tat org.apache.spark.sql.DataFrameReader.loadV1Source(DataFrameReader.scala:236)\n\tat org.apache.spark.sql.DataFrameReader.$anonfun$load$2(DataFrameReader.scala:219)\n\tat scala.Option.getOrElse(Option.scala:189)\n\tat org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:219)\n\tat org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:188)\n\tat java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)\n\tat java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)\n\tat java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)\n\tat java.base/java.lang.reflect.Method.invoke(Method.java:566)\n\tat py4j.reflection.MethodInvoker.invoke(MethodInvoker.java:244)\n\tat py4j.reflection.ReflectionEngine.invoke(ReflectionEngine.java:374)\n\tat py4j.Gateway.invoke(Gateway.java:282)\n\tat py4j.commands.AbstractCommand.invokeMethod(AbstractCommand.java:132)\n\tat py4j.commands.CallCommand.execute(CallCommand.java:79)\n\tat py4j.GatewayConnection.run(GatewayConnection.java:238)\n\tat java.base/java.lang.Thread.run(Thread.java:829)\n\\nExecution URL: https://msit.powerbi.com/workloads/data-pipeline/artifacts/workspaces/af00f007-7e26-4654-803c-d82f11108b79/pipelines/PL_People/f5f780ba-b5b7-452c-80e7-77b05d898799?experience=power-bi\\nApp Name: Ap001\",\"CorrelationId\": \"\",\"User\": \"System\"}]"  

    If you find our response helpful, kindly mark it as the accepted solution and provide kudos. This will help other community members encountering similar queries.

    Thank you.

v-pnaroju-msft's avatar
v-pnaroju-msft
Community Support
1 year ago

Hi prabhatnath,

We are pleased to learn that the information provided has resolved your issue. Kindly mark the response that addressed your query as the accepted solution, as this will assist other community members facing similar challenges in finding solutions more effectively.

Thank you.

prabhatnath's avatar
prabhatnath
Advocate III
1 year ago

Hello, thanks for checking on this thread.

Actually the suggested change did worked to handle \n with the 2 examples provided, but looks like the failing to handle handle \" inside the string. Can you help me with the change that can handle this situation as well.

 

I have provided an example log in my last message.

Thanks,

Prabhat 

  • prabhatnath's avatar
    prabhatnath
    Advocate III
    1 year ago

    Hi Friends,
    Here is an example JSON string that has " inside the "Details" section and that needed to be handelled to ensure the JSON can be used. Please review and suggest an approach for this as I am not sure how do I change the \" into \\" inside the Details section.

     

    Example you can see those words:  "spark.sql.parquet.datetimeRebaseModeInRead", "datetimeRebaseMode", "LEGACY", "CORRECTED". 

     

    var_str_log_data = "[{\"ApplicationName\": \"APP\",\"WorkspaceId\": \"0addb382-fa0d-4ce1-9c3c-95e25b957955\",\"Environment\": \"DEV\",\"Level\": \"ERROR\",\"Severity\": \"SEV 4\",\"Component\": \"PL_APP_Ingest\",\"Operation\": \"ETL\",\"Run_Id\": \"e0dc524a-b36a-49da-8af7-dac7ead251af\",\"SessionId\": \"\",\"Message\": \"[DEV] - APP - Pipeline Execution Failed - 20250319\",\"Status\": \"Success\",\"Details\": \"Pipeline Name: PL_APP_Ingest\\nRun Id: e0dc524a-b36a-49da-8af7-dac7ead251af\\nError Message: Notebook execution failed at Notebook service with http status code - '200', please check the Run logs on Notebook, additional details - 'Error name - Py4JJavaError, Error value - An error occurred while calling o5298.save.\n: org.apache.spark.SparkException: Job aborted due to stage failure: Task 0 in stage 299.0 failed 4 times, most recent failure: Lost task 0.3 in stage 299.0 (TID 3552) (vm-89b62646 executor 1): org.apache.spark.SparkUpgradeException: [INCONSISTENT_BEHAVIOR_CROSS_VERSION.READ_ANCIENT_DATETIME] You may get a different result due to the upgrading to Spark >= 3.0:\nreading dates before 1582-10-15 or timestamps before 1900-01-01T00:00:00Z\nfrom Parquet files can be ambiguous, as the files may be written by\nSpark 2.x or legacy versions of Hive, which uses a legacy hybrid calendar\nthat is different from Spark 3.0+'s Proleptic Gregorian calendar.\nSee more details in SPARK-31404. You can set the SQL config \"spark.sql.parquet.datetimeRebaseModeInRead\" or\nthe datasource option \"datetimeRebaseMode\" to \"LEGACY\" to rebase the datetime values\nw.r.t. the calendar difference during reading. To read the datetime values\nas it is, set the SQL config or the datasource option to \"CORRECTED\".\n	at org.apache.spark.sql.errors.QueryExecutionErrors$.sparkUpgradeInReadingDatesError(QueryExecutionErrors.scala:763)\n	at org.apache.spark.util.Utils$.tryWithSafeFinally(Utils.scala:1529)\n	at org.apache.spark.executor.Executor$TaskRunner.run(Executor.scala:577)\n	... 3 more\n' : \\nExecution URL: https://msit.powerbi.com/workloads/data-pipeline/artifacts/workspaces/0addb382-fa0d-4ce1-9c3c-95e25b957955/pipelines/PL_APP_Ingest/e0dc524a-b36a-49da-8af7-dac7ead251af?experience=power-bi\\nApp Name: APP\",\"CorrelationId\": \"\",\"User\": \"System\"}]"

     

    Thanks,

    Prabhat