免费开发工具 · Apache Ossie / Dosi

Apache Ossie 转 Snowflake 语义模型转换器

将 Apache Ossie YAML 导出为 Snowflake Cortex Analyst 格式。试用 TPC-DS Retail 样例，检查元数据诊断并下载生成的 YAML。

无需注册 · 内置测试样例 · 离线转换检查

转换工作台

源文件1样例

TPC-DS Retail · ANSI_SQL

tpcds_retail.yml

# Licensed to the Apache Software Foundation (ASF) under one
# or more contributor license agreements.  See the NOTICE file
# distributed with this work for additional information
# regarding copyright ownership.  The ASF licenses this file
# to you under the Apache License, Version 2.0 (the
# "License"); you may not use this file except in compliance
# with the License.  You may obtain a copy of the License at
#
#   http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing,
# software distributed under the License is distributed on an
# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
# KIND, either express or implied.  See the License for the
# specific language governing permissions and limitations
# under the License.

# yaml-language-server: $schema=../core-spec/ossie-schema.json

# TPC-DS Semantic Model Example
# This example demonstrates the Ossie Core Metadata Spec using the TPC-DS benchmark schema
# TPC-DS is a decision support benchmark with a realistic retail business model

version: "0.2.0.dev0"

name: tpcds_retail_model
description: TPC-DS retail semantic model for sales and customer analytics
ai_context:
  instructions: "Use this semantic model for retail analytics. It provides comprehensive sales, customer, product, and store data from the TPC-DS benchmark. The model supports time-based analysis, customer segmentation, product performance, and store operations metrics."

datasets:
  # Fact table: Store sales transactions
  - name: store_sales
    source: tpcds.public.store_sales
    primary_key: [ss_item_sk, ss_ticket_number]  # Composite primary key
    unique_keys:
      - [ss_item_sk, ss_ticket_number]  # Composite key: item + ticket number uniquely identifies a line item
    description: Fact table containing all store sales transactions
    ai_context:
      synonyms:
        - "sales transactions"
        - "store purchases"
        - "retail sales"
        - "POS data"

fields:
      - name: ss_sold_date_sk
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: ss_sold_date_sk
        description: Foreign key to date dimension
        dimension:
          is_time: false
        ai_context:
          synonyms:
            - "sale date"
            - "transaction date"

- name: ss_item_sk
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: ss_item_sk
        description: Foreign key to item dimension
        dimension:
          is_time: false
        ai_context:
          synonyms:
            - "product"
            - "item"

- name: ss_customer_sk
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: ss_customer_sk
        description: Foreign key to customer dimension
        dimension:
          is_time: false
        ai_context:
          synonyms:
            - "customer"
            - "buyer"

- name: ss_store_sk
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: ss_store_sk
        description: Foreign key to store dimension
        dimension:
          is_time: false
        ai_context:
          synonyms:
            - "store"
            - "location"

- name: ss_quantity
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: ss_quantity
        description: Quantity of items sold
        ai_context:
          synonyms:
            - "units sold"
            - "quantity"

- name: ss_sales_price
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: ss_sales_price
        description: Sales price per unit
        ai_context:
          synonyms:
            - "unit price"
            - "price"

- name: ss_ext_sales_price
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: ss_ext_sales_price
        description: Extended sales price (quantity * price)
        ai_context:
          synonyms:
            - "total price"
            - "line total"

- name: ss_net_profit
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: ss_net_profit
        description: Net profit from the sale
        ai_context:
          synonyms:
            - "profit"
            - "margin"

# Dimension table: Date
  - name: date_dim
    source: tpcds.public.date_dim
    primary_key: [d_date_sk]  # Simple primary key
    unique_keys:
      - [d_date_sk]  # Simple key: single column
    description: Date dimension with calendar attributes
    ai_context:
      synonyms:
        - "calendar"
        - "dates"
        - "time periods"

fields:
      - name: d_date_sk
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: d_date_sk
        description: Surrogate key for date
        dimension:
          is_time: false

- name: d_date
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: d_date
        description: Actual date value
        dimension:
          is_time: true
        ai_context:
          synonyms:
            - "date"
            - "calendar date"

- name: d_year
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: d_year
        description: Year
        dimension:
          is_time: true
        ai_context:
          synonyms:
            - "year"

- name: d_quarter_name
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: d_quarter_name
        description: Quarter name (e.g., 2024Q1)
        dimension:
          is_time: true
        ai_context:
          synonyms:
            - "quarter"
            - "fiscal quarter"

