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