SNOW-1358461: Issue with metadata query for columns

Question

SNOW-1358461: Issue with metadata query for columns

Closed this issue 4 months ago · 8 comments

I am utilizing the getColumns method of SnowflakeDatabaseMetaData class. As I debug I found that internally it makes query:
show /* JDBC:DatabaseMetaData.getColumns() */ columns in database "MY_DB_NAME",

If my schema or table name includes underscore (_) like A_BC in the name because it checks for wildcard patterns internally, and if it finds any, it creates a query for the entire metadata instead of just the table.
like: If there is _ in Schema name
show /* JDBC:DatabaseMetaData.getColumns() */ columns in database " ABC_DATABASE"

Expecting:
If I am providing all details like Database, Schema, and Table name with or without underscore (_) like A_BC then it should create a query like:
show /* JDBC:DatabaseMetaData.getColumns() */ columns in table "MY_DB_NAME"."MY_SCHEMA_NAME"."MY_TABLE_NAME"

Library:

 <dependency>
       <groupId>net.snowflake</groupId>
       <artifactId>snowflake-jdbc</artifactId>
       <version>3.13.6</version>
</dependency>

Responsible code:

 String showColumnsCommand = "show /* JDBC:DatabaseMetaData.getColumns() */ columns";

    if (columnNamePattern != null
        && !columnNamePattern.isEmpty()
        && !columnNamePattern.trim().equals("%")
        && !columnNamePattern.trim().equals(".*")) {
      showColumnsCommand += " like '" + escapeSingleQuoteForLikeCommand(columnNamePattern) + "'";
    }

    if (catalog == null) {
      showColumnsCommand += " in account";
    } else if (catalog.isEmpty()) {
      return SnowflakeDatabaseMetaDataResultSet.getEmptyResultSet(
          extendedSet ? GET_COLUMNS_EXTENDED_SET : GET_COLUMNS, statement);
    } else {
      String catalogEscaped = escapeSqlQuotes(catalog);
      if (schemaPattern == null || isSchemaNameWildcardPattern(schemaPattern)) {
        showColumnsCommand += " in database \"" + catalogEscaped + "\"";
      } else if (schemaPattern.isEmpty()) {
        return SnowflakeDatabaseMetaDataResultSet.getEmptyResultSet(
            extendedSet ? GET_COLUMNS_EXTENDED_SET : GET_COLUMNS, statement);
      } else {
        String schemaUnescaped = unescapeChars(schemaPattern);
        if (tableNamePattern == null || Wildcard.isWildcardPatternStr(tableNamePattern)) {
          showColumnsCommand += " in schema \"" + catalogEscaped + "\".\"" + schemaUnescaped + "\"";
        } else if (tableNamePattern.isEmpty()) {
          return SnowflakeDatabaseMetaDataResultSet.getEmptyResultSet(
              extendedSet ? GET_COLUMNS_EXTENDED_SET : GET_COLUMNS, statement);
        } else {
          String tableNameUnescaped = unescapeChars(tableNamePattern);
          showColumnsCommand +=
              " in table \""
                  + catalogEscaped
                  + "\".\""
                  + schemaUnescaped
                  + "\".\""
                  + tableNameUnescaped
                  + "\"";
        }
      }
    }

I have also checked the recently released version 3.16.0 but still happening the same. https://github.com/snowflakedb/snowflake-jdbc/blob/master/src/main/java/net/snowflake/client/jdbc/SnowflakeDatabaseMetaData.java#L1674

Answer 1 · 2024-05-02T08:08:33.000Z

Hello @jb-saurabh ,

Thanks for raising the question.

Did you try setting the parameter CLIENT_METADATA_REQUEST_USE_CONNECTION_CTX=true, this parameter can change the default search scope from all databases/schemas to the current database/schema. The narrower search typically returns fewer rows and executes more quickly.

Documentation:
https://docs.snowflake.com/en/sql-reference/parameters
search for CLIENT_METADATA_REQUEST_USE_CONNECTION_CTX

let us know if you still facing the issue after setting below parameter.

Example

`stmt.execute("alter session set CLIENT_METADATA_REQUEST_USE_CONNECTION_CTX = true");

DatabaseMetaData dbmd = con.getMetaData();
System.out.println("Metadata:");
System.out.println("================================");
// fetch metadata

ResultSet columns = dbmd.getColumns(null, "_TEST", null, null);
  //Printing the column name and size
  while (columns.next()){
    System.out.print("Column name and size: "+columns.getString("COLUMN_NAME"));
    System.out.print("("+columns.getInt("COLUMN_SIZE")+")");
    System.out.println(" ");
    System.out.println("Ordinal position: "+columns.getInt("ORDINAL_POSITION"));
    System.out.println("Catalog: "+columns.getString("TABLE_CAT"));
    System.out.println("Data type (integer value): "+columns.getInt("DATA_TYPE"));
    System.out.println("Data type name: "+columns.getString("TYPE_NAME"));
    System.out.println(" ");
  }

`

Regards,
Sujan

Answer 2 · 2024-05-02T13:06:03.000Z

Hello @jb-saurabh ,

Thanks for raising the question.

Did you try setting the parameter CLIENT_METADATA_REQUEST_USE_CONNECTION_CTX=true, this parameter can change the default search scope from all databases/schemas to the current database/schema. The narrower search typically returns fewer rows and executes more quickly.

Documentation: https://docs.snowflake.com/en/sql-reference/parameters search for CLIENT_METADATA_REQUEST_USE_CONNECTION_CTX

