使用 HDInsight 中的 Apache Hive 分析航班延误数据

Apache Hive 提供了通过类似于 SQL 的脚本语言(称为 HiveQL)运行 Apache Hadoop MapReduce 作业的方法,此方法可用于对大量数据进行汇总、查询和分析。

Important

本文档中的步骤要求使用基于 Windows 的 HDInsight 群集。 Linux 是 HDInsight 3.4 或更高版本上使用的唯一操作系统。 有关详细信息,请参阅 HDInsight 在 Windows 上停用。 有关适用于基于 Linux 的群集的步骤,请参阅在 HDInsight (Linux) 中使用 Apache Hive 分析航班延误数据

Azure HDInsight 的主要优势之一就是隔离数据存储和计算。 HDInsight 将 Azure Blob 存储用于数据存储。 典型的作业包含三部分:

  1. 将数据存储在 Azure Blob 存储中。 例如,将天气数据、传感器数据、Web 日志以及此示例中的航班延误数据保存到 Azure Blob 存储中。
  2. 运行作业。 要处理数据时,可以运行 Windows PowerShell 脚本(或客户端应用程序)创建 HDInsight 群集、运行作业,然后删除该群集。 作业将输出数据保存到 Azure Blob 存储。 甚至在删除该群集后,输出数据也会保留。 这样,仅为已使用的内容付费。
  3. 从 Azure Blob 存储检索输出,或在此教程中将数据导出到 Azure SQL 数据库。

下图演示了本教程的方案和结构:

HDI.FlightDelays.flow

请注意,图中的编号对应于章节标题。 M 代表主进程。 A 代表附录中的内容。

本教程的主要部分说明如何使用单个 Windows PowerShell 脚本执行以下任务:

  • 创建 HDInsight 群集。
  • 在群集上运行 Hive 作业,以计算机场的平均延迟。 航班延误数据会存储在 Azure Blob 存储帐户中。
  • 运行 Sqoop 作业将 Hive 作业输出导出至 Azure SQL 数据库。
  • 删除 HDInsight 群集。

在附录中,你可以找到有关上传航班延误数据、创建/上传 Hive 查询字符串和针对 Sqoop 作业准备 Azure SQL 数据库的说明。

Note

本文档中的步骤特定于基于 Windows 的 HDInsight 群集。 有关适用于基于 Linux 的群集的步骤,请参阅在 HDInsight (Linux) 中使用 Apache Hive 分析航班延误数据

先决条件

要阅读本教程,必须具备以下项:

本教程中使用的文件

本教程使用来自 美国研究与技术创新管理部门 - 运输统计局或 RITA的航班准时表现数据。 数据的副本已上传至具有公共 Blob 访问权限的 Azure Blob 存储容器。 PowerShell 脚本的一部分将数据从公共 blob 容器复制到群集的默认 blob 容器。 HiveQL 脚本也会复制到同一 Blob 容器。 如果想要了解如何将数据获取/上传到自己的存储帐户,以及如何创建/上传 HiveQL 脚本文件,请参阅附录 A附录 B

下表列出了本教程中使用的文件:

文件 说明
wasb://flightdelay@hditutorialdata.blob.core.windows.net/flightdelays.hql Hive 作业所用的 HiveQL 脚本文件。 此脚本已上传到具有公共访问权限的 Azure Blob 存储帐户。 附录 B 提供了有关准备此文件以及将其上传到用户的 Azure Blob 存储帐户的说明。
wasb://flightdelay@hditutorialdata.blob.core.windows.net/2013Data Hive 作业的输入数据。 这些数据已上传到具有公共访问权限的 Azure Blob 存储帐户。 附录 A 提供了有关获取数据以及将数据上传到用户的 Azure Blob 存储帐户的说明。
\tutorials\flightdelays\output Hive 作业的输出路径。 默认容器用于存储输出数据。
\tutorials\flightdelays\jobstatus 默认容器上的 Hive 作业状态文件夹。

创建群集并运行 Hive/Sqoop 作业

Hadoop MapReduce 属于批处理。 运行 Hive 作业时,最具成本效益的方法是为作业创建群集,并在作业完成之后删除作业。 以下脚本覆盖了整个过程。 有关创建 HDInsight 群集和运行 Hive 作业的详细信息,请参阅在 HDInsight 中创建 Apache Hadoop 群集将 Apache Hive 与 HDInsight 配合使用

