用来处理数据的 ETL 和 ELT 工具的概述

数据集成和数据管理技术已存在很长一段时间。提取、转换和加载（ETL）数据的工具已经改变了传统的数据库和数据仓库。现在，内存中转换 ETL 工具使得提取、加载、转换（ELT）和 ETL 变得更快。对于大数据来说，是否能够使用内置的 Hadoop 工具而不是使用传统的 ETL 工具来提取、加载和转换数据呢？

大多数 ETL 软件包需要自己的服务器、处理、数据库和许可，还需要专家在该特定的工具中安装、配置和开发它们，而且这些技能并非总是可以转移的。Microsoft® SQL Server® Integration Services 或 IBM InfoSphere® DataStage® 的专家可能不知道如何使用 Informatica 或 Pentaho。为了避免采用新工具所涉及的学习曲线，可以考虑改为使用 Hadoop 生态系统中的工具。Apache Hive 和 Apache Pig（包括在 Hadoop 生态系统中）是提取、加载和转换各种形式的数据的领导者。与许多擅长结构化数据的传统 ETL 工具不同，创建 Hive 和 Pig 是为了在 Hadoop 分布式文件系统（HDFS）中加载和转换非结构化、结构化或半结构化数据。

Hive 构建​​于传统的数据库和数据仓库理念之上。它对待数据的方式就像是它有一个基于 SQL 或基于架构的结构。在 Hive 中，您可以将数据加载到 HDFS 中，或者将数据直接加载到 Hive 表中。不过，Pig 更加类似于标准的 ETL 脚本语言。在 Pig 中，您的心里可能有一个模式，但您更关心的是如何利用更复杂的功能在 HDFS 中转换和集成数据，而不是简单地将它们放进一个特定的表或数据库。因为 Pig 和 Hive 都使用了 MapReduce 功能，所以它们在执行非面向批量的处理时可能并不是那么快。有些开源工具试图改变这种限制，但问题依然存在。

回页首

传统的 ETL 和 ELT 理念的优点和局限性

多年来，ETL 技术和工具几乎完全没有变化，尤其在数据仓库方面。工具已经有所改进，但方法大体保持不变。您从各种来源提取数据，运行一组脚本或 ETL 工作流来转换该数据，然后将其加载到一个星型模式或半标准化的数据仓库或主数据管理系统中。

大部分数据专业人士都很熟悉 ETL。变更管理、慢慢改变的维度、插入、更新和发行公告等问题都已在多年前得到解决或找到解决方法。由于数据仓库中的数据并不总是可靠的，人们依靠微软 Excel® 电子表格来存储数据。这种方法已经形成了其理念和方法论，而且已经实现了相应的战略，但仍有许多不同的方法。

ETL 理念的关键限制是，在流程的早期，必须有人来确定哪些数据是重要的，哪些数据需要更新，哪些数据需要放在一边，以及谁可以获得数据许可。数据仓库或主数据管理系统只存储有人认为重要的数据。最初的原始数据不会被存储，并且无法进行检索。数据集市和转换后的数据成为仅有的可用数据，即使它是数据子集，创建和设计它的人有可能甚至已没有在该公司工作，可能对哪些数据重要持有不同的理念。

鉴于这些限制，人们开始寻找解决方法，比如在本地数据库中存储数据。部门建立了自己的孤岛和数据集市，突然之间，主数据成为了一种有趣的概念，而不是现实。数据并不是集成的数据。销售、营销和财务团队都有不同的数据。数字和仪表板是不可靠的，不值得信任。显然，ETL 不能容纳大数据。

回页首

使用 Hive 作为传统 ELT 工具的替代

Apache Hive 数据仓库软件有助于查询和管理位于分布式存储中的大型数据集。对于 ETL 而言，Hive 是一个强大的工具，而对于 Hadoop，它既是数据仓库，也是 Hadoop 的数据库。不过，相对于传统的数据库，它是相对缓慢的。它没有提供所有的 SQL 特性，甚至没有提供与传统的数据库相同的数据库特性。但它支持 SQL，它的确像一个数据库那样工作，它让更多的人（即使那些不是程序员的人）可以获得 Hadoop 技术。它提供了一种将非结构化和半结构化数据转化为基于模式的可用数据的方法。要建立一个主数据管理系统？您可以利用 Hive。要建立一个数据仓库？您也可以利用 Hive，但您需要学习一些技巧，使 Hive 成为一个强大的 ETL 工具。

