Improve Snowflake MetaData calls in the ODBC & JDBC APIs

0
votes

We have seen a number of questions on how to avoid retrieving metadata on all objects when only a specific database or database schema is specified. This helps to improve performance of metadata operations and reduce overhead, especially for Snowflake customers with very large deployments that can contain large numbers of databases, schemas, tables and views.

Snowflake data is maintained in databases, each database consists of one or more schemas. Database objects such as tables and views are logically grouped in these database schemas.

If a table, schema or database has an underscore character in it's name, the getMetaData calls need to be handled in a specific manner. The default interpretation of the underscore character in ODBC and JDBC calls will be to handle it as a wildcard character i.e. referring to a specific schema database1.schema_name will actually retrieve all metadata in database1. A similar interpretation applies to database and table naming conventions as well.

To avoid having the underscore character being used as a wildcard and retrieving metadata for all objects, a backslash "\" character needs to be used in front of the underscore character to escape the underscore being used as a wildcard character. The drivers will then correctly identify the specific path for the objects to perform the metadata call.

Anything else that should be specified?

1
snowflake-cloud-data-platform
This question doesn't actually appear to be a question, but more of a request for product improvement. Do you have a specific question that needs to be answered? – Mike Walton
Snowflake is working with some partners and customers on this. If you work at a partner or customer, feel free to contact me by putting a dot between my first and last name for the email at Snowflake. I can get you in contact with the appropriate people. Thanks. – Greg Pavlik

1 Answers

0
votes

Have you encountered before the Snowflake Session parameter CLIENT_METADATA_REQUEST_USE_CONNECTION_CTX?

From the docs:

By default, if the database and schema are not specified (i.e. you pass NULL values for both the database and schema), then these methods/functions return metadata about all databases and all schemas.

Note that retrieving metadata for all databases and all schemas can temporarily consume enough resources to impact performance.

To avoid this potential performance impact, set the CLIENT_METADATA_REQUEST_USE_CONNECTION_CTX parameter to TRUE. When the parameter is enabled, if the database and schema arguments are both NULL, these methods/functions retrieve metadata for only the current database and schema, based on the current connection context. This parameter can also affect the method/function behavior when one, but not both, of the database and schema arguments are passed.

As for the request as to how getMetData calls get handled/interpreted, this types of improvement requests are best posted in the Snowflake Ideas page as this feed is reviewed by Snowflake Product team.