OpenLake Studio: Intelligent lakehouse data development

Updated at:

This guide shows you how to transition from traditional data development to a modern, accessible, and automated AI-driven paradigm. This new approach significantly improves development efficiency, lowers the technical skill required, and helps you derive value from your data faster.

Product overview

DataWorks is deeply integrated with the OpenLake architecture. Its core Agent automates data development and provides an end-to-end "Data+AI" management service, covering everything from requirements analysis and ETL processing to data analysis and Data Governance. Built on the open OpenLake architecture, DataWorks orchestrates data development workflows and leverages multi-engine collaboration across MaxCompute, EMR Serverless Spark, Hologres, and EMR Serverless StarRocks, to help you easily build and manage a modern Data Lakehouse.

Lab overview

Welcome to the OpenLake Studio Intelligent Lakehouse Data Development hands-on lab!

In this lab, you will work with a real-world e-commerce livestreaming scenario to transform raw business data into actionable insights.

Step 1: Process e-commerce livestreaming data

First, you will build a modern Data Lakehouse system by:

  • Syncing business data from ApsaraDB RDS to an open data lake managed by Data Lake Formation (DLF).

  • Leveraging multi-engine collaboration across MaxCompute, EMR Serverless Spark, EMR Serverless StarRocks, and Hologres.

  • Building detailed data models, performing Lightweight Aggregation, accelerating queries, and enabling AI applications.

Step 2: Automatically build a data workflow

The core highlight of this hands-on lab is the DataWorks Agent. You will act as an ETL developer and use natural language instructions, such as "Help me build a livestreaming order analysis workflow". Watch as the Agent automatically analyzes requirements, plans tasks, generates code, orchestrates the workflow, and deploys it. This step demonstrates the productivity leap that the Agent brings to ETL development.

Step 3: Notebook and ChatBI

Once the ETL process is ready, you will explore new ways to consume data:

  • Use a professional Notebook for interactive analysis.

  • Use ChatBI to get instant business insights through a conversational, Q&A-based interface.

By completing these three steps, you will understand how OpenLake Studio simplifies complex data development, making it efficient and intelligent.

Prerequisites

To ensure a smooth lab experience, your environment must meet the following requirements.

Item

Requirement

DataWorks workspace template

Select the OpenLake template.

Region consistency

All products, including DLF, MaxCompute, Hologres, and EMR, must be deployed in the same region.
This feature is currently available only in the Hangzhou region.

DLF version

Use the latest version of DLF.

Hologres instance version

Version R3.2 or later.

Network connectivity

We recommend deploying all resources in the same Virtual Private Cloud (VPC) to ensure network connectivity.

Required permissions for environment setup

To prepare the environment, use a Root Account or a RAM User with the following RAM permissions to activate the required products:
- AliyunDataWorksFullAccess
- AliyunDLFFullAccess
- AliyunMaxComputeFullAccess
- AliyunHologresFullAccess
- AliyunEMRServerlessSparkFullAccess
- AliyunEMRStarRocksFullAccess
















Environment preparation

External resources

DataWorks environment

  1. Activate DataWorks and create an OpenLake workspace.

  2. Bind the DLF Data Catalog.

    image

  3. Bind the compute resources you prepared earlier: MaxCompute, Hologres, EMR Serverless Spark, and EMR Serverless StarRocks.

  4. Create a serverless resource group.

    image

  5. Bind the resource group to the OpenLake workspace.image

DataWorks data sources

RDS data source (MySQL)
  1. In the Management Center, navigate to the Data Sources tab and click New Data Source. Select MySQL as the data source type. image

  2. Enter the information for your activated ApsaraDB RDS instance and test the connection.

    image

DLF data source
  1. In Data Integration, navigate to the Data Sources tab and click New Data Source. Select DLF as the data source type. image

  2. Configure the DLF metadata address and permissions.

    image

Get started

  1. Click DataWorks OpenLake Solution and find the workspace you created in the OpenLake workspace list.image.png

  2. Click Go to > OpenLake Studio to open the Data Development page. image.png

  3. In the tutorial section, click OpenLake Studio Intelligent Lakehouse Data Development to begin the hands-on practice. image.png

