HIVE常用DDL语句

• 基本内部分区表

create table IF NOT EXISTS table1 (
name String,
age String,
code String)
PARTITIONED BY(dt String)
ROW FORMAT DELIMITED
  FIELDS TERMINATED BY "|"
 STORED AS TEXTFILE

$ LOAD DATA LOCAL INPATH "/home/data" INTO TABLE table1 PARTITION(dt="2018")

• 基本外部表

create EXTERNAL TABLE IF NOT EXISTS table2(
name String,
age String,
code String)
PARTITIONED BY(dt String, city String)
ROW FORMAT DELIMITED
  FIELDS TERMINATED BY "|"
STORED AS TEXTFILE
LOCATION "/user/data"

$ ALTER TABLE table2 ADD IF NOT EXISTS  PARTITION(dt="2018",city="nc")

• avro表

CREATE EXTERNAL TABLE IF NOT EXISTS `format`(
  `date` string COMMENT '', 
  `time` string COMMENT '', 
  `status` string COMMENT '', 
  `name` string COMMENT '', 
  `plat` string COMMENT '', 
  `version` string COMMENT '')
PARTITIONED BY ( 
  `ds` string)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.serde2.avro.AvroSerDe' 
WITH SERDEPROPERTIES ( 
  'avro.schema.url'='hdfs:///user/schema/softschema.avsc') 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat'
LOCATION
  'hdfs://localhost:8020/output/format'

$ ALTER TABLE FORMAT ADD IF NOT EXISTS PARTITION(ds="2018")

数据导入

• partition load

$ ALTER TABLE table2 ADD IF NOT EXISTS PARTITION(ds="2018")

• file to hive

$ LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]

$ LOAD DATA LOCAL INPATH "/home/dada" OVERWRITE INTO TABLE  tablename partition(dt="2018",city="nc")

• table to table

$ INSERT INTO tablename PARTITION(dt="2018",city="nc") SELECT NAME,ID,CODE From
tale2 WHERE value= "xzc"

$ INSERT OVERWRITE table1 PARTITION(dt="2018",city="nc") IF NOT EXISTS  SELECT NAME,ID FROM table2 where nd_dm="2018"