{"id":96925,"date":"2023-05-24T10:10:22","date_gmt":"2023-05-24T10:10:22","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=96925"},"modified":"2024-09-03T20:15:19","modified_gmt":"2024-09-03T20:15:19","slug":"microsoft-fabric-data-warehouse-ingestion-using-t-sql-and-more","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/microsoft-fabric-data-warehouse-ingestion-using-t-sql-and-more\/","title":{"rendered":"Microsoft Fabric: Data Warehouse Ingestion using T-SQL and more"},"content":{"rendered":"

.In this blog I will illustrate how we can ingest data from a blob storage to a Microsoft Fabric<\/strong> Data Warehouse using T-SQL.<\/p>\n

Create a new workspace<\/h2>\n
    \n
  1. On the left ribbon, click Workspaces.<\/em><\/li>\n
  2. Click the New Workspace<\/em> button.<\/li>\n<\/ol>\n

    \"Interface<\/p>\n

      \n
    1. Provide a name for the workspace.<\/li>\n<\/ol>\n

      \"Tabela\n\nDescri\u00e7\u00e3o<\/p>\n

        \n
      1. On advanced configuration, ensure you choose Premium by Capacity,<\/em> and you need to select a capacity.<\/li>\n<\/ol>\n

        \"Interface<\/p>\n

          \n
        1. Click the Apply<\/em> button.<\/li>\n<\/ol>\n

          \"Interface<\/p>\n

          Creating the Microsoft Fabric Data Warehouse<\/h2>\n
            \n
          1. On the left ribbon, Experiences<\/em> button, click the Data Warehouse<\/em> experience.<\/li>\n<\/ol>\n

            \"Interface<\/p>\n

              \n
            1. Click the Warehouse<\/em> button to create a new Warehouse\"Interface<\/li>\n
            2. Define the name wwiSample<\/em> for the Data Warehouse and click Create.<\/em><\/li>\n<\/ol>\n

              \"Interface<\/p>\n

              \"Interface<\/p>\n

              Creating the schema to import the data<\/h2>\n
                \n
              1. Click the New SQL query<\/em> button.<\/li>\n<\/ol>\n

                \u00a0–dimension_city<\/i><\/span>
                \nDROP<\/span>\u00a0TABLE<\/span>IF<\/span>\u00a0EXISTS<\/span>\u00a0[dbo]<\/span>.<\/span>[dimension_city]<\/span>;<\/span><\/span><\/p>\n

                CREATE<\/span>\u00a0TABLE<\/span>\u00a0[dbo]<\/span>.<\/span>[dimension_city]<\/span>
                \n(<\/span>
                \n[citykey]<\/span>\u00a0[INT]<\/i><\/span>\u00a0NULL<\/span>,<\/span>
                \n[wwicityid]<\/span>\u00a0[INT]<\/i><\/span>\u00a0NULL<\/span>,<\/span>
                \n[city]<\/span>\u00a0[VARCHAR]<\/i><\/span>(<\/span>8000<\/span>)<\/span>\u00a0NULL<\/span>,<\/span>
                \n[stateprovince]<\/span>\u00a0[VARCHAR]<\/i><\/span>(<\/span>8000<\/span>)<\/span>\u00a0NULL<\/span>,<\/span>
                \n[country]<\/span>\u00a0[VARCHAR]<\/i><\/span>(<\/span>8000<\/span>)<\/span>\u00a0NULL<\/span>,<\/span>
                \n[continent]<\/span>\u00a0[VARCHAR]<\/i><\/span>(<\/span>8000<\/span>)<\/span>\u00a0NULL<\/span>,<\/span>
                \n[salesterritory]<\/span>\u00a0[VARCHAR]<\/i><\/span>(<\/span>8000<\/span>)<\/span>\u00a0NULL<\/span>,<\/span>
                \n[region]<\/span>\u00a0[VARCHAR]<\/i><\/span>(<\/span>8000<\/span>)<\/span>\u00a0NULL<\/span>,<\/span>
                \n[subregion]<\/span>\u00a0[VARCHAR]<\/i><\/span>(<\/span>8000<\/span>)<\/span>\u00a0NULL<\/span>,<\/span>
                \n[location]<\/span>\u00a0[VARCHAR]<\/i><\/span>(<\/span>8000<\/span>)<\/span>\u00a0NULL<\/span>,<\/span>
                \n[latestrecordedpopulation]<\/span>\u00a0[BIGINT]<\/i><\/span>\u00a0NULL<\/span>,<\/span>
                \n[validfrom]<\/span>\u00a0[DATETIME2]<\/i><\/span>(<\/span>6<\/span>)<\/span>\u00a0NULL<\/span>,<\/span>
                \n[validto]<\/span>\u00a0[DATETIME2]<\/i><\/span>(<\/span>6<\/span>)<\/span>\u00a0NULL<\/span>,<\/span>
                \n[lineagekey]<\/span>\u00a0[INT]<\/i><\/span>\u00a0NULL<\/span>
                \n)<\/span>;<\/span> <\/span><\/p>\n

                –fact_sale<\/i><\/span><\/p>\n

                DROP<\/span>\u00a0TABLE<\/span>IF<\/span>\u00a0EXISTS<\/span>\u00a0[dbo]<\/span>.<\/span>[fact_sale]<\/span>;<\/span><\/span><\/p>\n

                CREATE<\/span>\u00a0TABLE<\/span>\u00a0[dbo]<\/span>.<\/span>[fact_sale]<\/span>
                \n(<\/span>
                \n[salekey]<\/span>\u00a0[BIGINT]<\/i><\/span>\u00a0NULL<\/span>,<\/span>
                \n[citykey]<\/span>\u00a0[INT]<\/i><\/span>\u00a0NULL<\/span>,<\/span>
                \n[customerkey]<\/span>\u00a0[INT]<\/i><\/span>\u00a0NULL<\/span>,<\/span>
                \n[billtocustomerkey]<\/span>\u00a0[INT]<\/i><\/span>\u00a0NULL<\/span>,<\/span>
                \n[stockitemkey]<\/span>\u00a0[INT]<\/i><\/span>\u00a0NULL<\/span>,<\/span>
                \n[invoicedatekey]<\/span>\u00a0[DATETIME2]<\/i><\/span>(<\/span>6<\/span>)<\/span>\u00a0NULL<\/span>,<\/span>
                \n[deliverydatekey]<\/span>\u00a0[DATETIME2]<\/i><\/span>(<\/span>6<\/span>)<\/span>\u00a0NULL<\/span>,<\/span>
                \n[salespersonkey]<\/span>\u00a0[INT]<\/i><\/span>\u00a0NULL<\/span>,<\/span>
                \n[wwiinvoiceid]<\/span>\u00a0[INT]<\/i><\/span>\u00a0NULL<\/span>,<\/span>
                \n[description]<\/span>\u00a0[VARCHAR]<\/i><\/span>(<\/span>8000<\/span>)<\/span>\u00a0NULL<\/span>,<\/span>
                \n[package]<\/span>\u00a0[VARCHAR]<\/i><\/span>(<\/span>8000<\/span>)<\/span>\u00a0NULL<\/span>,<\/span>
                \n[quantity]<\/span>\u00a0[INT]<\/i><\/span>\u00a0NULL<\/span>,<\/span>
                \n[unitprice]<\/span>\u00a0[DECIMAL]<\/i><\/span>(<\/span>18<\/span>,<\/span>\u00a02<\/span>)<\/span>\u00a0NULL<\/span>,<\/span>
                \n[taxrate]<\/span>\u00a0[DECIMAL]<\/i><\/span>(<\/span>18<\/span>,<\/span>\u00a03<\/span>)<\/span>\u00a0NULL<\/span>,<\/span>
                \n[totalexcludingtax]<\/span>\u00a0[DECIMAL]<\/i><\/span>(<\/span>29<\/span>,<\/span>\u00a02<\/span>)<\/span>\u00a0NULL<\/span>,<\/span>
                \n[taxamount]<\/span>\u00a0[DECIMAL]<\/i><\/span>(<\/span>38<\/span>,<\/span>\u00a06<\/span>)<\/span>\u00a0NULL<\/span>,<\/span>
                \n[profit]<\/span>\u00a0[DECIMAL]<\/i><\/span>(<\/span>18<\/span>,<\/span>\u00a02<\/span>)<\/span>\u00a0NULL<\/span>,<\/span>
                \n[totalincludingtax]<\/span>\u00a0[DECIMAL]<\/i><\/span>(<\/span>38<\/span>,<\/span>\u00a06<\/span>)<\/span>\u00a0NULL<\/span>,<\/span>
                \n[totaldryitems]<\/span>\u00a0[INT]<\/i><\/span>\u00a0NULL<\/span>,<\/span>
                \n[totalchilleritems]<\/span>\u00a0[INT]<\/i><\/span>\u00a0NULL<\/span>,<\/span>
                \n[lineagekey]<\/span>\u00a0[INT]<\/i><\/span>\u00a0NULL<\/span>,<\/span>
                \n[month]<\/span>\u00a0[INT]<\/i><\/span>\u00a0NULL<\/span>,<\/span>
                \n[year]<\/span>\u00a0[INT]<\/i><\/span>\u00a0NULL<\/span>,<\/span>
                \n[quarter]<\/span>\u00a0[INT]<\/i><\/span>\u00a0NULL<\/span>
                \n)<\/span>;<\/span> <\/span><\/p>\n

                  \n
                1. Click the Run<\/em> button.<\/li>\n<\/ol>\n

                  The Data Warehouse has full DDL support to prepare tables for data ingestion or create table aggregations.<\/p>\n

                    \n
                  1. On the Explorer, open Schemas->dbo->Tables<\/em><\/li>\n
                  2. Click the three dots on the right and select Refresh.<\/em><\/li>\n<\/ol>\n

                    \"Interface<\/p>\n

                    \"Interface<\/p>\n

                      \n
                    1. Double click the query in the top of the window and give it a new name, Create Tables<\/em><\/li>\n<\/ol>\n

                      \"Interface<\/p>\n

                        \n
                      1. You will be able to see your query on the left side, in the Explorer.<\/em><\/li>\n<\/ol>\n

                        \"Interface<\/p>\n

                        The Queries area in the explorer is like the queries in datamarts, but in the Data Warehouse we have the Shared Queries<\/em>, allowing us to create a distinction between our personal queries and queries shared with other users.<\/p>\n

                        You can move a query to Shared Queries<\/em> by clicking the 3 dots and selecting the option Move to Shared Queries<\/em><\/p>\n

                        \"Interface<\/p>\n

                        Ingesting the data<\/h2>\n

                        We can use the COPY statement to ingest the data from an Azure Blog Storage to the data warehouse.<\/p>\n

                        This is the COPY statement syntax:<\/p>\n

                        \"Texto\n\nDescri\u00e7\u00e3o<\/p>\n

                        The copy statement is capable of:<\/p>\n