Основы Hive: анализ больших данных с помощью SQL

Введение

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

По возможности вводите весь примерный код из документа самостоятельно, а не копируйте и вставляйте его. Так вы лучше освоите код. Если возникнут вопросы, внимательно перечитайте документацию или обратитесь за помощью и советом на форум.

Знакомство с Hive

На этом этапе вы узнаете, как Apache Hive предоставляет SQL-интерфейс для запросов и анализа больших наборов данных, хранящихся в Hadoop. Ниже описаны архитектура Hive и его основные возможности.

Архитектура Hive

Компоненты: Hive включает HCatalog для управления таблицами и хранилищем, а также WebHCat для запуска задач MapReduce, запросов Pig и взаимодействия с метаданными Hive через REST API.
Возможности:
- Поддерживает SQL-запросы для задач хранилищ данных, таких как ETL, анализ данных и создание отчётов.
- Данные хранятся в системах хранения, например HDFS или HBase, а запросы выполняются вычислительными платформами, такими как Tez, Spark или MapReduce.
- Поддерживает процедурные языки, HPL-SQL и выполнение подзапросов с использованием Hive LLAP, YARN и Slider.

Основные единицы данных Hive

База данных: пространство имён, которое помогает избежать конфликтов имён и повысить безопасность.
Таблица: набор однотипных данных с общей схемой. Например, таблица page_views может содержать столбцы с временными метками, идентификаторами пользователей и URL страниц.
Секция (partition): разделяет данные на секции по ключам, чтобы ускорить их поиск.
Бакет (bucket): разделяет данные секции на бакеты по значениям хеш-функции, что повышает эффективность запросов.

Типы данных Hive

Hive поддерживает примитивные и сложные типы данных. Подробности см. в документации по типам данных Hive.

Hive предназначен для задач хранилищ данных, а не для обработки онлайн-транзакций (OLTP). Основное внимание уделяется масштабируемости, производительности, надёжности и отказоустойчивости. Hive поддерживает различные входные форматы, а также расширяется с помощью коннекторов для разных форматов. Чтобы использовать возможности Hive для анализа больших данных, важно понимать его архитектуру, единицы данных и типы данных.

Установка и развёртывание Hive

На этом этапе вы ознакомитесь с установкой и развёртыванием Hive, в том числе со структурой каталогов, переменными среды, хранилищем метаданных и системными параметрами.

Сначала переключитесь на пользователя hadoop: дальнейшие действия нужно выполнять от его имени. Дважды щёлкните значок терминала Xfce на рабочем столе, чтобы открыть терминал, и выполните команду:

su - hadoop

Совет: пароль пользователя hadoop — hadoop.

Затем скачайте стабильную версию Hive с помощью команды wget. В этой работе используется Hive версии 2.3.3:

sudo wget https://labexfile.oss-us-west-1.aliyuncs.com/courses/144/apache-hive-2.3.3-bin.tar.gz

Распакуйте загруженный архив. Введите в терминале следующую команду:

tar -zxvf apache-hive-2.3.3-bin.tar.gz

Переместите распакованный каталог в /opt с помощью sudo. Без этих прав у вас может не быть возможности записывать данные в /opt:

sudo mv /home/hadoop/apache-hive-2.3.3-bin /opt/hive-2.3.3

После перемещения назначьте пользователя hadoop и его группу владельцами каталога Hive. Выполните в терминале команду:

sudo chown -R hadoop:hadoop /opt/hive-2.3.3

Совет: для выполнения перечисленных команд с правами sudo можно открыть ещё один терминал.

labex:~/ $ ls -al /opt/
total 24
drwxrwxr-x 10 hadoop hadoop 4096 Mar 3 12:01 hive-2.3.3
...

Настройка переменных среды

На этом этапе вы настроите переменные среды Hive, чтобы HIVE_HOME указывала на каталог установки Hive.

Сначала откройте файл .bashrc пользователя hadoop в редакторе vim. Выполните в терминале команду:

vim /home/hadoop/.bashrc

Добавьте в конец файла следующий текст. При необходимости измените значение PATH с учётом текущей среды лабораторной работы. Выражение $PATH сохраняет существующее содержимое переменной. Добавьте :$HIVE_HOME/bin в конец значения переменной среды PATH:

export HIVE_HOME=/opt/hive-2.3.3
export PATH=$PATH:$HIVE_HOME/bin

