base_tableUsage
Measure table and view usage by users in a Teradata schema to identify actively used database objects and analyze SQL query patterns with rendered SQL metadata.
Instructions
Measure the usage of a table and views by users in a given schema, this is helpful to infer what database objects are most actively used or drive most value via SQLAlchemy, bind parameters if provided (prepared SQL), and return the fully rendered SQL (with literals) in metadata.
Arguments: database_name - Database name
Returns: ResponseType: formatted response with query results + metadata
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| database_name | No |
Implementation Reference
- The main handler function for the 'base_tableUsage' tool (internally named 'handle_base_tableUsage'). It executes a SQL query against DBQLObjTbl and DBQLogTbl to compute table usage statistics (query count, frequency, recency) for tables in the specified database. Returns JSON-formatted results with metadata.def handle_base_tableUsage(conn: TeradataConnection, database_name: str | None = None, *args, **kwargs): """ Measure the usage of a table and views by users in a given schema, this is helpful to infer what database objects are most actively used or drive most value via SQLAlchemy, bind parameters if provided (prepared SQL), and return the fully rendered SQL (with literals) in metadata. Arguments: database_name - Database name Returns: ResponseType: formatted response with query results + metadata """ logger.debug("Tool: handle_base_tableUsage: Args: database_name:") database_name_filter = f"AND objectdatabasename = '{database_name}'" if database_name else "" table_usage_sql=""" LOCKING ROW for ACCESS sel DatabaseName ,TableName ,Weight as "QueryCount" ,100*"Weight" / sum("Weight") over(partition by 1) PercentTotal ,case when PercentTotal >=10 then 'High' when PercentTotal >=5 then 'Medium' else 'Low' end (char(6)) usage_freq ,FirstQueryDaysAgo ,LastQueryDaysAgo from ( SELECT TRIM(QTU1.TableName) AS "TableName" , TRIM(QTU1.DatabaseName) AS "DatabaseName" ,max((current_timestamp - CollectTimeStamp) day(4)) as "FirstQueryDaysAgo" ,min((current_timestamp - CollectTimeStamp) day(4)) as "LastQueryDaysAgo" , COUNT(DISTINCT QTU1.QueryID) as "Weight" FROM ( SELECT objectdatabasename AS DatabaseName , ObjectTableName AS TableName , QueryId FROM DBC.DBQLObjTbl /* uncomment for DBC */ WHERE Objecttype in ('Tab', 'Viw') {database_name_filter} 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 DBC.DBQLogTbl QU /* uncomment for DBC */ ON QTU1.QueryID=QU.QueryID AND (QU.AMPCPUTime + QU.ParserCPUTime) > 0 GROUP BY 1,2 ) a order by PercentTotal desc qualify PercentTotal>0 ; """ with conn.cursor() as cur: rows = cur.execute(table_usage_sql.format(database_name_filter=database_name_filter)) data = rows_to_json(cur.description, rows.fetchall()) if len(data): info=f'This data contains the list of tables most frequently queried objects in database schema {database_name}' else: info=f'No tables have recently been queried in the database schema {database_name}.' metadata = { "tool_name": "handle_base_tableUsage", "database": database_name, "table_count": len(data), "comment": info } logger.debug(f"Tool: handle_base_tableUsage: metadata: {metadata}") return create_response(data, metadata)