Product UpdatesStatus PageAutomation Institute
English
Get a trial
Data orchestration
Data transformation
SQL Transformations

# 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 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
  • 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
  1. Configure your output
  2. Output fields

SAMPLE USE CASES

See our guides for step-by-step instructions on how to leverage SQL Transformations for the following use cases:

← Set up your data source
Configure the output →


Last updated: 7/11/2024, 8:12:26 PM

On this page