MaxCompute:Data analysis with the MaxCompute client

3.1 Download and extract the client package

1. Download the latest version of the MaxCompute client package (GitHub).

2. Extract the downloaded package. The following folders are generated:

   • bin: The directory for executable files.

   • conf: The directory for configuration files.

   • lib: The directory for library files.

   • plugins: The directory for plug-ins.

3. Make a note of the path where the package is extracted. You will need it in later steps.

3.2 Configure the client

1. Go to the conf folder and find the odps_config.ini configuration file.

2. Open the odps_config.ini file in a text editor and configure the following parameters:

   project_name=<your_project_name>
   access_id=<your_access_key_id>
   access_key=<your_access_key_secret>
   end_point=https://service.ap-southeast-1.maxcompute.aliyun.com/api

   Parameter description:

   • project_name: Enter the name of the MaxCompute project that you created in Step 2.

   • access_id: Enter the AccessKey ID that you created in Step 1.2.

   • access_key: Enter the AccessKey Secret that you created in Step 1.2.

   • end_point: The endpoint of the MaxCompute service. This tutorial uses the endpoint for the Singapore region.

   Important

   If the region you selected in Step 1 is not Singapore, you need to find the corresponding public endpoint for your selected region.

3.3 Start the MaxCompute client

You can start the MaxCompute client in either of the following ways:

Expected result: The client starts successfully and returns the following message, which indicates that a connection to the MaxCompute project is established:

          __                         __
 ___  ___/ /___   ___ ____ __ _  ___/ /
/ _ \/ _  // _ \ (_-</ __//  ' \/ _  / 
\___/\_,_// .__//___/\__//_/_/_/\_,_/  
         /_/                           
Aliyun ODPS Command Line Tool
Version x.xx.x-public
@Copyright 2026 Alibaba Cloud Computing Co., Ltd. All rights reserved.
Connecting to https://service.ap-**.maxcompute.aliyun.com/api, project: <your_project_name>
Endpoint: https://service.ap-**.maxcompute.aliyun.com/api
Project: <your_project_name>
Quota: default in region N/A
Timezone: **
Connected!
<your_project_name> >

4.1 Download the sample data

This tutorial uses four sample data files. Follow these steps to download them:

1. banking.txt (Full raw dataset, 41,188 records)

2. banking_yescreditcard.csv (Customers with credit cards, 3 records)

3. banking_nocreditcard.csv (Customers without credit cards, 32,588 records)

4. banking_uncreditcard.csv (Customers with an unknown credit card status, 8,597 records)

4.2 Understand the data structure

The logical relationship between these four datasets is as follows:

banking.txt (Full raw dataset, 41,188 records)
├─ banking_yescreditcard.csv (Customers with credit cards, 3 records)  
├─ banking_nocreditcard.csv (Customers without credit cards, 32,588 records)  
└─ banking_uncreditcard.csv (Customers with an unknown credit card status, 8,597 records)

4.3 Preview the data format

A sample analysis of the banking.txt dataset shows that each customer record contains multiple attributes. These include basic demographic information, such as age, job type, marital status, and education level, as well as features related to financial services.

Data sample:

44,blue-collar,married,basic.4y,unknown,yes,no,cellular,aug,thu,210,1,999,0,nonexistent,1.4,93.444,-36.1,4.963,5228.1,0
53,technician,married,unknown,no,no,no,cellular,nov,fri,138,1,999,0,nonexistent,-0.1,93.2,-42,4.021,5195.8,0
28,management,single,university.degree,no,yes,no,cellular,jun,thu,339,3,6,2,success,-1.7,94.055,-39.8,0.729,4991.6,1

Data format description:

• Delimiter: Comma (,)

• Header: No header row

• Number of fields: 21

4.4 Place the data files

Place the downloaded data files in the bin directory of the MaxCompute client, or make a note of the full path to the data files. If the downloaded sample data is not in the bin directory, you must provide the correct file path when you run the import commands.

5.1 Design the table structures

1. Based on the sample data and logical relationships, this tutorial uses the following table structures:

   • Non-partitioned table bank_data: Stores the full raw dataset from banking.txt (41,188 records).

   • Partitioned table bank_data_pt: Stores the three CSV datasets, partitioned by the credit field (credit card status).

     • credit='yes': Customers with credit cards (3 records)

     • credit='no': Customers without credit cards (32,588 records)

     • credit='unknown': Customers with an unknown credit card status (8,597 records)

   • Non-partitioned table result_table1: Stores the query results for education level statistics.

   • Non-partitioned table result_table2: Stores the query results for statistics on education level and credit card status.

   Note

   A partition is like a category. By categorizing data, you place different types of data into different directories. Partitioned tables optimize queries. By specifying partitions in the WHERE clause, you avoid a full table scan, which improves processing efficiency and reduces computing costs.

