Eric's another Big Data Blog
When running Javascript UDF with VARIANT data type passed into it as parameter, it fails with error: TIME inside VARIANT value – not supported This issue can be reproduced using below steps: And the last SELECT statement will fail with above mentioned error. This is because Javascript does not support the TIME data type, as […]
When unloading Timestamp data into Parquet file, the nano and micro seconds precision will be lost, and only milliseconds are stored in the file. To reproduce the issue, please see below script: We can see that the nano seconds part .12434453 was truncated to .124. This is due to limitations on Parquet V1 that is currently being used […]
When running a Stored Procedure that triggers a query to run against Views or Functions from INFORMATION_SCHEMA, it might fail with below error message: Requested information on the current user is not accessible in stored procedure. This can be easily reproduced using below code: The reason for the failure is because for the owner’s right […]
Recently, I was working on an issue for customer who faced a weird behaviour that TIMESTAMP conversion function returned different results if different sorting direction was used, meaning, results are different if using ASC vs DESC. To illustrate the issue, please see my test case below: Create a table with testing data: 2. Run below […]
Currently Snowflake does not support pruning External Table files. To prove it, I have performed below test. Assume that I have an external S3 stage setup correctly, which points to an external S3 location with a few CSV files, I create below external table: The stage @ERICLIN_S3_STAGE/test contains a list of CSV files And each […]
Today, I have learnt that Snowflake will convert decimal values to integer values if all trailings are zeros. Please see below examples: It returns: The same applies to PARSE_JSON: It returns: Instead of: After researching with our team, it turned out that it was expected behaviour. In fact, Snowflake has been behaving this way for […]
Recently I was dealing with an issue that a Snowflake query against Parquet file in internal Staging failed with error as below: After researching online, I figured out that it was nothing to do with Snowflake, it was because user used AWS Cost and Usage Report (CUR) tool to generate the Parquet file, which contains a version of […]
It is common that we will need to perform Date transformations inside Stored Procedures in SQL. Recently I have encountered an error that Snowflake does not like the format that is generated by Javascript inside Stored Procedure code, and resulted an error. See below example: To execute this Stored Procedure, simply run below query: And […]
Pagination on query results is straightforward enough, and it is so common that most of SQL developers would need to do it on weekly, if not on daily basis. Paginate the resultset of SHOW TABLES query however, is not as common and might not be known to most developers, because of the different syntax, plus […]
We know that it is important to keep track of table ownerships to understand who created and owned the tables in the system. Snowflake keeps such records in multiple places. So depending on your needs, the methods might be different. In this post, I will show you 2 ways that you can get such information […]