MaxCompute:Public datasets

Project name

BIGDATA_PUBLIC_DATASET

Schema name

github_events

Available regions

China (Hangzhou), China (Shanghai), China (Beijing), China (Zhangjiakou), China (Ulanqab), China (Shenzhen), China (Chengdu)

Tables and descriptions

Developers on GitHub generate a large volume of events when working on open-source projects. GitHub records details for each event, such as the event type, developer, and repository, and publishes these as public events. Examples include starring a repository and committing code. For a list of event types, see GitHub Events.

MaxCompute processes the public event data from GH Archive offline to generate the following tables:

• dwd_github_events_odps (fact table for GitHub public event data)

• dws_overview_by_repo_month (aggregated table for monthly metrics of GitHub public events)

Update cycle

• dwd_github_events_odps: Updated hourly (T+1).

• dws_overview_by_repo_month: Updated daily (T+1).

Query table structure

-- Enable session-level schema syntax.
SET odps.namespace.schema=true; 
-- Query the structure of the dwd_github_events_odps table. To query other tables, replace the schema and table names.
DESC bigdata_public_dataset.github_events.dwd_github_events_odps;

Query example

-- Enable session-level schema syntax.
SET odps.namespace.schema=true; 
-- This example counts the top-starred projects over the past year. Note that this query does not account for unstarring events.
SELECT
    repo_id,
    repo_name,
    COUNT(actor_login) total
FROM
    bigdata_public_dataset.github_events.dwd_github_events_odps
WHERE
    ds>=date_add(getdate(), -365)
    AND type = 'WatchEvent'
GROUP BY
    repo_id,
    repo_name
ORDER BY
    total DESC
LIMIT 10;

For more information and query examples, see GitHub Public Event Data.

Project name

BIGDATA_PUBLIC_DATASET

Schema name

national_data

Available regions

China (Hangzhou), China (Shanghai), China (Beijing), China (Zhangjiakou), China (Ulanqab), China (Shenzhen), China (Chengdu)

Tables and descriptions

• annual_gdp_by_province (annual GDP by province in China)

• annual_gdp_by_country (annual GDP by country worldwide)

Update cycle

This is a static dataset and is not updated.

Query table structure

-- Enable session-level schema syntax.
SET odps.namespace.schema=true; 
-- Query the structure of the annual_gdp_by_province table. To query other tables, replace the schema and table names.
DESC bigdata_public_dataset.national_data.annual_gdp_by_province;

Query example

-- Enable session-level schema syntax.
SET odps.namespace.schema=true; 
-- This example shows the GDP trend for Beijing over the past 20 years.
SELECT
    region,
    gdp,
    year
FROM
    bigdata_public_dataset.national_data.annual_gdp_by_province
WHERE
    region='Beijing'
ORDER BY
    year ASC
LIMIT 20;

Project name

BIGDATA_PUBLIC_DATASET

Schema name

tpcds_10g, tpcds_100g, tpcds_1t, tpcds_10t

Available regions

China (Hangzhou), China (Shanghai), China (Beijing), China (Zhangjiakou), China (Ulanqab), China (Shenzhen), China (Chengdu), China (Hong Kong), Japan (Tokyo), Singapore, Malaysia (Kuala Lumpur), Indonesia (Jakarta), US (Virginia), US (Silicon Valley), UK (London), Germany (Frankfurt), UAE (Dubai), China (Shanghai) Finance, China (Beijing) Finance, China (Beijing) Government Cloud, China (Shenzhen) Finance

Tables and descriptions

The TPC-DS model simulates the sales system of a large nationwide retail chain with three sales channels: store (physical stores), web (online stores), and catalog (phone orders). For each channel, a pair of tables records sales and returns. The dataset also includes dimension tables for items, promotions, customers, and other information.

• call_center (call center data)

• catalog_page (product catalog)

• catalog_returns (product return records from the catalog channel)

• catalog_sales (product sales records from the catalog channel)

• customer (customer data)

• customer_address (customer address data)

• customer_demographics (customer credit demographics)

• date_dim (date dimension)

• household_demographics (household credit demographics)

• income_band (income bands)

• inventory (inventory data)

• item (item data)

• promotion (product promotions)

• reason (return reasons)

• ship_mode (shipping modes)

• store (store data)

• store_returns (product return records from the store channel)

• store_sales (product sales records from the store channel)

• time_dim (time dimension)

• warehouse (warehouse data)

• web_page (web page data)

• web_returns (product return records from the web channel)

• web_sales (product sales records from the web channel)

• web_site (website data)

Update cycle

This is a static dataset and is not updated.

Query table structure

