Skip to main content

Tables

Definition

CREATE TABLE part for the ‘max_size’ option.

‘max_size        –       This option is required as it tells how many rows may be inserted into the table and determines the size of memory allocated on GPU.

CREATE TABLE

CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( [
  column_name data_type [ OPTIONS ( option 'value' [, ... ] ) ]
[, ... ]
] )
SERVER server_name
[ OPTIONS ( option 'value' [, ... ] ) ]
DISTRIBUTE BY { REPLICATION | ROUNDROBIN | { [HASH | MODULO ] ( column_name ) } } |
where column_constraint is:
[ CONSTRAINT constraint_name ]
{ NOT NULL |
NULL |
DEFAULT default_expr }
Options:
max_size 'number_of_rows'                      required
index 'column_number [ [, ...] : column_number optional

Example

CREATE FOREIGN TABLE lineitem
( l_orderkey INT,
l_partkey INT,
l_suppkey INT,
l_linenumber INT,
l_quantity REAL,
l_extendedprice REAL,
l_discount REAL,
l_tax REAL,
l_returnflag VARCHAR(1),
l_linestatus VARCHAR(1),
l_shipdate DATE,
l_commitdate DATE,
l_receiptdate DATE,
l_shipinstruct VARCHAR(25),
l_shipmode VARCHAR(10),
l_comment VARCHAR(44)
) SERVER gm_fdw_server
OPTIONS (max_size '20000000', index '6:7:6,7')
DISTRIBUTE BY HASH(l_orderkey);

Description

Brytlyt uses Foreign Data Wrapper functionality to implement table creation.

Sharding

Data can be partitioned across the cluster using a range of mechanisms.

DISTRIBUTE BY – This clause specifies how the table is distributed or replicated among Datanodes.

REPLICATION – Each row of the table will be replicated to all the Datanodes of the database cluster.

ROUNDROBIN – Each row of the table will be placed in one of the Datanodes in a round-robin manner. The value of the row will not be needed to determine what Datanode to go.

HASH ( column_name ) – Each row of the table will be placed based on the hash value of the specified column.

MODULO ( column_name ) – Each row of the table will be placed based on the modulo of the specified column.

DROP TABLE

DROP FOREIGN TABLE [ IF EXISTS ] table_name;

Example

DROP FOREIGN TABLE IF EXISTS lineitem;

Indexes

Indexes should be specified while creating a table with additional ‘index’ option. A single index is defined by a list of columns numbers (starting from 0) separated by commas. Indexes are separated by a colon. For example:

CREATE FOREIGN TABLE
...
SERVER gm_fdw_server
OPTIONS
(
max_size '10',
is_random 'false',
index '1:0,1:0,2,3'
);

The above example shows three indexes, first for column 1, second for columns 0 and 1, third for columns 0, 2 and 3.

Copy Tables

Allows copying data between tables:

COPY_TABLE relname [(columnList)] TO relname [(columnList)]
COPY_TABLE ( SELECT ... ) TO relname [(columnList)]

We can copy the whole table or choose appropriate columns, another option is to use SELECT statement to fetch data which will be copied to some table.