Table of Contents

Search

  1. Preface
  2. Part 1: Getting Started with Snowflake Data Cloud Connector
  3. Part 2: Data Integration with Snowflake Data Cloud Connector
  4. Part 3: SQL ELT with Snowflake Data Cloud Connector
  5. Appendix A: Data type reference
  6. Appendix B: Additional runtime configurations
  7. Appendix C: Upgrading to Snowflake Data Cloud Connector

Snowflake Data Cloud Connector

Snowflake Data Cloud Connector

Functions in mappings in SQL ELT mode

Functions in mappings in SQL ELT mode

When you create expressions within a mapping in SQL ELT mode, you must use the native functions and expression syntax of Snowflake Data Cloud and not Informatica functions and expression syntax.
You can use the following native functions in a mapping in SQL ELT mode:

Aggregate functions

Function
Function
Function
ANY_VALUE(expr)
MEDIAN(expr)
VAR_POP(expr)
AVG(expr)
MODE(expr)
VAR_SAMP(expr)
COUNT(expr [, expr1])
STDDEV(expr)
VARIANCE(expr)
COUNT_IF(cond)
STDDEV_POP(expr)
VARIANCE_POP(expr)
MAX(expr)
STDDEV_SAMP(expr)
VARIANCE_SAMP(expr)
MIN(expr)
SUM(expr)
-

Bitwise expression functions

Function
Function
Function
BITAND(expr1, expr2)
BITSHIFTLEFT(expr1, n)
GETBIT(integer_expr, bit_position)
BITNOT(expr)
BITSHIFTRIGHT(expr1, n)
-
BITOR(expr1, expr2)
BITXOR(expr1, expr2)
-

Conditional expression functions

Function
IFF(<condition>, expr1, expr2)
IFNULL(expr1, expr2)

Context functions

Function
Function
Function
ALL_USER_NAMES()
CURRENT_SCHEMA()
INVOKER_ROLE()
CURRENT_ACCOUNT()
CURRENT_SCHEMAS()
INVOKER_SHARE()
CURRENT_AVAILABLE_ROLES()
CURRENT_SECONDARY_ROLES()
IS_GRANTED_TO_INVOKER_ROLE( role_name )
CURRENT_CLIENT()
CURRENT_SESSION()
IS_ROLE_IN_SESSION( role_name )
CURRENT_DATABASE()
CURRENT_STATEMENT()
LAST_QUERY_ID( num )
CURRENT_DATE()
CURRENT_TIMESTAMP( fract_sec_precision )
LAST_TRANSACTION()
CURRENT_IP_ADDRESS()
CURRENT_TRANSACTION()
LOCALTIMESTAMP()
CURRENT_REGION()
CURRENT_USER()
SYSDATE()
CURRENT_ROLE()
CURRENT_VERSION()
-
CURRENT_ROLE_TYPE()
CURRENT_WAREHOUSE()
-

Conversion functions

Function
Function
Function
DATE(String)
TO_NUMBER(Boolean)
TO_VARCHAR(Binary)
DATE(Temporal)
TO_NUMBER(String)
TO_VARCHAR(Numeric)
TO_BINARY
TO_NUMERIC
TO_VARCHAR(Temporal)
TO_BINARY(String)
TO_NUMERIC(Boolean)
TRY_TO_BINARY
TO_BOOLEAN
TO_NUMERIC(String)
TRY_TO_BOOLEAN
TO_BOOLEAN(Number)
TO_TIMESTAMP
TRY_TO_DATE
TO_CHAR
TO_TIMESTAMP(String)
TRY_TO_DECIMAL
TO_CHAR(Binary)
TO_TIMESTAMP(Temporal)
TRY_TO_DOUBLE
TO_CHAR(Numeric)
TO_TIMESTAMP_LTZ
TRY_TO_NUMBER
TO_CHAR(Temporal)
TO_TIMESTAMP_LTZ(String)
TRY_TO_NUMERIC
TO_DECIMAL(Boolean)
TO_TIMESTAMP_LTZ(Temporal)
TRY_TO_TIMESTAMP
TO_DECIMAL(String)
TO_TIMESTAMP_NTZ
TRY_TO_TIMESTAMP_LTZ
TO_DOUBLE
TO_TIMESTAMP_NTZ(String)
TRY_TO_TIMESTAMP_NTZ
TO_DOUBLE(String)
TO_TIMESTAMP_NTZ(Temporal)
-
TO_NUMBER
TO_VARCHAR
-

Data generation functions

Function
Function
Function
NORMAL( mean,stddev,gen )
SEQ2( [0|1] )
UUID_STRING(uuid,name)
RANDOM()
SEQ4( [0|1] )
ZIPF( s, N, gen )
RANDSTR( length,gen )
SEQ8( [0|1] )
-
SEQ1( [0|1] )
UNIFORM( min,max,gen )
-

Date and time functions

