Best practices: Build a multi-mode analytics data dashboard using Hologres Dynamic Tables

Environment setup

• Set up a Hologres environment.

  • You must activate Hologres, create a Hologres database, and connect to it. Later, run commands in the Hologres database to read data.

    Note

    You can apply for a free Hologres resource plan to try core steps in this tutorial at no cost. For details about free resource plans and how to apply, see Free trial for new users.

  • We recommend activating Hologres Serverless resources. Submit refresh tasks to a serverless cluster to isolate them from each other. For details, see Serverless Computing.

• Set up a DataWorks development environment.

  • This example uses DataWorks to create Dynamic Tables and develop SQL. Use the new DataStudio interface for data development. Make sure the Join Data Studio public preview parameter is enabled. For instructions, see Create a workspace.

  • You must attach the Hologres data source created earlier to your DataWorks development environment. For instructions, see New data development: Attach Hologres compute resources.

• (Optional) Set up a dashboard tool. This example uses DataV.

  This example uses DataV to show the final dashboard.

Data preparation (Prepare the Hologres base table)

Create the following tables and import data from the MaxCompute public dataset (Digital Commerce Dataset) into Hologres internal tables. Then create Dynamic Tables for data transformation.

• public.dim_ad_feature

• public.behavior_log

• public.raw_sample

• public.dim_user_profile

The DDL statements to create internal tables and the SQL statements to import data are as follows.

-- Create a foreign table
CREATE SCHEMA foreign_holo;
IMPORT FOREIGN SCHEMA "BIGDATA_PUBLIC_DATASET#commerce" LIMIT to
(adv_raw_sample,adv_ad_feature,user_profile,behavior_log) 
  FROM server odps_server INTO foreign_holo options(if_table_exist 'error');


-- Create the dimension table for ad features
DROP TABLE IF EXISTS public.dim_ad_feature;
BEGIN;
CREATE TABLE public.dim_ad_feature (
   adgroup_id bigint NOT NULL,
    cate_id bigint,
    campaign_id bigint,
    customer_id bigint,
    brand bigint,
    price double precision
    ,PRIMARY KEY (adgroup_id)
) WITH (
orientation = 'column',
storage_format = 'orc',
distribution_key = 'adgroup_id'
);
END;
-- Import dimension table data
INSERT INTO dim_ad_feature SELECT 
   try_cast(adgroup_id as bigint) as adgroup_id,
   try_cast(cate_id as bigint) as cate_id,
   try_cast(campaign_id as bigint) as campaign_id,
   try_cast(customer_id as bigint) as customer_id,
   try_cast(brand as bigint) as brand,
    price::double precision
 FROM foreign_holo.adv_ad_feature;

-- Create the user behavior table
DROP TABLE IF EXISTS public.behavior_log;
BEGIN;
CREATE TABLE public.behavior_log (
    user_id bigint,
    ds text,
    "time" text,
    behavior_type text,
    cate bigint,
    brand bigint
) WITH (
orientation = 'column',
storage_format = 'orc',
binlog_level = 'replica',
binlog_ttl = '2592000',
bitmap_columns = 'ds,"time",behavior_type',
dictionary_encoding_columns = 'ds:auto,"time":auto,behavior_type:auto',
distribution_key = 'cate'
);
END;
-- Import data
INSERT INTO behavior_log SELECT 
    "user"::bigint,
    date(to_timestamp(time_stamp::bigint)),
    to_timestamp(time_stamp::bigint),
    btag::text,
    cate::bigint,
    brand::bigint
 FROM foreign_holo.behavior_log 
 WHERE date(to_timestamp(time_stamp::bigint))='2017-05-13' ;


