Introduction

Pour la mise en place de mon podcast Livres à vif, j'ai choisi de tout héberger moi-même et de maîtriser complètement la chaîne.

Les fichiers audio sont hébergés dans Amazon S3. Le serveur web est aussi sur s3 pour la partie hébergement et j'ai mis en place Amazon Cloudfront pour bénéficier du HTTPS. Cf l'article Héberger son podcast chez Amazon Web Services

Analyse des statistiques

J'utilise amazon AWS Athena pour l'analyse des requêtes.

Création de la base de données

Il faut d'abord créer la base de donnée

create database audiostatdb

Cloudfront

Cloudfront sert de cache pour les requêtes mais je l'utilise uniquement car c'est le seul moyen d'activer HTTPS.

Création de la table contenant les logs cloudfront

CREATE EXTERNAL TABLE IF NOT EXISTS audiostatdb.cloudfront_logs (
  `date` DATE,
  time STRING,
  location STRING,
  bytes BIGINT,
  request_ip STRING,
  method STRING,
  host STRING,
  uri STRING,
  status INT,
  referrer STRING,
  user_agent STRING,
  query_string STRING,
  cookie STRING,
  result_type STRING,
  request_id STRING,
  host_header STRING,
  request_protocol STRING,
  request_bytes BIGINT,
  time_taken FLOAT,
  xforwarded_for STRING,
  ssl_protocol STRING,
  ssl_cipher STRING,
  response_result_type STRING,
  http_version STRING,
  fle_status STRING,
  fle_encrypted_fields INT,
  c_port INT,
  time_to_first_byte FLOAT,
  x_edge_detailed_result_type STRING,
  sc_content_type STRING,
  sc_content_len BIGINT,
  sc_range_start BIGINT,
  sc_range_end BIGINT
)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t'
LOCATION 's3://log-livresavif/cloudfront/'
TBLPROPERTIES ( 'skip.header.line.count'='2' )			

		

Pour voir les requêtes

SELECT DISTINCT * 
FROM cloudfront_logs where "date" 
BETWEEN DATE '2020-07-30' AND DATE '2020-08-10';
			

Pour avoir les logs ordonné par date sur le fuseau horaire France. Il faut d'abord concaténer les champs date et time pour faire un objet de type datetime. Puis on ajoute le mot clé AT TIME ZONE pour convertir sur le fuseau horaire France.

SELECT 	
parse_datetime( concat( concat( format_datetime(date,
         'yyyy-MM-dd'), '-' ), time ),'yyyy-MM-dd-HH:mm:ss') 
AT TIME ZONE 'Europe/Paris' AS thedate,	
request_ip,	method,	uri, status, referrer, user_agent, query_string,host_header	
FROM cloudfront_logs 
where "date" BETWEEN DATE '2020-08-10' AND DATE '2020-08-10'  order by thedate DESC
 

Analyse des logs S3

Création de la table

CREATE EXTERNAL TABLE `audio_logs`(
  `bucketowner` string COMMENT '', 
  `bucket` string COMMENT '', 
  `requestdatetime` string COMMENT '', 
  `remoteip` string COMMENT '', 
  `requester` string COMMENT '', 
  `requestid` string COMMENT '', 
  `operation` string COMMENT '', 
  `key` string COMMENT '', 
  `requesturi_operation` string COMMENT '', 
  `requesturi_key` string COMMENT '', 
  `requesturi_httpprotoversion` string COMMENT '', 
  `httpstatus` string COMMENT '', 
  `errorcode` string COMMENT '', 
  `bytessent` bigint COMMENT '', 
  `objectsize` bigint COMMENT '', 
  `totaltime` string COMMENT '', 
  `turnaroundtime` string COMMENT '', 
  `referrer` string COMMENT '', 
  `useragent` string COMMENT '', 
  `versionid` string COMMENT '', 
  `hostid` string COMMENT '', 
  `sigv` string COMMENT '', 
  `ciphersuite` string COMMENT '', 
  `authtype` string COMMENT '', 
  `endpoint` string COMMENT '', 
  `tlsversion` string COMMENT '')
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.serde2.RegexSerDe' 
WITH SERDEPROPERTIES ( 
  'input.regex'='([^ ]*) ([^ ]*) \\[(.*?)\\] ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) 
\\\"([^ ]*) ([^ ]*) (- |[^ ]*)\\\" (-|[0-9]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) 
([^ ]*) (\"[^\"]*\") ([^ ]*)(?: ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*))?.*$') 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://log-livresavif/audio'
TBLPROPERTIES (
  'transient_lastDdlTime'='1594043117')
		

Analyse des téléchargements

On arrive ici à la partie compliquée qui correspond à la gestion des dates. En effet, parè défaut le format de date utilise des mois sous forme de lettre comme Jul pour July et le fuseau horaire n'est pas celui de Paris

30/Jul/2020:17:51:29 +0000

Cela pose problème si on veut trier les requêtes, il faut donc convertir le champ requestdatetime dans un format style 2020-08-01:13:34:45

SELECT parse_datetime(RequestDateTime,
'dd/MMM/yyyy:HH:mm:ss Z') AT TIME ZONE 'Europe/Paris' AS thedate, 
remoteip,requester,key,requesturi_key,requesturi_operation,httpstatus,
bytessent,objectsize,totaltime, referrer,useragent
FROM "audiostatdb"."audio_logs"
WHERE key LIKE 'LAV%.mp3'
AND parse_datetime(RequestDateTime,'dd/MMM/yyyy:HH:mm:ss Z')
	BETWEEN parse_datetime('2020-08-01:00:00:00','yyyy-MM-dd:HH:mm:ss')
	AND parse_datetime('2020-09-01:00:00:00','yyyy-MM-dd:HH:mm:ss')
ORDER BY  thedate DESC;