MaxCompute:Overview of public datasets

GitHub public event data

Use this dataset to analyze open source project activity on GitHub — who is starring repositories, committing code, opening pull requests, and more. For a full list of event types, see GitHub Events.

Tables:

Inspect the table schema:

-- Enable session-level schema syntax.
SET odps.namespace.schema=true;
-- Replace the schema and table name to inspect other tables.
DESC bigdata_public_dataset.github_events.dwd_github_events_odps;

Example query — find the most-starred repositories in the past year:

Which repositories received the most stars in the last 365 days?

-- Enable session-level schema syntax.
SET odps.namespace.schema=true;
-- Returns the top 10 repositories by star count over the last 365 days.
-- Note: This query does not account for users who later unstarred a repository.
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 data details and query samples, see GitHub public event data.

National statistics

Use this dataset to analyze long-term economic trends. It contains annual GDP data for provinces in China and for countries worldwide.

Tables:

Inspect the table schema:

-- Enable session-level schema syntax.
SET odps.namespace.schema=true;
-- Replace the table name to inspect other tables.
DESC bigdata_public_dataset.national_data.annual_gdp_by_province;

Example query — view Beijing's GDP trend over the past 20 years:

How has Beijing's GDP changed over the last 20 years?

-- Enable session-level schema syntax.
SET odps.namespace.schema=true;
SELECT
    region,
    gdp,
    year
FROM
    bigdata_public_dataset.national_data.annual_gdp_by_province
WHERE
    region = 'Beijing'
ORDER BY
    year ASC
LIMIT 20;

TPC-DS data

Use TPC-DS data to run decision support benchmark queries on MaxCompute. TPC-DS simulates the sales system of a large retail chain across three channels — physical stores, online stores, and phone orders (catalog) — with data covering sales, returns, customers, products, and promotions. Choose the schema that matches your target data volume for performance testing.

Tables (24 total):

Inspect the table schema:

-- Enable session-level schema syntax.
SET odps.namespace.schema=TRUE;
-- This example uses tpcds_10g. Replace the schema name to query other dataset sizes.
DESC bigdata_public_dataset.tpcds_10g.call_center;

Example query — find top-selling brands in December by year:

Which brands had the highest sales in December each year, for a specific manufacturer?

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 sample files covering all dataset sizes, see TPC-DS data. For benchmark specification details, see the official TPC Benchmark DS standard specification.

TPC-H data

Use TPC-H data to benchmark Online Analytical Processing (OLAP) queries on MaxCompute. TPC-H uses a set of business-oriented ad hoc queries with concurrent data modifications to evaluate query performance on large datasets. The data simulates transactions between suppliers and buyers.

Tables (8 total):

Inspect the table schema:

-- Enable session-level schema syntax.
SET odps.namespace.schema=TRUE;
-- This example uses tpch_10g. Replace the schema name to query other dataset sizes.
DESC bigdata_public_dataset.tpch_10g.lineitem;

Example query — pricing summary report for shipped line items:

What is the summary of pricing information — quantities, prices, discounts, taxes, and order counts — for all line items shipped before a given date?

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 benchmark specification details, see the official TPC Benchmark H standard specification.

TPCx-BB data

TPCx-BB is a big data benchmark that measures the performance of Hadoop-based big data systems. It evaluates hardware and software by running 30 common analytical queries on a simulated online retail scenario, covering sales, returns, products, promotions, and web clickstreams.

Tables (23 total):

Inspect the table schema:

-- Enable session-level schema syntax.
SET odps.namespace.schema=TRUE;
-- This example uses tpcxbb_10g. Replace the schema name to query other dataset sizes.
DESC bigdata_public_dataset.tpcxbb_10g.web_sales;

Example query:

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

For benchmark specification details, see the official TPCx-BB standard specification.

Digital commerce dataset

Use this dataset to analyze ad click-through rates and shopping behavior. It contains display ad click logs and shopping activity — browsing, adding to cart, liking, and purchasing — from over one million randomly sampled Taobao users.

Tables:

Inspect the table schema:

-- Enable session-level schema syntax.
SET odps.namespace.schema=TRUE;
-- Replace the table name to inspect other tables.
DESC bigdata_public_dataset.commerce.behavior_log;

Example query — find the top three product categories by sales volume over 22 days:

Which product categories had the highest purchase volume across the 22-day observation period?

-- Enable session-level schema syntax.
SET odps.namespace.schema=TRUE;
SELECT
    cate,
    count(btag) sales
FROM
    behavior_log
WHERE
    btag = 'buy'
GROUP BY
    cate
ORDER BY
    sales DESC
LIMIT 3;

Life service dataset

Use this dataset to analyze entertainment trends and geographic reference data. It includes movie box office results and administrative reference codes for China.

Tables:

Inspect the table schema:

-- Enable session-level schema syntax.
SET odps.namespace.schema=TRUE;
-- Replace the table name to inspect other tables.
DESC bigdata_public_dataset.life_service.movie_box;

Example query — find the top 10 movies at the box office on January 14, 2017:

Which movies had the highest box office ranking on January 14, 2017?

-- Enable session-level schema syntax.
SET odps.namespace.schema=TRUE;
SELECT
    moviename
FROM
    bigdata_public_dataset.life_service.movie_box
WHERE
    ds = '20170114'
ORDER BY
    rank ASC
LIMIT 10;

Financial stock dataset

Use this dataset to analyze stock performance trends and quarterly financial results. It contains stock price history and quarterly financial reports for publicly traded companies.

Tables:

Inspect the table schema:

-- Enable session-level schema syntax.
SET odps.namespace.schema=TRUE;
-- Replace the table name to inspect other tables.
DESC bigdata_public_dataset.finance.ods_enterprise_share_basic;

Example query — view basic stock data for January 14, 2017:

What basic stock information was recorded on January 14, 2017?

-- Enable session-level schema syntax.
SET odps.namespace.schema=TRUE;
SELECT *
FROM bigdata_public_dataset.finance.ods_enterprise_share_basic
WHERE ds = '20170114'
LIMIT 10;

Prerequisites

Before you begin, ensure that you have:

• An active MaxCompute service

• A MaxCompute project. For setup instructions, see Create a project

Supported tools

Run queries against public datasets using any of the following:

• Local client (odpscmd)

• MaxCompute Studio

• DataWorks Data Development nodes

Procedure (DataWorks Data Development node example)

1. Log on to the DataWorks console and select a region in the upper-left corner.

2. Create a workspace.

3. Attach a MaxCompute data source.

4. Create an ODPS SQL node and enter the following SQL example.

   -- View the GDP trend of each province in China over the last 20 years.
   SET odps.namespace.schema=true; 
   SET odps.sql.validate.orderby.limit = false;
   SELECT
       region,
       gdp,
       year
   FROM
       bigdata_public_dataset.national_data.annual_gdp_by_province
   ORDER BY
       year ASC;

5. Click to view the results.

Query using DataWorks

1. Log on to the DataWorks console and select a region in the upper-left corner.

2. Create a workspace.

3. Attach a MaxCompute data source.

4. Create an ODPS SQL node and enter your SQL query. The following example views GDP trends across all provinces in China over the past 20 years:

   SET odps.namespace.schema=true;
   SET odps.sql.validate.orderby.limit = false;
   SELECT
       region,
       gdp,
       year
   FROM
       bigdata_public_dataset.national_data.annual_gdp_by_province
   ORDER BY
       year ASC;

5. Click the run button to execute the query and view results.