Use a Python 2 UDTF to read resources from MaxCompute-MaxCompute(MaxCompute)-阿里云帮助中心

Use a Python 2 user-defined table-valued function (UDTF) to read file and table resources at query time on the MaxCompute client.

Prerequisites

Before you begin, ensure that you have:

Installed the MaxCompute client

How a Python 2 UDTF works

A Python 2 UDTF is a class that extends BaseUDTF. You implement one or both of the following methods:

Method	Required	Description
`__init__`	Optional	Runs once before processing begins. Load file and table resources here so they are read once per query, not once per input row.
`process`	Required	Called once per input row. Call `self.forward()` to emit each output row.

Resources are loaded through odps.distcache:

Function	Description
`get_cache_file(filename)`	Reads a file resource
`get_cache_table(table_name)`	Reads a table resource

Write, register, and call the UDTF

Step 1: Write the UDTF

Save the following code as py_udtf_example.py and place it in the bin folder of the MaxCompute client.

# -*- coding: utf-8 -*-
from odps.udf import annotate
from odps.udf import BaseUDTF
from odps.distcache import get_cache_file
from odps.distcache import get_cache_table

@annotate('string -> string, bigint')
class UDTFExample(BaseUDTF):
    """Read pageid and adid in the file and table to generate dict."""

    def __init__(self):
        import json
        # Load the file resource once, before processing begins
        cache_file = get_cache_file('test_json.txt')
        self.my_dict = json.load(cache_file)
        cache_file.close()
        # Load the table resource and merge it into the dict
        records = list(get_cache_table('table_resource1'))
        for record in records:
            self.my_dict[record[0]] = [record[1]]

    def process(self, pageid):
        """Emit one row per adid associated with the given pageid."""
        for adid in self.my_dict[pageid]:
            self.forward(pageid, adid)

The @annotate decorator declares the function signature: one string input column, and two output columns (string, bigint).

Resources are loaded in __init__ so they are read once per query execution, not once per input row.

Step 2: Prepare the data

Log on to the MaxCompute client and run the following statements to create the resource table and input table.

Create a resource table named table_resource1 and insert data.

create table if not exists table_resource1 (pageid string, adid int);
insert into table table_resource1 values("contact_page2",2),("contact_page3",5);

Create an internal table named tmp1 and insert data. tmp1 is the input table used in the SQL queries later.

create table if not exists tmp1 (pageid string);
insert into table tmp1 values ("front_page"),("contact_page1"),("contact_page3");

Create the file resource test_json.txt with the following content and place it in the bin folder of the MaxCompute client.
```
{"front_page":[1, 2, 3], "contact_page1":[3, 4, 5]}
```

Step 3: Upload resources and register the UDTF

Add the Python file, data file, and resource table as MaxCompute resources. For details, see Add resources.
```
add py py_udtf_example.py;
add file test_json.txt;
add table table_resource1 as table_resource1;
```

Clause	Description
`as '<file>.<ClassName>'`	Points to the handler class. For a UDTF, specify the class name (not a method name).
`using '<resources>'`	Comma-separated list of all resources the UDTF depends on.

create function my_udtf as 'py_udtf_example.UDTFExample' using 'py_udtf_example.py, test_json.txt, table_resource1';

Step 4: Call the UDTF

After the UDTF is registered, run SQL statements on the MaxCompute client to call it. All examples below query tmp1 and expand each pageid into one or more (pageid, adid) pairs.

Example 1: Direct UDTF call

select my_udtf(pageid) as (pageid, adid) from tmp1;

Result:

+---------------+------+
| pageid        | adid |
+---------------+------+
| front_page    | 1    |
| front_page    | 2    |
| front_page    | 3    |
| contact_page1 | 3    |
| contact_page1 | 4    |
| contact_page1 | 5    |
| contact_page3 | 5    |
+---------------+------+

Example 2: LATERAL VIEW

Use LATERAL VIEW to join the UDTF output back to the original table, keeping the original pageid column alongside the UDTF-generated adid.

select pageid, adid from tmp1 lateral view my_udtf(pageid) adTable as udtf_pageid, adid;

The output is the same 7 rows as Example 1. pageid comes from tmp1; adid is generated by the UDTF.

Example 3: Aggregate with LATERAL VIEW

Combine LATERAL VIEW with an aggregate function to count how many times each adid appears.

-- Count occurrences of each adid
select adid, count(1) as cnt
    from tmp1 lateral view my_udtf(pageid) adTable as udtf_pageid, adid
group by adid;

Result:

+------+-----+
| adid | cnt |
+------+-----+
| 1    | 1   |
| 2    | 1   |
| 3    | 2   |
| 4    | 1   |
| 5    | 2   |
+------+-----+