Example of using SQL to create a semantic view¶

CREATE TABLE region (
  r_regionkey  INTEGER NOT NULL,
  r_name       VARCHAR,
  r_comment    VARCHAR,
  PRIMARY KEY (r_regionkey)
);

CREATE TABLE nation (
  n_nationkey  INTEGER NOT NULL,
  n_name       VARCHAR,
  n_regionkey  INTEGER NOT NULL,
  n_comment    VARCHAR,
  PRIMARY KEY (n_nationkey)
);

CREATE TABLE customer (
  c_custkey     INTEGER NOT NULL,
  c_name        VARCHAR,
  c_address     VARCHAR,
  c_nationkey   INTEGER NOT NULL,
  c_phone       VARCHAR,
  c_acctbal     DECIMAL,
  c_mktsegment  VARCHAR,
  c_comment     VARCHAR,
  PRIMARY KEY (c_custkey)
);

CREATE TABLE orders (
  o_orderkey       INTEGER NOT NULL,
  o_custkey        INTEGER NOT NULL,
  o_orderstatus    VARCHAR,
  o_totalprice     DECIMAL,
  o_orderdate      DATE,
  o_orderpriority  VARCHAR,
  o_clerk          VARCHAR,
  o_shippriority   INTEGER,
  o_comment        VARCHAR,
  PRIMARY KEY (o_orderkey)
);

CREATE TABLE lineitem (
  l_orderkey       INTEGER NOT NULL,
  l_partkey        INTEGER NOT NULL,
  l_suppkey        INTEGER NOT NULL,
  l_linenumber     INTEGER NOT NULL,
  l_quantity       DECIMAL,
  l_extendedprice  DECIMAL,
  l_discount       DECIMAL,
  l_tax            DECIMAL,
  l_returnflag     VARCHAR,
  l_linestatus     VARCHAR,
  l_shipdate       DATE,
  l_commitdate     DATE,
  l_receiptdate    DATE,
  l_shipinstruct   VARCHAR,
  l_shipmode       VARCHAR,
  l_comment        VARCHAR,
  PRIMARY KEY (l_orderkey, l_linenumber)
);

CREATE TABLE supplier (
  s_suppkey     INTEGER NOT NULL,
  s_name        VARCHAR,
  s_address     VARCHAR,
  s_nationkey   INTEGER NOT NULL,
  s_phone       VARCHAR,
  s_acctbal     DECIMAL,
  s_comment     VARCHAR,
  PRIMARY KEY (s_suppkey)
);

CREATE OR REPLACE SEMANTIC VIEW tpch_analysis

  TABLES (
    tpch.region   PRIMARY KEY (r_regionkey),
    tpch.nation   PRIMARY KEY (n_nationkey),
    tpch.customer PRIMARY KEY (c_custkey),
    tpch.orders   PRIMARY KEY (o_orderkey),
    tpch.lineitem PRIMARY KEY (l_orderkey, l_linenumber),
    tpch.supplier PRIMARY KEY (s_suppkey)
  )

  RELATIONSHIPS (
    nation   (n_regionkey) REFERENCES region,
    customer (c_nationkey) REFERENCES nation,
    orders   (o_custkey)   REFERENCES customer,
    lineitem (l_orderkey)  REFERENCES orders,
    supplier (s_nationkey) REFERENCES nation
  )

  FACTS (
    region.r_name AS r_name,
    nation.n_name AS n_name,
    orders.o_orderkey AS o_orderkey,
    -- Fact on Customer table that fetches data from related Orders table
    customer.c_customer_order_count AS COUNT(orders.o_orderkey)
  )

  DIMENSIONS (
    -- Dimensions on Nation table
    nation.nation_name AS n_name,
    -- Dimensions on Customer table
    customer.customer_name AS c_name,
    customer.customer_region_name AS region.r_name,
    customer.customer_nation_name AS nation.n_name,
    customer.customer_market_segment AS c_mktsegment,
    customer.customer_country_code AS LEFT(c_phone, 2)
  )

  METRICS (
    -- Metrics on Customer table
    customer.customer_count AS COUNT(c_custkey),
    customer.customer_order_count AS SUM(c_customer_order_count),
    -- Metrics on Orders table
    orders.order_count AS COUNT(o_orderkey),
    orders.order_average_value AS AVG(orders.o_totalprice),
    -- Metrics on Supplier table
    supplier.supplier_count AS COUNT(s_suppkey)
  )
;