1; External Tables
CREATE EXTERNAL TABLE IFNOT EXISTS stocks(
exchange STRING,
symbol STRING,
ymd STRING,
price_open FLOAT,
price_high FLOAT,
price_low FLOAT,
price_close FLOAT,
volume INT,
price_adj_close FLOAT)
ROW FORMAT DELIMITEDFIELDS TERMINATED BY ','
LOCATION'/data/stocks';
2: Partitioned, Managed Tables
CREATE TABLE employees(
name STRING,
salary FLOAT,
subordinates ARRAY<STRING>,
deductions MAP<STRING,FLOAT>,
address STRUCT<street:STRING,city:STRING, state:STRING,zip:INT>
)
PARTITIONED BY (country STRING, state STRING);
3:External Partitioned Tables
CREATE EXTERNAL TABLE IF NOT EXISTS log_messages (
hms INT,
severity STRING,
server STRING,
process_id INT,
message STRING)
PARTITIONED BY (year INT, month INT, day INT)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
4:alter table
ALTER TABLE log_messagesADD PARTITION(year = 2012,month =1, day =2)
LOCATION'hdfs://master_server/data/log_messages/2012/01/02';
hive> SHOWPARTITIONSlog_messages;
...
year=2011/month=12/day=31
year=2012/month=1/day=1
year=2012/month=1/day=2
hive> DESCRIBEEXTENDEDlog_messages;
...
message string,
yearint,
monthint,
dayint
Detailed Table Information...
partitionKeys:[FieldSchema(name:year, type:int, comment:null),
FieldSchema(name:month, type:int, comment:null),
FieldSchema(name:day, type:int, comment:null)],
...
5:Customizing Table Storage Formats
CREATE TABLE employees(
name STRING,
salary FLOAT,
subordinates ARRAY<STRING>,
deductions MAP<STRING,FLOAT>,
address STRUCT<street:STRING,city:STRING, state:STRING,zip:INT>
)
ROWFORMAT DELIMITED
FIELDS TERMINATED BY '\001'
COLLECTION ITEMS TERMINATED BY '\002'
MAPKEYS TERMINATED BY'\003'
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;
6:
CREATE EXTERNAL TABLE IFNOT EXISTS stocks(
exchange STRING,
symbol STRING,
ymd STRING,
price_open FLOAT,
price_high FLOAT,
price_low FLOAT,
price_close FLOAT,
volume INT,
price_adj_close FLOAT)
CLUSTERED BY (exchange,symbol)
SORTED BY (ymdASC)
INTO 96 BUCKETS
ROW FORMAT DELIMITEDFIELDS TERMINATED BY ','
LOCATION'/data/stocks';
ALTER TABLE log_messages ADD IF NOT EXISTS
PARTITION (year = 2011, month = 1, day = 1) LOCATION '/logs/2011/01/01'
PARTITION (year = 2011, month = 1, day = 2) LOCATION '/logs/2011/01/02'
PARTITION (year = 2011, month = 1, day = 3) LOCATION '/logs/2011/01/03'
7:Changing Columns
ALTER TABLE log_messages
CHANGE COLUMN hmshours_minutes_seconds INT
COMMENT 'The hours,minutes, and seconds part of the timestamp'
AFTERseverity;
8: Adding Columns
ALTER TABLE log_messages ADD COLUMNS (
app_name STRING COMMENT 'Application name',
session_id LONG COMMENT 'The current session id');
9:Deleting or Replacing Columns
ALTER TABLE log_messagesREPLACE COLUMNS(
hours_mins_secs INTCOMMENT 'hour,minute, seconds from timestamp',
severity STRING COMMENT 'The messageseverity'
message STRING COMMENT 'The rest of the message');
10:Alter Table Properties
ALTER TABLE log_messagesSET TBLPROPERTIES(
'notes' ='The process id is no longer captured; this column is alwaysNULL');
11:Alter Storage Properties
ALTER TABLE log_messages
PARTITION(year = 2012, month = 1, day = 1)
SET FILE FORMAT SEQUENCEFILE;
ALTER TABLE table_using_JSON_storage
SET SERDE 'com.example.JSONSerDe'
WITH SERDEPROPERTIES (
'prop1' ='value1',
'prop2' ='value2');
ALTER TABLE table_using_JSON_storage
SET SERDEPROPERTIES (
'prop3' ='value3',
'prop4' ='value4');