Wednesday, July 21, 2021

SQL compilation error: Unsupported data type 'XXX' in Snowflake

Working on loading of data into Snowflake (Online DWH). The first step is to have the database and tables created in snowflake.

After creating database, opened the worksheet to run create table query (quite straight forward)

CREATE TABLE [DATA].[BOLS]
(
        ID INT,
BOLNUMBER VARCHAR(50),
ROWGUID UNIQUEIDENTIFIER,
STPNUMBER INT,
        BB_ID SMALLINT,
ACCTNO VARCHAR(6),
CREATEDATE DATETIME
    
)

But when clicked on Run, got following error in result.

SQL compilation error: Unsupported data type 'UNIQUEIDENTIFIER'.

Like wise for other tables got below errors

SQL compilation error: Unsupported data type 'MONEY''.
SQL compilation error: Unsupported data type 'MONEY'



SQL compilation error: Unsupported data type 'BIT''
                               
   SQL compilation error: Unsupported data type 'bit'



Why it happened


Snowflake is a data platform and data warehouse that supports the most common standardized version of SQL: ANSI. This means that all of the most common operations are usable within Snowflake. Snowflake also supports all of the operations that enable data warehousing operations, like create, update, insert, etc.

However it's not true with the all the datatype used in SQL, that's what the error message say so 😉


What to do


You need to find out the equivalent datatypes in Snowflake for the ones you get error for and you will need to rewrite this datatype in Snowflake

Like in my case following were the equivalents to be used
  • DECIMAL is the equivalent for MONEY datatype in Snowflake. 
  • STRING is the equivalent for UNIQUEIDENTIFIER datatype in Snowflake.
  • BOOLEAN is the equivalent for BIT datatype in Snowflake.







No comments:

Post a Comment

If you have any suggestions or questions or want to share something then please drop a comment