Tunnel commands

Syntax

tunnel <subcommand> [options] [args]

Options

Available subcommands:
    upload (u)
    download (d)
    resume (r)
    show (s)
    purge (p)
    help (h)
    upsert(us)

Parameters

• upload: uploads data to a MaxCompute table. You can upload files to only one table or only one partition in a table each time. For a partitioned table, you must specify the partition to which you want to upload data. For a multi-level partitioned table, you must specify the lowest-level partition.

  -- Uploads data from log.txt (located in the bin directory of the MaxCompute client) to the partition p1="b1",p2="b2" of the two-level partitioned table test_table in the project test_project.
  tunnel upload log.txt test_project.test_table/p1="b1",p2="b2";
  
  -- Uploads data from log.txt to test_table. The --scan=true parameter validates the data in log.txt against the schema of test_table. If the data does not conform to the schema, an error is reported and the upload is aborted.
  tunnel upload  log.txt  test_table --scan=true;
  
  -- Uploads data from log.txt in a different directory to the partition p1="b1",p2="b2" of the two-level partitioned table test_table in the project test_project.
  tunnel upload D:\test\log.txt test_project.test_table/p1="b1",p2="b2";

• download: downloads data from a MaxCompute table. You can download data from only one table or partition to a single local file at a time. For a partitioned table, you must specify the partition from which you want to download data. For a multi-level partitioned table, you must specify the lowest-level partition.

  -- Downloads data from a partition of the two-level partitioned table test_table in the project test_project to the test_table.txt file.
  tunnel download  test_project.test_table/p1="b1",p2="b2"  test_table.txt;
  -- Downloads data from a partition of the two-level partitioned table test_table in the project test_project to a file in a different directory.
  tunnel download  test_project.test_table/p1="b1",p2="b2"  D:\test\test_table.txt;

• resume: resumes the transfer of files or directories. The transfer is interrupted because your network is disconnected or Tunnel is faulty. You can use this command to resume only data uploads. One data download or upload is referred to as a session. You must specify the session ID in the resume command before you run this command.

  tunnel resume;

• show: displays historical task information.

  -- Display the commands used in the last five data uploads or downloads. 
  tunnel show history -n 5;
  -- Display the logs of the last data upload or download. 
  tunnel show log;

• purge: Clears Tunnel log files from the session directory. By default, it clears logs from the last three days.

  -- Clear logs from the last five days. 
  tunnel purge 5;

• help: obtains help information.

• upsert: uses the UPDATE and INSERT semantics to write data. Only Transaction Table 2.0 tables support data writing by using the UPSERT statement.

  If no matching data is found in the destination table, new data is inserted into the table. If the data to be written already exists, data is updated in the table.

  -- Upload data in the log.txt file to the p1="b1" and p2="b2" partitions of the test_table table that has two levels of partitions in the test_project project. The log.txt file is saved in the bin directory of the MaxCompute client. 
  tunnel upsert log.txt test_project.test_table/p1="b1",p2="b2";

Description

Syntax

tunnel upload [options] <path> <[project.]table[/partition]>

Format

Available options:
 -acp,-auto-create-partition <ARG>   auto create target partition if not
                                     exists, default false
 -bs,-block-size <ARG>               block size in MiB, default 100
 -c,-charset <ARG>                   specify file charset, default ignore.
                                     set ignore to download raw data
 -cf,-csv-format <ARG>               use csv format (true|false), default
                                     false. When uploading in csv format,
                                     file splitting not supported.
 -cp,-compress <ARG>                 compress, default true
 -dbr,-discard-bad-records <ARG>     specify discard bad records
                                     action(true|false), default false
 -dfp,-date-format-pattern <ARG>     specify date format pattern, default
                                     yyyy-MM-dd HH:mm:ss
 -fd,-field-delimiter <ARG>          specify field delimiter, support
                                     unicode, eg \u0001. default ","
 -h,-header <ARG>                    if local file should have table
                                     header, default false
 -mbr,-max-bad-records <ARG>         max bad records, default 1000
 -ni,-null-indicator <ARG>           specify null indicator string,
                                     default ""(empty string)
 -ow,-overwrite <true | false>       overwrite specified table or
                                     partition, default: false
 -rd,-record-delimiter <ARG>         specify record delimiter, support
                                     unicode, eg \u0001. default "\r\n"
 -s,-scan <ARG>                      specify scan file
                                     action(true|false|only), default true
 -sd,-session-dir <ARG>              set session dir, default
                                     D:\software\odpscmd_public\plugins\dship
 -ss,-strict-schema <ARG>            specify strict schema mode. If false,
                                     extra data will be abandoned and
                                     insufficient field will be filled
                                     with null. Default true
 -t,-threads <ARG>                   number of threads, default 1
 -te,-tunnel_endpoint <ARG>          tunnel endpoint
 -time,-time <ARG>                   keep track of upload/download elapsed
                                     time or not. Default false
 -tz,-time-zone <ARG>                time zone, default local timezone:
                                     Asia/Shanghai