使用 Azure PowerShell 运行 Hive 查询

  1. 按照 附录 C中的说明,为 Sqoop 作业输出创建 Azure SQL 数据库和表。

  2. 打开 Windows PowerShell ISE 并运行以下脚本:

    $subscriptionID = "<Azure Subscription ID>"
    $nameToken = "<Enter an Alias>"
    
    ###########################################
    # You must configure the following variables
    # for an existing Azure SQL Database
    ###########################################
    $existingSqlDatabaseServerName = "<Azure SQL Database Server>"
    $existingSqlDatabaseLogin = "<Azure SQL Database Server Login>"
    $existingSqlDatabasePassword = "<Azure SQL Database Server login password>"
    $existingSqlDatabaseName = "<Azure SQL Database name>"
    
    $localFolder = "E:\Tutorials\Downloads\" # A temp location for copying files.
    $azcopyPath = "C:\Program Files (x86)\Microsoft SDKs\Azure\AzCopy" # depends on the version, the folder can be different
    
    ###########################################
    # (Optional) configure the following variables
    ###########################################
    
    $namePrefix = $nameToken.ToLower() + (Get-Date -Format "MMdd")
    
    $resourceGroupName = $namePrefix + "rg"
    $location = "CHINA EAST"
    
    $HDInsightClusterName = $namePrefix + "hdi"
    $httpUserName = "admin"
    $httpPassword = "<Enter the Password>"
    
    $defaultStorageAccountName = $namePrefix + "store"
    $defaultBlobContainerName = $HDInsightClusterName # use the cluster name
    
    $existingSqlDatabaseTableName = "AvgDelays"
    $sqlDatabaseConnectionString = "jdbc:sqlserver://$existingSqlDatabaseServerName.database.chinacloudapi.cn;user=$existingSqlDatabaseLogin@$existingSqlDatabaseServerName;password=$existingSqlDatabaseLogin;database=$existingSqlDatabaseName"
    
    $hqlScriptFile = "/tutorials/flightdelays/flightdelays.hql"
    
    $jobStatusFolder = "/tutorials/flightdelays/jobstatus"
    
    ###########################################
    # Login
    ###########################################
    try{
        $acct = Get-AzureRmSubscription
    }
    catch{
        Connect-AzureRmAccount -EnvironmentName AzureChinaCloud
    }
    Select-AzureRmSubscription -SubscriptionID $subscriptionID
    
    ###########################################
    # Create a new HDInsight cluster
    ###########################################
    
    # Create ARM group
    New-AzureRmResourceGroup -Name $resourceGroupName -Location $location
    
    # Create the default storage account
    New-AzureRmStorageAccount -ResourceGroupName $resourceGroupName -Name $defaultStorageAccountName -Location $location -Type Standard_LRS
    
    # Create the default Blob container
    $defaultStorageAccountKey = (Get-AzureRmStorageAccountKey -ResourceGroupName $resourceGroupName -Name $defaultStorageAccountName)[0].Value
    $defaultStorageAccountContext = New-AzureStorageContext -StorageAccountName $defaultStorageAccountName -StorageAccountKey $defaultStorageAccountKey
    New-AzureStorageContainer -Name $defaultBlobContainerName -Context $defaultStorageAccountContext
    
    # Create the HDInsight cluster
    $pw = ConvertTo-SecureString -String $httpPassword -AsPlainText -Force
    $httpCredential = New-Object System.Management.Automation.PSCredential($httpUserName,$pw)
    
    New-AzureRmHDInsightCluster `
        -ResourceGroupName $resourceGroupName `
        -ClusterName $HDInsightClusterName `
        -Location $location `
        -ClusterType Hadoop `
        -OSType Windows `
        -ClusterSizeInNodes 2 `
        -HttpCredential $httpCredential `
        -DefaultStorageAccountName "$defaultStorageAccountName.blob.core.chinacloudapi.cn" `
        -DefaultStorageAccountKey $defaultStorageAccountKey `
        -DefaultStorageContainer $existingDefaultBlobContainerName
    
    ###########################################
    # Prepare the HiveQL script and source data
    ###########################################
    
    # Create the temp location
    New-Item -Path $localFolder -ItemType Directory -Force
    
    # Download the sample file from Azure Blob storage
    $context = New-AzureStorageContext -StorageAccountName "hditutorialdata" -Anonymous
    $blobs = Get-AzureStorageBlob -Container "flightdelay" -Context $context
    #$blobs | Get-AzureStorageBlobContent -Context $context -Destination $localFolder
    
    # Upload data to default container
    
    $azcopycmd = "cmd.exe /C '$azcopyPath\azcopy.exe' /S /Source:'$localFolder' /Dest:'https://$defaultStorageAccountName.blob.core.chinacloudapi.cn/$defaultBlobContainerName/tutorials/flightdelays' /DestKey:$defaultStorageAccountKey"
    
    Invoke-Expression -Command:$azcopycmd
    
    ###########################################
    # Submit the Hive job
    ###########################################
    Use-AzureRmHDInsightCluster -ClusterName $HDInsightClusterName -HttpCredential $httpCredential
    $response = Invoke-AzureRmHDInsightHiveJob `
                    -Files $hqlScriptFile `
                    -DefaultContainer $defaultBlobContainerName `
                    -DefaultStorageAccountName $defaultStorageAccountName `
                    -DefaultStorageAccountKey $defaultStorageAccountKey `
                    -StatusFolder $jobStatusFolder
    
    write-Host $response
    
    ###########################################
    # Submit the Sqoop job
    ###########################################
    $exportDir = "wasb://$defaultBlobContainerName@$defaultStorageAccountName.blob.core.chinacloudapi.cn/tutorials/flightdelays/output"
    
    $sqoopDef = New-AzureRmHDInsightSqoopJobDefinition `
                    -Command "export --connect $sqlDatabaseConnectionString --table $sqlDatabaseTableName --export-dir $exportDir --fields-terminated-by \001 "
    $sqoopJob = Start-AzureRmHDInsightJob `
                    -ResourceGroupName $resourceGroupName `
                    -ClusterName $hdinsightClusterName `
                    -HttpCredential $httpCredential `
                    -JobDefinition $sqoopDef #-Debug -Verbose
    
    Wait-AzureRmHDInsightJob `
        -ResourceGroupName $resourceGroupName `
        -ClusterName $HDInsightClusterName `
        -HttpCredential $httpCredential `
        -WaitTimeoutInSeconds 3600 `
        -Job $sqoopJob.JobId
    
    Get-AzureRmHDInsightJobOutput `
        -ResourceGroupName $resourceGroupName `
        -ClusterName $hdinsightClusterName `
        -HttpCredential $httpCredential `
        -DefaultContainer $existingDefaultBlobContainerName `
        -DefaultStorageAccountName $defaultStorageAccountName `
        -DefaultStorageAccountKey $defaultStorageAccountKey `
        -JobId $sqoopJob.JobId `
        -DisplayOutputType StandardError
    
    ###########################################
    # Delete the cluster
    ###########################################
    Remove-AzureRmHDInsightCluster -ResourceGroupName $resourceGroupName -ClusterName $hdinsightClusterName
    
  3. 连接到 SQL 数据库,并在 AvgDelays 表中按城市查看平均航班延迟:

    HDI.FlightDelays.AvgDelays.Dataset


附录 A - 将航班延迟数据上传到 Azure Blob 存储

上传数据文件和 HiveQL 脚本文件(请参阅附录 B)需要进行规划。 思路是在创建 HDInsight 群集和运行 Hive 作业之前存储数据文件和 HiveQL 文件。 可以使用两个选项:

  • 使用由 HDInsight 群集用作默认文件系统的同一 Azure 存储帐户。 由于 HDInsight 群集将具有存储帐户访问密钥,因此你无需进行任何其他更改。
  • 使用与 HDInsight 群集默认文件系统不同的 Azure 存储帐户。 如果选择了此项,则必须修改创建 HDInsight 群集和运行 Apache Hive/Sqoop 作业中的 Windows PowerShell 脚本的创建部分,以链接该存储帐户作为额外的存储帐户。 有关说明,请参阅在 HDInsight 中创建 Apache Hadoop 群集。 这样,HDInsight 群集就会知道存储帐户的访问密钥。

Note

数据文件的 WASB 路径会在 HiveQL 脚本文件中进行硬编码。 必须相应地进行更新。

下载航班数据

  1. 浏览到 美国研究与技术创新管理部门 - 运输统计局

  2. 在该页面上,选择以下值:

    名称
    筛选年份 2013
    筛选期间 1 月
    字段 YearFlightDateUniqueCarrierCarrierFlightNumOriginAirportIDOriginOriginCityNameOriginStateDestAirportIDDestDestCityNameDestStateDepDelayMinutesArrDelayArrDelayMinutesCarrierDelayWeatherDelayNASDelaySecurityDelayLateAircraftDelay(清除其他所有字段)
  3. 单击“下载”。

  4. 将文件解压缩到 C:\Tutorials\FlightDelay\2013Data 文件夹。 每个文件均为 CSV 文件且大小约为 60GB。

  5. 将文件重命名为其包含的数据所对应的月份的名称。 例如,将包含 1 月份数据的文件命名为 January.csv

  6. 重复步骤 2 和步骤 5 为 2013 年中的 12 个月分别下载一个对应的文件。 完成本教程至少需要一个文件。

将航班延迟数据上传到 Azure Blob 存储

  1. 准备参数:

    变量名 注释
    $storageAccountName 数据上传的目标 Azure 存储帐户。
    $blobContainerName 数据上传的目标 Blob 容器。
  2. 打开 Azure PowerShell ISE。

  3. 将以下脚本粘贴到脚本窗格中:

    [CmdletBinding()]
    Param(
    
        [Parameter(Mandatory=$True,
                    HelpMessage="Enter the Azure storage account name for creating a new HDInsight cluster. If the account doesn't exist, the script will create one.")]
        [String]$storageAccountName,
    
        [Parameter(Mandatory=$True,
                    HelpMessage="Enter the Azure blob container name for creating a new HDInsight cluster. If not specified, the HDInsight cluster name will be used.")]
        [String]$blobContainerName
    )
    
    #Region - Variables
    $localFolder = "C:\Tutorials\FlightDelay\2013Data"  # The source folder
    $destFolder = "tutorials/flightdelay/2013data"     #The blob name prefix for the files to be uploaded
    #EndRegion
    
    #Region - Connect to Azure subscription
    Write-Host "`nConnecting to your Azure subscription ..." -ForegroundColor Green
    try{Get-AzureRmContext}
    catch{Connect-AzureRmAccount -EnvironmentName AzureChinaCloud}
    #EndRegion
    
    #Region - Validate user input
    Write-Host "`nValidating the Azure Storage account and the Blob container..." -ForegroundColor Green
    # Validate the Storage account
    if (-not (Get-AzureRmStorageAccount|Where-Object{$_.StorageAccountName -eq $storageAccountName}))
    {
        Write-Host "The storage account, $storageAccountName, doesn't exist." -ForegroundColor Red
        exit
    }
    else{
        $resourceGroupName = (Get-AzureRmStorageAccount|Where-Object{$_.StorageAccountName -eq $storageAccountName}).ResourceGroupName
    }
    
    # Validate the container
    $storageAccountKey = (Get-AzureRmStorageAccountKey -StorageAccountName $storageAccountName -ResourceGroupName $resourceGroupName)[0].Value
    $storageContext = New-AzureStorageContext -StorageAccountName $storageAccountName -StorageAccountKey $storageAccountKey
    
    if (-not (Get-AzureStorageContainer -Context $storageContext |Where-Object{$_.Name -eq $blobContainerName}))
    {
        Write-Host "The Blob container, $blobContainerName, doesn't exist" -ForegroundColor Red
        Exit
    }
    #EngRegion
    
    #Region - Copy the file from local workstation to Azure Blob storage
    if (test-path -Path $localFolder)
    {
        foreach ($item in Get-ChildItem -Path $localFolder){
            $fileName = "$localFolder\$item"
            $blobName = "$destFolder/$item"
    
            Write-Host "Copying $fileName to $blobName" -ForegroundColor Green
    
            Set-AzureStorageBlobContent -File $fileName -Container $blobContainerName -Blob $blobName -Context $storageContext
        }
    }
    else
    {
        Write-Host "The source folder on the workstation doesn't exist" -ForegroundColor Red
    }
    
    # List the uploaded files on HDInsight
    Get-AzureStorageBlob -Container $blobContainerName  -Context $storageContext -Prefix $destFolder
    #EndRegion
    
  4. F5 运行脚本。

