Kinetica Dialect for SQLAlchemy

Kinetica provides a SQL interface through its SQLAlchemy dialect, which makes use of the DBAPI interface of Kinetica’s Python API.

Installation

The Kinetica Dialect for SQLAlchemy can be installed from PyPI using pip:

pip3 install sqlalchemy_kinetica

This will also install the supporting version of the Kinetica Python API. The package is also available in GitHub:

https://github.com/kineticadb/sqlalchemy-kinetica

Connecting

The standard SQLAlchemy create_engine call can be used to connect to a Kinetica database instance. For HTTPS-based installations, the bypass_ssl_cert_check option can be used to bypass any server certificate validation.

engine = create_engine(
    "kinetica://",
    connect_args = {
            "url": url,
            "username": username,
            "password": password
    }
)

Managing Connections

SQLAlchemy connections can either be automatically or manually managed. When manually managed, connections must be explicitly closed after use.

from sqlalchemy import text

with engine.connect() as conn:
    conn.execute(text("SELECT NOW()"))

Data Types

All Kinetica data types are available to be used in SQLAlchemy through either native SQLAlchemy types or Kinetica custom dialect types.

Number

Kinetica Data Type	SQLAlchemy Type Class
`BOOLEAN`	`sqlalchemy.Boolean`
`TINYINT`	`sqlalchemy_kinetica.kinetica_types.TINYINT`
`SMALLINT`	`sqlalchemy_kinetica.kinetica_types.SMALLINT`
`INTEGER`	`sqlalchemy.Integer`
`BIGINT`	`sqlalchemy.BIGINT`
`UNSIGNED BIGINT`	`sqlalchemy_kinetica.kinetica_types.UnsignedBigInteger`
`REAL`	`sqlalchemy.REAL`
`DOUBLE`	`sqlalchemy.DOUBLE`
`DECIMAL(P,S)`	`sqlalchemy_kinetica.kinetica_types.DECIMAL`

String

Kinetica Data Type	SQLAlchemy Type Class
`VARCHAR`	`sqlalchemy.VARCHAR`
`IPV4`	`sqlalchemy_kinetica.kinetica_types.IPV4`
`UUID`	`sqlalchemy.UUID`
`JSON`	`sqlalchemy_kinetica.kinetica_types.JSON`

Date/Time

Kinetica Data Type	SQLAlchemy Type Class
`DATE`	`sqlalchemy.DATE`
`DATETIME`	`sqlalchemy.DATETIME`
`TIME`	`sqlalchemy.TIME`
`TIMESTAMP`	`sqlalchemy.TIMESTAMP`

Binary

Kinetica Data Type	SQLAlchemy Type Class
`BLOB`	`sqlalchemy_kinetica.kinetica_types.BLOB`

Geospatial

Kinetica Data Type	SQLAlchemy Type Class
`GEOMETRY`	`sqlalchemy_kinetica.kinetica_types.GEOMETRY`
`BLOB(WKT)`	`sqlalchemy_kinetica.kinetica_types.BlobWKT`

Composite

Kinetica Data Type	SQLAlchemy Type Class
`BOOLEAN[N]`	`sqlalchemy.ARRAY`, `sqlalchemy.Boolean`
`INTEGER[N]`	`sqlalchemy.ARRAY`, `sqlalchemy.Integer`
`BIGINT[N]`	`sqlalchemy.ARRAY`, `sqlalchemy.BIGINT`
`VECTOR(N)`	`sqlalchemy_kinetica.kinetica_types.VECTOR`
`REAL[N]`	`sqlalchemy.ARRAY`, `sqlalchemy.REAL`
`DOUBLE[N]`	`sqlalchemy.ARRAY`, `sqlalchemy.DOUBLE`
`VARCHAR[N]`	`sqlalchemy.ARRAY`, `sqlalchemy.VARCHAR`

Creating Tables

The full set of Kinetica table operations, table options, and column types are available through the execution of Kinetica SQL as string literals. A large portion of the Kinetica table feature set is available via SQLAlchemy objects and the Kinetica dialect.

Column Types

The following demonstrates creating a table with various column types using a SQL literal as compared to using SQLAlchemy objects:

from sqlalchemy import text

