Skip to content

Instantly share code, notes, and snippets.

View onderkalaci's full-sized avatar

Önder Kalacı onderkalaci

View GitHub Profile
CREATE TABLE lineitem (
l_orderkey bigint not null,
l_partkey integer not null,
l_suppkey integer not null,
l_linenumber integer not null,
l_quantity decimal(15, 2) not null,
l_extendedprice decimal(15, 2) not null,
l_discount decimal(15, 2) not null,
-- On master and worker nodes:
CREATE SCHEMA test_schema;
set search_path to test_schema;
-- On master node:
CREATE TABLE customer_reviews_hash
(
customer_id TEXT,
review_date DATE,
---------------------------- TEST 1 - Shard Creation ------------------------
CREATE SCHEMA tpch;
set search_path to tpch;
CREATE TABLE nation_append (
n_nationkey integer not null,
n_name char(25) not null,
n_regionkey integer not null,
---------------------------- TEST 15 -- Create FDWs on different schema ------------------------
CREATE SCHEMA fdw_test;
SET search_path TO public;
CREATE EXTENSION cstore_fdw;
-- create server object
CREATE SERVER cstore_server FOREIGN DATA WRAPPER cstore_fdw;
SELECT cluster_add_node('localhost', 9700);
SELECT cluster_activate_node('localhost', 9700);
SELECT cluster_add_node('localhost', 9701);
SELECT cluster_activate_node('localhost', 9701);
-- test 1, very basic test
SET search_path TO public;
SELECT cluster_add_node('localhost', 9700);
SELECT cluster_add_node('localhost', 9701);
SELECT cluster_activate_node('localhost', 9700);
SELECT cluster_activate_node('localhost', 9701);
CREATE TABLE nation_hash(
```
CREATE TABLE pg_dist_colocation
(shardCount INT, workerNodeList [TEXT], replicationFactor INT, colocationId SERIAL,
UNIQUE (shardCount, workerNodeList))
```
ALTER TABLE pg_dist_partition ADD COLUMN colocationId INT;
def create_hash_partitioned_table(tableName, partitionColumn):
#
# Psude code for creating hash partitioned tables.
# Algorithm:
# When a table is first created with a given shard count and
# replication factor, generate a new collocationId.
# If there already exists a collocation configuration with the
# given shard count and replication factor, create placements
# that are collocated with an existing table.
#
-- definition of pg_dist_collocation
CREATE TABLE pg_dist_collocation (
shardCount INT,
replicationFactor INT,
collocationId SERIAL,
UNIQUE (shardCount, replicationFactor))
;
-- generation of a new collocationid with the given inputs
-- create and distribute table
CREATE TABLE main_table (user_id int, username text, timeout bigint, occuruence_time date);
SELECT master_create_distributed_table('main_table', 'user_id', 'hash');
SELECT master_create_worker_shards('main_table', 16, 2);
CREATE TABLE reference_table (user_id int, username text, timeout bigint, occuruence_time date);
SELECT master_create_distributed_table('reference_table', 'user_id', 'hash');
SELECT master_create_worker_shards('reference_table', 16, 2);