MaxCompute SQL is the SQL dialect used in MaxCompute for large-scale data queries and analysis. It extends the ANSI SQL-92 standard with capabilities designed for large-scale data warehouse scenarios.
When to use MaxCompute SQL
MaxCompute SQL is built for batch jobs that process gigabytes, terabytes, or exabytes of data. After you submit a job, queue scheduling may add tens of seconds to several minutes before execution begins — plan your workflows accordingly.
MaxCompute SQL is not designed for OLTP systems or applications that require thousands to tens of thousands of transactions per second. For those workloads, use a transactional database instead.
How MaxCompute SQL differs from standard SQL
If you are coming from Hive, MySQL, or Oracle, start with these two topics before writing any SQL:
-
Differences in SQL statement support — syntax gaps between MaxCompute SQL and mainstream databases
-
Data type mappings between MaxCompute and Hive, MySQL, and Oracle — type compatibility across databases
These two topics cover the compatibility gaps most likely to affect your existing SQL code.
Language reference
Language fundamentals
| Topic | Description |
|---|---|
| Differences in SQL statement support | Syntax differences between MaxCompute SQL and mainstream databases |
| Reserved words and keywords | Reserved words and keywords in MaxCompute SQL |
| Data types | Supported data types, editions, and differences between editions |
| Type conversions | Type conversions supported by MaxCompute SQL |
| Data type mappings between MaxCompute and Hive, MySQL, and Oracle | Type compatibility across databases |
| Escape character | Escape character syntax in MaxCompute SQL |
| LIKE | Characters supported by the LIKE operator for pattern matching |
| RLIKE | Characters supported by the RLIKE operator for regex matching |
| Operators | Relational, arithmetic, bitwise, and logical operators |
SQL statements
| Topic | Description |
|---|---|
| Limits | Constraints to follow when writing MaxCompute SQL scripts |
| DDL statements | Syntax for creating and managing tables, partitions, columns, and lifecycles |
| DML statements | INSERT INTO and INSERT OVERWRITE syntax |
| DQL statements | SELECT statement syntax for querying data |
| Enhanced SQL syntax | LOAD and UNLOAD statements for improved readability and execution efficiency |
| Run MaxCompute SQL in script mode | Compile SQL scripts in script mode |
Extensibility
| Topic | Description |
|---|---|
| MaxCompute UDF | Create user-defined functions (UDFs), user-defined table-valued functions (UDTFs), and user-defined aggregate functions (UDAFs) to extend SQL with custom logic |
| MaxCompute UDT | Call classes and methods from third-party programming languages directly in SQL |
| MaxCompute UDJ | Perform custom operations across tables or on multiple tables |
Development tools
Choose a tool based on job complexity and team workflow:
| Tool | Best for |
|---|---|
| MaxCompute client | Simple jobs |
| MaxCompute Studio | Complex jobs |
| DataWorks console | Complex jobs |