如果选择使用其他方法上传文件,请确保文件路径是 tutorials/flightdelay/data。 用于访问文件的语法是:

wasb://<ContainerName>@<StorageAccountName>.blob.core.chinacloudapi.cn/tutorials/flightdelay/data

路径 tutorials/flightdelay/data 是上传文件时创建的虚拟文件夹。 验证是否有 12 个文件,每个月对应一个文件。

Note

必须更新 Hive 查询,才能从新位置进行读取。

必须配置容器访问权限,使其成为公用,或者将存储帐户绑定到 HDInsight 群集。 否则,Hive 查询字符串无法访问数据文件。


附录 B - 创建并上传 HiveQL 脚本

使用 Azure PowerShell,可以一次运行多个 HiveQL 语句,或者将 HiveQL 语句打包到一个脚本文件中。 本部分说明如何创建 HiveQL 脚本,以及如何使用 Azure PowerShell 将脚本上传到 Azure Blob 存储。 Hive 要求 HiveQL 脚本必须存储在 Azure Blob 存储中。

HiveQL 脚本执行以下操作:

  1. 删除 delays_raw 表(如果该表已存在)。
  2. 创建 delays_raw 外部 Hive 表,并将该表指向航班延误文件所在的 Blob 存储位置。 此查询指定用“,”分隔字段并用“\n”终止行。 这在字段值包含逗号时将导致出现问题,因为 Hive 无法区分逗号是字段分隔符还是字段值的一部分(在 ORIGIN_CITY_NAME 和 DEST_CITY_NAME 的字段值中属于此情况)。 为了解决此问题,此查询创建 TEMP 列来保存未正确拆分到列中的数据。
  3. 删除 delays 表(如果该表已存在)。
  4. 创建 delays 表。 这适用于在进一步处理前清理数据。 此查询将从 delays_raw 表创建一个新表 delays。 请注意,将不会复制 TEMP 列(如前所述),并且将使用 substring 函数从数据中删除引号标记。
  5. 计算平均天气延迟,并按城市名对结果进行分组。 它还会将结果输出到 Blob 存储。 请注意,查询将从数据中删除撇号,并且将排除 weather_delay 的值为 null 的行。 由于本教程中稍后使用的 Sqoop 在默认情况下无法适当地处理这些值,因此这是必要的。