Hands-on practice

OpenLake: E-commerce livestreaming data processing

Click View Sample to preview the built-in sample workflow: "E-commerce Livestreaming Data Processing".

image.png

1. Create the data processing workflow

Return to the tutorial page. Click Create E-commerce Livestreaming Data Processing Workflow and confirm. The system automatically generates a new workflow container for you.

image

2. Sync RDS data to DLF

  1. Click Sync RDS E-commerce Livestreaming Data to DLF to start creating a Data Integration/Batch Synchronization node. image.png

  2. Click Confirm. The system creates the node. image.png

  3. Hover over the node and click Open Node to open its configuration page.

    image.png

  4. Select MySQL as the Source and DLF as the Destination. Configure the data sources and resource group.

    Select the previously created ApsaraDB RDS and DLF data sources, and the resource group bound to the current workspace. Ensure that the resource group can connect to the data sources.

    image

  5. Click Next to configure the task.

  6. In the Source section, select the source table ctlive_trd_order_create. In the Destination section, click Generate Target Table Schema to automatically create the table.

    You can select more source tables as needed.

    image.png

  7. For Field Mapping, you can use the default name-based matching. image.png

  8. For the dirty data policy, we recommend that you select Reject dirty data.image.png

  9. In the right-side toolbar, click Run Configuration and configure the compute resources and resource group.

  10. At the top of the node editor, click Save and then Run to start the data synchronization.

