9zxtkjW4

1# Command
2DROP TABLE IF EXISTS edw_fact_klook_inventory_20180330 CASCADE;
3CREATE TEMPORARY TABLE edw_fact_klook_inventory_20180330 ( LIKE edw.fact_klook_inventory );
4COPY edw_fact_klook_inventory_20180330 FROM '/data/dataset/final/day_1/edw.fact_klook_inventory/csv/2018-03-30/part-00000-21dacc2b-bc81-412c-a8ea-d45d2109d9e0.csv' DELIMITER '|' CSV HEADER;
5DELETE FROM edw.fact_klook_inventory WHERE CONCAT(ARRANGEMENT_ID, PACKAGE_ID, INVENTORY_ID, ID, ACT_ID, SUB_ID, DATA_ID) IN (SELECT DISTINCT(CONCAT(ARRANGEMENT_ID, PACKAGE_ID, INVENTORY_ID, ID, ACT_ID, SUB_ID, DATA_ID)) FROM edw_fact_klook_inventory_20180330 WHERE edw.fact_klook_inventory.SCRAPED_DATE <= edw_fact_klook_inventory_20180330.SCRAPED_DATE );
6INSERT INTO edw.fact_klook_inventory SELECT * FROM edw_fact_klook_inventory_20180330 WHERE CONCAT(ARRANGEMENT_ID, PACKAGE_ID, INVENTORY_ID, ID, ACT_ID, SUB_ID, DATA_ID) NOT IN (SELECT CONCAT(ARRANGEMENT_ID, PACKAGE_ID, INVENTORY_ID, ID, ACT_ID, SUB_ID, DATA_ID) FROM edw.fact_klook_inventory);
7DROP TABLE IF EXISTS edw_fact_klook_inventory_20180330 CASCADE;
8
9# Error
10psycopg2.DataError: invalid input syntax for type date: "Hong Kong Helicopter Tours"
11
12# Table DDL
13--
14-- Type: TABLE ; Name: fact_klook_inventory; Owner: root
15--
16
17CREATE TABLE edw.fact_klook_inventory (
18    arrangement_id integer,
19    credits double precision,
20    id integer NOT NULL,
21    inventory_id integer,
22    market_price integer,
23    max_pax integer,
24    min_pax integer,
25    name character varying(150),
26    price integer,
27    required boolean,
28    date timestamp without time zone,
29    package_id integer,
30    stock integer,
31    act_id integer,
32    selling_price integer,
33    sub_id integer,
34    city_id integer,
35    city_name character varying(20),
36    data_id integer,
37    link character varying(200),
38    popularity character varying(20),
39    rating double precision,
40    ticket_name character varying(200),
41    scraped_date date
42);
43
44
45ALTER TABLE edw.fact_klook_inventory OWNER TO root;
46
47
48# Related Row in CSV
49https://docs.google.com/spreadsheets/d/1yjNLD-M7PA3kGAMSM8GNBTBQRYIMnZEaU3hlLN8E-Ss/edit?usp=sharing