配置 PolyBase 以访问 Hadoop 中的外部数据

适用于: SQL Server - 仅限 Windows Azure SQL 托管实例

本文介绍如何使用 SQL Server 实例上的 PolyBase 来查询 Hadoop 中的外部数据。

注意

从 SQL Server 2022 (16.x) 开始,PolyBase 不再支持 Hadoop。

先决条件

  • 如果尚未安装 PolyBase,请参阅 PolyBase 安装。 这篇安装文章介绍了安装的先决条件。
  • PolyBase 支持两个 Hadoop 提供程序:Hortonworks 数据平台 (HDP) 和 Cloudera 分布式 Hadoop (CDH)。 Hadoop 遵循其新版本的“Major.Minor.Version”模式,且主要和次要版本中支持的所有版本均受支持。 有关支持的 Hortonworks 数据平台 (HDP) 和 Cloudera Distributed Hadoop (CDH) 版本的信息,请参阅 PolyBase 连接配置

注意

从 SQL Server 2016 SP1 CU7 和 SQL Server 2017 CU3 开始,PolyBase 支持 Hadoop 加密区域。 如果使用 PolyBase 横向扩展组,所有计算节点还必须位于包含对 Hadoop 加密区域的支持的内部版本上。

配置 Hadoop 连接

首先,配置 SQL Server PolyBase 以使用特定的 Hadoop 提供程序。

  1. 使用“hadoop connectivity”运行 sp_configure 并为提供程序设置适当的值。 若要为提供程序查找值,请参阅 PolyBase 连接配置

    -- Values map to various external data sources.  
    -- Example: value 7 stands for Hortonworks HDP 2.1 to 2.6 on Linux,
    -- 2.1 to 2.3 on Windows Server, and Azure Blob Storage  
    sp_configure @configname = 'hadoop connectivity', @configvalue = 7;
    GO
    
    RECONFIGURE
    GO
    
  2. 必须使用 services.msc 重启 SQL Server。 重启 SQL Server 会重启这些服务:

    • SQL Server PolyBase 数据移动服务
    • SQL Server PolyBase 引擎

    在 services.msc 中停止和启动 PolyBase 服务

启用下推计算

若要提高查询性能,请对 Hadoop 群集启用下推计算:

  1. 在 SQL Server 的安装路径中查找文件 yarn-site.xml 。 通常情况下,该路径为:

    C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn\PolyBase\Hadoop\conf\  
    
  2. 对于 Hadoop 计算机,在 Hadoop 配置目录中查找类似文件。 在文件中,查找并复制配置密钥 yarn.application.classpath 的值。

  3. 对于 SQL Server 计算机,在 yarn-site.xml 文件中,查找 yarn.application.classpath 属性。 将 Hadoop 计算机的值粘贴到值元素中。

  4. 对于所有 CDH 5.X 版本,都需要将 mapreduce.application.classpath 配置参数添加到 yarn-site.xml 文件的末尾或添加到 mapred-site.xml 文件中。 HortonWorks 在 yarn.application.classpath 配置中包括了这些配置。 有关示例,请参阅 PolyBase 配置

重要

若要将计算下推功能用于 Hadoop,目标 Hadoop 群集必须具有 HDFS、YARN 和 MapReduce 的核心组件且已启用作业历史记录服务器。 PolyBase 通过 MapReduce 提交下推查询,并且从作业历史记录服务器拉取状态。 缺少任一组件,查询都会失败。

配置外部表

若要查询 Hadoop 数据源中的数据,必须定义外部表以在 Transact-SQL 查询中使用。 以下步骤介绍如何配置外部表。

  1. 创建数据库主密钥(如果尚不存在)。 这是加密凭据密钥所必需的。

     CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';  
    

    参数

    PASSWORD ='password'

    用于加密数据库主密钥的密码。 密码必须符合托管 SQL Server 实例的计算机的 Windows 密码策略要求。

  2. 为受 Kerberos 保护的 Hadoop 群集创建数据库范围凭据。

    -- IDENTITY: the Kerberos user name.  
    -- SECRET: the Kerberos password  
    CREATE DATABASE SCOPED CREDENTIAL HadoopUser1
    WITH IDENTITY = '<hadoop_user_name>', Secret = '<hadoop_password>';  
    
  3. 使用 CREATE EXTERNAL DATA SOURCE 创建外部数据源。

    -- LOCATION (Required) : Hadoop Name Node IP address and port.  
    -- RESOURCE MANAGER LOCATION (Optional): Hadoop Resource Manager location to enable pushdown computation.  
    -- CREDENTIAL (Optional):  the database scoped credential, created above.  
    CREATE EXTERNAL DATA SOURCE MyHadoopCluster WITH (  
          TYPE = HADOOP,
          LOCATION ='hdfs://10.xxx.xx.xxx:xxxx',
          RESOURCE_MANAGER_LOCATION = '10.xxx.xx.xxx:xxxx',
          CREDENTIAL = HadoopUser1
    );  
    
  4. 使用 CREATE EXTERNAL FILE FORMAT 创建外部文件格式。

    -- FORMAT TYPE: Type of format in Hadoop (DELIMITEDTEXT,  RCFILE, ORC, PARQUET).
    CREATE EXTERNAL FILE FORMAT TextFileFormat WITH (  
          FORMAT_TYPE = DELIMITEDTEXT,
          FORMAT_OPTIONS (FIELD_TERMINATOR ='|',
                USE_TYPE_DEFAULT = TRUE))
    
  5. 使用 CREATE EXTERNAL TABLE 创建指向存储在 Hadoop 中的数据的外部表。 在此示例中,外部数据包含汽车传感器数据。

    -- LOCATION: path to file or directory that contains the data (relative to HDFS root).  
    CREATE EXTERNAL TABLE [dbo].[CarSensor_Data] (  
          [SensorKey] int NOT NULL,
          [CustomerKey] int NOT NULL,
          [GeographyKey] int NULL,
          [Speed] float NOT NULL,
          [YearMeasured] int NOT NULL  
    )  
    WITH (LOCATION='/Demo/',
          DATA_SOURCE = MyHadoopCluster,  
          FILE_FORMAT = TextFileFormat  
    );  
    
  6. 在外部表上创建统计信息。

    CREATE STATISTICS StatsForSensors on CarSensor_Data(CustomerKey, Speed)  
    