5.2 Create the non-partitioned table bank_data

Run the following SQL statement in the MaxCompute client:

-- Create the non-partitioned table bank_data to store the full raw dataset.
CREATE TABLE IF NOT EXISTS bank_data
(
 age             BIGINT comment 'Age',
 job             STRING comment 'Job type',
 marital         STRING comment 'Marital status',
 education       STRING comment 'Education level',
 credit          STRING comment 'Credit card status',
 housing         STRING comment 'Home loan status',
 loan            STRING comment 'Personal loan status',
 contact         STRING comment 'Contact method',
 month           STRING comment 'Month',
 day_of_week     STRING comment 'Day of the week',
 duration        STRING comment 'Duration',
 campaign        BIGINT comment 'Number of contacts during this campaign',
 pdays           DOUBLE comment 'Days since the last contact',
 previous        DOUBLE comment 'Number of contacts before this campaign',
 poutcome        STRING comment 'Outcome of the previous marketing campaign',
 emp_var_rate    DOUBLE comment 'Employment variation rate',
 cons_price_idx  DOUBLE comment 'Consumer price index',
 cons_conf_idx   DOUBLE comment 'Consumer confidence index',
 euribor3m       DOUBLE comment 'Euribor 3-month rate',
 nr_employed     DOUBLE comment 'Number of employees',
 fixed_deposit   BIGINT comment 'Has a fixed deposit'
);

5.3 Create the partitioned table bank_data_pt

Run the following SQL statement in the MaxCompute client:

-- Create the partitioned table bank_data_pt, with 'credit' (credit card status) as the partition key.
CREATE TABLE IF NOT EXISTS bank_data_pt
(
 age             BIGINT comment 'Age',
 job             STRING comment 'Job type',
 marital         STRING comment 'Marital status',
 education       STRING comment 'Education level',
 housing         STRING comment 'Home loan status',
 loan            STRING comment 'Personal loan status',
 contact         STRING comment 'Contact method',
 month           STRING comment 'Month',
 day_of_week     STRING comment 'Day of the week',
 duration        STRING comment 'Duration',
 campaign        BIGINT comment 'Number of contacts during this campaign',
 pdays           DOUBLE comment 'Days since the last contact',
 previous        DOUBLE comment 'Number of contacts before this campaign',
 poutcome        STRING comment 'Outcome of the previous marketing campaign',
 emp_var_rate    DOUBLE comment 'Employment variation rate',
 cons_price_idx  DOUBLE comment 'Consumer price index',
 cons_conf_idx   DOUBLE comment 'Consumer confidence index',
 euribor3m       DOUBLE comment 'Euribor 3-month rate',
 nr_employed     DOUBLE comment 'Number of employees',
 fixed_deposit   BIGINT comment 'Has a fixed deposit'
)partitioned by (credit STRING comment 'Credit card status');

-- Add three partitions: yes, no, and unknown.
alter table bank_data_pt add if not exists partition (credit='yes') partition (credit='no') partition (credit='unknown');

5.4 Create the result table result_table1

Run the following SQL statement in the MaxCompute client:

-- Create the result table result_table1 to store education level statistics.
CREATE TABLE IF NOT EXISTS result_table1
(
 education   STRING comment 'Education level',
 num         BIGINT comment 'Count'
);

5.5 Create the result table result_table2

Run the following SQL statement in the MaxCompute client:

-- Create the result table result_table2 to store statistics on education level and credit card status.
CREATE TABLE IF NOT EXISTS result_table2
(
 education   STRING comment 'Education level',
 num         BIGINT comment 'Count',
 credit      STRING comment 'Credit card status'
);

5.6 Verify table creation

SHOW tables;

ALIYUN$****:bank_data
ALIYUN$****:bank_data_pt
ALIYUN$****:result_table1
ALIYUN$****:result_table2

OK

-- View the structure of the bank_data table.
DESC bank_data;

-- View the structure of the bank_data_pt table.
DESC bank_data_pt;

-- View the structure of the result_table1 table.
DESC result_table1;

-- View the structure of the result_table2 table.
DESC result_table2;

