{"id":210,"date":"2018-12-17T15:10:04","date_gmt":"2018-12-17T15:10:04","guid":{"rendered":"http:\/\/jackofalltradesmasterofsome.com\/blog\/?p=210"},"modified":"2020-04-15T14:21:14","modified_gmt":"2020-04-15T14:21:14","slug":"the-modern-data-warehouse-azure-data-lake-and-u-sql-to-combine-data","status":"publish","type":"post","link":"http:\/\/jackofalltradesmasterofsome.com\/blog\/2018\/12\/17\/the-modern-data-warehouse-azure-data-lake-and-u-sql-to-combine-data\/","title":{"rendered":"The Modern Data Warehouse; Azure Data Lake and U-SQL to combine data"},"content":{"rendered":"\n

The modern data warehouse will need to use Azure Data Lake and U-SQL to combine data. Begin by navigating to your Azure Portal and searching for the Data Lake Analytics Resource. Let\u2019s start by creating a new Data Lake. Don\u2019t worry, this service only charges on data in and out, not just remaining on like an HDInsights cluster so you should not be charged anything and we will not need to spin up and spin down services like we did earlier. <\/p>\n\n\n\n\n\n\n\n

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

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

You will need to give it a unique name as well as tie it to a pay as you go subscription. A data lake will also need a data lake storage layer as well. You can keep the naming as is or rename it if you wish. It is recommended to keep the storage encrypted. Deployment may take a few minutes.<\/p>\n\n\n\n

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

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

Let\u2019s navigate to the data explore and create a new folder to put our sample data. Upload the same 3 files from the happiness index to the new folder. <\/p>\n\n\n\n

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

Take a look through your data explorer at the tables. It should contain a master database similar to as if you were running a traditional SQL Server.<\/p>\n\n\n\n

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

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

Create a new U-SQL Job that creates the new database in the\ncatalog as well as a schema and a table. <\/p>\n\n\n\n

CREATE DATABASE IF\nNOT EXISTS testdata;<\/p>\n\n\n\n

USE DATABASE testdata;<\/p>\n\n\n\n

CREATE SCHEMA IF NOT EXISTS happiness;<\/p>\n\n\n\n

CREATE TABLE happiness.placeholderdata<\/p>\n\n\n\n

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

    Region string,<\/p>\n\n\n\n

       HappinessRank float,<\/p>\n\n\n\n

       HappinessScore float,<\/p>\n\n\n\n

      \nLowerConfidenceInterval float,<\/p>\n\n\n\n

      \nUpperConfidenceInterval float,<\/p>\n\n\n\n

       Economy_GDPperCapita float,<\/p>\n\n\n\n

       Family float,<\/p>\n\n\n\n

       Health_LifeExpectancy\nfloat,<\/p>\n\n\n\n

      \nFreedomTrust_GovernmentCorruption \nfloat,<\/p>\n\n\n\n

       GenerosityDystopiaResidual\nfloat,<\/p>\n\n\n\n

     INDEX clx_Region CLUSTERED(Region ASC) DISTRIBUTED BY HASH(Region)<\/p>\n\n\n\n

);<\/p>\n\n\n\n

While running and once completed, Azure will present the execution\ntree. With the new table created, we can now load data into that table.<\/p>\n\n\n\n

USE DATABASE testdata;<\/p>\n\n\n\n

@log = <\/p>\n\n\n\n

EXTRACT Region string,<\/p>\n\n\n\n

       HappinessRank float,<\/p>\n\n\n\n

       HappinessScore float,<\/p>\n\n\n\n

      \nLowerConfidenceInterval float,<\/p>\n\n\n\n

      \nUpperConfidenceInterval float,<\/p>\n\n\n\n

       Economy_GDPperCapita float,<\/p>\n\n\n\n

       Family float,<\/p>\n\n\n\n

       Health_LifeExpectancy\nfloat,<\/p>\n\n\n\n

      \nFreedomTrust_GovernmentCorruption \nfloat,<\/p>\n\n\n\n

      \nGenerosityDystopiaResidual float<\/p>\n\n\n\n

FROM “\/sampledata\/{*}.csv”<\/p>\n\n\n\n

USING Extractors.Text(‘,’,silent:true);<\/p>\n\n\n\n

INSERT INTO happiness.placeholderdata<\/p>\n\n\n\n

SELECT * FROM @log;<\/p>\n\n\n\n

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

Once the data is loaded, we can go query the data in the data\nexplorer to see what it looks like. The script will run the select and output\nthe data to a file to be browsed. <\/p>\n\n\n\n

@table = SELECT * FROM [testdata].[happiness].[placeholderdata];<\/p>\n\n\n\n

OUTPUT @table<\/p>\n\n\n\n

    TO “\/OUTPUTS\/Sampledataquery.csv”<\/p>\n\n\n\n

\u00a0\u00a0\u00a0\u00a0USING Outputters.Csv(); <\/p>\n\n\n


\n
\n<\/p>\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 Database, 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

<\/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>","protected":false},"excerpt":{"rendered":"

The modern data warehouse will need to use Azure Data Lake and U-SQL to combine data. Begin by navigating to your Azure Portal and searching for the Data Lake Analytics Resource. Let\u2019s start by creating a new Data Lake. Don\u2019t worry, this service only charges on data in and out, not just remaining on like […]<\/p>\n","protected":false},"author":1,"featured_media":135,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4,28,27,11,29],"tags":[44,15,60,42,62,61],"class_list":["post-210","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-analytics","category-azure","category-big-data","category-data-warehouse","category-hdinsight","tag-azure","tag-big-data","tag-business-intelligence","tag-cloud","tag-data-lake","tag-usql"],"_links":{"self":[{"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/posts\/210","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=210"}],"version-history":[{"count":7,"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/posts\/210\/revisions"}],"predecessor-version":[{"id":779,"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/posts\/210\/revisions\/779"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/media\/135"}],"wp:attachment":[{"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/media?parent=210"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/categories?post=210"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/tags?post=210"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}