MaxCompute:Run SQL and export results

Prerequisites

Before you begin, ensure that you have:

• Data imported into a MaxCompute table. For more information, see Import data to tables.

Supported SQL statement types

The MaxCompute client supports DDL (Data Definition Language), DML (Data Manipulation Language), and DQL (Data Query Language) statements. For a list of commonly used SQL statements, see List of common commands.

Step 1: Execute SQL statements

In this step, you run INSERT...SELECT queries to write filtered data from the source tables into result tables, then verify the output.

1. On the MaxCompute client, run the following queries to count single persons with home loans at each education level. The first query reads from the non-partitioned table bank_data and writes results to result_table1. The second query reads from the partitioned table bank_data_pt and writes results to result_table2.

   -- Query bank_data (non-partitioned) and write 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 bank_data_pt (partitioned) and write 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. Verify that data was written to the result tables:

   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 result data

In this step, you use Tunnel Download commands to download the result tables to your local machine as files. For more information about Tunnel operations, see Tunnel command.

1. Determine an export path.

   • To export to the bin directory of the MaxCompute client, specify the path as <filename>.<extension> (for example, result_table1.txt).

   • To export to a custom directory, specify the full path in the D:\test\<filename>.<extension> format.

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

2. Run the following Tunnel Download commands to export the 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 successfully.

3. Check the exported files at the specified paths to confirm the data is complete.

   The following figure shows the exported data from result_table1.

   

   The following figure shows the exported data from result_table2.

   

What's next

If you no longer need the sample data or the MaxCompute project, delete them to avoid unnecessary resource consumption and storage fees. For more information, see Delete tables and projects.