Tag: Big Data

  • Get HDInsight Properties with PowerShell

    Small Bites of Big Data from AzureCAT

    You’ve created your HDInsight Hadoop clusters and now you want to know exactly what you have out there in Azure. Maybe you want to pull the key information into a repository periodically as a reference for future troubleshooting, comparisons, or billing. Maybe you just need to get a quick look at your overall HDInsight usage. This is something you can easily automate with PowerShell.

    Environment

    First, open Windows Azure PowerShell or powershell_ise.exe.

    Set some values for your environment:

    $SubName = "YourSubscriptionName"
    
    Select-AzureSubscription -SubscriptionName $SubName
    
    Get-AzureSubscription -Current
    
    $ClusterName = "HDInsightClusterName" #HDInsight cluster name
    
    
    

    HDInsight Usage for the Subscription

    Take a look at your overall HDInsight usage for this subscription:

    Get-AzureHDInsightProperties

    Get-AzureHDInsightProperties returns the number of clusters for this subscription, the total HDInsight cores used and available (for head nodes and data nodes), the Azure regions where HDInsight clusters can be created, and the HDInsight versions available for new clusters:

    ClusterCount    : 2
    
    CoresAvailable  : 122
    
    CoresUsed       : 48
    
    Locations       : {East US, North Europe, Southeast Asia, West Europe...}
    
    MaxCoresAllowed : 170
    
    Versions        : {1.6, 2.1, 3.0}

    You can also pick out specific pieces of information and write them to a file, store them as variables, or use them elsewhere. This example simply outputs the values to the screen.

    write-host '== Max HDInsight Cores for Sub: ' (Get-AzureHDInsightProperties).MaxCoresAllowed
    
    write-host '== Cores Available:             ' (Get-AzureHDInsightProperties).CoresAvailable
    
    write-host '== Cores Used:                  ' (Get-AzureHDInsightProperties).CoresUsed

    HDInsight Cluster Information

    Get-AzureHDInsightCluster provides information about all existing HDInsight clusters for this subscription:

    Get-AzureHDInsightCluster
    As you can see this cmdlet tells you the size, connection information, and version.
    ClusterSizeInNodes    : 4
    
    ConnectionUrl         : https://BigCAT.azurehdinsight.net
    
    CreateDate            : 4/5/2014 3:37:23 PM
    
    DefaultStorageAccount : sqlcatwomanwestus.blob.core.windows.net
    
    HttpUserName          : Admin
    
    Location              : West US
    
    Name                  : BigCAT30
    
    State                 : Running
    
    StorageAccounts       : {}
    
    SubscriptionId        : {YourSubID}
    
    UserName              : Admin
    
    Version               : 3.0.0.0.661685
    
    VersionStatus         : Compatible
    
    
    
    ClusterSizeInNodes    : 4
    
    ConnectionUrl         : https://cgrosstest.azurehdinsight.net
    
    CreateDate            : 5/5/2014 6:09:58 PM
    
    DefaultStorageAccount : cgrosstest.blob.core.windows.net
    
    HttpUserName          : Admin
    
    Location              : West US
    
    Name                  : cgrosstest
    
    State                 : Running
    
    StorageAccounts       : {sqlcatwomanwestus.blob.core.windows.net}
    
    SubscriptionId        : {YourSubID}
    
    UserName              : Admin
    
    Version               : 3.0.2.0.727283
    
    VersionStatus         : Compatible

    You can also get information about just one HDInsight cluster at a time:

    Get-AzureHDInsightCluster  -name $ClusterName

    Or you can get very granular and look at specific properties, even some that aren’t in the default values:

    write-host '== Default Storage Account:     ' `
    
    (Get-AzureHDInsightCluster -Cluster $ClusterName).DefaultStorageAccount.StorageAccountName.split(".")[0]
    
    write-host '== Default Container:           ' `
    
    (Get-AzureHDInsightCluster -Cluster $ClusterName).DefaultStorageAccount.StorageContainerName

    This information will be a valuable source of information for tracking past configurations, current usage, and planning. Enjoy your Hadooping!

    Sample Script

    # Cindy Gross 2014
    
    # Get HDInsight properties
    
    $SubName = "YourSubscriptionName"
    
    Select-AzureSubscription -SubscriptionName $SubName
    
    Get-AzureSubscription -Current
    
    $ClusterName        = "YourHDInsightClusterName" #HDInsight cluster name
    
    
    
    Get-AzureHDInsightProperties 
    
    Get-AzureHDInsightCluster 
    
    Get-AzureHDInsightCluster  -name $ClusterName
    
    write-host '== Default Storage Account:     ' `
    
    (Get-AzureHDInsightCluster -Cluster $ClusterName).DefaultStorageAccount.StorageAccountName.split(".")[0]
    
    write-host '== Default Container:           ' `
    
    (Get-AzureHDInsightCluster -Cluster $ClusterName).DefaultStorageAccount.StorageContainerName
    
    write-host '== Max HDInsight Cores for Sub: ' (Get-AzureHDInsightProperties).MaxCoresAllowed
    
    write-host '== Cores Available:             ' (Get-AzureHDInsightProperties).CoresAvailable
    
    write-host '== Cores Used:                  ' (Get-AzureHDInsightProperties).CoresUsed
  • Use Additional Storage Accounts with HDInsight Hive

    When you create an HDInsight Hadoop cluster you pass in one or more storage accounts and their associated keys. This allows you to access the files on all associated storage accounts from the cluster. If you want to use public storage that isn’t passed in at create time that’s easy – simply supply the storage account name each time you run a job. But how do you access data on private storage accounts that need an access key?

    The steps are laid out in this wiki by Eric Hanson: Using an HDInsight Cluster with Alternate Storage Accounts and Metastores

    http://social.technet.microsoft.com/wiki/contents/articles/23256.using-an-hdinsight-cluster-with-alternate-storage-accounts-and-metastores.aspx

    I am providing a variable based variation of the PowerShell sample for Hive. To set up PowerShell for use with Azure see Getting Started with Azure PowerShell Cmdlets–Subscription Management.

    First you will set some values for your environment. If you use your default subscription you don’t need to pass in the subscription name and select it. However, you will always need to specify the HDInsight cluster name. In this example $undefinedStorageAccount is the name of an account that you want to access from a cluster but you didn’t define it when you created the cluster. You always need to specify which container to use for any given reference so you also need to define $undefinedContainer. If the storage account belongs to the current subscription you can simply ask Azure to return the key (#commented out in the example below) or you can paste in the key that someone has given you.

    $subscriptionName = "LocalAzureSubscriptionName"
    
    $clusterName = "HDInsightClusterName"
    
    $undefinedStorageAccount = "AdditionalStorageAccount"
    
    $undefinedContainer = "ContainerOnAdditionalStorageAccount"
    
    #$undefinedStorageKey = Get-AzureStorageKey $undefinedStorageAccount | %{ $_.Primary }
    
    $undefinedStorageKey = "YourActualAccessKeyFromAzurePortal"

    Now choose which of your locally defined subscriptions to use:

    Select-AzureSubscription -SubscriptionName $subscriptionName

    Set the context of the cluster you want to use:

    Use-AzureHDInsightCluster $clusterName

    Now let’s check your HDInsight cluster properties.

    $defaultStorageAccount  = (Get-AzureHDInsightCluster -Name $clusterName).DefaultStorageAccount.StorageAccountName #default/only storage account
    
    $defaultContainerName   = (Get-AzureHDInsightCluster -Subscription $SubID -Cluster $ClusterName).DefaultStorageAccount.StorageContainerName
    
    $definedStorageAccounts = (Get-AzureHDInsightCluster -Name $clusterName).StorageAccounts #no 2nd account is associated, no value is returned

    Let’s check the values and verify that the storage account you want to use is not listed as either the DefaultStorageAccount (every cluster has one) or as one of the additional known storage accounts configured during provisioning (you may have zero, one, or many).

    write-host "===Default storage account"
    
    $defaultStorageAccount
    
    write-host "===Default container name"
    
    $defaultContainerName
    
    write-host "===Other defined storage accounts for this cluster"
    
    $definedStorageAccounts

    Next we’ll get a non-recursive listing of the files in the default location:

    invoke-hive "dfs -ls wasb://$defaultContainerName@$defaultStorageAccount/;" #default storage

    And then try to get a listing for the private storage account that we have not associated with the cluster:

    invoke-hive "dfs -ls wasb://$undefinedContainer@$undefinedStorageAccount/;" #not associated, errors

    Because the storage account access key is not yet known you will see an error similar to this one:

    Logging initialized using configuration in file:/C:/apps/dist/hive-0.12.0.2.0.7.0-1559/conf/hive-log4j.properties
    
    ls: org.apache.hadoop.fs.azure.AzureException: Unable to access container xyz in account abc using anonymous credentials, 
    
    and no credentials found for them  in the configuration.
    
    Command failed with exit code = 1

    But we can fix this! From PowerShell we can pass in “defines” statements to change configuration values, add libraries, etc.

    $defines = @{}
    
    $defines.Add("fs.azure.account.key.$undefinedStorageAccount.blob.core.windows.net", $undefinedStorageKey)
    
    Invoke-Hive -Defines $defines -Query "dfs -ls wasb://$undefinedContainer@$undefinedStorageAccount.blob.core.windows.net/;"

    The access key is only available to this Hive query, but now that I have the variables set I can pass it in to other queries as well. Happy Hiving!

    I hope you enjoyed this small bite of Big Data!

  • Sample PowerShell Script: HDInsight Custom Create

    This is a working script I use to create various HDInsight clusters. For a really reproducible, automated environment you would want to put this into a .ps1 script that accepts parameters (see here for an example). However, you may find the method below good for learning and experimenting. Replace all the “YOURxyz” sections with your actual information. Beware of oddities introduced by cut/paste such as spaces being replaced by line breaks or quotes being replaced by smart quotes. The # is a comment, some commands that you rarely run are commented out so remove the # to run them if you need them.

    # This PowerShell script is meant to be a cut/paste of specific parts, it is NOT designed to be run as a whole.

    # Do once after you install the cmdlets
    #Get-AzurePublishSettingsFile
    #Import-AzurePublishSettingsFile C:UsersYOURDirectoryDownloadsYOURName-credentials.publishsettings

    # Use if you admin more than one subscription
    #Get-AzureAccount # This may be needed to log in to Azure
    Select-AzureSubscription –SubscriptionName YOURSubscription
    Get-AzureSubscription -Current

    # Many things are easier in the ISE
    ise

    ###############################################
    ### create clusters ###
    ###############################################

    # Add your specific information here
    # Previous failures may make a name unavailable for a while – check to see if previous cluster was partially created
    $ClusterName = “YOURNewHDInsightClusterName” #the name you will give to your cluster
    $Location = “YOURDataCenter” #cluster data center must be East US, West US, or North Europe (as of December 2013)
    $NumOfNodes = 1 #start small
    $StorageAcct1 = “YOURExistingStorageAccountName” #currently must be in same data center as the cluster
    $DefaultContainer = “YOURExistingContainerName” #already exists on the storage account

    # These variables are automatically set for you
    $FullStorage1 = “${StorageAcct1}.blob.core.windows.net”
    $Key1 = Get-AzureStorageKey $StorageAcct1 | %{ $_.Primary }
    $SubID = Get-AzureSubscription -Current | %{ $_.SubscriptionId }
    $SubName = Get-AzureSubscription -Current | %{ $_.SubscriptionName }
    $Cert = Get-AzureSubscription -Current | %{ $_.Certificate }
    $Creds = Get-Credential -Message “New admin account to be created for your HDInsight cluster” #this prompts you

    ###############################################
    # Sample quick create
    ###############################################
    # Equivalent of quick create
    # The ` specifies that the cmd continues on the next line, beware of artifical line breaks added during cut/paste from the blog
    New-AzureHDInsightCluster -Name $ClusterName -ClusterSizeInNodes $NumOfNodes -Subscription $SubID -Location “$Location” `
    -DefaultStorageAccountName $FullStorage1 -DefaultStorageAccountKey $Key1 -DefaultStorageContainerName $DefaultContainer -Credential $Creds

    ###############################################
    # Sample custom create
    ###############################################
    #https://hadoopsdk.codeplex.com/wikipage?title=PowerShell%20Cmdlets%20for%20Cluster%20Management
    # Most params are the same as quick create, use a new cluster name
    # Pass in a 2nd storage account, a SQLAzure db for the metastore (assume same db for Oozie and Hive), add Avro library, some config values
    # Execute all the variable settings from above

    # This value is set for you, don’t change!
    $configvalues = new-object ‘Microsoft.WindowsAzure.Management.HDInsight.Cmdlet.DataObjects.AzureHDInsightHiveConfiguration’

    # Add your specific information here
    $ClusterName = “YOURNewHDInsightClusterName”
    $StorageAcct2 = “YOURExistingStorageAccountName2”
    $MetastoreAzureSQLDBName = “YOURExistingSQLAzureDBName”
    $MetastoreAzureServerName = “YOURExistingSQLAzureServer.database.windows.net” #gives a DNS error if you don’t use the full name
    $configvalues.Configuration = @{ “hive.exec.compress.output”=”true” }  #this is an example of a config value you may pass in

    # These variables are automatically set for you
    $FullStorage2 = “${StorageAcct2}.blob.core.windows.net”
    $Key2 = Get-AzureStorageKey $StorageAcct2 | %{ $_.Primary }
    $MetastoreCreds = Get-Credential -Message “existing id/password for your SQL Azure DB (metastore)” #This prompts for the existing id and password of your existing SQL Azure DB

    # Add a config file value
    # Add AVRO SerDe libraries for Hive (on storage 1)
    $configvalues.AdditionalLibraries = new-object ‘Microsoft.WindowsAzure.Management.HDInsight.Cmdlet.DataObjects.AzureHDInsightDefaultStorageAccount’
    $configvalues.AdditionalLibraries.StorageAccountName = $FullStorage1
    $configvalues.AdditionalLibraries.StorageAccountKey = $Key1
    $configvalues.AdditionalLibraries.StorageContainerName = “hivelibs” #container called hivelibs must exist on specified storage account
    # Create custom cluster
    New-AzureHDInsightClusterConfig -ClusterSizeInNodes $NumOfNodes `
    | Set-AzureHDInsightDefaultStorage -StorageAccountName $FullStorage1 -StorageAccountKey $Key1 -StorageContainerName $DefaultContainer `
    | Add-AzureHDInsightStorage -StorageAccountName $FullStorage2 -StorageAccountKey $Key2 `
    | Add-AzureHDInsightMetastore -SqlAzureServerName $MetastoreAzureServerName -DatabaseName $MetastoreAzureSQLDBName -Credential $MetastoreCreds -MetastoreType OozieMetastore `
    | Add-AzureHDInsightMetastore -SqlAzureServerName $MetastoreAzureServerName -DatabaseName $MetastoreAzureSQLDBName -Credential $MetastoreCreds -MetastoreType HiveMetastore `
    | Add-AzureHDInsightConfigValues -Hive $configvalues `
    | New-AzureHDInsightCluster -Subscription $SubID -Location “$Location” -Name $ClusterName -Credential $Creds

    ###############################################
    # get status, properties, etc.
    ###############################################
    #$SubName = $SubID = Get-AzureSubscription -Current | %{ $_.SubscriptionName }
    Get-AzureHDInsightProperties -Subscription $SubName
    Get-AzureHDInsightCluster -Subscription $SubName
    Get-AzureHDInsightCluster -Subscription $SubName -name YOURClusterName

    ###############################################
    # remove cluster
    ###############################################
    #Remove-AzureHDInsightCluster -Name $ClusterName -Subscription $SubName

  • Your First HDInsight Cluster–Step by Step

    Small Bites of Big Data from AZURECAT
    Big Data Tech Training Series #1
    Cindy Gross | Murshed Zaman

    Sometimes it is just hard to get started. Have you been putting off your first foray into Hadoop? Are you not sure where to begin? Let’s get really basic.

    Prerequisites:

    Log on to the Windows Azure Portal https://manage.windowsazure.com

    Go to storage https://manage.windowsazure.com/#Workspaces/StorageExtension/storage. Create a storage account in a location that is available to HDInsight (as of November 2013 that’s East US, West US, and North Europe). Do NOT choose an affinity group. If you choose to “Enable Geo-Replication” there will be an extra charge – it’s probably not necessary for a demo/test account as you have a limited amount of credit in the trial subscription. In the portal choose the STORAGE icon on the left. Then click on +NEW at the bottom. That opens a QUICK CREATE window. Enter a unique name for your storage, such as sqlcatwomanrules. It only allows lower case letters and numbers.

    StorageNov2013 NewStorageAccountNov2013

    Now click on the HDInsight icon just below the storage icon storage. Choose QUICK CREATE. Enter a unique name for your HDInsight cluster. For a demo choose 4 data nodes. Enter a password that contains upper and lower case letters, a number, and a special character. Choose the storage account you created above. Once you click on “CREATE HDINSIGHT CLUSTER” it will take several minutes for the cluster to be deployed.

    StorageNov2013_HDI QuickCreateNov2013

    Once it completes you are ready to use your cluster!

    ClusterQuickStart

    If you won’t be using the cluster right away, go ahead and delete it (look for the icon at the bottom of the portal) to save compute time and money. You can easily recreate it when you need it.

    image

    Look for more blogs soon on customizing your cluster with CUSTOM CREATE or PowerShell and on automating deployment and jobs with PowerShell. In the meantime see if you can get Invoke-Hive working from PowerShell for some simple Hive commands such as:

    Invoke-Hive “select * from hivesampletable limit 10”

    Big Data Technical Series:

    Your First HDInsight Cluster–Step by Step

    Automating HDInsight cluster creation with PowerShell

  • PowerShell for Azure cmdlets: Subscription was all Wacky

    I was working on some HDInsight scripts in PowerShell and doing lots of experimenting. I’m not sure what exactly I did but all of a sudden everything stopped working. With lots of interruptions from meetings and chats and lunch…. I couldn’t retrace my steps. Everything seemed to fail on the Azure subscription information so I tried to get really basic – what did Get-AzureSubscription|%{$_.SubscriptionName} return? As it turns out, wacky garbage:

    set-azuresubscription ?
    !?
    !
    help
    sqlcatwoman
    ?
    Sqlcatwomanwestus

    What I expected to see was my single subscription:

    sqlcatwoman

    So what happened? The Azure portal only shows one subscription. Obviously those other lines are not valid subscriptions – they look like the output of a help command or an error. Reinstalling the cmdlets, rebooting, and reimporting certificates didn’t help. I turned to my AzureCAT coworkers for help and @elcid98 pointed out this blog post that talks about how subscriptions are used in PowerShell:

    Azure Subscriptions in PowerShell demystified http://yossidahan.wordpress.com/2012/12/12/azure-subscriptions-in-powershell-demystified/

    This caught my attention: “The second file – DefaultSubscriptionData.xml – also lists the available subscriptions and the associated certificates’ thumbprints“. Ok, where is that file? A search finds it in

    C:Users%username%AppDataRoamingWindows Azure Powershell

    I checked and sure enough, where I would expect just one entry I see multiple – and they’re named the same thing as the garbage in my output! I cleared out all but one entry to end up with this:

    <?xml version=”1.0″ encoding=”utf-8″?>
    < Subscriptions xmlns:xsd=”http://www.w3.org/2001/XMLSchema” xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” version=”0″ xmlns=”urn:Microsoft.WindowsAzure.Management:WaPSCmdlets”>
    < Subscription name=”sqlcatwoman”>
    < SubscriptionId>You don’t get to see the real info!</SubscriptionId>
    <Thumbprint>Not here either!</Thumbprint>
    < ServiceEndpoint>https://management.core.windows.net</ServiceEndpoint>
    < /Subscription>
    < /Subscriptions>

    Hmmmm…. I still got an error from Get-AzureSubscription. Back to C:Users%username%AppDataRoamingWindows Azure PowerShell. What’s this? WindowsAzureProfile.xml also has all the same junk! I cleared out all the extras to end up with this:

    <?xml version=”1.0″ encoding=”utf-8″?>
    < ProfileData xmlns:i=”http://www.w3.org/2001/XMLSchema-instance” xmlns=”http://schemas.datacontract.org/2004/07/Microsoft.WindowsAzure.Commands.Utilities.Common”>
    < DefaultEnvironmentName>AzureCloud</DefaultEnvironmentName>
    <Environments />
    <Subscriptions>
    < AzureSubscriptionData>
    < ActiveDirectoryEndpoint>https://login.windows.net/</ActiveDirectoryEndpoint>
    < ActiveDirectoryTenantId>More secrets!</ActiveDirectoryTenantId>
    < ActiveDirectoryUserId>cgross@microsoft.com</ActiveDirectoryUserId>
    < CloudStorageAccount i:nil=”true” />
    < IsDefault>true</IsDefault>
    < LoginType i:nil=”true” />
    < ManagementCertificate>Hiding this one too!</ManagementCertificate>
    < ManagementEndpoint>https://management.core.windows.net/</ManagementEndpoint>
    <Name>sqlcatwoman</Name>
    <RegisteredResourceProviders xmlns:d4p1=”http://schemas.microsoft.com/2003/10/Serialization/Arrays” />
    < SubscriptionId>And more secrets</SubscriptionId>
    </AzureSubscriptionData>
    </Subscriptions>
    < /ProfileData>

    Success! Get-AzureSubscription now returns just my single, valid subscription. All my other Azure cmdlets magically started working again. I don’t know how it got that way, but at least now I know where the subscription information is stored. I hope this helps someone else with their Azure subscription PowerShell scripting!

  • HDInsight Big Data Talks from #SQLPASS

    SQL PASS Summit 2013 was another great data geek week! I chatted with many of you about Big Data, Hadoop, HDInsight, architecting solutions, SQL Server, data, BI, analytics, and general geekiness – great fun! This time around I delivered two talks on Hadoop and HDInsight – the slides from both are attached.

    Zero to 60 with HDInsight takes you from an overview of Big Data and why it matters (zero) all the way through an end to end solution (60). We discussed how to create an HDInsight cluster with the Azure portal or PowerShell and talked through the architecture of the data and analysis behind the release of Halo 4. We talked about how you could use the same architectural pattern for many projects and walked through Hive and Pig script examples. We finished up with how to use Power Map (codename GeoFlow) over that data to gain new insights and improve the game experience for the end user.

    The next session I co-presented with HDInsight PM Dipti Sangani: CAT: From Question to Insight with HDInsight and BI. We went deeper this time. Not only did we present an end to end story with how our own internal Windows Azure SQL Database team uses telemetry to improve your experience with SQL Server in Azure PaaS but we also went deeper with demos of Hive, Pig, and Oozie. We also gave another archetypical design scenario that will apply to many of your own scenarios and talked about how HDInsight fits with SQL Server and your other existing infrastructure. The deck covers your cloud and on-premises options for Hadoop on Windows including HDInsight Service, Hortonworks HDP for Windows, OneBox, and PDW with Polybase.

    Please let me know if you have any questions from the talks or just general HDInsight questions!

    PASSSummit2013BigData.zip

  • Big Data Twitter Demo

    Real-time. Social Sentiment Analysis. Twitter. Cloud. Insights. We have your Big Data buzzwords here!

    Everyone seems to want to incorporate social sentiment into their business analysis. Well we have the demo for you! Use it for a quick demonstration of what can be done and when the excitement goes through the roof, use it to inspire your own design!

    Real-Time Processing – Instant Insights!

    First, use Event Driven Processing (EDP) to show the data on a dashboard. In the demo you’ll use StreamInsight, also referred to as Complex Event Processing (CEP), though you may want to use Microsoft Open Technologies’s RX / Reactive Extensions in your own project. Use the dashboard to make real-time decisions and take immediate action. For example, configure your EDP to “pop” only on terms related to your company and your marketing analyst can watch how the volume of tweets and the sentiment (measured positive/neutral/negative in this example) change in response to your Super Bowl ad or a mention of your company on a the news show. She can respond instantly with changes to your website, your own tweets, sales/promotions, or whatever is appropriate to your business.

    Data Storage – Enable Insights!

    EDP reads the data as it is pushed through a query, there is no inherent storage involved. You could just discard the data and never store it. In this example we chose to store the data for later trending and historical analysis. We take the tweet id, the date/time the tweet was captured, the sentiment score calculated during the real-time processing, and the keyword that caught our attention and store it in SQL Azure. This data is available to other applications that need to join it with existing data and requires fast responses to individual queries. The remaining data including the raw tweet, any geographic data the tweeter chose to share, and other data is dropped in an Azure Blob Store.

    Trends, Patterns, and Historical Insights!

    Now point HDInsight (Hadoop) to the Azure Blob Storage using HDInsight’s ASV extension to HDFS. You can spin up a Hadoop cluster in Azure, pay for as many nodes as you need for as long as you need them then spin them down to save money. The data remains in the blob store – available for future Hadoop clusters, other applications, archival, or whatever you need it for. Add structure to the JSON data with Hive and now you have rows and columns that can be accessed by BI tools!

    Visualization for Powerful Insights!

    Now create a PowerPivot tabular model (self-service BI / client side) or an Analysis Service tabular model (corporate BI) to store the relevant data in a highly compressed, in-memory format for fast access. Add in a few Power View visualizations mashing up data from multiple structured and unstructured sources and you can show your business decision makers easily digestible and understandable data in a format they just get and love! Make some decisions, take some actions, and you’ve just shown how to turn free Twitter data into a valuable resource that can have a direct impact on your company!

    How to Get These Insights

    Follow the instructions on the CodePlex site for the Big Data Twitter Demo project. Set it up, run through the demo, get excited, and go improve your business!

    Demo Created By:

    Vu Le

    Andrew Moll

    Aviad Ezra @aviade_pro

    Brad Sarsfield @Bradoop

    Later Additions By:

    Lara Rubbelke @SQLGal | http://sqlblog.com/blogs/lara_rubbelke/

    Robert Bruckner http://blogs.msdn.com/b/robertbruckner/

    Cindy Gross @SQLCindy | http://blogs.msdn.com/cindygross

  • Access Azure Blob Stores from HDInsight

    Small Bites of Big Data

    Edit Mar 6, 2014: This is no longer necessary for HDInsight – you specify the storage accounts when you create the cluster and the rest happens auto-magically. See http://blogs.msdn.com/b/cindygross/archive/2013/11/25/your-first-hdinsight-cluster-step-by-step.aspx or http://blogs.msdn.com/b/cindygross/archive/2013/12/06/sample-powershell-script-hdinsight-custom-create.aspx.

    One of the great enhancements in Microsoft’s HDInsight distribution of Hadoop is the ability to store and access Hadoop data on an Azure Blob Store. We do this via the HDFS API extension called Azure Storage Vault (ASV). This allows you to persist data even after you spin down an HDInsight cluster and to make that data available across multiple programs or clusters from persistent storage. Blob stores can be replicated for redundancy and are highly available. When you need to access the data from Hadoop you point your cluster at the existing data and the data persists even after the cluster is spun down.

    Azure Blob Storage

    Let’s start with how your data is stored. A storage account is created in the Azure portal and has access keys associated with it. All access to your Azure blob data is done via storage accounts. Within a storage account you need to create at least one container, though you can have many. Files (blobs) are put in the container(s). For more information on how to create and use storage accounts and containers see: http://www.windowsazure.com/en-us/develop/net/how-to-guides/blob-storage/. Any storage accounts associated with HDInsight should be in the same data center as the cluster and must not be in an affinity group.

    You can create a container from the Azure portal or from any of the many Azure storage utilities available such as CloudXplorer. In the Azure portal you click on the Storage Account then go to the CONTAINERS tab. Next click on ADD CONTAINER at the very bottom of the screen. Enter a name for your container, choose the ACCESS property, and click on the checkmark.

    AzureStorageContainers_thumb AzureStorageManageContainers_thumb AzureStorageNewContainer_thumb

    HDInsight Service Preview

    When you create your HDInsight Service cluster on Azure you associate your cluster with an existing Azure storage account in the same data center. In the current interface the QUICK CREATE doesn’t allow you to choose a default container on that storage account so it creates a container with the same name as the cluster. If you choose CUSTOM CREATE you have the option to choose the default container from existing containers associated with the storage account you choose. This is all done in the Azure management portal: https://manage.windowsazure.com/.

    Quick Create: image

    Custom Create: image

    You can then add additional storage accounts to the cluster by updating C:appsdisthadoop-1.1.0-SNAPSHOTconfcore-site.xml on the head node. This is only necessary if those additional accounts have private containers (this is a property set in the Azure portal for each container within a storage account). Public containers and public blobs can be accessed without the id/key being stored in the configuration file. You choose the public/private setting when you create the container and can later edit it in the “Edit container metadata” dialog on the Azure portal.

    image StorageContainerEdit

    The key storage properties in the default core-site.xml on HDInsight Service Preview are:

    <property>
    <name>fs.default.name</name>
    <!– cluster variant –>
    <value>asv://YOURDefaultContainer@YOURStorageAccount.blob.core.windows.net</value>
    <description>The name of the default file system.  Either the
    literal string “local” or a host:port for NDFS.</description>
    <final>true</final>
    </property>

    <property>
    <name>dfs.namenode.rpc-address</name>
    <value>hdfs://namenodehost:9000</value>
    </property>

    <property>
    <name>fs.azure.account.key.YOURStorageAccount.blob.core.windows.net</name>
    <value>YOURActualStorageKeyValue</value>
    </property>

    To add another storage account you will need the Windows Azure storage account information from https://manage.windowsazure.com. Log in to your Azure subscription and pick storage from the left menu. Click on the account you want to use then at the very bottom click on the “MANAGE KEYS” button. Cut and paste the PRIMARY ACCESS KEY (you can use the secondary if you prefer) into the new property values we’ll discuss below.

    AzureStorageAccounts_thumb AzureStorageContainerKeys_thumb1 AzureStorageMgKeys_thumb1

    Create a Remote Desktop (RDP) connection to the head node of your HDInsight Service cluster. You can do this by clicking on the CONNECT button at the bottom of the screen when your HDInsight Preview cluster is highlighted. You can choose to save the .RDP file and edit it before you connect (right click on the .RDP file in Explorer and choose Edit). You may want to enable access to your local drives from the head node via the “Local Resources” tab under the “More” button in the “Local devices and resources” section. Then go back to the General tab and save the settings. Connect to the head node (either choose Open after you click CONNECT or use the saved RDP).

    image image

    On the head node make a copy of C:appsdisthadoop-1.1.0-SNAPSHOTconfcore-site.xml in case you have to revert back to the original. Next open core-site.xml in Notepad or your favorite editor.

    Add your 2nd Azure storage account by adding another property.

    <property>
    <name>fs.azure.account.key.YOUR_SECOND_StorageAccount.blob.core.windows.net</name>
    <value>YOUR_SECOND_ActualStorageKeyValue</value>
    </property>

    Save core-site.xml.

    Repeat for each storage account you need to access from this cluster.

    HDInsight Server Preview

    If you have downloaded the on-premises HDInsight Server preview from http://microsoft.com/bigdata that gives you a single node “OneBox” install to test basic functionality. You can put it on your local machine, on a Hyper-V virtual machine, or in a Windows Azure IaaS virtual machine. You can also point this OneBox install to ASV. Using an IaaS VM in the same data center as your storage account will give you better performance, though the OneBox preview is meant purely for basic functional testing and not for high performance as it is limited to a single node. The steps are slightly different for on-premises as the installation directory and default properties in core-site.xml are different.

    Make a backup copy of C:Hadoophadoop-1.1.0-SNAPSHOTconfcore-site.xml from your local installation (local could be on a VM).

    Edit core-site.xml:

    1) Change the default file system from local HDFS to remote ASV

    <property>
    <name>fs.default.name</name>
    <!– cluster variant –>
    <value>hdfs://localhost:8020</value>
    <description>The name of the default file system.  Either the
    literal string “local” or a host:port for NDFS.</description>
    <final>true</final>
    </property>

    to:

    <property>
    <name>fs.default.name</name>
    <!– cluster variant –>
    <value>asv://YOURDefaultContainer@YOURStorageAccount.blob.core.windows.net</value>
    <description>The name of the default file system.  Either the
    literal string “local” or a host:port for NDFS.</description>
    <final>true</final>
    </property>

    2) Add the namenode property (do not change any values)

    <property>
    <name>dfs.namenode.rpc-address</name>
    <value>hdfs://namenodehost:9000</value>
    </property>

    3) Add the information that associates the key value with your default storage account

    <property>
    <name>fs.azure.account.key.YOURStorageAccount.blob.core.windows.net</name>
    <value>YOURActualStorageKeyValue</value>
    </property>

    4) Add any additional storage accounts you plan to access

    <property>
    <name>fs.azure.account.key.YOUR_SECOND_StorageAccount.blob.core.windows.net</name>
    <value>YOUR_SECOND_ActualStorageKeyValue</value>
    </property>

    Save core-site.xml.

    Files

    Upload one or more files to your container(s). You can use many methods for loading the data including Hadoop file system commands such as copyFromLocal or put, 3rd party tools like CloudXPlorer, JavaScript, or whatever method you find fits your needs. For example, I can upload all files in a data directory (for simplicity this sample refers to C: which is local to the head node) using the Hadoop put command:

    hadoop fs -put c:data asv://data@sqlcatwomanblog.blob.core.windows.net/

    Or upload a single file:

    hadoop fs -put c:databacon.txt asv://data@sqlcatwomanblog.blob.core.windows.net/bacon.txt

    To view the files in a linked non-default container or a public container use this syntax from a Hadoop Command Line prompt (fs=file system, ls=list):

    hadoop fs -ls asv://data@sqlcatwomanblog.blob.core.windows.net/

    Found 1 items
    -rwxrwxrwx   1        124 2013-04-24 20:12 /bacon.txt

    In this case the container data on the private storage account sqlcatwomanblog has one file called bacon.txt.

    For the default container the syntax does not require the container and account information. Since the default storage is ASV rather than HDFS (even for HDInsight Server in this case because we changed it in core-site.xml) you can even leave out the ASV reference.

    hadoop fs -ls asv:///bacon.txt
    hadoop fs -ls /bacon.txt

    More Information

    I hope you’ve enjoyed this small bite of big data! Look for more blog posts soon.

    Note: the Preview, CTP, and TAP programs are available for a limited time. Details of the usage and the availability of the pre-release versions may change rapidly.

    Digg This
  • HDInsight: Jiving about Hadoop and Hive with CAT

    Tomorrow I will be talking about Hive as part of Pragmatic Work’s Women in Technology (WIT) month of webcasts. I am proud to be part of this lineup with all these stellar WITs! I encourage my fellow WITs to get more involved in your data community and if you don’t already do so start tweeting, blogging, and speaking. I am happy to coach you through your first speaking engagement if you are interested. Get out there and start showing the world what you can do!

    Thursday’s talk is going to be HDInsight: Jiving about Hadoop and Hive with CAT. Let’s break that title down.

    HDInsight is Microsoft’s distribution of Hadoop. As part of the HDInsight project we have checked code back into the core Apache Hadoop source code to make the core code runs great on Windows. We are also adding functionality and features such as JavaScript and Azure Storage Vault that make the product more robust and enterprise friendly. This week the HDInsight Service Preview on Azure became available to those with an Azure subscription.

    Hadoop is a scale out methodology that allows businesses to quickly consume and analyze data in ways they haven’t been able to before. This can lead to faster, better business insights and business actions.

    Hive is a way to impose metadata and structure on the loosely structured (unstructured, multi-structured, semi-structured) data that resides in Hadoop’s HDFS file system. With Hive and the Hive ODBC driver you can make Hadoop data look like any other data source to your familiar BI tools such as Excel. PowerPivot can connect to Hive data, mash that data up with existing data sources such as SQL Azure, SQL Server, and OData, and allow you to visualize it with Power View. I have an end to end demo of this: Hurricane Sandy Mash-Up: Hive, SQL Server, PowerPivot & Power View.

    CAT is my team at Microsoft. The Customer Advisory Team (CAT) works with customers who are doing new, unusual, and interesting things that push the boundaries of technology. We share what we learn with the community so you can do your jobs better and we take what we learn from you to the product team to help improve the product.

    My slides are attached at the bottom of this post. I believe a recording of the talk will be posted by Pragmatic Works on their site.

    I look forward to “seeing” you all at my talk tomorrow and would love to see your tweets or hear directly from you afterwards.

    I hope you’ve enjoyed this small bite of big data! Look for more blog posts soon on the samples and other activities.

    Note: the CTP and TAP programs are available for a limited time. Details of the usage and the availability of the CTP may change rapidly.

    Digg This

    PragmaticWorksHDInsightJivingAboutHadoopAndHiveWithCATMar202013.pptx

  • PASS BAC PREVIEW SERIES: SQL Professionals and the World of Self-service BI and Big Data

    Are you excited about the upcoming PASS Business Analytics Conference? You should be! This conference will offer a wide range of sessions about Microsoft’s End to End Business Intelligence (including Self-Service BI), Analytics, Big Data, Architecture, Reporting, Information Delivery, Data Management, and Visualization solutions. Whether you are an implementer, a planner, or a decision maker there is something here for you!

    PASS_BAC_Horizontal_Banner

    What makes this conference different? Why should you put in the effort to attend this conference in particular? We are seeing a paradigm shift focused on shorter time to decision, more data available than ever before, and the need for self-service BI. There are exciting technology solutions being presented to deal with these needs and new architectural skills are needed to implement them properly. Self-Service BI and Big Data are very different in many ways but also responding to the same problem – the need for additional insights and less time spent getting to those insights and the resulting impactful decisions. Self-Service BI via PowerPivot, Power View, Excel, and existing and new data sources including HDInsight/Hadoop (usually via Hive) offers fast time to decision, but you still sometimes need Enterprise BI to add additional value via services such as data curation, data stewardship, collaboration tools, additional security, training, and automation. Add in the powerful new data sources available with Big Data technologies such as HDInsight/Hadoop that can also reduce time to decision and open up all sorts of new opportunities for insight and you have many powerful new areas to explore. Not to mention that Dr. Steven Levitt, author of Freakonomics and SuperFreakonomics, is one of the keynote speakers!

    Read more about my thoughts on Self-Service BI and Big Data in this #PASSBAC guest blog published today: PASS BAC PREVIEW SERIES: SQL Professionals and the World of Self-service BI and Big Data

    And sign up for the session I am co-presenting at #PASSBAC with Eduardo Gamez of Intel: How Intel Integrates Self-Service BI with IT for Better Business Results

    Take a look at all the information tagged with #PASSBAC and tweeted by @PASSBAC, there are some good blogs, preview sessions, and tidbits being posted. Get your own Twibbon for Twitter, Facebook, or however you want to use it, the Twibbon site will add a ribbon to the picture of your choice:

    PASSBA2013Cindy

    If you’re going to be in Chicago anyway, you might as well stay a few extra days for two nearby SQL Saturdays. The weekend before the conference take a short hop over to Madison, WI for #SQLSAT206 on April 6, 2013 at the Madison Area Tech College. Then head over to the bacon, uhhh, PASS BA CONference April 10-12. Stay one more day in Chicago (technically Addison, IL) for the #SQLSAT211 sessions at Devry. This is a great opportunity for even more SQL Server immersion and networking!

    See you at #PASSBAC in Chicago in April!

    @SQLCindy

    Small Bites of Big Data