После внесения изменений сохраните файл и выйдите из редактора. Затем примените настройки переменных среды с помощью команды source.

В этой лабораторной работе Hive уже установлен, поэтому выполнять команду source для переменных среды не нужно. При самостоятельной установке необходимость в этом шаге зависит от ситуации.

Настройка конфигурации

На этом этапе вы ознакомитесь с предварительно настроенной конфигурацией Hive и создадите файлы, необходимые для проверок.

Настройка метахранилища

Перед использованием Hive настройте хранилище метаданных. По умолчанию Hive хранит метаданные во встроенной базе данных Derby. Расположение данных на диске задаёт параметр javax.jdo.option.ConnectionURL в конфигурационном файле Hive conf/hive-default.xml. По умолчанию используется каталог ./metastore_db.

В этой лабораторной работе для хранения метаданных будет использоваться MySQL. Поэтому необходимо изменить конфигурационный файл Hive.

Создайте и откройте конфигурационный файл в редакторе vim:

vim /opt/hive-2.3.3/conf/hive-site.xml

<configuration>
<property>
  <name>javax.jdo.option.ConnectionURL</name>
  <value>jdbc:mysql://localhost:3306/hive_metastore1?createDatabaseIfNotExist=true</value>
</property>
<property>
  <name>javax.jdo.option.ConnectionDriverName</name>
  <value>com.mysql.cj.jdbc.Driver</value>
</property>
<property>
  <name>javax.jdo.option.ConnectionUserName</name>
  <value>hiveuser1</value>
</property>
<property>
  <name>javax.jdo.option.ConnectionPassword</name>
  <value>123qweQWE...</value>
</property>
</configuration>

Добавив приведённые выше параметры конфигурации, сохраните файл и выйдите из редактора.

Создание базы данных MySQL

Запустите сервер MySQL с помощью команды sudo mysql и создайте базу данных hive_metastore:

CREATE DATABASE hive_metastore1;
CREATE USER 'hiveuser1'@'localhost' IDENTIFIED BY '123qweQWE...';
GRANT ALL PRIVILEGES ON hive_metastore1.* TO 'hiveuser1'@'localhost';
FLUSH PRIVILEGES;
exit;

Затем скачайте драйвер MySQL JDBC и скопируйте его в каталог /opt/hive-2.3.3/lib:

cp /usr/local/hive/lib/mysql-connector-j-8.3.0.jar /opt/hive-2.3.3/lib

Настройка системных параметров Hive

Настроив метахранилище, укажите внутренние параметры Hive: путь к Hadoop, расположение внутренних конфигурационных файлов и другие значения.

Сначала скопируйте шаблон настроек с помощью команды cp, чтобы создать действующий файл конфигурации.

Выполните в терминале команду:

cp /opt/hive-2.3.3/conf/hive-env.sh.template /opt/hive-2.3.3/conf/hive-env.sh

Затем откройте файл hive-env.sh в редакторе vim:

vim /opt/hive-2.3.3/conf/hive-env.sh

В строке 48 укажите путь к каталогу установки Hadoop для параметра HADOOP_HOME:

HADOOP_HOME=/home/hadoop/hadoop

После изменения параметров сохраните файл и выйдите из редактора.

Инициализация метахранилища

На этом этапе вы инициализируете метахранилище Hive и запустите необходимые Hive службы Hadoop.

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

start-dfs.sh
start-yarn.sh

Проверьте состояние служб командой jps:

hadoop:~/ $ jps                                                      [17:31:43]
8960 Jps
3153 NodeManager
2823 SecondaryNameNode
3017 ResourceManager
2570 DataNode
2428 NameNode

Если вы впервые используете Hadoop, удалите данные Hadoop и выполните инициализацию командой hdfs namenode -format.

В этой лабораторной работе предварительно установленный Hive уже инициализирован с базой данных u per. Если вы хотите инициализировать Hive, который только что скачали, выполните команду:

/opt/hive-2.3.3/bin/schematool -initSchema -dbType mysql

Initialization script completed
schemaTool completed

Если вы впервые инициализируете только что скачанный Hive, измените путь HIVE_HOME в файле ~/.bashrc на путь к каталогу установки этого Hive и выполните команду source, чтобы применить изменения. Однако мы не рекомендуем этого делать: последующие проверки Hive в этой лабораторной работе основаны на предварительно установленной версии.

Когда появится сообщение об окончании инициализации, войдите в командную строку Hive командой hive. Выполните в терминале:

hive

