:Run SQL commands and export results

Step 1: Run SQL commands

1. Query the non-partitioned table bank_data and the partitioned table bank_data_pt to find the number of single people with a housing loan, grouped by education level. Save the results to result_table1 and result_table2, respectively.
   Command examples:

   -- Query the number of single people with a housing loan at each education level in the non-partitioned table bank_data and 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;
   -- Query the number of single people with a housing loan at each education level in the partitioned table bank_data_pt and write the query results 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 in result_table1 and result_table2.
   Command examples:

   select * from result_table1;
   select * from result_table2;

   The following results are 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 results

Export data from a MaxCompute table to your local machine using Tunnel Download. For more information about Tunnel operations, see Tunnel command. The procedure is as follows:

1. Specify an export path for the data file.
   You can export the data to the bin directory of the MaxCompute client by specifying only the filename, such as filename.extension. Alternatively, specify a full path, such as D:\test\filename.extension, to export the data to a different location.

   This example exports data from result_table1 to the bin directory of the MaxCompute client, and data from result_table2 to the test folder on drive D.

2. In the MaxCompute client, run a Tunnel Download command to export the data.
   Command examples:

   tunnel download result_table1 result_table1.txt;
   tunnel download result_table2 D:\test\result_table2.csv;

   The OK message in the output indicates that the export is successful.

   odps@ doc_test_dev>tunnel download result_table2 D:\test\result_table2.csv;
   2021-05-18 18:19:16  -  new session: 202105181821291531f60b253e09d6    total lines: 15
   2021-05-18 18:19:16  -  file [0]: [0, 15), D:\test\result_table2.csv
   downloading 15 records into 1 file
   2021-05-18 18:19:16  -  file [0] start
   2021-05-18 18:19:16  -  file [0] OK. total: 337 bytes
   total: 337 bytes, time: 282 ms, average speed: 1,000 bytes/s
   download OK
   odps@ doc_test_dev>

3. In the export path, verify that the data files exist and are complete.

   The data exported from result_table1 is as follows.

   basic.4y,227
   basic.6y,172
   basic.9y,709
   high.school,1641
   illiterate,1
   professional.course,785
   university.degree,2399
   unknown,257

   The exported file is saved in the D:\test directory and is named result_table2.csv. A preview of the data shows that the columns correspond to the education level, count, and credit status, matching the data in result_table2.

Next steps

If you no longer need the sample data or the MaxCompute project, you can delete them to avoid incurring unnecessary charges. For more information about how to delete data or a MaxCompute project, see Delete a table or a MaxCompute project.