Example:
    tunnel upload log.txt test_project.test_table/p1="b1",p2="b2"

Parameters

• Required parameters

  • path

    The path and name of the data file to upload.

    You can save data files in the bin directory of the MaxCompute client. In this case, you must set the path parameter to a value in the File name.File name extension format. You can also save data files to another directory, such as the test folder in drive D. In this case, you must set the path parameter to a value in the D:\test\File name.File name extension format.

    Note

    In macOS, the value of the path parameter can only be an absolute path. For example, if the data files are saved in the bin directory of the MaxCompute client, you must set the path parameter to a value in the /Users/username/MaxCompute/bin/file name.file name extension format.

  • [project.]table[/partition]

    The name of the table to which you want to upload data. You must specify the lowest-level partition for a partitioned table. If the table does not belong to the current project, specify the project where the table is located.

• Optional parameters

  • -acp

    Auto-creates the target partition if it does not exist. Default value: False.

  • -bs

    The size of each data block uploaded by Tunnel. Default value: 100 MiB (1 MiB = 1024 × 1024 bytes).

  • -c

    The encoding format of the data file. By default, this parameter is not specified, and raw data is downloaded.

  • -cf

    Whether the file is in CSV format. Default value: False.

    Note

    The Upload command can be used to upload only TXT and CSV files. TXT files are uploaded by default. If you want to upload a CSV file, you must configure the -cf parameter and download the latest version of the MaxCompute client.

  • -cp

    Whether to compress the local data file before uploading to reduce network traffic. Default value: True.

  • -dbr

    Whether to discard dirty data, such as additional columns, missing columns, or mismatched column data types. Default value: False.

    • True: discards all data that does not match the table definition. By default, up to 1,000 records are discarded. To change the limit, specify the -mbr parameter.

    • False: returns an error when dirty data is detected, preventing contamination of existing table data.

  • -dfp

    The format of DATETIME data. The default format is yyyy-MM-dd HH:mm:ss. To specify millisecond precision, use yyyy-MM-dd HH:mm:ss.SSS. For more information about the DATETIME data type, see Data type version guide.

  • -fd

    The column delimiter in the local data file. Default value: comma (,).

  • -h

    Whether the data file contains a table header. Default value: False, meaning the file must not contain a header. If set to True, the system skips the first row and uploads data starting from the second row.

  • -mbr

    The maximum number of dirty data records allowed. Must be used with -dbr and takes effect only when -dbr is set to True. If the number of dirty records exceeds this value, the upload stops. Default value: 1000.

  • -ni

    The NULL data identifier. Default value: an empty string.

  • -ow

    Whether the uploaded data overwrites the existing table or partition data. Default value: False, meaning data is uploaded in append mode. The following code shows an example.

    -- Create a partitioned table.
    CREATE TABLE IF NOT EXISTS sale_detail(
          shop_name     STRING,
          customer_id   STRING,
          total_price   DOUBLE)
    PARTITIONED BY (sale_date STRING,region STRING);
    
    ALTER TABLE sale_detail ADD PARTITION (sale_date='201312', region='hangzhou');
    
    -- Prepare a local data file named data.txt in drive D with the following content:
    shopx,x_id,100
    shopy,y_id,200
    
    -- Upload data to the partitioned table.
    tunnel upload d:\data.txt sale_detail/sale_date=201312,region=hangzhou;
    
    -- Query the sale_detail table.
    SELECT * FROM sale_detail;
    
    -- The following result is returned:
    +------------+-------------+-------------+------------+------------+
    | shop_name  | customer_id | total_price | sale_date  | region     |
    +------------+-------------+-------------+------------+------------+
    | shopx      | x_id        | 100.0       | 201312     | hangzhou   |
    | shopy      | y_id        | 200.0       | 201312     | hangzhou   |
    +------------+-------------+-------------+------------+------------+
    
    -- Modify the local data.txt file with the following content:
    shopx,x_id,300
    shopy,y_id,400
    
    -- Overwrite the data in the partition.
    tunnel upload -ow true data.txt sale_detail/sale_date=201312,region=hangzhou;
    
    -- Query the sale_detail table.
    SELECT * FROM sale_detail;
    
    -- The following result is returned:
    +------------+-------------+-------------+------------+------------+
    | shop_name  | customer_id | total_price | sale_date  | region     |
    +------------+-------------+-------------+------------+------------+
    | shopx      | x_id        | 300.0       | 201312     | hangzhou   |
    | shopy      | y_id        | 400.0       | 201312     | hangzhou   |
    +------------+-------------+-------------+------------+------------+

  • -rd

    The row delimiter in the local data file. Default value: \r\n.

  • -s

    Whether to scan the local data file before uploading. Default value: True.

    • True: scans the data and starts importing only if the format is valid.

    • False: imports data without scanning.

    • Only: scans the local data without importing it.

  • -sd

    The session directory.

  • -ss

    Enables or disables strict schema mode. Default value: True. If set to False, extra data is discarded, and missing fields are filled with NULL.

  • -t

    The number of threads. Default value: 1.

  • -te

    The Tunnel endpoint.

  • -time

    Whether to track the upload elapsed time. Default value: False.

  • -tz

    The time zone. Default value: local time zone, such as Asia/Shanghai. For more information about time zones, see Time zone list.