-- Create the ad detail table
DROP TABLE IF EXISTS public.raw_sample;
BEGIN;
CREATE TABLE public.raw_sample (
    user_id bigint,
    adgroup_id bigint,
    ds text,
    "time" text,
    pid text,
    noclk integer,
    clk integer
) WITH (
orientation = 'column',
storage_format = 'orc',
binlog_level = 'replica',
binlog_ttl = '2592000',
bitmap_columns = 'ds,"time",pid',
dictionary_encoding_columns = 'ds:auto,"time":auto,pid:auto',
distribution_key = 'user_id'
);
END;
-- Import data
INSERT INTO raw_sample SELECT 
    user_id::bigint,
    adgroup_id::bigint,
    date(to_timestamp(time_stamp::bigint)),
    to_timestamp(time_stamp::bigint),
    pid::text,
    noclk::integer,
    clk::integer
 FROM foreign_holo.adv_raw_sample;


-- Create the user profile dimension table
DROP TABLE IF EXISTS public.dim_user_profile;
BEGIN;
CREATE TABLE public.dim_user_profile (
    user_id bigint NOT NULL,
    cms_seg_id bigint,
    cms_group_id bigint,
    final_gender_code integer,
    age_level integer,
    pvalue_level integer,
    shopping_level integer,
    occupation integer,
    new_user_class_level integer
    ,PRIMARY KEY (user_id)
) WITH (
orientation = 'column',
storage_format = 'orc',
distribution_key = 'user_id'
);
END;
-- Import data
INSERT INTO dim_user_profile SELECT 
    try_cast(userid as bigint),
    try_cast(cms_segid as bigint),
    try_cast(cms_group_id as bigint),
    try_cast(final_gender_code as integer),
    try_cast(age_level as integer),
    try_cast(pvalue_level as integer),
    try_cast(shopping_level as integer),
    try_cast(occupation as integer),
    try_cast(new_user_class_level as integer)
FROM foreign_holo.user_profile WHERE userid != 'userid';

Create a Dynamic Table

1. Go to the Workspaces page in the DataWorks console. In the top navigation bar, select a desired region. Find the desired workspace and choose Shortcuts > Data Studio in the Actions column.

2. Click the icon on the left and, on the Data Catalog page, select Hologres.

3. Click , select the DataWorks Data Source tab, and click Add As Data Catalog in the Actions column of the bound target data source.

   Note

   For details about binding data sources, see New data development: Attach Hologres compute resources.

4. Click your target schema under the Hologres directory on the left. Select the Dynamic Tables tab. Click Create dynamic table.

5. On the Create dynamic table page, enter table information.

   1. Enter a table name. For example, commerce_behavior_summary_incrmental. If you use a custom name, update the SQL query below accordingly.

   2. In the SQL editor, enter the following SQL.

      SELECT 
        behavior_log.ds, 
        substring(behavior_log.time, 12, 2) as hour, 
        behavior_log.behavior_type, 
        COUNT(*) AS total_behavior_count, 
        COUNT(DISTINCT behavior_log.user_id) AS uv_count 
      FROM 
        behavior_log 
        JOIN dim_user_profile FOR SYSTEM_TIME AS OF PROCTIME() AS user_profile ON behavior_log.user_id = user_profile.user_id 
      WHERE
        user_profile.shopping_level > 1
      GROUP BY 
        behavior_log.ds, 
        hour, 
        behavior_log.behavior_type;

   3. Click Precompile to check syntax correctness.

6. On the right, select Refresh policy and configure parameters.

   Parameter Name	Description
   Refresh mode	Select Incremental.
   Start time for data refresh	Select Start immediately after table creation.
   Refresh interval	1 minute.
   Hologres compute resource	Select Serverless resource.
   Hologres compute resource specification	32 cores.

   

7. After you finish, submit and publish. After successful publishing, the incremental refresh task runs periodically based on the refresh interval to perform incremental computation on your data.

Query data

After the incremental refresh task runs, you can query incremental changes. Example SQL queries follow.

• Hourly page views.

  -- Hourly page views
  SELECT hour, total_behavior_count, uv_count FROM commerce_behavior_summary_incrmental WHERE ds = '2017-05-13' AND behavior_type = 'pv' ORDER BY hour;

