import json
from pydantic import parse_obj_as
from typing import Any, List, Dict
from aworld.replay_buffer.base import Storage, DataRow, ExpMeta, Experience
from aworld.replay_buffer.query_filter import QueryCondition, QueryBuilder
from aworld.core.common import Observation, ActionModel
from aworld.logs.util import logger
from aworld.utils.import_package import import_package
import_package("odps") # noqa
from odps import ODPS # noqa
from odps.models.record import Record # noqa
class OdpsSQLBuilder:
''' Example:
query_condition = QueryBuilder().eq("field1", "value1").and_().eq("field2", "value2")
sql_builder = OdpsSQLBuilder(query_condition)
sql = sql_builder.build_sql()
print(sql) # 输出: "field1 = 'value1' AND field2 = 'value2'"
'''
def __init__(self, query_condition: QueryCondition):
self.query_condition = query_condition
def _build_condition(self, condition: QueryCondition) -> str:
if condition is None:
return ""
if "field" in condition and "op" in condition:
field = condition["field"].split('.')[-1]
op = condition["op"]
value = condition.get("value")
if op == "eq":
return f"{field} = {self._format_value(value)}"
elif op == "ne":
return f"{field} != {self._format_value(value)}"
elif op == "gt":
return f"{field} > {self._format_value(value)}"
elif op == "gte":
return f"{field} >= {self._format_value(value)}"
elif op == "lt":
return f"{field} < {self._format_value(value)}"
elif op == "lte":
return f"{field} <= {self._format_value(value)}"
elif op == "in":
return f"{field} IN ({self._format_value(value)})"
elif op == "not_in":
return f"{field} NOT IN ({self._format_value(value)})"
elif op == "like":
return f"{field} LIKE '{value}'"
elif op == "not_like":
return f"{field} NOT LIKE '{value}'"
elif op == "is_null":
return f"{field} IS NULL"
elif op == "is_not_null":
return f"{field} IS NOT NULL"
elif "and_" in condition:
return f"({' AND '.join(self._build_condition(c) for c in condition['and_'])})"
elif "or_" in condition:
return f"({' OR '.join(self._build_condition(c) for c in condition['or_'])})"
return ""
def _format_value(self, value: Any) -> str:
if isinstance(value, str):
return f"'{value}'"
elif isinstance(value, (list, tuple)):
return ", ".join(self._format_value(v) for v in value)
return str(value)
def build_sql(self) -> str:
if not self.query_condition:
return ""
return self._build_condition(self.query_condition)
class OdpsStorage(Storage):
'''
Aliyun ODPS storage.
Table schema:
id: int
task_id: string
task_name: string
agent_id: string
step: int
execute_time: string
state: string
actions: string
reward_t: string
adv_t: string
v_t: string
'''
def __init__(self, table_name: str, project: str, endpoint: str, access_id: str, access_key: str, **kwargs):
self.table_name = table_name
self.project = project
self.endpoint = endpoint
self.access_id = access_id
self.access_key = access_key
self.kwargs = kwargs
self._init_odps()
def _init_odps(self):
self.odps = ODPS(self.access_id, self.access_key,
self.project, self.endpoint)
def _get_table(self):
return self.odps.get_table(self.table_name)
def _convert_row_to_record(self, row: DataRow) -> Record:
table = self._get_table()
record = table.new_record()
record["id"] = row.id
record["task_id"] = row.exp_meta.task_id
record["task_name"] = row.exp_meta.task_name
record["agent_id"] = row.exp_meta.agent_id
record["step"] = row.exp_meta.step
record["execute_time"] = row.exp_meta.execute_time
if row.exp_data.state:
record["state"] = row.exp_data.state.model_dump_json()
if row.exp_data.actions:
record["actions"] = "[" + ", ".join(action.model_dump_json()
for action in row.exp_data.actions) + "]"
if row.exp_data.reward_t:
record["reward_t"] = row.exp_data.reward_t
if row.exp_data.adv_t:
record["adv_t"] = row.exp_data.adv_t
if row.exp_data.v_t:
record["v_t"] = row.exp_data.v_t
return record
def _convert_record_to_row(self, record: Record) -> DataRow:
return DataRow(
id=record.id,
exp_meta=ExpMeta(
task_id=record['task_id'],
task_name=record['task_name'],
agent_id=record['agent_id'],
step=record['step'],
execute_time=record['execute_time'],
pre_agent=record['pre_agent'] if 'pre_agent' in record else None
),
exp_data=Experience(
state=parse_obj_as(Observation, json.loads(record['state'])),
actions=[parse_obj_as(ActionModel, item)
for item in json.loads(record['actions'])],
reward_t=record['reward_t'] if 'reward_t' in record else None,
adv_t=record['adv_t'] if 'adv_t' in record else None,
v_t=record['v_t'] if 'v_t' in record else None,
)
)
def _build_paginated_sql(self, page: int = None, page_size: int = None):
if page and page_size:
offset = (page - 1) * page_size
limit = page_size
return f" LIMIT {offset}, {limit}"
return ""
def _build_sql(self, query_condition: QueryCondition, page: int = None, page_size: int = None):
if not query_condition:
return f"SELECT * FROM {self.table_name}" + self._build_paginated_sql(page, page_size)
where_builder = OdpsSQLBuilder(query_condition)
sql = f"SELECT * FROM {self.table_name} WHERE {where_builder.build_sql()}" + self._build_paginated_sql(page,
page_size)
return sql
def _build_count_sql(self, query_condition: QueryCondition):
if not query_condition:
return f"SELECT count(1) as count FROM {self.table_name}"
where_builder = OdpsSQLBuilder(query_condition)
sql = f"SELECT count(1) as count FROM {self.table_name} WHERE {where_builder.build_sql()}"
return sql
def add(self, row: DataRow):
record = self._convert_row_to_record(row)
self.odps.write_table(self.table_name, [record])
def add_batch(self, rows: list[DataRow]):
records = [self._convert_row_to_record(row) for row in rows]
self.odps.write_table(self.table_name, records)
def size(self, query_condition: QueryCondition = None) -> int:
sql = self._build_count_sql(query_condition)
with self.odps.execute_sql(sql).open_reader() as reader:
return reader[0]["count"]
def get_all(self, query_condition: QueryCondition = None) -> list[DataRow]:
sql = self._build_sql(query_condition)
logger.info(f"get_all sql: {sql}")
with self.odps.execute_sql(sql).open_reader(tunnel=True) as reader:
rows = []
for record in reader:
rows.append(self._convert_record_to_row(record))
return rows
def get_paginated(self, page: int, page_size: int, query_condition: QueryCondition = None) -> List[DataRow]:
sql = self._build_sql(query_condition, page, page_size)
logger.info(f"get_paginated sql: {sql}")
with self.odps.execute_sql(sql).open_reader(tunnel=True) as reader:
rows = []
for record in reader:
rows.append(self._convert_record_to_row(record))
return rows
def get_by_task_id(self, task_id: str) -> List[DataRow]:
query_condition = QueryBuilder().eq("task_id", task_id).build()
return self.get_all(query_condition)
def get_bacth_by_task_ids(self, task_ids: List[str]) -> Dict[str, List[DataRow]]:
query_condition = QueryBuilder().in_("task_id", task_ids).build()
sql = self._build_sql(query_condition)
logger.info(f"get_bacth_by_task_ids sql: {sql}")
result = {}
with self.odps.execute_sql(sql).open_reader(tunnel=True) as reader:
for record in reader:
row = self._convert_record_to_row(record)
if row.exp_meta.task_id not in result:
result[row.exp_meta.task_id] = []
result[row.exp_meta.task_id].append(row)
return result