github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/workload/tpch/import-sf1.sql (about)

     1  --
     2  -- This script imports TPCH data for scale factor 1 (SF-1, which is ~1GB) into
     3  -- Cockroach DB. Data is imported from the CSV files generated by the TPCH dbgen
     4  -- tool and uploaded to Google storage. Foreign key constraints are added
     5  -- separately because the IMPORT statement does not support their direct
     6  -- declaration in the import schema.
     7  --
     8  -- For faster import, the TPCH database can be restored from backup:
     9  --
    10  --   RESTORE DATABASE tpch FROM 'gs://cockroach-fixtures/workload/tpch/scalefactor=1/backup';
    11  --
    12  -- When re-generating all data (say after a schema change), a fresh backup can
    13  -- be created via:
    14  --
    15  --   BACKUP DATABASE tpch TO 'gs://cockroach-fixtures/workload/tpch/scalefactor=1/backup';
    16  --
    17  
    18  IMPORT TABLE region CREATE USING 'gs://cockroach-fixtures/tpch-csv/schema/region.sql' CSV DATA(
    19    'gs://cockroach-fixtures/tpch-csv/sf-1/region.tbl'
    20  ) WITH delimiter='|';
    21  
    22  IMPORT TABLE nation CREATE USING 'gs://cockroach-fixtures/tpch-csv/schema/nation.sql' CSV DATA(
    23    'gs://cockroach-fixtures/tpch-csv/sf-1/nation.tbl'
    24  ) WITH delimiter='|';
    25  
    26  ALTER TABLE nation ADD CONSTRAINT nation_fkey_region FOREIGN KEY (n_regionkey) references region (r_regionkey);
    27  
    28  IMPORT TABLE part CREATE USING 'gs://cockroach-fixtures/tpch-csv/schema/part.sql' CSV DATA(
    29    'gs://cockroach-fixtures/tpch-csv/sf-1/part.tbl.1',
    30    'gs://cockroach-fixtures/tpch-csv/sf-1/part.tbl.2',
    31    'gs://cockroach-fixtures/tpch-csv/sf-1/part.tbl.3',
    32    'gs://cockroach-fixtures/tpch-csv/sf-1/part.tbl.4',
    33    'gs://cockroach-fixtures/tpch-csv/sf-1/part.tbl.5',
    34    'gs://cockroach-fixtures/tpch-csv/sf-1/part.tbl.6',
    35    'gs://cockroach-fixtures/tpch-csv/sf-1/part.tbl.7',
    36    'gs://cockroach-fixtures/tpch-csv/sf-1/part.tbl.8'
    37  ) WITH delimiter='|';
    38  
    39  IMPORT TABLE supplier CREATE USING 'gs://cockroach-fixtures/tpch-csv/schema/supplier.sql' CSV DATA(
    40    'gs://cockroach-fixtures/tpch-csv/sf-1/supplier.tbl.1',
    41    'gs://cockroach-fixtures/tpch-csv/sf-1/supplier.tbl.2',
    42    'gs://cockroach-fixtures/tpch-csv/sf-1/supplier.tbl.3',
    43    'gs://cockroach-fixtures/tpch-csv/sf-1/supplier.tbl.4',
    44    'gs://cockroach-fixtures/tpch-csv/sf-1/supplier.tbl.5',
    45    'gs://cockroach-fixtures/tpch-csv/sf-1/supplier.tbl.6',
    46    'gs://cockroach-fixtures/tpch-csv/sf-1/supplier.tbl.7',
    47    'gs://cockroach-fixtures/tpch-csv/sf-1/supplier.tbl.8'
    48  ) WITH delimiter='|';
    49  
    50  ALTER TABLE supplier ADD CONSTRAINT supplier_fkey_nation FOREIGN KEY (s_nationkey) references nation (n_nationkey);
    51  
    52  IMPORT TABLE partsupp CREATE USING 'gs://cockroach-fixtures/tpch-csv/schema/partsupp.sql' CSV DATA(
    53    'gs://cockroach-fixtures/tpch-csv/sf-1/partsupp.tbl.1',
    54    'gs://cockroach-fixtures/tpch-csv/sf-1/partsupp.tbl.2',
    55    'gs://cockroach-fixtures/tpch-csv/sf-1/partsupp.tbl.3',
    56    'gs://cockroach-fixtures/tpch-csv/sf-1/partsupp.tbl.4',
    57    'gs://cockroach-fixtures/tpch-csv/sf-1/partsupp.tbl.5',
    58    'gs://cockroach-fixtures/tpch-csv/sf-1/partsupp.tbl.6',
    59    'gs://cockroach-fixtures/tpch-csv/sf-1/partsupp.tbl.7',
    60    'gs://cockroach-fixtures/tpch-csv/sf-1/partsupp.tbl.8'
    61  ) WITH delimiter='|';
    62  
    63  ALTER TABLE partsupp ADD CONSTRAINT partsupp_fkey_part FOREIGN KEY (ps_partkey) references part (p_partkey);
    64  ALTER TABLE partsupp ADD CONSTRAINT partsupp_fkey_supplier FOREIGN KEY (ps_suppkey) references supplier (s_suppkey);
    65  
    66  IMPORT TABLE customer CREATE USING 'gs://cockroach-fixtures/tpch-csv/schema/customer.sql' CSV DATA(
    67    'gs://cockroach-fixtures/tpch-csv/sf-1/customer.tbl.1',
    68    'gs://cockroach-fixtures/tpch-csv/sf-1/customer.tbl.2',
    69    'gs://cockroach-fixtures/tpch-csv/sf-1/customer.tbl.3',
    70    'gs://cockroach-fixtures/tpch-csv/sf-1/customer.tbl.4',
    71    'gs://cockroach-fixtures/tpch-csv/sf-1/customer.tbl.5',
    72    'gs://cockroach-fixtures/tpch-csv/sf-1/customer.tbl.6',
    73    'gs://cockroach-fixtures/tpch-csv/sf-1/customer.tbl.7',
    74    'gs://cockroach-fixtures/tpch-csv/sf-1/customer.tbl.8'
    75  ) WITH delimiter='|';
    76  
    77  ALTER TABLE customer ADD CONSTRAINT customer_fkey_nation FOREIGN KEY (c_nationkey) references nation (n_nationkey);
    78  
    79  IMPORT TABLE orders CREATE USING 'gs://cockroach-fixtures/tpch-csv/schema/orders.sql' CSV DATA(
    80    'gs://cockroach-fixtures/tpch-csv/sf-1/orders.tbl.1',
    81    'gs://cockroach-fixtures/tpch-csv/sf-1/orders.tbl.2',
    82    'gs://cockroach-fixtures/tpch-csv/sf-1/orders.tbl.3',
    83    'gs://cockroach-fixtures/tpch-csv/sf-1/orders.tbl.4',
    84    'gs://cockroach-fixtures/tpch-csv/sf-1/orders.tbl.5',
    85    'gs://cockroach-fixtures/tpch-csv/sf-1/orders.tbl.6',
    86    'gs://cockroach-fixtures/tpch-csv/sf-1/orders.tbl.7',
    87    'gs://cockroach-fixtures/tpch-csv/sf-1/orders.tbl.8'
    88  ) WITH delimiter='|';
    89  
    90  ALTER TABLE orders ADD CONSTRAINT orders_fkey_customer FOREIGN KEY (o_custkey) references customer (c_custkey);
    91  
    92  IMPORT TABLE lineitem CREATE USING 'gs://cockroach-fixtures/tpch-csv/schema/lineitem.sql' CSV DATA(
    93    'gs://cockroach-fixtures/tpch-csv/sf-1/lineitem.tbl.1',
    94    'gs://cockroach-fixtures/tpch-csv/sf-1/lineitem.tbl.2',
    95    'gs://cockroach-fixtures/tpch-csv/sf-1/lineitem.tbl.3',
    96    'gs://cockroach-fixtures/tpch-csv/sf-1/lineitem.tbl.4',
    97    'gs://cockroach-fixtures/tpch-csv/sf-1/lineitem.tbl.5',
    98    'gs://cockroach-fixtures/tpch-csv/sf-1/lineitem.tbl.6',
    99    'gs://cockroach-fixtures/tpch-csv/sf-1/lineitem.tbl.7',
   100    'gs://cockroach-fixtures/tpch-csv/sf-1/lineitem.tbl.8'
   101  ) WITH delimiter='|';
   102  
   103  ALTER TABLE lineitem ADD CONSTRAINT lineitem_fkey_orders FOREIGN KEY (l_orderkey) references orders (o_orderkey);
   104  ALTER TABLE lineitem ADD CONSTRAINT lineitem_fkey_part FOREIGN KEY (l_partkey) references part (p_partkey);
   105  ALTER TABLE lineitem ADD CONSTRAINT lineitem_fkey_supplier FOREIGN KEY (l_suppkey) references supplier (s_suppkey);
   106  
   107  -- TODO(andyk): This fails with `pq: column "l_partkey" cannot be used by multiple foreign key constraints`.
   108  -- This limitation would appear to violate TPCH rules, as all foreign keys must be defined, or none at all.
   109  -- ALTER TABLE lineitem ADD CONSTRAINT lineitem_fkey_partsupp FOREIGN KEY (l_partkey, l_suppkey) references partsupp (ps_partkey, ps_suppkey);