Совет: команда hive запускает оболочку предварительно установленного Hive. Чтобы открыть оболочку только что настроенного Hive, укажите абсолютный путь к его исполняемому файлу.

Hive Session ID = 3eee2693-175d-4452-82d2-47f1b639d9d5

Logging initialized using configuration in jar:file:/usr/local/hive/lib/hive-common-3.1.3.jar!/hive-log4j2.properties Async: true
Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
Hive Session ID = 68d0d345-92c3-4eb1-8393-7ee34f56363f
hive>

Основные операции в Hive

На этом этапе вы изучите основные операции с таблицами Hive. Все операторы Hive также приведены в его руководстве по языку.

Создание таблицы

Сначала получите данные. В этой части лабораторной работы используются журналы, имитирующие работу сервера NginX. Их можно анализировать, чтобы узнать количество просмотров страниц сайта.

Откройте ещё один терминал и выполните команды, чтобы скачать пример данных:

su hadoop
cd ~

wget http://labfile.oss-cn-hangzhou.aliyuncs.com/courses/567/log_example.csv

Затем загрузите файл в HDFS:

hdfs dfs -mkdir -p /user/data/staging/page_view
hdfs dfs -put /home/hadoop/log_example.csv /user/data/staging/page_view

Получив данные, создайте таблицу. Чтобы создать для этих данных таблицу page_view, введите следующий оператор в командной строке Hive:

CREATE TABLE page_view(viewTime INT, userid BIGINT,
Page_url STRING, referrer_url STRING,
Ip STRING COMMENT 'IP Address of the User')
COMMENT 'This is the page view table'
PARTITIONED BY(dt STRING, country STRING)
STORED AS SEQUENCEFILE;

Для каждого столбца в этом примере задан подходящий тип данных. К столбцам и таблицам можно добавлять комментарии. Кроме того, предложение PARTITIONED BY задаёт секционные столбцы, отличающиеся от столбцов с данными. Секционные столбцы не хранятся вместе с данными столбцов. Если секционные столбцы заданы таким образом, строки разделяются символом новой строки.

Если данные имеют другой формат, можно задать разделитель полей, как в следующем примере:

Следующий оператор приведён только для демонстрации. Не вводите его в оболочку hive.

