Eric's another Big Data Blog
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 […]
If you come from RDBMS, we all know that it is important to have constraints in your database, so that your data integrity will be maintained, like Primary Key, Foreign Key, Unique Indexing etc. However, in Snowflake world, it is very different. Though Snowflake Supports constraints, like the ones I mentioned above, they are NOT […]
Recursive query in SQL can be as useful as recursive functions that developers use all the time. Snowflake has documentations on how to use CTE to construct recursive queries. Example can be found here: Working with CTEs. It uses a simple company employees hierarchy to demonstrate how to run recursive queries, and it also describes […]
We all know that ALTER TABLE is a DDL (Data Definition Language), not a DML (Data Manipulation Language), because ALTER TABLE only changes metadata information about a table, like adding a new column, or changing existing column names.. If you are unsure about the differences between DDL and DML, please refer to this page on […]
By default, GET_DLL function in Snowflake will return TABLE or VIEW without database and schema path. Please see example below: The result is below: You can see that the table name GET_DDL_TEST returned without Database and Schema info. This could be problematic if user is relying on the output to re-create tables or views, and […]
Sometimes it can be confusing when trying to parse JSON string, this is no different when using PARSE_JSON function in Snowflake, especially when there are special characters involved. Look at below examples when I need to have backslash inside the key string: We can see that both of 2 and 3 backslashes produced errors, while […]
Sometimes user can face “JWT token is invalid” error when trying to connect to Snowflake via SnowSQL using key based authentication. To capture this error, logging needs to be enabled for SnowSQL via command line. Use below sample command to redirect logs to a file with DEBUG level logging: And check the log file under […]
Currently Snowflake does not support the change of COLLATIONs at COLUMN level once table is defined. This is documented on Snowflake’s official documentation as below: Once a table column is defined, you cannot change the collation for the column. In other words, after a column has been created with a particular collation using a CREATE TABLE statement, […]