+------------------------------------------------------------------------------------+
| Owner: ALIYUN$****          | Project: doc_test_dev                                |
| TableComment:                                                                      |
+------------------------------------------------------------------------------------+
| CreateTime:               2026-05-17 14:02:21                                      |
| LastDDLTime:              2026-05-17 14:02:21                                      |
| LastModifiedTime:         2026-05-17 14:02:21                                      |
+------------------------------------------------------------------------------------+
| InternalTable: YES      | Size: 0                                                  |
+------------------------------------------------------------------------------------+
| Native Columns:                                                                    |
+------------------------------------------------------------------------------------+
| Field           | Type       | Label | Comment                                     |
+------------------------------------------------------------------------------------+
| age             | bigint     |       | Age                                         |
| job             | string     |       | Job type                                    |
| marital         | string     |       | Marital status                              |
| education       | string     |       | Education level                             |
| housing         | string     |       | Home loan status                            |
| loan            | string     |       | Personal loan status                        |
| contact         | string     |       | Contact method                              |
| month           | string     |       | Month                                       |
| day_of_week     | string     |       | Day of the week                             |
| duration        | string     |       | Duration                                    |
| campaign        | bigint     |       | Number of contacts during this campaign     |
| pdays           | double     |       | Days since the last contact                 |
| previous        | double     |       | Number of contacts before this campaign     |
| poutcome        | string     |       | Outcome of the previous marketing campaign  |
| emp_var_rate    | double     |       | Employment variation rate                   |
| cons_price_idx  | double     |       | Consumer price index                        |
| cons_conf_idx   | double     |       | Consumer confidence index                   |
| euribor3m       | double     |       | Euribor 3-month rate                        |
| nr_employed     | double     |       | Number of employees                         |
| fixed_deposit   | bigint     |       | Has a fixed deposit                         |
+------------------------------------------------------------------------------------+
| Partition Columns:                                                                 |
+------------------------------------------------------------------------------------+
| credit          | string     | Credit card status                                  |
+------------------------------------------------------------------------------------+

OK

SHOW partitions bank_data_pt;

credit=no
credit=unknown
credit=yes

OK

6.1 The Tunnel tool

Tunnel is a data channel that serves as the data import and export tool for MaxCompute. You can use Tunnel to upload data to or download data from MaxCompute.

6.2 Import data into the bank_data table

Run the following command to import the banking.txt file into the bank_data table:

tunnel upload banking.txt bank_data;

Command format:

tunnel upload <path_to_the_data_file_to_import> <destination_table>

Expected output:

Upload session: 20260202192653092c3b1a0d8ed099
Start upload:banking.txt
Using \n to split records
Upload in strict schema mode: true
Total bytes:4841548	 Split input to 1 blocks
2026-05-02 19:26:53	scan block: '1'
2026-05-02 19:26:53	scan block complete, block id: 1
2026-05-02 19:26:53	upload block: '1'
2026-05-02 19:26:55	Block info: 1:0:4841548:banking.txt, progress: 100%, bs: 4.6 MB, speed: 2.3 MB/s
2026-05-02 19:26:55	upload block complete, block id: 1
upload complete, average speed is 1.5 MB/s
OK

6.3 Import data into the partitioned table

Run the following commands to import the three CSV files into their corresponding partitions in the bank_data_pt table:

-- Import data for customers with credit cards into the credit='yes' partition.
tunnel upload banking_yescreditcard.csv bank_data_pt/credit="yes";

-- Import data for customers with an unknown status into the credit='unknown' partition.
tunnel upload banking_uncreditcard.csv bank_data_pt/credit="unknown";

-- Import data for customers without credit cards into the credit='no' partition.
tunnel upload banking_nocreditcard.csv bank_data_pt/credit="no";

Command format (for partitioned tables):

tunnel upload <path_to_the_data_file_to_import> <destination_table>/<partition_key>="<partition_value>"

Expected output (using the first command as an example):

Upload session: 20260202192822042e3b1a0d8f28f6
Start upload:banking_yescreditcard.csv
Using \r\n to split records
Upload in strict schema mode: true
Total bytes:351	 Split input to 1 blocks
2026-05-02 19:28:22	scan block: '1'
2026-05-02 19:28:22	scan block complete, block id: 1
2026-05-02 19:28:22	upload block: '1'
2026-05-02 19:28:22	upload block complete, block id: 1
upload complete, average speed is 351 bytes/s
OK

The OK message in the output indicates that the data was imported successfully.

6.4 Verify data import

Run the following commands to check if the number of rows in each table is correct:

-- Check the number of rows in the bank_data table (should be 41,188).
select count(*) as num1 from bank_data;

-- Check the number of rows in the credit='yes' partition of the bank_data_pt table (should be 3).
select count(*) as num2 from bank_data_pt where credit="yes";

-- Check the number of rows in the credit='unknown' partition of the bank_data_pt table (should be 8,597).
select count(*) as num3 from bank_data_pt where credit="unknown";

