Reports Page

The Okera Portal provides a Reports page for users with access to view usage metrics for their datasets. The Reports page is driven from the Okera audit logs, which contain information on everything that happens in the Okera system (for more on the structure of the audit logs, see Auditing).

Okera Reports Page

Enabling the Reports page

See here for how to enable access to the reports page.

Note that even though a user has access to the Reports page, the audit views are set up to only show audit logs pertaining to the datasets a user has SELECT privileges on.

Filtering by database

By default, Reports will show aggregated metrics for all of the datasets on which the current user has read-access on. You can change which database or set of databases the reports show using the multi-select dropdown in the top right of the page labelled "Databases".

Changing the time range

The default time range on the Reports page is "Last 24 hours". This means that every report on the page will be showing data only for queries that occurred in the 24 hours prior to the page visit. Use the "Time range" dropdown to change the time range used to generate the reports to one of the following options:

  • Last 24 hours (default): reports will show data from the 24 hours prior to visiting the page.
  • Last 7 days: reports will show data for seven days prior to visiting the page.
  • Last 2 weeks: reports will show data for two weeks prior to visiting the page.
  • Last month: reports will show data for one month prior to visiting the page.

Drilldowns

You can drill down further on a report by clicking on any data point in the report. The drilldown displays a sample of up to 100 queries run for the selected data point. This allows users to explore the underlying data powering the reports.

Each report has a slightly different drilldown query, but they all follow a similar pattern of a base query restricted by time, followed by additional constraints. For example, the drilldown for "Most queried datasets" looks like this:

  SELECT *
  FROM   okera_system.reporting_audit_logs
  WHERE  ymd BETWEEN '2019-09-30'
        AND '2019-10-01'
        AND start_unix_time BETWEEN 1569868800000
        AND 1569955800000
            AND client_application LIKE 'hive%'

There are a few things to note about this query:

  • We are selecting from okera_system.reporting_audit_logs, this is a special system view that has permissions so users will only see data for datasets on which they have admin privileges.
  • We are restricting by ymd AND start_unix_time. This seems redundant, but it helps performance by first choosing the right partition, then restricting data further based on timestamps.
  • Finally, the query restricts to all client applications starting with hive. This is to capture all versions of "hive".

Okera Reports Drilldown Preview

Follow the "go to Workspace" link in the drilldown preview to open the query in Workspace. There, you can modify the query to find other insights. When there, click Run to run the query. You will see the audit log data for the specified time range. Note that results are restricted to the number of rows in the "Row limit" box. If you need to see more results, you can use the same query in an EMR cluster or whatever other client application you use to query data through Okera.

Understanding the reports

There are five reports displayed on the Reports page. They should mostly make sense without a lot of explanation, but each has a few details to note.

Queries over time

Okera Reports Queries over time

This report shows the number of queries that occurred during the selected time range as a trend with each point representing the sum of queries in a particular time bucket. The total time range is broken into buckets of a set size and the number of queries occurring during each bucket-sized span are summed to produce each point. For day-long time ranges, the buckets are 10 minutes, for week-long time ranges, the buckets are an hour and 40 minutes. Points' timestamps represent the end of the bucket. So, in a "Last 24 hours" report, if the value or a point is 120, that means 120 queries occurred in the 10 minutes prior to the time on the point.

"Queries over time" supports drilling down to see a list of some of the queries run in a particular time bucket. To drill down, pick a bucket and click on it. You will see a modal that displays a sample of queries run in this time frame. This modal is populated by a query similar to:

  SELECT *
  FROM   okera_system.reporting_audit_logs
  WHERE  ymd BETWEEN '2019-09-30'
        AND '2019-10-01'
          -- Timestamps below restrict data to timerange for reports ('Last 24 hours')
        AND start_unix_time BETWEEN 1569868200000
        AND 1569955200000
          -- Timestamps below restrict data to specific bucket that was clicked
            AND start_unix_time >= 1569919800000
            AND start_unix_time <= 1569920400000

In addition to the details mentioned in the Drilldowns section above, this query has four unix timestamps. The first two timestamps are part of the base query, restricting the data to the outer time range of the time chart ("Last 24 hours", "Last 7 days", etc.), and the next two timestamps restrict the range to the specific bucket that was clicked. All of them intentionally restrict against start_unix_time, so we are counting when events started, not when they complete.

Most frequently used client applications

Okera Reports Queries by client applications

This report shows the most popular client applications ranked by number of queries run in the given time range. The top ten client applications are represented as bars in a bar chart, and more applications will can be shown by clicking on the "View all..." link at the bottom of the panel. Mouseover on the bars in the chart will show the number of queries on the application. In the top ten bar chart, various versions of an application will be combined into one bar, but in the "All applications" modal, each version of an the application will be separated.

