--
-- partsupp
--
\! ./s3bin/s3 delete  @obsbucket@/obscheck/tpch_partsupp_out/ address=@obshostname@ ak=@ak@ sk=@sk@
DROP FOREIGN TABLE if exists EXTPARTSUPP;
CREATE FOREIGN TABLE EXTPARTSUPP (
  PS_PARTKEY    INTEGER NOT NULL, -- references P_PARTKEY
  PS_SUPPKEY    INTEGER NOT NULL, -- references S_SUPPKEY
  PS_AVAILQTY   INTEGER,
  PS_SUPPLYCOST DECIMAL,
  PS_COMMENT    VARCHAR(199)
)SERVER gsmpp_server OPTIONS (
    location 'gsobs://@obshostname@/@obsbucket@/obscheck/tpch_partsupp/partsupp',
    format 'text',
    DELIMITER '|',
    encoding 'utf8',
    chunksize '32',
    encrypt 'off',
    access_key '@ak@',
    secret_access_key '@sk@'
);

DROP TABLE if exists PARTSUPP;
CREATE TABLE PARTSUPP (
  PS_PARTKEY    INTEGER NOT NULL, -- references P_PARTKEY
  PS_SUPPKEY    INTEGER NOT NULL, -- references S_SUPPKEY
  PS_AVAILQTY   INTEGER,
  PS_SUPPLYCOST DECIMAL,
  PS_COMMENT    VARCHAR(199)
);
insert into PARTSUPP select * from EXTPARTSUPP;

DROP FOREIGN TABLE if exists EXTPARTSUPP_WRITE;
CREATE FOREIGN TABLE EXTPARTSUPP_WRITE (
  PS_PARTKEY    INTEGER NOT NULL, -- references P_PARTKEY
  PS_SUPPKEY    INTEGER NOT NULL, -- references S_SUPPKEY
  PS_AVAILQTY   INTEGER,
  PS_SUPPLYCOST DECIMAL,
  PS_COMMENT    VARCHAR(199)
)SERVER gsmpp_server OPTIONS (
    location 'gsobs://@obshostname@/@obsbucket@/obscheck/tpch_partsupp_out/',
    format 'text',
    DELIMITER '|',
    encoding 'utf8',
    encrypt 'off',
    access_key '@ak@',
    secret_access_key '@sk@'
)
Write Only;

insert into EXTPARTSUPP_WRITE select * from PARTSUPP;

drop foreign table if exists EXTPARTSUPP_READ;
CREATE FOREIGN TABLE EXTPARTSUPP_READ
(
  PS_PARTKEY    INTEGER NOT NULL, -- references P_PARTKEY
  PS_SUPPKEY    INTEGER NOT NULL, -- references S_SUPPKEY
  PS_AVAILQTY   INTEGER,
  PS_SUPPLYCOST DECIMAL,
  PS_COMMENT    VARCHAR(199)
)SERVER gsmpp_server OPTIONS (
    location 'gsobs://@obshostname@/@obsbucket@/obscheck/tpch_partsupp_out/',
    format 'text',
    DELIMITER '|',
    encoding 'utf8',
    chunksize '32',
    encrypt 'off',
    access_key '@ak@',
    secret_access_key '@sk@'
);


select count(*) from EXTPARTSUPP_READ;

drop table if exists PARTSUPP_READ;
CREATE TABLE PARTSUPP_READ
(
  PS_PARTKEY    INTEGER NOT NULL, -- references P_PARTKEY
  PS_SUPPKEY    INTEGER NOT NULL, -- references S_SUPPKEY
  PS_AVAILQTY   INTEGER,
  PS_SUPPLYCOST DECIMAL,
  PS_COMMENT    VARCHAR(199)
);

insert into PARTSUPP_READ select * from EXTPARTSUPP_READ;
select count(*) from (select * from PARTSUPP minus all select * from PARTSUPP_READ);

drop foreign table EXTPARTSUPP_READ;
drop foreign table EXTPARTSUPP_WRITE;
drop table PARTSUPP_READ;
drop table PARTSUPP;
drop foreign table EXTPARTSUPP;
\! ./s3bin/s3 delete  @obsbucket@/obscheck/tpch_partsupp_out/ address=@obshostname@ ak=@ak@ sk=@sk@