相对于 Apache Pig 和 MapReduce，Hive 让传统的 RDBMS 数据库开发人员或了解 SQL 的其他人可以更容易访问和转换 Hadoop 中的数据。然而，Pig 不太容易理解，对于那些没有软件开发背景的人来说道，学习曲线是陡峭的。MapReduce 是 Java™、C ++ 和 Python 程序员可以相对迅速学会的技术。但是，如果没有一项技术（如 Java）基础，几乎不可能学会 MapReduce。因此，如果您知道 SQL，那么学习和使用 Hive 就会比较容易。

回页首

例如：如何使用 Hive 实现 ELT

我从世界银行的网站提取逗号分隔值（CSV）文件。该网站有大量关于经济、金融、贫困等方面的样本和真实数据。在这个示例中，我下载了 World-Finance, Inequality, and Poverty 1958-1998。这些数据将用于研究和教育目的，它们是 52 个发展中国家和发达国家的样本汇总。这些数据包括私人信贷、通货膨胀、国内生产总值（GDP）、GDP 增长率、收入占比等指标，它们提供了可能的洞察力。

在这个示例中，我使用 Hive 从网站提取数据，加载它，并转换它。此用例的目标是要取消各种来源的标准（本例中有 4 个 CSV 文件），然后对一个列执行一些简单的汇总。在您阅读这篇文章，并逐步完成这个示例之后，就会知道如何使用 Hive 实现 ELT 功能。要学习如何执行传统的 ETL 功能，只需逆转该流程，先使用 Hive 转换和汇总数据，然后加载它。

为了执行这些步骤，您需要获得 IBM InfoSphere® BigInsights™ Quick Start Edition。（您需要拥有或注册一个 IBM 通用 ID，然后才能下载 InfoSphere BigInsights Quick Start Edition。​​）

有两个版本可供下载。首先是 Quick Start Native Software Edition；用它在您自己的机器上运行软件。另一个版本是 Quick Start Edition VMware Image（为 Mac 用户融合）；您需要有 VMware Player，这个虚拟镜像才可以工作。我使用的是在 CentOS 6.4 Linux® 桌面上的 VMware Player 版本。

导入数据

首先从世界银行网站下载包含 4 个 CSV 文件的 .zip 文件。参见 下载 部分的文件。

然后，启动 InfoSphere BigInsights。（在 VMware Player 版本中，只需单击图标启动 InfoSphere BigInsights，就会立刻启动并运行 Hadoop。）单击 InfoSphere BigInsights 中的 shell 文件夹，单击终端，就可以开始为这个用例建立您的目录。清单 1 显示了命令行。

清单 1. 为用例创建文件夹结构

$ Sudo mkdir WorldBank

$ cd WorldBank

$ Sudo mkdir data

$ cd data

$ wget

http://microdata.worldbank.org/index.php/catalog/1788/download/28424/

WLD_1998_FIP_v01_M_CSV.zip

$ ls

$ cd WLD_1998_FIP_v01_M

$ ls

$ unzip WLD_1998_FIP_v01_M_CSV.zip

$ ls

如果操作正确，您应该获得四个 CSV 文件。现在，将数据加载到 Hadoop 中。在 InfoSphere BigInsights 中，您可以有几种加载方法。Distributed File Copy 应用程序使加载变得更容易，但在这个示例中，请使用以下 Hadoop shell 命令：

$ hadoop fs -ls

$ hadoop fs -mkdir WorldBank

现在您已经在 HDFS 中创建了 World Bank 目录，您可以使用下面的代码将 CSV 文件添加到 Hadoop：

$ hadoop fs -copyFromLocal