Okera Reports All client applications

Click on any bar to see a drilldown of some of the queries run on that particular application. This modal is populated by a query similar to:

  SELECT *
  FROM   okera_system.reporting_audit_logs
  WHERE  ymd BETWEEN '2019-09-17'
        AND '2019-10-01'
        AND start_unix_time BETWEEN 1568745600000
        AND (1569955800000
            AND client_application LIKE 'hive%')

As discussed in the Drilldowns section above, this query gets the audit logs in the current time range and filters by application name. It uses LIKE to pick up all versions of the application, which is stored as a string with the name first, then version.

Most queried datasets

Okera Reports Most queried datasets

This report is very similar to "Most frequently used client applications", showing the most queried datasets in the selected time range. The top ten datasets are represented as bars in the bar chart, and the rest can be viewed by clicking "All datasets". You may notice some entries include multiple datasets separated by commas. Those counts represent queries that included both of the datasets. Mouseover on the bars in the chart will show the number of queries on that dataset.

Click on any bar to see a drilldown of some of the queries run on that particular dataset. This modal is populated by a query similar to:

  SELECT *
  FROM   okera_system.reporting_audit_logs
  WHERE  ymd BETWEEN '2019-09-30'
        AND '2019-10-01'
        AND start_unix_time BETWEEN 1569868800000
        AND 1569955800000
            AND (ae_table = 'okera_sample.users'
            OR ae_view = 'okera_sample.users')

In addition to the details mentioned in the Drilldowns section above, this query restricts ae_table or ae_view to the specified dataset.

Most active users

Okera Reports Most active users

As the name implies, this report shows a list of users in the system, ranked by the number queries they ran in the given time frame. Click on any row to see a drilldown of some of the queries run by that particular user. This modal is populated by a query similar to:

  SELECT *
  FROM   okera_system.reporting_audit_logs
  WHERE  ymd BETWEEN '2019-09-30'
        AND '2019-10-01'
        AND start_unix_time BETWEEN 1569876600000
        AND 1569963600000
            AND user = 'sample_user'

Like the other drilldowns, this query restricts results based on time, then adds an additional constraint. In this case, exact user name.

Queries by duration of planner request

Okera Reports Queries by duration of planner request

This report shows how long query planner requests took to run. Queries are grouped into specific time ranges for planner request duration.

Click on any row to see a drilldown of some of the queries run for that duration range. This modal is populated by a query similar to:

  SELECT *
  FROM   okera_system.reporting_audit_logs
  WHERE  ymd BETWEEN '2019-09-30'
        AND '2019-10-01'
        AND start_unix_time BETWEEN 1569875400000
        AND 1569962400000
            AND end_unix_time - start_unix_time > 0
            AND end_unix_time - start_unix_time <= 100

Restricting time range

Reporting time ranges can be restricted via the environment variable OKERA_REPORTING_TIME_RANGE. This may be necessary in environments with huge data volumes. If reporting times out for longer time ranges, you may wish to restrict these longer times.

The format of this string should be a comma-delimited pair representing the inclusive boundaries to filter the existing reporting periods (currently 'Last 24 hours', '7 days', '2 weeks', and '1 month') supported in the web UI.

Supported inputs are all integer values of hours, days, weeks, and months. As an example, if you only want reporting options between five days and three weeks, you would set the environment variable OKERA_REPORTING_TIME_RANGE=5days,3weeks. This would result in the Reports page time range dropdown only showing '7 days' and '2 weeks' as those are the only options within that range supported by the UI.

Notes on implementation

Time values

The times shown in the reports are all in client-local time. Times stored on the server (and therefore part of drilldowns) are stored as UTC time in milliseconds and represent when each event happened on the server. Each event is counted at its start time, not its end time.

When is data available?

Report data is updated every five minutes. Queries run in the last five minutes are being logged, but may not be reflected in the reports. Every five minutes a watcher task processes the audit logs, adding them to the corresponding query-able table.

What is being counted?

Queries are not counted 1 to 1 in the audit logs; Okera logs more than one event per user query. For each query, Okera logs a PLAN event, plus some number of SCAN events depending on the scale of the data being scanned. For very large scans, the number of events logged can be very high. All of these events are shown in queries on the audit logs, but in reports most of them are rolled up into a small number of events. The reports reflect a low constant number of events per query. The exact number depends on the client and query type, but not on scale of data scanned. So, for similar queries from the same source, the numbers in the reports are consistent.