3. Build order data details

  1. Click MaxCompute: Build Order Data Details to create a MaxCompute SQL node.image.png

  2. Click Confirm. The node is created successfully.image.png

  3. Hover over the node and click Open Node to go to the configuration page. The system automatically populates the editor with the following code.

    MaxCompute SQL code

    -- Order detail table
    -- Purpose: Serves as the DWD (Data Warehouse Detail) layer table to store standardized order detail data, supporting downstream multi-dimensional analysis (e.g., user behavior, product sales trends).
    
    -- Part 1: Create the target table
    -- Enable schema mode to allow the use of multi-level namespaces (e.g., e_commerceanalytics_mc_hzext.ctlive_dwd).
    SET odps.namespace.schema=true;
    
    -- Drop the table if it already exists to avoid metadata conflicts from repeated creation.
    drop table if exists ctlive_dwd_trd_order_create_di
    ;
    -- Create a partitioned table. Fields include basic order information (user, product, amount), geographic information (province/city/district), and device information (device type, network).
    -- Partition field: ds (date partition), used for daily data management to improve query efficiency.
    create table if not exists e_commerceanalytics_mc_hzext.ctlive_dwd.ctlive_dwd_trd_order_create_di
    (
      date_key         string comment 'Date key'
      ,live_room_id    string comment 'Livestreaming room ID'
      ,visitor_id      string comment 'Visitor ID'
      ,consumer_id     string comment 'Consumer ID'
      ,user_type       string comment 'User type'
      ,anchor_id       string comment 'Anchor ID'
      ,anchor_name     string comment 'Anchor name'
      ,agency_id       string comment 'Agency ID'
      ,agency_name     string comment 'Agency name'
      ,seller_id       string comment 'Seller ID'
      ,seller_name     string comment 'Seller name'
      ,shop_id         string comment 'Shop ID'
      ,item_id         string comment 'Item ID'
      ,leaf_cate_id    string comment 'Leaf category ID'
      ,brand_id        string comment 'Brand ID'
      ,brand_name      string comment 'Brand name'
      ,parent_order_id string comment 'Parent order ID'
      ,order_id        string comment 'Order ID'
      ,order_status    string comment 'Order status'
      ,item_quantity   bigint comment 'Item quantity'
      ,order_amount    double comment 'Order amount (CNY)'
      ,discount_amount double comment 'Discount amount (CNY)'
      ,shipping_fee    double comment 'Shipping fee (CNY)'
      ,total_amount    double comment 'Total amount paid (CNY)'
      ,create_time     string comment 'Order creation time'
      ,pay_time        string comment 'Order payment time'
      ,ship_time       string comment 'Order shipping time'
      ,complete_time   string comment 'Order completion time'
      ,cancel_time     string comment 'Order cancellation time'
      ,province_code   string comment 'Province code'
      ,province_name   string comment 'Province name'
      ,city_code       string comment 'City code'
      ,city_name       string comment 'City name'
      ,district_code   string comment 'District code'
      ,district_name   string comment 'District name'
      ,device_type     string comment 'Device type'
      ,os              string comment 'Operating system'
      ,network_type    string comment 'Network type'
    )
    COMMENT 'Livestreaming order creation detail table'
    PARTITIONED BY (
      ds STRING COMMENT 'Partition field'
    );
    
    -- Part 2: Insert cleaned data into the target table
    -- Temporarily disable the full table scan restriction in ODPS, allowing queries without partition conditions (for the current session only).
    SET odps.sql.allow.fullscan = true;
    
    -- Use INSERT OVERWRITE to write the cleaned data to the target table, automatically partitioning by the date_key field.
    INSERT OVERWRITE TABLE e_commerceanalytics_mc_hzext.ctlive_dwd.ctlive_dwd_trd_order_create_di PARTITION (ds)
    -- Query order data from the livestreaming e-commerce order table and perform a left join with the geography dimension table to add province, city, and district names.
    select  o.date_key ---Date key
            ,o.live_room_id ---Livestreaming room ID
            ,o.visitor_id ---Visitor ID
            ,o.consumer_id ---Consumer ID
            ,o.user_type ---User type
            ,o.anchor_id ---Anchor ID
            ,o.anchor_name ---Anchor name
            ,o.agency_id ---Agency ID
            ,o.agency_name ---Agency name
            ,o.seller_id ---Seller ID
            ,o.seller_name ---Seller name
            ,o.shop_id ---Shop ID
            ,o.item_id ---Item ID
            ,o.leaf_cate_id ---Leaf category ID
            ,o.brand_id ---Brand ID
            ,o.brand_name ---Brand name
            ,o.parent_order_id ---Parent order ID
            ,o.order_id ---Order ID
            ,o.order_status ---Order status
            ,o.item_quantity ---Item quantity
            ,o.order_amount ---Order amount
            ,o.discount_amount ---Discount amount
            ,o.shipping_fee ---Shipping fee
            ,o.total_amount ---Total amount
            ,o.create_time ---Creation time
            ,o.pay_time ---Payment time
            ,o.ship_time ---Shipping time
            ,o.complete_time ---Completion time
            ,o.cancel_time ---Cancellation time
            ,coalesce(g.province_code,o.province_code) as province_code ---Province code
            ,coalesce(g.province_name,o.province_name) as province_name ---Province name
            ,coalesce(g.city_code,o.city_code) as city_code ---City code
            ,coalesce(g.city_name,o.city_name) as city_name ---City name
            ,coalesce(g.district_code,o.district_code) as district_code ---District code
            ,coalesce(g.district_name,o.district_name) as district_name ---District name
            ,o.device_type ---Device type
            ,o.os ---Operating system
            ,o.network_type ---Network type
            ,o.date_key ---Insert partition
    from    e_commerceanalytics_mc_hzext.ctlive_ods.ods_ctlive_trd_order_create o
    left outer join e_commerceanalytics_mc_hzext.ctlive_dim.dim_pub_geography_df g
    on      o.province_code = g.province_code
    and     o.city_code = g.city_code
    and     o.district_code = g.district_code
    and     g.region_level = 4;
    
    -- Part 3: Data validation
    -- 1. Total data volume validation
    -- 1.1 Total data volume in the ODS layer
    SET odps.namespace.schema=true;
    SET odps.sql.allow.fullscan = true;
    select count(*) from e_commerceanalytics_mc_hzext.ctlive_ods.ods_ctlive_trd_order_create;
    
    -- 1.2 Total data volume in the DWD layer
    SET odps.namespace.schema=true;
    SET odps.sql.allow.fullscan = true;
    select count(*) from e_commerceanalytics_mc_hzext.ctlive_dwd.ctlive_dwd_trd_order_create_di;
    
    -- 2. Data partition validation
    -- 2.1 Data partitions in the ODS layer
    SET odps.namespace.schema=true;
    SET odps.sql.allow.fullscan = true;
    select date_key,count(*) from e_commerceanalytics_mc_hzext.ctlive_ods.ods_ctlive_trd_order_create group by date_key order by date_key desc limit 1000 ;
    
    -- 2.2 Data partitions in the DWD layer
    SET odps.namespace.schema=true;
    SET odps.sql.allow.fullscan = true;
    select ds,count(*) from e_commerceanalytics_mc_hzext.ctlive_dwd.ctlive_dwd_trd_order_create_di group by ds order by ds desc limit 1000;
    
    -- 3. Detailed data query
    SET odps.namespace.schema=true;
    SET odps.sql.allow.fullscan = true;
    select * from e_commerceanalytics_mc_hzext.ctlive_dwd.ctlive_dwd_trd_order_create_di where ds='20250618' limit 1000;

    image.png

  4. In the right-side toolbar, click Run Configuration and configure the compute resources and resource group you prepared earlier.image.png

  5. At the top of the node editor, click Run to run the task.

  6. After the task is complete, you can view the execution results and order detail data in the query results area.

    image.png

