​
Introduction

Honeydew enables deep integration in Snowflake with a snowflake native application.

It can be installed from the Snowflake Marketplace Listing.

When the native app is installed, Honeydew API can be accessed directly from a Snowflake connection or the web interface, to do things such as:

  • Query data based on a semantic query
  • Generate Snowflake SQL query from a semantic query
  • Consume any metadata such as field names and descriptions
  • Update the semantic layer definitions

​
Installation

  1. Honeydew native application requires a Honeydew account. If you don’t have one yet, schedule a 20-min onboarding here.

  2. Note the name you have given to the application (i.e. SEMANTIC_LAYER_ENTERPRISE_EDITION) and replace SEMANTIC_LAYER_ENTERPRISE_EDITION in all examples below with the actual application name. You can find the names of the installed applications by running the following command:

SHOW APPLICATIONS;
  1. Set the Honeydew API Credentials for the Honeydew application, replacing myusername@mydomain.com and mypassword with your Honeydew username and password:
CALL SEMANTIC_LAYER_ENTERPRISE_EDITION.API.SET_API_CREDENTIALS('myusername@mydomain.com', 'mypassword');
  1. Create a Snowflake integration to the Honeydew API:
CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION HONEYDEW_API_ACCESS_INTEGRATION
  ALLOWED_NETWORK_RULES = (
    SEMANTIC_LAYER_ENTERPRISE_EDITION.HONEYDEW_EXTERNAL_ACCESS.API_NETWORK_RULE,
    SEMANTIC_LAYER_ENTERPRISE_EDITION.HONEYDEW_EXTERNAL_ACCESS.AUTH_NETWORK_RULE)
  ALLOWED_AUTHENTICATION_SECRETS = (
    SEMANTIC_LAYER_ENTERPRISE_EDITION.HONEYDEW_EXTERNAL_ACCESS.HONEYDEW_API_CLIENT_ID,
    SEMANTIC_LAYER_ENTERPRISE_EDITION.HONEYDEW_EXTERNAL_ACCESS.HONEYDEW_API_CLIENT_SECRET,
    SEMANTIC_LAYER_ENTERPRISE_EDITION.HONEYDEW_EXTERNAL_ACCESS.HONEYDEW_API_USERNAME_PASSWORD)
  ENABLED = TRUE;
  1. Grant the Honeydew API integration to the Honeydew application:
GRANT USAGE ON INTEGRATION HONEYDEW_API_ACCESS_INTEGRATION TO APPLICATION SEMANTIC_LAYER_ENTERPRISE_EDITION;
  1. Enable the Honeydew API integration for all Honeydew code functions and procedures:
CALL SEMANTIC_LAYER_ENTERPRISE_EDITION.API.ENABLE_EXTERNAL_ACCESS('HONEYDEW_API_ACCESS_INTEGRATION');
  1. Optionally, grant native app access to any additional Snowflake roles:
GRANT APPLICATION ROLE SEMANTIC_LAYER_ENTERPRISE_EDITION.HONEYDEW_APP_PUBLIC TO ROLE MY_ROLE;

Only the ACCOUNTADMIN role has the CREATE INTEGRATION privilege by default. The privilege can be granted to additional roles as needed.

​
Upgrade

  1. Upgrade the Honeydew application:
ALTER APPLICATION SEMANTIC_LAYER_ENTERPRISE_EDITION UPGRADE;
  1. Re-enable the Honeydew API integration for all Honeydew code functions and procedures (some functions or procedures may have been added as part of the upgrade):
CALL SEMANTIC_LAYER_ENTERPRISE_EDITION.API.ENABLE_EXTERNAL_ACCESS('HONEYDEW_API_ACCESS_INTEGRATION');
  1. Re-grant the Honeydew native application with all the originally provided access.

​
Usage Examples

Note that most calls require to choose the workspace and working branch.

The convention here is to set those as variables, i.e.

SET WORKSPACE='tpch';
SET BRANCH='prod';

​
Workspace and Branches

List all workspaces and branches:

select * from table(SEMANTIC_LAYER_ENTERPRISE_EDITION.API.SHOW_WORKSPACES());

Create a new branch named branch for a given workspace