CREATE TABLE page_view1(viewTime INT, userid BIGINT,
Page_url STRING, referrer_url STRING,
Ip STRING COMMENT 'IP Address of the User')
COMMENT 'This is the page view table'
PARTITIONED BY(dt STRING, country STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '1'
STORED AS SEQUENCEFILE;

Hive не определяет символ разделения строк: он задаётся в Hadoop. Поэтому вручную изменить его нельзя.

Обычно данные таблицы с определённым набором столбцов хранятся в бакетах. Это упрощает эффективную выборку данных при запросах. Если бакеты не созданы, случайную выборку из таблицы выполнить можно, но при сканировании всего набора данных она не будет столь же эффективной. В следующем примере показано, как включить бакетирование таблицы page_view по столбцу userid.

Следующий оператор приведён только для демонстрации. Не вводите его в оболочку hive.

CREATE TABLE page_view2(viewTime INT, userid BIGINT,
Page_url STRING, referrer_url STRING,
Ip STRING COMMENT 'IP Address of the User')
COMMENT 'This is the page view table'
PARTITIONED BY(dt STRING, country STRING)
CLUSTERED BY(userid) SORTED BY(viewTime) INTO 32 BUCKETS
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '1'
COLLECTION ITEMS TERMINATED BY '2'
MAP KEYS TERMINATED BY '3'
STORED AS SEQUENCEFILE;

В этом примере значения столбца userid распределяются по 32 бакетам с помощью хеш-функции. В каждом бакете данные сортируются по столбцу viewTime в порядке возрастания. Такой способ организации данных позволяет эффективно выбирать записи по столбцам, используемым для группировки (в данном случае — по userid). Сортировка также помогает менеджерам данных эффективнее оценивать запросы благодаря более подходящим структурам данных.

Следующий оператор приведён только для демонстрации. Не вводите его в оболочку hive.

CREATE TABLE page_view3(viewTime INT, userid BIGINT,
Page_url STRING, referrer_url STRING,
Friends ARRAY<BIGINT>, properties MAP<STRING, STRING>,
Ip STRING COMMENT 'IP Address of the User')
COMMENT 'This is the page view table'
PARTITIONED BY(dt STRING, country STRING)
CLUSTERED BY(userid) SORTED BY(viewTime) INTO 32 BUCKETS
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '1'
COLLECTION ITEMS TERMINATED BY '2'
MAP KEYS TERMINATED BY '3'
STORED AS SEQUENCEFILE;

В приведённом примере формат каждой строки таблицы задаётся именами и типами полей, подобно определениям типов в других языках программирования. Обратите внимание: имена таблиц и столбцов регистронезависимы. Комментарий IP Address of the User и предложение COMMENT показывают, что комментарии можно добавлять и к столбцам, и к таблицам. Кроме того, предложение PARTITIONED BY задаёт секционные столбцы, отличающиеся от столбцов с данными. Как отмечалось ранее, сами секционные столбцы данные не хранят. Предложение CLUSTERED BY указывает, какой столбец использовать для создания бакетов и сколько их создать. Предложение ROW FORMAT DELIMITED задаёт способ хранения строк в таблице Hive. Для форматов с разделителями в нём можно указать разделители полей, элементов коллекции (массива или отображения) и ключей отображения; числа задаются в кодировке ASCII. STORED AS SEQUENCEFILE означает, что данные хранятся в HDFS в двоичном формате, а именно в формате Hadoop SequenceFile. Параметры предложений ROW FORMAT и STORED AS совпадают со значениями Hive по умолчанию, поэтому в первом операторе создания таблицы их можно было не указывать.

Просмотр таблиц и секций

Чтобы вывести список существующих таблиц в хранилище, выполните оператор:

SHOW TABLES;

Если таблиц много, этот оператор вернёт большой список. Чтобы ограничить результаты, задайте префикс. Например, чтобы вывести таблицы, имена которых начинаются с page, выполните оператор:

SHOW TABLES 'page.*';

Здесь используются правила сопоставления регулярных выражений: точка (.) обозначает любой символ.

Чтобы вывести список секций таблицы, выполните оператор ниже. Если таблица не разбита на секции, Hive ничего не выведет:

SHOW PARTITIONS page_view;

Чтобы вывести столбцы таблицы и их типы, выполните оператор DESCRIBE:

DESCRIBE page_view;

Чтобы вывести столбцы и все остальные свойства таблицы, добавьте ключевое слово EXTENDED. Hive выведет много информации, которая обычно нужна для отладки:

DESCRIBE EXTENDED page_view;

Изменение таблицы

Чтобы переименовать существующую таблицу, используйте оператор ALTER TABLE с предложением RENAME TO. Если таблица с новым именем уже существует, Hive вернёт ошибку. Следующие операторы приведены только в качестве примера. Не выполняйте их: на следующем этапе используется созданная вами таблица page_view.

ALTER TABLE page_view RENAME TO new_page_view;

Посмотрите на результат:

hive> ALTER TABLE page_view RENAME TO new_page_view;
OK
Time taken: 0.124 seconds
hive> show tables;
OK
new_page_view
...
Time taken: 0.021 seconds, Fetched: 4 row(s)

Также можно переименовать столбцы существующей таблицы. При этом обязательно сохраните тип данных столбцов и укажите запись для каждого существующего столбца:

ALTER TABLE new_page_view REPLACE COLUMNS (viewTime INT, userid BIGINT, page_url STRING, referrer_url STRING, ipaddr STRING COMMENT 'IP Address of the User');

Кроме того, в существующую таблицу можно добавить столбцы:

ALTER TABLE new_page_view ADD COLUMNS (new_column INT COMMENT 'a new int column');

Обратите внимание: при изменении схемы, например при добавлении столбца, схема старых секций таблицы сохраняется, чтобы они не превращались в секционированную таблицу. Запросы, обращающиеся к новым столбцам старых секций, неявно возвращают null или заданные для этих столбцов значения по умолчанию.

Удаление таблиц и секций

Hive создан как хранилище данных, поэтому удаление данных считается нежелательным действием. По этой причине удаление таблицы выполняется не так просто. При удалении таблицы командой DROP также неявно удаляются все созданные для неё индексы.

В следующем примере удаляется таблица. Не выполняйте эту команду в лабораторной работе:

DROP TABLE new_page_view;

Загрузка данных и выполнение запросов

На этом этапе вы загрузите подготовленные данные в секционированную таблицу Hive и выполните запрос к результату.

Загрузка данных

Загрузить данные в таблицу Hive можно несколькими способами. Можно создать внешнюю таблицу, связанную с определённым каталогом в HDFS. Затем с помощью команды put или copy HDFS скопировать файл данных в этот каталог и создать таблицу, которая будет на него ссылаться. В такой таблице будет указана вся необходимая информация о формате строк.

После создания внешней таблицы данные можно преобразовать и вставить в любую другую таблицу Hive. Ранее мы загрузили файл log_example.csv в HDFS и переименовали его в page_view. Чтобы загрузить данные в соответствующую секцию таблицы page_view, выполните следующие действия.

Сначала создайте внешнюю таблицу и свяжите её с указанным файлом:

CREATE EXTERNAL TABLE page_view_stg(viewTime INT, userid BIGINT,
                Page_url STRING, referrer_url STRING,
                Ip STRING COMMENT 'IP Address of the User',
                Country STRING COMMENT 'country of origin')
COMMENT 'This is the staging page view table'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '44' LINES TERMINATED BY '\n' STORED AS TEXTFILE
LOCATION '/user/data/staging/page_view';

Затем создайте таблицу page_view для постоянного хранения данных:

CREATE TABLE page_view(viewTime INT, userid BIGINT,
                Page_url STRING, referrer_url STRING,
                Ip STRING COMMENT 'IP Address of the User')
COMMENT 'This is the page view table'
PARTITIONED BY(dt STRING, country STRING)
STORED AS SEQUENCEFILE;

Для этого небольшого набора данных не требуется распределённая задача YARN. Настройте локальное выполнение заданий Hadoop для текущего сеанса Hive, чтобы вставка надёжно завершилась в виртуальной машине лабораторной среды:

SET mapreduce.framework.name=local;

Наконец, вставьте данные из внешней таблицы в таблицу page_view:

FROM page_view_stg pvs
INSERT OVERWRITE TABLE page_view
PARTITION(dt='2015-05-17', country='us')
SELECT pvs.viewTime, pvs.userid, pvs.page_url, pvs.referrer_url, pvs.ip
WHERE pvs.country = 'us';

Локальное задание должно завершиться за несколько секунд и загрузить подходящие строки в секцию таблицы page_view.

...
Loading data to table default.page_view partition (dt=2015-05-17, country=us)
MapReduce Jobs Launched:
Stage-Stage-1: HDFS Read: ... HDFS Write: ... SUCCESS
Total MapReduce CPU Time Spent: 0 msec
OK
Time taken: ... seconds

Простой запрос

После вставки данных выполните простой запрос, похожий на обычный запрос SQL. Введите следующий оператор в командной строке Hive:

SELECT * FROM page_view WHERE userid = 0 LIMIT 10;

В ответе отобразятся найденные записи:

hive> select * from page_view;
OK
NULL 490 /downloads/product_1 /archives/webroot 217.168.17.5 2015-05-17 us
NULL 490 /downloads/product_2 /archives/webroot 217.168.17.5 2015-05-17 us
NULL 3316 /downloads/product_2 /archives/webroot 217.168.17.5 2015-05-17 us
...

Остановка служб

На этом этапе вы выйдете из Hive и остановите службы Hadoop. Дополнительные материалы для изучения:

Закончив работу с Hive, выполните команду quit, чтобы выйти из его командной строки:

quit;

Не забудьте также остановить HDFS. Введите в терминале следующие команды:

stop-yarn.sh
stop-dfs.sh

Итоги

В этой лабораторной работе вы познакомились с архитектурой, установкой и развёртыванием Hive, а также с основными операторами HQL. Вы также научились импортировать данные с помощью примера из лабораторной работы.

Основные темы:

архитектура Hive;
основные единицы данных Hive;
развёртывание Hive;
язык HQL в Hive.

Hive — это программный пакет для создания хранилищ данных, и его возможности можно изучать дальше. Регулярно обращайтесь к техническим материалам и продолжайте обучение на следующих курсах.

Быстрый старт с Hive

Введение

Знакомство с Hive

Архитектура Hive

Основные единицы данных Hive

Типы данных Hive

Установка и развёртывание Hive

Настройка переменных среды

Настройка конфигурации

Настройка метахранилища

Создание базы данных MySQL

Настройка системных параметров Hive

Инициализация метахранилища

Основные операции в Hive

Создание таблицы

Просмотр таблиц и секций

Изменение таблицы

Удаление таблиц и секций

Загрузка данных и выполнение запросов

Загрузка данных

Простой запрос

Остановка служб

Итоги