Блог

Главная - Вопросы по программированию - Разделите запрос Athena по дате создания S3

Разделите запрос Athena по дате создания S3

#amazon-s3 #amazon-athena #aws-glue

#amazon-s3 #amazon-athena #aws-glue

Вопрос:

У меня есть корзина S3 с ~ 70 миллионами JSON (~ 15 ТБ) и таблицей athena для запроса по метке времени и некоторым другим ключам, определенным в JSON.

Гарантируется, что временная метка в JSON более или менее равна дате создания S3 в JSON (или, по крайней мере, достаточно равна для целей моего запроса)

Могу ли я каким-то образом улучшить производительность запросов (и стоимость), добавив createddate как нечто вроде «раздела», что, как я понимаю, возможно только для префиксов / папок?

редактировать: в настоящее время я имитирую это, используя S3 inventory CSV для предварительной фильтрации по CreatedDate, а затем загружаю все JSON-файлы и выполняю остальную фильтрацию, но я хотел бы сделать это полностью внутри athena, если это возможно

Ответ №1:

Нет способа заставить Athena использовать такие вещи, как метаданные объекта S3, для планирования запросов. Единственный способ заставить Athena пропустить чтение объектов — это организовать объекты таким образом, чтобы можно было настроить разделенную таблицу, а затем выполнить запрос с фильтрами по ключам раздела.

Похоже, у вас есть представление о том, как работает разделение в Athena, и я предполагаю, что есть причина, по которой вы им не пользуетесь. Однако в интересах других пользователей, сталкивающихся с подобными проблемами, я начну с объяснения того, что вы можете сделать, если сможете изменить способ организации объектов. В конце я дам альтернативное предложение, возможно, вы захотите сразу перейти к нему.

Я бы посоветовал вам упорядочить объекты JSON, используя префиксы, которые содержат некоторую часть временных меток объектов. Сколько именно зависит от способа запроса данных. Вы не хотите, чтобы он был слишком детализированным и не слишком грубым. Если сделать его слишком детализированным, Athena потратит больше времени на перечисление файлов на S3, если сделать его слишком грубым, это приведет к чтению слишком большого количества файлов. Если наиболее распространенный период запросов составляет месяц, это хорошая детализация, если наиболее распространенный период составляет пару дней, то, вероятно, лучше день.

Например, если для вашего набора данных лучше всего использовать детализацию day, вы могли бы организовать объекты с помощью ключей, подобных этому:

 s3://some-bucket/data/2019-03-07/object0.json
s3://some-bucket/data/2019-03-07/object1.json
s3://some-bucket/data/2019-03-08/object0.json
s3://some-bucket/data/2019-03-08/object1.json
s3://some-bucket/data/2019-03-08/object2.json

Вы также можете использовать схему разделения в стиле Hive, которую ожидают другие инструменты, такие как Glue, Spark и Hive, поэтому, если у вас нет причин не делать этого, это может избавить вас от огорчений в будущем:

 s3://some-bucket/data/created_date=2019-03-07/object0.json
s3://some-bucket/data/created_date=2019-03-07/object1.json
s3://some-bucket/data/created_date=2019-03-08/object0.json

Я выбрал имя created_date здесь, я не знаю, какое имя было бы подходящим для ваших данных. Вы можете использовать just date , но не забывайте всегда заключать его в кавычки (и по-разному в DML и DDL …), поскольку это зарезервированное слово.

Затем вы создаете разделенную таблицу:

 CREATE TABLE my_data (
  column0 string,
  column1 int
)
PARTITIONED BY (created_date date)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe' 
STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION 's3://some-bucket/data/'
TBLPROPERTIES ('has_encrypted_data'='false')

Затем в некоторых руководствах вам будет предложено выполнить MSCK REPAIR TABLE загрузку разделов для таблицы. Если вы используете разделение в стиле Hive (т.Е. …/created_date=2019-03-08/… ), вы можете это сделать, но это займет много времени, и я бы не рекомендовал это. Вы можете сделать это намного лучше, добавив разделы вручную, что вы делаете следующим образом:

 ALTER TABLE my_data ADD
  PARTITION (created_date = '2019-03-07') LOCATION 's3://some-bucket/data/created_date=2019-03-07/'
  PARTITION (created_date = '2019-03-08') LOCATION 's3://some-bucket/data/created_date=2019-03-08/'

Наконец, при запросе таблицы обязательно включите created_date столбец, чтобы предоставить Athena информацию, необходимую для чтения только объектов, имеющих отношение к запросу:

 SELECT COUNT(*)