select SEMANTIC_LAYER_ENTERPRISE_EDITION.API.CREATE_WORKSPACE_BRANCH($WORKSPACE, 'branch');

​
Schema

​
Parameters

List all global parameters in the given workspace and branch:

select * from table(SEMANTIC_LAYER_ENTERPRISE_EDITION.API.SHOW_GLOBAL_PARAMETERS($WORKSPACE, $BRANCH));

​
Entities

List all entities in the given workspace and branch:

select * from table(SEMANTIC_LAYER_ENTERPRISE_EDITION.API.SHOW_ENTITIES($WORKSPACE, $BRANCH));

List all entity relations in the given workspace and branch:

select * from table(SEMANTIC_LAYER_ENTERPRISE_EDITION.API.SHOW_RELATIONS($WORKSPACE, $BRANCH));

​
Fields Metadata

List all fields in the given workspace and branch:

select * from table(SEMANTIC_LAYER_ENTERPRISE_EDITION.API.SHOW_FIELDS($WORKSPACE, $BRANCH));

Fields include metrics, attributes and filters

​
Fields Add/Update/Delete

Change attribute field of entity entity

select SEMANTIC_LAYER_ENTERPRISE_EDITION.API.CREATE_ATTRIBUTE($WORKSPACE, $BRANCH, 'entity', 'field', 'expression');
select SEMANTIC_LAYER_ENTERPRISE_EDITION.API.ALTER_ATTRIBUTE($WORKSPACE, $BRANCH, 'entity', 'field', 'expression');
select SEMANTIC_LAYER_ENTERPRISE_EDITION.API.DROP_ATTRIBUTE($WORKSPACE, $BRANCH, 'entity', 'field');

Change metric field of entity entity

select SEMANTIC_LAYER_ENTERPRISE_EDITION.API.CREATE_METRIC($WORKSPACE, $BRANCH, 'entity', 'field', 'expression')
select SEMANTIC_LAYER_ENTERPRISE_EDITION.API.ALTER_METRIC($WORKSPACE, $BRANCH, 'entity', 'field', 'expression')
select SEMANTIC_LAYER_ENTERPRISE_EDITION.API.DROP_METRIC($WORKSPACE, $BRANCH, 'entity', 'field')

Change filter field of entity entity

select SEMANTIC_LAYER_ENTERPRISE_EDITION.API.CREATE_FILTER($WORKSPACE, $BRANCH, 'entity', 'field', 'expression')
select SEMANTIC_LAYER_ENTERPRISE_EDITION.API.ALTER_FILTER($WORKSPACE, $BRANCH, 'entity', 'field', 'expression')
select SEMANTIC_LAYER_ENTERPRISE_EDITION.API.DROP_FILTER($WORKSPACE, $BRANCH, 'entity', 'field')

​
Domains

List all domains in the given workspace and branch:

select * from table(SEMANTIC_LAYER_ENTERPRISE_EDITION.API.SHOW_DOMAINS($WORKSPACE, $BRANCH));

​
Queries

​
Get data from a semantic query

The following stored procedure allows to run a semantic query and get the resulting data.

It also possible to only generate the SQL (see next section).

You might need to grant access for the native application to the relevant data, for example:

GRANT USAGE ON DATABASE MY_DATABASE TO APPLICATION SEMANTIC_LAYER_ENTERPRISE_EDITION;
GRANT USAGE ON SCHEMA MY_DATABASE.MY_SCHEMA TO APPLICATION SEMANTIC_LAYER_ENTERPRISE_EDITION;
GRANT SELECT ON ALL TABLES IN SCHEMA MY_DATABASE.MY_SCHEMA TO APPLICATION SEMANTIC_LAYER_ENTERPRISE_EDITION;
GRANT SELECT ON ALL VIEWS IN SCHEMA MY_DATABASE.MY_SCHEMA TO APPLICATION SEMANTIC_LAYER_ENTERPRISE_EDITION;

call SEMANTIC_LAYER_ENTERPRISE_EDITION.API.SELECT_FROM_FIELDS(
        -- workspace / branch
        $WORKSPACE, $BRANCH,
        -- domain (can be NULL)
        'domain',
        -- dimensions - can be ad-hoc expressions
        ['entity.attr', ...],
        -- metrics - can be named or ad-hoc expressions
        ['entity.metric', 'SUM(entity.measure)', ...],
        -- filters - can be named or ad-hoc expressions
        ['entity.filter', 'entity.attr > 0', 'entity.name like ''%cheese%''', ...]
    );

