Working with API Logs in Looker

After ingesting your logs into your warehouse, you can monitor and visualize the logs using a BI tool like Looker.

Building a View

To start, build a Looker View that unpacks the logs JSON. We recommend using a persistent derived table to unpack the JSON so the JSON-parsing query is not run every time the BI queries are run.

Here is an example modelbit_logs.view:

view: modelbit_logs {
  derived_table: {
    sql:
      select
        to_timestamp((j.value:batchStartTs/1000)::int) as batch_start,
        j.value:batchDurationMs::int as batch_duration_ms,
        j.value:deploymentName as deployment_name,
        j.value:deploymentVersion as deployment_version,
        strip_null_value(j.value:error) as error,
        j.value:resultCount::int as result_count
      from demo_schema.modelbit_logs_raw, lateral flatten(input => "DATA") as j
     ;;
    sql_trigger_value: select count(1) from modelbit_logs ;;
  }

  dimension: batch_start {
    type: date_time
    sql: ${TABLE}."BATCH_START" ;;
  }

  dimension: batch_duration_ms {
    type: number
    sql: ${TABLE}."BATCH_DURATION_MS" ;;
  }

  dimension: deployment_name {
    type: string
    sql: ${TABLE}."DEPLOYMENT_NAME" ;;
  }

  dimension: deployment_version {
    type: string
    sql: ${TABLE}."DEPLOYMENT_VERSION" ;;
  }

  dimension: error {
    type: string
    sql: ${TABLE}."ERROR" ;;
  }

  dimension: result_count {
    type: number
    sql: ${TABLE}."RESULT_COUNT" ;;
  }
}

This view uses a SQL trigger persistence strategy to re-materialize the view every time new logs are ingested. Other persistence strategies work equally well. Choose the one that offers the right cost/benefit tradeoff for your organization.

Adding your View to your Model

Choose a data model to add your view to. For most model monitoring use cases, a single line in an existing data model is fine. E.g. in your_model.model, add:

explore: modelbit_logs {}

You can add join routes to other views here if you are planning to join information in log lines to other tables in your database.

Exploring and Dashboarding

Once you push those changes to production, in Looker Explore, you will see "Modelbit Logs" on the left hand side. Click to build charts based on the fields in your logs!

If you want to save these charts to a dashboard, click the gear icon in the upper right and click "Save..." to save to a new or existing dashboard.