This topic describes how to execute SQL statements on the MaxCompute client and use Tunnel Download commands to download the result data.

Prerequisites

Data is imported to a MaxCompute table. For more information about how to import data, see Import data to tables.

Background information

You can execute DDL, DML, and DQL statements on the MaxCompute client based on the provided syntax.

For more information about the common SQL statements that can be executed on the MaxCompute client, see Common SQL statements.

Step 1: Execute SQL statements

  1. On the MaxCompute client, query the number of single persons with home loans at each education level from the non-partitioned table bank_data and the partitioned table bank_data_pt and write the result data to the result_table1 and result_table2 tables:
    -- Query the number of single persons with home loans at each education level from the non-partitioned table bank_data and write the result data 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;
    
    -- Query the number of single persons with home loans at each education level from the partitioned table bank_data_pt and write the result data to result_table2. 
    set odps.sql.allow.fullscan=true;
    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;
  2. Query the data that is written to result_table1 and result_table2:
    select * from result_table1;
    select * from result_table2;
    The following information is returned:
    -- Data in result_table1 
    +------------+------------+
    | education  | num        |
    +------------+------------+
    | basic.4y   | 227        |
    | basic.6y   | 172        |
    | basic.9y   | 709        |
    | high.school | 1641       |
    | illiterate | 1          |
    | professional.course | 785        |
    | university.degree | 2399       |
    | unknown    | 257        |
    +------------+------------+
    -- Data in result_table2 
    +------------+------------+------------+
    | education  | num        | credit     |
    +------------+------------+------------+
    | basic.4y   | 164        | no         |
    | basic.4y   | 63         | unknown    |
    | basic.6y   | 104        | no         |
    | basic.6y   | 68         | unknown    |
    | basic.9y   | 547        | no         |
    | basic.9y   | 162        | unknown    |
    | high.school | 1469       | no         |
    | high.school | 172        | unknown    |
    | illiterate | 1          | unknown    |
    | professional.course | 721        | no         |
    | professional.course | 64         | unknown    |
    | university.degree | 2203       | no         |
    | university.degree | 196        | unknown    |
    | unknown    | 206        | no         |
    | unknown    | 51         | unknown    |
    +------------+------------+------------+

Step 2: Export result data

Export data from MaxCompute tables to your computer by running Tunnel Download commands. For more information about Tunnel operations, see Tunnel commands.

  1. Determine an export path.
    You can export the data as a file to the bin directory of the MaxCompute client. In this case, you must specify an export path in the File name.File name extension format in the export command. You can also export the data as a file to another directory, such as the test folder on drive D. In this case, you must specify an export path in the D:\test\File name.File name extension format in the export command.

    In this example, the data in result_table1 is exported to the bin directory of the MaxCompute client, and the data in result_table2 is exported to the test folder on drive D.

  2. On the MaxCompute client, run the following Tunnel Download commands to export data.
    tunnel download result_table1 result_table1.txt;
    tunnel download result_table2 D:\test\result_table2.csv;
    If OK is returned, the data is exported. Exported
  3. Check whether the data is completely exported to the export paths.

    The following figure shows the data exported from result_table1.

    Export result 1

    The following figure shows the data exported from result_table2.

    Export result 2

Additional information

If you no longer need to use the sample data or the MaxCompute project in which the sample data is used, you can delete the data or the MaxCompute project to reduce resource consumption and storage fees. For more information about how to delete data and MaxCompute projects, see Delete a table or a MaxCompute project.