1-- 2-- multi behavioral analytics 3-- this file is intended to create the table requires for the tests 4-- 5SET citus.next_shard_id TO 1400000; 6SET citus.shard_replication_factor = 1; 7SET citus.shard_count = 32; 8CREATE SCHEMA with_basics; 9SET search_path TO 'with_basics'; 10CREATE TABLE users_table (user_id int, time timestamp, value_1 int, value_2 int, value_3 float, value_4 bigint); 11SELECT create_distributed_table('users_table', 'user_id'); 12 create_distributed_table 13-------------------------- 14 15(1 row) 16 17CREATE TABLE events_table (user_id int, time timestamp, event_type int, value_2 int, value_3 float, value_4 bigint); 18SELECT create_distributed_table('events_table', 'user_id'); 19 create_distributed_table 20-------------------------- 21 22(1 row) 23 24\COPY users_table FROM '@abs_srcdir@/data/users_table.data' WITH CSV; 25\COPY events_table FROM '@abs_srcdir@/data/events_table.data' WITH CSV; 26SET citus.shard_count = 96; 27CREATE SCHEMA subquery_and_ctes; 28SET search_path TO subquery_and_ctes; 29CREATE TABLE users_table (user_id int, time timestamp, value_1 int, value_2 int, value_3 float, value_4 bigint); 30SELECT create_distributed_table('users_table', 'user_id'); 31 create_distributed_table 32-------------------------- 33 34(1 row) 35 36CREATE TABLE events_table (user_id int, time timestamp, event_type int, value_2 int, value_3 float, value_4 bigint); 37SELECT create_distributed_table('events_table', 'user_id'); 38 create_distributed_table 39-------------------------- 40 41(1 row) 42 43\COPY users_table FROM '@abs_srcdir@/data/users_table.data' WITH CSV; 44\COPY events_table FROM '@abs_srcdir@/data/events_table.data' WITH CSV; 45SET citus.shard_count TO DEFAULT; 46SET search_path TO DEFAULT; 47CREATE TABLE users_table (user_id int, time timestamp, value_1 int, value_2 int, value_3 float, value_4 bigint); 48SELECT create_distributed_table('users_table', 'user_id'); 49 create_distributed_table 50-------------------------- 51 52(1 row) 53 54CREATE TABLE events_table (user_id int, time timestamp, event_type int, value_2 int, value_3 float, value_4 bigint); 55SELECT create_distributed_table('events_table', 'user_id'); 56 create_distributed_table 57-------------------------- 58 59(1 row) 60 61CREATE TABLE agg_results (user_id int, value_1_agg int, value_2_agg int, value_3_agg float, value_4_agg bigint, agg_time timestamp); 62SELECT create_distributed_table('agg_results', 'user_id'); 63 create_distributed_table 64-------------------------- 65 66(1 row) 67 68-- we need this to improve the concurrency on the regression tests 69CREATE TABLE agg_results_second (user_id int, value_1_agg int, value_2_agg int, value_3_agg float, value_4_agg bigint, agg_time timestamp); 70SELECT create_distributed_table('agg_results_second', 'user_id'); 71 create_distributed_table 72-------------------------- 73 74(1 row) 75 76-- same as agg_results_second 77CREATE TABLE agg_results_third (user_id int, value_1_agg int, value_2_agg int, value_3_agg float, value_4_agg bigint, agg_time timestamp); 78SELECT create_distributed_table('agg_results_third', 'user_id'); 79 create_distributed_table 80-------------------------- 81 82(1 row) 83 84-- same as agg_results_second 85CREATE TABLE agg_results_fourth (user_id int, value_1_agg int, value_2_agg int, value_3_agg float, value_4_agg bigint, agg_time timestamp); 86SELECT create_distributed_table('agg_results_fourth', 'user_id'); 87 create_distributed_table 88-------------------------- 89 90(1 row) 91 92-- same as agg_results_second 93CREATE TABLE agg_results_window (user_id int, value_1_agg int, value_2_agg int, value_3_agg float, value_4_agg bigint, agg_time timestamp); 94SELECT create_distributed_table('agg_results_window', 'user_id'); 95 create_distributed_table 96-------------------------- 97 98(1 row) 99 100CREATE TABLE users_ref_test_table(id int, it_name varchar(25), k_no int); 101SELECT create_reference_table('users_ref_test_table'); 102 create_reference_table 103------------------------ 104 105(1 row) 106 107INSERT INTO users_ref_test_table VALUES(1,'User_1',45); 108INSERT INTO users_ref_test_table VALUES(2,'User_2',46); 109INSERT INTO users_ref_test_table VALUES(3,'User_3',47); 110INSERT INTO users_ref_test_table VALUES(4,'User_4',48); 111INSERT INTO users_ref_test_table VALUES(5,'User_5',49); 112INSERT INTO users_ref_test_table VALUES(6,'User_6',50); 113\COPY users_table FROM '@abs_srcdir@/data/users_table.data' WITH CSV; 114\COPY events_table FROM '@abs_srcdir@/data/events_table.data' WITH CSV; 115-- create indexes for 116CREATE INDEX is_index1 ON users_table(user_id); 117CREATE INDEX is_index2 ON events_table(user_id); 118CREATE INDEX is_index3 ON users_table(value_1); 119CREATE INDEX is_index4 ON events_table(event_type); 120CREATE INDEX is_index5 ON users_table(value_2); 121CREATE INDEX is_index6 ON events_table(value_2); 122-- Create composite type to use in subquery pushdown 123CREATE TYPE user_composite_type AS 124( 125 tenant_id BIGINT, 126 user_id BIGINT 127); 128-- ... create a test HASH function. Though it is a poor hash function, 129-- it is acceptable for our tests 130SELECT run_command_on_master_and_workers($f$ 131 132 CREATE FUNCTION test_composite_type_hash(user_composite_type) RETURNS int 133 AS 'SELECT hashtext( ($1.tenant_id + $1.tenant_id)::text);' 134 LANGUAGE SQL 135 IMMUTABLE 136 RETURNS NULL ON NULL INPUT; 137$f$); 138 run_command_on_master_and_workers 139----------------------------------- 140 141(1 row) 142 143SET citus.next_shard_id TO 1400297; 144CREATE TABLE events_reference_table (like events_table including all); 145SELECT create_reference_table('events_reference_table'); 146 create_reference_table 147------------------------ 148 149(1 row) 150 151CREATE INDEX events_ref_val2 on events_reference_table(value_2); 152INSERT INTO events_reference_table SELECT * FROM events_table; 153CREATE TABLE users_reference_table (like users_table including all); 154SELECT create_reference_table('users_reference_table'); 155 create_reference_table 156------------------------ 157 158(1 row) 159 160INSERT INTO users_reference_table SELECT * FROM users_table; 161