SQL_CREATE_TABLE = """
    CREATE TABLE sqlalchemy.column_types
    (
        i    INTEGER NOT NULL,                                 /* non-nullable native integer, part of primary key (defined at end)                  */
        ti   TINYINT,                                          /* native int8                                                                        */
        si   SMALLINT,                                         /* native int16                                                                       */
        bi   BIGINT NOT NULL,                                  /* non-nullable native long, part of primary key (defined at end)                     */
        ubi  UNSIGNED BIGINT,                                  /* native unsigned long                                                               */
        b    BOOLEAN,                                          /* 0s and 1s only                                                                     */
        r    REAL,                                             /* native float                                                                       */
        db   DOUBLE,                                           /* native double                                                                      */
        dc   DECIMAL(10, 4),                                   /* native decimal                                                                     */
        v    VARCHAR(TEXT_SEARCH),                             /* string, searchable, only limited in size by system-configured value                */
        cd   VARCHAR(30, DICT),                                /* char32 using dictionary-encoding of values                                         */
        ct   VARCHAR(256, TEXT_SEARCH),                        /* char256, searchable                                                                */
        ip   IPV4,                                             /* IP address                                                                         */
        u    UUID(INIT_WITH_UUID),                             /* UUID                                                                               */
        d    DATE,                                             /* simple date                                                                        */
        t    TIME,                                             /* simple time                                                                        */
        dt   DATETIME(INIT_WITH_NOW),                          /* date/time                                                                          */
        ts   TIMESTAMP,                                        /* timestamp                                                                          */
        j    JSON,                                             /* JSON string                                                                        */
        bl   BLOB,                                             /* native bytes                                                                       */
        wkt  BLOB(WKT),                                        /* geospatial column for WKT binary data                                              */
        geo  GEOMETRY,                                         /* geospatial column for WKT string data                                              */
        vec  VECTOR(10),                                       /* vector column holding 10 floating point values                                     */
        ab   BOOLEAN[10],                                      /* array column holding 10 BOOLEAN values                                             */
        ai   INTEGER[10],                                      /* array column holding 10 INTEGER values                                             */
        abi  BIGINT[10],                                       /* array column holding 10 BIGINT values                                              */
        ar   REAL[10],                                         /* array column holding 10 REAL values                                                */
        adb  DOUBLE[10],                                       /* array column holding 10 DOUBLE values                                              */
        av   VARCHAR[10],                                      /* array column holding 10 VARCHAR values                                             */
        PRIMARY KEY (i, bi)                                    /* composite primary key on i & bi columns, which must be NOT NULL                    */
    )
"""

conn.execute(text(SQL_CREATE_TABLE))

Replicated Tables

The following demonstrates creating a replicated table using a SQL literal as compared to using SQLAlchemy objects:

from sqlalchemy import text

SQL_CREATE_TABLE = """
    CREATE REPLICATED TABLE sqlalchemy.employee
    (
        id INTEGER NOT NULL,
        dept_id INTEGER NOT NULL,
        manager_id INTEGER,
        first_name VARCHAR(30),
        last_name VARCHAR(30),
        sal DECIMAL(18,4),
        hire_date DATE,
        work_district WKT,
        office_longitude REAL,
        office_latitude REAL,
        profile VECTOR(10),
        PRIMARY KEY (id)
    )
"""

conn.execute(text(SQL_CREATE_TABLE))

Sharded Tables with Options

The following demonstrates creating a sharded table with a variety of table options applied, using a SQL literal as compared to using SQLAlchemy objects:

from sqlalchemy import text

SQL_CREATE_TABLE = """
    CREATE OR REPLACE TABLE sqlalchemy.employee
    (
        id INTEGER NOT NULL,
        dept_id INTEGER NOT NULL,
        manager_id INTEGER,
        first_name VARCHAR(30),
        last_name VARCHAR(30),
        sal DECIMAL(18,4),
        hire_date DATE,
        work_district WKT,
        office_longitude REAL,
        office_latitude REAL,
        profile VECTOR(10),
        PRIMARY KEY (id, dept_id),
        SHARD KEY (dept_id)
    )
    PARTITION BY RANGE (YEAR(hire_date))
    PARTITIONS
    (
        order_2018_2020 MIN(2018) MAX(2021),
        order_2021                MAX(2022),
        order_2022                MAX(2023),
        order_2023                MAX(2024)
    )
    TIER STRATEGY
    (
        ( ( VRAM 1, RAM 7, PERSIST 5 ) )
    )
    INDEX (dept_id)
    CHUNK SKIP INDEX (id)
    GEOSPATIAL INDEX (work_district)
    GEOSPATIAL INDEX (office_longitude, office_latitude)
    CAGRA INDEX (profile)
    USING TABLE PROPERTIES (CHUNK SIZE = 1000000, NO_ERROR_IF_EXISTS = true, TTL = 120)
"""

