利用PyODPS高效上传下载数据

工作中经常涉及到向odps（maxcompute）进行数据的上传和下载，这里基于pyodps编写了两个函数。

数据下载

针对分区表和非分区表下载到本地为csv格式

from odps import ODPS
from odps.tunnel import TableTunnel

def download_odps_table_to_csv(odps_access_id, odps_secret_access_key, project_name, endpoint, table_name, output_file, sep=',', partition_spec=None):
    """
    Download an ODPS table to a local CSV file.

    :param odps_access_id: ODPS access ID.
    :param odps_secret_access_key: ODPS secret access key.
    :param project_name: ODPS project name.
    :param endpoint: ODPS endpoint.
    :param table_name: ODPS table name.
    :param output_file: Output CSV file path.
    :param sep: CSV separator character.
    :param partition_spec: Partition specification as a dictionary, e.g., {'partition_column': 'value'}.
    """
    # 初始化ODPS入口
    o = ODPS(odps_access_id, odps_secret_access_key, project_name, endpoint=endpoint)

    # 获取表的实例
    table = o.get_table(table_name)

    # 创建TableTunnel实例
    tunnel = TableTunnel(o)

    # 创建下载会话
    if partition_spec:
        download_session = tunnel.create_download_session(project_name, table_name, partition_spec)
    else:
        download_session = tunnel.create_download_session(project_name, table_name)

    # 指定块大小，根据需要调整
    block_size = 10000

    # 打开下载会话
    with download_session.open_reader(block_size=block_size) as reader:
        # 创建CSV文件
        with open(output_file, 'w', encoding='utf-8') as f:
            # 写入表头
            headers = [col.name for col in table.schema.columns]
            f.write(sep.join(headers) + '\n')

            # 读取并写入数据
            while True:
                # 读取一个块的数据
                records = reader.read_block()
                if not records:
                    break

                # 将数据转换为字符串列表
                for record in records:
                    row = [str(value) for value in record]
                    f.write(sep.join(row) + '\n')

    print(f'下载完成，CSV文件已保存为{output_file}')

# 示例调用
download_odps_table_to_csv(
    odps_access_id='your_access_id',
    odps_secret_access_key='your_secret_access_key',
    project_name='your_project_name',
    endpoint='your_odps_endpoint',
    table_name='your_table_name',
    output_file='output.csv',
    sep=',',
    partition_spec={'partition_column': 'partition_value'}  # 对于分区表，需要指定分区信息
)

数据上传

目前很多场景下使用的是pandas对数据进行处理，所以这里假设已经将原始数据转换为了DataFrame对象。 为了方便上传~这里同意将类型转换为odps支持的string类型。

from odps import ODPS
from odps.tunnel import TableTunnel
import pandas as pd

def upload_dataframe_to_odps(odps_access_id, odps_secret_access_key, project_name, endpoint, table_name, df, overwrite=False, partition_spec=None):
    """
    Upload a pandas DataFrame to an ODPS table.

    :param odps_access_id: ODPS access ID.
    :param odps_secret_access_key: ODPS secret access key.
    :param project_name: ODPS project name.
    :param endpoint: ODPS endpoint.
    :param table_name: ODPS table name.
    :param df: pandas DataFrame to upload.
    :param overwrite: If True, overwrite the existing data. If False, append to the table.
    :param partition_spec: Partition specification as a dictionary, e.g., {'partition_column': 'value'}.
    """
    # 初始化ODPS入口
    o = ODPS(odps_access_id, odps_secret_access_key, project_name, endpoint=endpoint)
    # 获取表的实例
    table = o.get_table(table_name)
    # 将DataFrame中的所有数据转换为字符串类型
    df = df.astype(str)
    # 创建TableTunnel实例
    tunnel = TableTunnel(o)
    # 创建上传会话
    if partition_spec:
        upload_session = tunnel.create_upload_session(project_name, table_name, partition_spec, if_exists=overwrite)
    else:
        upload_session = tunnel.create_upload_session(project_name, table_name, if_exists=overwrite)
    # 打开上传会话
    with upload_session.open_writer() as writer:
        # 写入数据
        for _, row in df.iterrows():
            writer.write([row[col.name] for col in table.schema.columns])
    # 提交数据
    upload_session.commit()
    print(f'上传完成，数据已写入{table_name}表。')

# 示例调用
upload_dataframe_to_odps(
    odps_access_id='your_access_id',
    odps_secret_access_key='your_secret_access_key',
    project_name='your_project_name',
    endpoint='your_odps_endpoint',
    table_name='your_table_name',
    df=df,  # 这里是您的pandas DataFrame对象
    overwrite=False,  # 或者True，取决于您是否要覆盖数据
    partition_spec={'partition_column': 'partition_value'}  # 对于分区表，需要指定分区信息
)