最基本的建表语句:
create table student(id string comment '学号',name string comment '姓名',sex string comment '性别',age string comment '年龄') comment '学生表';
show create table student;
CREATE TABLE `student`( `id` string COMMENT '学号', `name` string COMMENT '姓名', `sex` string COMMENT '性别', `age` string COMMENT '年龄')COMMENT '学生表'ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'LOCATION 'hdfs://mycluster/user/hive/warehouse/gld.db/student'TBLPROPERTIES ( 'COLUMN_STATS_ACCURATE'='{\"BASIC_STATS\":\"true\"}', 'numFiles'='0', 'numRows'='0', 'rawDataSize'='0', 'totalSize'='0', 'transient_lastDdlTime'='1537867370')
插入样本数据:
insert into student values('1','孙悟空','男','100');insert into student values('2','明世隐','男','101');insert into student values('3','高渐离','男','102');insert into student values('4','孙尚香','女','103');insert into student values('5','安琪拉','女','104');
$ hdfs dfs -ls /user/hive/warehouse/gld.db/studentFound 5 items-rwxrwxrwx 3 gld supergroup 20 2018-09-25 18:15 /user/hive/warehouse/gld.db/student/000000_0-rwxrwxrwx 3 gld supergroup 20 2018-09-25 18:18 /user/hive/warehouse/gld.db/student/000000_0_copy_1-rwxrwxrwx 3 gld supergroup 20 2018-09-25 18:18 /user/hive/warehouse/gld.db/student/000000_0_copy_2-rwxrwxrwx 3 gld supergroup 20 2018-09-25 18:19 /user/hive/warehouse/gld.db/student/000000_0_copy_3-rwxrwxrwx 3 gld supergroup 20 2018-09-25 18:19 /user/hive/warehouse/gld.db/student/000000_0_copy_4
$ hdfs dfs -cat /user/hive/warehouse/gld.db/student/000000_01孙悟空男100$ hdfs dfs -cat /user/hive/warehouse/gld.db/student/000000_0_copy_12明世隐男101
create table student(id string comment '学号',name string comment '姓名',sex string comment '性别',age string comment '年龄') comment '学生表'row format delimitedfields terminated by '\t';
建立外部表:这个针对HBase中已有的表
HBase:
hbase> create 'gld:student', 'cf1'
Hive:
SET hbase.zookeeper.quorum=zkNode1,zkNode2,zkNode3;CREATE DATABASE gld;USE gld;
CREATE EXTERNAL TABLE student(id STRING,name STRING,sex STRING,age STRING) STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key, cf1:name, cf1:sex, cf1:age")TBLPROPERTIES("hbase.table.name" = "gld:student");
hbase.zookeeper.quorum:设定HBase中ZooKeeper的地址,格式:ip:port,ip:port,ip:port,端口默认2181,可不填。
hbase.columns.mapping:映射HBase中对应的列,格式:列族名:列名,:key表示HBase中的rowkey。
hbase.table.name:映射HBase中对应的表名,格式:命名空间:表名。
这样之后,就可以在Hive中通过SQL语句操作HBase中的表了:insert,delete,update,select。
注意点:
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:MetaException(message:Table gld:student already exists within HBase; use CREATE EXTERNAL TABLE instead to register it in Hive.)
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:MetaException(message:HBase table gld:student doesn't exist while the table is declared as xternal table.)
建立内部表:这个针对HBase中并没有这个表
CREATE TABLE student(id STRING,name STRING,sex STRING,age STRING) STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key, cf1:name, cf1:sex, cf1:age")TBLPROPERTIES("hbase.table.name" = "gld:student");
注意点:
联系客服