### Get MaxCompute Project List

Source: https://help.aliyun.com/zh/maxcompute/user-guide/cli-integration-example

This example shows how to use the Alibaba Cloud CLI to call the MaxCompute `ListProjects` API. It demonstrates filtering projects by `tenantId` and `quotaName`, and includes necessary headers for the request.

```APIDOC
## Get MaxCompute Project List

### Description
This command invokes the MaxCompute `ListProjects` API using the Alibaba Cloud CLI. It allows you to retrieve a list of your MaxCompute projects, optionally filtering them by tenant ID and quota name. The example includes a sample JSON response.

### Method
CLI Command

### Endpoint
`GET /api/v1/projects`

### Parameters
#### Query Parameters
- `tenantId` (string) - Required - The ID of the tenant to filter projects by.
- `quotaName` (string) - Required - The name of the quota to filter projects by.

#### Headers
- `Content-Type` (string) - Required - Specifies the content type of the request, typically `application/json;`.

### Request Example
```bash
ali yun maxcompute GET /api/v1/projects --tenantId '5495****3697' --quotaName 'aliyun_5495***3697' --header "Content-Type=application/json;"
```

### Response
#### Success Response (200)
- `requestId` (string) - The unique ID of the request.
- `data` (object) - Contains the list of projects and pagination information.
  - `projects` (array) - A list of MaxCompute project objects.
    - `name` (string) - The name of the project.
    - `owner` (string) - The owner of the project.
    - `type` (string) - The type of the project (e.g., 'managed').
    - `comment` (string) - A description of the project.
    - `defaultQuota` (string) - The default quota associated with the project.
    - `status` (string) - The current status of the project (e.g., 'AVAILABLE').
    - `costStorage` (string) - Storage cost information.
    - `regionId` (string) - The region where the project is located.
    - `createdTime` (number) - The timestamp when the project was created.
    - `properties` (object) - Project-specific properties.
    - `securityProperties` (object) - Security-related properties of the project.
    - `ipWhiteList` (object) - IP whitelist configuration for the project.
    - `saleTag` (object) - Sales-related tags for the project.
    - `threeTierModel` (boolean) - Indicates if the three-tier model is enabled.
  - `marker` (string) - A marker for pagination.
  - `maxItem` (number) - The maximum number of items per page.
  - `NextToken` (string) - Token for the next page of results.

### Response Example
```json
{
  "requestId": "0b16399216671970335563173e2340",
  "data": {
    "projects": [
      {
        "name": "odps_project",
        "owner": "ALIYUN$odps****@aliyunid.com\n",
        "type": "managed",
        "comment": "BI_Analysis",
        "defaultQuota": "os_PayAsYouGoQuota\n",
        "status": "AVAILABLE",
        "costStorage": "16489027",
        "regionId": "cn-beijing",
        "createdTime": 1704380838000,
        "properties": {
          "timezone": "Asia/Shanghai\n",
          "retentionDays": 1,
          "allowFullScan": false,
          "typeSystem": "2",
          "enableDecimal2": true,
          "sqlMeteringMax": "1500",
          "tableLifecycle": {
            "type": "optional",
            "value": "37231\n"
          },
          "encryption": {
            "enable": true,
            "algorithm": "AES256",
            "key": "dafault"
          },
          "tunnelQuota": "Default",
          "enableTunnelQuotaRoute": true,
          "externalProjectProperties": {
            "isExternalCatalogBound": "true"
          }
        },
        "securityProperties": {
          "usingAcl": true,
          "usingPolicy": true,
          "objectCreatorHasAccessPermission": true,
          "objectCreatorHasGrantPermission": true,
          "labelSecurity": false,
          "enableDownloadPrivilege": false,
          "projectProtection": {
            "protected": true,
            "exceptionPolicy": "{\n      \"Version\": \"1\",\n      \"Statement\": [\n            {\n                  \"Effect\": \"Allow\",\n                  \"Principal\": \"\",\n                  \"Action\": [\n                        \"odps:[, , ...]\"\n                  ],\n                  \"Resource\": \"acs:odps:*:\",\n                  \"Condition\": {\n                        \"StringEquals\": {\n                              \"odps:TaskType\": [\n                                    \"\"\n                              ]\n                        }\n                  }\n            }\n      ]\n}"
          }
        },
        "ipWhiteList": {
          "ipList": "10.88.111.3",
          "vpcIpList": "10.88.111.3"
        },
        "saleTag": {
          "resourceId": "b7afb7d1-****-****-****-c393669c307b\n",
          "resourceType": "PayAsYouGo\n"
        },
        "threeTierModel": true
      }
    ],
    "marker": "cHlvZHBzX3VkZl8xMDExNV8xNDU3NDI4NDkzKg==",
    "maxItem": 10,
    "NextToken": "AAAAAV3MpHK1AP0pfERHZN5pu6kvikyUl3ChyRxN+qLPvtOb"
  }
}
```
```

