Отображение имени файла в столбце AWS Athena: руководство

Быстрый ответ

Чтобы извлечь имя файла непосредственно из SQL-запроса в AWS Athena, вы можете использовать псевдостолбец $path. Для этого добавьте его в запрос следующим образом (замените your_table на наименование вашей таблицы):

SELECT "$path" AS source_file, *
FROM your_table;

В результате выполнения запроса каждая строка будет включать путь к файлу S3 в виде нового столбца.

Работа с большими объёмами данных

Когда приходится столкнуться с огромными объёмами файлов в S3 (например, с тысячами и даже большим количеством), Athena может стать незаменимым инструментом. Вот как можно использовать $path для эффективного отображения имён файлов в больших наборах данных:

SELECT "$path" AS file_source, COUNT(*) AS records
FROM data_lake
GROUP BY "$path";

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

Разбор пути к файлу

Важно учитывать, что $path возвращает полную URI S3 файла, а не только его имя. Если нужно получить именно имя файла, потребуется применить строковые функции для извлечения нужного фрагмента:

SELECT SUBSTRING("$path", CHAR_LENGTH('$path') – POSITION('/' IN REVERSE('$path')) + 2) AS file_name, 
       *
FROM your_table;

Здесь используется поиск последнего вхождения символа "/" и извлечение всего, что следует за ним. Это аналогично ситуации, когда вам нарассказывают всю биографию человека, а вам надо знать только его имя, и вы терпеливо просите: "Извините, назовите, пожалуйста, только имя."

Почему мы выбираем $path?

Забудьте про INPUT__FILE__NAME, которую часто применяли в Hive, и начинайте использовать $path в Athena для более эффективного получения пути к файлу.

Визуализация

AWS Athena можно представить как библиотеку, в которой каждый файл – это книга со своим уникальным названием:

Библиотека (🏛): [Отчеты2020.sql, ПланыБюджета.csv, СтатистикаПользователей.json]

Вам, вероятно, больше понравится, если каждая "книга" откроется на участке, где имеется запись:

В Каждой Книге Теперь Можно Найти:
- Данные: [...]
- Примечание: "Эти данные из файла ПланыБюджета.csv"

На практике в Athena это будет так:

SELECT "$path" AS filename, *
FROM external_table
WHERE "$path" LIKE '%/ПланыБюджета.csv';

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

Обращайте внимание на производительность

При использовании $path имейте в виду следующее:

1. $path может увеличить время выполнения запроса.
2. Athena расплачивается в зависимости от объёма сканируемых данных.

Для оптимизации работы с большими наборами данных распределите их по партициям в S3. Вот пример такого запроса:

SELECT "$path", COUNT(*)
FROM partitioned_table
WHERE year = '2022' AND month = '01'
GROUP BY "$path";

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

Поддерживайте порядок

Наконец, не позволяйте чтобы $path запутывал результаты запросов. Используйте псевдонимы и выбирайте конкретные столбцы для скорейшей ориентации в данных:

SELECT
  SUBSTRING("$path", CHAR_LENGTH('$path') – POSITION('/' IN REVERSE('$path')) + 2) AS file_name, 
  column1, 
  column2
FROM table_name;

Такой подход напомниает методику Мари Кондо – сохраняйте лишь то, что действительно важно, то есть только нужные данные.

Полезные материалы

1. Создание таблиц в Athena – Amazon Athena
2. Запросы к каталогу данных AWS Glue – Amazon Athena
3. Анализ данных в S3 с помощью Amazon Athena | Блог о больших данных AWS
4. Разделение данных в Athena – Amazon Athena
5. Топ-10 советов по настройке производительности для Amazon Athena | Блог о больших данных AWS