{"id":644,"date":"2020-04-14T16:36:46","date_gmt":"2020-04-14T16:36:46","guid":{"rendered":"http:\/\/jackofalltradesmasterofsome.com\/blog\/?p=644"},"modified":"2020-04-14T17:27:43","modified_gmt":"2020-04-14T17:27:43","slug":"modern-data-architecture-part-9-loading-data-synapse-data-warehouse","status":"publish","type":"post","link":"http:\/\/jackofalltradesmasterofsome.com\/blog\/2020\/04\/14\/modern-data-architecture-part-9-loading-data-synapse-data-warehouse\/","title":{"rendered":"Modern Data Architecture \u2013 Part 9 &#8211; Load Data into Synapse Data Warehouse"},"content":{"rendered":"\n<p>Modern Data Architecture \u2013 Part 9 &#8211; Loading Data into Synapse Data Warehouse<\/p>\n\n\n\n<p>Now that we have provisioned a Synapses Data Analytics\nenvironment, we are now ready to begin loading data into this environment. In\nlab 7, we loaded a single table \u201csales.customer\u201d to our data lake. To complete\nthis lab, you will either need to complete the same exercise for the tables\nlisted below. To make it easier, we have included the csv files you can upload\ndirectly to the data lake raw folder as well in the Datafiles folder included\nwith this lab.<\/p>\n\n\n\n<p><strong>Tables<\/strong><\/p>\n\n\n\n<p>Sales.customers<\/p>\n\n\n\n<p>Sales.order_items<\/p>\n\n\n\n<p>Sales.orders<\/p>\n\n\n\n<p>Sales.staffs<\/p>\n\n\n\n<p>Sales.stores <\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"624\" height=\"148\" src=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-79.png\" alt=\"\" class=\"wp-image-733\" srcset=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-79.png 624w, http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-79-300x71.png 300w\" sizes=\"auto, (max-width: 624px) 100vw, 624px\" \/><\/figure>\n\n\n\n<ol class=\"wp-block-list\"><li>Open or navigate back to your Data Bricks environment<\/li><\/ol>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"624\" height=\"280\" src=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-80.png\" alt=\"\" class=\"wp-image-734\" srcset=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-80.png 624w, http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-80-300x135.png 300w\" sizes=\"auto, (max-width: 624px) 100vw, 624px\" \/><\/figure>\n\n\n\n<ul class=\"wp-block-list\"><li>We will be creating a similar script as we did in lab 7 but in reverse. We will connect to the data lake first and then load this data to our data warehouse.<\/li><li>Create a new work book called \u201cloading_customers\u201d<\/li><\/ul>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"624\" height=\"203\" src=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-81.png\" alt=\"\" class=\"wp-image-735\" srcset=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-81.png 624w, http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-81-300x98.png 300w\" sizes=\"auto, (max-width: 624px) 100vw, 624px\" \/><\/figure>\n\n\n\n<p><strong>Block 1<\/strong><\/p>\n\n\n\n<p><strong>Description \u2013<\/strong> This section\nconnects your data bricks storage to your data lake so that we can pull data from\nthe data lake in the next step. This is the quick and easy way to accomplish\nthis but your access keys are visible and shared in your code and not best\npractice. For production, be sure to use secret access keys.<\/p>\n\n\n\n<p>Insert your storage account name,\nthe destination folder and your access key respectively. <\/p>\n\n\n\n<p><strong>Code<\/strong><\/p>\n\n\n\n<p>%scala <\/p>\n\n\n\n<p>spark.conf.set(&#8220;fs.azure.account.key.trainingsavimal.dfs.core.windows.net&#8221;,&#8221;<em>youraccesskey<\/em>&#8220;)<\/p>\n\n\n\n<p>To access \u201c<em>youraccesskey<\/em>\u201d, we are going to need some additional information. From the azure portal, navigate back to your Storage Account \u201ctrainingsa<em>yourname<\/em>\u201d and select \u201cAccess Keys\u201d<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"360\" height=\"295\" src=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-82.png\" alt=\"\" class=\"wp-image-736\" srcset=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-82.png 360w, http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-82-300x246.png 300w\" sizes=\"auto, (max-width: 360px) 100vw, 360px\" \/><\/figure>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"623\" height=\"70\" src=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-83.png\" alt=\"\" class=\"wp-image-737\" srcset=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-83.png 623w, http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-83-300x34.png 300w\" sizes=\"auto, (max-width: 623px) 100vw, 623px\" \/><\/figure>\n\n\n\n<p><strong>Block 2<\/strong><\/p>\n\n\n\n<p><strong>Description \u2013<\/strong> This will\nstore your data to a data frame in data bricks memory. &nbsp;<\/p>\n\n\n\n<p><strong>Code<\/strong><\/p>\n\n\n\n<p>%scala<\/p>\n\n\n\n<p>val df = spark.read.option(&#8220;header&#8221;, &#8220;true&#8221;).csv(&#8220;abfss:\/\/root@trainingsavimal.dfs.core.windows.net\/raw\/customers.csv&#8221;)   <\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"624\" height=\"210\" src=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-84.png\" alt=\"\" class=\"wp-image-738\" srcset=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-84.png 624w, http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-84-300x101.png 300w\" sizes=\"auto, (max-width: 624px) 100vw, 624px\" \/><\/figure>\n\n\n\n<p><strong>Block 3<\/strong><\/p>\n\n\n\n<p><strong>Description \u2013<\/strong> This will\ndisplay the contents of what has now been stored into the dataframe. Code to\nmanipulte or transform the data can now be done if needed.<\/p>\n\n\n\n<p><strong>Code<\/strong><\/p>\n\n\n\n<p>%scala<\/p>\n\n\n\n<p>df.show()<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"624\" height=\"225\" src=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-85.png\" alt=\"\" class=\"wp-image-739\" srcset=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-85.png 624w, http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-85-300x108.png 300w\" sizes=\"auto, (max-width: 624px) 100vw, 624px\" \/><\/figure>\n\n\n\n<p><strong>Block 3<\/strong><\/p>\n\n\n\n<p><strong>Description \u2013<\/strong> Similar to\nbefore, we now connect to our SQL Server instance but this time we connect to\nour Synapses database instead of our SQL Server Database.<\/p>\n\n\n\n<p><strong>Code<\/strong><\/p>\n\n\n\n<p>%scala <\/p>\n\n\n\n<p>Class.forName(&#8220;com.microsoft.sqlserver.jdbc.SQLServerDriver&#8221;)<\/p>\n\n\n\n<p>val jdbcHostname = &#8220;training-sqlserver-vimal.database.windows.net&#8221;&nbsp; <\/p>\n\n\n\n<p>val jdbcPort = 1433&nbsp; <\/p>\n\n\n\n<p>val jdbcDatabase = &#8220;training_sqlpool_vimal&#8221;&nbsp; <\/p>\n\n\n\n<p>\/\/ Create the JDBC URL without\npassing in the user and password parameters.&nbsp;\n<\/p>\n\n\n\n<p>val jdbcUrl =\ns&#8221;jdbc:sqlserver:\/\/${jdbcHostname}:${jdbcPort};database=${jdbcDatabase}&#8221;&nbsp; <\/p>\n\n\n\n<p>import java.util.Properties&nbsp; <\/p>\n\n\n\n<p>val connectionProperties = new\nProperties()&nbsp; <\/p>\n\n\n\n<p>connectionProperties.put(&#8220;user&#8221;,\n&#8220;sqlserveradmin&#8221;)&nbsp; <\/p>\n\n\n\n<p>connectionProperties.put(&#8220;password&#8221;,\n&#8220;Password1234&#8221;)<\/p>\n\n\n\n<p>val driverClass =\n&#8220;com.microsoft.sqlserver.jdbc.SQLServerDriver&#8221;&nbsp; <\/p>\n\n\n\n<p>connectionProperties.setProperty(&#8220;Driver&#8221;, driverClass)<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"624\" height=\"352\" src=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-86.png\" alt=\"\" class=\"wp-image-740\" srcset=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-86.png 624w, http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-86-300x169.png 300w\" sizes=\"auto, (max-width: 624px) 100vw, 624px\" \/><\/figure>\n\n\n\n<p><strong>Block 4<\/strong><\/p>\n\n\n\n<p><strong>Description \u2013<\/strong> We can now\nwrite the contents of our data frame to our SQL Server Synapses database<\/p>\n\n\n\n<p><strong>Code<\/strong><\/p>\n\n\n\n<p>%scala <\/p>\n\n\n\n<p>df.write.mode(&#8220;append&#8221;).jdbc(jdbcUrl, &#8220;customers&#8221;, connectionProperties)<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"539\" height=\"155\" src=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-87.png\" alt=\"\" class=\"wp-image-741\" srcset=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-87.png 539w, http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-87-300x86.png 300w\" sizes=\"auto, (max-width: 539px) 100vw, 539px\" \/><\/figure>\n\n\n\n<ul class=\"wp-block-list\"><li>Heading back to your SQL Server Management Studio and query the table customers. You should now see data available in this table<\/li><\/ul>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"624\" height=\"350\" src=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-88.png\" alt=\"\" class=\"wp-image-742\" srcset=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-88.png 624w, http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2020\/04\/image-88-300x168.png 300w\" sizes=\"auto, (max-width: 624px) 100vw, 624px\" \/><\/figure>\n\n\n\n<p> Be sure to check out my full online class on the topic. A hands on walk through of a Modern Data Architecture using Microsoft Azure. For beginners and experienced business intelligence experts alike, learn the basic of navigating the Azure Portal to building an end to end solution of a modern data warehouse using popular technologies such as SQL\u00a0Database, Data Lake, Data Factory, Data Bricks, Azure Synapse Data Warehouse and Power BI. Link to the class can be <a href=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/2020\/04\/08\/modern-data-architecture-using-microsoft-azure-online-class-and-free-ebook\/\">found here<\/a> or directly <a href=\"https:\/\/www.udemy.com\/course\/modern-data-architecture-using-microsoft-azure\/learn\/lecture\/18527998#overview\">here<\/a>.   <\/p>\n\n\n<p><br>\n<br>\n<!--StartFragment--><\/p>\n\n\n<p><a href=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/2020\/04\/08\/modern-data-architecture-part-1-navigating-the-azure-portal\/\">Part 1 &#8211; Navigating the Azure Portal<\/a><\/p>\n\n\n\n<p><a href=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/2020\/04\/13\/modern-data-architecture-part-2-resource-groups-and-subscriptions\/\">Part 2 &#8211; Resource Groups and Subscriptions<\/a><\/p>\n\n\n\n<p><a href=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/2020\/04\/13\/modern-data-architecture-part-3-creating-data-lake-storage\/\">Part 3 &#8211; Creating Data Lake Storage<\/a><\/p>\n\n\n\n<p><a href=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/2020\/04\/13\/modern-data-architecture-part-4-setting-up-a-sql-server\/\">Part 4 &#8211; Setting up an Azure SQL Server<\/a><\/p>\n\n\n\n<p><a href=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/2020\/04\/13\/modern-data-architecture-part-5-loading-data-lake-with-data-factory\/\">Part 5 &#8211; Loading Data Lake with Azure Data Factory<\/a><\/p>\n\n\n\n<p><a href=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/2020\/04\/13\/modern-data-architecture-part-6-configuring-and-setting-up-data-bricks\/\">Part 6 &#8211; Configuring and Setting up Data Bricks<\/a><\/p>\n\n\n\n<p><a href=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/2020\/04\/13\/modern-data-architecture-part-7-staging-data-into-data-lake\/\">Part 7 &#8211; Staging data into Data Lake<\/a><\/p>\n\n\n\n<p><a href=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/2020\/04\/14\/modern-data-architecture-part-8-provisioning-a-synapsis-sql-data-warehouse\/\">Part 8 = Provisioning a Synapse SQL Data Warehouse<\/a><\/p>\n\n\n\n<p><a href=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/2020\/04\/14\/modern-data-architecture-part-9-loading-data-synapse-data-warehouse\/\">Part 9 &#8211; Loading Data into Azure Data Synapse Data Warehouse<\/a><\/p>\n\n\n<p><!--EndFragment--><br>\n<br>\n<\/p>\n\n\n<p> Modern Data Architecture \u2013 Part 9 &#8211; Loading Data into Synapse Data Warehouse <\/p>\n","protected":false},"excerpt":{"rendered":"<p>Modern Data Architecture \u2013 Part 9 &#8211; Loading Data into Synapse Data Warehouse Now that we have provisioned a Synapses Data Analytics environment, we are now ready to begin loading data into this environment. In lab 7, we loaded a single table \u201csales.customer\u201d to our data lake. To complete this lab, you will either need [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":624,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4,28,27,11,85],"tags":[],"class_list":["post-644","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-analytics","category-azure","category-big-data","category-data-warehouse","category-sql-server"],"_links":{"self":[{"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/posts\/644","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/comments?post=644"}],"version-history":[{"count":5,"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/posts\/644\/revisions"}],"predecessor-version":[{"id":772,"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/posts\/644\/revisions\/772"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/media\/624"}],"wp:attachment":[{"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/media?parent=644"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/categories?post=644"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/tags?post=644"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}