Analyzing UXI Data in S3 with Amazon Athena

Once your UXI sensor test result or issue data is in S3, you can use tools like Amazon Athena to analyze the data. Athena is a serverless application that uses the S3 data directly. You do not need to load your data into Athena, you simply describe the format of how it is represented in S3 and run SQL queries to analyze it.

You can refer to Amazon’s official documentation for getting started with Athena. Below are some examples of creating a database, setting up a table partitioned by date, and running a query to analyze the data.

Steps:
Create a Database

CREATE DATABASE my_uxi_data;

Create a Table

CREATE EXTERNAL TABLE issues (
	code string,
	severity string,
	status string,
	timestamp string,
	uid string,
	context struct < category: string,
	    customer_uid: string,
	    hierarchy_node_name: string,
	    hierarchy_type: string,
	    mac_address: string,
	    network_name: string,
	    network_uid: string,
	    sensor_name: string,
	    sensor_serial: string,
	    sensor_uid: string,
	    service_name: string,
	    service_uid: string >
) PARTITIONED BY (year string, month string, day string) ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe' LOCATION 's3://<your bucket name>/Aruba-UXI/issues.s3.<customer uid>/'

Add a partition

ALTER TABLE issues 
ADD PARTITION (year='2022', month='06', day='05') LOCATION 's3://<your bucket name>/Aruba-UXI/issues.s3.<customer uid>/year=2022/month=06/day=03'

Querying the data

SELECT * FROM "my_uxi_data"."issues" 
WHERE year='2022' AND month='06' AND day='05'
limit 10

Note: For querying test results data you can simply replace "issues" with "test_results"