SNOW-1250205: Driver returns incorrect column type while querying on Query History Views
Closed this issue · 4 comments
- What version of JDBC driver are you using?
- What operating system and processor architecture are you using?
- MacOS Ventura 13.6.4, arm64
- What version of Java are you using?
- Java 18
- What did you do?
- Views in Context - Access History, Query History
- The document suggests that the column
QUERY_START_TIME
(from Access History View) andSTART_TIME
(from Query History View) are both of typeTIMESTAMP_LTZ
.
- The document suggests that the column
- Via the JDBC driver, try to fetch the row for the same query (via query_id)
example query:
SELECT
qh.QUERY_ID AS QHI,
qh.START_TIME AS START_TIME,
ah.QUERY_ID AS AHI,
ah.QUERY_START_TIME AS QUERY_START_TIME
FROM
SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY AS qh
INNER JOIN
SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY AS ah
ON
qh.QUERY_ID = ah.QUERY_ID
WHERE
qh.QUERY_ID='QUERY_ID';
- What did you expect to see?
- Both
START_TIME
andQUERY_START_TIME
should ideally return the same column type (i.e TIMESTAMP_LTZ or SnowflakeTimestampWithTimezone) and value, but it doesn't. (image)
-
Can you set logging to DEBUG and collect the logs?
https://community.snowflake.com/s/article/How-to-generate-log-file-on-Snowflake-connectors
-
What is your Snowflake account identifier, if any? (Optional)
- qia75894
Hello @inishchith ,
I tried to reproduce it with latest Snowflake JDBC 3.15.0, but its reflecting the correct data type TIMESTAMPLTZ for columns START_TIME from view SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY and SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY
Here is the output:
Column Name: QHI, Data Type: VARCHAR
Column Name: START_TIME, Data Type: TIMESTAMPLTZ
Column Name: AHI, Data Type: VARCHAR
Column Name: QUERY_START_TIME, Data Type: TIMESTAMPLTZ
Here is the sample code snippet
ResultSet resultSet=statement.executeQuery("SELECT \n"
+ " qh.QUERY_ID AS QHI, \n"
+ " qh.START_TIME AS START_TIME, \n"
+ " ah.QUERY_ID AS AHI, \n"
+ " ah.QUERY_START_TIME AS QUERY_START_TIME \n"
+ "FROM \n"
+ " SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY AS qh\n"
+ "INNER JOIN \n"
+ " SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY AS ah \n"
+ "ON \n"
+ " qh.QUERY_ID = ah.QUERY_ID\n"
+ "WHERE \n"
+ " qh.QUERY_ID='QUERY_ID' limit 1");
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
for (int i = 1; i <= columnCount; i++) {
String columnName = metaData.getColumnName(i);
String columnType = metaData.getColumnTypeName(i);
System.out.println("Column Name: " + columnName + ", Data Type: " + columnType );
}
Regards,
Sujaan
Hello @inishchith ,
let us know if anything further or if you could provide the sample application with jdbc log which reproduces the issue, because its working expected and no issue being surfaced.
Regards,
Sujan
@sfc-gh-sghosh Thanks for the turnaround, I wasn't able to reproduce this with the above snippet.
I think this could be with the way I am using it - I shall take a look. We can close this, If am able to get back - will reopen and share.
Thanks again!