Displays historical records.

• Syntax

  tunnel show history [-n <number>];

  -n <number>: specifies the number of historical records to display.

• Examples

  Example 1: Display history records. By default, 500 data records are saved.

  tunnel show history;

  The following result is returned:

  20230505xxxxxxxxxxxxxx0b0d5b3c  bad     'upload d:\data.txt sale_detail/sale_date=201312,region=hangzhou -dbr true -time true'
  20230505xxxxxxxxxxxxxx0ad720a3  failed  'upload d:\data.txt sale_detail/sale_date=201312,region=hangzhou -time true'
  20230505xxxxxxxxxxxxxx0ad5ca68  bad     'upload d:\data.txt sale_detail/sale_date=201312,region=hangzhou -dbr true'
  ......

  Example 2: Display the commands used in the last five data uploads or downloads.

  tunnel show history -n 5;

  The following result is returned:

  20230505xxxxxxxxxxxxxx0aa48c4b  success 'download sale_detail/sale_date=201312,region=hangzhou result.txt'
  20230505xxxxxxxxxxxxxx0aa6165c  success 'download sale_detail/sale_date=201312,region=hangzhou result.txt'
  20230505xxxxxxxxxxxxxx0af11472  failed  'upload d:\data.txt sale_detail/sale_date=201312,region=hangzhou -s false'
  20230505xxxxxxxxxxxxxx0b464374  success 'upload d:\data.txt sale_detail/sale_date=201312,region=hangzhou -s false'
  20230505xxxxxxxxxxxxxx02dbb6bd  failed  'upload d:\data.txt sale_detail/sale_date="201312",region="hangzhou" -s false'

Displays the logs of the last data upload or download.

tunnel show log;

Description

Resumes interrupted historical upload operations. Only data uploads can be resumed.

Syntax

tunnel resume <session_id> [-force];

Parameters

• session_id

  The session ID of the failed upload. Required.