4. Build an order summary wide table

  1. Return to the workflow page for this tutorial and click Serverless Spark: Build Order Summary Wide Table to create an EMR Serverless Spark SQL node.image.png

  2. Click Confirm. The node is created successfully.

    image

  3. Hover over the node and click Open Node to go to the configuration page. The system automatically populates the editor with the following code.

    EMR Serverless Spark SQL code

    -- Order creation lightweight aggregation table
    -- Purpose: Serves as a lightweight aggregation layer table to summarize order data by dimensions such as anchor, province, and user type. It supports quick access to core metrics like paid GMV and order volume for these dimensions.
    
    -- Part 1: Create the target table
    -- Drop the table if it already exists to avoid metadata conflicts from repeated creation.
    drop table if exists ctlive_dws.dws_ctlive_trd_order_create_nd;
    
    -- Create a partitioned table. Fields include order_id, user_id, product_id, amount, province, city, district, device_type, network, and more.
    -- Partition field: ds (date partition), used for managing data by day, anchor, province, and other dimensions to improve query efficiency.
    create table ctlive_dws.dws_ctlive_trd_order_create_nd
    (
        date_key          string comment 'Date key'
        ,anchor_id        string comment 'Anchor ID'
        ,anchor_name      string comment 'Anchor name'
        ,agency_id        string comment 'Agency ID'
        ,agency_name      string comment 'Agency name'
        ,province_name    string comment 'Province name'
        ,user_type        string comment 'User type'
        ,seller_id        string comment 'Seller ID'
        ,seller_name      string comment 'Seller name'
        ,shop_id          string comment 'Shop ID'
        ,item_id          string comment 'Item ID'
        ,leaf_cate_id     string comment 'Leaf category ID'
        ,brand_id         string comment 'Brand ID'
        ,brand_name       string comment 'Brand name'
        ,order_count      bigint comment 'Number of orders'
        ,order_user_count bigint comment 'Number of ordering users'
        ,paid_order_count bigint comment 'Number of paid orders'
        ,paid_user_count  bigint comment 'Number of paying users'
        ,gmv              double comment 'GMV - Gross Merchandise Volume'
        ,total_amount     double comment 'Total amount paid'
        ,discount_amount  double comment 'Total discount amount'
        ,shipping_fee     double comment 'Total shipping fee'
        ,item_quantity    bigint comment 'Total number of items'
    )
    partitioned by 
    (
        ds                string comment 'Partition field'
    );
    
    -- Part 2: Insert cleaned data into the target table
    -- Use INSERT OVERWRITE to write the cleaned data to the target table.
    INSERT OVERWRITE TABLE  ctlive_dws.dws_ctlive_trd_order_create_nd partition (ds)
    -- Query order data from the livestreaming e-commerce order detail table. Aggregate core metrics like order count and GMV by order date, anchor, agency, province, and user type. Automatically partition by the date_key field.
    select  date_key -- Date key
            ,anchor_id -- Anchor ID
            ,anchor_name -- Anchor name
            ,agency_id -- Agency ID
            ,agency_name -- Agency name
            ,province_name -- Province name
            ,user_type -- User type
            ,seller_id ---Seller ID
            ,seller_name ---Seller name
            ,shop_id ---Shop ID
            ,item_id ---Item ID
            ,leaf_cate_id ---Leaf category ID
            ,brand_id ---Brand ID
            ,brand_name ---Brand name
            ,count(order_id) as order_count -- Total number of orders
            ,count(distinct consumer_id) as order_user_count -- Number of ordering users
            ,count(case    when order_status in ('paid','shipped','completed') then order_id end) as paid_order_count -- Number of paid orders
            ,count(distinct case    when order_status in ('paid','shipped','completed') then consumer_id end) as paid_user_count -- Number of paying users
            ,sum(order_amount) as gmv -- Gross Merchandise Volume
            ,sum(total_amount) as total_amount -- Total amount
            ,sum(discount_amount) as discount_amount -- Discount amount
            ,sum(shipping_fee) as shipping_fee -- Shipping fee
            ,sum(item_quantity) as item_quantity -- Item quantity
            ,date_key as ds -- Date partition
    from    ctlive_dwd.dwd_ctlive_trd_order_create_di
    group by date_key
             ,anchor_id
             ,anchor_name
             ,agency_id
             ,agency_name
             ,province_name
             ,user_type
             ,seller_id
             ,seller_name
             ,shop_id
             ,item_id
             ,leaf_cate_id
             ,brand_id
             ,brand_name;
    
    -- Part 3: Data validation
    -- 1. Query data from a specific partition in the daily aggregated livestreaming business transaction table.
    select * from ctlive_dws.dws_ctlive_trd_order_create_nd where ds='20250618' limit 100;

    image.png

  4. In the right-side toolbar, click Run Configuration and configure the compute resources and resource group you prepared earlier.

    image

  5. At the top of the node editor, click Run to run the task.

    We recommend that you run only the final SELECT statement in the code.
  6. After the task is complete, you can view the execution results and order summary wide table data in the query results area.

    image.png

