MaxCompute:Connect to MaxCompute with odpscmd

Billing

Using the MaxCompute client to connect to a project is free of charge, but operations performed through the client are subject to MaxCompute billing. For example, submitting an SQL query consumes compute resources, and writing data uses storage space. This results in compute and storage costs. For more information about MaxCompute billing, see Billable items and billing methods.

Install and configure odpscmd

odpscmd v0.27.0 and later support the MaxCompute 2.0 data types. We recommend that you use these data types. For a list of supported data types, see Data Types (V2.0).

Procedure

1. Download the odpscmd installation package (GitHub).

   Note

   • Follow the link to the release page and download the latest version of the odpscmd installation package (odpscmd_public.zip).

   • If you cannot download the package from the GitHub link, try downloading the odpscmd installation package (OSS). If you have issues accessing GitHub, we recommend that you search for solutions online.

2. Unzip the downloaded package. The extracted directory contains the bin, conf, lib, and plugins folders.

3. Go to the conf folder and configure the odps_config.ini file.

   In the odps_config.ini file, the number sign (#) indicates a comment. The following table describes the parameters.

   Parameter	Required	Description	Example
   project_name	Yes	The name of the destination MaxCompute project. If you created a workspace in standard mode, distinguish between the project names for the production environment and the development environment (_dev) when you configure the project_name parameter. For more information, see Differences between workspace modes. Log in to the MaxCompute console and select a region in the upper-left corner. In the left-side navigation pane, choose Manage Configurations > Projects. On the Projects page, view your MaxCompute project name.	doc_test_dev
   access_id	Yes	The AccessKey ID of your Alibaba Cloud account or RAM user. Obtain the AccessKey ID from the AccessKey Management page.	N/A
   access_key	Yes	The AccessKey Secret that corresponds to the AccessKey ID.	N/A
   end_point	Yes	The endpoint of the MaxCompute service. You must configure the endpoint based on the region where your MaxCompute project is located and the network connection type. For a list of endpoints for different regions and network types, see Endpoints. Important An endpoint connects to the MaxCompute service, while a tunnel endpoint connects to the MaxCompute Tunnel service. Specify the endpoint for the MaxCompute service here. An incorrect endpoint configuration causes an access error.	http://service.cn-hangzhou.maxcompute.aliyun.com/api
   log_view_host	No	The LogView URL. We recommend that you configure this parameter. If you do not configure this parameter, you cannot quickly identify the cause of a job failure. Use this URL to view detailed job runtime information and troubleshoot errors. The fixed value is http://logview.odps.aliyun.com.	http://logview.odps.aliyun.com
   https_check	No	Specifies whether to enable HTTPS to encrypt requests for accessing the MaxCompute project. Valid values: True: Enables HTTPS. False: Uses HTTP. The default value is False.	True
   data_size_confirm	No	The maximum input data size in GB. This value has no upper limit. Recommended value: 100.	100
   update_url	No	This parameter is reserved for future use.	N/A
   use_instance_tunnel	No	Specifies whether to use InstanceTunnel to download SQL execution results. Valid values: True: Use InstanceTunnel to download SQL execution results. False: Do not use InstanceTunnel to download SQL execution results. The default value is False.	True
   instance_tunnel_max_record	No	The maximum number of records in an SQL execution result. If use_instance_tunnel is set to True, you must configure this parameter. The maximum value is 10,000.	10000
   tunnel_endpoint	No	The public endpoint for the Tunnel service. If you do not configure this parameter, Tunnel automatically routes requests to the tunnel endpoint that corresponds to the network of the MaxCompute service. If you configure a tunnel endpoint, Tunnel uses that value and does not perform automatic routing. For a list of tunnel endpoints for different regions and network types, see Endpoints.	http://dt.cn-hangzhou.maxcompute.aliyun.com
   set.<key>	No	Sets a property for the MaxCompute project. For more information about properties, see property list.	set.odps.sql.decimal.odps2=true

   Ensure the preceding information is configured correctly. Misconfigurations can cause project connection failures.

Start the MaxCompute client

1. Create a MaxCompute project.

2. If you use the MaxCompute client as a RAM user, use your Alibaba Cloud account to add the RAM user to the target MaxCompute project. For more information about adding users, see Grant permissions to other users.

3. Use one of the following methods to start the MaxCompute client:

   Script file

   In the bin directory of your MaxCompute client installation, double-click odpscmd.bat (on Windows) or odpscmd (on macOS) to start the MaxCompute client. The following output indicates a successful connection to the MaxCompute project.

   odpscmd
   Aliyun ODPS Command Line Tool
   Version 0.4xxx
   @Copyright 2020 Alibaba Cloud Computing Co., Ltd. All rights reserved.
   Connecting to http://service.xxx.maxcompute.aliyun.com/api, project: xxx
   Executing predefined SET command: SET odps.sql.hive.compatible=true
   OK
   Endpoint: http://service.xxx.maxcompute.aliyun.com/api
   Project: xxx
   Schema: default
   Quota: default in region N/A
   Timezone: Asia/Shanghai
   Connected!

   Command-line window

   In a command-line window, navigate to the bin directory of your MaxCompute client installation. Run odpscmd on Windows or sh odpscmd on Linux or macOS to start the MaxCompute client. A success message indicates that the client has connected to the MaxCompute project.

   Note

   On Ubuntu, sh odpscmd returns an error. Use ./odpscmd instead.

   When you start the MaxCompute client from a command-line window, you can specify startup parameters to run commands. For more information, see Startup parameters.

MaxCompute client operations

...\odpscmd\bin>odpscmd -h

odps@project_name>whoami;

odps@project_name>quit;
-- The following command is equivalent.
odps@project_name>q;

Related documents

After you log in to the MaxCompute client, you can execute SQL commands in a MaxCompute project. For more information, see Use the MaxCompute client.

For details about the command syntax of the MaxCompute client, see Command reference or SQL commands and functions.

FAQ

After configuring the odps_config.ini file and starting the MaxCompute client, you may encounter the following common errors:

Startup parameters

Usage: odpscmd [OPTION]...
where options include:
    --help                                  (-h)for help
    --config=<config_file>                  specify another config file
    --project=<prj_name>                    use project
    --endpoint=<http://host:port>           set endpoint
    -k <n>                                  will skip begining queries and start from specified position
    -r <n>                                  set retry times
    -f <"file_path;">                       execute command in file
    -e <"command;[command;]...">            execute command, include sql command

On the shell or Windows command line, use odpscmd -e <SQL_statement> to capture dynamic return values in a shell variable for subsequent jobs. In this scenario, the return value must not contain extra information, such as runtime details or a header. To facilitate shell scripting, set the use_instance_tunnel parameter in the odps_config.ini file to false to disable Instance Tunnel. Then, run the set odps.sql.select.output.format={"needHeader":false,"fieldDelim":" "}; command to suppress the header.

--Windows command line:
...\odpscmd\bin>odpscmd -e "set odps.sql.select.output.format={""needHeader"":false,""fieldDelim"":"" ""};select * from noheader;" >D:\test.txt
--The result is saved to D:\test.txt.
--Shell:
/Users/.../odpscmd/bin/odpscmd -e "set odps.sql.select.output.format={\"needHeader\":false,\"fieldDelim\":\"\"};select * from noheader;" >/Users/A/temp/test.txt 
--The result is saved to /Users/A/temp/test.txt.
--Output:
1
2
3

Version history

The following table describes recent updates to odpscmd. For more details, click the link for a specific version.

For a complete list of odpscmd versions and release notes, see GitHub.