{"id":640,"date":"2020-04-13T19:33:28","date_gmt":"2020-04-13T19:33:28","guid":{"rendered":"http:\/\/jackofalltradesmasterofsome.com\/blog\/?p=640"},"modified":"2020-04-14T17:27:05","modified_gmt":"2020-04-14T17:27:05","slug":"modern-data-architecture-part-7-staging-data-into-data-lake","status":"publish","type":"post","link":"https:\/\/jackofalltradesmasterofsome.com\/blog\/2020\/04\/13\/modern-data-architecture-part-7-staging-data-into-data-lake\/","title":{"rendered":"Modern Data Architecture \u2013 Part 7 \u2013 Staging Data into Data Lake"},"content":{"rendered":"\n

Modern Data Architecture \u2013 Part 7 \u2013 Staging Data into Data Lake<\/p>\n\n\n\n

In Lab 5, we demonstrated loaded data from our sample SQL server into our data lake. Although this tool is very handy, it creates a new service and set of tools that need to be provisioned and monitored. It is best to use ADF more as an workflow orchestration tool and use a single tool to handle all of your ETL. Data Brinks provides a robust tool to handle this task. We will investigate how to query data from your SQL Server and load your data lake. <\/p>\n\n\n\n

For this lab we\nwill be hard coding the access keys and credentials directly into the code. The\noptional section at the bottom will demonstrate how to configure Azure\ncredentials secret keys to configure this the correct way as you would in\nproduction. <\/p>\n\n\n\n

Connect and Query SQL Server<\/h3>\n\n\n\n
  1. Create a new workbook in your Data Bricks and name it \u201cstaging_customers\u201d<\/li><\/ol>\n\n\n\n
    \"\"<\/figure>\n\n\n\n

    Copy the Query below and paste it\ninto your Data Bricks Window. Replace the highlighted sections with your\ndatabase information. Most of our code will be writing using Scala to start. We\nwill place each piece of code into its own cells to make the job a bit easer to\nread and test. Copy and paste the following code into your notebook. We will be\nusing hard coded credentials and keys for the lab but in real world, be sure to\nconfigure secret key access only.<\/p>\n\n\n\n

    Example<\/strong><\/p>\n\n\n\n

    \"\"<\/figure>\n\n\n\n

    Block 1<\/strong> <\/p>\n\n\n\n

    Description –<\/strong> This sets the\ninitial driver for our database connection<\/p>\n\n\n\n

    Code<\/strong><\/p>\n\n\n\n

    %scala <\/p>\n\n\n\n

    Class.forName(“com.microsoft.sqlserver.jdbc.SQLServerDriver”)<\/p>\n\n\n\n

    \"\"<\/figure>\n\n\n\n

    Block 2<\/strong><\/p>\n\n\n\n

    Description \u2013<\/strong> We configure\nand test the connection. Replace the items highlighted below with you SQL\nServer name, database name, username and password respectively. <\/p>\n\n\n\n

    Code<\/strong><\/p>\n\n\n\n

    %scala <\/p>\n\n\n\n

    val jdbcHostname = “training-sqlserver-vimal.database.windows.net”  <\/p>\n\n\n\n

    val jdbcPort = 1433  <\/p>\n\n\n\n

    val jdbcDatabase = “training_sqldatabase_vimal”  <\/p>\n\n\n\n

    \/\/ Create the JDBC URL without\npassing in the user and password parameters. \n<\/p>\n\n\n\n

    val jdbcUrl =\ns”jdbc:sqlserver:\/\/${jdbcHostname}:${jdbcPort};database=${jdbcDatabase}”  <\/p>\n\n\n\n

    import java.util.Properties  <\/p>\n\n\n\n

    val connectionProperties = new\nProperties()  <\/p>\n\n\n\n

    connectionProperties.put(“user”,\n“sqlserveradmin”)  <\/p>\n\n\n\n

    connectionProperties.put(“password”,\n“Password123”)<\/p>\n\n\n\n

    val driverClass =\n“com.microsoft.sqlserver.jdbc.SQLServerDriver”  <\/p>\n\n\n\n

    connectionProperties.setProperty(“Driver”, driverClass)<\/p>\n\n\n\n

    \"\"<\/figure>\n\n\n\n

    Block 4<\/strong><\/p>\n\n\n\n

    Description \u2013<\/strong> This allows\nus to review and test the connection to our sales.customers table in the\ndatabase<\/p>\n\n\n\n

    Code<\/strong><\/p>\n\n\n\n

    %scala <\/p>\n\n\n\n

    val customer = spark.read.jdbc(jdbcUrl, “sales.customers”, connectionProperties)<\/p>\n\n\n\n

    \"\"<\/figure>\n\n\n\n

    Block 5<\/strong><\/p>\n\n\n\n

    Description \u2013<\/strong> Select the\ndata from the table for display<\/p>\n\n\n\n

    Code<\/strong><\/p>\n\n\n\n

    %scala <\/p>\n\n\n\n

    customer.select(“*”).show()<\/p>\n\n\n\n

    \"\"<\/figure>\n\n\n\n

    Block 6<\/strong><\/p>\n\n\n\n

    Description \u2013<\/strong> This section\nconnects your data bricks storage to your data lake so that we can push data to\nthe data lake in the next step.  Insert\nyour blob name, your storage account name, the destionation folder, the\ndestination folder, the storage account name and your access key respectively. <\/p>\n\n\n\n

    Code<\/strong><\/p>\n\n\n\n

    %scala <\/p>\n\n\n\n

    dbutils.fs.mount(  <\/p>\n\n\n\n

    source = “wasbs:\/\/root@trainingsavimal.blob.core.windows.net\/raw”,  <\/p>\n\n\n\n

    mountPoint = “\/mnt\/raw”,  <\/p>\n\n\n\n

    extraConfigs =\nMap(“fs.azure.account.key.trainingsavimal.blob.core.windows.net” -> “ youraccesskey<\/em>\n“))<\/p>\n\n\n\n

    To access \u201cyouraccesskey<\/em>\u201d, we are going to need some additional information. From the azure portal, navigate back to your Storage Account \u201ctrainingsayourname<\/em>\u201d and select \u201cAccess Keys\u201d<\/p>\n\n\n\n

    \"\"

    <\/figcaption><\/figure>\n\n\n\n
    \"\"<\/figure>\n\n\n\n

    Block 7<\/strong><\/p>\n\n\n\n

    Description \u2013<\/strong> This last\nsection pushed your data to a csv on to the data lake. Upon completion, you can\nnow see you new data file on your storage via the storage explorer. There will\nalso be a folder containing some of the data tranfer metadata.<\/p>\n\n\n\n

    Code<\/strong><\/p>\n\n\n\n

    %scala <\/p>\n\n\n\n

    customer.coalesce(1)  <\/p>\n\n\n\n

    .write.format(“com.databricks.spark.csv”)  <\/p>\n\n\n\n

    .option(“header”,\n“true”)  <\/p>\n\n\n\n

    .save(“\/mnt\/raw\/customers.csv”)<\/p>\n\n\n\n

    \"\"<\/figure>\n\n\n\n

    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 found here<\/a> or directly here<\/a>. <\/p>\n\n\n


    \n
    \n<\/p>\n\n\n

    Part 1 – Navigating the Azure Portal<\/a><\/p>\n\n\n\n

    Part 2 – Resource Groups and Subscriptions<\/a><\/p>\n\n\n\n

    Part 3 – Creating Data Lake Storage<\/a><\/p>\n\n\n\n

    Part 4 – Setting up an Azure SQL Server<\/a><\/p>\n\n\n\n

    Part 5 – Loading Data Lake with Azure Data Factory<\/a><\/p>\n\n\n\n

    Part 6 – Configuring and Setting up Data Bricks<\/a><\/p>\n\n\n\n

    Part 7 – Staging data into Data Lake<\/a><\/p>\n\n\n\n

    Part 8 = Provisioning a Synapse SQL Data Warehouse<\/a><\/p>\n\n\n\n

    Part 9 – Loading Data into Azure Data Synapse Data Warehouse<\/a><\/p>\n\n\n


    \n
    \n<\/p>\n\n\n

    Modern Data Architecture \u2013 Part 7 \u2013 Staging Data into Data Lake<\/p>\n","protected":false},"excerpt":{"rendered":"

    Modern Data Architecture \u2013 Part 7 \u2013 Staging Data into Data Lake In Lab 5, we demonstrated loaded data from our sample SQL server into our data lake. Although this tool is very handy, it creates a new service and set of tools that need to be provisioned and monitored. It is best to use […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4,28,27,11,85],"tags":[],"class_list":["post-640","post","type-post","status-publish","format-standard","hentry","category-analytics","category-azure","category-big-data","category-data-warehouse","category-sql-server"],"_links":{"self":[{"href":"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/posts\/640","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/comments?post=640"}],"version-history":[{"count":6,"href":"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/posts\/640\/revisions"}],"predecessor-version":[{"id":770,"href":"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/posts\/640\/revisions\/770"}],"wp:attachment":[{"href":"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/media?parent=640"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/categories?post=640"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/tags?post=640"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}