PARTITION BY

Description

All rows of a table partition are physically colocated. (Sybase SQL Language Reference)

This syntax is not needed in Snowflake.

Grammar Syntax

PARTITION BY 
     <range-partitioning-scheme>
     | <hash-partitioning-scheme> 
     | <composite-partitioning-scheme>
     
<range-partitioning-scheme> ::=
   RANGE ( <partition-key> ) ( <range-partition-decl> [,<range-partition-decl> … ] )

<partition-key> ::= <column-name>

<range-partition-declaration> ::=
    <range-partition-name> VALUES <= ( {<constant> |  MAX } ) [ IN <dbspace-name> ]

<hash-partitioning-scheme> ::=
   HASH ( <partition-key> [ , <partition-key>, … ] )

<composite-partitioning-scheme> ::=
   <hash-partitioning-scheme> SUBPARTITION BY <range-partitioning-scheme>

Sample Source Patterns

Input Code:

Sybase

IN -> Sybase_01.sql

-- Range Partitioning
CREATE TABLE sales (
    sale_id INT,
    sale_date DATE,
    amount DECIMAL(10, 2)
)
PARTITION BY RANGE (sale_date) (
    PARTITION p1 VALUES <= ('2023-01-01'),
    PARTITION p2 VALUES <= ('2024-01-01'),
    PARTITION p3 VALUES < MAXVALUE
);

-- Hash Partitioning
CREATE TABLE customers (
    customer_id INT,
    customer_name VARCHAR(255)
)
PARTITION BY HASH (customer_id) (
    PARTITION p1,
    PARTITION p2,
    PARTITION p3,
    PARTITION p4
);

-- Composite Partitioning (Hash-Range)
CREATE TABLE orders (
    order_id INT,
    customer_id INT,
    order_date DATE,
    amount DECIMAL(10,2)
)
PARTITION BY HASH (customer_id)
    SUBPARTITION BY RANGE (order_date) (
        PARTITION p1 VALUES <= ('2023-01-01'),
        PARTITION p2 VALUES <= ('2024-01-01'),
        PARTITION p3 VALUES < MAXVALUE
    );

Output Code:

Snowflake

OUT -> Sybase_01.sql

-- Range Partitioning
CREATE TABLE sales (
    sale_id INT,
    sale_date DATE,
    amount DECIMAL(10, 2)
)
;

-- Hash Partitioning
CREATE TABLE customers (
    customer_id INT,
    customer_name VARCHAR(255)
);

-- Composite Partitioning (Hash-Range)
CREATE TABLE orders (
    order_id INT,
    customer_id INT,
    order_date DATE,
    amount DECIMAL(10,2)
);