conn.execute(text(SQL_CREATE_TABLE))

CREATE TABLE…AS

CREATE TABLE … AS is supported in both native SQL and in the Kinetica Dialect for SQLAlchemy as the following examples show:

from sqlalchemy import text

SQL_CREATE_TABLE = """
    CREATE OR REPLACE REPLICATED TEMP TABLE sqlalchemy.new_temp_employee AS
    (
        SELECT *
        FROM sqlalchemy.employee
    )
"""

conn.execute(text(SQL_CREATE_TABLE))

External Tables

CREATE EXTERNAL TABLE is supported in both native SQL and in the Kinetica Dialect for SQLAlchemy as the following examples show:

from sqlalchemy import text

SQL_CREATE_TABLE = """
    CREATE EXTERNAL TABLE sqlalchemy.remote_employee
    REMOTE QUERY 'SELECT * EXCLUDE(profile) FROM sqlalchemy.employee'
    WITH OPTIONS
    (
        DATA SOURCE = 'sqlalchemy.jdbc_ds',
        SUBSCRIBE = TRUE,
        REMOTE_QUERY_INCREASING_COLUMN = 'id'
    )
    USING TABLE PROPERTIES (CHUNK SIZE = 1000000, NO_ERROR_IF_EXISTS = true, TTL = 120)
"""

conn.execute(text(SQL_CREATE_TABLE))

Managing Data

The full set of Kinetica data manipulation operations are available through both the execution of Kinetica SQL as string literals as well as via SQLAlchemy objects and the Kinetica dialect.

Inserting Data as Values

The following demonstrates inserting data as raw values using a SQL literal as compared to using SQLAlchemy objects:

from sqlalchemy import text

SQL_INSERT = """
    INSERT INTO sqlalchemy.employee (id, dept_id, manager_id, first_name, last_name, sal, hire_date)
    VALUES /* KI_HINT_UPDATE_ON_EXISTING_PK */
        (1, 1, null, 'Anne',     'Arbor',   200000,      '2000-01-01'),
        (2, 2,    1, 'Brooklyn', 'Bridges', 100000,      '2000-02-01'),
        (3, 3,    1, 'Cal',      'Cutta',   100000,      '2000-03-01'),
        (4, 2,    2, 'Dover',    'Della',   150000,      '2000-04-01'),
        (5, 2,    2, 'Elba',     'Eisle',    50000,      '2000-05-01'),
        (6, 4,    1, 'Frank',    'Furt',     12345.6789, '2000-06-01')
"""

conn.execute(text(SQL_INSERT))

Inserting Data from a Query

The following demonstrates inserting data from a query using a SQL literal as compared to using SQLAlchemy objects:

from sqlalchemy import text

SQL_INSERT = """
    INSERT INTO sqlalchemy.employee_backup (id, dept_id, manager_id, first_name, last_name, sal)
    SELECT id, dept_id, manager_id, first_name, last_name, sal
    FROM sqlalchemy.employee
    WHERE hire_date >= '2000-04-01'
"""

conn.execute(text(SQL_INSERT))

Inserting Data using a CTE

The following demonstrates inserting data with a CTE using a SQL literal as compared to using SQLAlchemy objects:

from sqlalchemy import text

SQL_INSERT = """
    INSERT INTO sqlalchemy.dept2_emp_mgr_roster (emp_first_name, emp_last_name, mgr_first_name, mgr_last_name)
    WITH
        dept2_emp AS
        (
            SELECT first_name, last_name, manager_id
            FROM sqlalchemy.employee
            WHERE dept_id = 2
        ),
        dept2_mgr AS
        (
            SELECT first_name, last_name, id
            FROM sqlalchemy.employee
            WHERE dept_id = 2
        )
    SELECT d2emp.first_name, d2emp.last_name, d2mgr.first_name, d2mgr.last_name
    FROM
        dept2_emp as d2emp
        JOIN dept2_mgr as d2mgr ON d2emp.manager_id = d2mgr.id
"""

conn.execute(text(SQL_INSERT))

Updating Data with Constants

The following demonstrates updating data with raw values using a SQL literal as compared to using SQLAlchemy objects:

from sqlalchemy import text

SQL_UPDATE = """
    UPDATE sqlalchemy.employee
    SET sal = sal * 1.05
"""

conn.execute(text(SQL_UPDATE))

