Amazon Athena per leggere i dati su S3 con un linguaggio SQL
da Massimo Della Rovere · pubblicato il 19 luglio, 2017 · modificato il 28 luglio, 2017

Questo servizio permette di interrogare gli oggetti presenti in un bucket S3 con un linguaggio compatibile SQL. In questo modo si evitano tutti quei complessi script che siamo costretti molte volte a scrivere per gestire gli oggetti presenti in S3. Athena è un servizio serverless e non necessita di nessuna infrastruttura per l’esecuzione delle query che verranno eseguite da un sistema batch presente in Amazon AWS.

Introduzione

Se devo dire la verità, dopo l’annuncio ufficiale di questo servizio non ho avuto subito la curiosità di vedere come funzionava, forse non avevo ancora inquadrato il reale utilizzo, fino al giorno in cui ho dovuto eseguire un’analisi di log  molto complessa e su un numero importante di dati che riguardava il servizio di Amazon Cloudfront. Proprio durante questa esperienza ho scoperto le grandi qualità di questo servizio e la straordinaria semplicità con cui si configurano le tabelle e sui cui estrarre i dati con comandi SQL.

Praticamente è come avere un servizio di (ELT Extract, load, transform) automatico, creo una tabella con i riferimenti dei campi presenti su un file di testo e da quel momento in avanti mi dimentico di lavorare con dei file, penso solo ad interrogare i dati come un vero database, infatti avremo a disposizione anche dei driver JDBC con cui connettersi ad Athena tramite applicazioni esterne o client GUI che accettano driver JDBC.

Funzionamento

Configurare Amazon Athena è molto semplice, basta andare sulla management console e selezionare il servizio nella regione geografica desiderata. Ovviamente dobbiamo usare la stessa regione dei bucket di Amazon S3 che vogliamo analizzare, altrimenti avremmo un grosso problema di banda e il servizio non sarebbe più performante. Una volta che siamo nella pagina del servizio dobbiamo creare un database e la nostra prima tabella usando un comando SQL di CREATE dove specificare i campi presenti nel file e il suo formato.

Se volete fare delle prove prima di iniziare con i vostri dati trovate una serie di risorse che amazon mette a disposizione come esempi e sui cui potete provare il servizio. In questo articolo andiamo a provare dei dati simili a questi esempi in quanto per problemi di privacy non posso usare i dati del mio cliente anche se sarebbero stati più interessanti. Vi indico qui di seguito la schermata iniziale del servizio e le opzioni a disposizione:

Come potete vedere dalla schermata iniziale di Amazon Athena i passi necessari per utilizzare il servizio sono racchiusi in tre operazioni molto semplici:

Select a data set: Dobbiamo identificare le sorgenti dei dati su cui Athena dovrà eseguire le elaborazioni, quindi dare anche le opportune regole di accesso ai bucket interessati, se utilizzate gli esempi di Amazon per provare il servizio non avete questa necessità.

Create a Table: Creare una tabella usando il comando SQL CREATE su cui specificare i campi e la sorgente dei dati. La sorgente può essere un singolo file ma anche una cartella con migliaia di file, sarà il sistema che si preoccuperà di unire il tutto.

Query data: Possiamo eseguire da subito le query di interrogazione, sia usando il client web messo a disposizione da Amazon AWS e/o un driver JDBC che potrà essere connesso a diversi client GUI o essere utilizzato direttamente da molti programmi applicativi.

Origine dei dati

Come esempio prendiamo alcuni file di log generati dal servizio di Elastic Load Balancer, nei quali troviamo tutto il traffico internet generato verso i nostri server web. Questi log si trovano su un cartella di Amazon S3 e su un bucket dedicato alla raccolta dei log:

Su questo stesso bucket assicuratevi che nella sezione (bucket policy) ci siano indicate le autorizzazioni necessari per consentire l’accesso ai dati tramite Athena. In caso di dubbi vi rimando alla pagina ufficiale => Setting User and Amazon S3 Bucket Permissions.

Creazione di un tabella

Adesso che abbiamo identificato i dati da leggere dobbiamo creare una tabella SQL che si connessa a questa origine di dati e nella quale bisogna specificare la struttura dei campi che sono presenti nel file di testo, oltre ad identificare il tipo di formato. Ci sono due modi per farlo, il primo è utilizzare un wizard grafico presente nella management console e il secondo è usare un comando SQL di CREATE TABLE. Vi allego gli esempi qui di seguito:

Durante la fase di creazione tabella potete anche creare un database o semplicemente selezionarne uno esistente. Come potete notare dalla schermata l’origine dei dati ha una opzione per criptare i dati, in questo modo tutti i file dei risultati saranno memorizzati e criptati con una chiave appartenente al vostro account su Amazon AWS. Una volta che inserite questi dati passerete alle schermate successive per la gestione dei campi. Come detto in precedenza potete fare tutto questo anche con un comando SQL:

CREATE EXTERNAL TABLE IF NOT EXISTS elasticloadbalancing ( 
    request_timestamp string, 
    elb_name string, 
    request_ip string, 
    request_port int, 
    backend_ip string, 
    backend_port int, 
    request_processing_time double, 
    backend_processing_time double, 
    client_response_time double, 
    elb_response_code string, 
    backend_response_code string, 
    received_bytes bigint, 
    sent_bytes bigint, 
    request_verb string, 
    url string, 
    protocol string, 
    user_agent string, 
    ssl_cipher string, 
    ssl_protocol string 
)

ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'

WITH SERDEPROPERTIES ( 
    'serialization.format' = '1',
    'input.regex' = '([^ ]*) ([^ ]*) ([^ ]*):([0-9]*) ([^ ]*)[:\-]([0-9]*) ([-.0-9]*) ([-.0-9]*) ([-.0-9]*) (|[-0-9]*) (-|[-0-9]*) ([-0-9]*) ([-0-9]*) \\\"([^ ]*) ([^ ]*) (- |[^ ]*)\\\" (\"[^\"]*\") ([A-Z0-9-]+) ([A-Za-z0-9.-]*)$' 
)

LOCATION 's3://bucket_di_prova.log.eu/elasticloadbalancing/';

Per avere delle informazioni più dettagliate sulle opzioni permesse nel comando, come ad esempio i formati di file supportati vi consiglio la documentazione di Athena.

Esecuzione di una query

Una volta creata la tabella potete subito eseguire dei comandi SQL per le interrogazioni, la via più semplice è sempre quella della management console, altrimenti potete usare dei driver JDBC messi a disposizione gratuita da Amazon. Nel nostro esempio la prima query che mi viene in mente potrebbe essere quella di identificare tutte le connessioni che hanno avuto un tempo di risposta da parte dei server maggiore dei 0.5 secondi.

SELECT * FROM elasticloadbalancing 
 WHERE backend_processing_time > 0.5

Le query sono molto veloci se pensiamo a tutto il lavoro di trasformazione che c’è dietro e il risultato della query precedente è il seguente:

Non è difficile pensare che utilizzando i comandi SQL con GROUP BY o con delle WHERE più complesse si possono ottenere dei risultati molto più interessanti. In questo momento sto lavorando su dei Log di Apache e vi assicuro che mi è tornata la voglia di analizzarli, usando i comandi SQL è tutto molto più semplice e di immediata lettura.

Guida completa su AWS

Questo articolo appartiene ad una serie di pubblicazioni che costituiscono una guida completa dedicata agli Amazon Web Services. Molti servizi che trattiamo in questo blog vengono anche spiegati con dei video che trovate nel nostro canale youtube. Se volete seguire questo percorso didattico iscrivetevi alla community Cloud AWS.

3 Commenti

  1. Alla fine del tuo articolo riporti il fatto che stai lavorando sui log di Apache, che opzioni usi per fare questo? Perchè io conosco solo il modo di condividere i log su CloudWatch tramite il software di Amazon da installare sulle istanze EC2 il quale non prevede un output su Amazon S3. Forse esiste qualche opzione che ancora non conosco?

  2. Ciao Eder, io uso la stessa tecnica da te indicata, uso l'agente su EC2 che crea uno stream su Amazon CloudWatch. L'unica cosa che ho fatto è creare una funzione Lambda che ogni inizio mese esegue un lavoro di export tra CloudWatch e S3 usando la funzione nativa rilasciata sotto AWS.

    Amazon CloudWatch S3Export

  3. Servizio eccezionale, anche se le performance riscontrate non sono quelle che mi aspettavo dopo aver letto la scheda informativa del prodotto. Una cosa che non mi è chiara è il limite di quando usare un servizio tipo GLUE e quanto usare Athena.

condividi