-- Check the number of rows in the credit='no' partition of the bank_data_pt table (should be 32,588).
select count(*) as num4 from bank_data_pt where credit="no";

Expected output:

-- Number of rows in bank_data
+------------+
| num1       | 
+------------+
| 41188      | 
+------------+
A total of 1 records fetched by instance tunnel. Max record number: 10000

-- Number of rows where credit is 'yes' in bank_data_pt
+------------+
| num2       | 
+------------+
| 3          | 
+------------+
A total of 1 records fetched by instance tunnel. Max record number: 10000

-- Number of rows where credit is 'unknown' in bank_data_pt
+------------+
| num3       | 
+------------+
| 8597       | 
+------------+
A total of 1 records fetched by instance tunnel. Max record number: 10000

-- Number of rows where credit is 'no' in bank_data_pt
+------------+
| num4       | 
+------------+
| 32588      | 
+------------+
A total of 1 records fetched by instance tunnel. Max record number: 10000

Verification results:

• bank_data table: 41,188 rows (matches banking.txt)

• bank_data_pt table, credit='yes' partition: 3 rows (matches banking_yescreditcard.csv)

• bank_data_pt table, credit='unknown' partition: 8,597 rows (matches banking_uncreditcard.csv)

• bank_data_pt table, credit='no' partition: 32,588 rows (matches banking_nocreditcard.csv)

If the row counts do not match, check the following:

1. Ensure the data files were downloaded completely.

2. Verify the import commands were run correctly.

3. Check that the data file format is correct.

7.1 Run a simple query (Optional)

Before you start running complex queries, you can run a simple query to verify that the data was imported correctly:

-- View the first 10 rows of the bank_data table.
select * from bank_data limit 10;

Expected output: The command returns 10 data records, including values for all 21 fields.

7.2 Query data from a non-partitioned table

Run the following SQL command to query the bank_data non-partitioned table for the number of single individuals with a home loan, grouped by education level. Save the results to result_table1.

-- Query the number of single individuals with a home loan from the non-partitioned table bank_data, grouped by education level.
-- Write the query results to result_table1.
INSERT OVERWRITE TABLE result_table1
 SELECT education, COUNT(marital) AS num
 FROM bank_data
 WHERE housing = 'yes' AND marital = 'single'
 GROUP BY education;

SQL description:

• insert overwrite table: Overwrites the destination table with the query result.

• where housing = 'yes' and marital = 'single': Specifies the filter conditions (has a home loan and is single).

• group by education: Groups the results by education level.

• count(marital): Counts the number of individuals for each education level.

Expected output:

Job Queueing...

OK

7.3 Query data from a partitioned table

Run the following SQL command to query the bank_data_pt partitioned table for the number of single individuals with a home loan, grouped by education level and credit card status. Save the results to result_table2.

-- Allow a full table scan. This is required if no partition condition is specified when querying a partitioned table.
set odps.sql.allow.fullscan=true;

-- Query the number of single individuals with a home loan from the partitioned table bank_data_pt.
-- Group the query results by education level and credit card status, and write them to result_table2.
insert overwrite table result_table2 
select education, count(marital) as num, credit 
from bank_data_pt 
where housing = 'yes' and marital = 'single'
group by education, credit;

SQL description:

• set odps.sql.allow.fullscan=true;: Allows a full table scan, which is necessary when querying a partitioned table without a partition filter.

• group by education, credit: Groups the results by education level and credit card status.

Expected output:

Job Queueing...

OK

7.4 View query results

Run the following commands to view the query results:

-- View the query results in result_table1.
select * from result_table1;

-- View the query results in result_table2.
select * from result_table2;

Expected output:

-- Data in result_table1
+------------+------------+
| education  | num        | 
+------------+------------+
| university.degree | 2399       | 
| basic.4y   | 227        | 
| basic.9y   | 709        | 
| unknown    | 257        | 
| professional.course | 785        | 
| high.school | 1641       | 
| basic.6y   | 172        | 
| illiterate | 1          | 
+------------+------------+
A total of 8 records fetched by instance tunnel. Max record number: 10000

-- Data in result_table2
+------------+------------+------------+
| education  | num        | credit     | 
+------------+------------+------------+
| unknown    | 51         | unknown    | 
| high.school | 172        | unknown    | 
| basic.4y   | 63         | unknown    | 
| university.degree | 196        | unknown    | 
| professional.course | 64         | unknown    | 
| basic.9y   | 162        | unknown    | 
| basic.6y   | 68         | unknown    | 
| illiterate | 1          | unknown    | 
| university.degree | 2203       | no         | 
| basic.4y   | 164        | no         | 
| basic.9y   | 547        | no         | 
| professional.course | 721        | no         | 
| high.school | 1469       | no         | 
| unknown    | 206        | no         | 
| basic.6y   | 104        | no         | 
+------------+------------+------------+
A total of 15 records fetched by instance tunnel. Max record number: 10000

