{
    // STRING, 22 character unique STRING business id
    "business_id": "tnhfDv5Il8EaGSXZGiuQGg",

    // STRING, the business's name
    "name": "Garaje",

    // STRING, the full address of the business
    "address": "475 3rd St",

    // STRING, the city
    "city": "San Francisco",

    // STRING, 2 character state code, if applicable
    "state": "CA",

    // STRING, the postal code
    "postal code": "94107",

    // FLOAT, latitude
    "latitude": 37.7817529521,

    // FLOAT, longitude
    "longitude": -122.39612197,

    // FLOAT, star rating, rounded to half-stars
    "stars": 4.5,

    // integer, number of reviews
    "review_count": 1198,

    // integer, 0 or 1 for closed or open, respectively
    "is_open": 1,

    // object, business attributes to values. note: some attribute values might be objects
    "attributes": {
        "RestaurantsTakeOut": true,
        "BusinessParking": {
            "garage": false,
            "street": true,
            "validated": false,
            "lot": false,
            "valet": false
        },
    },

    // an array of STRINGs of business categories
    "categories": [
        "Mexican",
        "Burgers",
        "Gastropubs"
    ],

    // an object of key day to value hours, hours are using a 24hr clock
    "hours": {
        "Monday": "10:00-21:00",
        "Tuesday": "10:00-21:00",
        "Friday": "10:00-21:00",
        "Wednesday": "10:00-21:00",
        "Thursday": "10:00-21:00",
        "Sunday": "11:00-18:00",
        "Saturday": "10:00-21:00"
    }

}

CREATE DATABASE yelp;
USE yelp;
CREATE TABLE business_json_parsing (json STRING);
LOAD DATA INPATH "/user/hduser/yelp/business.json" INTO TABLE business_json_parsing;
SELECT json_tuple(json, "business_id", "name", "address", "city", "state", "stars")  from (select * from business_json_parsing) as q;
CREATE TABLE business (business_id string, name string, address string, city string, state string, stars float);
INSERT INTO business SELECT json_tuple(json, "business_id", "name", "address", "city", "state", "stars")  from (select * from business_json_parsing) as q;

SELECT state, AVG(stars) FROM business GROUP BY state;

SELECT state, COUNT(business_id) FROM business GROUP BY state;

SELECT COUNT(DISTINCT(name)) FROM business;

SELECT a.state, a.stars, a.name FROM business a INNER JOIN (SELECT state, MAX(stars) stars FROM business GROUP BY state) b ON a.state = b.state AND a.stars = b.stars;

SELECT name, count(address) locations FROM business GROUP BY name HAVING locations > 1 ORDER BY locations DESC;

SELECT name, COUNT(DISTINCT(state)) state FROM business GROUP BY name HAVING state >1 ORDER BY state;

SELECT name, state, COUNT(address) address FROM business GROUP BY name, state HAVING address > 1 ORDER BY address;