• Hourly purchases.

  -- Hourly purchases
  SELECT hour, total_behavior_count, uv_count FROM commerce_behavior_summary_incrmental WHERE ds = '2017-05-13' AND behavior_type = 'buy' ORDER BY hour;

Incremental dashboard display

After the incremental refresh task starts, connect your Dynamic Table directly to an application to query data. This practice connects directly to DataV for real-time dashboard display. Follow these steps.

1. Create a Hologres data source (optional).

   Create your Hologres instance and database as a DataV data source. For details, see DataV.

2. Create a visualization application.

   1. Log on to the DataV console.

   2. On the workspace page, click Create PC dashboard.

   3. Select an existing template or create a custom one to visualize your data.

This example shows cumulative hourly behavior data. Use a column chart in DataV to visualize incremental data changes clearly.

Scenario 2: Historical ad data analysis and data backfill

Businesses often need to perform association analysis on historical data. Use the full refresh mode of Dynamic Tables to support this. Full refresh processes all data at once. With Dynamic Tables, you can process and compute historical data quickly and then analyze it. This practice uses full refresh to compute the top 10 ad click rankings for the past 7 days. It uses the DataWorks UI to create a Dynamic Table with full refresh.

-- Top ad clicks for the past 7 days
SELECT 'cate_id_' || cate_id, sum(total_clicks) AS sum_total_clicks FROM commerce_top_clicks_cate_full WHERE ds <= '2017-05-13' AND ds >= TO_CHAR(ds::date - INTERVAL '6 days', 'YYYY-MM-DD') GROUP BY cate_id ORDER BY sum_total_clicks DESC limit 10;

Appendix: Dynamic Tables

-- Cumulative incremental transaction analysis with incremental refresh
DROP TABLE IF EXISTS commerce_behavior_summary_incrmental;
CREATE DYNAMIC TABLE commerce_behavior_summary_incrmental WITH (
    auto_refresh_enable='true',
    refresh_mode = incremental, 
    incremental_auto_refresh_schd_start_time='immediate',
    incremental_auto_refresh_interval = '1 minutes',
    incremental_guc_hg_computing_resource = 'serverless',
    incremental_guc_hg_experimental_serverless_computing_required_cores='32'
)
 AS 
SELECT 
  behavior_log.ds, 
  substring(behavior_log.time, 12, 2) as hour, 
  behavior_log.behavior_type, 
  COUNT(*) AS total_behavior_count, 
  COUNT(DISTINCT behavior_log.user_id) AS uv_count 
FROM 
  behavior_log 
  JOIN dim_user_profile FOR SYSTEM_TIME AS OF PROCTIME() AS user_profile ON behavior_log.user_id = user_profile.user_id 
WHERE
  user_profile.shopping_level > 1
GROUP BY 
  behavior_log.ds, 
  hour, 
  behavior_log.behavior_type;

-- Historical ad data analysis with full refresh
DROP TABLE IF EXISTS commerce_top_clicks_cate_full;
CREATE DYNAMIC TABLE commerce_top_clicks_cate_full    WITH (
    auto_refresh_enable='true',
    refresh_mode = full, 
    full_auto_refresh_schd_start_time='immediate',
    full_auto_refresh_interval = '24 hours',
    full_guc_hg_computing_resource = 'serverless'
)
 AS 
SELECT 
  raw_sample.ds, 
  ad_feature.cate_id, 
  COUNT(*) AS total_browses, 
  COUNT(DISTINCT raw_sample.user_id) AS total_unique_visitors, 
  SUM(
    CASE WHEN raw_sample.clk = '1' THEN 1 ELSE 0 END
  ) AS total_clicks 
FROM 
  raw_sample 
  JOIN dim_ad_feature FOR SYSTEM_TIME AS OF PROCTIME() AS ad_feature ON raw_sample.adgroup_id = ad_feature.adgroup_id 
GROUP BY 
  raw_sample.ds, 
  ad_feature.cate_id;