5. Analyze order sales distribution by province

  1. Return to the workflow page for this tutorial and click Serverless StarRocks: Order Sales by Province to create a StarRocks SQL node. image.png

  2. Click Confirm. The node is created successfully.

    image.png

  3. Hover over the node and click Open Node to go to the configuration page. The system populates the editor with the following code.

    EMR Serverless StarRocks SQL code

    -- Order sales distribution by province table
    -- Purpose: Serves as an ADS (Application Data Service) layer table to store core order metrics (total orders, user count, payment rate, GMV, etc.) aggregated by province.
    
    -- Part 1: Create the target table
    -- Create a non-partitioned table. Fields include province, total orders, number of ordering users, number of paying users, payment conversion rate, total GMV, actual paid amount, and average order value.
    CREATE TABLE dlf_catalog.ctlive_ads.ads_ctlive_trd_order_create_province_nd (
        province_name        STRING COMMENT 'Province name',
        total_orders         BIGINT COMMENT 'Total number of orders',
        total_users          BIGINT COMMENT 'Number of ordering users',
        paid_users           BIGINT COMMENT 'Number of paying users',
        conversion_rate      DOUBLE COMMENT 'Payment conversion rate (paying users / ordering users)',
        gmv                  DOUBLE COMMENT 'Total GMV',
        paid_gmv             DOUBLE COMMENT 'Actual paid amount (GMV)',
        avg_order_value      DOUBLE COMMENT 'Average order value (paid GMV / paying users)'
    ) COMMENT 'ADS order creation distribution by province';
    
    -- Part 2: Insert cleaned data into the target table
    -- Use INSERT OVERWRITE to write the cleaned data to the target table.
    INSERT OVERWRITE dlf_catalog.ctlive_ads.ads_ctlive_trd_order_create_province_nd
    -- Aggregate core metrics from livestreaming e-commerce order data by province, covering total orders, user count, conversion rate, GMV, etc. The results are ordered by paid GMV in descending order.
    SELECT
        province_name -- Province name
    ,
        SUM(order_count) AS total_orders -- Total number of orders
    ,
        SUM(order_user_count) AS total_users -- Total number of users
    ,
        SUM(paid_user_count) AS paid_users -- Number of paying users
    ,
        IF(
            SUM(order_user_count) > 0,
            ROUND(
                CAST(SUM(paid_user_count) AS double) / SUM(order_user_count),
                4
            ),
            0.0
        ) AS conversion_rate -- Conversion rate
    ,
        ROUND(SUM(gmv)) AS gmv -- Total GMV
    ,
        ROUND(SUM(total_amount)) AS paid_gmv -- Paid GMV
    ,
        IF(
            SUM(paid_user_count) > 0,
            ROUND(SUM(total_amount) / SUM(paid_user_count), 2),
            0.0
        ) AS avg_order_value -- Average order value
    FROM
        dlf_catalog.ctlive_dws.dws_ctlive_trd_order_create_nd
    WHERE
        province_name IS NOT NULL
    GROUP BY
        province_name
    ORDER BY
        paid_gmv DESC;
    
    
    -- Part 3: Data query
    -- 1. Extract core metrics from the pre-aggregated province-level e-commerce transaction table and display the transaction performance of the top 10 provinces, ordered by paid GMV in descending order.
    SELECT 
        province_name AS 'Province Name',
        total_orders AS 'Total Orders',
        total_users AS 'Total Users',
        paid_users AS 'Paid Users',
        conversion_rate AS 'Payment Conversion Rate',
        gmv AS 'Total GMV',
        paid_gmv AS 'Paid GMV',
        avg_order_value AS 'Average Order Value'
    FROM
        dlf_catalog.ctlive_ads.ads_ctlive_trd_order_create_province_nd
    ORDER BY
        `Paid GMV` DESC;

    image.png

  4. In the right-side toolbar, click Run Configuration and configure the compute resources and resource group you prepared earlier.

    image

  5. At the top of the node editor, click Run to run the task.

  6. After the task is complete, you can view the execution results and order detail data in the query results area.image.png

