hive 表类型

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');