This chapter will cover the following recipes:
Data warehouse architects are facing the need to integrate many types of data. Cloud data integration can be a real challenge for on-premises data warehouses for the following reasons:
While there are many cloud-based solutions on the market, the recipes in this chapter will talk about the Microsoft Azure...
This recipe will cover the following topics:
This recipe assumes that you have a Microsoft Azure account. You can always create a trial account by registering at https://azure.microsoft.com .
ssiscookbook.ETL.Staging project and call it AggregatedSalesFromCloudDW.Connection Manager pane and select New file connection from the contextual menu that appears.Add SSIS Connection Manager window appears. Select Azure Srorage and click on the Add... button.Storage account name textbox, as shown the following screenshot:
cmgr_AzureStorage_ssiscookbook.In the previous recipe, we created and managed files using an Azure Blob storage. This recipe will do similar actions but this time using an on-premises Hadoop cluster.
DOWNLOAD FOR DOCKER. Once done, follow the instructions to configure it and make sure you have added the following entry to the %systemroot%\system32\drivers\etc\hosts file:127.0.0.1 sandbox.hortonworks.com
This is shown in the following screenshot:
http://sandbox.hortonworks.com:8888. Your browser screen should look like the following screenshot:
In this recipe, we'll add a package that will copy local data to the local cluster.
This recipe assumes that you have access to an on-premises cluster and have created a folder to hold the files in it from the previous recipe.
ETL.DW project and right-click on it to add a new package. Name it FactOrdersToHDPCuster.dtsx.Parameters tab and add a new parameter:
Name: LoadExecutionIdData type: Int64Value: Leave the default value 0Sensitive: Leave the default value FalseRequired: Truedft_FactOrders.ole_src_DW_vwFactOrders.cmgr_DW.SELECT OrderDate, FirstName, LastName, CompanyName, Category...
Hive is one of the languages used in Hadoop to interact with large volumes of data. It is very easy to learn since it uses SQL commands. This recipe will show you how we can use Hive to transform data from our source. Although we have only 542 lines of data in our file, we can still use it to learn Hadoop services calls.
In this recipe, we're going to create a database in Hive.
This recipe assumes that you have access to a Hortonworks sandbox on-premises or in Azure. It is also assumed that you have executed the previous recipe.
http://Sandbox.Hortonworks.com:8080. Use raj_ops for both the username and password to log in.raj_ops) in the toolbar and select Hive View 2.0, as shown in the following screenshot:
create database SSISCookBook in Worksheet1 and click on Execute, as shown in the following screenshot:
Browse...The data is now in the cluster in HDFS. We'll now transform it using a SQL script. The program we're using is Hive. This program interacts with the data using SQL statements.
With most Hadoop programs (Hive, Pig, Sparks, and so on), source is read-only. It means that we cannot modify the data in the file that we transferred in the previous recipe. Some languages such as HBase allow us to modify the source data though. But for our purpose, we'll use Hive, a well-known program in the Hadoop ecosystem.
This recipe assumes that you have access to a Hortonworks cluster and that you have transferred data to it following the previous recipe.
FactOrdersToHDPCuster.dtsx.hht_HDPDWHiveTable.Hadoop Hive Task Editor, as shown in the following screenshot:
Update the following parameters:
HadoopConnection:
cmgr_Hadoop_Sandbox...
Now that we have some data created by Hadoop Hive on-premises, we're going to transfer this data to a cloud storage on Azure. Then, we'll do several transformations to it using Hadoop Pig Latin. Once done, we'll transfer the data to an on-premises table in the staging schema of our AdventureWorksLTDW2016 database.
In this recipe, we're going to copy the data processed by the local Hortonworks cluster to an Azure Blob storage. Once the data is copied over, we can transform it using Azure compute resources, as we'll see in the following recipes.
This recipe assumes that you have created a storage space in Azure as described in the previous recipe.
So far, we've managed Blobs data using SSIS. In this case, the data was at rest and SSIS was used to manipulate it. SSIS was the orchestration service in Azure parlance. As stated in the introduction, SSIS can only be used on- premises and, so far, on a single machine.
The goal of this recipe is to use Azure HDInsight computation services. These services allow us to use (rent) powerful resources as a cluster of machines. These machines can run Linux or Windows according to user choice, but be aware that Windows will be deprecated for the newest version of HDInsight. Such clusters or machines, as fast and powerful as they can be, are very expensive to use. In fact, this is quite normal; we're talking about a potentially large amount of hardware here.
For this reason, unless we want to have these computing resource running continuously, SSIS has a way to create and drop a cluster on demand. The following recipe will show you how to do it.
Pig Latin is one of the programs available in big data clusters. The purpose of this program is to run scripts that can accept any type of data. "Pig can eat everything," as the mantra of the creators states.
This recipe is just meant to show you how to call a simple Pig script. No transformations are done. The purpose of the script is to show you how we can use an Azure Pig task with SSIS.
StgAggregatedSales.dtsx SSIS package, drag and drop an Azure Pig Task onto the control flow. Rename it apt_AggregateData.Azure HDInsight Pig Task Editor and set the properties as shown in the following screenshot:
SalesExtractsSource = LOAD 'wasbs:///Import/FactOrdersAggregated.txt';
rmf wasbs:///Export/;
STORE SalesExtractsSource INTO 'wasbs:///Export/' USING PigStorage('|'); So far, we've created and dropped a HDInsight cluster and called a Pig script using the Azure Pig task. This recipe will demonstrate how to import data from an Azure Blob storage to a table in the staging schema.
Execute SQL Task on the control flow, and rename it sql_truncate_Staging_StgCloudSales.SQL Task Editor. Set the properties as follows and click on OK:
Connection: cmgr_DWSQL Statement: TRUNCATE TABLE [Staging].[StgCloudSales];Foreach Loop Container and rename it felc_StgCloudSales.Foreach Loop Editor, and assign the properties in the Collection pane, as shown in the following screenshot:
Variable Mappings pane and add a string variable called User::AzureAggregatedData. Make sure the scope is at the package level...
© 2017 Packt Publishing Limited All Rights Reserved