6. Build livestreaming product search

  1. Return to the workflow page for this tutorial and click Hologres: Build Livestreaming Product Search to create a Hologres SQL node.image.png

  2. Click Confirm. The node is created successfully.

  3. Hover over the node and click Open Node to go to the configuration page. The system automatically populates the editor with the following code.

    Hologres SQL code

    -- Build livestreaming product dimension search table
    CREATE TABLE IF NOT EXISTS tbl(id int);
    
    -- Load data from DLF into Hologres and create a vector index based on product description embeddings.
    CREATE DYNAMIC TABLE IF NOT EXISTS multimodal_search(
      CHECK(array_ndims(item_description_embeddings) = 1 AND array_length(item_description_embeddings, 1) =1024)
    )
    WITH (
        vectors = '{
          "item_description_embeddings": {
            "algorithm": "HGraph",
            "distance_method": "Cosine",
            "builder_params": {
            "base_quantization_type": "sq8_uniform",
            "max_degree": 64,
            "ef_construction": 400,
            "precise_quantization_type": "fp32",
            "use_reorder": true
            }
          }}',
        auto_refresh_mode='full',
        freshness='1 minutes'
    ) AS
    SELECT 
        item_id,
        item_name,
        item_subtitle,
        item_description,
        ai_embed ('qwen3_embedding_06b', item_description) AS item_description_embeddings,
        item_image_url
    FROM e_commerceanalytics_dlf.ctlive_ods.ods_ctlive_itm;
    
    -- Create a full-text index on the product description.
    CREATE INDEX IF NOT EXISTS idx_full_text_on_multimodal_search ON multimodal_search USING FULLTEXT (item_description);
    
    -- View refresh history.
    SELECT *
    FROM hologres.hg_dynamic_table_refresh_history
    WHERE dynamic_table_name ='multimodal_search'
    ORDER BY refresh_start DESC;
    
    -- View a sample of the data.
    SELECT * FROM multimodal_search LIMIT 10;
    
    -- Use the full-text index to search for products whose descriptions contain 'high-quality laptop'. Retrieve the top 50 products with the highest BM25 similarity score.
    SELECT
        *,
        text_search (item_description, 'high-quality laptop') as bm25_score
    FROM
        multimodal_search
    WHERE
        text_search (item_description, 'high-quality laptop') > 0
    ORDER BY
        text_search (item_description, 'high-quality laptop') DESC
    LIMIT 50;
    
    -- Use the vector index to search for products whose descriptions contain 'high-quality laptop'. Retrieve the top 50 products with the highest vector similarity.
    SELECT
        *,
        approx_cosine_distance (item_description_embeddings, ai_embed ('qwen3_embedding_06b', 'high-quality laptop')) as score
    FROM
        multimodal_search
    WHERE
        approx_cosine_distance (item_description_embeddings,ai_embed ('qwen3_embedding_06b', 'high-quality laptop')) > 0
    ORDER BY
        approx_cosine_distance (item_description_embeddings, ai_embed ('qwen3_embedding_06b', 'high-quality laptop')) DESC
    LIMIT 50;
    
    -- Use both vector index search and full-text search. Search for products whose descriptions contain 'high-quality laptop'. Retrieve 50 results from each, then use the Qwen3-4b model for reranking to select the final 50 from the 100 candidates.
    WITH
    -- Vector recall
    embedding_recall AS (
        SELECT
            item_id,
            item_name,
            item_subtitle,
            item_description,
            item_image_url
        FROM
            multimodal_search
        WHERE
            approx_cosine_distance (item_description_embeddings, ai_embed ('qwen3_embedding_06b', 'high-quality laptop')) > 0
        ORDER BY
            approx_cosine_distance (item_description_embeddings, ai_embed ('qwen3_embedding_06b', 'high-quality laptop')) DESC
        LIMIT 50
    ),
    -- Full-text search recall (you can delete this CTE if not needed)
    text_search_recall AS (
        SELECT
            item_id,
            item_name,
            item_subtitle,
            item_description,
            item_image_url
        FROM
            multimodal_search
        WHERE
            text_search (item_description, 'high-quality laptop') > 0
        ORDER BY
            text_search (item_description, 'high-quality laptop') DESC
        LIMIT 50
    ),
    -- Merge and deduplicate
    union_recall AS (
        SELECT
            item_id,
            item_name,
            item_subtitle,
            item_description,
            item_image_url
        FROM
            embedding_recall
        UNION
        SELECT
            item_id,
            item_name,
            item_subtitle,
            item_description,
            item_image_url
        FROM
            text_search_recall
    )
    SELECT
        *,
        ai_rank ('qwen3_4b', 'high-quality laptop', item_description) AS score
    FROM
        union_recall
    ORDER BY
        score DESC
    LIMIT 50;

    image

  4. In the right-side toolbar, click Run Configuration and configure the compute resources and resource group you prepared earlier.

    image

  5. At the top of the node editor, click Run to run the task.

  6. After the task is complete, you can view the execution results and product search results in the query results area.

    image.png

