base_tableAffinity
Analyze table usage patterns to identify relationships between database tables, supporting SQL query generation and metadata extraction for Teradata databases.
Instructions
Get tables commonly used together by database users, this is helpful to infer relationships between tables via SQLAlchemy, bind parameters if provided (prepared SQL), and return the fully rendered SQL (with literals) in metadata.
Arguments: database_name - Database name object_name - table or view name
Returns: ResponseType: formatted response with query results + metadata
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| database_name | Yes | ||
| obj_name | Yes |
Implementation Reference
- The handler function implementing the 'base_tableAffinity' tool. It executes a SQL query against DBC.DBQLObjTbl to identify tables frequently queried together with the specified database and object, providing affinity information including query counts and timestamps.def handle_base_tableAffinity(conn: TeradataConnection, database_name: str, obj_name: str, *args, **kwargs): """ Get tables commonly used together by database users, this is helpful to infer relationships between tables via SQLAlchemy, bind parameters if provided (prepared SQL), and return the fully rendered SQL (with literals) in metadata. Arguments: database_name - Database name object_name - table or view name Returns: ResponseType: formatted response with query results + metadata """ logger.debug(f"Tool: handle_base_tableAffinity: Args: database_name: {database_name}, obj_name: {obj_name}") table_affiity_sql=""" LOCKING ROW for ACCESS SELECT TRIM(QTU2.DatabaseName) AS "DatabaseName" , TRIM(QTU2.TableName) AS "TableName" , COUNT(DISTINCT QTU1.QueryID) AS "QueryCount" , (current_timestamp - min(QTU2.CollectTimeStamp)) day(4) as "FirstQueryDaysAgo" , (current_timestamp - max(QTU2.CollectTimeStamp)) day(4) as "LastQueryDaysAgo" FROM ( SELECT objectdatabasename AS DatabaseName , ObjectTableName AS TableName , QueryId FROM DBC.DBQLObjTbl /* for DBC */ WHERE Objecttype in ('Tab', 'Viw') AND ObjectTableName = '{table_name}' AND objectdatabasename = '{database_name}' AND ObjectTableName IS NOT NULL AND ObjectColumnName IS NULL -- AND LogDate BETWEEN '2017-01-01' AND '2017-08-01' /* uncomment for PDCR */ -- AND LogDate BETWEEN current_date - 90 AND current_date - 1 /* uncomment for PDCR */ GROUP BY 1,2,3 ) AS QTU1 INNER JOIN ( SELECT objectdatabasename AS DatabaseName , ObjectTableName AS TableName , QueryId , CollectTimeStamp FROM DBC.DBQLObjTbl /* for DBC */ WHERE Objecttype in ('Tab', 'Viw') AND ObjectTableName IS NOT NULL AND ObjectColumnName IS NULL GROUP BY 1,2,3, 4 ) AS QTU2 ON QTU1.QueryID=QTU2.QueryID INNER JOIN DBC.DBQLogTbl QU /* uncomment for DBC */ -- INNER JOIN DBC.DBQLogTbl QU /* uncomment for PDCR */ ON QTU1.QueryID=QU.QueryID WHERE (TRIM(QTU2.TableName) <> TRIM(QTU1.TableName) OR TRIM(QTU2.DatabaseName) <> TRIM(QTU1.DatabaseName)) AND (QU.AMPCPUTime + QU.ParserCPUTime) > 0 GROUP BY 1,2 ORDER BY 3 DESC, 5 DESC -- having "QueryCount">10 ; """ with conn.cursor() as cur: rows = cur.execute(table_affiity_sql.format(table_name=obj_name, database_name=database_name)) data = rows_to_json(cur.description, rows.fetchall()) if len(data): affinity_info=f'This data contains the list of tables most commonly queried alongside object {database_name}.{obj_name}' else: affinity_info=f'Object {database_name}.{obj_name} is not often queried with any other table or queried at all, try other ways to infer its relationships.' metadata = { "tool_name": "handle_base_tableAffinity", "database": database_name, "object": obj_name, "table_count": len(data), "comment": affinity_info } logger.debug(f"Tool: handle_base_tableAffinity: metadata: {metadata}") return create_response(data, metadata)