Hologres:Analyze massive MaxCompute data for display

Create a foreign table

1. Log on to the HoloWeb console.

2. In the top navigation bar, choose Metadata Management > MaxCompute Query Acceleration > Create Foreign Table.

3. Enter the name of the MaxCompute table to query, such as public_data.customer. The table fields are displayed.

4. Select the fields to synchronize, then click Submit.

To create multiple foreign tables at once, run the following SQL statement on the Ad-hoc Query page:

IMPORT FOREIGN SCHEMA public_data LIMIT to(
  customer,
  customer_address,
  customer_demographics,
  inventory,item,
  date_dim,
  warehouse)
FROM server odps_server INTO PUBLIC options(if_table_exist 'update');

Preview foreign table data

In the Instance Management panel, right-click the foreign table and select Open Table. On the table details page, click Data Preview.

Query data

On the table details page, click Query table. On the Ad-hoc Query page, enter SQL statements in the code editor and click the Run icon.

All four sample queries below run against the customer and customer_address tables in the public_data project.

-- SQL 1: Count customers by preferred flag, sorted descending
SELECT c_preferred_cust_flag,
       count(*) AS cnt
FROM customer
WHERE c_preferred_cust_flag IS NOT NULL
GROUP BY c_preferred_cust_flag
ORDER BY cnt DESC LIMIT 10;

-- SQL 2: Count customers by birth year (years with more than 1,000 customers)
SELECT c_birth_year,
       count(*) AS cnt
FROM customer
WHERE c_birth_year IS NOT NULL
GROUP BY c_birth_year HAVING count(*) > 1000
ORDER BY cnt DESC LIMIT 10;

-- SQL 3: Count customers by city (cities with more than 10 customers)
SELECT ca_city,
       count(*) AS cnt
FROM customer ,
     customer_address
WHERE c_current_addr_sk = ca_address_sk
  AND ca_city IS NOT NULL
GROUP BY ca_city HAVING count(*) > 10
ORDER BY cnt DESC LIMIT 10;

-- SQL 4: Preferred customers born between 1980 and 1989, by city
SELECT ca_city,
       count(*) AS cnt
FROM customer ,
     customer_address
WHERE c_current_addr_sk = ca_address_sk
  AND c_birth_year >= 1980
  AND c_birth_year < 1990
  AND c_preferred_cust_flag = 'Y'
  AND ca_city IS NOT NULL
GROUP BY ca_city HAVING count(*) > 10
ORDER BY cnt DESC LIMIT 10;