let us know if you still facing the issue after setting below parameter.

Example

`stmt.execute("alter session set CLIENT_METADATA_REQUEST_USE_CONNECTION_CTX = true");

DatabaseMetaData dbmd = con.getMetaData(); System.out.println("Metadata:"); System.out.println("================================"); // fetch metadata
ResultSet columns = dbmd.getColumns(null, "_TEST", null, null);
  //Printing the column name and size
  while (columns.next()){
    System.out.print("Column name and size: "+columns.getString("COLUMN_NAME"));
    System.out.print("("+columns.getInt("COLUMN_SIZE")+")");
    System.out.println(" ");
    System.out.println("Ordinal position: "+columns.getInt("ORDINAL_POSITION"));
    System.out.println("Catalog: "+columns.getString("TABLE_CAT"));
    System.out.println("Data type (integer value): "+columns.getInt("DATA_TYPE"));
    System.out.println("Data type name: "+columns.getString("TYPE_NAME"));
    System.out.println(" ");
  }
`

Regards, Sujan

Hi @sfc-gh-sghosh,
Thanks for answering,
I tried the steps you have above mentioned but I remain stuck because it is not executing for the table. As I read this doc(https://docs.snowflake.com/en/sql-reference/parameters#label-client-metadata-request-use-connection-ctx), here is mentioned if we are not providing database or schema name with this property, it will narrow the search to the current database and schema specified by the connection context. But I don't want to load the database or schema if I am providing all details, I want to directly load table metadata instead of loading the database or schema.

Answer 3 · 2024-05-06T01:36:41.000Z

Hello @jb-saurabh ,

Thanks for the update.
The correct syntax for the DatabaseMetaData getColumns() is ( catalog, schemaPattern, TableNamePattern, SchemaNamePattern), All are strings datatype.

I just tested it again, its perfectly narrow down to the current schema and database ( which is being used in the connection URL ) and will only search the respective table.

If you pass the respective catalog and scehma, then it will search from that specific catalog and schema.
ResultSet columns = dbmd.getColumns("SAMPLEDATABASE", "TEST", "MYCSVTABLE", null);

if you do not pass any database or catalog, then its recommended to use the below parameter CLIENT_METADATA_REQUEST_USE_CONNECTION_CTX .

I am getting same output for both the case.

Example: I am not passing the database or schema (its null) , I am passing only the tablename.

stmt.execute("alter session set CLIENT_METADATA_REQUEST_USE_CONNECTION_CTX = true");

ResultSet columns = dbmd.getColumns(null, null, "MYCSVTABLE", null);
//Printing the column name and size
while (columns.next()){
System.out.print("Table name and size: "+columns.getString("TABLE_NAME"));
System.out.println("Catalog: "+columns.getString("TABLE_CAT"));
System.out.print("Column name and size: "+columns.getString("COLUMN_NAME"));
System.out.print("("+columns.getInt("COLUMN_SIZE")+")");
System.out.println(" ");
System.out.println("Ordinal position: "+columns.getInt("ORDINAL_POSITION"));
System.out.println("Data type (integer value): "+columns.getInt("DATA_TYPE"));
System.out.println("Data type name: "+columns.getString("TYPE_NAME"));
System.out.println(" ");
}

Output:
Its only printing the columns info the table MYCSVTABLE and no other table

Get columns
Table name and size: MYCSVTABLE Catalog: SAMPLEDATABASE
Column name and size: SEQ(38)
Ordinal position: 1
Data type (integer value): -5
Data type name: NUMBER

Table name and size: MYCSVTABLE Catalog: SAMPLEDATABASE
Column name and size: LAST_NAME(50)
Ordinal position: 2
Data type (integer value): 12
Data type name: VARCHAR

Table name and size: MYCSVTABLE Catalog: SAMPLEDATABASE
Column name and size: FIRST_NAME(50)
Ordinal position: 3
Data type (integer value): 12
Data type name: VARCHAR

So, please try again and let us know your code if still doesnt work.

Regards,
Sujan

Answer 4 · 2024-05-06T06:35:23.000Z

Hi @sfc-gh-sghosh,
Thanks for your response.
Yes, you are correct, it working as expected for dbmd.getColumns("SAMPLEDATABASE", "TEST", "MYCSVTABLE", null);

But my issue is when I am providing a schema name like TEST_SCHEMA and a table name like MY_CSVTABLE then it will load metadata for the complete database. And query will be become something like: show /* JDBC:DatabaseMetaData.getColumns() */ columns in database "SAMPLEDATABASE"

Concludingly It should working as expected for dbmd.getColumns("SAMPLEDATABASE", "TEST_SCHEMA ", "MY_CSVTABLE ", null);

Answer 5 · 2024-05-07T20:34:01.000Z

@jb-saurabh That method supports pattern matching, but the _ character will match any character, so you would need to escape that in order to get the output you're looking for. Please refer to the documentation here for more information.

Answer 6 · 2024-05-09T12:26:59.000Z

@jb-saurabh have you tried escaping the underscore character and confirm that you get the expected output?

Answer 7 · 2024-05-09T12:34:46.000Z

Hi @sfc-gh-wfateem
Thanks for your response.
Yes, I tried this way as you suggested and it works for me.
Thank You!

Answer 8 · 2024-05-09T13:48:53.000Z

Thanks for confirming @jb-saurabh
I'll go ahead and close off this issue then.
Don't hesitate to reach out again if you run into any other issues.