base_tableUsage
Analyze table and view usage in Teradata databases to identify active database objects and their value through SQL execution tracking.
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
- Handler function implementing the logic for the 'base_tableUsage' tool. It executes a SQL query against Teradata's DBQL tables to compute table usage metrics including query frequency, percentage of total queries, usage category (High/Medium/Low), and recency of queries for tables in the specified database.
# Read table usage tool 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)