7.5 Data analysis and insights

Based on the query results, we can draw the following insights:

These insights can help the financial institution optimize credit product design, enhance risk control capabilities, and achieve more efficient customer outreach and conversion.

8.1 Export data from result_table1

Run the following Tunnel Download command to export data from result_table1:

tunnel download result_table1 result_table1.txt;

Command format:

tunnel download <source_table> <destination_file_path>

Expected output:

2026-05-02 19:34:40  -  new session: 20260202193440d909371a0d8e908c	total lines: 8
2026-05-02 19:34:40  -  file [0]: [0, 8), result_table1.txt
downloading 8 records into 1 file
2026-05-02 19:34:40  -  file [0] start
2026-05-02 19:34:41  -  file [0] OK. total: 128 bytes
total: 128 bytes, time: 442 ms, average speed: 0 bytes/s
download OK

The OK message in the output indicates that the data was exported successfully.

8.2 Export data from result_table2

Run the following command to export data from result_table2:

tunnel download result_table2 result_table2.csv;

Expected output:

2026-05-02 19:35:26  -  new session: 20260202193526dce7301a0d8ef026	total lines: 15
2026-05-02 19:35:26  -  file [0]: [0, 15), result_table2.csv
downloading 15 records into 1 file
2026-05-02 19:35:26  -  file [0] start
2026-05-02 19:35:26  -  file [0] OK. total: 322 bytes
total: 322 bytes, time: 441 ms, average speed: 0 bytes/s
download OK

8.3 Verify export results

1. Check that the data files exist in the specified export path (the bin directory of the MaxCompute client by default).

2. Open the exported files to verify data integrity:

   • result_table1.txt: Should contain 8 records (for 8 education levels).

   • result_table2.csv: Should contain 15 records (grouped by education level and credit card status).

Expected result:

• The files are successfully exported to the specified path.

• The data in the files matches the query results.

• The file format is correct (TXT or CSV).

9.1 Delete tables

Run the following commands in the MaxCompute client to delete the tables you created:

-- Delete result_table2.
drop table result_table2;

-- Delete result_table1.
drop table result_table1;

-- Delete the bank_data_pt partitioned table.
drop table bank_data_pt;

-- Delete the bank_data non-partitioned table.
drop table bank_data;

Command format:

drop table <table_name>;

9.2 Verify table deletion

Run the following command to confirm that the tables have been deleted:

show tables;

Expected output:

OK

The output contains no table information, which indicates that the tables were deleted successfully.

9.3 Delete the MaxCompute project

Important warning

Deleting a project has the following consequences:

• All resources in the project are deleted: including all tables, functions, and resources.

• This operation is irreversible: after deletion, the project and its data cannot be recovered.

• If you use an existing project: deleting the project also deletes other work in that project.

Procedure:

1. Log on to the MaxCompute console.

2. In the left-side navigation pane, choose Manage Configurations > Projects.

3. On the Projects page, click Delete in the Actions column for the target project.

4. Follow the on-screen instructions to confirm the deletion.

9.4 Verify resource cleanup

After you clean up the resources, confirm that:

• All tables have been deleted.

• No further charges are incurred.

• No longer incurs charges

Q1: What do I do if the client connection fails?

Possible causes:

• Incorrect project name configuration

• Incorrect AccessKey ID or Secret configuration

• Incorrect endpoint configuration

• Network connectivity issues

Solutions:

1. Check that the settings in the odps_config.ini file are correct.

2. Verify that the AccessKey is valid and has not expired or been deleted.

3. Verify that the endpoint matches the project's region.

4. Check your network connection.

Q2: What do I do if data import fails?

Possible causes:

• Incorrect data file path

• Data format mismatch (number of fields, data types)

• File encoding issues

Solutions:

1. Check that the data file path is correct. Use an absolute path.

2. Verify that the data file format matches the table structure.

3. Check the error message and make adjustments as prompted.

Q3: What do I do if an error occurs when I query a partitioned table?

Error message: ODPS-0130071: Partition exception

Solutions:

• If you query a partitioned table without specifying a partition condition, first run the command: set odps.sql.allow.fullscan=true;

• Alternatively, specify a partition condition in the WHERE clause. For example: where credit='yes'