如需 HiveQL 命令的完整列表,请参阅 Apache Hive 数据定义语言。 每条 HiveQL 命令必须以分号结尾。

创建 HiveQL 脚本文件

  1. 准备参数:

    变量名 注释
    $storageAccountName HiveQL 脚本上传的目标 Azure 存储帐户。
    $blobContainerName HiveQL 脚本上传的目标 Blob 容器。
  2. 打开 Azure PowerShell ISE。

  3. 将以下脚本复制并粘贴到脚本窗格中:

    [CmdletBinding()]
    Param(
    
        # Azure Blob storage variables
        [Parameter(Mandatory=$True,
                    HelpMessage="Enter the Azure storage account name for creating a new HDInsight cluster. If the account doesn't exist, the script will create one.")]
        [String]$storageAccountName,
    
        [Parameter(Mandatory=$True,
                    HelpMessage="Enter the Azure blob container name for creating a new HDInsight cluster. If not specified, the HDInsight cluster name will be used.")]
        [String]$blobContainerName
    )
    
    #region - Define variables
    # Treat all errors as terminating
    $ErrorActionPreference = "Stop"
    
    # The HiveQL script file is exported as this file before it's uploaded to Blob storage
    $hqlLocalFileName = "e:\tutorials\flightdelay\flightdelays.hql"
    
    # The HiveQL script file will be uploaded to Blob storage as this blob name
    $hqlBlobName = "tutorials/flightdelay/flightdelays.hql"
    
    # These two constants are used by the HiveQL script file
    #$srcDataFolder = "tutorials/flightdelay/data"
    $dstDataFolder = "/tutorials/flightdelay/output"
    #endregion
    
    #Region - Connect to Azure subscription
    Write-Host "`nConnecting to your Azure subscription ..." -ForegroundColor Green
    try{Get-AzureRmContext}
    catch{Connect-AzureRmAccount -EnvironmentName AzureChinaCloud}
    #EndRegion
    
    #Region - Validate user input
    Write-Host "`nValidating the Azure Storage account and the Blob container..." -ForegroundColor Green
    # Validate the Storage account
    if (-not (Get-AzureRmStorageAccount|Where-Object{$_.StorageAccountName -eq $storageAccountName}))
    {
        Write-Host "The storage account, $storageAccountName, doesn't exist." -ForegroundColor Red
        exit
    }
    else{
        $resourceGroupName = (Get-AzureRmStorageAccount|Where-Object{$_.StorageAccountName -eq $storageAccountName}).ResourceGroupName
    }
    
    # Validate the container
    $storageAccountKey = (Get-AzureRmStorageAccountKey -StorageAccountName $storageAccountName -ResourceGroupName $resourceGroupName)[0].Value
    $storageContext = New-AzureStorageContext -StorageAccountName $storageAccountName -StorageAccountKey $storageAccountKey
    
    if (-not (Get-AzureStorageContainer -Context $storageContext |Where-Object{$_.Name -eq $blobContainerName}))
    {
        Write-Host "The Blob container, $blobContainerName, doesn't exist" -ForegroundColor Red
        Exit
    }
    #EngRegion
    
    #region - Validate the file and file path
    
    # Check if a file with the same file name already exists on the workstation
    Write-Host "`nvalidating the folder structure on the workstation for saving the HQL script file ..."  -ForegroundColor Green
    if (test-path $hqlLocalFileName){
    
        $isDelete = Read-Host 'The file, ' $hqlLocalFileName ', exists.  Do you want to overwrite it? (Y/N)'
    
        if ($isDelete.ToLower() -ne "y")
        {
            Exit
        }
    }
    
    # Create the folder if it doesn't exist
    $folder = split-path $hqlLocalFileName
    if (-not (test-path $folder))
    {
        Write-Host "`nCreating folder, $folder ..." -ForegroundColor Green
    
        new-item $folder -ItemType directory
    }
    #end region
    
    #region - Write the Hive script into a local file
    Write-Host "`nWriting the Hive script into a file on your workstation ..." `
                -ForegroundColor Green
    
    $hqlDropDelaysRaw = "DROP TABLE delays_raw;"
    
    $hqlCreateDelaysRaw = "CREATE EXTERNAL TABLE delays_raw (" +
            "YEAR string, " +
            "FL_DATE string, " +
            "UNIQUE_CARRIER string, " +
            "CARRIER string, " +
            "FL_NUM string, " +
            "ORIGIN_AIRPORT_ID string, " +
            "ORIGIN string, " +
            "ORIGIN_CITY_NAME string, " +
            "ORIGIN_CITY_NAME_TEMP string, " +
            "ORIGIN_STATE_ABR string, " +
            "DEST_AIRPORT_ID string, " +
            "DEST string, " +
            "DEST_CITY_NAME string, " +
            "DEST_CITY_NAME_TEMP string, " +
            "DEST_STATE_ABR string, " +
            "DEP_DELAY_NEW float, " +
            "ARR_DELAY_NEW float, " +
            "CARRIER_DELAY float, " +
            "WEATHER_DELAY float, " +
            "NAS_DELAY float, " +
            "SECURITY_DELAY float, " +
            "LATE_AIRCRAFT_DELAY float) " +
        "ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' " +
        "LINES TERMINATED BY '\n' " +
        "STORED AS TEXTFILE " +
        "LOCATION 'wasb://flightdelay@hditutorialdata.blob.core.chinacloudapi.cn/2013Data';"
    
    $hqlDropDelays = "DROP TABLE delays;"
    
    $hqlCreateDelays = "CREATE TABLE delays AS " +
        "SELECT YEAR AS year, " +
            "FL_DATE AS flight_date, " +
            "substring(UNIQUE_CARRIER, 2, length(UNIQUE_CARRIER) -1) AS unique_carrier, " +
            "substring(CARRIER, 2, length(CARRIER) -1) AS carrier, " +
            "substring(FL_NUM, 2, length(FL_NUM) -1) AS flight_num, " +
            "ORIGIN_AIRPORT_ID AS origin_airport_id, " +
            "substring(ORIGIN, 2, length(ORIGIN) -1) AS origin_airport_code, " +
            "substring(ORIGIN_CITY_NAME, 2) AS origin_city_name, " +
            "substring(ORIGIN_STATE_ABR, 2, length(ORIGIN_STATE_ABR) -1)  AS origin_state_abr, " +
            "DEST_AIRPORT_ID AS dest_airport_id, " +
            "substring(DEST, 2, length(DEST) -1) AS dest_airport_code, " +
            "substring(DEST_CITY_NAME,2) AS dest_city_name, " +
            "substring(DEST_STATE_ABR, 2, length(DEST_STATE_ABR) -1) AS dest_state_abr, " +
            "DEP_DELAY_NEW AS dep_delay_new, " +
            "ARR_DELAY_NEW AS arr_delay_new, " +
            "CARRIER_DELAY AS carrier_delay, " +
            "WEATHER_DELAY AS weather_delay, " +
            "NAS_DELAY AS nas_delay, " +
            "SECURITY_DELAY AS security_delay, " +
            "LATE_AIRCRAFT_DELAY AS late_aircraft_delay " +
        "FROM delays_raw;"
    
    $hqlInsertLocal = "INSERT OVERWRITE DIRECTORY '$dstDataFolder' " +
        "SELECT regexp_replace(origin_city_name, '''', ''), " +
            "avg(weather_delay) " +
        "FROM delays " +
        "WHERE weather_delay IS NOT NULL " +
        "GROUP BY origin_city_name;"
    
    $hqlScript = $hqlDropDelaysRaw + $hqlCreateDelaysRaw + $hqlDropDelays + $hqlCreateDelays + $hqlInsertLocal
    
    $hqlScript | Out-File $hqlLocalFileName -Encoding ascii -Force
    #endregion
    
    #region - Upload the Hive script to the default Blob container
    Write-Host "`nUploading the Hive script to the default Blob container ..." -ForegroundColor Green
    
    # Create a storage context object
    $storageAccountKey = (Get-AzureRmStorageAccountKey -StorageAccountName $storageAccountName -ResourceGroupName $resourceGroupName)[0].Value
    $destContext = New-AzureStorageContext -StorageAccountName $storageAccountName -StorageAccountKey $storageAccountKey
    
    # Upload the file from local workstation to Blob storage
    Set-AzureStorageBlobContent -File $hqlLocalFileName -Container $blobContainerName -Blob $hqlBlobName -Context $destContext
    #endregion
    
    Write-host "`nEnd of the PowerShell script" -ForegroundColor Green
    

    该脚本中使用了以下变量:

    • $hqlLocalFileName - 该脚本会先将 HiveQL 脚本文件保存在本地,然后才上传到 Blob 存储。 这是文件名。 默认值是 C:\tutorials\flightdelay\flightdelays.hql
    • $hqlBlobName - 这是 Azure Blob 存储中使用的 HiveQL 脚本文件 Blob 名称。 默认值是 tutorials/flightdelay/flightdelays.hql。 因为文件会直接写入 Azure Blob 存储,所以 Blob 名称的开头不是“/”。 如果要从 Blob 存储访问文件,必须在文件名的开头添加“/”。
    • $srcDataFolder 和 $dstDataFolder - = "tutorials/flightdelay/data" = "tutorials/flightdelay/output"