-- Enable session-level schema syntax.
SET odps.namespace.schema=TRUE; 
-- Query the structure of the call_center table in the tpcds_10g schema. To query tables from other dataset sizes, replace the schema and table names.
DESC bigdata_public_dataset.tpcds_10g.call_center;

Query example

SET odps.namespace.schema=TRUE; 
SELECT dt.d_year ,
       item.i_brand_id brand_id ,
       item.i_brand brand ,
       SUM(ss_sales_price) sum_agg
FROM bigdata_public_dataset.tpcds_10g.date_dim dt ,
     bigdata_public_dataset.tpcds_10g.store_sales ,
     bigdata_public_dataset.tpcds_10g.item
WHERE dt.d_date_sk = store_sales.ss_sold_date_sk
  AND store_sales.ss_item_sk = item.i_item_sk
  AND item.i_manufact_id = 190
  AND dt.d_moy = 12
GROUP BY dt.d_year ,
         item.i_brand ,
         item.i_brand_id
ORDER BY dt.d_year,
         sum_agg DESC,
         brand_id LIMIT 100;

For query examples on datasets of different sizes, see TPC-DS Data.

For more details about the data, see the official TPC Benchmark DS specification.

Project name

BIGDATA_PUBLIC_DATASET

Schema name

tpch_10g, tpch_100g, tpch_1t, tpch_10t

Available regions

China (Hangzhou), China (Shanghai), China (Beijing), China (Zhangjiakou), China (Ulanqab), China (Shenzhen), China (Chengdu), China (Hong Kong), Japan (Tokyo), Singapore, Malaysia (Kuala Lumpur), Indonesia (Jakarta), US (Virginia), US (Silicon Valley), UK (London), Germany (Frankfurt), UAE (Dubai), China (Shanghai) Finance, China (Beijing) Finance, China (Beijing) Government Cloud, China (Shenzhen) Finance

Tables and descriptions

TPC-H is a benchmark for evaluating online analytical processing (OLAP) systems. It simulates transactions between suppliers and customers, and includes data such as orders, products, and customers.

• customer (customer data)

• lineitem (line item data)

• nation (nation data)

• orders (order data)

• part (part data)

• partsupp (part supplier data)

• region (region data)

• supplier (supplier data)

Update cycle

This is a static dataset and is not updated.

Query table structure

-- Enable session-level schema syntax.
SET odps.namespace.schema=TRUE; 
-- Query the structure of the lineitem table in the tpch_10g schema. To query tables from other dataset sizes, replace the schema and table names.
DESC bigdata_public_dataset.tpch_10g.lineitem;

Query example

SET odps.namespace.schema=TRUE; 
SET odps.sql.validate.orderby.limit=FALSE;
SET odps.sql.hive.compatible=TRUE;
SELECT l_returnflag,
       l_linestatus,
       sum(l_quantity) AS sum_qty,
       sum(l_extendedprice) AS sum_base_price,
       sum(l_extendedprice * (1 - l_discount)) AS sum_disc_price,
       sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) AS sum_charge,
       avg(l_quantity) AS avg_qty,
       avg(l_extendedprice) AS avg_price,
       avg(l_discount) AS avg_disc,
       count(*) AS count_order
FROM bigdata_public_dataset.tpch_10g.lineitem
WHERE l_shipdate <= date'1998-12-01' - interval '90' DAY
GROUP BY l_returnflag,
         l_linestatus
ORDER BY l_returnflag,
         l_linestatus;

For more information and query examples, see the official TPC Benchmark H specification.

Project name

BIGDATA_PUBLIC_DATASET

Schema name

tpcxbb_10g, tpcxbb_100g, tpcxbb_1t, tpcxbb_10t

Available regions

China (Hangzhou), China (Shanghai), China (Beijing), China (Zhangjiakou), China (Ulanqab), China (Shenzhen), China (Chengdu), China (Hong Kong), Japan (Tokyo), Singapore, Malaysia (Kuala Lumpur), Indonesia (Jakarta), US (Virginia), US (Silicon Valley), UK (London), Germany (Frankfurt), UAE (Dubai), China (Shanghai) Finance, China (Beijing) Finance, China (Beijing) Government Cloud, China (Shenzhen) Finance

Tables and descriptions

TPCx-BB is a big data benchmark that simulates an online retail scenario. The dataset includes sales and return records, as well as item and promotion information. The tables are as follows:

• customer (customer data)

• customer_address (customer address data)

• customer_demographics (customer credit demographics)

• date_dim (date dimension)

• household_demographics (household credit demographics)

• income_band (income bands)

• inventory (inventory data)

• item (item data)

• item_marketprices (Competitor prices for the item)

• product_reviews (product reviews)

• promotion (product promotions)

• reason (return reasons)

• ship_mode (shipping modes)

• store (store data)

• store_returns (product return records from the store channel)

