Data Types for Snowflake

Overview

Every column in a table has a name and a data type. The data type tells Snowflake how much physical storage to set aside for the column and the form in which the data is stored. For more information about Snowflake data types and to determine which data types are available for your version of Snowflake, see your Snowflake documentation.

For information about Snowflake data types and to determine which data types are available for your version of Snowflake, see your Snowflake documentation.

Supported Snowflake Data Types

Here are the data types that the Snowflake engine supports.

• Character data

  ARRAY	TEXT
  CHAR, CHARACTER	VARCHAR(n)
  OBJECT	VARIANT
  STRING

• Numeric data

  BIGINT	INT
  BOOLEAN	INTEGER
  DECIMAL	NUMBER(p,s)
  DOUBLE	NUMERIC
  DOUBLE PRECISION	REAL
  FLOAT	SMALLINT
  FLOAT4	TINYINT
  FLOAT8

  Note: When performing calculations on numeric values and when storing numeric values, SAS maintains up to 15 digits of precision. When you read values that contain more than 15 decimal digits of precision from a database into SAS, the values that SAS reads are rounded to meet this condition. When you use a large numeric value in a WHERE clause, this rounding can cause unexpected results, such as not selecting desired rows. For noncomputational purposes, such as storing ID values or credit card numbers, you can read the data in as character data. For more information, see Your Options When Choosing Your Needed Degree of Precision.
• Date, time, and timestamp data

  DATE	TIMESTAMP_LTZ
  DATETIME	TIMESTAMP_NTZ
  TIME	TIMESTAMP_TZ
  TIMESTAMP

LIBNAME Statement Data Conversions

This table shows the default formats that SAS/ACCESS Interface to Snowflake assigns to SAS variables when using the LIBNAME statement to read from a Snowflake table. These default formats are based on Snowflake column attributes. SAS/ACCESS does not support Snowflake data types that do not appear in this table.

*TIMESTAMP data types: SAS/ACCESS Interface for Snowflake sets the TIMESTAMP data type alias to map to TIMESTAMP without time zone. When the connection to Snowflake is established, these commands are executed.

ALTER SESSION SET TIMESTAMP_TYPE_MAPPING = TIMESTAMP_NTZ
ALTER SESSION SET CLIENT_TIMESTAMP_TYPE_MAPPING = TIMESTAMP_NTZ
ALTER SESSION SET WEEK_START=7

**Semi-structured data types: The ODBC driver describes these as SQL_VARCHAR, and they are read into SAS as a string. To read semi-structured data elements, use explicit SQL pass-through to submit SQL queries using special operators and functions.

Here is an example of semi-structured data.

libname snow dsn='snowflake' user=myusr1 pw=mypwd1
     schema=myschema;
proc sql noerrorstop;
connect using snow;
execute (
CREATE OR REPLACE TABLE "myClass" ( "src" variant )
AS
SELECT PARSE_JSON(column1) AS "src"
FROM VALUES
('{
    "Name" : "Alfred",
    "Sex" : "M",
    "Age" : 14,
    "Measurements" : {
      "Height": 69.0,
      "Weight" : 112.5
    }
}'),
('{
    "Name" : "Alice",
    "Sex" : "F",
    "Age" : 13,
    "Measurements" : {
      "Height": 56.5,
      "Weight" : 84.0
    }
}') ) by snow;
disconnect from snow;
quit;

proc print data=snow.myClass;
title 'proc print myClass';
run;

title 'Traversing semi-structured data';
proc sql;
connect using snow;
select name, sex from connection to snow 
   (select "src":Name as name, "src":Sex as sex from SASUSER."myClass");
disconnect from snow;
quit;

title 'Traversing semi-structured data - type specified';
proc sql;
connect using snow;
select name, sex from connection to snow 
   (select "src":Name::char(10) as name, 
      "src":Sex::char(1) as sex from SASUSER."myClass");
disconnect from snow;
quit;

The first PROC PRINT statement prints the semi-structured data in a string. The first SQL step uses the colon (:) notation to read a first-level element of the semi-structured data. In the second SQL step, a type specification is added.

proc print myClass          09:06 Thursday, November 22, 2018   2

     Obs src

       1 {
  "Age": 14,
  "Measurements": {
    "Height": 69,
    "Weight": 112.5
  },
  "Name": "Alfred",
  "Sex": "M"
}
       2 {
  "Age": 13,
  "Measurements": {
    "Height": 56.5,
    "Weight": 84
  },
  "Name": "Alice",
  "Sex": "F"
}
Traversing semi-structured data       09:06 Thursday, November 22, 2018  4

NAME                                  SEX
--------------------------------------------------------------------------
"Alfred"                              "M"

"Alice"                               "F"

Traversing semi-structured data, type specified  09:06 Thursday, November 22, 2018  5

                      NAME        SEX
                      ---------------
                      Alfred      M
                      Alice       F

The next table shows the default Snowflake data types that SAS/ACCESS assigns to SAS variable formats during output operations when you use the LIBNAME statement.