附录 C - 针对 Sqoop 作业输出准备 Azure SQL 数据库

准备 SQL 数据库(将此部分与 Sqoop 脚本合并)

  1. 准备参数:

    变量名 注释
    $sqlDatabaseServerName Azure SQL 数据库服务器的名称。 不输入任何值会创建新的服务器。
    $sqlDatabaseUsername Azure SQL 数据库服务器登录名。 如果 $sqlDatabaseServerName 是现有的服务器,登录名和登录密码将用来向服务器进行身份验证。 否则将其用于创建新的服务器。
    $sqlDatabasePassword Azure SQL 数据库服务器登录密码。
    $sqlDatabaseLocation 只有在创建新的 Azure 数据库服务器时才会使用此值。
    $sqlDatabaseName Sqoop 作业的 AvgDelays 表的 SQL 数据库。 保留空白会创建名为 HDISqoop 的数据库。 Sqooop 作业输出的表名称为 AvgDelays。
  2. 打开 Azure PowerShell ISE。

  3. 将以下脚本复制并粘贴到脚本窗格中:

    [CmdletBinding()]
    Param(
    
        # Azure Resource group variables
        [Parameter(Mandatory=$True,
                HelpMessage="Enter the Azure resource group name. It will be created if it doesn't exist.")]
        [String]$resourceGroupName,
    
        # SQL database server variables
        [Parameter(Mandatory=$True,
                HelpMessage="Enter the Azure SQL Database Server Name. It will be created if it doesn't exist.")]
        [String]$sqlDatabaseServer,
    
        [Parameter(Mandatory=$True,
                HelpMessage="Enter the Azure SQL Database admin user.")]
        [String]$sqlDatabaseLogin,
    
        [Parameter(Mandatory=$True,
                HelpMessage="Enter the Azure SQL Database admin user password.")]
        [String]$sqlDatabasePassword,
    
        [Parameter(Mandatory=$True,
                HelpMessage="Enter the region to create the Database in.")]
        [String]$sqlDatabaseLocation,   #For example, China North.
    
        # SQL database variables
        [Parameter(Mandatory=$True,
                HelpMessage="Enter the database name. It will be created if it doesn't exist.")]
        [String]$sqlDatabaseName # specify the database name if you have one created. Otherwise use "" to have the script create one for you.
    )
    
    # Treat all errors as terminating
    $ErrorActionPreference = "Stop"
    
    #region - Constants and variables
    
    # IP address REST service used for retrieving external IP address and creating firewall rules
    [String]$ipAddressRestService = "https://bot.whatismyipaddress.com"
    [String]$fireWallRuleName = "FlightDelay"
    
    # SQL database variables
    [String]$sqlDatabaseMaxSizeGB = 10
    
    #SQL query string for creating AvgDelays table
    [String]$sqlDatabaseTableName = "AvgDelays"
    [String]$sqlCreateAvgDelaysTable = " CREATE TABLE [dbo].[$sqlDatabaseTableName](
                [origin_city_name] [nvarchar](50) NOT NULL,
                [weather_delay] float,
            CONSTRAINT [PK_$sqlDatabaseTableName] PRIMARY KEY CLUSTERED
            (
                [origin_city_name] ASC
            )
            )"
    #endregion
    
    #Region - Connect to Azure subscription
    Write-Host "`nConnecting to your Azure subscription ..." -ForegroundColor Green
    try{Get-AzureRmContext}
    catch{Connect-AzureRmAccount -EnvironmentName AzureChinaCloud}
    #EndRegion
    
    #region - Create and validate Azure resource group
    try{
        Get-AzureRmResourceGroup -Name $resourceGroupName
    }
    catch{
        New-AzureRmResourceGroup -Name $resourceGroupName -Location $sqlDatabaseLocation
    }
    
    #EndRegion
    
    #region - Create and validate Azure SQL database server
    try{
        Get-AzureRmSqlServer -ServerName $sqlDatabaseServer -ResourceGroupName $resourceGroupName}
    catch{
        Write-Host "`nCreating SQL Database server ..."  -ForegroundColor Green
    
        $sqlDatabasePW = ConvertTo-SecureString -String $sqlDatabasePassword -AsPlainText -Force
        $credential = New-Object System.Management.Automation.PSCredential($sqlDatabaseLogin,$sqlDatabasePW)
    
        $sqlDatabaseServer = (New-AzureRmSqlServer -ResourceGroupName $resourceGroupName -ServerName $sqlDatabaseServer -SqlAdministratorCredentials $credential -Location $sqlDatabaseLocation).ServerName
        Write-Host "`tThe new SQL database server name is $sqlDatabaseServer." -ForegroundColor Cyan
    
        Write-Host "`nCreating firewall rule, $fireWallRuleName ..." -ForegroundColor Green
        $workstationIPAddress = Invoke-RestMethod $ipAddressRestService
        New-AzureRmSqlServerFirewallRule -ResourceGroupName $resourceGroupName -ServerName $sqlDatabaseServer -FirewallRuleName "$fireWallRuleName-workstation" -StartIpAddress $workstationIPAddress -EndIpAddress $workstationIPAddress
    
        #To allow other Azure services to access the server add a firewall rule and set both the StartIpAddress and EndIpAddress to 0.0.0.0. Note that this allows Azure traffic from any Azure subscription to access the server.
        New-AzureRmSqlServerFirewallRule -ResourceGroupName $resourceGroupName -ServerName $sqlDatabaseServer -FirewallRuleName "$fireWallRuleName-Azureservices" -StartIpAddress "0.0.0.0" -EndIpAddress "0.0.0.0"
    }
    
    #endregion
    
    #region - Create and validate Azure SQL database
    
    try {
        Get-AzureRmSqlDatabase -ResourceGroupName $resourceGroupName -ServerName $sqlDatabaseServer -DatabaseName $sqlDatabaseName
    }
    catch {
        Write-Host "`nCreating SQL Database, $sqlDatabaseName ..."  -ForegroundColor Green
        New-AzureRMSqlDatabase -ResourceGroupName $resourceGroupName -ServerName $sqlDatabaseServer -DatabaseName $sqlDatabaseName -Edition "Standard" -RequestedServiceObjectiveName "S1"
    }
    
    #endregion
    
    #region -  Execute an SQL command to create the AvgDelays table
    
    Write-Host "`nCreating SQL Database table ..."  -ForegroundColor Green
    $conn = New-Object System.Data.SqlClient.SqlConnection
    $conn.ConnectionString = "Data Source=$sqlDatabaseServer.database.chinacloudapi.cn;Initial Catalog=$sqlDatabaseName;User ID=$sqlDatabaseLogin;Password=$sqlDatabasePassword;Encrypt=true;Trusted_Connection=false;"
    $conn.open()
    $cmd = New-Object System.Data.SqlClient.SqlCommand
    $cmd.connection = $conn
    $cmd.commandtext = $sqlCreateAvgDelaysTable
    $cmd.executenonquery()
    
    $conn.close()
    
    Write-host "`nEnd of the PowerShell script" -ForegroundColor Green
    

    Note

    该脚本使用具象状态传输 (REST) 服务 https://bot.whatismyipaddress.com 来检索外部 IP 地址。 IP 地址用于创建 SQL 数据库服务器的防火墙规则。

    该脚本中使用的某些变量:

    • $ipAddressRestService - 默认值为 https://bot.whatismyipaddress.com。 这是用来获取外部 IP 地址的公共 IP 地址 REST 服务。 可根据需要使用其他服务。 使用此服务检索的外部 IP 地址将用于创建 Azure SQL 数据库服务器的防火墙规则,使用户能够从工作站访问数据库(通过 Windows PowerShell 脚本)。
    • $fireWallRuleName - 这是 Azure SQL 数据库服务器的防火墙规则名称。 默认名称为 FlightDelay。 可根据需要对其进行重命名。
    • $sqlDatabaseMaxSizeGB - 只有在创建新的 Azure SQL 数据库服务器时才会使用此值。 默认值为 10GB。 10GB 对于本教程来说已足够。
    • $sqlDatabaseName - 只有在创建新的 Azure SQL 数据库时才会使用此值。 默认值为 HDISqoop。 如果将它重命名,则必须相应地更新 Sqoop Windows PowerShell 脚本。
  4. F5 运行脚本。

  5. 验证脚本输出。 确保已成功运行脚本。

后续步骤

现在你已了解了如何执行以下操作:将文件上传到 Azure Blob 存储、使用 Azure Blob 存储中的数据填充 Apache Hive 表、运行 Apache Hive 查询以及使用 Sqoop 将数据从 Apache Hadoop HDFS 导出到 Azure SQL 数据库。 要了解更多信息,请参阅下列文章: