base_tableAffinity
Identify tables frequently accessed together in Teradata databases to reveal relationships and optimize SQL queries with rendered SQL metadata.
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 executing the logic for the 'base_tableAffinity' tool. It executes a predefined SQL query against DBQLObjTbl and DBQLogTbl to find tables commonly queried with the given database object, formats the results, and returns them with metadata.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)