Snowflake SQL to Denodo VQL Quick Reference
You can translate the document:
Goal
This document is a quick reference for migrating Snowflake SQL to Denodo VQL. The document is aimed at administrators and developers that want to efficiently migrate their existing snowflake queries to Denodo VQL queries.
Content
The Knowledge Base article VDP Conformance with Standard SQL contains a reference of the Virtual DataPort conformance with the SQL 92 standard. The document is focused on query capabilities and contains information about: Data Types SQL Predicates Support and SQL Functions Support. The Query Expressions section lists the expressions defined by the standard SQL and their equivalent in Virtual DataPort, explaining the differences with the standard when appropriate.
Following, a group of tables where the correspondence between Snowflake functions and the Denodo equivalents is presented. This list is just a reference since more functions can be included in future versions of the Denodo Virtual DataPort Server.
Date Value Functions
|
Snowflake Syntax |
Denodo Syntax |
|
addmonth(input, increment) |
ADD_MONTHS( <date_or_timestamp_expr> , <num_months_expr> ) |
|
DATE_PART( <date_or_time_part> , <date_or_time_expr> ) |
getday (input_date) |
|
DATE_TRUNC( <date_or_time_part>, <date_or_time_expr> ) |
trunc(<input_date>,<input_timestamp>) |
|
HOUR( <time_or_timestamp_expr> )
MINUTE( <time_or_timestamp_expr> )
SECOND( <time_or_timestamp_expr> ) |
GETHOUR/GETMINUTE/GETSECOND
gethour/getminute/getsecond(datetime) |
|
LAST_DAY( <date_or_time_expr> [ , <date_part> ] )
|
lastdayofmonth/lastdayofweek
lastdayofmonth(input) |
|
CONVERT_TIMEZONE( <source_tz> , <target_tz> , <source_timestamp_ntz> )
|
convert_timezone(sourcetimezone, targettimezone, timestamp) |
|
MONTHS_BETWEEN( <date_expr1> , <date_expr2> ) |
getmonthsbetween(date1, date2) |
|
PREVIOUS_DAY( <date_or_time_expr> , <dow> ) |
previousweekday(inputdate, weekday)
or
nextweekday(inputdate, weekday) (only week days) |
|
TIMEADD( <date_or_time_part> , <value> , <date_or_time_expr> )
DATEADD( <date_or_time_part> , <value> , <date_or_time_expr> )
|
ADDDAY/ADDHOUR/ADDMINUTE/ADDSECOND
ADDHOUR( <value:time>, <increment> ):time
ADDDAY( <value:intervaldaysecond>, <increment> ):intervaldaysecond(DEPRECEATED IN FUTURE)
ADDMINUTE( <value:time>, <increment> ):time
ADDSECOND( <value:time>, <increment> ):time |
|
TRUNC( <date_or_time_expr>, <date_or_time_part> ) |
trunc(value) |
|
DAY( <date_or_timestamp_expr> )
DAYOFMONTH( <date_or_timestamp_expr> )
DAYOFWEEKISO( <date_or_timestamp_expr> )
WEEKISO( <date_or_timestamp_expr> )
QUARTER( <date_or_timestamp_expr> )
|
getday(<value>) |
|
MONTH( <date_or_timestamp_expr> ) |
getmonth(<value>)
|
|
WEEK( <date_or_timestamp_expr> ) WEEKOFYEAR( <date_or_timestamp_expr> ) |
getweek(<value>)
|
|
YEAR( <date_or_timestamp_expr> )
YEAROFWEEK( <date_or_timestamp_expr> )
YEAROFWEEKISO( <date_or_timestamp_expr> )
|
getyear(<value>)
|
|
DAYOFWEEK( <date_or_timestamp_expr> ) |
getdayofweek(<value>) |
|
DAYOFYEAR( <date_or_timestamp_expr> ) |
getdayofyear(<value>)
|
|
CURRENT_DATE() |
current_date() |
|
CURRENT_TIME( [ <fract_sec_precision> ] ) |
to_time(datepattern, datevalue) --> can be done with current_timestamp |
|
LOCALTIMESTAMP()
LOCALTIME()
SYSDATE()
CURRENT_TIMESTAMP( [ <fract_sec_precision> ] )
|
localtimestamp()
current_timestamp() |
|
TO_DATE( <string_expr> [, <format> ] ) TO_DATE( <timestamp_expr> ) TO_DATE( '<integer>' ) TO_DATE( <variant_expr> )
DATE( <string_expr> [, <format> ] ) DATE( <timestamp_expr> ) DATE( '<integer>' ) DATE( <variant_expr> ) |
to_date(datepattern, datevalue) |
|
TO_TIME( <string_expr> [, <format> ] ) TO_TIME( <timestamp_expr> ) TO_TIME( '<integer>' ) TO_TIME( <variant_expr> )
TIME( <string_expr> ) TIME( <timestamp_expr> ) TIME( '<integer>' ) TIME( <variant_expr> ) |
to_time(<datepattern>, <datevalue>) |
|
timestampFunction ( <numeric_expr> [ , <scale> ] )
timestampFunction ( <date_expr> )
timestampFunction ( <timestamp_expr> )
timestampFunction ( <string_expr> [ , <format> ] )
timestampFunction ( '<integer>' )
timestampFunction ( <variant_expr> ) |
to_timestamp(datepattern, datevalue) |
|
EXTRACT( <date_or_time_part> FROM <date_or_time_expr> ) |
EXTRACT ( <part of field> FROM <value> ) |
Aggregate Functions
|
Snowflake Syntax |
Denodo Syntax |
|
AVG( [ DISTINCT ] <expr1> ) |
AVG( <expression> ) |
|
COUNT( [ DISTINCT ] <expr1> [ , <expr2> ... ] ) |
COUNT( <Value (either field name or *)> ) |
|
MEDIAN( <expr> ) |
median(value) |
|
min (value) , max(value) |
min (value) , max(value) |
|
STDDEV( [ DISTINCT ] <expression_1> ) or STDDEV_SAMP( [ DISTINCT ] <expr1> ) |
STDEV( <expression> ) |
|
STDDEV_POP( [ DISTINCT ] expression_1) |
STDEVP( <expression> ) |
|
SUM( [ DISTINCT ] <expr1> ) |
SUM ( <expression> ) |
|
VAR_POP( [ DISTINCT ] <expr1> ) |
VARP( <expression> ) |
|
VAR_SAMP( [DISTINCT] <expr1> ) |
VAR( <expression> ) |
Numeric Functions
|
Snowflake Syntax |
Denodo Syntax |
|
ABS( <num_expr> ) |
abs(value) |
|
ACOS( <real_expr> ) |
acos(value) |
|
ASIN( <real_expr> ) |
ASIN( <value:numeric> ):double |
|
ATAN( <real_expr> ) |
ATAN( <value:numeric> ):double |
|
ATAN2( <y> , <x> ) |
ATAN2( <x:numeric>, <y:numeric>):double |
|
CEIL( <input_expr> [, <scale_expr> ] )
|
CEIL( <value:decimal> ):long
CEIL( <value:int> ):int
CEIL( <value:long> ):long"
|
|
COS( <real_expr> ) |
COS( <angle:numeric> ):double |
|
COT( <real_expr> ) |
COT( <angle:numeric> ):double |
|
DEGREES( <real_expr> ) |
DEGREES( <angle:numeric> ):double |
|
DIV0( <dividend> , <divisor> ) |
DIV( <dividend:numeric>, <divisor:numeric> ):numeric |
|
EXP( <real_expr> ) |
EXP( <value:numeric> ):double |
|
FLOOR( <input_expr> [, <scale_expr> ] ) |
FLOOR( <value:decimal> ):long
FLOOR( <value:int> ):int
FLOOR( <value:long> ):long"
|
|
LN(<expr>) |
LN( <value:numeric> ):double |
|
LOG(<base>, <expr>) |
LOG( <value:numeric> [, <base:numeric> ]):double |
|
MOD( <expr1> , <expr2> ) |
MOD( <dividend:decimal>, <divisor:decimal> ):decimal
MOD( <dividend:double>, <divisor:double> ):double
MOD( <dividend:float>, <divisor:float> ):double
MOD( <dividend:int>, <divisor:int> ):int
MOD( <dividend:long>, <divisor:int> ):int
MOD( <dividend:long>, <divisor:long> ):long
|
|
PI() |
PI():double |
|
POW(x, y)
POWER (x, y) |
POWER( <base:numeric>, <exponent:double> ):double |
|
ROUND( <input_expr> [, <scale_expr> ] ) |
ROUND( <value:numeric [, n : integer ] ):numeric |
|
SIN( <real_expr> ) |
SIN( <angle:numeric> ):double |
|
SIGN( <expr> ) |
SIGN( <value:numeric> ):int |
|
TRUNCATE( <input_expr> [ , <scale_expr> ] )
TRUNC( <input_expr> [ , <scale_expr> ] ) |
TRUNC( <value:numeric> ):long |
|
TAN( <real_expr> ) |
TAN ( <angle:numeric> ):double |
|
SQRT(expr) |
SQRT( <value:numeric> ):double |
|
RADIANS( <real_expr> ) |
RADIANS( <angle:numeric> ):double |
|
RANDOM([seed]) |
RAND() |
Text Functions
|
Snowflake Syntax |
Denodo Syntax |
|
ASCII( <input> ) |
ascii (value) |
|
CHARINDEX( <expr1>, <expr2> [ , <start_pos> ] ) |
INSTR( <str1:text>, <str2:text> ):int |
|
CONCAT( <expr1> [ , <exprN> ... ] )
<expr1> || <expr2> [ || <exprN> ... ]' |
concat(value1, value2, ...) |
|
LENGTH( <expression> ) LEN( <expression> ) |
LEN( <value:text> ):int |
|
LOWER( <expr> ) |
lower(value) |
|
LTRIM( <expr> [, <characters> ] ) |
ltrim(value) |
|
POSITION( <expr1>, <expr2> [ , <start_pos> ] )
POSITION( <expr1> IN <expr2> ) |
position(substrtextstr) |
|
|
repeat(value, count) |
|
REPLACE( <subject> , <pattern> [ , <replacement> ] ) |
replace(value, from, to) |
|
RTRIM(<expr> [, <characters> ]) |
rtrim(value) |
|
SUBSTR( <base_expr>, <start_expr> [ , <length_expr> ] ) SUBSTRING( <base_expr>, <start_expr> [ , <length_expr> ] ) |
SUBSTRING( <value:text>, <start index:int> [, <end index:int> ]):text |
|
TRIM( <expr> [, <characters> ] ) |
trim([{leading|trailing|both}[trimcharacter]from|trimcharacterfrom]value) |
|
UPPER( <expr> ) |
upper(value) |
|
<subject> REGEXP <pattern> |
regexp(field, regex, replacement) |
|
LPAD(<base>, <length_expr> [, <pad>]) |
leftpad(input, size, padChar) |
|
HASH( <expr> [ , <expr2> ... ] ) |
hash(value) |
|
<expr> IS [ NOT ] NULL |
<a> IS NULL / <a> IS NOT NULL |
Conversion Functions
|
Snowflake Syntax |
Denodo syntax |
|
TO_ARRAY( <expr> )
TO_XML( <expression> )
TO_DOUBLE(<exp>[, ‘<format>’]) |
cast(data_type, value) |
Conditional Operators
|
Snowflake Syntax |
Denodo Syntax |
|
CASE WHEN <condition1> THEN <result1> [ WHEN <condition2> THEN <result2> ] [ ... ] [ ELSE <result3> ] END
CASE <expr> WHEN <value1> THEN <result1> [ WHEN <value2> THEN <result2> ] [ ... ] [ ELSE <result3> ] END Argument |
CASE arg0 WHEN arg1 THEN arg 2 [WHEN argi THEN argj] [ELSE argk] |
|
COALESCE( <expr1> , <expr2> [ , ... , <exprN> ] ) |
COALESCE( <field name:identifier>, <field name:identifier> [, <field name:identifier> ]*) |
|
DECODE( <expr> , <search1> , <result1> [ , <search2> , <result2> ... ] [ , <default> ] ) |
CASE arg0 WHEN arg1 THEN arg 2 [WHEN argi THEN argj] [ELSE argk] |
|
GREATEST( <expr1> [ , <expr2> ... ] ) |
MAX ( <expression> ) OVER ( [ <window partition clause> ] [ <window order by clause> [ <window_frame_clause> ] ) : <type of the input expression> |
|
IFNULL( <expr1> , <expr2> ) |
CASE arg0 WHEN arg1 THEN arg 2 [WHEN argi THEN argj] [ELSE argk] |
|
LEAST( <expr> , ... ) |
MIN (expression) |
|
NULLIF( <expr1> , <expr2> ) |
nullif(expression, expression) |
|
NVL( <expr1> , <expr2> ) |
COALESCE( <field name:identifier>, <field name:identifier> [, <field name:identifier> ]*) |
|
NVL2( <expr1> , <expr2> , <expr3> ) |
COALESCE( <field name:identifier>, <field name:identifier> [, <field name:identifier> ]*) |
Analytical Functions
|
Snowflake Syntax |
Denodo Syntax |
|
CUME_DIST() OVER ( [ PARTITION BY <partition_expr> ] ORDER BY <order_expr> [ ASC | DESC ] ) |
CUME_DIST() OVER ( [ <window partition clause> ] <window order by clause> ) : number |
|
DENSE_RANK() OVER ( [ PARTITION BY <expr1> ] ORDER BY <expr2> [ ASC | DESC ] [ <window_frame> ] ) |
DENSE_RANK( ) OVER ( [ <window partition clause> ] <window order by clause> ) : numeric |
|
FIRST_VALUE( <expr> ) [ { IGNORE | RESPECT } NULLS ] OVER ( [ PARTITION BY <expr1> ] ORDER BY <expr2> [ { ASC | DESC } ] [ <window_frame> ] ) |
FIRST_VALUE( <expression> ) OVER ( [ <window partition clause> ] [ <window order by clause> ] [ <window_frame_clause> ] ) : <type of the input expression> |
|
LAG ( <expr> [ , <offset> , <default> ] ) [ { IGNORE | RESPECT } NULLS ] OVER ( [ PARTITION BY <expr1> ] ORDER BY <expr2> [ { ASC | DESC } ] ) |
LAG ( <expression> [ , <offset:number> [, <default value:type of the input expression> ] ] ) OVER ( [ <window partition clause> ] <window order by clause> ): <type of the input expression |
|
LAST_VALUE( <expr> ) [ { IGNORE | RESPECT } NULLS ] OVER ( [ PARTITION BY <expr1> ] ORDER BY <expr2> [ { ASC | DESC } ] [ <window_frame> ] ) |
LAST_VALUE( <expression> ) OVER ( [ <window partition clause> ] [ <window order by clause> ] [ <window_frame_clause> ] ) : <type of the input expression> |
|
LEAD ( <expr> [ , <offset> , <default> ] ) [ { IGNORE | RESPECT } NULLS ] OVER ( [ PARTITION BY <expr1> ] ORDER BY <expr2> [ { ASC | DESC } ] ) |
LEAD( <expression> [ , <offset:number> [, <default value:type of the input expression> ] ] ) OVER ( [ <window partition clause> ] <window order by clause> ): <type of the input expression> |
|
NTILE( <constant_value> ) OVER ( [ PARTITION BY <expr1> ] ORDER BY <expr2> [ { ASC | DESC } ] ) |
NTILE ( <value> OVER ( [ <window partition clause> ] <window order by clause> ): <number> |
|
PERCENT_RANK() OVER ( [ PARTITION BY <expr1> ] ORDER BY <expr2> [ { ASC | DESC } ] [ <cumulativeRangeFrame> ] ) |
PERCENT_RANK ( ) OVER ( [ <window partition clause> ] <window order by clause> ) : number |
|
RANK() OVER ( [ PARTITION BY <expr1> ] ORDER BY <expr2> [ { ASC | DESC } ] [ <window_frame> ] ) |
RANK( ) OVER ( [ <window partition clause> ] <window order by clause> ) : numeric |
|
PERCENTILE_DISC( <percentile> ) WITHIN GROUP (ORDER BY <order_by_expr> ) |
PERCENTILE_DIST ( <percentile:number> ) WITHIN GROUP ( <window order by clause> ) OVER ( [ <window partition clause> ] ) |
|
PERCENTILE_CONT( <percentile> ) WITHIN GROUP (ORDER BY <order_by_expr>) |
PERCENTILE_CONT ( <percentile:number> ) WITHIN GROUP ( <window order by clause> ) OVER ( [ <window partition clause> ] ) |
|
ROW_NUMBER() OVER ( [ PARTITION BY <expr1> [, <expr2> ... ] ] ORDER BY <expr3> [ , <expr4> ... ] [ { ASC | DESC } ] ) |
ROW_NUMBER ( ) OVER ( [ <window partition clause> ] <window order by clause> ) : numeric |
References
The information provided in the Denodo Knowledge Base is intended to assist our users in advanced uses of Denodo. Please note that the results from the application of processes and configurations detailed in these documents may vary depending on your specific environment. Use them at your own discretion.
For an official guide of supported features, please refer to the User Manuals. For questions on critical systems or complex environments we recommend you to contact your Denodo Customer Success Manager.
Questions
