Содержание


Hive как инструмент для ETL или ELT

Извлечение, преобразование и загрузка ИЛИ извлечение, загрузка и преобразование

Comments

Обзор ETL- и ELT-инструментов для обработки данных

Технологии интеграции данных и управления данными используются уже давно. Инструменты, обеспечивающие извлечение, преобразование и загрузку (extract, transform, and load, ETL) данных изменили возможности традиционных баз данных и хранилищ данных. В настоящее время ETL-инструменты, работающие в оперативной памяти, ускорили процессы ETL и ELT (извлечение, загрузка и преобразование). Возможно ли для больших данных использовать встроенные инструменты Hadoop для извлечения, загрузки и преобразования данных вместо традиционных ETL-инструментов?

Для большинства ETL-решений требуются собственные серверы, средства обработки, базы данных и лицензии. И для них требуются настройка, конфигурирование и разработка специалистами по именно этим инструментам, а такие навыки не всегда переносимы. Эксперты по Microsoft® SQL Server® Integration Services или IBM InfoSphere® DataStage® могут не знать, как использовать Informatica или Pentaho. Чтобы избежать дополнительного обучения для освоения новых инструментов, обратите внимание на инструменты в экосистеме Hadoop. Инструменты Apache Hive и Apache Pig, включенные в экосистему Hadoop, являются первыми кандидатами на выполнение задач извлечения, загрузки и преобразования данных различных типов. В отличие от многих традиционных ETL-инструментов, которые хорошо подходят для обработки структурированных данных, Hive и Pig созданы для загрузки и преобразования неструктурированных, структурированных или полуструктурированных данных в Hadoop Distributed File System (HDFS).

Инструмент Hive применяет принципы традиционных баз данных и хранилищ данных. Он обращается с данными так, как если бы они основывались на SQL или схеме. Используя Hive, вы можете загружать данные в HDFS или непосредственно в таблицу Hive. А Pig больше похож на стандартный язык сценариев ETL. Используя Pig, вы можете подразумевать схему, но вас больше заботит то, как преобразовать и интегрировать данные в HDFS с использованием более сложных функций, чем просто занесение в конкретную таблицу или базу данных. Поскольку и Pig, и Hive используют функции MapReduce, они могут быть не такими быстрыми в выполнении непакетной обработки. Некоторые инструменты с открытым исходным кодом пытаются улучшить ситуацию, но проблема по-прежнему существует.

Преимущества и ограничения традиционных концепций ETL и ELT

Многие годы технологии и инструменты ETL остаются практически неизменными, в особенности в контексте хранилищ данных. Инструменты улучшились, но методологии в основном не изменились. Вы извлекаете данные из различных источников, выполняете набор сценариев или рабочих потоков ETL для преобразования этих данных, затем загружаете их в хранилище данных со схемой «звезда» или в полунормализованном формате, либо в систему управления основными данными.

Большинство специалистов по работе с данными знакомы с ETL. Такие проблемы, как управление изменениями, медленно меняющиеся измерения, вставки, обновления и логически удаленные элементы (tombstone), в течение этих лет решались, либо для них находились обходные пути. Поскольку данным в хранилищах данных не всегда доверяли, пользователи полагались на электронные таблицы Microsoft Excel®. Концепции и методологии разработаны, стратегии реализованы, однако существует обширное разнообразие подходов.

Ключевым ограничением концепции ETL является то, что в самом начале процесса кто-то должен решить, какие данные важны, какие данные необходимо обновить, какие данные следует отбросить и кто получает разрешение на эти данные. После этого хранилища данных или системы управления основными данными становятся репозиториями, содержащими только те данные, которые кто-то посчитал важными. Исходные данные не хранятся и не могут быть извлечены. Содержимое витрин данных и преобразованные данные будут единственными доступными данными, даже если это подмножество данных, которое спроектировал тот, кто может уже и не работать в компании, и кто может не разделять ваши представления о том, какие именно данные важны.

Учитывая такие ограничения, пользователи начали искать обходные пути, например хранение данных в локальной базе данных. Подразделения создали свои собственные, отдельные хранилища и витрины данных, и внезапно основные данные стали интересной концепцией — но не реальностью. Данные не были интегрированными. Отделы продаж, маркетинга и финансов использовали разные данные. Числа и информационные панели были ненадежными и недостоверными. Очевидно, что концепция ETL не могла поддерживать использование больших данных.

Hive как альтернатива традиционным ELT-инструментам

Программное обеспечение для хранилищ данных Apache Hive упрощает выполнение запросов и управление большими наборами данных, размещаемыми в распределенной среде хранения. Hive — это мощный инструмент для ETL, организации хранилищ данных для Hadoop и управления базами данных для Hadoop. Однако он является относительно медленным, в сравнении с традиционными средствами управления базами данных. Он не предлагает все функции SQL, или даже функции управления базами данных, доступные в традиционных СУБД. Но он поддерживает SQL, он функционирует как база данных, и он предоставляет доступ к Hadoop большему количеству пользователей (даже не программистам). Он предлагает возможность превращать неструктурированные и полуструктурированные данные в пригодные для использования данные на базе схемы. Хотите создать систему управления основными данными? Вы можете сделать это, используя Hive. Хотите создать хранилище данных? Вы можете сделать и это с использованием Hive, но вам потребуется изучить приемы, позволяющие сделать Hive мощным ETL-инструментом.