• -f

  Whether to forcefully resume the operation. Omitted by default.

Example

tunnel resume 20150610xxxxxxxxxxx70a002ec60c -force;

start resume
20150610xxxxxxxxxxx70a002ec60c
Upload session: 20150610xxxxxxxxxxx70a002ec60c
Start upload:d:\data.txt
Resume 1 blocks 
2015-06-10 16:46:42     upload block: '1'
2015-06-10 16:46:42     upload block complete, blockid=1
upload complete, average speed is 0 KB/s
OK

Description

Downloads MaxCompute table data or the execution result of a specific instance to a local directory.

You must have the Download permission to use Tunnel for downloading data. If you do not have this permission, contact the project owner or a user with the Super_Administrator role for authorization. For more information, see Policy-based access control.

Syntax

odps@ project_name>tunnel help download;
usage: tunnel download [options] <[project.]table[/partition]> <path>

              download data to local file
 -c,-charset <ARG>                 specify file charset, default ignore.
                                   set ignore to download raw data
 -cf,-csv-format <ARG>             use csv format (true|false), default
                                   false. When uploading in csv format,
                                   file splitting not supported.
 -ci,-columns-index <ARG>          specify the columns index(starts from
                                   0) to download, use comma to split each
                                   index
 -cn,-columns-name <ARG>           specify the columns name to download,
                                   use comma to split each name
 -cp,-compress <ARG>               compress, default true
 -dfp,-date-format-pattern <ARG>   specify date format pattern, default
                                   yyyy-MM-dd HH:mm:ss
 -e,-exponential <ARG>             When download double values, use
                                   exponential express if necessary.
                                   Otherwise at most 20 digits will be
                                   reserved. Default false
 -fd,-field-delimiter <ARG>        specify field delimiter, support
                                   unicode, eg \u0001. default ","
 -h,-header <ARG>                  if local file should have table header,
                                   default false
    -limit <ARG>                   specify the number of records to
                                   download
 -ni,-null-indicator <ARG>         specify null indicator string, default
                                   ""(empty string)
 -rd,-record-delimiter <ARG>       specify record delimiter, support
                                   unicode, eg \u0001. default "\r\n"
 -sd,-session-dir <ARG>            set session dir, default
                                   D:\software\odpscmd_public\plugins\dship
 -t,-threads <ARG>                 number of threads, default 1
 -te,-tunnel_endpoint <ARG>        tunnel endpoint
 -time,-time <ARG>                 keep track of upload/download elapsed
                                   time or not. Default false
 -tz,-time-zone <ARG>              time zone, default local timezone:
                                   Asia/Shanghai
usage: tunnel download [options] instance://<[project/]instance_id> <path>

              download instance result to local file
 -c,-charset <ARG>                 specify file charset, default ignore.
                                   set ignore to download raw data
 -cf,-csv-format <ARG>             use csv format (true|false), default
                                   false. When uploading in csv format,
                                   file splitting not supported.
 -ci,-columns-index <ARG>          specify the columns index(starts from
                                   0) to download, use comma to split each
                                   index
 -cn,-columns-name <ARG>           specify the columns name to download,
                                   use comma to split each name
 -cp,-compress <ARG>               compress, default true
 -dfp,-date-format-pattern <ARG>   specify date format pattern, default
                                   yyyy-MM-dd HH:mm:ss
 -e,-exponential <ARG>             When download double values, use
                                   exponential express if necessary.
                                   Otherwise at most 20 digits will be
                                   reserved. Default false
 -fd,-field-delimiter <ARG>        specify field delimiter, support
                                   unicode, eg \u0001. default ","
 -h,-header <ARG>                  if local file should have table header,
                                   default false
    -limit <ARG>                   specify the number of records to
                                   download
 -ni,-null-indicator <ARG>         specify null indicator string, default
                                   ""(empty string)
 -rd,-record-delimiter <ARG>       specify record delimiter, support
                                   unicode, eg \u0001. default "\r\n"
 -sd,-session-dir <ARG>            set session dir, default
                                   D:\software\odpscmd_public\plugins\dshi
 -t,-threads <ARG>                 number of threads, default 1
 -te,-tunnel_endpoint <ARG>        tunnel endpoint
 -time,-time <ARG>                 keep track of upload/download elapsed
                                   time or not. Default false
 -tz,-time-zone <ARG>              time zone, default local timezone:
                                   Asia/Shanghai
