Supported DBMS products: Snowflake, Databricks SQL
Starting from 26.0.650.patch3, entity descriptions are no longer used as
COMMENTs on SEMANTIC VIEWs in Snowflake, as they caused syntax errors during
creation. If you need a COMMENT on the SEMANTIC VIEW, add it manually in your
creation clause.
Good to know
In Snowflake SEMANTIC_VIEW physical type will generate a SEMANTIC VIEW. The logic is given in SQL.
In Databricks SQL SEMANTIC_VIEW physical type will generate a METRICS VIEW. The logic is given in YAML. Based on view generation specifics, Databricks Metrics view does support attributes, entity description and entity database tags. See example below.
See also
-
https://docs.snowflake.com/en/sql-reference/sql/create-semantic-view
-
https://docs.databricks.com/aws/en/sql/language-manual/sql-ref-syntax-ddl-create-view
-
Database Tags (for Databricks SQL)
Snowflake example
Case: Defined entity publish.TAXI_TRIP_CONTENT with physical type SEMANTIC_VIEW.
Adding load with entity mappings:
-
pub.F_TRIP
-
pub.D_TAXI_ZONE_PICKUP
-
pub.D_TAXI_ZONE_DROPOFF
Adding View SQL like this:
TABLES (
trips AS pub.F_TRIP
UNIQUE (trip_wid)
WITH SYNONYMS ('rides')
COMMENT = 'All NYC metered taxi trips (yellow and green services)',
pickup_zones AS pub.D_TAXI_ZONE_PICKUP UNIQUE (taxi_zone_wid),
dropoff_zones AS pub.D_TAXI_ZONE_DROPOFF UNIQUE (taxi_zone_wid)
)
RELATIONSHIPS (
trips(taxi_zone_pickup_wid) REFERENCES pickup_zone,
trips(taxi_zone_dropoff_wid) REFERENCES dropoff_zone
)
FACTS (
trips.fare_amount AS fare_amount
WITH SYNONYMS = ('base fare')
COMMENT = 'Time-and-distance fare in dollars calculated by the meter',
trips.total_amount AS total_amount
WITH SYNONYMS = ('total charge', 'trip total')
COMMENT = 'Total amount paid by the passenger (including tips, tolls, MTA tax and other surcharges)',
trips.trip_distance AS trip_distance
COMMENT = 'Total distance traveled in miles'
)
DIMENSIONS (
trips.taxi_type AS taxi_type
WITH SYNONYMS = ('service type', 'taxi service')
COMMENT = 'Taxi type: yellow (citywide street-hail) or green (outer-borough street-hail)',
pickup_zones.pickup_taxi_zone_name as taxi_zone
COMMENT = 'Official NYC TLC taxi zone name for the pickup location',
dropoff_zones.dropoff_taxi_zone_name as taxi_zone
COMMENT = 'Official NYC TLC taxi zone name for the dropoff location',
pickup_zones.pickup_borough as borough,
dropoff_zones.dropoff_borough as borough,
)
METRICS (
trips.total_revenue AS SUM(total_amount)
WITH SYNONYMS = ('gross revenue'),
trips.fare_revenue AS SUM(fare_amount),
trips.total_distance AS SUM(trip_distance),
trips.average_trip_total AS AVG(total_amount)
WITH SYNONYMS = ('average revenue per trip')
)
COMMENT = 'Semantic view for taxi data revenue analysis'
Will generate a Snowflake SEMANTIC VIEW like this:
CREATE SEMANTIC VIEW publish.TAXI_TRIP_CONTENT
TABLES (
trips AS pub.F_TRIP
UNIQUE (trip_wid)
WITH SYNONYMS ('rides')
COMMENT = 'All NYC metered taxi trips (yellow and green services)',
pickup_zones AS pub.D_TAXI_ZONE_PICKUP UNIQUE (taxi_zone_wid),
dropoff_zones AS pub.D_TAXI_ZONE_DROPOFF UNIQUE (taxi_zone_wid)
)
RELATIONSHIPS (
trips(taxi_zone_pickup_wid) REFERENCES pickup_zone,
trips(taxi_zone_dropoff_wid) REFERENCES dropoff_zone
)
FACTS (
trips.fare_amount AS fare_amount
WITH SYNONYMS = ('base fare')
COMMENT = 'Time-and-distance fare in dollars calculated by the meter',
trips.total_amount AS total_amount
WITH SYNONYMS = ('total charge', 'trip total')
COMMENT = 'Total amount paid by the passenger (including tips, tolls, MTA tax and other surcharges)',
trips.trip_distance AS trip_distance
COMMENT = 'Total distance traveled in miles'
)
DIMENSIONS (
trips.taxi_type AS taxi_type
WITH SYNONYMS = ('service type', 'taxi service')
COMMENT = 'Taxi type: yellow (citywide street-hail) or green (outer-borough street-hail)',
pickup_zones.pickup_taxi_zone_name as taxi_zone
COMMENT = 'Official NYC TLC taxi zone name for the pickup location',
dropoff_zones.dropoff_taxi_zone_name as taxi_zone
COMMENT = 'Official NYC TLC taxi zone name for the dropoff location',
pickup_zones.pickup_borough as borough,
dropoff_zones.dropoff_borough as borough,
)
METRICS (
trips.total_revenue AS SUM(total_amount)
WITH SYNONYMS = ('gross revenue'),
trips.fare_revenue AS SUM(fare_amount),
trips.total_distance AS SUM(trip_distance),
trips.average_trip_total AS AVG(total_amount)
WITH SYNONYMS = ('average revenue per trip')
)
COMMENT = 'Semantic view for taxi data revenue analysis'
;
Databricks SQL example (no attributes/descriptions/tags)
Case: Defined entity publish.TAXI_TRIP_CONTENT with physical type SEMANTIC_VIEW.
Adding load with entity mappings:
-
catalog.pub.F_TRIP
-
catalog.pub.D_TAXI_ZONE_PICKUP
-
catalog.pub.D_TAXI_ZONE_DROPOFF
Adding View SQL like this:
version: 1.1
comment: "All NYC metered taxi trips (yellow and green services)"
source: catalog.pub.F_TRIP
joins:
- name: pickup_zones
source: catalog.pub.D_TAXI_ZONE_PICKUP
on: source.taxi_zone_pickup_wid = pickup_zones.taxi_zone_wid
rely:
at_most_one_match: true
- name: dropoff_zones
source: catalog.pub.D_TAXI_ZONE_DROPOFF
on: source.taxi_zone_dropoff_wid = dropoff_zones.taxi_zone_wid
rely:
at_most_one_match: true
fields:
- name: trip_type
expr: trip_type
synonyms:
- service type
- taxi service
comment: "Taxi type: yellow (citywide street-hail) or green (outer-borough street-hail)"
- name: pickup_zone
expr: pickup_zones.pickup_zone
comment: "Official NYC TLC taxi zone name for the pickup location"
- name: dropoff_zone
expr: dropoff_zones.dropoff_zone
comment: "Official NYC TLC taxi zone name for the dropoff location"
- name: pickup_borough
expr: pickup_zones.pickup_borough
comment: "The borough of the pickup location"
- name: dropoff_borough
expr: dropoff_zones.dropoff_borough
comment: "The borough of the dropoff location"
# Include these fields if users should also be able to group or filter
# by the individual numeric values.
- name: fare_amount
expr: fare_amount
comment: "Time-and-distance fare in dollars calculated by the meter"
- name: total_amount
expr: total_amount
comment: "Total amount paid by the passenger (including tips, tolls, MTA tax and other surcharges)"
- name: trip_distance
expr: trip_distance
comment: "Total distance traveled in miles"
measures:
- name: total_revenue
expr: SUM(total_amount)
synonyms:
- gross revenue
- name: fare_revenue
expr: SUM(fare_amount)
- name: total_distance
expr: SUM(trip_distance)
synonyms:
- combined distance
Will generate a metrics view as:
CREATE VIEW publish.TAXI_TRIP_CONTENT WITH METRICS LANGUAGE YAML AS
$$
version: 1.1
comment: "All NYC metered taxi trips (yellow and green services)"
source: catalog.schema.trips
joins:
- name: pickup_zones
source: catalog.pub.D_TAXI_ZONE_PICKUP
on: source.taxi_zone_pickup_wid = pickup_zones.taxi_zone_wid
rely:
at_most_one_match: true
- name: dropoff_zones
source: catalog.pub.D_TAXI_ZONE_DROPOFF
on: source.taxi_zone_dropoff_wid = dropoff_zones.taxi_zone_wid
rely:
at_most_one_match: true
fields:
- name: trip_type
expr: trip_type
synonyms:
- service type
- taxi service
comment: "Taxi type: yellow (citywide street-hail) or green (outer-borough street-hail)"
- name: pickup_zone
expr: pickup_zones.pickup_zone
comment: "Official NYC TLC taxi zone name for the pickup location"
- name: dropoff_zone
expr: dropoff_zones.dropoff_zone
comment: "Official NYC TLC taxi zone name for the dropoff location"
- name: pickup_borough
expr: pickup_zones.pickup_borough
comment: "The borough of the pickup location"
- name: dropoff_borough
expr: dropoff_zones.dropoff_borough
comment: "The borough of the dropoff location"
# Include these fields if users should also be able to group or filter
# by the individual numeric values.
- name: fare_amount
expr: fare_amount
comment: "Time-and-distance fare in dollars calculated by the meter"
- name: total_amount
expr: total_amount
comment: "Total amount paid by the passenger (including tips, tolls, MTA tax and other surcharges)"
- name: trip_distance
expr: trip_distance
comment: "Total distance traveled in miles"
measures:
- name: total_revenue
expr: SUM(total_amount)
synonyms:
- gross revenue
- name: fare_revenue
expr: SUM(fare_amount)
- name: total_distance
expr: SUM(trip_distance)
synonyms:
- combined distance
$$
;
Databricks SQL example (with attributes,descriptions,tags)
Case: Defined entity publish.TAXI_TRIP_CONTENT with physical type SEMANTIC_VIEW.
Adding load with entity mappings:
-
catalog.pub.F_TRIP
-
catalog.pub.D_TAXI_ZONE_PICKUP
-
catalog.pub.D_TAXI_ZONE_DROPOFF
Defining attributes:
-
trip_type
-
pickup_zone
-
dropoff_zone
-
pickup_borough
-
dropoff_borough
-
fare_amount
-
total_amount
-
trip_distance
-
total_revenue
-
fare_revenue
-
total_distance
Defining entity description:
All the NYC metered taxi trips (yellow and green services) for now
Defining entity database tag:
cost_center:analytics
Adding View SQL like this:
version: 1.1
comment: "All NYC metered taxi trips (yellow and green services)"
source: catalog.pub.F_TRIP
joins:
- name: pickup_zones
source: catalog.pub.D_TAXI_ZONE_PICKUP
on: source.taxi_zone_pickup_wid = pickup_zones.taxi_zone_wid
rely:
at_most_one_match: true
- name: dropoff_zones
source: catalog.pub.D_TAXI_ZONE_DROPOFF
on: source.taxi_zone_dropoff_wid = dropoff_zones.taxi_zone_wid
rely:
at_most_one_match: true
fields:
- name: trip_type
expr: trip_type
synonyms:
- service type
- taxi service
comment: "Taxi type: yellow (citywide street-hail) or green (outer-borough street-hail)"
- name: pickup_zone
expr: pickup_zones.pickup_zone
comment: "Official NYC TLC taxi zone name for the pickup location"
- name: dropoff_zone
expr: dropoff_zones.dropoff_zone
comment: "Official NYC TLC taxi zone name for the dropoff location"
- name: pickup_borough
expr: pickup_zones.pickup_borough
comment: "The borough of the pickup location"
- name: dropoff_borough
expr: dropoff_zones.dropoff_borough
comment: "The borough of the dropoff location"
# Include these fields if users should also be able to group or filter
# by the individual numeric values.
- name: fare_amount
expr: fare_amount
comment: "Time-and-distance fare in dollars calculated by the meter"
- name: total_amount
expr: total_amount
comment: "Total amount paid by the passenger (including tips, tolls, MTA tax and other surcharges)"
- name: trip_distance
expr: trip_distance
comment: "Total distance traveled in miles"
measures:
- name: total_revenue
expr: SUM(total_amount)
synonyms:
- gross revenue
- name: fare_revenue
expr: SUM(fare_amount)
- name: total_distance
expr: SUM(trip_distance)
synonyms:
- combined distance
Will generate a metrics view as:
CREATE VIEW publish.TAXI_TRIP_CONTENT (
trip_type
, pickup_zone
, dropoff_zone
, pickup_borough
, dropoff_borough
, fare_amount
, total_amount
, trip_distance
, total_revenue
, fare_revenue
, total_distance
) WITH METRICS LANGUAGE YAML
COMMENT 'All the NYC metered taxi trips (yellow and green services) for now'
AS $$
version: 1.1
comment: "All NYC metered taxi trips (yellow and green services)"
source: ade_test.pub.F_TRIP_DB
joins:
- name: pickup_zones
source: ade_test.pub.D_TAXI_ZONE_DB_PICKUP
on: source.taxi_zone_pickup_wid = pickup_zones.taxi_zone_wid
rely:
at_most_one_match: true
- name: dropoff_zones
source: ade_test.pub.D_TAXI_ZONE_DB_DROPOFF
on: source.taxi_zone_dropoff_wid = dropoff_zones.taxi_zone_wid
rely:
at_most_one_match: true
fields:
- name: trip_type
expr: trip_type
synonyms:
- service type
- taxi service
comment: "Taxi type: yellow (citywide street-hail) or green (outer-borough street-hail)"
- name: pickup_zone
expr: pickup_zones.pickup_zone
comment: "Official NYC TLC taxi zone name for the pickup location"
- name: dropoff_zone
expr: dropoff_zones.dropoff_zone
comment: "Official NYC TLC taxi zone name for the dropoff location"
- name: pickup_borough
expr: pickup_zones.pickup_borough
comment: "The borough of the pickup location"
- name: dropoff_borough
expr: dropoff_zones.dropoff_borough
comment: "The borough of the dropoff location"
# Include these fields if users should also be able to group or filter
# by the individual numeric values.
- name: fare_amount
expr: fare_amount
comment: "Time-and-distance fare in dollars calculated by the meter"
- name: total_amount
expr: total_amount
comment: "Total amount paid by the passenger (including tips, tolls, MTA tax and other surcharges)"
- name: trip_distance
expr: trip_distance
comment: "Total distance traveled in miles"
measures:
- name: total_revenue
expr: SUM(total_amount)
synonyms:
- gross revenue
- name: fare_revenue
expr: SUM(fare_amount)
- name: total_distance
expr: SUM(trip_distance)
synonyms:
- combined distance
$$
;
ALTER VIEW publish.TAXI_TRIP_CONTENT SET TAGS ('cost_center' = 'analytics');