github.com/cloudberrydb/gpbackup@v1.0.3-0.20240118031043-5410fd45eed6/ci/scale/sql/scaletestdb_bigschema_ddl.sql (about)

     1  DROP SCHEMA IF EXISTS big CASCADE;
     2  CREATE SCHEMA big;
     3  
     4  -- BIG schema tables, for testing scale of data
     5  CREATE TABLE big.customer
     6  (C_CUSTKEY INT, 
     7  C_NAME VARCHAR(25),
     8  C_ADDRESS VARCHAR(40),
     9  C_NATIONKEY INTEGER,
    10  C_PHONE CHAR(15),
    11  C_ACCTBAL DECIMAL(15,2),
    12  C_MKTSEGMENT CHAR(10),
    13  C_COMMENT VARCHAR(117))
    14  DISTRIBUTED BY (C_CUSTKEY);
    15  
    16  CREATE TABLE big.lineitem
    17  (L_ORDERKEY BIGINT,
    18  L_PARTKEY INT,
    19  L_SUPPKEY INT,
    20  L_LINENUMBER INTEGER,
    21  L_QUANTITY DECIMAL(15,2),
    22  L_EXTENDEDPRICE DECIMAL(15,2),
    23  L_DISCOUNT DECIMAL(15,2),
    24  L_TAX DECIMAL(15,2),
    25  L_RETURNFLAG CHAR(1),
    26  L_LINESTATUS CHAR(1),
    27  L_SHIPDATE DATE,
    28  L_COMMITDATE DATE,
    29  L_RECEIPTDATE DATE,
    30  L_SHIPINSTRUCT CHAR(25),
    31  L_SHIPMODE CHAR(10),
    32  L_COMMENT VARCHAR(44))
    33  DISTRIBUTED BY (L_ORDERKEY,L_LINENUMBER)
    34  PARTITION BY RANGE (L_SHIPDATE)
    35  (start('1992-01-01') INCLUSIVE end ('1998-12-31') INCLUSIVE every (30),
    36  default partition others);
    37  
    38  CREATE TABLE big.nation
    39  (N_NATIONKEY INTEGER, 
    40  N_NAME CHAR(25), 
    41  N_REGIONKEY INTEGER, 
    42  N_COMMENT VARCHAR(152))
    43  DISTRIBUTED BY (N_NATIONKEY);
    44  
    45  CREATE TABLE big.orders
    46  (O_ORDERKEY BIGINT,
    47  O_CUSTKEY INT,
    48  O_ORDERSTATUS CHAR(1),
    49  O_TOTALPRICE DECIMAL(15,2),
    50  O_ORDERDATE DATE,
    51  O_ORDERPRIORITY CHAR(15), 
    52  O_CLERK  CHAR(15), 
    53  O_SHIPPRIORITY INTEGER,
    54  O_COMMENT VARCHAR(79))
    55  DISTRIBUTED BY (O_ORDERKEY)
    56  PARTITION BY RANGE (O_ORDERDATE)
    57  (start('1992-01-01') INCLUSIVE end ('1998-12-31') INCLUSIVE every (30),
    58  default partition others);
    59  
    60  CREATE TABLE big.orders_2
    61  (O_ORDERKEY BIGINT,
    62  O_CUSTKEY INT,
    63  O_ORDERSTATUS CHAR(1),
    64  O_TOTALPRICE DECIMAL(15,2),
    65  O_ORDERDATE DATE,
    66  O_ORDERPRIORITY CHAR(15), 
    67  O_CLERK  CHAR(15), 
    68  O_SHIPPRIORITY INTEGER,
    69  O_COMMENT VARCHAR(79))
    70  DISTRIBUTED BY (O_ORDERKEY)
    71  PARTITION BY RANGE (O_ORDERDATE)
    72  (start('1992-01-01') INCLUSIVE end ('1998-12-31') INCLUSIVE every (20),
    73  default partition others);
    74  
    75  CREATE TABLE big.orders_3
    76  (O_ORDERKEY BIGINT,
    77  O_CUSTKEY INT,
    78  O_ORDERSTATUS CHAR(1),
    79  O_TOTALPRICE DECIMAL(15,2),
    80  O_ORDERDATE DATE,
    81  O_ORDERPRIORITY CHAR(15), 
    82  O_CLERK  CHAR(15), 
    83  O_SHIPPRIORITY INTEGER,
    84  O_COMMENT VARCHAR(79))
    85  WITH (appendonly=true)
    86  DISTRIBUTED BY (O_ORDERKEY)
    87  PARTITION BY RANGE (O_ORDERDATE)
    88  (start('1992-01-01') INCLUSIVE end ('1998-12-31') INCLUSIVE every (15),
    89  default partition others);
    90  
    91  CREATE TABLE big.part
    92  (P_PARTKEY INT,
    93  P_NAME VARCHAR(55),
    94  P_MFGR CHAR(25),
    95  P_BRAND CHAR(10),
    96  P_TYPE VARCHAR(25),
    97  P_SIZE INTEGER,
    98  P_CONTAINER CHAR(10),
    99  P_RETAILPRICE DECIMAL(15,2),
   100  P_COMMENT VARCHAR(23))
   101  DISTRIBUTED BY (P_PARTKEY);
   102  
   103  CREATE TABLE big.partsupp
   104  (PS_PARTKEY INT,
   105  PS_SUPPKEY INT,
   106  PS_AVAILQTY INTEGER,
   107  PS_SUPPLYCOST DECIMAL(15,2),
   108  PS_COMMENT VARCHAR(199))
   109  DISTRIBUTED BY (PS_PARTKEY, PS_SUPPKEY);
   110  
   111  CREATE TABLE big.region
   112  (R_REGIONKEY INTEGER, 
   113  R_NAME CHAR(25),
   114  R_COMMENT VARCHAR(152))
   115  DISTRIBUTED BY (R_REGIONKEY);
   116  
   117  CREATE TABLE big.supplier 
   118  (S_SUPPKEY INT,
   119  S_NAME CHAR(25),
   120  S_ADDRESS VARCHAR(40),
   121  S_NATIONKEY INTEGER,
   122  S_PHONE CHAR(15),
   123  S_ACCTBAL DECIMAL(15,2),
   124  S_COMMENT VARCHAR(101))
   125  DISTRIBUTED BY (S_SUPPKEY);
   126  
   127  CREATE OR REPLACE FUNCTION 
   128      big.cnt_rows(tablename text) 
   129  RETURNS INTEGER AS
   130  $body$
   131  DECLARE
   132    result integer;
   133    query varchar;
   134  BEGIN
   135    query := 'SELECT count(1) FROM big.' || tablename;
   136    execute query into result;
   137    return result;
   138  END;
   139  $body$
   140  LANGUAGE
   141      plpgsql;