hive--创建表

压缩格式--parquet,SNAPPY-外部表

CREATE EXTERNAL TABLE `abc`(
abc string,
`ab1` array<struct<areaId:bigint,lineId:bigint,types:string>>,
`ab2` array<struct<areaId:bigint,areaType:string,direction:boolean>>,
`ab3` array<struct<channelId:int,temAlarm:boolean,temperature:int>>,
`ab4` struct<status:array<struct<statusValue:bigint,types:string>>>)
PARTITIONED BY (
abc string)
ROW FORMAT SERDE
'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
STORED AS INPUTFORMAT
'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION
'/data/abc'
TBLPROPERTIES (
'parquet.compression'='SNAPPY')

 

压缩格式--parquet,SNAPPY-内部表

CREATE TABLE `abc`(
 abc string)
PARTITIONED BY ( 
  `pdt` string)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
TBLPROPERTIES (
  'parquet.compression'='SNAPPY')

 

字典表类---'\t' 分隔

CREATE TABLE `abc`(
abc string)
PARTITIONED BY (
`abc` string)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'

 

hbase 创建表

create 'abc', { NAME => 'ab', TTL=>'15552000',COMPRESSION => 'SNAPPY',VERSIONS=>3};

 

hive创建Hbase外部映射表

CREATE EXTERNAL TABLE `abc`(
  `rowkey` string COMMENT 'from deserializer', 
   `abc` int COMMENT 'from deserializer',
   `abc1` string COMMENT 'from deserializer')
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.hbase.HBaseSerDe' 
STORED BY 
  'org.apache.hadoop.hive.hbase.HBaseStorageHandler' 
WITH SERDEPROPERTIES ( 
  'hbase.columns.mapping'=':key,ab:abc,ab:abc1,'serialization.format'='1')
TBLPROPERTIES (
  'hbase.mapred.output.outputtable'='test_abc', 
  'hbase.table.name'='test_abc', 
  'last_modified_by'='hdfs')