探索来自 SAP® Business Data Cloud 的数据¶

在本主题中，我们将探索已共享至 Snowflake 的数据。所有示例旨在演示如何使用 Snowflake 访问数据。

以下章节以 CUSTOMER 作为示例数据库，但您可以按照相同步骤探索自己数据库中的数据。

浏览数据库、架构和表¶

检查数据库：

DESC DATABASE CUSTOMER;

其产生的结果应类似于：

+--------------------------------+---------------------------------+
|created_on                              | name                            | kind      |
+--------------------------------+---------------------------------+
| 2025-12-17 13:30:01.062 -0800  | INFORMATION_SCHEMA      | SCHEMA    |
| 2025-12-17 13:11:12.206 -0800  | customer            | SCHEMA    |
+--------------------------------+---------------------------------+

其中每一行代表您数据库中的一个架构。

检查数据库中的表：

SHOW TABLES IN CUSTOMER;

其产生的结果应类似于：

+------------------------+----------------+-------------+-------+-------+--------+
| name                               | database_name  | schema_name | kind  | rows  | bytes  |
+------------------------+----------------+-------------+-------+-------+--------+
| customer                           | CUSTOMER       | customer    | TABLE | 2174  | 215708 |
| customercompanycode          | CUSTOMER               | customer    | TABLE | 1792  | 37311  |
| customerdunning                | CUSTOMER             | customer    | TABLE | 44    | 4912   |
| customersalesarea            | CUSTOMER               | customer    | TABLE | 442   | 34415  |
| customersalesareatax   | CUSTOMER             | customer    | TABLE | 883   | 9153   |
| customerunloadingpoint | CUSTOMER             | customer    | TABLE | 37    | 13253  |
+------------------------+----------------+-------------+-------+-------+--------+

查询 CUSTOMER 数据库中的表¶

查询“customer”表：

SELECT * FROM CUSTOMER.customer.customer;

SELECT * FROM CUSTOMER.customer.customer WHERE CREATEDBYUSER = 'KAPOORM'

从共享数据创建派生数据¶

通过联接来自 2 个共享数据产品的表来创建 L1 层数据：

-- Join tables in CUSTOMER and ENTRYVIEWJOURNALENTRY to find top 10 customers by revenue
SELECT
    c.customer,
    c.customername,
    c.country,
    c.region,
    c.businesstype,
    COUNT(DISTINCT e.accountingdocument) as num_transactions,
    SUM(e.amountincompanycodecurrency) as total_revenue,
    AVG(e.amountincompanycodecurrency) as avg_transaction_amount
FROM CUSTOMER.customer.customer c
JOIN ENTRYVIEWJOURNALENTRY.entryviewjournalentry.operationalacctgdocitem e
   ON c.customer = e.customer
WHERE c.deletionindicator = FALSE
GROUP BY 1,2,3,4,5
ORDER BY total_revenue DESC
LIMIT 10;

在新数据库中通过查询创建表 (CTAS)¶

创建一个新数据库来存放 CTAS：

CREATE DATABASE CUSTOMER_CTAS_DEMO;
USE DATABASE CUSTOMER_CTAS_DEMO;

-- Create the CTAS
CREATE OR REPLACE TABLE top_customers_by_revenue AS
SELECT
  c.customer,
  c.customername,
  c.country,
  c.region,
  c.businesstype,
  COUNT(DISTINCT e.accountingdocument) as num_transactions,
  SUM(e.amountincompanycodecurrency) as total_revenue,
  AVG(e.amountincompanycodecurrency) as avg_transaction_amount
FROM CUSTOMER.customer.customer c
JOIN ENTRYVIEWJOURNALENTRY.entryviewjournalentry.operationalacctgdocitem e
    ON c.customer = e.customer
WHERE c.deletionindicator = FALSE
 GROUP BY 1,2,3,4,5
 ORDER BY total_revenue DESC;

 -- Query the CTAS
 SELECT * FROM top_customers_by_revenue LIMIT 10;