В отличие от Apache Pig и MapReduce, Hive позволяет разработчикам традиционных реляционных СУБД и другим специалистам, знающим SQL, проще получать доступ к данным в Hadoop и преобразовывать их. Однако Pig не так прост в понимании, и требуется значительное дополнительное обучение для тех, кто не является разработчиком программного обеспечения. MapReduce — это технология, которую программисты на Java™, C++ и Python могут освоить относительно быстро. Но без знания таких технологий, как Java, почти невозможно изучить MapReduce. Поэтому, если вы знаете SQL, то Hive может быть относительно прост в изучении и использовании.

Пример: Как использовать Hive для ELT

Я получил файлы в формате CSV (comma-separated value) с web-сайта World Bank. Этот сайт предлагает множество примеров и реальных данных по экономике, финансам, бедности и другим аспектам. Для данного примера я загрузил данные World-Finance, Inequality, and Poverty 1958-1998. Они предназначены для исследовательских и образовательных целей, но включают данные по 52 развивающимся и развитым странам. Эти данные содержат показатели в таких областях, как кредитование частного сектора, инфляция, валовой внутренний продукт (ВВП), темпы роста ВВП и распределение доходов.

В предлагаемом примере я получаю данные с web-сайта, загружаю их и преобразовываю с использованием Hive. Целью является денормализация различных источников (в данном случае это четыре CSV-файла), затем применение некоторой простой агрегации к столбцу. Ознакомившись с этой статьей и предлагаемым примером, вы узнаете, как выполнять ELT-функции с использованием Hive. Чтобы узнать, как выполнять традиционные 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 для использования этого виртуализированного образа. Я использую версию для VMware Player на настольной системе под управлением CentOS 6.4 Linux®.

Импорт данных

Начните с загрузки с web-сайта World Bank архива .zip, содержащего CSV-файлы. Эти файлы можно получить в разделе Загрузка.

Затем запустите InfoSphere BigInsights. (В версии для VMware Player просто щелкните по пиктограмме для запуска InfoSphere BigInsights, и Hadoop сразу же будет готов к работе.) Щелкните по каталогу оболочки InfoSphere BigInsights, щелкните по терминалу — и вы можете приступать к созданию директорий для этого примера. Команды показаны в листинге 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:

$ hadoop fs -ls
$ hadoop fs -mkdir WorldBank

Создав директорию World Bank в HDFS, вы можете добавить 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 и Year. Видны только 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

Этот список наводит вас на хорошую идею о том, что вам нужно делать и создавать. Таблица 6005 будет иметь 17 числовых или десятичных столбцов, помимо столбца countrycode. Затем приступайте к созданию базы данных.

Щелкните по оболочке InfoSphere BigInsights, затем по Hive Shell.

Я обычно создаю текстовые файлы для каждой таблицы, записываю в них коды SQL и Data Manipulation Language (DML), сохраняю таблицы и затем вставляю коды в оболочку Hive. Этот метод значительно проще, чем ввод кодов SQL и DML строка за строкой в командной оболочке.

В листинге 2 показан SQL-код для создания базы данных и первой таблицы 6005.

Примечание: В используемой мной версии InfoSphere BigInsights тип DECIMAL не работал, поэтому я использовал Double.

Листинг 2. SQL-код для создания базы данных и таблицы 6005
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 и создания основной базы данных.

Агрегация таблиц

Для выполнения агрегации или анализа данных в денормализованной основной таблице у вас есть множество вариантов. Вы можете комбинировать некоторые столбцы, игнорировать столбцы, или можете сложить, вычесть, умножить или разделить пару столбцов и получить свой собственный производный столбец в новой таблице. Вам нужна стратегия. Возможно, вы хотите обнаружить корреляцию между ростом населения и нищетой. Вы можете выполнить сложные SQL-запросы Hive для получения этих результатов, но если вы хотите упростить задачу для себя или для своих пользователей, подумайте о создании агрегированной таблицы.

Создайте агрегированную таблицу подобно тому, как создавали основную таблицу. В Hive вы можете использовать такие конструкции, как если выполнено условие A, то…, если выполнено условие B, то… и т.д. Hive имеет ограничения, в сравнении с традиционной реляционной СУБД, но при правильной концепции и подготовке можно создавать запросы на агрегацию или более сложные производные таблицы.

Выполнение запроса

Используйте код из листинга 4 для выполнения запроса.

Листинг 4. Выполнение запроса в оболочке Hive
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 и впоследствии позаботиться о схеме. В данном примере все начинается с создания базы данных Hive, после того как данные, полученные из Интернета в виде CSV-файлов, уже помещены в HDFS. Однако данные могут быть получены из любого источника и в любом формате. Мы выяснили, как организованы эти файлы, приняли решения об ограничителе, переменных и других факторах, перед тем как создавать базы данных и выполнять запросы к этим данным.

Конечно, у Hive есть ограничения, но если вы в экосистеме Hadoop и уже знаете SQL, то это отличный инструмент для того, чтобы приступить к созданию баз данных, заполнению таблиц, преобразованиям и интеграции данных. Это относительно простой пример, однако Hive и Hadoop могут поддерживать и значительно более сложные процессы.


Ресурсы для скачивания


Похожие темы


Комментарии

Войдите или зарегистрируйтесь для того чтобы оставлять комментарии или подписаться на них.

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=40
Zone=Information Management
ArticleID=979192
ArticleTitle=Hive как инструмент для ETL или ELT
publish-date=05142014