语义视图的 YAML 规范¶

# Name and description of the semantic view.
name: <name>
description: <string>
comments: <string>

# Logical table-level concepts
# A semantic view can contain one or more logical tables.
tables:
  # A logical table on top of a base table.
  - name: <name>
    description: <string>
    # The fully qualified name of the base table.
    base_table:
      database: <database>
      schema: <schema>
      table: <base table name>

    # Dimension columns in the logical table.
    dimensions:
      - name: <name>
        synonyms: <array of strings>
        description: <string>
        expr: <SQL expression>
        data_type: <data type>
        unique: <boolean>
        cortex_search_service:
          service: <string>
          literal_column: <string>
          database: <string>
          schema: <string>
        is_enum: <boolean>
    - ...
    # Time dimension columns in the logical table.
    time_dimensions:
      - name: <name>
        synonyms: <array of strings>
        description: <string>
        expr: <SQL expression>
        data_type: <data type>
        unique: <boolean>

    # Fact columns in the logical table.
    facts:
      - name: <name>
        synonyms: <array of strings>
        description: <string>
        access_modifier: <public_access | private_access>  # Default is public_access.
        expr: <SQL expression>
        data_type: <data type>

    # Regular metrics scoped to the logical table.
    metrics:
      - name: <name>
        synonyms: <array of strings>
        description: <string>
        access_modifier: <public_access | private_access>  # Default is public_access.
        expr: <SQL expression>

    # Commonly used filters over the logical table.
    filters:
      - name: <name>
        synonyms: <array of strings>
        description: <string>
        expr: <SQL expression>

# View-level concepts
# Relationships between logical tables
relationships:
  - name: <string>
    left_table: <table>
    right_table: <table>
    relationship_columns:
      - left_column: <column>
        right_column: <column>
      - left_column: <column>
        right_column: <column>

# Derived metrics scoped to the semantic view.
# Derived metrics combine metrics from multiple tables.
metrics:
  - name: <name>
    synonyms: <array of strings>
    description: <string>
    access_modifier: <public_access | private_access>  # Default is public_access
    expr: <SQL expression>

# Additional context concepts
# Verified queries with example questions and queries that answer them
verified_queries:
  - name: <string>       # A descriptive name of the query.
    question: <string>   # The natural language question that this query answers.
    verified_at: <int>   # Optional: Time (in seconds since the UNIX epoch, January 1, 1970) when the query was verified.
    verified_by: <string> # Optional: Name of the person who verified the query.
    use_as_onboarding_question: <boolean>  # Optional: Marks this question as an onboarding question for the end user.
    sql: <string>        # The SQL query for answering the question

facts:
  - name: internal_cost
    expr: unit_cost * quantity
    data_type: NUMBER
    access_modifier: private_access  # Not visible in queries

metrics:
  - name: total_revenue
    expr: SUM(sale_amount)
    access_modifier: public_access  # Visible in queries

name: revenue_analysis
description: "Semantic view for analyzing revenue across products and customers"

tables:
  - name: customers
    description: "Customer information"
    base_table:
      database: sales_db
      schema: public
      table: customers
    dimensions:
      - name: customer_name
        synonyms: ["client name", "customer"]
        description: "Full name of the customer"
        expr: c_name
        data_type: VARCHAR
      - name: customer_segment
        synonyms: ["segment", "market segment"]
        description: "Customer market segment"
        expr: c_mktsegment
        data_type: VARCHAR
        is_enum: true

  - name: orders
    description: "Order information"
    base_table:
      database: sales_db
      schema: public
      table: orders
    dimensions:
      - name: order_date
        description: "Date when order was placed"
        expr: o_orderdate
        data_type: DATE
    time_dimensions:
      - name: order_year
        description: "Year when order was placed"
        expr: YEAR(o_orderdate)
        data_type: NUMBER
    facts:
      - name: order_total
        description: "Total order amount"
        expr: o_totalprice
        data_type: NUMBER
    metrics:
      - name: total_orders
        description: "Total number of orders"
        expr: COUNT(*)
      - name: average_order_value
        description: "Average order value"
        expr: AVG(o_totalprice)

relationships:
  - name: orders_to_customers
    left_table: orders
    right_table: customers
    relationship_columns:
      - left_column: o_custkey
        right_column: c_custkey

metrics:
  - name: revenue_per_customer
    description: "Average revenue per customer"
    expr: orders.total_revenue / customers.customer_count
    access_modifier: public_access

verified_queries:
  - name: top_customers_by_revenue
    question: "Who are the top 10 customers by revenue?"
    sql: |
      SELECT
        customer_name,
        SUM(order_total) as total_revenue
      FROM revenue_analysis
      GROUP BY customer_name
      ORDER BY total_revenue DESC
      LIMIT 10
    use_as_onboarding_question: true