{"id":101721,"date":"2024-03-27T17:00:32","date_gmt":"2024-03-27T17:00:32","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=101721"},"modified":"2024-09-03T20:04:46","modified_gmt":"2024-09-03T20:04:46","slug":"creating-a-single-json-file-for-configuration-using-notebooks","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/creating-a-single-json-file-for-configuration-using-notebooks\/","title":{"rendered":"Creating a Single JSON File for Configuration using Notebooks"},"content":{"rendered":"

On the blog Fabric Notebook and Deployment Pipelines<\/a> I explained a technique to keep notebooks configuration values in JSON<\/strong> files on lakehouses, a good solution from many different points of views.<\/p>\n

What if we need to provide maintenance to the JSON<\/strong> configuration file using notebooks?<\/p>\n

The first problem is the fact the typical statement to save a dataframe as JSON<\/strong> will always create a folder containing the JSON<\/strong> files. The folder behaves like a table and all the files contain the records.<\/p>\n

This statement will create a folder containing JSON<\/strong> files:<\/p>\n

<\/p>\n

\n
data_frame.<\/span>write.<\/span>mode('overwrite'<\/span>).<\/span>json('location'<\/span>)\r\n<\/pre>\n<\/div>\n
‘location’ will always be a folder, a single file name will never be accepted.<\/p>\n
The solution is a workaround: We can use mssparkutils<\/strong> to execute file system statements, copying the JSON<\/strong> file as a single file and dropping the folder and its content.<\/p>\n
These are the steps we need to execute:<\/p>\n
    \n
  1. Save the dataframe as a JSON<\/strong> in a temporary location<\/li>\n
  2. The last file in the folder created is the one we want. We need to get it and copy to it’s final location and name<\/li>\n
  3. We drop the entire folder and its content.<\/li>\n<\/ol>\n
    The final result of these steps is the single JSON<\/strong> file on the location we want it. We can develop these steps as a function, as the example below:<\/p>\n
    <\/p>\n
    \n
    def<\/span> saveResult<\/span> (data_frame, temp_location, file_path):\r\n    data_frame.<\/span>write.<\/span>mode('overwrite'<\/span>).<\/span>json(temp_location)\r\n    file<\/span> =<\/span> mssparkutils.<\/span>fs.<\/span>ls(temp_location)[-<\/span>1<\/span>].<\/span>path\r\n    mssparkutils.<\/span>fs.<\/span>cp(file<\/span>, file_path)\r\n    mssparkutils.<\/span>fs.<\/span>rm(temp_location, recurse=<\/span>True<\/span>)\r\n<\/pre>\n<\/div>\n
    The parameters of the function are:<\/p>\n
      \n
    • data_frame:<\/strong> As the name explains<\/li>\n
    • temp_location<\/strong>: Temporary location where we will save the JSON<\/strong> folder<\/li>\n
    • file_path<\/strong>: Final location of the single JSON<\/strong> file we want.<\/li>\n<\/ul>\n
      The statements used on the mssparkutils<\/strong> are the following:<\/p>\n
        \n
      • ls<\/strong>: List the files in the location<\/li>\n
      • cp<\/strong>: Copy a file to a new location<\/li>\n
      • rm<\/strong>: Remove a complete folder, with the option to be recursive, removing the folder content<\/li>\n<\/ul>\n
        Once the function is created, we just need to use it:<\/p>\n
        <\/p>\n
        \n
        #read the json file<\/span>\r\ndf =<\/span> spark.<\/span>read.<\/span>json('Files\/configuration\/config.json'<\/span>, multiLine=<\/span>True<\/span>)\r\n\r\n#change the content as needed<\/span>\r\n#save the json file using the function<\/span>\r\nsaveResult(dt,'Files\/configuration\/accounts'<\/span>,'Files\/configuration\/config.json'<\/span>)\r\n<\/pre>\n<\/div>\n
         <\/p>\n","protected":false},"excerpt":{"rendered":"
        On the blog Fabric Notebook and Deployment Pipelines I explained a technique to keep notebooks configuration values in JSON files on lakehouses, a good solution from many different points of views. What if we need to provide maintenance to the JSON configuration file using notebooks? The first problem is the fact the typical statement to……<\/p>\n","protected":false},"author":50808,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2,159164],"tags":[158998,158997,159035],"coauthors":[6810],"class_list":["post-101721","post","type-post","status-publish","format-standard","hentry","category-blogs","category-microsoft-fabric","tag-lakehouse","tag-microsoft-fabric","tag-notebook"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/101721","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/50808"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=101721"}],"version-history":[{"count":6,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/101721\/revisions"}],"predecessor-version":[{"id":101951,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/101721\/revisions\/101951"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=101721"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=101721"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=101721"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=101721"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}