{"id":8968,"date":"2024-02-19T14:33:06","date_gmt":"2024-02-19T14:33:06","guid":{"rendered":"https:\/\/craftydba.com\/?p=8968"},"modified":"2024-12-26T21:03:29","modified_gmt":"2024-12-26T21:03:29","slug":"thread-02-data-engineering-with-fabric","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=8968","title":{"rendered":"Thread 02 – Data Engineering with Fabric"},"content":{"rendered":"

\"Microsoft<\/p>\n

Managing Files and Folders<\/h2>\n

What is a data lake? It is just a bunch of files organized by folders. Keeping these files organized prevents your data lake<\/font> from becoming a data swamp<\/font>. Today, we are going to learn about a python library that can help you.<\/p>\n

Business Problem<\/h4>\n

Our manager has given us weather data to load into Microsoft Fabric. We need to create folders in the landing zone to organize these files by both full and incremental loads. How can we accomplish this task?<\/p>\n

Technical Solution<\/h4>\n

This use case allows data engineers to learn how to programmatically manage files and folders with the Microsoft Spark Utilities library<\/a>. The following topics will be explored in this article (thread).<\/p>\n

    \n
  1. drop hive tables<\/li>\n
  2. remove folders<\/li>\n
  3. create folders<\/li>\n
  4. move files<\/li>\n
  5. copy files<\/li>\n
  6. write files<\/li>\n
  7. full load strategy<\/li>\n
  8. incremental load strategy<\/li>\n<\/ol>\n

    Architectural Overview<\/h4>\n

    The architectural diagram shows how folders are used to organize the data into quality zones. This is sometimes referred to as a medallion<\/a> lakehouse architecture.<\/p>\n

    \"Fabric<\/p>\n

    In practice, I have seen an additional quality zone called raw<\/b> be used to stage files in their native format before converting to a delta file format. Please note, the lake house uses either shortcuts or pipelines to get files into the lake. We will talk more about bronze, silver and gold zones when I cover full and incremental loading later in this article.<\/p>\n

    Data Lake Cleanup<\/h4>\n

    One great property of a data lake is the fact that it can always be torn down<\/b> from a current state and rebuilt<\/b> from the source files. We are going to remove tables and folders from the prior lesson at this time.<\/p>\n

    <\/p>\n

    The above image shows the PySpark notebook for lesson 2 loaded into the lake house explorer. The first two tasks involve the execution of Spark SQL to drop tables from the hive catalog.<\/p>\n

    \n%%sql\n--\n--  drop unmanaged (external) table\n--\n\ndrop table if exists unmanaged_weather;\n\n<\/pre>\n
    The above snippet drops the unmanage table while the below snippet drops the managed table.<\/p>\n
    \n%%sql\n--\n--  drop managed (internal) table\n--\n\ndrop table if exists managed_weather;\n\n<\/pre>\n
    The last task is to remove the folder named bronze.  We can use the file system remove command with the recursive flag equal to true.  I will be talk about the Microsoft Spark Utilities library in detail shortly.<\/p>\n
    \n#\n#  Remove bronze directory\n# \n\n# location\npath = \"Files\/bronze\"\n\n# remove folder\nmssparkutils.fs.rm(path, True)\n<\/pre>\n
    The image below show that both tables and one folder have been deleted.<\/p>\n
    <\/p>\n
    We are now ready to re-organize the raw (staging) folder (directory).<\/p>\n
    Managing Folders<\/h4>\n
    Microsoft has supplied the data lake engineer with the Spark Utilities library.  One can use the help method to list all the functions that can be called.  Please see image below for details on file system methods (functions).<\/p>\n
    <\/p>\n
    In this section, we are going to focus on the remove and create directory functions in this article.  We used the rm <\/b>function to delete the bronze<\/b> directory in the prior sections.<\/p>\n
    We want to organize and\/or create data in the following folders.<\/p>\n