Attributes and metrics may either refer to named fields in the semantic layer, or to new ad-hoc calculations based on them.

Metric ad-hoc expressions can do anything a metric can do.

In particular may use qualifiers such as FILTER (WHERE ...) and GROUP BY (...) to create ad-hoc filtered and/or partial metrics.

All the filters apply.

To allow better performance optimizations, it is recommended to pass multiple filters that will all apply rather than a single one with multiple conditions and an AND between them.

When passing filters to the native app, may use both attributes and metrics for filtering.

Attributes filter data similar to how WHERE behaves in SQL (only rows that match the expression are returned)

When a metric is used in a filter (entity.count > 0) it will be grouped by attributes before filtering, similar to how HAVING behaves in SQL. Only rows that the metric aggregation matches are returned.

​
Generate SQL for a query

The following function generates SQL for an ad-hoc semantic query

select SEMANTIC_LAYER_ENTERPRISE_EDITION.API.GET_SQL_FOR_FIELDS(
        -- workspace / branch
        $WORKSPACE, $BRANCH,
        -- domain (can be NULL)
        'domain',
        -- dimensions - can be ad-hoc expressions
        ['entity.attr', ...],
        -- metrics - can be named or ad-hoc expressions
        ['entity.metric', 'SUM(entity.measure)', ...],
        -- filters - can be named or ad-hoc expressions
        ['entity.filter', 'entity.attr > 0', 'entity.name like ''%cheese%''', ...]
    );

​
BI SQL Interface wrapper

It is possible to use the native application as a wrapper to the live SQL interface:

Get data from ad-hoc SQL:

CALL SEMANTIC_LAYER_ENTERPRISE_EDITION.API.SELECT_FROM_QUERY($WORKSPACE, $BRANCH, 'select ... from world');

Get compiled SQL query from ad-hoc SQL:

SELECT SEMANTIC_LAYER_ENTERPRISE_EDITION.API.GET_SQL_FOR_QUERY($WORKSPACE, $BRANCH, 'select ... from world');

​
Dynamic Datasets

​
Metadata

List all dynamic datasets in the given workspace and branch:

select * from table(SEMANTIC_LAYER_ENTERPRISE_EDITION.API.SHOW_DYNAMIC_DATASETS($WORKSPACE, $BRANCH));

​
Add/Update/Delete

Add or update a dynamic dataset dataset in given workspace and branch, and for a specific domain.

call SEMANTIC_LAYER_ENTERPRISE_EDITION.API.ALTER_DYNAMIC_DATASET(
        -- workspace / branch
        $WORKSPACE, $BRANCH,
        -- dataset name
        'dataset',
        -- domain (can be NULL for a global-level dataset)
        'domain',
        -- dimensions - can be ad-hoc expressions
        ['entity.attr', ...],
        -- metrics - can be named or ad-hoc expressions
        ['entity.metric', 'SUM(entity.measure)', ...],
        -- filters - can be named or ad-hoc expressions
        ['entity.filter', 'entity.attr > 0', ...]
    );

Delete a dynamic dataset dataset from a given workspace and branch

select SEMANTIC_LAYER_ENTERPRISE_EDITION.API.DROP_DYNAMIC_DATASET($WORKSPACE, $BRANCH, 'dataset');

​
Get Data or SQL

Get the data for a dynamic dataset dataset:

call SEMANTIC_LAYER_ENTERPRISE_EDITION.API.SELECT_FROM_DYNAMIC_DATASET($WORKSPACE, $BRANCH, 'dataset');

Get the SQL query for a dynamic dataset dataset:

select SEMANTIC_LAYER_ENTERPRISE_EDITION.API.GET_SQL_FOR_DYNAMIC_DATASET($WORKSPACE, $BRANCH, 'dataset');

​
Deployment

Deploy the dynamic dataset dataset according to its deployment settings:

select SEMANTIC_LAYER_ENTERPRISE_EDITION.API.DEPLOY_DYNAMIC_DATASET($WORKSPACE, $BRANCH, 'dataset');

Was this page helpful?

dbtSecurity