Set up your query
After you've set up your data source, continue with SQL Transformations by setting up your query.
Set up your query
After specifying a data source, you can define the query that acts on the data. Query inputs in SQL Transformations support all standard operations offered by a standard SQL query engine.
Example query setup
The following query merges the employee and zipcode tables together based on the zipcode column in both tables.
Example query setup
CAPITALIZATION
In the preceding example, all of the CSV headers are lowercase. If your query contains capitalized CSV headers, you must enclose the capitalized headers in quotation marks (""). For example:
sql
SELECT distinct test."PropertyId", test."Tract_Business" FROM test ORDER by test."PropertyId" LIMIT 1 OFFSET 2Supported operations
The following section contains lists of supported and unsupported data types, syntax clauses, subqueries, and functions in SQL Transformations.
Data types
Character types
- CHAR
- VARCHAR
- TEXT
- STRING
Numeric types
- TINYINT
- SMALLINT
- INT or INTEGER
- BIGINT
- TINYINT UNSIGNED
- SMALLINT UNSIGNED
- INT UNSIGNED or INTEGER UNSIGNED
- BIGINT UNSIGNED
- FLOAT
- REAL
- DOUBLE
- DECIMAL(precision, scale)
Date/Time types
- DATE
- TIME
- TIMESTAMP
- INTERVAL
Boolean types
- BOOLEAN
Binary types
- BYTEA
Arrow types
- Null
- Boolean
- Int8
- Int16
- Int32
- Int64
- UInt8
- UInt16
- UInt32
- UInt64
- Float16
- Float32
- Float64
- Utf8
- LargeUtf8
- Binary
- Timestamp(Second, None)
- Timestamp(Millisecond, None)
- Timestamp(Microsecond, None)
- Timestamp(Nanosecond, None)
- Time32
- Time64
- Duration(Second)
- Duration(Millisecond)
- Duration(Microsecond)
- Duration(Nanosecond)
- Interval(YearMonth)
- Interval(DayTime)
- Interval(MonthDayNano)
- FixedSizeBinary(<len>)
- Example: FixedSizeBinary(16)
- Decimal128(<precision>, <scale>)
- Example: Decimal128(3, 10)
- Decimal256(<precision>, <scale>)
- Example: Decimal256(3, 10)
Unsupported data types
Unsupported types
- UUID
- BLOB
- CLOB
- BINARY
- VARBINARY
- REGCLASS
- NVARCHAR
- CUSTOM
- ARRAY
- ENUM
- SET
- DATETIME
SELECT syntax clauses
SELECT syntax clauses supported
- WITH
- SELECT
- FROM
- WHERE
- JOIN
- INNER JOIN
- LEFT OUTER JOIN
- RIGHT OUTER JOIN
- FULL OUTER JOIN
- NATURAL JOIN
- CROSS JOIN
- GROUP BY
- HAVING
- UNION
- ORDER BY
- LIMIT
- EXCLUDE and EXCEPT
Subqueries
SELECT subqueries supported
- EXISTS
- NOT EXISTS
- IN
- NOT IN
- Scalar Subquery
Operators
Numerical operators
+(plus)-(minus)*(multiply)/(divide)%(modulo)
Comparison operators
=(equal)!=(not equal)<(less than)<=(less than or equal to)>(greater than)>=(greater than or equal to)- IS DISTINCT FROM
- IS NOT DISTINCT FROM
~(regex match)~*(regex case-insensitive match)!~(not regex match)!~*(not regex case-insensitive match)
Logical operators
- AND
- OR
Bitwise operators
&(bitwise and)|(bitwise or)#(bitwise xor)>>(bitwise shift right)<<(bitwise shift left)
Other operators
||(string concatenation)@>(array contains)<@(array is contained by)
Aggregate functions
General
- avg
- bit_and
- bit_or
- bit_xor
- bool_and
- bool_or
- count
- max
- mean
- median
- min
- sum
- array_agg
- first_value
- last_value
Statistical
- corr
- covar
- covar_pop
- covar_samp
- stddev
- stddev_pop
- stddev_samp
- var
- var_pop
- var_samp
- regr_avgx
- regr_avgy
- regr_count
- regr_intercept
- regr_r2
- regr_slope
- regr_sxx
- regr_syy
- regr_sxy
Approximate
- approx_distinct
- approx_median
- approx_percentile_cont
- approx_percentile_cont_with_weight
Window functions
Aggregate functions
All aggregate functions can be used as window functions.
Ranking functions
- row_number
- rank
- dense_rank
- ntile
Analytical functions
- cume_dist
- percent_rank
- lag
- lead
- first_value
- last_value
- nth_value
Scalar functions
Math functions
- abs(x)
- acos(x)
- acosh(x)
- asin(x)
- asinh(x)
- atan(x)
- atanh(x)
- atan2(y, x)
- cbrt(x)
- ceil(x)
- cos(x)
- cosh(x)
- degrees(x)
- exp(x)
- factorial(x)
- floor(x)
- gcd(x, y)
- isnan(x)
- iszero(x)
- lcm(x, y)
- ln(x)
- log(base, x)
- log10(x)
- log2(x)
- nanvl(x, y)
- pi()
- power(base, exponent)
- pow(base, exponent)
- radians(x)
- random()
- round(x[, decimal_places])
- signum(x)
- sin(x)
- sinh(x)
- sqrt(x)
- tan(x)
- tanh(x)
- trunc(x[, decimal_places])
Conditional functions
- coalesce
- nullif
- nvl
- nvl2
- ifnull
String functions
- ascii
- bit_length
- btrim
- char_length
- character_length
- concat
- concat_ws
- chr
- ends_with
- initcap
- instr
- left
- length
- lower
- lpad
- ltrim
- octet_length
- repeat
- replace
- reverse
- right
- rpad
- rtrim
- split_part
- starts_with
- strpos
- substr
- to_hex
- translate
- trim
- upper
- uuid
- overlay
- levenshtein
- substr_index
- find_in_set
- position
- contains
Binary string functions
- decode
- encode
Regular expression functions
- regexp_like
- regexp_match
- regexp_replace
Temporal functions
- now
- current_date
- current_time
- date_bin
- date_trunc
- datetrunc
- date_part
- datepart
- extract
- today
- make_date
- to_char(expression, format)
- Example: to_char("Date", "%Y-%m-%d")
- to_date
- to_local_time
- to_timestamp
- to_timestamp_millis
- to_timestamp_micros
- to_timestamp_nanos
- to_timestamp_seconds
- from_unixtime
Array functions
- array_append
- array_sort
- array_cat
- array_concat
- array_contains
- array_dims
- array_distinct
- array_has
- array_has_all
- array_has_any
- array_element
- array_empty
- array_except
- array_extract
- array_fill
- array_indexof
- array_intersect
- array_join
- array_length
- array_ndims
- array_prepend
- array_pop_front
- array_pop_back
- array_position
- array_positions
- array_push_back
- array_push_front
- array_repeat
- array_resize
- array_remove
- array_remove_n
- array_remove_all
- array_replace
- array_replace_n
- array_replace_all
- array_reverse
- array_slice
- array_to_string
- array_union
- cardinality
- empty
- flatten
- generate_series
- list_append
- list_sort
- list_cat
- list_concat
- list_dims
- list_distinct
- list_element
- list_except
- list_extract
- list_has
- list_has_all
- list_has_any
- list_indexof
- list_intersect
- list_join
- list_length
- list_ndims
- list_prepend
- list_pop_back
- list_pop_front
- list_position
- list_positions
- list_push_back
- list_push_front
- list_repeat
- list_resize
- list_remove
- list_remove_n
- list_remove_all
- list_replace
- list_replace_n
- list_replace_all
- list_slice
- list_to_string
- list_union
- make_array
- make_list
- string_to_array
- string_to_list
- trim_array
- unnest
- range
Struct functions
- struct
- named_struct
- unnest
Hashing functions
- digest
- md5
- sha224
- sha256
- sha384
- sha512
Other functions
- arrow_cast
- arrow_typeof
Read next
SAMPLE USE CASES
See our guides for step-by-step instructions on how to leverage SQL Transformations for the following use cases:
Last updated: