Support SHOW TABLES LIKE for discovering $-suffix metadata tables in the FileConnector

[srsuryadev]

The File connector (introduced in #1435) allows users to query raw files of any format or type or modalities and their internal metadata via SQL using $-suffix table variants (e.g., $encodings, $fields). However, there is currently no way for users to discover which metadata table variants are available for a given file.

Since the File connector is catalog-less (table names are file paths), SHOW TABLES cannot enumerate files in the traditional sense.

Problem

Users must already know the exact $-suffix metadata table names to query them. There is no discoverability mechanism — if a user has a file /path/to/file.nimble, they cannot programmatically determine that /path/to/file.nimble$encodings or /path/to/file.nimble$fields exist as queryable variants.

Proposed Solution

Support a pattern like:

SHOW TABLES LIKE '/path/to/file.nimble$%'

This should return the available metadata variants for that file, e.g.:

• /path/to/file.nimble$encodings
• /path/to/file.nimble$fields

Technical Considerations

• This likely requires changes to how SHOW TABLES interacts with the listTableNames interface in the connector.
• The connector would need to recognize that the pattern targets a specific file and enumerate the available $-suffix metadata tables for it.
• Should gracefully handle cases where the base file doesn't exist or has no metadata variants.

References

• Originating discussion: feat: Add connector for querying raw files (#1435) #1435

[srsuryadev]

cc: @mbasmanova

[mbasmanova]

One way to implement this is to extend the listTableNames API in ConnectorMetadata to accept an optional prefix argument:

std::vector<std::string> listTableNames(
    const ConnectorSessionPtr& session,
    const std::string& schemaName,
    std::optional<std::string> prefix);

SHOW TABLES LIKE analyzes the pattern to extract a fixed prefix (everything before the first wildcard). If the prefix alone is equivalent to the full LIKE pattern, the connector result is final. Otherwise, SHOW TABLES applies the LIKE filter on top of the connector's prefix-filtered results.

For example:

• SHOW TABLES LIKE '/path/to/file.nimble$%' — prefix is "/path/to/file.nimble$", trailing % means the connector result is final (no additional filtering).
• SHOW TABLES LIKE 'foo_bar' — prefix is "foo", but _ is a single-char wildcard, so the LIKE filter is still applied on top of the prefix-filtered results.

CC @amitkdutta