Полное руководство: Запросы к S3 с помощью Athena без создания таблиц

Да, вы можете запрашивать данные непосредственно из бакета S3 с помощью AWS Athena без создания постоянных таблиц, используя внешние схемы, операторы CTAS (CREATE TABLE AS SELECT) или CTE с внешними ссылками. Хотя истинные временные таблицы не сохраняются в Athena, вы можете создавать временные ссылки на данные S3 с помощью одной команды SQL через различные методы, которые устраняют необходимость в определениях постоянных таблиц.

Содержание

• Запрос данных S3 с использованием внешних схем
• Использование CTAS для одноразового анализа
• CTE с внешними ссылками
• Методы запросов с одной командой
• Лучшие практики для временных запросов S3

Запрос данных S3 с использованием внешних схем

AWS Athena позволяет запрашивать данные S3 непосредственно с использованием внешних схем без создания постоянных таблиц в вашей базе данных. Этот подход использует синтаксис CREATE EXTERNAL TABLE, но вы можете изменить его для создания временных ссылок.

CREATE EXTERNAL TABLE IF NOT EXISTS temp_orders (
    order_id BIGINT,
    customer_id BIGINT,
    order_date TIMESTAMP,
    total_amount DECIMAL(10,2)
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerDe'
WITH SERDEPROPERTIES (
    'separatorChar' = ',',
    'quoteChar' = '"',
    'escapeChar' = '\\'
)
LOCATION 's3://your-bucket/path/to/orders/'
TBLPROPERTIES (
    'has_encrypted_data'='false',
    'skip.header.line.count'='1'
);

Хотя это создает определение таблицы, вы можете удалить его сразу после использования:

-- Запрос данных
SELECT * FROM temp_orders WHERE order_date >= '2024-01-01';

-- Удаление временной таблицы
DROP TABLE temp_orders;

Основное преимущество заключается в том, что вам не нужно управлять этим определением таблицы постоянно - его можно создать, использовать и удалить в том же сеансе.

Использование CTAS для одноразового анализа

Операторы CTAS (CREATE TABLE AS SELECT) особенно полезны для создания временных результатов анализа без постоянного хранения. Вы можете создать временную таблицу из данных S3, а затем запросить ее:

-- Создание временной таблицы из данных S3 одной командой
CREATE TEMPORARY TABLE temp_sales_analysis AS
SELECT 
    customer_id,
    COUNT(*) as order_count,
    SUM(total_amount) as total_spent,
    AVG(total_amount) as avg_order_value
FROM EXTERNAL TABLE orders (
    order_id BIGINT,
    customer_id BIGINT,
    order_date TIMESTAMP,
    total_amount DECIMAL(10,2)
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerDe'
LOCATION 's3://your-bucket/sales/orders/'
GROUP BY customer_id;

-- Запрос временного результата
SELECT customer_id, total_spent, order_count
FROM temp_sales_analysis
WHERE total_spent > 1000;

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

CTE с внешними ссылками

Хотя вы не можете напрямую ссылаться на данные S3 в CTE без предварительного определения таблицы, вы можете обойти это ограничение, объединив CTE с определениями внешних таблиц. Вот как создать шаблон временной ссылки:

-- Определение структуры внешней таблицы в CTE
WITH external_orders AS (
    SELECT * FROM EXTERNAL TABLE orders (
        order_id BIGINT,
        customer_id BIGINT,
        order_date TIMESTAMP,
        total_amount DECIMAL(10,2),
        product_id STRING,
        quantity INT
    )
    ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerDe'
    LOCATION 's3://your-bucket/orders/'
    TBLPROPERTIES ('skip.header.line.count'='1')
),
-- Создание аналитических CTE
customer_summary AS (
    SELECT 
        customer_id,
        COUNT(*) as total_orders,
        SUM(total_amount) as lifetime_value
    FROM external_orders
    GROUP BY customer_id
),
monthly_stats AS (
    SELECT 
        DATE_TRUNC('month', order_date) as month,
        COUNT(*) as monthly_orders,
        SUM(total_amount) as monthly_revenue
    FROM external_orders
    GROUP BY DATE_TRUNC('month', order_date)
)
-- Финальный запрос с использованием CTE
SELECT 
    cs.customer_id,
    cs.total_orders,
    cs.lifetime_value,
    ms.month,
    ms.monthly_orders,
    ms.monthly_revenue
FROM customer_summary cs
JOIN monthly_stats ms ON cs.customer_id = ms.customer_id
WHERE cs.lifetime_value > 500
ORDER BY cs.lifetime_value DESC;

Этот подход дает вам преимущества CTE при сохранении возможности запрашивать данные S3 непосредственно.

Методы запросов с одной командой

Для истинных запросов с одной командой без каких-либо определений таблиц вы можете использовать встроенную поддержку Athena для запроса файлов непосредственно в S3 с помощью оператора SELECT с предложениями FROM, которые ссылаются на пути S3:

-- Запрос CSV-файлов непосредственно из S3
SELECT 
    order_id,
    customer_id,
    order_date,
    total_amount
FROM 's3://your-bucket/orders/*.csv'
WHERE total_amount > 100
   AND order_date >= '2024-01-01'
LIMIT 1000;

Обратите внимание, что этот подход имеет некоторые ограничения:

• Вывод схемы может работать не идеально с сложными типами данных
• Производительность может быть ниже, чем с правильно определенными таблицами
• Вы теряете некоторые функции оптимизации Athena

Для большего контроля в одной команде:

-- Использование встроенного определения схемы
SELECT 
    cast(order_id as bigint) as order_id,
    cast(customer_id as bigint) as customer_id,
    cast(order_date as timestamp) as order_date,
    cast(total_amount as decimal(10,2)) as total_amount
FROM (
    SELECT 
        split(line, ',')[0] as order_id,
        split(line, ',')[1] as customer_id,
        split(line, ',')[2] as order_date,
        split(line, ',')[3] as total_amount
    FROM (
        SELECT line FROM
        EXTERNAL TABLE raw_orders (
            line STRING
        )
        LOCATION 's3://your-bucket/orders/'
    )
) parsed_data
WHERE cast(total_amount as decimal(10,2)) > 100
   AND cast(order_date as timestamp) >= '2024-01-01';

Лучшие практики для временных запросов S3

При работе с временными запросами S3 в Athena учитывайте эти лучшие практики:

Оптимизация производительности

• Разделяйте данные на разделы: При запросе больших наборов данных используйте разделенные пути S3 для уменьшения объема сканируемых данных
• Используйте форматы файлов: Предпочитайте Parquet или ORC вместо CSV для лучшего сжатия и производительности запросов
• Используйте рабочие группы Athena: Настраивайте рабочие группы для ограничения затрат на сканирование данных

-- Пример с разделенными данными
SELECT 
    region,
    product_category,
    SUM(sales_amount) as total_sales
FROM 's3://your-bucket/sales/year=2024/month=01/*.parquet'
GROUP BY region, product_category;

Управление затратами

• Используйте предложения LIMIT: Всегда ограничивайте результирующие наборы при исследовании данных
• Реализуйте выборку столбцов: Выбирайте только те столбцы, которые вам нужны

-- Эффективный запрос с выборкой столбцов
SELECT order_id, customer_id, total_amount
FROM 's3://your-bucket/orders/*.parquet'
WHERE total_amount > 1000
LIMIT 1000;

Обработка ошибок

• Проверяйте данные перед запросом: Используйте простые запросы для проверки качества данных сначала
• Обрабатывайте значения NULL: Учитывайте возможные значения NULL в ваших запросах

-- Надежный запрос с обработкой NULL
SELECT 
    COALESCE(customer_id, 0) as customer_id,
    COUNT(DISTINCT order_id) as order_count
FROM 's3://your-bucket/orders/*.parquet'
WHERE total_amount IS NOT NULL
GROUP BY customer_id;

Заключение

• Прямой запрос S3 возможен в Athena без постоянных таблиц с использованием внешних схем, операторов CTAS или прямых ссылок на пути S3
• CTE могут работать с данными S3 при объединении с определениями внешних таблиц, позволяя выполнять сложный многошаговый анализ в одной команде SQL
• Методы с одной командой предлагают удобство, но могут пожертвовать производительностью и контролем схемы по сравнению с правильно определенными таблицами
• Лучшие практики включают разделение данных, использование эффективных форматов файлов, реализацию выборки столбцов и управление затратами с помощью соответствующей конфигурации рабочих групп

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

Источники

1. Документация AWS Athena - Запрос данных в S3
2. Лучшие практики AWS Athena - Оптимизация производительности
3. Справочник по SQL AWS Athena - CREATE TABLE AS SELECT
4. Форматы данных AWS Athena - CSV, JSON, Parquet
5. Рабочие группы AWS Athena - Управление затратами