--------------------------------

### List MaxCompute Projects using CLI

Source: https://help.aliyun.com/zh/maxcompute/user-guide/cli-integration-example

Execute a command to retrieve a list of MaxCompute projects. This example shows how to specify tenant ID and quota name, and set the Content-Type header for the API call.

```bash
aliyun maxcompute GET /api/v1/projects --tenantId '5495****3697' --quotaName 'aliyun_5495***3697' --header "Content-Type=application/json;"
```

```json
{
  "requestId": "0b16399216671970335563173e2340",
  "data": {
    "projects": [
      {
        "name": "odps_project",
        "owner": "ALIYUN$odps****@aliyunid.com\n",
        "type": "managed",
        "comment": "BI_Analysis",
        "defaultQuota": "os_PayAsYouGoQuota\n",
        "status": "AVAILABLE",
        "costStorage": "16489027",
        "regionId": "cn-beijing",
        "createdTime": 1704380838000,
        "properties": {
          "timezone": "Asia/Shanghai\n",
          "retentionDays": 1,
          "allowFullScan": false,
          "typeSystem": "2",
          "enableDecimal2": true,
          "sqlMeteringMax": "1500",
          "tableLifecycle": {
            "type": "optional",
            "value": "37231\n"
          },
          "encryption": {
            "enable": true,
            "algorithm": "AES256",
            "key": "dafault"
          },
          "tunnelQuota": "Default",
          "enableTunnelQuotaRoute": true,
          "externalProjectProperties": {
            "isExternalCatalogBound": "true"
          }
        },
        "securityProperties": {
          "usingAcl": true,
          "usingPolicy": true,
          "objectCreatorHasAccessPermission": true,
          "objectCreatorHasGrantPermission": true,
          "labelSecurity": false,
          "enableDownloadPrivilege": false,
          "projectProtection": {
            "protected": true,
            "exceptionPolicy": "{\n      \"Version\": \"1\",\n      \"Statement\": [\n            {\n                  \"Effect\": \"Allow\",\n                  \"Principal\": \"\",\n                  \"Action\": [\n                        \"odps:[, , ...]\"\n                  ],\n                  \"Resource\": \"acs:odps:*:\",\n                  \"Condition\": {\n                        \"StringEquals\": {\n                              \"odps:TaskType\": [\n                                    \"\"\n                              ]\n                        }\n                  }\n            }\n      ]\n}"
          }
        },
        "ipWhiteList": {
          "ipList": "10.88.111.3",
          "vpcIpList": "10.88.111.3"
        },
        "saleTag": {
          "resourceId": "b7afb7d1-****-****-****-c393669c307b\n",
          "resourceType": "PayAsYouGo\n"
        },
        "threeTierModel": true
      }
    ],
    "marker": "cHlvZHBzX3VkZl8xMDExNV8xNDU3NDI4NDkzKg==",
    "maxItem": 10,
    "NextToken": "AAAAAV3MpHK1AP0pfERHZN5pu6kvikyUl3ChyRxN+qLPvtOb"
  }
}
```

--------------------------------

### Get MaxCompute OpenAPI List with CLI

Source: https://help.aliyun.com/zh/maxcompute/user-guide/cli-integration-example

Use the `--help` option to list all supported MaxCompute OpenAPI commands available via the Alibaba Cloud CLI. This is useful for discovering available operations.

```bash
aliyun maxcompute --help
```

--------------------------------

### List MaxCompute OpenAPI Commands

Source: https://help.aliyun.com/zh/maxcompute/user-guide/cli-integration-example

This command demonstrates how to use the `--help` option with the `aliyun maxcompute` command to get a list of all supported MaxCompute OpenAPI operations available through the CLI.