Updating Data with Subquery Filter

The following demonstrates updating data with a subquery filter (subquery in the WHERE clause) using a SQL literal as compared to using SQLAlchemy objects:

from sqlalchemy import text

SQL_UPDATE = """
    UPDATE sqlalchemy.employee b
    SET sal = sal * 1.05
    WHERE sal =
    (
        SELECT MIN(sal)
        FROM sqlalchemy.employee l
        WHERE b.dept_id = l.dept_id
    )
"""

conn.execute(text(SQL_UPDATE))

Updating Data with Subquery Assignment

The following demonstrates updating data with a subquery assignment (subquery in the SET clause) using a SQL literal as compared to using SQLAlchemy objects:

from sqlalchemy import text

SQL_UPDATE = """
    UPDATE  sqlalchemy.employee b
    SET     sal =
            (
                SELECT MAX(sal)
                FROM sqlalchemy.employee l
                WHERE l.dept_id = b.dept_id
            ) * .1 + sal * .9
"""

conn.execute(text(SQL_UPDATE))

Updating Data with Join

The following demonstrates updating data with a join using a SQL literal as compared to using SQLAlchemy objects:

from sqlalchemy import text

SQL_UPDATE = """
    UPDATE eb
    SET sal = e.sal, manager_id = e.manager_id
    FROM sqlalchemy.employee_backup eb
    JOIN sqlalchemy.employee e ON eb.id = e.id
"""

conn.execute(text(SQL_UPDATE))

Deleting Data with Constants

The following demonstrates deleting data with raw values using a SQL literal as compared to using SQLAlchemy objects:

from sqlalchemy import text

SQL_DELETE = """
    DELETE
    FROM sqlalchemy.employee
    WHERE id = 6
"""

conn.execute(text(SQL_DELETE))

Deleting Data with Subquery Filter

The following demonstrates deleting data with a subquery filter (subquery in the WHERE clause) using a SQL literal as compared to using SQLAlchemy objects:

from sqlalchemy import text

SQL_DELETE = """
    DELETE
    FROM sqlalchemy.employee b
    WHERE id =
        (
            SELECT MAX(l.id)
            FROM sqlalchemy.employee l
            WHERE b.dept_id = l.dept_id
        )
"""

conn.execute(text(SQL_DELETE))

Querying Data

The full Kinetica query capability is available through the execution of Kinetica SQL as string literals. Much of the Kinetica query feature set is available via SQLAlchemy objects and the Kinetica dialect.

CTE

The following demonstrates a CTE query using a SQL literal as compared to using SQLAlchemy objects:

from sqlalchemy import text

SQL_SELECT = """
    WITH
        dept2_emp_sal_by_mgr (manager_id, salary) AS
        (
            SELECT manager_id, sal
            FROM sqlalchemy.employee
            WHERE dept_id = 2
        )
    SELECT
        manager_id dept2_mgr_id,
        MAX(salary) dept2_highest_emp_sal_per_mgr,
        COUNT(*) as dept2_total_emp_per_mgr
    FROM dept2_emp_sal_by_mgr
    GROUP BY manager_id
"""

result = conn.execute(text(SQL_SELECT))

Join

The following demonstrates a join query using a SQL literal as compared to using SQLAlchemy objects:

from sqlalchemy import text

SQL_SELECT = """
    SELECT
        e.last_name || ', ' || e.first_name AS "Employee_Name",
        m.last_name || ', ' || m.first_name AS "Manager_Name"
    FROM
        sqlalchemy.employee e
        LEFT JOIN sqlalchemy.employee m ON e.manager_id = m.id
    WHERE
        e.dept_id IN (1, 2, 3)
    ORDER BY
        m.id ASC NULLS FIRST,
        e.hire_date
"""

result = conn.execute(text(SQL_SELECT))

ASOF Join

The following demonstrates an ASOF join query using a SQL literal as compared to using SQLAlchemy objects:

from sqlalchemy import text

SQL_SELECT = """
    SELECT
        t.id,
        t.dt AS execution_dt,
        q.open_dt AS quote_dt,
        t.price AS execution_price,
        q.open_price
    FROM
        sqlalchemy.trades t
        LEFT JOIN sqlalchemy.quotes q ON
            t.ticker = q.symbol AND
            ASOF(t.dt, q.open_dt, INTERVAL '-1' DAY, INTERVAL '0' DAY, MAX)
"""

result = conn.execute(text(SQL_SELECT))