DataWorks Agent: Automatically build a data workflow

  1. Return to the tutorial page and click DataWorks Agent: Automatically Build Data Workflow.

    image

  2. The system automatically enters a prompt and uploads a requirements document:

    1. User prompt:

      Based on the content of the requirements document, build the ADS layer for "livestreaming order data analysis".
      Requirements:
      1. You must follow data warehousing standards. Build the tables and code for the DWD and DWS layers, and use a workflow to manage all development tasks.
      2. The ADS layer tables must be implemented using StarRocks and Hologres. The other tables must be implemented using Spark SQL and MaxCompute.
      3. First, output a plan. Do not modify or create any files. Wait for user confirmation before executing the plan.
    2. Requirements document content: livestreaming_order_analysis_requirements.md.

      # Data Analysis Requirements Document
      ## Background
      **Existing tables:**
      - **ods_ctlive_trd_order_create** - Transaction order creation table
      - **ods_ctlive_itm_df** - Product dimension table
      
      ## Requirements
      
      #### 1.1 Product sales analysis
      - **Top-selling product analysis**: Analyze which products have the highest sales volume and largest sales revenue.
      
      ## Notes
      - **ADS layer tables**: Implement using StarRocks and Hologres.
      - **Other tables**: Implement using Spark SQL and MaxCompute.

      image.png

  3. In the bottom-right dialog box, click Send. The Agent begins to analyze the requirements.

  4. DataWorks Agent processes the input and previous conversation history to accurately understand the user's intent and generates a data development execution plan. During planning, the Agent intelligently senses the data context, automatically searches for relevant metadata, and references big data best practices and your custom enterprise knowledge base.

    To better display the Agent's output, manually widen the central panel.

    image.png

  5. In the DataWorks Copilot prompt input box, type "continue" and send the message.

    image

  6. The Agent then uses tools in the DataWorks MCP Server to generate the required data processing nodes and the corresponding SQL code based on the requirements.

    image

  7. The Agent automatically orchestrates all data processing nodes into a workflow.

    For the best viewing experience, manually format the workflow and center it on the page.

    image.png

  8. After the workflow is generated, the Agent configures the task scheduling as required. Click Confirm to apply the workflow Scheduling Configuration.

    image

    image

  9. The Agent begins publishing the workflow. Confirm the deployment method and click Confirm.

    To ensure an optimal experience on small screens, close the scheduling configuration drawer, raise the deployment method selection box, and center the workflow on the page.

    image.png

  10. The Agent continues to publish the workflow.

    image

  11. The deployment is now complete. DataWorks Agent has automatically fulfilled an ETL development request.image.png