/home/biadmin/WorldBank/data/WLD_1998_FIP-v01_M/*.csv /user/biadmin/WorldBank

$ hadoop fs -ls /user/biadmin/WorldBank

四个 CSV 文件现在都已在 HDFS 中。下一步是用 Hive 转换那些文件。

设计 Hive 模式和数据库

首先，您需要了解数据在这些 CSV 文件中是如何布局的。在 Linux 终端窗口中输入以下命令：

$ head -2 Finance_inequality_and_the_poor_data_6005.csv

head -2 命令让您可以看到 CSV 文件的前两行（文件头加上第一行）。您可以看到 17 个列，其中大部分是数字或十进制格式，这使得它更容易。惟一的例外是 Countrycode，这是一个类似于字符串的字符。接着，查看其他文件。在 Linux 中，可以用不同的方式来比较文件（diff等），但现在，只需使用下面的命令来查看列名和数据：

$ head -2 Finance_inequality_and_the_poor_data_8005.csv

第二个文件有更多的列。除了 countrycode 之外，还有 Country 和年份列。只可以看见 13 列。当您比较这个文件与 * .6005.csv 文件时，似乎缺失了其他一些列。当您继续完成这个过程时，就会看到这四个 CSV 文件有差异，这使得反规范化和汇总数据变得更具挑战性。

确定如何建模您的数据库。您可以看到，您将获得四个核心表（6005、8005、data_panel 和 data_poverty）。创建并加载这些表后，需要创建一个反规范化的主表。在这个示例中，称这个表为 Finance_Inequality。此表整合了来自四个表中的所有数据。（显然，某些列将会留空。）

在创建和加载主表 (Finance_Inequality) 之后，最后一步是汇总一个列，并建立一个 Finance_Inequality_Transformed 表。这是一个简单的用例，但正如您所看到的，不难想象 Hive 用来构建复杂的汇总表和转换表的生产级系统是怎样的。

看看您必须为您的表创建的 6005 列：

• countrycode
• year
• loginitialgini
• growtheingini
• span
• loginitialgdppercapita
• growthgdppercapital
• privcreavg
• logprivatecredit
• inflation
• logtrade
• gr_ltrade
• gr_school
• logschooling
• logcommercialcentralbank
• loginitiallowestincomshare
• growthinlowestincomeshare

这个列表让您很容易了解需要做什么和创建什么。除了 countrycode 之外，6005 表将有 17 个数字或十进制的列。接下来，我们要开始建立数据库。

单击 InfoSphere BigInsights shell，然后单击 Hive Shell。

我通常会为每个表都创建文本文件，在该文件中编写 SQL 和 Data Manipulation Language (DML) 代码，保存表，然后将代码粘贴到 Hive shell 中。这种方法比在命令 shell 中逐行键入 SQL 和 DML 代码要容易得多。

清单 2 提供了创建数据库和第一个 6005 表的 SQL 代码。

备注：在我使用的 InfoSphere BigInsights 版本中，DECIMAL 无法工作，所以我必须使用 Double。

清单 2. 创建数据库和 6005 表的 SQL 代码

show databases;

create database WorldBank_Finance_Inequality;

use WorldBank_Finance_Inequality;

Create table IF NOT EXISTS WorldBank_Finance_Inequality.tbl_6005

(

 countrycode String,

 year int,

 loginitialgini double,

 growtheingini double,

 span int,

 loginitialgdppercapital double,

 growthgdppercapital double,

 privcreavg double,

 logprivatecredit double,

 inflation double,

 logtrade double,

 gr_ltrade double,

 gr_school double,

 logschooling double,

 logcommercialcentralbank double,

 loginitiallowestincomshare double,

 growthinlowestincomeshare double

)

ROW FORMAT DELIMITED FIELDS TERMINATED BY ','

;

现在，用类似的方式创建表 tbl_8005、tbl_data_panel 和 tbl_data_poverty。

在创建了四个表后，就已经做好了使用以下命令来填充它们的准备：

LOAD DATA INPATH '/user/biadmin/WorldBank/Finance_inequality_and_the_poor_data_6005.csv'

OVERWRITE INTO TABLE worldbank.finance_inequality.tbl_6005;

select * from worldbank.finance_inequality.tbl_6005 limit 10;

select count(*) from worldbank.finance_inequality.tbl_6005;

填充表并运行一些简单的 select 语句后，数据应该是正确的。您可以先删除头文件，再将它们加载到 HDFS 或 Hive 表中。

备注：请记住，在您创建表时，要包括用单引号 (') 终止的行格式分隔字段。如果遗漏了这个关键部分，将无法正确加载您的数据。

构建主 Finance_Inequality 表来整合数据

在构建了四个核心表后，下一步是构建主表，整合来自所有四个表的数据。这个任务可能有些复杂，具体情况取决于您的源代码。对于这个用例，使用左外连接和右外连接就足以创建主表。清单 3 显示了相关代码。

清单 3. 构建主 Finance_Inequality 表的代码

CREATE table WorldBank_Finance_inequality.master_tbl_Finance_Inequality AS

SELECT

a.countrycode,

b.country,

a.year,

c.timeperiod,

a.loginitialgini,

a.growthingini,

a.span,

a.loginitialgdppercapita ,

a.growthgdppercapita,

a.privcreavg,

a.logprivatecredit,

a.inflation,

a.logtrade,

a.gr_ltrade,

a.gr_school,

a.logschooling,

a.logcommercialcentralbank,

a.loginitiallowestincomeshare, a.growthinlowestincomeshare,

d.logagedependency,

d.loginitialheadcount,

d.loginitialpovertygap,

d.growthinheadcount,

d.growthinpovertygap,

d.growthinmeanincome,

d.populationgrowth

FROM WorldBank_Finance_inequality.tbl_6005 A

LEFT OUTER JOIN

WorldBank_Finance_inequality.tbl_8005 B ON A.countrycode = B.countrycode

RIGHT OUTER JOIN

WorldBank_Finance_inequality.tbl_data_panel C ON A.countrycode = C.countrycode

LEFT OUTER JOIN

WorldBank_Finance_inequality.tbl_data_poverty D ON A.countrycode = D.countrycode

;

在运行这段代码后，就会有 315 个行或 314 个行，具体行数取决于您是否丢弃了标题行。有了这个主表，就可以对数据执行组合、汇总、删除和任何您希望执行的操作。显然，随着更深入的研究，您会发现数据质量问题或重复数据，但对于本用例，您已经使用 Hive 完成了 ELT，并通过转换工作构建了一个主数据库。

汇总表

为了对反规范化的主表中的数据进行汇总或执行分析，您有很多选择。您可以组合一些列；丢弃列；或加、减、乘，或者除几个列，并在一个新表中生成自己的派生列​​。您需要有一个策略。也许您想找到人口增长和贫富差距之间的相关性。您可以运行复杂的 Hive SQL 查询来获取这些结果，但是，如果您想让自己或用户的工作变得更容易，那么可以考虑建立一个汇总表。

创建汇总表的过程类似于创建主表。在 Hive 中， 您可以使用选项，例如， 如果出现情况 A，则……样，如果出现情况 B，则……样， 等。与传统的 RDBMS 相比，Hive 有其局限性，但凭借正确的理念和准备，您可以创建汇总查询，或创建更复杂的派生表。

运行查询

使用清单 4 中的代码运行一个查询。

清单 4. 在 Hive shell 中运行一个查询

use WorldBank_Finance_Inequality;

select countrycode, country,

CASE WHEN YEAR > 1990 then YEAR ELSE 0 END AS theYEAR,

populationgrowth, growthinpovertygap

FROM master_tbl_finance_inequality;

回页首

结束语

显然，在采用 ELT 或 ETL 理念之间进行选择是需要三思的。对于许多数据仓库、主数据管理和其他数据库项目而言，这个决定占用的时间可能是所需的规划时间的 70％ 以上。有效的数据分析需要正确的数据。如果没有合适的数据，就无法获得准确的分析。

在这篇文章中，我们的用例显示，将数据转储到 HDFS，然后再考虑模式，这是比较容易做到的方法。本文在数据已经进入 HDFS 后开始建立 Hive 数据库，样本数据来自 Web 的 CSV 文件。但该数据可以来自任何来源，可以采用任何格式。在该示例将近结束时，我们确定了这些文件的布局、分隔符、变量和其他因素，然后再为该数据建立数据库并运行查询。

Hive 肯定有其局限性，但是，如果您在 Hadoop 生态系统中，而且已经了解 SQL，那么就可以利用这个奇妙的工具开始建立数据库、表流、转换和数据集成。这是一个相对简单的用例，在 Hive 和 Hadoop 中还可以建立更复杂的流程。