Add Support for BigQuery Table and Column Descriptions

[ergut]

Currently, our MCP server shows basic schema information but misses out on the rich metadata that BigQuery maintains about tables and columns. Let's enhance the schema information to include these descriptions, making it easier for LLMs to understand the context and meaning of the data they're working with.

Current Behavior

• Schema endpoint returns only basic field information (name, type)
• Table descriptions and column descriptions are not accessible
• LLMs have limited context about the meaning of tables and fields

Proposed Changes

1. Update the ReadResourceRequestSchema handler to include:

   • Table description from table.getMetadata()
   • Column descriptions from the schema metadata
   • Last modified time and other relevant table metadata

2. Modify the schema JSON structure to include:

{
  "tableMetadata": {
    "description": "string",
    "lastModifiedTime": "string",
    "location": "string",
    "type": "string"
  },
  "fields": [
    {
      "name": "string",
      "type": "string",
      "description": "string",
      "mode": "string"
    }
  ]
}

3. Update the resource listing to indicate if a table has a description

Implementation Notes

• We can get table descriptions using the existing getMetadata() call
• Column descriptions are part of the schema metadata
• Need to handle cases where descriptions are missing
• Should maintain backward compatibility with existing schema format

Example Code Snippet

const [metadata] = await table.getMetadata();
return {
  contents: [
    {
      uri: request.params.uri,
      mimeType: "application/json",
      text: JSON.stringify({
        tableMetadata: {
          description: metadata.description,
          lastModifiedTime: metadata.lastModifiedTime,
          location: metadata.location,
          type: metadata.type
        },
        fields: metadata.schema.fields.map(field => ({
          ...field,
          description: field.description || null
        }))
      }, null, 2)
    }
  ]
};

Benefits

• LLMs get richer context about data meaning
• Easier for users to understand table/column purposes
• Maintains existing query patterns
• Helpful for data discovery and documentation

Testing Considerations

• Test with tables that have/don't have descriptions
• Verify backward compatibility
• Check handling of special characters in descriptions
• Validate JSON structure remains valid

Related

• Similar to how we handle view vs table type indicators
• Builds on existing metadata handling