Eric's another Big Data Blog
Currently, Snowflake does not have a native function to merge JSON data type, the workaround is to continuously use the OBJECT_INSERT, which can have performance implications. Please see the example below: Table setup In order to add more properties, we have to use the OBJECT_INSERT: You can see how cumbersome it is to do so. […]
SQLCODE is used in Snowflake Scripting to capture the error code returned from running a certain query so the script can decide how to handle the error. Recently I have seen some users reported that the SQLCODE returned 0 even though the query failed. I was able to demonstrate the issue using the below script: […]
When trying to use replaceAll() function from a String object in Snowflake’s Javascipt UDF, it will fail with the below error message: Based on the information on Wikipedia, the string function replaceAll() was released in June 2021, with 12th Edition – ECMAScript 2021. Since this is a new function, it has not been incorporated into Snowflake’s Javascript library. […]
Recently I was helping one of the StackOverflow users to resolve one of his/her problems. The question was whether Snowflake has any way of automatically creating views on top of a table that has a variant data type, so that users can access data inside the variant just like a normal column. I do not […]
Currently Snowflake does not show the parent and child relationships between executed Stored Procedure and queries triggered from within the Stored Procedure from the QUERY_HISTORY view. This is quite an inconvenience if we want to find out what are the queries triggered by a certain Stored Procedure. I found a workaround to this issue, though […]
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 […]