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 the newly created tables or views might end up in […]
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 4 backslash returned value as “my\\id“, which contains 2 backslashes. […]
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 /tmp/snowsql.log. There are at least two possible causes that can […]
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, you cannot use ALTER TABLE to change the collation. Additional Considerations for […]
Most of time when Snowflake query complains about something “is not recognized”, it normally means that Snowflake is not able to CAST a given value to a target type. In my case of “Timestamp ‘2020-09-28’ is not recognized”, it means Snowflake is not able to convert value “2020-09-28” to Timestamp value. This sounds a bit non-sense, as “2020-09-28” is a valid timestamp value, even without […]
Currently in Snowflake, it supports using the precision format of FF<N>, where N is a number between 0 to 9, to display precision part of the timestamp value. To demonstrate how it works, please see below examples: By default the output returns only 3 digits, which is up to milliseconds, is because the session level parameter TIMESTAMP_OUTPUT_FORMAT has the default value of “YYYY-MM-DD HH24:MI:SS.FF3 TZHTZM”. […]
Currently if you use GROUP BY CUBE clause in Snowflake, it has the limitation of supporting only 7 elements, or the equivalent of 128 grouping sets. This is documented in below Snowflake’s official documentation: GROUP BY CUBE Usage NotesSnowflake allows up to 7 elements (equivalent to 128 grouping sets) in each cube. This same limitation also applies to both GROPU BY ROLLUP and GROUP BY […]
UNPIVOT is a SQL standard function that allows users to transform columns into rows, and Snowflake also supports it. Please see below example on how to use it: You can see that the result turned the scores that were in each column of a row into seperate rows that still associated with original student ID. This query works well for the columns defined in UNPIVOT […]
Welcome to my first Snowflake blog post. Since I just started as a Cloud Data Warehouse Engineer 2 weeks ago, Snowflake is also very new to me. I have spent sometime played around with Snowflake interface and learn how to load data from local file system into Snowflake and then query from Snowflake Web UI. I have noticed that though loading data is straight forward, […]
If you have been following me on my Cloudera Blog, you probably know me well enough. I worked at Cloudera as a Support Engineer for the last 5.5 years. I have decided to move on and started at Snowflake as a Cloud Data Warehouse Engineer (Cloud Support Engineer) since last week. Don’t get me wrong. I really enjoyed working at Cloudera and I did feel […]