-
Notifications
You must be signed in to change notification settings - Fork 561
Notes on Hive and S3
mmalohlava edited this page Mar 29, 2013
·
10 revisions
There are two posibilities of using Hive:
- local installation
- use map-reduce functionality in AWS
- AWS console -> map-reduce -> start new interactive hive job
- connect to master server (master is tagged by MASTER role tag - see EC2 management console)
Hive supports common SQL syntax: creata tables, show tables, describe <table>, ...
To start HQL script simply type: hive < script.sql
- Create local iris table
create table iris (sepal_len float, sepal_width float, petal_len float, petal_width float, response STRING) row format delimited fields terminated by ',';- Load data from local file
iris.data
load data local inpath 'iris.data' into table iris;- Create a table in S3 bucket
create external table s3iristable (sepal_len float, sepal_width float, petal_len float, petal_width float, response STRING) ROW format DELIMITED location 's3n://hive-tests/iris';- Insert data from local table
insert overwrite table s3iristable select * from iris;The objective is to load airline data into S3 bucket and split them according year and month.
-- create table
create table airlines (Year int,Month int,DayofMonth int,DayOfWeek int,DepTime int,CRSDepTime int,ArrTime int,CRSArrTime int,UniqueCarrier string,FlightNum int,TailNum string,ActualElapsedTime int,CRSElapsedTime int,AirTime int,ArrDelay int,DepDelay int,Origin string,Dest string,Distance int,TaxiIn int,TaxiOut int,Cancelled int,CancellationCode string,Diverted int,CarrierDelay int,WeatherDelay int,NASDelay int,SecurityDelay int,LateAircraftDelay int,IsArrDelayed string,IsDepDelayed string) row format delimited fields terminated by ',';
-- load data
load data local inpath 'allyears1m.csv' into table airlines;-- create S3 table:
-- create dynamic partition per years and months
create external table s3airlines (Year int,Month int,DayofMonth int,DayOfWeek int,DepTime int,CRSDepTime int,ArrTime int,CRSArrTime int,UniqueCarrier string,FlightNum int,TailNum string,ActualElapsedTime int,CRSElapsedTime int,AirTime int,ArrDelay int,DepDelay int,Origin string,Dest string,Distance int,TaxiIn int,TaxiOut int,Cancelled int,CancellationCode string,Diverted int,CarrierDelay int,WeatherDelay int,NASDelay int,SecurityDelay int,LateAircraftDelay int,IsArrDelayed string,IsDepDelayed string) partitioned by (theyear int, themonth int) row format delimited location 's3n://hive-tests/airlines/allyears1m/';
-- setup dynamic partitoning for hive queries
set hive.optimize.s3.query=true;
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.dynamic.partition=true;
set hive.enforce.bucketing = true;
-- insert data from local table
insert overwrite table s3airlines partition (theyear, themonth) select a.*,a.Year,a.Month from airlines a;--
-- Create S3 table:
-- * cluster per year and month
CREATE EXTERNAL TABLE s3airlines (Year int,Month int,DayofMonth int,DayOfWeek int,DepTime int,CRSDepTime int,ArrTime int,CRSArrTime int,UniqueCarrier string,FlightNum int,TailNum string,ActualElapsedTime int,CRSElapsedTime int,AirTime int,ArrDelay int,DepDelay int,Origin string,Dest string,Distance int,TaxiIn int,TaxiOut int,Cancelled int,CancellationCode string,Diverted int,CarrierDelay int,WeatherDelay int,NASDelay int,SecurityDelay int,LateAircraftDelay int,IsArrDelayed string,IsDepDelayed string) CLUSTERED BY (Year, Month) SORTED BY (Month) INTO 144 BUCKETS ROW FORMAT DELIMITED LOCATION 's3n://hive-tests-airlines/allyears10k/';
--
-- setup dynamic partitoning for hive queries
--
set hive.optimize.s3.query=true;
set hive.enforce.bucketing = true;
-- insert data from local table
INSERT OVERWRITE TABLE s3airlines SELECT a.* FROM airlines a;drop table airlines;
drop table s3airlines;- Do not use only
s3protocol but uses3n - It seems that Hive does not like a plain s3n bucket
s3n://tests/, but the directory is requireds3n://tests/table - Hive does not support nested directories - do not use
s3n://tests/table/subtable/