Aggregation (GROUP BY)

The following demonstrates an aggregation query using a SQL literal as compared to using SQLAlchemy objects:

from sqlalchemy import text

SQL_SELECT = """
    SELECT dept_id AS "DepartmentID", COUNT(*) AS "TotalEmployees", SUM(sal) AS "TotalSalary"
    FROM sqlalchemy.employee
    GROUP BY dept_id
"""

result = conn.execute(text(SQL_SELECT))

Aggregation (ROLLUP)

The following demonstrates an aggregated roll-up query using a SQL literal as compared to using SQLAlchemy objects:

from sqlalchemy import text

SQL_SELECT = """
    SELECT
        CASE
            WHEN (GROUPING(dept_id) = 1) THEN '<Total>'
            ELSE NVL(STRING(dept_id), '<No Department>')
        END AS "DepartmentID",
        COUNT(*) AS "TotalEmployees",
        SUM(sal) AS "TotalSalary"
    FROM sqlalchemy.employee
    GROUP BY ROLLUP(dept_id)
    ORDER BY "DepartmentID"
"""

result = conn.execute(text(SQL_SELECT))

Aggregation (GROUPING SETS)

The following demonstrates an aggregated grouping sets query using a SQL literal as compared to using SQLAlchemy objects:

from sqlalchemy import text

SQL_SELECT = """
    SELECT
        CASE
            WHEN (GROUPING(dept_id) = 1) THEN '<Total>'
            ELSE NVL(STRING(dept_id), '<No Department>')
        END AS "DepartmentID",
        CASE
            WHEN (GROUPING(manager_id) = 1) THEN '<Total>'
            ELSE NVL(STRING(manager_id), '<No Manager>')
        END AS "ManagerID",
        COUNT(*) AS "TotalEmployees",
        SUM(sal) AS "TotalSalary"
    FROM sqlalchemy.employee
    GROUP BY GROUPING SETS ((dept_id), (manager_id), ())
    ORDER BY "DepartmentID", "ManagerID"
"""

result = conn.execute(text(SQL_SELECT))

Window Functions (Rolling Sum)

The following demonstrates a rolling sum window function query using a SQL literal as compared to using SQLAlchemy objects:

from sqlalchemy import text

SQL_SELECT = """
    SELECT
        ticker AS "Stock",
        dt AS "TradeDateTime",
        price AS "TradePrice",
        DECIMAL
        (
            SUM(price) OVER
                (
                    PARTITION BY ticker
                    ORDER BY dt
                )
        ) AS "TotalTraded"
    FROM sqlalchemy.trades
    ORDER BY ticker, dt
"""

result = conn.execute(text(SQL_SELECT))

Window Functions (Moving Average)

The following demonstrates a moving average window function query using a SQL literal as compared to using SQLAlchemy objects:

from sqlalchemy import text

SQL_SELECT = """
    SELECT
        ticker AS "Stock",
        dt AS "TradeDateTime",
        price AS "TradePrice",
        DECIMAL
        (
            AVG(price) OVER
                (
                    PARTITION BY ticker
                    ORDER BY dt
                    RANGE BETWEEN INTERVAL 2 DAYS PRECEDING AND INTERVAL 1 DAY FOLLOWING
                )
        ) AS "AverageTradePrice"
    FROM sqlalchemy.trades
    ORDER BY ticker, dt
"""

result = conn.execute(text(SQL_SELECT))

Window Functions (Ranking)

The following demonstrates a ranking window function query using a SQL literal as compared to using SQLAlchemy objects:

from sqlalchemy import text

SQL_SELECT = """
    SELECT
        ticker AS "Stock",
        dt AS "TradeDateTime",
        price AS "TradePrice",
        RANK() OVER
            (
                PARTITION BY ticker
                ORDER BY price
            ) AS "RankedTrade",
        DECIMAL
        (
            PERCENT_RANK() OVER
                (
                    PARTITION BY ticker
                    ORDER BY price
                )
        ) * 100 AS "PercentRankedTrade"
    FROM sqlalchemy.trades
    ORDER BY ticker, dt
"""

result = conn.execute(text(SQL_SELECT))

Pivot

The following demonstrates a pivot query using a SQL literal as compared to using SQLAlchemy objects:

from sqlalchemy import text

SQL_SELECT = """
    SELECT
        name,
        Home_Phone,
        Work_Phone,
        Cell_Phone
    FROM
        sqlalchemy.phone_list
    PIVOT
    (
        MAX(phone_number) AS Phone
        FOR phone_type IN ('Home', 'Work', 'Cell')
    )
    ORDER BY name
"""

