rmrfslashbin/README.md

## README.md

      
    Raw
  

              README.md
            
          
    Helpful Trino queries

... to fetch data from my aws-cf-rtl and Trino/Superset projects.
Docker

I run Trino in a local Docker container. A Trino query can be invoked from the CLI by passing the query into the --execute param.
docker exec -it trino-us-east-1 trino --output-format ALIGNED --execute "${QUERY}"

Hit count per hostname

SELECT host, COUNT(host) AS hits FROM hive.cfrtl.rtl GROUP BY host ORDER BY hits DESC;
Total hits
SELECT COUNT(*) FROM hive.cfrtl.rtl;
Cast unixtime to localtime and time
SELECT FROM_UNIXTIME(CAST (timestamp AS DOUBLE), '-05:00'), FROM_UNIXTIME(CAST (timestamp AS DOUBLE)) FROM hive.cfrtl.rtl LIMIT 1;
Most recent entry
SELECT FROM_UNIXTIME(CAST (timestamp AS DOUBLE), '-05:00') FROM hive.cfrtl.rtl order by timestamp desc LIMIT 1;
Get hits by hour
SELECT date_format(FROM_UNIXTIME(CAST (timestamp AS DOUBLE)), '%H') AS hour, COUNT(timestamp) AS hits FROM hive.cfrtl.rtl WHERE year='2022' AND month='1' AND day='20' GROUP BY DATE_FORMAT(FROM_UNIXTIME(CAST (timestamp AS DOUBLE)), '%H') ORDER BY hour;