Example:
    tunnel download test_project.test_table/p1="b1",p2="b2" log.txt // Download data from a specific table.
    tunnel download instance://test_project/test_instance log.txt   // Download the execution result of a specific instance.

Parameters

• Required parameters

  • path

    The path to save the downloaded data file.

    You can save data files in the bin directory of the MaxCompute client. In this case, you must set path to a value in the File name.File name extension format. You can also save data files to another directory, such as the test folder in drive D. In this case, you must set path to a value in the D:\test\File name.File name extension format.

  • [project.]table[/partition]

    The name of the table to download. You must specify the lowest-level partition for a partitioned table. If the table does not belong to the current project, specify the project where the table is located.

  • [project/]instance_id

    The ID of an instance whose execution result you want to download.

• Optional parameters

  • -c

    The encoding format of the local data file. This parameter is omitted by default.

  • -cf

    Whether the file is in CSV format. Default value: False.

    Note

    Only TXT and CSV files can be downloaded. TXT files are downloaded by default. If you want to download a CSV file, you must set the -cf parameter to true and download the latest version of the MaxCompute client. After you set the -cf parameter to true, the file delimiter can only be commas (,). In this case, the delimiter specified by the -fd parameter does not take effect.

  • -ci

    The indexes of the columns to download. Column indexes start from 0. Separate multiple indexes with commas (,).

  • -cn

    The names of the columns to download. Separate multiple column names with commas (,).

  • -cp

    Whether to compress data during download to reduce network traffic. Default value: True.

  • -dfp

    The format of DATETIME data. The default format is yyyy-MM-dd HH:mm:ss.

  • -e

    Whether to use exponential notation for DOUBLE values. If disabled, up to 20 digits are retained. Default value: False.

  • -fd

    The column delimiter for the local data file. Default value: comma (,).

  • -h

    Whether the downloaded data file includes a table header. Default value: False, meaning no header is included. If set to True, the file includes a header row.

    Note

    -h=true and threads>1 cannot be used together. threads>1 indicates that the number of threads is greater than 1.

  • -limit

    The number of records to download.

  • -ni

    The NULL data identifier. Default value: an empty string.

  • -rd

    The row delimiter in the local data file. Default value: \r\n.

  • -sd

    The session directory.

  • -t

    The number of threads. Default value: 1.

  • -te

    The Tunnel endpoint.

  • -time

    Whether to track the download elapsed time. Default value: False.

  • -tz

    The time zone. The local time zone is used by default, such as Asia/Shanghai.

Description

Clears Tunnel log files from the session directory.

Syntax

tunnel purge [n];

Parameters

n: The retention period in days for tunnel historical logs. Default: 3.

The following table describes the supported data types.

"yyyyMMddHHmmss": For example, "20140209101000" is used.
"yyyy-MM-dd HH:mm:ss" (default format): For example, "2014-02-09 10:10:00" is used.
"MM/dd/yyyy": For example, "09/01/2014" is used.

tunnel upload log.txt test_table -dfp "yyyy-MM-dd HH:mm:ss"

Null: each data type can have null values.

• By default, a blank string indicates a null value.

• Use -null-indicator to specify a custom null string.

  tunnel upload log.txt test_table -ni "NULL"

Encoding format: you can specify the file encoding format. Default value: UTF-8.

tunnel upload log.txt test_table -c "gbk"

Delimiter: Tunnel commands support custom delimiters. Use -record-delimiter for row delimiters and -field-delimiter for column delimiters.

• A row or column delimiter can contain multiple characters.

• A column delimiter cannot contain a row delimiter.

• Only the following escape character delimiters are supported in the command line: \r, \n, and \t.

tunnel upload log.txt test_table -fd "||" -rd "\r\n"