result = conn.execute(text(SQL_SELECT))

Unpivot

The following demonstrates an unpivot query using a SQL literal as compared to using SQLAlchemy objects:

from sqlalchemy import text

SQL_SELECT = """
    SELECT name, phone_type, phone_number
    FROM
    (
        SELECT
            name,
            Home_Phone AS Home,
            Work_Phone AS Work,
            Cell_Phone AS Cell
        FROM
            sqlalchemy.customer_contact
    )
    UNPIVOT (phone_number FOR phone_type IN (Home, Work, Cell))
    ORDER BY name, phone_type
"""

result = conn.execute(text(SQL_SELECT))

Set Union (Deduplicate)

The following demonstrates a set union query that keeps duplicate records using a SQL literal as compared to using SQLAlchemy objects:

from sqlalchemy import text

SQL_SELECT = """
    SELECT
        food_name,
        category,
        price
    FROM
        sqlalchemy.lunch_menu
    UNION
    SELECT
        food_name,
        category,
        price
    FROM
        sqlalchemy.dinner_menu
    ORDER BY
        food_name,
        category,
        price
"""

result = conn.execute(text(SQL_SELECT))

Set Union (Keep Duplicates)

The following demonstrates a set union query that discards duplicate records using a SQL literal as compared to using SQLAlchemy objects:

from sqlalchemy import text

SQL_SELECT = """
    SELECT
        food_name,
        category,
        price
    FROM
        sqlalchemy.lunch_menu
    UNION ALL
    SELECT
        food_name,
        category,
        price
    FROM
        sqlalchemy.dinner_menu
    ORDER BY
        food_name,
        category,
        price
"""

result = conn.execute(text(SQL_SELECT))

Set Intersection

The following demonstrates a set intersection query that discards duplicate records using a SQL literal as compared to using SQLAlchemy objects:

from sqlalchemy import text

SQL_SELECT = """
    SELECT
        food_name,
        category,
        price
    FROM
        sqlalchemy.lunch_menu
    INTERSECT
    SELECT
        food_name,
        category,
        price
    FROM
        sqlalchemy.dinner_menu
    ORDER BY
        food_name,
        category,
        price
"""

result = conn.execute(text(SQL_SELECT))

Set Exception

The following demonstrates a set exception query that discards duplicate records using a SQL literal as compared to using SQLAlchemy objects:

from sqlalchemy import text

SQL_SELECT = """
    SELECT
        food_name,
        category,
        price
    FROM
        sqlalchemy.lunch_menu
    EXCEPT
    SELECT
        food_name,
        category,
        price
    FROM
        sqlalchemy.dinner_menu
    ORDER BY
        food_name,
        category,
        price
"""

result = conn.execute(text(SQL_SELECT))

Full Text Search

The following demonstrates using the full text search feature using a SQL literal as compared to using SQLAlchemy objects:

from sqlalchemy import text

SQL_SELECT = """
    SELECT *
    FROM TABLE
    (
        FILTER_BY_STRING
        (
            TABLE_NAME => INPUT_TABLE(SELECT event_time, message FROM sqlalchemy.event_log),
            COLUMN_NAMES => 'message',
            MODE => 'contains',
            EXPRESSION => 'ERROR'
        )
    )
"""

result = conn.execute(text(SQL_SELECT))

​Installation

​Connecting

​Managing Connections

​Data Types

​Number

​String

​Date/Time

​Binary

​Geospatial

​Composite

​Creating Tables

​Column Types

​Replicated Tables

​Sharded Tables with Options

​CREATE TABLE…AS

​External Tables

​Managing Data

​Inserting Data as Values

​Inserting Data from a Query

​Inserting Data using a CTE

​Updating Data with Constants

​Updating Data with Subquery Filter

​Updating Data with Subquery Assignment

​Updating Data with Join

​Deleting Data with Constants

​Deleting Data with Subquery Filter

​Querying Data

​CTE

​Join

​ASOF Join

​Aggregation (GROUP BY)

​Aggregation (ROLLUP)

​Aggregation (GROUPING SETS)

​Window Functions (Rolling Sum)

​Window Functions (Moving Average)

​Window Functions (Ranking)

​Pivot

​Unpivot

​Set Union (Deduplicate)

​Set Union (Keep Duplicates)

​Set Intersection

​Set Exception

​Full Text Search