# 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:

SELECT distinct test."PropertyId", test."Tract_Business" FROM test ORDER by test."PropertyId" LIMIT 1 OFFSET 2

# Supported 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

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: 7/11/2024, 8:12:26 PM