본문 바로가기

Hadoop Ecosystem

[hadoop@h001 ~]$ cat .hivehistory with 항공데이터





[hadoop@h001 ~]$ cat .hivehistory 

show tables;

show databases;

show databases;


create table jtemp01 (a int, b string);

create table jtmep01_invites (a int, b string) partitioned by (cs string);


describe jtemp01;

describe jtemp01_invites;

alter table jtemp01 add columns (new_col int);

alter table invites add columns ( new_col2 int comment 'aaaa comment');

alter table jtmep01_invites add columns( new_col2 int comment 'aaa comment);


describe jtmep01_invites;

create table invites (foo int, bar string) partitioned by (ds string);

show tables;

describe invites;

alter table invites add columns (new_col2 int comment ' by jacob ');

describe invites;


load data local inpath './examples/files/kv2.txt' overwrite into table invites partition (ds='2008-08-15');


select a.foo from invites a where a.ds='2008-08-15';

select a.foo, a.bar, a.new_col2, a. from invites a where a.ds='2008-08-15';

select * from invites a where a.ds='2008-08-15';

select a.foo, a.ba* from invites a where a.ds='2008-08-15';

select a.foo, a.bar from invites a where a.ds='2008-08-15';


show tables;

exit;

show databases;

show tables;

exit;

show database;

show databases;

ls

;

exit;

show databases;

show tables;

ls

;

cls

;

clear;

show tables;

creat table invites ( foo int, bar string) partitioned by (ds string);

create table invites ( foo int, bar string) partitioned by (ds string);

describe invites;

alter table invites add column (new_col2 int comment ' by jacob ');

alter table invites add columns (new_col2 int comment ' by jacob ');

describe invites;


load data local inpath './examples/files/kv2.txt' overwrite into table invites partition (ds='2008-08-15');


load data local inpath '../examples/files/kv2.txt' overwrite into table invites partition (ds='2008-08-15');


select a.foo from invites a where a.ds = '2008-08-15';

sdfnl

;

load data local inpath '../examples/files/kv3.txt' overwrite into table invites partition (ds='2013-06-16');

select a.foo from invites a where a.ds='2013-06-16';

select a.foo from invites a;



CREATE TABLE airline_delay(

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 COMMENT 'A = carrier, B = weather, C = NAS, D=security' , 

Diverted    INT COMMENT '1 = yes, 0 = no', 

CarrierDelay    STRING, 

WeatherDelay    STRING, 

NASDelay    STRING, 

SecurityDelay    STRING, 

LateAircraftDelay STRING

)

COMMENT 'The data consists of flight arrival and departure details for all commercial flights within the USA, from October 1987 to April 2008'

Partitioned by (DelayYear INT)

ROW FORMAT DELIMITED

    FIELDS TERMINATED BY ','

    LINES TERMINATED BY '\n'

    STORED AS TEXTFILE;



CREATE TABLE airline_delay(

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 COMMENT 'A = carrier, B = weather, C = NAS, D=security' , 

Diverted    INT COMMENT '1 = yes, 0 = no', 

CarrierDelay    STRING, 

WeatherDelay    STRING, 

NASDelay    STRING, 

SecurityDelay    STRING, 

LateAircraftDelay STRING

)

COMMENT 'The data consists of flight arrival and departure details for all commercial flights within the USA, from October 1987 to April 2008'

Partitioned by (DelayYear INT)

ROW FORMAT DELIMITED

    FIELDS TERMINATED BY ','

    LINES TERMINATED BY '\n'

    STORED AS TEXTFILE;

describe airline_delay

;

load data local inpath '../examples/2008_new.csv'

overwrite into table airline_delay

partition (delayYear='2008') ;

select * from airline_delay 

where delayYear = '2008'

LIMIT 10;

select count(*) from airline_delay ;

load data local inpath '../examples/1987_new.csv'

overwrite into table airline_delay

partition (delayYear='1987') ;

select * from airline_delay limit 10;

select * from airline_delay where delayYear = 1987 limit 10;

select count(*) from airline_delay where delayYear = 1987 limit 10;

select count(*) from airline_delay where delayYear = 1987;

select Year, Month, count(*) as arrrive_delay_count

from airline_delay

where delayYear = 1987

and arrDelay > 0

Group by Year, Month;

select Year, Month, AVG(ArrDelay) as avg_arrive_delay_time, AVG(depDelay) as avg_departure_delay_time

from airline_delay

where delayYear = 1987

and arrdelay > 0

group by Year, Month;

thanks for wathing this movie even though it is a bit boring

and not well organized ^^

anyway thanks again 

from jacob  calebpro@gmail.com

show databases

;

show tables;

exit

;

show tables

;

dfs-help

;

dfs -help

;

dfs -help;

dfs -ls;

select * from system_logs;

show tables;

describe jtemp01;

list 

;

select * from jtemp01

;

select * from invites;

set hive.cli.print.header=true;

select * from invites limit 3;

[hadoop@h001 ~]$