import psycopg2 as pg import psycopg2.extras import getpass import os import sys from datetime import datetime from time import sleep if len (sys.argv) != 5 : print ("Usage: python_dbconnect user password testdb_name clusterhostname") sys.exit (1) rds_user=sys.argv[1] rds_pass=sys.argv[2] test_db= sys.argv[3] host_name=sys.argv[4] create_schema=''' create schema if not exists jtest; ''' #print (create_schema) create_function=''' CREATE Or Replace FUNCTION jtest.gen_col_get_dt(p_event jsonb) RETURNS date AS $$ SELECT to_date( jsonb_path_query( p_event, '$.date[0]')::text, '\"YYYY/MM/DD\"'); $$ LANGUAGE sql IMMUTABLE; ''' #print (create_function) create_table=''' drop table if exists jtest.TEST_TABLE_PERF; CREATE TABLE jtest.TEST_TABLE_PERF ( COL_DT_BI_ID bigserial NOT NULL, COL_DT_TMP TIMESTAMP NOT NULL, COL_DT_BI_ID2 BIGINT NOT NULL, COL_DT_TMP_LAST_DML TIMESTAMP, COL_DT_BL boolean, COL_DT_NMR NUMERIC (4,2), COL_DT_VCHR4000 VARCHAR(4000), COL_DT_VCHR80_LAST_UPDATER VARCHAR(80) DEFAULT current_user, COL_DT_VCHR01 VARCHAR(40), COL_DT_VCHR02 VARCHAR(40), COL_DT_VCHR03 VARCHAR(40), COL_DT_VCHR04 VARCHAR(40), COL_DT_VCHR05 VARCHAR(40), COL_DT_VCHR06 VARCHAR(40), COL_DT_VCHR07 VARCHAR(40), COL_DT_VCHR08 VARCHAR(40), COL_DT_VCHR09 VARCHAR(40), COL_DT_VCHR10 VARCHAR(40), COL_DT_VCHR11 VARCHAR(40), COL_DT_VCHR12 VARCHAR(40), COL_DT_VCHR13 VARCHAR(40), COL_DT_VCHR14 VARCHAR(40), COL_DT_VCHR15 VARCHAR(40), COL_DT_VCHR16 VARCHAR(40), COL_DT_VCHR17 VARCHAR(40), COL_DT_VCHR18 VARCHAR(40), COL_DT_VCHR19 VARCHAR(40), COL_DT_VCHR20 VARCHAR(40), COL_DT_VCHR21 VARCHAR(40), COL_DT_VCHR22 VARCHAR(40), COL_DT_VCHR23 VARCHAR(40), COL_DT_VCHR24 VARCHAR(40), COL_DT_VCHR25 VARCHAR(40), COL_DT_BGINT01 BIGINT, COL_DT_BGINT02 BIGINT, COL_DT_BGINT03 BIGINT, COL_DT_BGINT04 BIGINT, COL_DT_BGINT05 BIGINT, COL_DT_BGINT06 BIGINT, COL_DT_BGINT07 BIGINT, COL_DT_BGINT08 BIGINT, COL_DT_BGINT09 BIGINT, COL_DT_BGINT10 BIGINT, COL_DT_BGINT11 BIGINT, COL_DT_BGINT12 BIGINT, COL_DT_BGINT13 BIGINT, COL_DT_BGINT14 BIGINT, COL_DT_BGINT15 BIGINT, COL_DT_BGINT16 BIGINT, COL_DT_BGINT17 BIGINT, COL_DT_BGINT18 BIGINT, COL_DT_BGINT19 BIGINT, COL_DT_BGINT20 BIGINT, COL_DT_BGINT21 BIGINT, COL_DT_BGINT22 BIGINT, COL_DT_BGINT23 BIGINT, COL_DT_BGINT24 BIGINT, COL_DT_BGINT25 BIGINT, COL_DT_BOOL01 boolean, COL_DT_BOOL02 boolean, COL_DT_BOOL03 boolean, COL_DT_BOOL04 boolean, COL_DT_BOOL05 boolean, COL_DT_BOOL06 boolean, COL_DT_BOOL07 boolean, COL_DT_BOOL08 boolean, COL_DT_BOOL09 boolean, COL_DT_BOOL10 boolean, COL_DT_BOOL11 boolean, COL_DT_BOOL12 boolean, COL_DT_BOOL13 boolean, COL_DT_BOOL14 boolean, COL_DT_BOOL15 boolean, COL_DT_BOOL16 boolean, COL_DT_BOOL17 boolean, COL_DT_BOOL18 boolean, COL_DT_BOOL19 boolean, COL_DT_BOOL20 boolean, COL_DT_BOOL21 boolean, COL_DT_BOOL22 boolean, COL_DT_BOOL23 boolean, COL_DT_BOOL24 boolean, COL_DT_BOOL25 boolean, COL_DT_BOOL26 boolean, COL_DT_TMSTMP01 TIMESTAMP, COL_DT_TMSTMP02 TIMESTAMP, COL_DT_TMSTMP03 TIMESTAMP, COL_DT_TMSTMP04 TIMESTAMP, COL_DT_TMSTMP05 TIMESTAMP, COL_DT_TMSTMP06 TIMESTAMP, COL_DT_TMSTMP07 TIMESTAMP, COL_DT_TMSTMP08 TIMESTAMP, COL_DT_TMSTMP09 TIMESTAMP, COL_DT_TMSTMP10 TIMESTAMP, COL_DT_TMSTMP11 TIMESTAMP, COL_DT_TMSTMP12 TIMESTAMP, COL_DT_TMSTMP13 TIMESTAMP, COL_DT_TMSTMP14 TIMESTAMP, COL_DT_TMSTMP15 TIMESTAMP, COL_DT_TMSTMP16 TIMESTAMP, COL_DT_TMSTMP17 TIMESTAMP, COL_DT_TMSTMP18 TIMESTAMP, COL_DT_TMSTMP19 TIMESTAMP, COL_DT_TMSTMP20 TIMESTAMP, COL_DT_TMSTMP21 TIMESTAMP, COL_DT_TMSTMP22 TIMESTAMP, COL_DT_TMSTMP23 TIMESTAMP, COL_DT_TMSTMP24 TIMESTAMP, COL_DT_TMSTMP25 TIMESTAMP, COL_DT_JSONB JSONB, COL_DT_GEN_COL date GENERATED ALWAYS AS (jtest.gen_col_get_dt(COL_DT_JSONB)) STORED, COL_DT_BYTEA BYTEA, CONSTRAINT PK_TEST_TABLE_PERFPRIMARY PRIMARY KEY (COL_DT_BI_ID) ) PARTITION BY HASH(COL_DT_BI_ID); ''' create_index=''' --drop index jtest.IDX_TEST_TABLE_PARTITIONED_TMP; CREATE INDEX IF NOT EXISTS IDX_TEST_TABLE_PARTITIONED_TMP ON jtest.TEST_TABLE_PERF (COL_DT_BI_ID2, COL_DT_TMP) ; ''' create_partitions=''' -- partitions 16 drop table if exists jtest.TEST_TABLE_PARTITIONED_P00; drop table if exists jtest.TEST_TABLE_PARTITIONED_P01; drop table if exists jtest.TEST_TABLE_PARTITIONED_P02; drop table if exists jtest.TEST_TABLE_PARTITIONED_P03; drop table if exists jtest.TEST_TABLE_PARTITIONED_P04; drop table if exists jtest.TEST_TABLE_PARTITIONED_P05; drop table if exists jtest.TEST_TABLE_PARTITIONED_P06; drop table if exists jtest.TEST_TABLE_PARTITIONED_P07; drop table if exists jtest.TEST_TABLE_PARTITIONED_P08; drop table if exists jtest.TEST_TABLE_PARTITIONED_P09; drop table if exists jtest.TEST_TABLE_PARTITIONED_P10; drop table if exists jtest.TEST_TABLE_PARTITIONED_P11; drop table if exists jtest.TEST_TABLE_PARTITIONED_P12; drop table if exists jtest.TEST_TABLE_PARTITIONED_P13; drop table if exists jtest.TEST_TABLE_PARTITIONED_P14; drop table if exists jtest.TEST_TABLE_PARTITIONED_P15; CREATE TABLE jtest.TEST_TABLE_PARTITIONED_P00 PARTITION OF jtest.TEST_TABLE_PERF FOR VALUES WITH (MODULUS 16, REMAINDER 0) ; CREATE TABLE jtest.TEST_TABLE_PARTITIONED_P01 PARTITION OF jtest.TEST_TABLE_PERF FOR VALUES WITH (MODULUS 16, REMAINDER 1) ; CREATE TABLE jtest.TEST_TABLE_PARTITIONED_P02 PARTITION OF jtest.TEST_TABLE_PERF FOR VALUES WITH (MODULUS 16, REMAINDER 2) ; CREATE TABLE jtest.TEST_TABLE_PARTITIONED_P03 PARTITION OF jtest.TEST_TABLE_PERF FOR VALUES WITH (MODULUS 16, REMAINDER 3) ; CREATE TABLE jtest.TEST_TABLE_PARTITIONED_P04 PARTITION OF jtest.TEST_TABLE_PERF FOR VALUES WITH (MODULUS 16, REMAINDER 4) ; CREATE TABLE jtest.TEST_TABLE_PARTITIONED_P05 PARTITION OF jtest.TEST_TABLE_PERF FOR VALUES WITH (MODULUS 16, REMAINDER 5) ; CREATE TABLE jtest.TEST_TABLE_PARTITIONED_P06 PARTITION OF jtest.TEST_TABLE_PERF FOR VALUES WITH (MODULUS 16, REMAINDER 6) ; CREATE TABLE jtest.TEST_TABLE_PARTITIONED_P07 PARTITION OF jtest.TEST_TABLE_PERF FOR VALUES WITH (MODULUS 16, REMAINDER 7) ; CREATE TABLE jtest.TEST_TABLE_PARTITIONED_P08 PARTITION OF jtest.TEST_TABLE_PERF FOR VALUES WITH (MODULUS 16, REMAINDER 8) ; CREATE TABLE jtest.TEST_TABLE_PARTITIONED_P09 PARTITION OF jtest.TEST_TABLE_PERF FOR VALUES WITH (MODULUS 16, REMAINDER 9) ; CREATE TABLE jtest.TEST_TABLE_PARTITIONED_P10 PARTITION OF jtest.TEST_TABLE_PERF FOR VALUES WITH (MODULUS 16, REMAINDER 10) ; CREATE TABLE jtest.TEST_TABLE_PARTITIONED_P11 PARTITION OF jtest.TEST_TABLE_PERF FOR VALUES WITH (MODULUS 16, REMAINDER 11) ; CREATE TABLE jtest.TEST_TABLE_PARTITIONED_P12 PARTITION OF jtest.TEST_TABLE_PERF FOR VALUES WITH (MODULUS 16, REMAINDER 12) ; CREATE TABLE jtest.TEST_TABLE_PARTITIONED_P13 PARTITION OF jtest.TEST_TABLE_PERF FOR VALUES WITH (MODULUS 16, REMAINDER 13) ; CREATE TABLE jtest.TEST_TABLE_PARTITIONED_P14 PARTITION OF jtest.TEST_TABLE_PERF FOR VALUES WITH (MODULUS 16, REMAINDER 14) ; CREATE TABLE jtest.TEST_TABLE_PARTITIONED_P15 PARTITION OF jtest.TEST_TABLE_PERF FOR VALUES WITH (MODULUS 16, REMAINDER 15) ; ''' if __name__ == "__main__": ##### Connect to the database, and create testdata. connection = psycopg2.connect(host=host_name , user=rds_user , password=rds_pass , database=test_db , port=5432 ) cursor =connection.cursor() connection.autocommit=True cursor.execute(create_schema) print ("Schema Created") cursor.execute(create_function) print ("function created") cursor.execute(create_table) print ("table created") cursor.execute(create_index) print ("Index created") cursor.execute(create_partitions) print ("Table Partitions created") connection.close()