This topic describes how to run SQL statements to export data.
Currently, MaxCompute SQL supports the use of:
- A variety of operators.
- DDL statements to manage tables, partitions, and views.
- SELECT statements to query records in tables and WHERE statements to filter records in tables.
- INSERT statements to insert and update data.
- JOIN operations to associate two tables and MAPJOIN operations for multiple small tables.
- Built-in and user-defined functions for computing.
- Regular expressions.
- MaxCompute SQL does not support transactions, indexes, update operations, or delete operations. The SQL syntax of MaxCompute is different from that of Oracle and MySQL. Therefore, you cannot migrate SQL statements from other databases to MaxCompute.
- For more information about SQL operations, see SQL summary.
- After you submit a MaxCompute job, some time is required to schedule the job. Therefore, MaxCompute is suitable for processing jobs in batches, that is, processing a massive volume of data. MaxCompute is not suitable for frontend business systems that must process several thousand or tens of thousands of transactions per second. For more information about how to optimize a job, see Optimize SQL statements.
- For the limits of MaxCompute SQL, see MaxCompute SQL limits.
Obtain and analyze data
The following is an example of using SQL to query the number of single people (with different education backgrounds) that have purchased a house. The result is saved to the result_table table for analysis and display.You can use
INSERT OVERWRITE TABLE result_table --Insert data to the result_table table. SELECT education,COUNT(marital) AS num FROM bank_data WHERE housing = 'yes' AND marital = 'single' GROUP BY education;
select * from result_table;to view data in the result_table table, as shown in the following figure.You can process multiple tables by using multiple SQL statements in DataWorks.
After processing data by using SQL statements, you can export data to your D drive by running a tunnel command. For more information, see Tunnel commands.
If download OK (outlined in red in the following figure) is displayed, the data is successfully exported.
tunnel download result_table D:\result.txt;
Note You can use the data integration feature to export data to other data sources (such as MySQL).