Fase 2: creazione di una tabella

Ora che hai un database, puoi creare una tabella Athena. La tabella creata sarà basata sui dati di log di Amazon CloudFront di esempio nella posizione s3://athena-examples-myregion/cloudfront/plaintext/, dove myregion è la tua Regione AWS attuale.

I dati di log di esempio sono in formato TSV (Tab-Separated Values), il che significa che un carattere di tabulazione viene utilizzato come delimitatore per separare i campi. I dati vengono mostrati come nell'esempio seguente. Per la leggibilità, le tabulazioni nell'estratto sono state convertite in spazi e il campo finale è stato abbreviato.

2014-07-05 20:00:09 DFW3 4260 10.0.0.15 GET eabcd12345678.cloudfront.net /test-image-1.jpeg 200 - Mozilla/5.0[...]
2014-07-05 20:00:09 DFW3 4252 10.0.0.15 GET eabcd12345678.cloudfront.net /test-image-2.jpeg 200 - Mozilla/5.0[...]
2014-07-05 20:00:10 AMS1 4261 10.0.0.15 GET eabcd12345678.cloudfront.net /test-image-3.jpeg 200 - Mozilla/5.0[...]

Per consentire ad Athena di leggere questi dati, puoi eseguire un'istruzione CREATE EXTERNAL TABLE come la seguente. L'istruzione che crea la tabella definisce le colonne che mappano i dati, specifica la modalità di delimitazione dei dati e specifica la posizione Amazon S3 che contiene i dati di esempio. Tieni presente che, poiché Athena prevede di eseguire la scansione di tutti i file in una cartella, la LOCATION clausola specificare la posizione di una cartella Amazon S3, non un file specifico.

Non utilizzate ancora questo esempio in quanto presenta un'importante limitazione che verrà spiegata a breve.

CREATE EXTERNAL TABLE IF NOT EXISTS cloudfront_logs (
  `Date` DATE,
  Time STRING,
  Location STRING,
  Bytes INT,
  RequestIP STRING,
  Method STRING,
  Host STRING,
  Uri STRING,
  Status INT,
  Referrer STRING,
  ClientInfo STRING
  ) 
  ROW FORMAT DELIMITED
  FIELDS TERMINATED BY '\t'
  LINES TERMINATED BY '\n'
  LOCATION 's3://athena-examples-my-region/cloudfront/plaintext/';

L'esempio crea una tabella denominata cloudfront_logs e specifica un nome e un tipo di dati per ogni campo. Questi campi diventano le colonne nella tabella. Poiché date è una Parola riservata, viene ingannato con caratteri backtick (`). ROW FORMAT DELIMITED significa che Athena userà una libreria predefinita chiamata LazySimpleSerde per eseguire il lavoro effettivo di analisi dei dati. L'esempio specifica inoltre che i campi sono separati da tabulazioni (FIELDS TERMINATED BY '\t') e che ogni record nel file termina con un carattere di nuova riga (LINES TERMINATED BY '\n). Infine, la clausola LOCATION specifica il percorso in Amazon S3 in cui si trovano i dati effettivi da leggere.

Se si dispone di una scheda o di dati separati da virgole, puoi utilizzare una dichiarazione CREATE TABLE come l'esempio appena illustrato. Tuttavia, se una colonna del genere ClientInfo contiene informazioni annidate che utilizzano un delimitatore diverso, è necessario un approccio diverso.

Estrazione di dati dal campo ClientInfo

Tornando ai dati di esempio, ecco un esempio completo del campo finale ClientInfo:

Mozilla/5.0%20(Android;%20U;%20Windows%20NT%205.1;%20en-US;%20rv:1.9.0.9)%20Gecko/2009040821%20IE/3.0.9

Come puoi vedere, questo campo è multivalore. Se l'istruzione specifica le schede come delimitatori di campo, i componenti separati all'interno di questo particolare campo non possono essere suddivisi in colonne separate. Pertanto, è necessaria una nuova CREATE TABLE dichiarazione.

Per creare colonne dai valori all'interno del campo ClientInfo, è possibile utilizzare un'espressione regolare (regex) che contiene gruppi regex. I gruppi regex specificati diventano colonne di tabella separate. Per usare una regex nella tua istruzione CREATE TABLE, utilizzare la sintassi simile alla seguente. Questa sintassi indica ad Athena di utilizzare la libreria SerDe Regex e l'espressione regolare specificata.

ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
  WITH SERDEPROPERTIES ("input.regex" = "regular_expression")

Le espressioni regolari possono essere utili per la creazione di tabelle da dati CSV o TSV complessi, ma possono essere difficili da scrivere e gestire. Fortunatamente, ci sono altre librerie che è possibile utilizzare per formati come JSON, Parquet e ORC. Per ulteriori informazioni, consulta Scegliere una SerDe per i propri dati.

Ora è possibile creare la tabella nell'editor di query Athena. L'istruzione CREATE TABLE e la regex ti vengono forniti.