Use PyODPS in a local environment

Prerequisites

Before you begin, ensure you meet the following requirements:

• Python 3.6 or later is installed.

• You have an Alibaba Cloud account and an access key to connect to your MaxCompute project. We recommend using the access key of a RAM user.

Install PyODPS

1. Install PyODPS

   • Linux/macOS:

     pip3 install pyodps

   • Windows:

     pip install pyodps

   If you encounter C code compilation errors when installing dependencies such as numpy or pyarrow, your pip or setuptools version might be outdated. Upgrade them and retry the installation:

   # Linux/macOS
   pip3 install -U pip setuptools
   
   # Windows
   pip install -U pip setuptools

2. Verify the installation

   • Linux/macOS:

     python3 -c "from odps import ODPS"

   • Windows:

     python -c "from odps import ODPS"

     If no error is reported, the installation is successful. If an error occurs, see Troubleshooting.

Set environment variables

To avoid hard-coding your credentials in your code, set them as environment variables.

Log on to the RAM console, copy your access key ID and secret access key, and then configure them based on your operating system. For example, on macOS (zsh):

1. macOS (zsh):

   vim ~/.zshrc

2. Add the following lines and replace the placeholders with your actual values

   export ALIBABA_CLOUD_ACCESS_KEY_ID=<your-AccessKey-ID>
   export ALIBABA_CLOUD_ACCESS_KEY_SECRET=<your-AccessKey-secret>

3. Apply the configuration

   source ~/.zshrc

4. Verify that the environment variables are set

   echo $ALIBABA_CLOUD_ACCESS_KEY_ID
   echo $ALIBABA_CLOUD_ACCESS_KEY_SECRET

   Each command should output its corresponding value. If the output is empty, repeat steps 2 and 3.

For details on setting environment variables on Linux, macOS, and Windows, see Set the Alibaba Cloud access key in an environment variable.

Initialize the ODPS entry point

The ODPS class is the entry point for all PyODPS operations. It must be initialized with credential and project information before you call any other API.

Before you run the code, make sure the ALIBABA_CLOUD_ACCESS_KEY_ID and ALIBABA_CLOUD_ACCESS_KEY_SECRET environment variables are set. You can also explicitly specify your access key, but this is not recommended because hard-coding credentials in source code poses a security risk.

To obtain an access key ID and a secret access key, see Obtain an access key ID and a secret access key.

import os
from odps import ODPS
from odps.accounts import StsAccount
# Make sure the ALIBABA_CLOUD_ACCESS_KEY_ID environment variable is set to your access key ID,
# the ALIBABA_CLOUD_ACCESS_KEY_SECRET environment variable is set to your secret access key,
# and the ALIBABA_CLOUD_STS_TOKEN environment variable is set to your STS token.
# For security, avoid hard-coding your access key ID or secret access key.
account = StsAccount(
    os.getenv('ALIBABA_CLOUD_ACCESS_KEY_ID'),
    os.getenv('ALIBABA_CLOUD_ACCESS_KEY_SECRET'),
    os.getenv('ALIBABA_CLOUD_STS_TOKEN'),
)
o = ODPS(
    account=account,
    project='**your-default-project**',
    endpoint='**your-end-point**',
)

After completing these configurations, you can use PyODPS in your local environment. For more information, see Overview of basic operations.

Process data

# Use the dedicated method to create a table
o.create_table('my_t', 'num bigint, id string', if_not_exists=True)

# Run a SELECT query
result = o.execute_sql('SELECT * FROM pyodps_iris LIMIT 3')
with result.open_reader() as reader:
    for record in reader:
        print(record)

o.execute_sql_interactive('SELECT * FROM dual', fallback='all')

# Fall back only when resources are insufficient or the scenario is unsupported
o.execute_sql_interactive('SELECT * FROM dual', fallback="noresource,unsupported")

from odps.df import DataFrame

# Load the pyodps_iris table into a DataFrame
iris = DataFrame(o.get_table('pyodps_iris'))

# Filter rows and print the results. The .execute() method triggers the actual computation.
for record in iris[iris.sepalwidth < 3].execute():
    print(record)

from odps import options
options.verbose = True

o.execute_sql('SELECT * FROM pyodps_iris', hints={'odps.sql.mapper.split.size': 16})

from odps import options

# All subsequent execute_sql() calls will automatically include these hints
options.sql.settings = {'odps.sql.mapper.split.size': 16}
o.execute_sql('SELECT * FROM pyodps_iris')

Complete example

The following example demonstrates the complete process: connecting to MaxCompute, creating a table, writing data, reading data, querying the my_new_table table, and cleaning up resources.

This example calls table.drop() at the end, which permanently deletes the my_new_table table. To avoid data loss, do not use an existing table name, as this script drops the table. If you want to keep the table created by this script, remove the final table.drop() call.

1. Create a local file named test-pyodps-local.py.

2. Add the following code:

   import os
   from odps import ODPS
   
   o = ODPS(
       access_id=os.getenv('ALIBABA_CLOUD_ACCESS_KEY_ID'),
       secret_access_key=os.getenv('ALIBABA_CLOUD_ACCESS_KEY_SECRET'),
       project='your-default-project',
       endpoint='your-end-point',
   )
   
   table = o.create_table('my_new_table', 'num bigint, id string', if_not_exists=True)
   
   records = [[111, 'aaa'],
              [222, 'bbb'],
              [333, 'ccc'],
              [444, 'non_ascii_text']]
   o.write_table(table, records)
   
   # Read the records
   for record in o.read_table(table):
       print(record[0], record[1])
   
    # Use SQL to query the my_new_table table
   result = o.execute_sql('SELECT * FROM my_new_table;')
   
   
   print('Read data from my_new_table using open_reader:')
   
   # Print the query results
   with result.open_reader() as reader:
       for record in reader:
           print(record[0], record[1])
   
   # Drop the table to release resources
   table.drop()

3. Run the script

   python test-pyodps-local.py

   Expected output:

   111 aaa
   222 bbb
   333 ccc
   444 non_ascii_text
   Read data from my_new_table using open_reader:
   111 aaa
   222 bbb
   333 ccc
   444 non_ascii_text

Troubleshooting