FROM my_data
WHERE created_date >= DATE '2019-03-07'

Вы можете убедиться, что запрос будет дешевле, наблюдая за разницей в сканируемых данных при переходе, например, с created_date >= DATE '2019-03-07' на created_date = DATE '2019-03-07' .

Если вы не можете изменить способ организации объектов в S3, существует плохо документированная функция, которая позволяет создавать секционированную таблицу, даже если вы не можете изменить объекты данных. Что вы делаете, так это создаете те же префиксы, что я предлагал выше, но вместо перемещения объектов JSON в эту структуру вы помещаете файл с именем symlink.txt в префикс каждого раздела:

 s3://some-bucket/data/created_date=2019-03-07/symlink.txt
s3://some-bucket/data/created_date=2019-03-08/symlink.txt

В каждом symlink.txt вы вводите полный URI S3 файлов, которые вы хотите включить в этот раздел. Например, в первом файле вы могли бы поместить:

 s3://data-bucket/data/object0.json
s3://data-bucket/data/object1.json

и второй файл:

 s3://data-bucket/data/object2.json
s3://data-bucket/data/object3.json
s3://data-bucket/data/object4.json

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

 CREATE TABLE my_data (
  column0 string,
  column1 int
)
PARTITIONED BY (created_date date)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe' 
STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.SymlinkTextInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION 's3://some-bucket/data/'
TBLPROPERTIES ('has_encrypted_data'='false')

Обратите внимание на значение INPUTFORMAT свойства.

Разделы добавляются точно так же, как и для любой разделенной таблицы:

 ALTER TABLE my_data ADD
  PARTITION (created_date = '2019-03-07') LOCATION 's3://some-bucket/data/created_date=2019-03-07/'
  PARTITION (created_date = '2019-03-08') LOCATION 's3://some-bucket/data/created_date=2019-03-08/'

Единственная документация по этой функции, связанная с Athena, с которой я столкнулся, — это документы инвентаризации S3 для интеграции с Athena.

Комментарии:

1. Вы сказали «Вы можете использовать просто date » в своем ответе выше, но я попытался создать раздел с именем date, и он не запустил запрос. Я пробовал использовать одинарные и двойные кавычки и обратные метки, но это не сработало. Я присвоил индексу псевдоним date as dt , но тогда все мои файлы в s3 имеют префикс date , а не dt . Предположительно, для использования в этом примере префикса файла в s3 должен быть dt ?

2. Кроме того, у вас есть ежедневные разделы, но вы предлагаете не использовать MSCK REPAIR TABLE . Как эти индексы обычно обновляются затем автоматическим способом? Очевидно, что запускать alter table или даже таблицу восстановления вручную каждый день непрактично.

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

4. @BenSwinburne У меня есть два предложения о том, как автоматизировать создание раздела: если вы разделяете только по времени (например, «created_date»), вы можете запускать лямбда-функцию в последний день каждого месяца (настройте расписание с помощью Event Bridge), которая добавляет разделы следующего месяца (на S3 не обязательно должны быть какие-либо данные, разделы — это просто метаданные). Если вы разделяете больше времени, используйте триггер S3, который запускает лямбда-функцию для каждого нового объекта и проверяет, нужно ли добавлять новый раздел для каждого объекта (вы также можете поместить события в очередь, чтобы избежать запуска для каждого нового объекта).

5. При использовании Lambda я бы рекомендовал использовать Glue API непосредственно для создания разделов, используя вызовы BatchCreatePartition или CreatePartition . Это сильно отличается от выполнения SQL в Athena, и вам нужно указать гораздо больше, но это быстрее.

Ответ №2:

Я начал работать с ответом Тео, и он был очень близок (спасибо, Тео за отличный и очень подробный ответ), но при добавлении нескольких разделов в соответствии с документацией вам нужно указать «ДОБАВИТЬ» только один раз в начале запроса.

Я попытался указать «ДОБАВИТЬ» в каждой строке в примере Тео, но получил ошибку. Однако это работает, если указано только один раз. Ниже приведен формат, который я использовал, который был успешным:

 ALTER TABLE db.table_name ADD IF NOT EXISTS
 PARTITION (event_date = '2019-03-01') LOCATION 's3://bucket-name/2019-03-01/'
 PARTITION (event_date = '2019-03-02') LOCATION 's3://bucket-name/2019-03-02/'
 PARTITION (event_date = '2019-03-03') LOCATION 's3://bucket-name/2019-03-03/'
 ...

Комментарии:

1. Извините за плохой синтаксис в моем ответе, я это исправил.