При условии, что ваши файлы данных содержат дату и время, включенные в их имена, вы можете использовать «секретную» переменную $PATH в запросе Athena. Затем вы можете использовать regexp_extract и функции datetime для создания столбцов, которые затем можно использовать в запросе CTAS для разделения ваших данных.
Вот структура фиктивных файлов в моем S3:
s3://main-bucket/questions=59541533
├── folder-1
| ├── file1-2019-01-01.json
| ├── file1-2019-01-02.json
| ├── file1-2019-02-01.json
| └── file1-2019-02-02.json
├── folder-2
| ├── file1-2019-01-01.json
| ├── file1-2019-01-02.json
| ├── file1-2019-02-01.json
| └── file1-2019-02-02.json
Затем я определил таблицу, которая указывает на folder-1:
CREATE EXTERNAL TABLE `stackoverflow`.`question_59541533_v1`(
`foo` int,
`bar` int)
ROW FORMAT SERDE
'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
'paths'='row,uf')
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
's3://main-bucket/questions=59541533/folder-1'
TBLPROPERTIES (
'classification'='json',
'compressionType'='none'
'typeOfData'='file')
Вот как может выглядеть запрос, где я извлекаю дату из имени файла с довольно наивным regexp_extract < / a> поскольку имена файлов детерминированы.
SELECT
YEAR("file_date") as "year",
MONTH("file_date") as "month",
DAY("file_date") as "day"
FROM(
SELECT
DATE(regexp_extract("$PATH", '(.)*/(.)*([0-9]{4}-[0-9]{2}-[0-9]{2}).json', 3)) as "file_date",
FROM
"stackoverflow"."question_59541533_v1"
)
ORDER BY month, day
Результат:
year | month | day
---------------------
2019 | 1 | 1
2019 | 1 | 2
2019 | 2 | 1
2019 | 2 | 2
Теперь вы можете преобразовать данные и разделить их за один раз с помощью запроса CTAS (не забудьте выбрать все исходные данные).
CREATE TABLE partitioned_and_in_parquet
WITH (
format = 'PARQUET',
external_location = 's3://new_s3_location/',
partitioned_by = ARRAY['year', 'month', 'day']
) AS (
SELECT
* , -- select existing data
YEAR("file_date") as "year",
MONTH("file_date") as "month",
DAY("file_date") as "day"
FROM(
SELECT
* , -- select existing data
DATE(regexp_extract("$PATH", '(.)*/(.)*([0-9]{4}-[0-9]{2}-[0-9]{2}).json', 3)) as "file_date",
FROM
"stackoverflow"."question_59541533_v1"
)
)
Преимущество этого подхода в том, что вы разделяете данные и конвертируете их в паркет за один раз. Кроме того, вы должны заранее знать, сколько с вас будет взиматься плата, поскольку цены на Athena зависят от объема сканируемых данных, то есть 1 ТБ = 5 $.
Одним из основных недостатков этого подхода является то, что запрос CTAS имеет ограничение на запись только 100 разделов за раз. Поэтому, если у вас есть более 3 месяцев запроса данных, запрос завершится неудачно, если вы не добавите предложение WHERE, чтобы преодолеть это. Вы можете $PATH в пункте WHERE. Я помню, что я тестировал его некоторое время назад, и даже когда файлы находились в одной «папке», Афина не сканировала содержимое файлов, следовательно, с вас не будет взиматься многократная оплата. Но я предлагаю это проверить. Как вариант, просто остановитесь на месячном уровне.
Еще одна вещь, которую вам необходимо учитывать, - это количество выходных файлов запроса CTAS и их размеры. Как правило, вы можете использовать сегментирование, т.е. bucketed_by = ARRAY['some_column'], bucket_count = 3, чтобы управлять им, но это может увеличить время выполнения.
В любом случае, есть много вещей, с которыми можно поиграть, если вы решите сделать это с Афиной.
person
Ilya Kisil
schedule
01.01.2020
