set hive.execution.engine=mr;
set mapreduce.map.memory.mb=5240;
set mapreduce.reduce.memory.mb=5240;
set mapreduce.map.java.opts=-DAMYWANG_MAP=1 -Xmx4G -XX:PermSize=256m -XX:MaxPermSize=256m -XX:PrintGCDetails -XX:PrintGCTimeStamps;
set mapreduce.reduce.java.opts=-DAMYWANG_REDUCE=1 -Xmx4G -XX:PermSize=256m -XX:MaxPermSize=256m -XX:PrintGCDetails -XX:PrintGCTimeStamps;
create table airlines_all_05p(
Year INT,
Month INT,
DayofMonth INT,
DayOfWeek INT,
DepTime INT,
CRSDepTime INT,
ArrTime INT,
CRSArrTime INT,
UniqueCarrier STRING,
FlightNum INT,
TailNum INT,
ActualElapsedTime INT,
CRSElapsedTime INT,
AirTime INT,
ArrDelay INT,
DepDelay INT,
Dest STRING,
Origin 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 ‘,’
location ‘/apps/hive/warehouse/data/airlines_all_05p’;
load data inpath ‘hdfs://mr-0xd6.0xdata.loc:8020/user/amy/airlines_all.05p.csv’ into table airlines_all_05p;
create table csv_airlines_all(
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,
Dest STRING,
Origin 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 ‘,’
STORED AS ORC;
INSERT OVERWRITE TABLE orc_airlines_all_05p select * from airlines_all_05p;
The following is from Nidhi:
CREATE EXTERNAL TABLE ta (a1 INT, a2 STRING, a3 STRING, a4 STRING, a5 INT, a6 STRING, a7 INT, a8 INT, a9 INT)
STORED AS ORC;
LOAD DATA local INPATH ‘/home/wendy/demo-11-zlib.orc’ OVERWRITE INTO TABLE ta;
select * from ta limit 3;
#OK
#1 M M Primary 500 Good 0 0 0
#2 F M Primary 500 Good 0 0 0
#3 M S Primary 500 Good 0 0 0
CREATE EXTERNAL TABLE aa (a1 INT, a2 STRING, a3 STRING, a4 STRING, a5 INT, a6 STRING, a7 INT, a8 INT, a9 INT)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’
STORED AS TEXTFILE
LOCATION ‘/user/wendy/from_hive’;
select * from aa limit 3;
#OK
#NULL NULL NULL NULL NULL NULL NULL NULL NULL
#NULL NULL NULL NULL NULL NULL NULL NULL NULL
#NULL NULL NULL NULL NULL NULL NULL NULL NULL
INSERT OVERWRITE TABLE aa SELECT * FROM ta;
select * from aa limit 3;
#OK
#1 M M Primary 500 Good 0 0 0
#2 F M Primary 500 Good 0 0 0
#3 M S Primary 500 Good 0 0 0
#Time taken: 0.079 seconds, Fetched: 3 row(s)
Convert csv to orc:
CREATE EXTERNAL TABLE da (a1 INT, a2 INT, a3 INT, a4 INT, a5 INT, a6 INT, a7 DOUBLE, a8 DOUBLE, a9 INT)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’
STORED AS TEXTFILE;
LOAD DATA local INPATH ‘/home/nidhi/prostate.csv’ OVERWRITE INTO TABLE bb;
select * from da limit 3;
CREATE EXTERNAL TABLE bb (a1 INT, a2 INT, a3 INT, a4 INT, a5 INT, a6 INT, a7 DOUBLE, a8 DOUBLE, a9 INT)
STORED AS ORC
LOCATION ‘/user/nidhi/from_hive_prna’;
INSERT OVERWRITE TABLE bb
SELECT *
FROM da;
select * from bb limit 3;
create table for milsongs
create external table milsongs (
c1 INT,
c2 DOUBLE,
c3 DOUBLE,
c4 DOUBLE,
c5 DOUBLE,
c6 DOUBLE,
c7 DOUBLE,
c8 DOUBLE,
c9 DOUBLE,
c10 DOUBLE,
c11 DOUBLE,
c12 DOUBLE,
c13 DOUBLE,
c14 DOUBLE,
c15 DOUBLE,
c16 DOUBLE,
c17 DOUBLE,
c18 DOUBLE,
c19 DOUBLE,
c20 DOUBLE,
c21 DOUBLE,
c22 DOUBLE,
c23 DOUBLE,
c24 DOUBLE,
c25 DOUBLE,
c26 DOUBLE,
c27 DOUBLE,
c28 DOUBLE,
c29 DOUBLE,
c30 DOUBLE,
c31 DOUBLE,
c32 DOUBLE,
c33 DOUBLE,
c34 DOUBLE,
c35 DOUBLE,
c36 DOUBLE,
c37 DOUBLE,
c38 DOUBLE,
c39 DOUBLE,
c40 DOUBLE,
c41 DOUBLE,
c42 DOUBLE,
c43 DOUBLE,
c44 DOUBLE,
c45 DOUBLE,
c46 DOUBLE,
c47 DOUBLE,
c48 DOUBLE,
c49 DOUBLE,
c50 DOUBLE,
c51 DOUBLE,
c52 DOUBLE,
c53 DOUBLE,
c54 DOUBLE,
c55 DOUBLE,
c56 DOUBLE,
c57 DOUBLE,
c58 DOUBLE,
c59 DOUBLE,
c60 DOUBLE,
c61 DOUBLE,
c62 DOUBLE,
c63 DOUBLE,
c64 DOUBLE,
c65 DOUBLE,
c66 DOUBLE,
c67 DOUBLE,
c68 DOUBLE,
c69 DOUBLE,
c70 DOUBLE,
c71 DOUBLE,
c72 DOUBLE,
c73 DOUBLE,
c74 DOUBLE,
c75 DOUBLE,
c76 DOUBLE,
c77 DOUBLE,
c78 DOUBLE,
c79 DOUBLE,
c80 DOUBLE,
c81 DOUBLE,
c82 DOUBLE,
c83 DOUBLE,
c84 DOUBLE,
c85 DOUBLE,
c86 DOUBLE,
c87 DOUBLE,
c88 DOUBLE,
c89 DOUBLE,
c90 DOUBLE,
c91 DOUBLE)