- name: d_moy
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: d_moy
        description: Month of year (1-12)
        dimension:
          is_time: true
        ai_context:
          synonyms:
            - "month"

# Dimension table: Customer
  - name: customer
    source: tpcds.public.customer
    primary_key: [c_customer_sk]  # Simple primary key
    unique_keys:
      - [c_customer_sk]  # Simple key: single column
      - [c_customer_id]  # Alternate key: customer business key
    description: Customer dimension with demographic information
    ai_context:
      synonyms:
        - "customers"
        - "shoppers"
        - "buyers"

fields:
      - name: c_customer_sk
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: c_customer_sk
        description: Surrogate key for customer
        dimension:
          is_time: false

- name: c_customer_id
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: c_customer_id
        description: Business key for customer
        dimension:
          is_time: false
        ai_context:
          synonyms:
            - "customer ID"
            - "customer number"

- name: c_first_name
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: c_first_name
        description: Customer first name
        dimension:
          is_time: false

- name: c_last_name
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: c_last_name
        description: Customer last name
        dimension:
          is_time: false

- name: customer_full_name
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: c_first_name || ' ' || c_last_name
        description: Customer full name (computed field)
        dimension:
          is_time: false
        ai_context:
          synonyms:
            - "full name"
            - "customer name"

- name: c_email_address
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: c_email_address
        description: Customer email address
        dimension:
          is_time: false
        ai_context:
          synonyms:
            - "email"
            - "contact"

# Dimension table: Item (Product)
  - name: item
    source: tpcds.public.item
    primary_key: [i_item_sk]  # Simple primary key
    unique_keys:
      - [i_item_sk]  # Simple key: single column
    description: Item/Product dimension with product attributes
    ai_context:
      synonyms:
        - "products"
        - "items"
        - "merchandise"

fields:
      - name: i_item_sk
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: i_item_sk
        description: Surrogate key for item
        dimension:
          is_time: false

- name: i_item_id
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: i_item_id
        description: Business key for item
        dimension:
          is_time: false
        ai_context:
          synonyms:
            - "item ID"
            - "product ID"
            - "SKU"

- name: i_item_desc
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: i_item_desc
        description: Item description
        dimension:
          is_time: false
        ai_context:
          synonyms:
            - "product description"
            - "item name"

- name: i_brand
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: i_brand
        description: Brand name
        dimension:
          is_time: false
        ai_context:
          synonyms:
            - "brand"
            - "manufacturer"

- name: i_category
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: i_category
        description: Item category
        dimension:
          is_time: false
        ai_context:
          synonyms:
            - "product category"
            - "department"

- name: i_current_price
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: i_current_price
        description: Current price of the item
        dimension:
          is_time: false
        ai_context:
          synonyms:
            - "price"
            - "list price"

# Dimension table: Store
  - name: store
    source: tpcds.public.store
    primary_key: [s_store_sk]  # Simple primary key
    unique_keys:
      - [s_store_sk]  # Simple key: single column
    description: Store dimension with location and store attributes
    ai_context:
      synonyms:
        - "stores"
        - "retail locations"
        - "branches"

fields:
      - name: s_store_sk
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: s_store_sk
        description: Surrogate key for store
        dimension:
          is_time: false

- name: s_store_id
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: s_store_id
        description: Business key for store
        dimension:
          is_time: false
        ai_context:
          synonyms:
            - "store ID"
            - "store number"

- name: s_store_name
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: s_store_name
        description: Store name
        dimension:
          is_time: false
        ai_context:
          synonyms:
            - "store name"
            - "location name"

- name: s_city
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: s_city
        description: City where store is located
        dimension:
          is_time: false
        ai_context:
          synonyms:
            - "city"
            - "location"

- name: s_state
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: s_state
        description: State where store is located
        dimension:
          is_time: false
        ai_context:
          synonyms:
            - "state"
            - "region"

- name: s_number_employees
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: s_number_employees
        description: Number of employees at the store
        ai_context:
          synonyms:
            - "employee count"
            - "staff size"

# Relationships between datasets
relationships:
  - name: store_sales_to_date
    from: store_sales
    to: date_dim
    from_columns: [ss_sold_date_sk]
    to_columns: [d_date_sk]
    ai_context:
      synonyms:
        - "sales date relationship"
        - "when sale occurred"

- name: store_sales_to_customer
    from: store_sales
    to: customer
    from_columns: [ss_customer_sk]
    to_columns: [c_customer_sk]
    ai_context:
      synonyms:
        - "customer purchase relationship"
        - "who bought"

