Connection Setup

Create a connection and set up JDBC parameters to configure a connection.

Use catalog to choose workspace and branch.

Each workspace branch is a separate connection.

Different branch connections can be used interchangeably with the same Honeydew explore.

Metadata Sync

Looker relies on LookML views to query data. Honeydew can create LookML and synchronize all associated metadata.

Honeydew will synchronizes the following metadata to LookML:

  • Fields - every attribute in the field is defined as a Looker dimension, and every metric as a Looker measure.
  • Field data types.
  • Field display names (to label LookML field property). When no display name is set, Honeydew creates an automatic user friendly name.
  • Field folders (to view_label LookML field property). When no folder is set, Honeydew uses the entity name as folder names.
  • Field hidden property (to hidden LookML field property).
  • Field descriptions (to description LookML field property).

The recommended approach is use Honeydew to generate LookML. However, it is possible to use the connection directly and manually create a LookML view on top of it. Note that in that case the the continuous updates are manual as well.

Functionality

Honeydew supports Looker Explore over the shared semantic layer.

Groups and Pivots

Any Honeydew attribute can be used as a Looker dimension for groups and pivots.

Measures

Any Honeydew metric can be used as a measure (aggregation) in Looker.

If you want to use a metric as a dimension, define it as a calculated attribute in Honeydew first.

Filters

Any Honeydew attribute or metric can be used as a Looker filter.

Filters are automatically pushed down to the source in order to optimize performance.

Cross-entity queries

Any entity in Honeydew can be used in an Explore.

Joins are automatically constructed by Honeydew. They are based on the relationships between entities that are used in a particular user Explore query.

Custom dimensions and measures

Custom dimensions such as binning, custom groups, unique value list dimension and Looker functions in custom field definitions are automatically converted to Honeydew attributes.

Custom measures such as an ad-hoc average or sum of a dimension are automatically converted to Honeydew metrics.

If you want to reuse a custom measure or a custom dimension defined in Looker: save it in Honeydew to a named attribute or metric.

That way, the code to compute it moves to the shared semantic layer.

User attributes and parameters

User attributes and parameters can be passed to Honeydew parameters using the parameter SQL interface as part of an explore definition.

Explore configuration

Looker Explores can be based on domains in Honeydew.

Common Looker functionality maps to domains:

  • Per explore filters (access_filter, sql_always_where, sql_always_having in LookML) can be modeled as domain filters.
  • Per explore join paths (join in LookML) can be modeled with domain active roles when multiple join paths exist between explores.
  • Per explore join aliases (from in LookML) can be modeled with domain role aliases.

Domains

Honeydew domains can be mapped to Looker Explores.

Domains can be used to define centrally things such as:

  • Access filters (instead using LookML such as always_filter)
  • Customized join relations (instead of setting join-s in LookML)

To use a domain, use it as an explore source table, such as :

explore: supply_chain {
  view_name: world
  sql_table_name: domains.supply_chain ;;
}

User attributes and Parameters

Parameters can be passed to the Honeydew SQL interface via the FROM statement.

Looker user attributes and LookML parameters can be both used to pass down to an explore based on world or on a domain:

# pass $param1 and $param2 to Honeydew from different sources in Looker
explore: world_with_parameters{
  sql_table_name:
    (select *,
      {{_user_attributes['param1']}} as "$param1",
      {% parameter param2 %} as "$param2",
      from world.world);;
  view_name:  world

Limitations

  1. Filtered measures in an Explore over Honeydew metrics are not supported. Create a Honeydew filtered metric instead.
  2. Persistent Derived Tables (PDTs) are not supported. See performance acceleration in Honeydew for alternative performance approaches.
  3. Joins in an Explore between Views on Honeydew datasets are not supported. Instead, build a single Looker View with all the required entities, and Honeydew will figure out the needed joins.

Setting up a new Connection

Looker connects to Honeydew via JDBC connection, using the Trino protocol.

The Honeydew JDBC connection translates Looker Trino SQL to Snowflake SQL.

Looker connections are mapped to a Honeydew workspace and branch

It is recommended to use an API Key for setting up the connection.

  1. Go to Create and then click on Connection
  2. Provide a Name for the connection (e.g. “Honeydew”) and choose the desired Connection Scope
  3. In the Dialect field, choose Trino from the list of supported dialects
  4. Set Host to jdbc.honeydew.cloud

If your organization uses a custom hostname for the JDBC connection, you can locate it in the Honeydew UI, under the JDBC section in Settings.

  1. Set Port to 443
  2. Set Database to <workspace>__<branch> value, based on the Honeydew workspace and branch you would like to connect to. Omit the branch value if connecting to prod. For example, for workspace tpch, branch prod, the value would be: tpch. And for workspace tpch, branch dev, it would be tpch__dev.
  3. In Authentication, choose Database Account
  4. Set Username to the API key (or a Honeydew username), and Password to the API secret (or to the user’s password in Honeydew).
  5. In Optional Settings, expand Additional Settings, and then switch on the Verify SSL toggle, if not already turned on.
  6. Click Test to verify the connection settings
  7. Click Connect to connect to the newly created Honeydew connection

You can now use the newly built datasource to integrate Honeydew with Looker.

LookML

Use Honeydew to generate a LookML file.

The LookML for a Honeydew dataset will look similar to this:

## LookML code

view: customers_and_orders {

  # A specific dynamic dataset that applies its filtering
  # Can use world.world for the whole semantic world
  sql_table_name: dynamic_dataset.customers_and_orders ;;

  # String dimensions can look like
  dimension: customer__c_name {
    view_label: "customer"
    label: "c_name"
    type: string
    sql: ${TABLE}."customer.c_name" ;;
  }

  # Date dimensions can look like
  dimension_group: orders__o_orderdate {
    view_label: "orders"
    label: "o_orderdate"
    type: time
    sql: ${TABLE}."orders.o_orderdate" ;;
    timeframes: [date, week, month, month_name, month_num, quarter, quarter_of_year, year]
  }

  # Numeric metrics can look like
  measure: customers__count {
    view_label: "customers"
    label: "count"
    type: number
    sql: AGG(${TABLE}."customers.count") ;;
  }
}