MaxCompute:Execute SQL statements and export the result data

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.
3. Check whether the data is completely exported to the export paths.

   The following figure shows the data exported from result_table1.

   

   The following figure shows the data exported from result_table2.

   

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.