{"id":98274,"date":"2023-09-13T17:00:02","date_gmt":"2023-09-13T17:00:02","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=98274"},"modified":"2024-09-03T20:15:18","modified_gmt":"2024-09-03T20:15:18","slug":"microsoft-fabric-using-notebooks-and-table-partitioning-to-convert-files-to-tables","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/microsoft-fabric-using-notebooks-and-table-partitioning-to-convert-files-to-tables\/","title":{"rendered":"Microsoft Fabric: using Notebooks and Table Partitioning to Convert Files to Tables"},"content":{"rendered":"

When Microsoft Fabric was born, the only method to convert files to tables was using notebooks. Nowadays we have an easy-to-use UI feature for the conversion.<\/p>\n

As I explained on the article about lakehouse and ETL<\/a>, there are some scenarios where we still need to use notebooks for the conversion. One of these scenarios is when we need table partitioning.<\/p>\n

Let\u2019s make a step-by-step on this blog about how to use notebooks and table partitioning.<\/p>\n

The steps explained here are based on the same sample data from the lakehouse and ETL article<\/a>. In that article you can find the steps to build a pipeline to import the data to the files area.<\/p>\n

The Starting Point<\/h2>\n

The starting point for this example is a lakehouse<\/strong> called demolake<\/em> with the files already imported to the Files<\/strong> area using a pipeline.<\/p>\n

No table is created yet.<\/p>\n

Loading a Notebook<\/h2>\n

We will use a notebook for the conversion. You can download the notebook here<\/a>. We will use the notebook called \u201c01 – Create Delta Tables.ipynb\u201d<\/em><\/p>\n

    \n
  1. Click the Experience<\/em> button and select the Data Engineering Experience<\/em><\/li>\n
  2. Click the Import Notebook<\/em> button.<\/li>\n<\/ol>\n

    \"Interface<\/p>\n

      \n
    1. Click the Upload<\/em> button and select the notebook you just downloaded.<\/li>\n<\/ol>\n

      \"Interface<\/p>\n

        \n
      1. Click the button lakehouse demo<\/em> in the left button bar to return to the workspace.<\/li>\n<\/ol>\n

        \"Interface<\/p>\n

          \n
        1. Click on the notebook you imported.<\/li>\n
        2. On the Lakehouse explorer<\/em> window, click the Add<\/em> button to link the notebook to a lakehouse.<\/li>\n<\/ol>\n

          \"Interface<\/p>\n

            \n
          1. On the Add Lakehouse<\/em> window, select Existing Lakehouse<\/em> and click the Add<\/em> button.<\/li>\n<\/ol>\n

            \"Interface<\/p>\n

              \n
            1. Select the demolake and click the Add<\/em> button.<\/li>\n<\/ol>\n

              \"Interface<\/p>\n

              The notebook code<\/h2>\n

              Let\u2019s analyse the code in the notebook we just imported.<\/p>\n

              First Code Block<\/h3>\n

              <\/p>\n

              \n
              # Copyright (c) Microsoft Corporation.<\/span>\r\n# Licensed under the MIT License.<\/span>\r\n\r\nspark.<\/span>conf.<\/span>set(\"sprk.sql.parquet.vorder.enabled\"<\/span>, \"true\"<\/span>)\r\n\r\nspark.<\/span>conf.<\/span>set(\"spark.microsoft.delta.optimizeWrite.enabled\"<\/span>, \"true\"<\/span>)\r\n\r\nspark.<\/span>conf.<\/span>set(\"spark.microsoft.delta.optimizeWrite.binSize\"<\/span>, \"1073741824\"<\/span>)\r\n<\/pre>\n<\/div>\n
              This code block enables the V-Order<\/strong> optimization. This is a special optimization for delta tables. You can read more about the V-Order<\/strong> optimization on this link: https:\/\/learn.microsoft.com\/en-us\/fabric\/data-engineering\/delta-optimization-and-v-order?tabs=sparksql<\/a><\/p>\n
              The optimizeWrite<\/strong> option allow us to define what’s called binSize<\/strong>. This configuration defines the size for each parquet file used to store the data. Controlling the size of the files we can avoid too big files or too small files, either of these cases would cause a performance problem.<\/p>\n
              I would not be surprised if in the future some of these configurations may become a default.<\/p>\n
              Second Code Block<\/h3>\n
              <\/p>\n
              \n
              from<\/span> pyspark.sql.functions<\/span> import<\/span> col, year, month, quarter\r\n\r\ntable_name =<\/span> 'fact_sale'<\/span>\r\n\r\ndf =<\/span> spark.<\/span>read.<\/span>format(\"parquet\"<\/span>).<\/span>load('Files\/fact_sale_1y_full'<\/span>)\r\n\r\ndf =<\/span> df.<\/span>withColumn('Year'<\/span>, year(col(\"InvoiceDateKey\"<\/span>)))\r\n\r\ndf =<\/span> df.<\/span>withColumn('Quarter'<\/span>, quarter(col(\"InvoiceDateKey\"<\/span>)))\r\n\r\ndf =<\/span> df.<\/span>withColumn('Month'<\/span>, month(col(\"InvoiceDateKey\"<\/span>)))\r\n\r\ndf.<\/span>write.<\/span>mode(\"overwrite\"<\/span>).<\/span>format(\"delta\"<\/span>).<\/span>partitionBy(\"Year\"<\/span>,\"Quarter\"<\/span>).<\/span>save(\"Tables\/\"<\/span> +<\/span> table_name)\r\n<\/pre>\n<\/div>\n
              This block executes the following tasks:<\/p>\n
                \n
              • Read the fact_sale <\/em>table from the Files<\/strong> folder.<\/li>\n
              • Create a column called Year.<\/em><\/li>\n
              • Create a column called Quarter<\/em>.<\/li>\n
              • Create a column called Month<\/em>.<\/li>\n
              • Save the data in the Tables<\/strong> area. The table is partitioned by Year<\/em> and Quarter,<\/em> and it will overwrite any existing table with the same name.<\/li>\n<\/ul>\n
                Third Code Block<\/h3>\n
                <\/p>\n
                \n
                from<\/span> pyspark.sql.types<\/span> import<\/span> *<\/span>\r\ndef<\/span> loadFullDataFromSource<\/span>(table_name):\r\n    df =<\/span> spark.<\/span>read.<\/span>format(\"parquet\"<\/span>).<\/span>load('Files\/'<\/span> +<\/span> table_name)\r\n    df.<\/span>write.<\/span>mode(\"overwrite\"<\/span>).<\/span>format(\"delta\"<\/span>).<\/span>save(\"Tables\/\"<\/span> +<\/span> table_name)\r\n\r\nfull_tables =<\/span> [\r\n\r\n'dimension_city'<\/span>,\r\n'dimension_date'<\/span>,\r\n'dimension_employee'<\/span>,\r\n'dimension_stock_item'<\/span>\r\n ]\r\n\r\nfor<\/span> table in<\/span> full_tables:\r\n    loadFullDataFromSource(table)\r\n<\/pre>\n<\/div>\n
                This block has a function called loadFullDataFromSource<\/em>. The function is defined in the beginning of the code, but it will only be executed when called.<\/p>\n
                The execution itself starts on the full_tables<\/em> variable definition. This variable is an array with the name of the dimensions, which are also the name of the folders under the Files<\/strong> area in the lakehouse.<\/p>\n
                A FOR<\/strong> loop is executed over the table variable. For each dimension, the function loadFullDataFromSource<\/em> is called receiving the name of the dimension as a parameter.<\/p>\n
                The function loads the data from the Files<\/strong> area and saves it to the Tables<\/strong> area using the table name as parameter. As a result, the function will load all the dimensions, one by one.<\/p>\n
                Executing the Notebook and checking the result<\/h2>\n
                  \n
                1. Click the Run All button to execute all the code blocks in the notebook.<\/li>\n<\/ol>\n
                  Microsoft Fabric<\/strong> uses a feature called Live Pool<\/strong>. We don’t need to configure a spark pool for the notebook execution. Every workspace linked with Fabric<\/strong> artifacts has the Live Pool<\/strong> enabled. Once we ask to execute a notebook or a single code block, the pool is enabled in very few seconds.<\/p>\n
                    \n
                  1. On the lakehouse explorer, right click Tables<\/strong> and click Refresh<\/em>.<\/li>\n<\/ol>\n
                    \"Interface<\/p>\n
                      \n
                    1. The tables are now available in the Tables<\/strong> area.<\/li>\n<\/ol>\n
                      \"Interface<\/p>\n
                        \n
                      1. On the left bar, click the demolake button.<\/li>\n<\/ol>\n
                        Let’s look on the storage behind the tables in the lakehouse.<\/p>\n
                          \n
                        1. Right click the fact_sale<\/em> table<\/li>\n
                        2. On the context menu, click View files menu item.<\/li>\n<\/ol>\n
                          \"Interface<\/p>\n
                          Mind how the files are stored in delta format and partitioned by Year<\/em> and Quarter<\/em> according to the configuration defined in the notebook.<\/p>\n
                          \"Interface<\/p>\n
                            \n
                          1. Click the Year<\/em> folder.<\/li>\n<\/ol>\n
                            \"Interface<\/p>\n
                              \n
                            1. Click one of the Quarters<\/em> folder.<\/li>\n<\/ol>\n
                              \"Interface<\/p>\n
                              Conclusion<\/h2>\n
                              On this blog you discovered how you can make the transformation from Files<\/strong> to Tables<\/strong> in a lakehouse<\/strong> using pyspark<\/strong> code and in this way be able to partition tables and schedule the code to make this movement.<\/p>\n","protected":false},"excerpt":{"rendered":"
                              When Microsoft Fabric was born, the only method to convert files to tables was using notebooks. Nowadays we have an easy-to-use UI feature for the conversion. As I explained on the article about lakehouse and ETL, there are some scenarios where we still need to use notebooks for the conversion. One of these scenarios is……<\/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,159166],"tags":[123646,123648,158998,158997,159035,101611],"coauthors":[6810],"class_list":["post-98274","post","type-post","status-publish","format-standard","hentry","category-blogs","category-microsoft-fabric","category-powerbi","tag-data-lake","tag-data-platform","tag-lakehouse","tag-microsoft-fabric","tag-notebook","tag-power-bi"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/98274","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=98274"}],"version-history":[{"count":3,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/98274\/revisions"}],"predecessor-version":[{"id":98354,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/98274\/revisions\/98354"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=98274"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=98274"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=98274"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=98274"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}