Amazon Redshift SQL translation guide

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/cloud/onboard/02_migrate/01_migration_guides/06_redshift/03_sql_translation_reference.md)

## Data types[​](#data-types "Direct link to Data types")

Users moving data between ClickHouse and Redshift will immediately notice
that ClickHouse offers a more extensive range of types, which are also less
restrictive. While Redshift requires users to specify possible string
lengths, even if variable, ClickHouse removes this restriction and burden
from the user by storing strings without encoding as bytes. The ClickHouse
String type thus has no limits or length specification requirements.

Furthermore, you can exploit Arrays, Tuples, and Enums - absent from
Redshift as first-class citizens (although Arrays/Structs can be imitated
with `SUPER`) and a common frustration of users. ClickHouse additionally
allows the persistence, either at query time or even in a table, of
aggregation states. This will enable data to be pre-aggregated, typically
using a materialized view, and can dramatically improve query performance
for common queries.

Below we map the equivalent ClickHouse type for each Redshift type:

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Redshift ClickHouse|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [`SMALLINT`](https://docs.aws.amazon.com/redshift/latest/dg/r_Numeric_types201.html#r_Numeric_types201-integer-types) [`Int8`](/docs/sql-reference/data-types/int-uint) \*| [`INTEGER`](https://docs.aws.amazon.com/redshift/latest/dg/r_Numeric_types201.html#r_Numeric_types201-integer-types) [`Int32`](/docs/sql-reference/data-types/int-uint) \*| [`BIGINT`](https://docs.aws.amazon.com/redshift/latest/dg/r_Numeric_types201.html#r_Numeric_types201-integer-types) [`Int64`](/docs/sql-reference/data-types/int-uint) \*| [`DECIMAL`](https://docs.aws.amazon.com/redshift/latest/dg/r_Numeric_types201.html#r_Numeric_types201-decimal-or-numeric-type) [`UInt128`, `UInt256`, `Int128`, `Int256`](/docs/sql-reference/data-types/int-uint), [`Decimal(P, S)`, `Decimal32(S)`, `Decimal64(S)`, `Decimal128(S)`, `Decimal256(S)`](/docs/sql-reference/data-types/decimal) - (high precision and ranges possible)| [`REAL`](https://docs.aws.amazon.com/redshift/latest/dg/r_Numeric_types201.html#r_Numeric_types201-floating-point-types) [`Float32`](/docs/sql-reference/data-types/float)| [`DOUBLE PRECISION`](https://docs.aws.amazon.com/redshift/latest/dg/r_Numeric_types201.html#r_Numeric_types201-floating-point-types) [`Float64`](/docs/sql-reference/data-types/float)| [`BOOLEAN`](https://docs.aws.amazon.com/redshift/latest/dg/r_Boolean_type.html) [`Bool`](/docs/sql-reference/data-types/boolean)| [`CHAR`](https://docs.aws.amazon.com/redshift/latest/dg/r_Character_types.html#r_Character_types-char-or-character) [`String`](/docs/sql-reference/data-types/string), [`FixedString`](/docs/sql-reference/data-types/fixedstring)| [`VARCHAR`](https://docs.aws.amazon.com/redshift/latest/dg/r_Character_types.html#r_Character_types-varchar-or-character-varying) \*\* [`String`](/docs/sql-reference/data-types/string)| [`DATE`](https://docs.aws.amazon.com/redshift/latest/dg/r_Datetime_types.html#r_Datetime_types-date) [`Date32`](/docs/sql-reference/data-types/date32)| [`TIMESTAMP`](https://docs.aws.amazon.com/redshift/latest/dg/r_Datetime_types.html#r_Datetime_types-timestamp) [`DateTime`](/docs/sql-reference/data-types/datetime), [`DateTime64`](/docs/sql-reference/data-types/datetime64)| [`TIMESTAMPTZ`](https://docs.aws.amazon.com/redshift/latest/dg/r_Datetime_types.html#r_Datetime_types-timestamptz) [`DateTime`](/docs/sql-reference/data-types/datetime), [`DateTime64`](/docs/sql-reference/data-types/datetime64)| [`GEOMETRY`](https://docs.aws.amazon.com/redshift/latest/dg/geospatial-overview.html) [Geo Data Types](/docs/sql-reference/data-types/geo)| [`GEOGRAPHY`](https://docs.aws.amazon.com/redshift/latest/dg/geospatial-overview.html) [Geo Data Types](/docs/sql-reference/data-types/geo) (less developed e.g. no coordinate systems - can be emulated [with functions](/docs/sql-reference/functions/geo))| [`HLLSKETCH`](https://docs.aws.amazon.com/redshift/latest/dg/r_HLLSKTECH_type.html) [`AggregateFunction(uniqHLL12, X)`](/docs/sql-reference/data-types/aggregatefunction)| [`SUPER`](https://docs.aws.amazon.com/redshift/latest/dg/r_SUPER_type.html) [`Tuple`](/docs/sql-reference/data-types/tuple), [`Nested`](/docs/sql-reference/data-types/nested-data-structures/nested), [`Array`](/docs/sql-reference/data-types/array), [`JSON`](/docs/sql-reference/data-types/newjson), [`Map`](/docs/sql-reference/data-types/map)| [`TIME`](https://docs.aws.amazon.com/redshift/latest/dg/r_Datetime_types.html#r_Datetime_types-time) [`DateTime`](/docs/sql-reference/data-types/datetime), [`DateTime64`](/docs/sql-reference/data-types/datetime64)| [`TIMETZ`](https://docs.aws.amazon.com/redshift/latest/dg/r_Datetime_types.html#r_Datetime_types-timetz) [`DateTime`](/docs/sql-reference/data-types/datetime), [`DateTime64`](/docs/sql-reference/data-types/datetime64)| [`VARBYTE`](https://docs.aws.amazon.com/redshift/latest/dg/r_VARBYTE_type.html) \*\* [`String`](/docs/sql-reference/data-types/string) combined with [`Bit`](/docs/sql-reference/functions/bit-functions) and [Encoding](/docs/sql-reference/functions/encoding-functions#hex) functions | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |

\* ClickHouse additionally supports unsigned integers with extended ranges i.e. [`UInt8`, `UInt32`, `UInt32` and `UInt64`](http://clickhouse.com/docs/sql-reference/data-types/int-uint).
  
\*\*ClickHouse’s String type is unlimited by default but can be constrained to specific lengths using [Constraints](http://clickhouse.com/docs/sql-reference/statements/create/table#constraints).

## DDL syntax[​](#compression "Direct link to DDL syntax")

### Sorting keys[​](#sorting-keys "Direct link to Sorting keys")

Both ClickHouse and Redshift have the concept of a “sorting key”, which define
how data is sorted when being stored. Redshift defines the sorting key using the
`SORTKEY` clause:

```
CREATE TABLE some_table(...) SORTKEY (column1, column2)
```

Comparatively, ClickHouse uses an `ORDER BY` clause to specify the sort order:

```
CREATE TABLE some_table(...) ENGINE = MergeTree ORDER BY (column1, column2)
```

In most cases, you can use the same sorting key columns and order in ClickHouse
as Redshift, assuming you're using the default `COMPOUND` type. When data is
added to Redshift, you should run the `VACUUM` and `ANALYZE` commands to re-sort
newly added data and update the statistics for the query planner - otherwise, the
unsorted space grows. No such process is required for ClickHouse.

Redshift supports a couple of convenience features for sorting keys. The first is
automatic sorting keys (using `SORTKEY AUTO`). While this may be appropriate for
getting started, explicit sorting keys ensure the best performance and storage
efficiency when the sorting key is optimal. The second is the `INTERLEAVED` sort key,
which gives equal weight to a subset of columns in the sort key to improve
performance when a query uses one or more secondary sort columns. ClickHouse
supports explicit [projections](/docs/data-modeling/projections), which achieve the
same end-result with a slightly different setup.