create_tables.sql

CREATE TABLE Calendar (
    listing_id varchar(60) PRIMARY KEY, 
    date Date,
    available Boolean,
    price VarChar,
    adjusted_price Varchar,
    minimum_nights NUMERIC,
    maximum_nights NUMERIC 
    );

create or replace TABLE AIRBNB.MAIN.HOSTS (
    LISTING_ID VARCHAR(60) NOT NULL, 
    host_id VARCHAR(60) NOT NULL, 
    host_url VARCHAR,
    host_name String,
    host_since Date, 
    host_location String,
    host_about string,
    host_response_time string,
    host_response_rate varchar,
    host_acceptance_rate varchar,
    host_is_superhost boolean,
    host_thumbnail_url varchar,
    host_picture_url varchar,
    host_neighbourhood string,
    host_listings_count number,
    host_total_listings_count number,
    host_verifications variant,
    host_has_profile_pic boolean,
    host_identity_verified boolean,
    calculated_host_listings_count number,
    calculated_host_listings_count_entire_homes number,
    calculated_host_listings_count_private_rooms number,
    calculated_host_listings_count_shared_rooms number,
    primary key (host_ID),
    foreign key (LISTING_ID) references AIRBNB.MAIN.LISTINGS(LISTING_ID)
    );

create or replace TABLE AIRBNB.MAIN.LISTINGS (
    LISTING_ID VARCHAR(60) NOT NULL, 
    listing_url String, 
    scrape_id Number, 
    last_scraped Date, 
    name String, 
    description String,
    neighborhood_overview String,
    picture_url String, 
    host_id VARCHAR(60), 
    neighbourhood String,
    neighbourhood_cleansed String,
    neighbourhood_group_cleansed String,
    latitude Float,
    longitude Float,
    property_type String, 
    room_type String, 
    accommodates number, 
    bathrooms number,
    bathrooms_text varchar, 
    bedrooms number, 
    beds number, 
    amenities variant, 
    price varchar,
    minimum_nights number, 
    maximum_nights number, 
    minimum_minimum_nights number,
    maximum_minimum_nights number, 
    minimum_maximum_nights number,
    maximum_maximum_nights number, 
    minimum_nights_avg_ntm number,
    maximum_nights_avg_ntm number, 
    calendar_updated varchar, 
    has_availability boolean,
    availability_30 number, 
    availability_60 number, 
    availability_90 number,
    availability_365 number, 
    calendar_last_scraped date, 
    number_of_reviews number,
    number_of_reviews_ltm number, 
    number_of_reviews_l30d number, 
    first_review date,
    last_review date, 
    review_scores_rating number, 
    review_scores_accuracy number,
    review_scores_cleanliness number, 
    review_scores_checkin number,
    review_scores_communication number, 
    review_scores_location number,
    review_scores_value number, 
    license varchar, 
    instant_bookable boolean, 
    reviews_per_month number,
    primary key (LISTING_ID)
    );

create or replace TABLE AIRBNB.MAIN.REVIEWS (
    LISTING_ID VARCHAR(60),
    ID VARCHAR(60) NOT NULL,
    DATE DATE,
    REVIEWER_ID VARCHAR(60) NOT NULL,
    REVIEWER_NAME VARCHAR(120),
    COMMENTS VARCHAR(16777216),
    primary key (ID),
    foreign key (LISTING_ID) references AIRBNB.MAIN.CALENDAR(LISTING_ID)
    );