Querying Amazon EMR logs - Amazon Athena

Querying Amazon EMR logs

Amazon EMR and big data applications that run on Amazon EMR produce log files. Logs files are written to the master node, and you can also configure Amazon EMR to archive log files to Amazon S3 automatically. You can use Amazon Athena to query these logs to identify events and trends for applications and clusters. For more information about the types of log files in Amazon EMR and saving them to Amazon S3, see View log files in the Amazon EMR Management Guide.

Creating and querying a basic table based on Amazon EMR log files

The following example creates a basic table, myemrlogs, based on log files saved to s3://aws-logs-123456789012-us-west-2/elasticmapreduce/j-2ABCDE34F5GH6/elasticmapreduce/. The Amazon S3 location used in the examples below reflects the pattern of the default log location for an EMR cluster created by Amazon Web Services account 123456789012 in Region us-west-2. If you use a custom location, the pattern is s3://PathToEMRLogs/ClusterID.

For information about creating a partitioned table to potentially improve query performance and reduce data transfer, see Creating and querying a partitioned table based on Amazon EMR logs.

CREATE EXTERNAL TABLE `myemrlogs`( `data` string COMMENT 'from deserializer') ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION 's3://aws-logs-123456789012-us-west-2/elasticmapreduce/j-2ABCDE34F5GH6'

The following example queries can be run on the myemrlogs table created by the previous example.

Example – Query step logs for occurrences of ERROR, WARN, INFO, EXCEPTION, FATAL, or DEBUG

SELECT data, "$PATH" FROM "default"."myemrlogs" WHERE regexp_like("$PATH",'s-86URH188Z6B1') AND regexp_like(data, 'ERROR|WARN|INFO|EXCEPTION|FATAL|DEBUG') limit 100;

Example – Query a specific instance log, i-00b3c0a839ece0a9c, for ERROR, WARN, INFO, EXCEPTION, FATAL, or DEBUG

SELECT "data", "$PATH" AS filepath FROM "default"."myemrlogs" WHERE regexp_like("$PATH",'i-00b3c0a839ece0a9c') AND regexp_like("$PATH",'state') AND regexp_like(data, 'ERROR|WARN|INFO|EXCEPTION|FATAL|DEBUG') limit 100;

Example – Query presto application logs for ERROR, WARN, INFO, EXCEPTION, FATAL, or DEBUG

SELECT "data", "$PATH" AS filepath FROM "default"."myemrlogs" WHERE regexp_like("$PATH",'presto') AND regexp_like(data, 'ERROR|WARN|INFO|EXCEPTION|FATAL|DEBUG') limit 100;

Example – Query Namenode application logs for ERROR, WARN, INFO, EXCEPTION, FATAL, or DEBUG

SELECT "data", "$PATH" AS filepath FROM "default"."myemrlogs" WHERE regexp_like("$PATH",'namenode') AND regexp_like(data, 'ERROR|WARN|INFO|EXCEPTION|FATAL|DEBUG') limit 100;

Example – Query all logs by date and hour for ERROR, WARN, INFO, EXCEPTION, FATAL, or DEBUG

SELECT distinct("$PATH") AS filepath FROM "default"."myemrlogs" WHERE regexp_like("$PATH",'2019-07-23-10') AND regexp_like(data, 'ERROR|WARN|INFO|EXCEPTION|FATAL|DEBUG') limit 100;

Creating and querying a partitioned table based on Amazon EMR logs

These examples use the same log location to create an Athena table, but the table is partitioned, and a partition is then created for each log location. For more information, see Partitioning data in Athena.

The following query creates the partitioned table named mypartitionedemrlogs:

CREATE EXTERNAL TABLE `mypartitionedemrlogs`( `data` string COMMENT 'from deserializer') partitioned by (logtype string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION 's3://aws-logs-123456789012-us-west-2/elasticmapreduce/j-2ABCDE34F5GH6'

The following query statements then create table partitions based on sub-directories for different log types that Amazon EMR creates in Amazon S3:

ALTER TABLE mypartitionedemrlogs ADD PARTITION (logtype='containers') LOCATION 's3://aws-logs-123456789012-us-west-2/elasticmapreduce/j-2ABCDE34F5GH6/containers/'
ALTER TABLE mypartitionedemrlogs ADD PARTITION (logtype='hadoop-mapreduce') LOCATION 's3://aws-logs-123456789012-us-west-2/elasticmapreduce/j-2ABCDE34F5GH6/hadoop-mapreduce/'
ALTER TABLE mypartitionedemrlogs ADD PARTITION (logtype='hadoop-state-pusher') LOCATION 's3://aws-logs-123456789012-us-west-2/elasticmapreduce/j-2ABCDE34F5GH6/hadoop-state-pusher/'
ALTER TABLE mypartitionedemrlogs ADD PARTITION (logtype='node') LOCATION 's3://aws-logs-123456789012-us-west-2/elasticmapreduce/j-2ABCDE34F5GH6/node/'
ALTER TABLE mypartitionedemrlogs ADD PARTITION (logtype='steps') LOCATION 's3://aws-logs-123456789012-us-west-2/elasticmapreduce/j-2ABCDE34F5GH6/steps/'

After you create the partitions, you can run a SHOW PARTITIONS query on the table to confirm:

SHOW PARTITIONS mypartitionedemrlogs;

The following examples demonstrate queries for specific log entries use the table and partitions created by the examples above.

Example – Querying application application_1561661818238_0002 logs in the containers partition for ERROR or WARN

SELECT data, "$PATH" FROM "default"."mypartitionedemrlogs" WHERE logtype='containers' AND regexp_like("$PATH",'application_1561661818238_0002') AND regexp_like(data, 'ERROR|WARN') limit 100;

Example – Querying the hadoop-Mapreduce partition for job job_1561661818238_0004 and failed reduces

SELECT data, "$PATH" FROM "default"."mypartitionedemrlogs" WHERE logtype='hadoop-mapreduce' AND regexp_like(data,'job_1561661818238_0004|Failed Reduces') limit 100;

Example – Querying Hive logs in the node partition for query ID 056e0609-33e1-4611-956c-7a31b42d2663

SELECT data, "$PATH" FROM "default"."mypartitionedemrlogs" WHERE logtype='node' AND regexp_like("$PATH",'hive') AND regexp_like(data,'056e0609-33e1-4611-956c-7a31b42d2663') limit 100;

Example – Querying resourcemanager logs in the node partition for application 1567660019320_0001_01_000001

SELECT data, "$PATH" FROM "default"."mypartitionedemrlogs" WHERE logtype='node' AND regexp_like(data,'resourcemanager') AND regexp_like(data,'1567660019320_0001_01_000001') limit 100