Notebook: Interactive analysis

  1. Return to the tutorial page and click Notebook: Interactive Analysis.

    image

  2. Click Create Notebook: E-commerce Livestreaming Promotion Data Analysis. The Add Node dialog box appears. Click Confirm.

    image.png

  3. Hover over the node and click Open Node to open the Notebook editor.

    image.png

  4. On the Notebook editor page, browse the "E-commerce Livestreaming Promotion War Room" section to see how different compute resources and SQL are used to analyze core data metrics.

    image.png

  5. On the Notebook editor page, browse the Predicting Interest Tags for E-commerce Livestreaming Users section to see how PySpark is used for data processing and machine learning model training.

    image.png

  6. In the "spark.emr.serverless.environmentId": "[environmentId]" section, replace [environmentId] with the sample value.

    image.png

  7. At the top of the Data Development page, select the personal development environment instance e_commerce_env.

  8. On the Notebook editor page, click Run All. Wait for all cells to run in sequence and then view the output.

    image.png

ChatBI: Q&A-based analysis

  1. Return to the tutorial page and click ChatBI: Q&A-based analysis.

    image

  2. This action redirects you to the ChatBI page and creates a new session.

    image

  3. ChatBI provides a list of recommended questions based on the "E-commerce Livestreaming Dataset":

    • What is the daily trend of overall GMV and order volume in 2025?

    • For the top 10 anchors with the highest GMV in 2025, what is their average order value (GMV / number of paying users) and conversion rate (number of paid orders / number of total orders)?

    • What is the distribution of GMV and the number of ordering users by province in 2025?

    • What is the correlation between the number of anchors and GMV for different merchants in 2025?

    • How many users placed orders within different discount amount ranges in 2025?

    • What are the differences in ordering behavior and spending power among different user types in 2025?

    • What is the number of daily active users (DAU) who entered at least one livestreaming room each day in 2025?

    • On June 18, 2025, what is the comparison between the number of livestreaming rooms that received likes and those that did not?