-- Licensed to the Apache Software Foundation (ASF) under one or more
-- contributor license agreements.  See the NOTICE file distributed with
-- this work for additional information regarding copyright ownership.
-- The ASF licenses this file to You under the Apache License, Version 2.0
-- (the "License") you may not use this file except in compliance with
-- the License.  You may obtain a copy of the License at
--
--     http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
DROP TABLE users

DROP TABLE states

DROP TABLE countries

DROP TABLE users_level


-- From HIVE-1257

CREATE TABLE users(key string, state string, country string, country_id int)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES (
"hbase.columns.mapping" = "info:state,info:country,info:country_id"
)


CREATE TABLE states(key string, name string)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES (
"hbase.columns.mapping" = "state:name"
)


CREATE TABLE countries(key string, name string, country string, country_id int)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES (
"hbase.columns.mapping" = "info:name,info:country,info:country_id"
)


INSERT OVERWRITE TABLE users SELECT 'user1', 'IA', 'USA', 0
FROM src WHERE key=100


INSERT OVERWRITE TABLE states SELECT 'IA', 'Iowa'
FROM src WHERE key=100


INSERT OVERWRITE TABLE countries SELECT 'USA', 'United States', 'USA', 1
FROM src WHERE key=100
set hive.input.format = org.apache.hadoop.hive.ql.io.HiveInputFormat


SELECT u.key, u.country, c.name, c.key FROM users u JOIN countries c 
ON (u.country = c.key)
user1	USA	United States	USA


SELECT u.key, u.country, c.name, c.key FROM users u JOIN countries c
ON (u.country = c.country)
user1	USA	United States	USA


SELECT u.key, u.country, c.name, c.key FROM users u JOIN countries c 
ON (u.country_id = c.country_id)


SELECT u.key, u.state, s.name FROM users u JOIN states s 
ON (u.state = s.key)
user1	IA	Iowa
set hive.input.format = org.apache.hadoop.hive.ql.io.CombineHiveInputFormat


SELECT u.key, u.country, c.name, c.key FROM users u JOIN countries c 
ON (u.country = c.key)
user1	USA	United States	USA


SELECT u.key, u.country, c.name, c.key FROM users u JOIN countries c
ON (u.country = c.country)
user1	USA	United States	USA


SELECT u.key, u.country, c.name, c.key FROM users u JOIN countries c 
ON (u.country_id = c.country_id)


SELECT u.key, u.state, s.name FROM users u JOIN states s 
ON (u.state = s.key)
user1	IA	Iowa


DROP TABLE users

DROP TABLE states

DROP TABLE countries


CREATE TABLE users(key int, userid int, username string, created int) 
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,f:userid,f:nickname,f:created")


CREATE TABLE users_level(key int, userid int, level int)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,f:userid,f:level")


-- HIVE-1903:  the problem fixed here showed up even without any data,
-- so no need to load any to test it
SELECT year(from_unixtime(users.created)) AS year, level, count(users.userid) AS num 
 FROM users JOIN users_level ON (users.userid = users_level.userid) 
 GROUP BY year(from_unixtime(users.created)), level


DROP TABLE users

DROP TABLE users_level