```APIDOC
## List MaxCompute OpenAPI Commands

### Description
This command retrieves a list of all available MaxCompute OpenAPI operations that can be invoked using the Alibaba Cloud CLI. It's useful for discovering the full range of functionalities accessible via the CLI.

### Method
CLI Command

### Endpoint
N/A

### Parameters
#### CLI Options
- `--help` (flag) - Displays help information for the `aliyun maxcompute` command, including a list of subcommands and available operations.

### Request Example
```bash
ali yun maxcompute --help
```

### Response
#### Success Response
Output will list available MaxCompute commands and their descriptions.
```

--------------------------------

### MATERIALIZE CTE Usage Example

Source: https://help.aliyun.com/zh/maxcompute/user-guide/common-table-expressions

Shows how to use the MATERIALIZE HINT to cache CTE computation results. This avoids re-computation and improves performance for subsequent references.

```sql
WITH 
  v1 AS (SELECT /*+ MATERIALIZE */ SIN(1.0) AS a) 
SELECT a FROM v1 UNION ALL SELECT a FROM v1;
```

--------------------------------

### Non-Recursive CTE Expansion Example

Source: https://help.aliyun.com/zh/maxcompute/user-guide/common-table-expressions

Demonstrates how non-recursive CTEs are expanded during query planning. In this case, the function SIN(1.0) is executed twice.

```sql
WITH
  v1 AS (SELECT SIN(1.0) AS a) 
SELECT a FROM v1 UNION ALL SELECT a FROM v1;
```

```sql
SELECT a FROM (SELECT SIN(1.0) AS a) 
UNION ALL 
SELECT a FROM (SELECT SIN(1.0) AS a);
```

--------------------------------

### Show Tables using odpscmd

Source: https://help.aliyun.com/zh/maxcompute/user-guide/connect-using-the-cloud-command-line-odpscmd

Execute this command to view all tables within the current MaxCompute project. This is useful for verifying table creation or listing existing tables.

```sql
SHOW TABLES;
```

--------------------------------

### Create and Populate Employees Table

Source: https://help.aliyun.com/zh/maxcompute/user-guide/common-table-expressions

Creates an 'employees' table to store employee-manager relationships and inserts sample data. This table serves as the base for demonstrating hierarchical queries.

```sql
CREATE TABLE employees(name STRING, boss_name STRING);
INSERT INTO TABLE employees VALUES 
  ('zhang_3',   null),
  ('li_4',      'zhang_3'),
  ('wang_5',    'zhang_3'),
  ('zhao_6',    'li_4'),
  ('qian_7',    'wang_5');
```

--------------------------------

### Build Company Hierarchy with Recursive CTE

Source: https://help.aliyun.com/zh/maxcompute/user-guide/common-table-expressions

Constructs a 'company_hierarchy' recursive CTE to represent the organizational structure from the 'employees' table. It calculates the level of each employee in the hierarchy, starting with level 0 for top-level managers.

```sql
WITH RECURSIVE company_hierarchy(name, boss_name, level) AS (
    SELECT name, boss_name, 0L FROM employees WHERE boss_name IS NULL 
    UNION ALL 
    SELECT e.name, e.boss_name, h.level+1 FROM employees e JOIN company_hierarchy h WHERE e.boss_name = h.name
  )
SELECT * FROM company_hierarchy ORDER BY level, boss_name, name LIMIT 1000;
```

--------------------------------

### Non-Recursive CTE Usage Example

Source: https://help.aliyun.com/zh/maxcompute/user-guide/common-table-expressions

Demonstrates how to refactor a complex query with repeated subqueries into a more readable and maintainable version using non-recursive CTEs. CTEs allow subqueries to be defined once and reused throughout the statement.

```sql
WITH 
  a AS (SELECT * FROM src WHERE key IS NOT NULL),
  b AS (SELECT * FROM src2 WHERE value > 0),
  c AS (SELECT * FROM src3 WHERE value > 0),
  d AS (SELECT a.key, b.value FROM a JOIN b ON a.key=b.key),
  e AS (SELECT a.key, c.value FROM a LEFT OUTER JOIN c ON a.key=c.key AND c.key IS NOT NULL)
INSERT OVERWRITE TABLE srcp PARTITION (p='abc') 
SELECT * FROM d UNION ALL SELECT * FROM e;
```

--------------------------------

### View Project Data Type Version

Source: https://help.aliyun.com/zh/maxcompute/user-guide/data-type-editions

Execute this command in the MaxCompute client to view the current project's properties, including data type version settings.

```sql
setproject;
```