- name: store_sales_to_item
    from: store_sales
    to: item
    from_columns: [ss_item_sk]
    to_columns: [i_item_sk]
    ai_context:
      synonyms:
        - "product sold relationship"
        - "what was sold"

- name: store_sales_to_store
    from: store_sales
    to: store
    from_columns: [ss_store_sk]
    to_columns: [s_store_sk]
    ai_context:
      synonyms:
        - "store location relationship"
        - "where sale occurred"

# Semantic model-level metrics spanning multiple datasets
metrics:
  - name: total_sales
    expression:
      dialects:
        - dialect: ANSI_SQL
          expression: SUM(store_sales.ss_ext_sales_price)
    description: Total sales revenue across all transactions
    ai_context:
      synonyms:
        - "total revenue"
        - "gross sales"
        - "sales amount"

- name: total_profit
    expression:
      dialects:
        - dialect: ANSI_SQL
          expression: SUM(store_sales.ss_net_profit)
    description: Total net profit from store sales
    ai_context:
      synonyms:
        - "net profit"
        - "total earnings"
        - "profit"

- name: customer_lifetime_value
    expression:
      dialects:
        - dialect: ANSI_SQL
          expression: SUM(store_sales.ss_ext_sales_price) / COUNT(DISTINCT customer.c_customer_sk)
    description: Average lifetime sales value per customer
    ai_context:
      synonyms:
        - "CLV"
        - "LTV"
        - "customer value"
        - "lifetime revenue"

- name: sales_by_brand
    expression:
      dialects:
        - dialect: ANSI_SQL
          expression: SUM(store_sales.ss_ext_sales_price)
    description: Total sales by brand (requires grouping by item.i_brand)
    ai_context:
      synonyms:
        - "brand sales"
        - "brand performance"
        - "brand revenue"

- name: store_productivity
    expression:
      dialects:
        - dialect: ANSI_SQL
          expression: SUM(store_sales.ss_ext_sales_price) / NULLIF(SUM(store.s_number_employees), 0)
    description: Sales per employee across stores
    ai_context:
      synonyms:
        - "sales per employee"
        - "employee productivity"
        - "revenue per employee"

custom_extensions:
  - vendor_name: SALESFORCE
    data: |
      {
        "tableau_workbook_id": "tpcds_retail_dashboard",
        "einstein_enabled": true,
        "crm_sync": {
          "enabled": true,
          "sync_frequency": "daily",
          "customer_mapping": "customer.c_customer_id -> Account.AccountNumber"
        },
        "tableau_semantics": {
          "published": true,
          "version": "1.0"
        }
      }

- vendor_name: DBT
    data: '{"project_name": "tpcds_analytics", "models_path": "models/semantic"}'

源文件仅保留在内存中，请一并上传所有被引用的文件。查看原始样例

结果

选择目标平台并转换，在此预览临时文件。

点击转换时，当前源文件将发送至 Datus 服务器；不会连接数据仓库或执行 SQL。

此转换器的用途

由 Apache Ossie 模型准备 Snowflake Cortex Analyst YAML。此页预载 TPC-DS Retail 样例并选择 Snowflake 导出目标，适合检查较大模型的目标元数据诊断。

来源格式

Apache Ossie YAML。Retail 样例包含 ANSI_SQL 表达式，请核对自己的数仓流程可以使用哪些原生表达式。

生成产物

受支持的 Snowflake Cortex Analyst YAML，不生成或部署 CREATE SEMANTIC VIEW SQL。

转换步骤

检查 TPC-DS Retail 模型，或上传兼容的 Ossie YAML。
保留 Snowflake 目标，检查已部署目录提供的配置。
转换后阅读元数据与兼容性警告，再编辑生成的 YAML。
下载文件，通过所需 Snowflake 流程验证，并测试代表性问题或指标查询。

使用产物前需要检查的事项

工具没有 Snowflake Cortex Analyst 转 Ossie 的通用反向导入器。

选择方言不会将 ANSI_SQL 翻译为 Snowflake SQL；离线检查也不会执行数仓查询。

继续探索

常见问题

能将 Snowflake Cortex Analyst 模型反向导入 Ossie 吗？

此工具没有 Snowflake Cortex Analyst 的通用反向导入器。本页支持的流程是将 Apache Ossie YAML 导出为 Snowflake 目标格式。

会生成 CREATE SEMANTIC VIEW SQL 吗？

此目标生成支持的 Cortex Analyst YAML，不承诺通过 SQL DDL 创建或部署原生 Snowflake semantic view。

为什么 Retail 样例可能需要检查？

部分目标无法保留所有元数据与模型结构。请阅读诊断，并在 Snowflake 中验证产物后再使用。