Function
Function
ADD_MONTHS(date_or_time_expr, num_months_expr)
NEXT_DAY(date_or_time_expr, dow_string)
DATEADD(date_or_time_part, value, date_or_time_expr)
PREVIOUS_DAY(date_or_time_expr, dow_string)
DATEDIFF(date_or_time_part, date_or_time_expr1, date_or_time_expr2)
SYSDATE()
DATE_FROM_PARTS(year, month, day) or DATEFROMPARTS(year, month, day)
TIMESTAMPADD(date_or_time_part,value,date_or_time_expr)
DATE_PART(date_or_time_part,date_or_time_expr)
TIMESTAMPDIFF(date_or_time_part,date_or_time_expr1,date_or_time_expr2)
DATE_TRUNC(date_or_time_part,date_or_time_expr)
TIMESTAMP_LTZ_FROM_PARTS(year, month, day, hour, minute, second) or TIMESTAMPLTZFROMPARTS(year, month, day,hour, minute, second)
DAYNAME(date_or_timestamp_expr)
TIMESTAMP_NTZ_FROM_PARTS(year, month, day, hour, minute, second) or TIMESTAMPNTZFROMPARTS(year, month, day, hour, minute, second)
LAST_DAY(date_or_time_expr)
TIME_SLICE(date_or_time_expr, slice_length,date_or_time_part)
MONTHNAME(date_or_time_expr)
TRUNC(date_or_time_expr, date_or_time_part)
MONTHS_BETWEEN(date_expr1, date_expr2)
-

Encryption functions

Function
DECRYPT( value_to_decrypt,passphrase )
DECRYPT_RAW( value_to_encrypt,key,iv )
ENCRYPT( value_to_encrypt,passphrase )
ENCRYPT_RAW( value_to_encrypt,key,iv )

Numeric functions

Function
Function
Function
ABS( expr )
DEGREES( real_expr )
ROUND( input_expr )
ACOS( real_expr )
DIV0( dividend,divisor )
SIGN( expr )
ACOSH( real_expr )
DIV0NULL( dividend,divisor )
SIN( real_expr )
ASIN( real_expr )
EXP( real_expr )
SINH( real_expr )
ASINH( real_expr )
FACTORIAL( integer_expr )
SQRT( expr )
ATAN( real_expr )
FLOOR( expr )
SQUARE( expr )
ATAN2( y,x )
LN( expr )
TAN( real_expr )
ATANH( real_expr )
LOG( base,expr )
TANH( real_expr )
CBRT( expr )
MOD( expr1,expr2 )
TRUNCATE( expr )
CEIL( expr )
PI( real_expr )
TRUNC( expr )
COS( real_expr )
POW( x,y )
WIDTH_BUCKET( expr,min_value,max_value,num_buckets )
COSH( real_expr )
POWER( x,y )
-
COT( real_expr )
RADIANS( real_expr )
-

Regular expression functions

Function
REGEXP_COUNT(subject,pattern)
REGEXP_INSTR(subject,pattern)
REGEXP_LIKE(subject,pattern)
REGEXP_REPLACE(subject,pattern)
REGEXP_SUBSTR(subject,pattern)
REGEXP_SUBSTR_ALL(subject,pattern)
RLIKE(subject,pattern)

String and binary functions

Function
Function
Function
ASCII( expr )
JAROWINKLER_SIMILARITY( string_expr1,string_expr2 )
RTRIMMED_LENGTH( expr )
BASE64_DECODE_BINARY(input [, alphabet ])
LEFT( string_expr,length_expr )
SHA1( expr )
BASE64_DECODE_STRING(input [, alphabet ])
LEN( expr )
SHA1_HEX( expr )
BASE64_ENCODE( input )
LENGTH( expr )
SHA1_BINARY( expr )
BIT_LENGTH( expr )
LIKE( subject, pattern )
SHA2_HEX( expr )
BIT_LENGTH( expr )
LOWER( expr )
SHA2( expr )
CHR( expr )
LPAD( expr1, expr2 )
SHA2_BINARY( expr )
CHAR( expr )
LPAD( expr1, expr2, expr3 )
SOUNDEX( varchar_expr )
CHARINDEX( expr1, expr2 )
LTRIM( expr )
SOUNDEX_P123( varchar_expr )
COLLATE( string_expression,collation_specification )
MD5( expr )
SPACE( expr )
COLLATION( expression )
MD5_BINARY( expr )
SPLIT( expr1, expr2 )
COMPRESS( input,method )
MD5_HEX( expr )
SPLIT_PART( string,delimiter,part_number )
CONCAT( expr )
MD5_NUMBER_LOWER64( msg )
STARTSWITH( expr1, expr2 )
CONCAT_WS( expr1, expr2 )
MD5_NUMBER_UPPER64( msg )
STRTOK( string )
CONTAINS( expr1, expr2 )
OCTET_LENGTH( string )
STRTOK_TO_ARRAY( string )
DECOMPRESS_BINARY( input,method )
PARSE_IP( expr,type )
SUBSTR( base, start )
DECOMPRESS_STRING( input,method )
PARSE_URL( expr )
SUBSTRING( base, start )
EDITDISTANCE( expr1, expr2 )
POSITION( expr1, expr2 )
TRANSLATE( subject,sourceAlphabet,targetAlphabet )
ENDSWITH( expr1, expr2 )
REPEAT( expr1, expr2 )
TRIM( expr )
HASH( expr1 )
REPLACE( subject, pattern )
TRY_BASE64_DECODE_BINARY( input )
HEX_DECODE_BINARY( input )
REVERSE( expr )
TRY_BASE64_DECODE_STRING( input )
HEX_DECODE_STRING( input )
RIGHT( expr, len )
TRY_HEX_DECODE_BINARY( input )
HEX_ENCODE( input )
RIGHT( expr1, len )
TRY_HEX_DECODE_STRING( input )
ILIKE( subject,pattern )
RPAD( expr1, expr2 )
UNICODE( expr )
INITCAP( expr )
RPAD( expr1, expr2, expr3 )
UPPER( expr )
INSERT( expr1, pos, len, expr2 )
RTRIM( expr1 )
-
For information on functions and expression syntax, see SQL function reference in the Snowflake documentation.

0 COMMENTS

We’d like to hear from you!