![](https://habrastorage.org/getpro/habr/upload_files/2e2/296/a9d/2e2296a9df67cd94092ee5d6ad77644c.png)
Часто специалисты, работающие с классическими реляционными базами данных, например, с PostgreSQL, испытывают затруднения в работе при переходе на систему хранения больших данных типа Apache Hive. Это связано с непониманием того, как можно использовать в новой среде уже наработанные подходы и методы работы с данными.
В данной статье рассмотрены некоторые особенности использования языка SQL в реляционных СУБД и Apache Hive. Кроме того, проведен сравнительный обзор возможностей и подходов, а также применение партиционирования на практике.
Материал будет полезен специалистам младших и средних грейдов, которые используют в своей практике SQL, но имеют мало опыта в Hive или Postgres.
В начале рассмотрим отличие подходов и применение рассматриваемых технологий.
Параметр | PostgreSQL v 14 | Apache Hive v 3 |
Направление использования | OLTP (учёт операций) | OLAP (анализ данных) |
Основа | Реляционные операции | MapReduce Framework / FS HDFS |
Ссылочная целостность | Полная поддержка | Отсутствует |
Применимость в Big Data | DWH | DataLake |
Цель | Аналитика для бизнес-решений | Экономичное хранилище больших данных |
Процесс | ETL | ELT |
Хранение данных | Табличные пространства | Файловая система HDFS |
Сжатие данных | Отсутствует | LZO, gZIP, Snappy |
Реляционные СУБД чаще всего используются в системах учета операций, где важна скорость записи изменений и транзакционный подход – система OLTP. Hive же чаще всего используется, как дешевая среда хранения большого объема данных, и проведение аналитических исследований над этими данными – OLAP подход.
Основой любой реляционной СУБД служит реляционная алгебра, отношения между кортежами и операции над ними. Этот подход позволяет хранить большие объёмы структурированной информации. К плюсам реляционных СУБД можно отнести: согласованность данных, их структурируемость, стандартные подходы к организации хранилища, высокая производительность. Минусы: высокие аппаратные требования, высокая стоимость хранения данных, трудная масштабируемость.
В свою очередь, Hive использует другой подход. В его основе лежит использование фреймворка MapReduce и Hadoop HDFS в качестве системы хранения. Такой подход позволяет совместить возможность распределенных вычислений и дешевого хранилища данных. Также следует выделить высокую надежность хранения данных, их распределение по нодам и простоту расширения кластера путем добавления новых нод. К минусам можно отнести отсутствие единой структуры хранения данных, полной поддержки ACID, а также ограничения по операциям DML и низкую скорость записи данных.
Отдельно стоит рассмотреть ссылочную целостность. Функционал реляционных СУБД предоставляют разработчику несколько вариантов реализации ссылочной целостности: мы можем заставить систему обновлять зависимые записи, удалять их или менять внешний ключ. После установки правил обновления данных система сама будет следить за соблюдением ссылочной целостности и содержать базу в согласованном состоянии. Hive такого функционала не имеет, и варианты обновления согласованных данных разработчик должен решать самостоятельно.
Что касается применения рассматриваемых систем в качестве основы хранилища больших данных, то Postgres подходит для построения DWH и аналитики бизнес-решений. И как следствие – мы обязаны использовать затратный ETL процесс, реализованный на отдельных серверах для подготовки данных перед загрузкой в DWH.
В отличие от DWH, в Hive можно реализовать DataLake, что позволяет производить все операции над данными непосредственно внутри хранилища и использовать ELT-процесс.
Рассмотрим хранение данных. Для определения места хранения данных в Postgres используются табличные пространства, которые позволяют, например, выбрать более производительные диски хранилища для горячих данных и дешевые хранилища – для холодных. В Hive есть возможность выбора директории хранения данных в HDFS. Также есть возможность выбрать формат хранения данных в HDFS: от выбора формата зависит возможность использования ACID и DML операций. При использовании сжатия данные можно сократить в пять раз.
![](https://habrastorage.org/getpro/habr/upload_files/273/5c6/bdc/2735c6bdc695d936b93c66421ab56e8d.png)
Далее речь пойдет про особенности создания таблиц в рассматриваемых системах.
В Postgres создание таблиц реализовано по стандартам SQL. Мы можем задать описание полей, их ограничения и значение по умолчанию. Что касается создания таблиц в Hive, то тут есть некоторые отличия.
В Hive можно создать два типа таблиц: внешние (External) и управляемые (Managed), не считая Temp. Как было отмечено выше, от выбора типа таблицы зависят функциональные возможности, формат хранения и поддержка транзакций. В дополнении к тому, что таблицы External не поддерживают транзакции, нужно учесть, что обновление и удаление данных во внешних таблицах невозможно с использованием DML-операций UPDATE, MERGE, DELETE. Как в этом случае осуществить обновление или вставку данных, мы рассмотрим далее.
В качестве указателя места хранения данных в PostgreSQL используется параметр TABLESPACE, в Hive установка LOCATION в HDFS. Кроме того, в Hive есть возможность выбрать файл хранения данных и сжатие. В PostgreSQL и в Hive есть возможность указать партиционирование, о чем будет подробно разобрано далее. Обе системы позволяют создавать временные таблицы и осуществлять проверку на наличие уже существующей таблицы.
Параметр | PostgreSQL | Apache Hive |
Выбор места хранения данных | Табличные пространства TABLESPACE табл_пространство | Файловая система HDFS LOCATION hdfs_path |
Возможность выбора формата хранения | Отсутствует | Множество вариантов форматов хранения (ORC, Parquet и т.д.) STORED AS file_format |
Партиционирование | Отсутствует динамическое партиционирование Типы партиционирования, Range, List, Hash, составное партиционирование . | В Hive возможно динамическое партиционирование Тип партиционирования List. |
Временные таблицы | CREATE [TEMPORARY | TEMP] TABLE | CREATE TEMPORARY TABLE |
Проверка на существование таблицы | CREATE TABLE [ IF NOT EXISTS ] имя_таблицы (…) | CREATE [TEMPORARY | EXTERNAL] TABLE [IF NOT EXISTS] |
Очень часто в практике требуется узнать структуру таблицы, ее поля и другие данные. Для этого в СУБД реализованы отдельные SQL-команды и запросы.
Для PostgreSQL запрос к служебной таблице information_schema, который выведет поля таблицы, их типы и ограничения.
В Hive возможны несколько вариантов получения описания таблицы:
Команда DESCRIBE FORMATTED вернет описание полей таблицы в табличном представлении;
SHOW CREATE TABLE вернет DDL создания таблицы с указанием формата хранения и location.
Рассмотрим примеры.
PostgreSQL:
SELECT table_schema, table_name, column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'dim_account';
![](https://habrastorage.org/getpro/habr/upload_files/f9b/2f5/ebd/f9b2f5ebda16a91952dd0606584c4341.png)
Apache Hive:
DESCRIBE FORMATTED dim_account
![](https://habrastorage.org/getpro/habr/upload_files/995/eea/7c7/995eea7c706edc9a66d7de2864507891.png)
SHOW CREATE TABLE dim_account
![](https://habrastorage.org/getpro/habr/upload_files/ab8/6e7/559/ab86e7559c06067009a420b589d9a509.png)
Партиционирование таблиц.
При создании таблиц крайне важно сразу продумать способ разбиения таблиц на партиции. Это коренным образом влияет на производительность работы базы данных в целом, скорость выполнения запросов и возможности по модификации таблицы в будущем. Это справедливо как для реляционных СУБД, так для Hive.
Рассмотрим разные методы партиционирования и их особенности.
Партиция – это поименованный самостоятельный фрагмент памяти на дисках. Важно запомнить, что партиции влияют на физическое хранение данных. Партиционирование еще может называться секционированием.
Применяется для:
Повышения производительности работы SQL-запросов и DML-операций по модификации строк таблицы – за счет того, что операции чтения ограниченны партицией и ядру СУБД нет необходимости прочитывать всю таблицу на всех носителях;
Быстрого удаления значительного числа строк в больших таблицах за счет выполнения операции truncate секций;
Разбиения большой таблицы на оперативную и архивную части, в том числе выделения оперативной части на скоростные носители;
Снижения конкуренции за строки и индексы таблицы, в том числе уменьшения вероятности блокировок за счет физического разделения данных;
Обеспечения устойчивости функционирования таблиц, так как данные в таблице могут быть разнесены на разные хранилища, что значительно повышает надежность.
Бывают различные методы партиционирования: по диапазону ключа, списку ключа, хеш- партиционирование и составное партиционирование.
Рассмотрим на примере PostgreSQL.
Допустим, нам необходимо разбить таблицу счета (dim_account) на сегменты, где в первую очередь будут содержаться счета, открытые в определенную дату, с разбивкой по источнику данных. Таким образом, мы получаем первую разбивку по диапазону ключа, c ключом дата открытия счета. И вторая разбивка по списку ключа, с ключом система-источник.
![](https://habrastorage.org/getpro/habr/upload_files/d64/ac0/a35/d64ac0a35ba92d81a0dabb12e9c06bcf.png)
На данный момент функционал PostgreSQL не позволяет создавать партиции автоматически, поэтому разработчику требуется самому позаботиться о создании партиций при проектировании ETL-процесса.
Чтобы создать секционированную таблицу, необходимо указать команду partition by и поле партиционирования.
![](https://habrastorage.org/getpro/habr/upload_files/db2/e8f/195/db2e8f1957045baf06b1049afc64fc48.png)
Далее нам необходимо создать дочерние таблицы, которые будут разбивать таблицу по интервалу. В данном случае два интервала: за даты 29.11.2022 и 30.11.2022. В описании интервала первое значение включается в интервал, а второе – нет.
![](https://habrastorage.org/getpro/habr/upload_files/063/832/8cc/0638328ccfd580589d7313a13dc96b98.png)
В свою очередь, эти дочерние таблицы должны быть партиционированы по системе-источнику – полю sourceid. А значит, мы должны создать еще четыре дочерних таблицы секционирования по списку.
![](https://habrastorage.org/getpro/habr/upload_files/494/617/c21/494617c2118e6aa91a14ce4c3408f156.png)
Таким образом, разбивка исходной таблицы на две даты и два источника потребует создания шести дочерних таблиц. В самой исходной таблице физически данные не будут содержаться, а фактически данные будут храниться только в конечных четырех таблицах.
В итоге мы получили структуру, представленную на рисунке: основная таблица, две партиционированные дочерние таблицы и четыре конечные таблицы хранения данных.
![](https://habrastorage.org/getpro/habr/upload_files/803/f0a/d46/803f0ad46989fa6114d077d9156f5af4.png)
Рассмотрим пример вставки данных в исходную таблицу.
![](https://habrastorage.org/getpro/habr/upload_files/2d1/cfe/9c8/2d1cfe9c8c4227b1db521bb1b01591f8.png)
В условии where есть указание на партицию, указана дата открытия счета и система-источник. Фактически данные вставятся не в исходную таблицу, а в партицию abs_dim_account_2022_11_30.
![](https://habrastorage.org/getpro/habr/upload_files/f03/f42/f23/f03f42f237e27c3c2aa6248006ca32cd.png)
Для того, чтобы просмотреть партиции исходной таблицы и количество записей, в них следует выполнить представленный скрипт.
![](https://habrastorage.org/getpro/habr/upload_files/919/b32/184/919b321843c8d4e4ea1a7f2437713433.png)
Видим три партиции с данными у родительской таблицы.
![](https://habrastorage.org/getpro/habr/upload_files/0ac/4f0/0cc/0ac4f00cc8bd7b0a1a8712c54c04498c.png)
При обращении к исходной таблице в условии where мы должны указать ключи партиционирования. В этом случае запрос не будет просматривать всю таблицу, а сразу считает данные с конкретной партиции.
![](https://habrastorage.org/getpro/habr/upload_files/553/edd/166/553edd1664924d5a01be6dba99a077c9.png)
Если известно имя партиции, в которой содержатся нужные записи, то возможно обращение непосредственно к этой партиции по имени таблицы.
![](https://habrastorage.org/getpro/habr/upload_files/5b7/86c/89d/5b786c89d102df78fef92b641bbcc019.png)
Два приведенных примера вернут одинаковый набор данных.
![](https://habrastorage.org/getpro/habr/upload_files/fa6/bf6/4e5/fa6bf64e538579f646eaf63f81c93bbc.png)
Как видим на примере плана запроса селекта к родительской таблице, по факту происходит обращение непосредственно к таблице партиции.
![](https://habrastorage.org/getpro/habr/upload_files/170/505/f59/170505f5944a0e1c900b2d9bc8af3abd.png)
Если же мы выполним данный запрос без учета партиций, то увидим, насколько дольше он будет обрабатываться. СУБД проверит все партиции на условие. В этом и заключается прирост производительности при использовании партиций.
![](https://habrastorage.org/getpro/habr/upload_files/f09/f44/88f/f09f4488ff0a27b585ab5545bfd76dd1.png)
Способность обращения с партициями как с таблицами открывает возможность к манипуляции с данными целыми участками, без затрагивания основной таблицы.
Например, запрос TRUNCATE быстро очистит данные без использования затратной операции DELETE. Эту операцию можно использовать при повторной заливке данных.
DROP TABLE удалит партицию и все данные.
С помощью ALTER TABLE можно поменять TABLESPACE и перенести горячие данные на более производительные носители.
![](https://habrastorage.org/getpro/habr/upload_files/5e8/4b1/51f/5e84b151f68f60e34618c9071b04955b.png)
Рассмотрим на примере Hive.
Партиция (partiton) в Hive — это результат разделения таблицы на отдельные части, которые физически хранятся в разных файлах на HDFS.
![](https://habrastorage.org/getpro/habr/upload_files/068/688/0db/0686880db96dc26a39814a386a2248a1.png)
Партиционирование в Hive является нативным функционалом и не требует создания подтаблиц как в случае PostgreSQL. Все партиции создаются автоматически и распределяются в каталогах HDFS.
Указав ключи партиционирования в блоке партиции, Hive в дальнейшем сам будет разделять поступающие данные по партициям.
Отдельно разберем плюсы и минусы партиционирования в Hive.
Плюсы:
Горизонтальное распределение вычислительной нагрузки по нодам кластера;
Время ответа на запрос сокращается до обработки небольшой части данных вместо поиска по всему набору данных;
Быстрое удаление значительного числа строк в больших таблицах;
Разбиение большой таблицы на оперативную и архивную части;
Возможность загрузки большого массива данных без блокировки доступа к основной таблице.
Минусы:
Ограничение по количеству партиций: не рекомендуется использовать более 10 тысяч партиций, что может привести к сильному замедлению работы кластера;
Партиции оптимизируют запросы, основанные на предложениях Where, но не эффективны при использовании группировки;
При обработке большого количества Mapreduce огромное количество разделов приведет к огромному количеству задач (которые будут выполняться в отдельной JVM), что создает большие накладные расходы на поддержку запуска и отключения JVM.
Рассмотрим варианты создания партиций в Hive.
Первый вариант – это загрузка данных из внешнего файла, когда таблицы в Hive нет. В данном случае Hadoop прочитает предложенный файл и разместит полученные данные в каталоге HDFS, выделенном для данной таблицы.
![](https://habrastorage.org/getpro/habr/upload_files/084/33f/46f/08433f46f39c4c38a7c9fc50db333fa8.png)
Второй вариант – загрузка данных из таблицы уже имеющейся в Hive. В данном случае есть два варианта загрузки, с добавлением данных в партицию или с перезаписью партиций. В случае, если нам надо перезаписать партицию полностью, например, при повторной выгрузке данных с источника, необходимо использовать конструкцию INSERT OVERWRITE TABLE. Тогда Hadoop перезапишет все файлы, находящиеся в каталоге партиции. Если же нам требуется добавить данные в существующую патрицию, то необходимо использовать конструкцию INSERT TABLE. В данном случае Hadoop допишет данные в существующий файл партиции.
Как мы уже знаем, Hive не поддерживает инструкции SQL UPDATE, MERGE, DELETE для External таблиц, а это большинство таблиц, используемых на практике. Тогда встает вопрос — как же тогда изменить часть данных внутри партиции? Для этого можно использовать вариант с перезаписью партиций совместно с вариантом добавления данных.
Рассмотрим пример.
Нам требуется за определенную дату для всех счетов, поступивших с источника ABS, прописать в description дату загрузки, филиал и указание на ABS.
Первым действием мы перезапишем нужную партицию, исключив из выборки все данные по источнику ABS.
![](https://habrastorage.org/getpro/habr/upload_files/aaf/425/06b/aaf42506b95d3e69fbcb6819cf648d3f.png)
Вторым действием допишем в уже обновленную партицию все записи по источнику ABS c указанием необходимого DESCRIPTION.
![](https://habrastorage.org/getpro/habr/upload_files/84f/5fc/45e/84f5fc45e021208c4a972b7c9556c306.png)
Таким образом, мы получим аналог UPDATE, реализованный двумя шагами. Так же можно реализовать и DELETE, просто исключив не нужные записи условием запроса.
Во всех приведенных выше примерах Hive, мы указывали – в какую конкретно партицию ему производить запись (PARTITION(BDATE = '2022-11-30', BRANCH = 'R19')). Но Hive поддерживает и динамическое партиционирование, когда на основе заданных ключей партиционирования и входных данных он сам решает – какие партиции следует создать и как распределить по ним данные.
Для этого необходимо указать соответствующие параметры для среды Hive. Они представлены на рисунке.
![](https://habrastorage.org/getpro/habr/upload_files/51c/b66/da7/51cb66da7f30a41bd0cf3e9ce709083b.png)
В данном случае в параметрах PARTITION мы указываем не конкретное значение, а только ключи партиционирования.
![](https://habrastorage.org/getpro/habr/upload_files/1a7/022/ffd/1a7022ffdf2bb421d8001b62ddb75835.png)
Если мы рассмотрим состояние партиций до вставки при помощи команды SHOW PARTITIONS, то увидим, что у таблицы только одна рабочая партиция бизнес-дата = 2022-11-30 и филиал R19, а также дефолтная партиция.
![](https://habrastorage.org/getpro/habr/upload_files/fc4/a99/961/fc4a9996116777accdc41bd27fc93640.png)
После выполнения INSERT в where, части которого мы указываем, все данные за дату больше 2023-01-01. Hive прочитает весь предложенный ему набор данных, сам выделит партиции на основе ключей партицонирования и распределит полученный набор на конечные партиции. В данном случае к таблице добавится еще три партиции.
![](https://habrastorage.org/getpro/habr/upload_files/48a/444/a84/48a444a84d372c65b57c62fac915e37d.png)
Как и в случае с PostgreSQL, в Hive над партициями можно производить операции через команду SQL ALTER TABLE. Например, для переноса партиции в другую директорию HDFS необходимо указать SET LOCATION.
![](https://habrastorage.org/getpro/habr/upload_files/3c7/551/883/3c755188338b84b6a887678339bf1b05.png)
Бывают случаи, когда к таблице надо добавить существующие партиции. Например, при разборке инцидентов, когда данные переносятся с прода на тест. Тогда, зная директорию хранения партиции, ее легко можно добавить к существующей таблице.
![](https://habrastorage.org/getpro/habr/upload_files/31f/181/814/31f1818143419d53e20dfda71ff57c9f.png)
Также партиции можно удалять через команду DROP PARTITION.
![](https://habrastorage.org/getpro/habr/upload_files/26c/7c6/df7/26c7c6df7d60dc646e4bcc1bea3ccce0.png)
Рассмотрим пример запроса данных из партиционированной таблицы.
![](https://habrastorage.org/getpro/habr/upload_files/35b/062/b41/35b062b41ecf5d285a8590f603f099b3.png)
Имеется таблица счета, партиционированная по бизнес дате и филиалу. Следовательно, для того, чтобы запрос был максимально эффективным, мы эти поля должны указать в ограничении условия WHERE. Иначе Hive будет считывать все партиции, что приведет к значительному увеличению времени выполнения запроса.
Партиционирование является мощным инструментом для управления данными и ускорения выполнения запросов как для классических реляционных систем (PostgreSQL), так и в Hive.
Подведем итог
Мы рассмотрели некоторые особенности использования языка SQL применительно к классической реляционной схеме на примере PostgreSQL и системы обработки больших данных на основе Apache Hive. Несмотря на то, что эти системы изначально создавались для разных целей, именно возможности языка SQL позволяют нам работать в этих системах, не обращая внимания на архитектурные различия.
Язык SQL, появившийся в 1986 году, до сегодняшнего дня не утратил своей актуальности. Последний стандарт SQL принят в 2016 году, но помимо этого появляются новые СУБД, которые постоянно вносят свои фичи в развитие этого языка, о которых сложно рассказать в рамках одной статьи, поэтому стоит только пожелать вам не останавливаться в постижении глубин языка SQL.