• store_sales (product sales records from the store channel)

• time_dim (time dimension)

• warehouse (warehouse data)

• web_clickstreams (web clickstreams)

• web_page (web page data)

• web_returns (product return records from the web channel)

• web_sales (product sales records from the web channel)

• web_site (website data)

Update cycle

This is a static dataset and is not updated.

Query table structure

-- Enable session-level schema syntax.
SET odps.namespace.schema=TRUE; 
-- Query the structure of the web_sales table in the tpcxbb_10g schema. To query tables from other dataset sizes, replace the schema and table names.
DESC bigdata_public_dataset.tpcxbb_10g.web_sales;

Query example

SET odps.namespace.schema=TRUE; 
SELECT * FROM bigdata_public_dataset.tpcxbb_10g.web_sales limit 100;

For more information and query examples, see the official TPCx-BB specification.

Project name

BIGDATA_PUBLIC_DATASET

Schema name

commerce

Available regions

China (Hangzhou), China (Shanghai), China (Beijing), China (Zhangjiakou), China (Ulanqab), China (Shenzhen), China (Chengdu)

Tables and descriptions

• adv_raw_sample (a raw sample of display ad impression and click logs from over one million randomly sampled Taobao users over an eight-day period.)

• adv_ad_feature (basic information about some of the ads in the raw_sample table)

• user_profile (basic information about all users in the raw_sample table)

• behavior_log (shopping behavior of all users in raw_sample for 22 days, including browsing, adding to cart, favoriting, and purchasing)

Update cycle

This is a static dataset and is not updated.

Query table structure

-- Enable session-level schema syntax.
SET odps.namespace.schema=TRUE; 
-- Query the structure of the behavior_log table. To query other tables, replace the table name.
DESC bigdata_public_dataset.commerce.behavior_log;

Query example

-- Enable session-level schema syntax.
SET odps.namespace.schema=TRUE; 
-- This example uses the behavior_log table to count the top three best-selling product category IDs over a 22-day period.
SELECT cate,
       count(btag) sales
FROM behavior_log
WHERE btag='buy'
GROUP BY cate
ORDER BY sales DESC LIMIT 3;

Project name

BIGDATA_PUBLIC_DATASET

Schema name

life_service

Available regions

China (Hangzhou), China (Shanghai), China (Beijing), China (Zhangjiakou), China (Ulanqab), China (Shenzhen), China (Chengdu)

Tables and descriptions

• movie_basic_info (basic movie information)

• movie_box (basic box office information)

• areacode_basic_info_2020 (2020 administrative and urban-rural division codes)

• phoneno_basic_info_2020 (2020 mobile phone number attribution)

Update cycle

• movie_basic_info, movie_box: Data is static, provided in date-based partitions.

• areacode_basic_info_2020, phoneno_basic_info_2020: These are static datasets and are not updated.

Query table structure

-- Enable session-level schema syntax.
SET odps.namespace.schema=TRUE; 
-- Query the structure of the movie_box table. To query other tables, replace the table name.
DESC bigdata_public_dataset.life_service.movie_box;

Query example

-- Enable session-level schema syntax.
SET odps.namespace.schema=TRUE;
-- This example queries the top ten movies by box office rank on January 14, 2017.
SELECT moviename
FROM bigdata_public_dataset.life_service.movie_box
WHERE ds ='20170114'
ORDER BY rank ASC LIMIT 10;

Project name

BIGDATA_PUBLIC_DATASET

Schema name

finance

Available regions

China (Hangzhou), China (Shanghai), China (Beijing), China (Zhangjiakou), China (Ulanqab), China (Shenzhen), China (Chengdu)

Tables and descriptions

• ods_enterprise_share_basic (basic stock information)

• ods_enterprise_share_quarter_cashflow (quarterly cash flow statement)

• ods_enterprise_share_quarter_growth (quarterly business growth data)

• ods_enterprise_share_quarter_operation (quarterly financial turnover data)

• ods_enterprise_share_quarter_profit (quarterly profit statement)

• ods_enterprise_share_quarter_report (quarterly report)

• ods_enterprise_share_trade_h (stock prices)

Update cycle

Data is static, provided in date-based partitions.

Query table structure

-- Enable session-level schema syntax.
SET odps.namespace.schema=TRUE; 
-- Query the structure of the ods_enterprise_share_basic table. To query other tables, replace the table name.
DESC bigdata_public_dataset.finance.ods_enterprise_share_basic;

Query example

-- Enable session-level schema syntax.
SET odps.namespace.schema=TRUE;
-- This example queries basic stock information for January 14, 2017.
SELECT *
FROM bigdata_public_dataset.finance.ods_enterprise_share_basic
WHERE ds ='20170114' LIMIT 10;