PolyBase Queries

PolyBase 适用于三个函数:

  • 对外部表的即席查询。
  • 导入数据。
  • 导出数据。

下面的查询提供了虚构汽车传感器数据示例。

即席查询

下面的即席查询联接与 Hadoop 数据的关系。 它选择驾驶速度超过 35 mph 的客户,将 SQL Server 中存储的结构化客户数据与 Hadoop 中存储的汽车传感器数据相联接。

SELECT DISTINCT Insured_Customers.FirstName,Insured_Customers.LastName,
       Insured_Customers. YearlyIncome, CarSensor_Data.Speed  
FROM Insured_Customers, CarSensor_Data  
WHERE Insured_Customers.CustomerKey = CarSensor_Data.CustomerKey and CarSensor_Data.Speed > 35
ORDER BY CarSensor_Data.Speed DESC  
OPTION (FORCE EXTERNALPUSHDOWN);   -- or OPTION (DISABLE EXTERNALPUSHDOWN)  

导入数据

下面的查询将外部数据导入 SQL Server。 此示例将快速驾驶员数据导入 SQL Server 以进一步深入分析。 为了提高性能,该示例使用列存储索引。

SELECT DISTINCT
      Insured_Customers.FirstName, Insured_Customers.LastName,   
      Insured_Customers.YearlyIncome, Insured_Customers.MaritalStatus  
INTO Fast_Customers from Insured_Customers INNER JOIN   
(  
      SELECT * FROM CarSensor_Data where Speed > 35   
) AS SensorD  
ON Insured_Customers.CustomerKey = SensorD.CustomerKey  
ORDER BY YearlyIncome  
  
CREATE CLUSTERED COLUMNSTORE INDEX CCI_FastCustomers ON Fast_Customers;  

导出数据

以下查询将数据从 SQL Server 导出到 Hadoop。 若要执行此操作,首先需要启用 PolyBase 导出。 然后,在向目标导出数据之前,为目标创建一个外部表。

-- Enable INSERT into external table  
sp_configure 'allow polybase export', 1;  
reconfigure  
  
-- Create an external table.
CREATE EXTERNAL TABLE [dbo].[FastCustomers2009] (  
      [FirstName] char(25) NOT NULL,
      [LastName] char(25) NOT NULL,
      [YearlyIncome] float NULL,
      [MaritalStatus] char(1) NOT NULL  
)  
WITH (  
      LOCATION='/old_data/2009/customerdata',  
      DATA_SOURCE = HadoopHDP2,  
      FILE_FORMAT = TextFileFormat,  
      REJECT_TYPE = VALUE,  
      REJECT_VALUE = 0  
);  

-- Export data: Move old data to Hadoop while keeping it query-able via an external table.  
INSERT INTO dbo.FastCustomer2009  
SELECT T.* FROM Insured_Customers T1 JOIN CarSensor_Data T2  
ON (T1.CustomerKey = T2.CustomerKey)  
WHERE T2.YearMeasured = 2009 and T2.Speed > 40;  

查看 SSMS 中的 PolyBase 对象

在 SSMS 中,外部表在单独的文件夹“外部表” 中显示。 外部数据源和外部文件格式位于“外部资源” 下的子文件夹中。

SSMS 中的 PolyBase 对象

后续步骤

有关为各种数据源创建外部数据源和外部表的更多教程,请参阅 PolyBase Transact-SQL 参考

在以下文章中了解更多使用和监视 PolyBase 的方式: