Hive学习笔记（6）DDL

官方参考文档 LanguageManual DDL

创建/删除/更改/使用数据库

在hive sql中database关键词和 schema关键词可以互换，意思是一样的

创建数据库

CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];

小牛试刀：
hive> create database if not exists test;
OK
Time taken: 0.131 seconds
hive> create schema if not exists test_schema;
OK
Time taken: 0.07 seconds
hive> show databases;
OK
default
test
test_schema
Time taken: 0.255 seconds, Fetched: 3 row(s)
hive> create database if not exists stefan comment 'just for test';
OK
Time taken: 0.047 seconds
hive> desc database stefan;
OK
stefan  just for test   hdfs://jms-master-01:9000/user/hive/warehouse/stefan.db hadoop  USER
Time taken: 0.068 seconds, Fetched: 1 row(s)

删除数据库

删除数据库时，默认行为是RESTRICT，这种情况下如果数据库不为空，则删除动作失败。如果需要删除库以及库里的表可以使用CASCADE关键字。

DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];

小牛试刀：
hive> drop database test;
OK
Time taken: 0.242 seconds
hive> drop database if exists test;
OK
Time taken: 0.011 seconds
hive> drop database if exists test_schema cascade;
OK
Time taken: 1.518 seconds
hive> show databases;
OK
default
prop_database
stefan

修改数据库

需要注意下述命令的生效版本号。

ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, ...); -- (Note: SCHEMA added in Hive 0.14.0)

ALTER (DATABASE|SCHEMA) database_name SET OWNER [USER|ROLE] user_or_role; -- (Note: Hive 0.13.0 and later; SCHEMA added in Hive 0.14.0)

ALTER (DATABASE|SCHEMA) database_name SET LOCATION hdfs_path; -- (Note: Hive 2.2.1, 2.4.0 and later)

alter database ... set location 语句不会将数据库当前目录的内容移动到新指定的位置。它不会更改当前数据库下已经存在的任何表/分区关联的位置，仅改变在该数据库下新建表的默认父目录。

小牛试刀：

添加属性：

hive> show create database stefan;
OK
CREATE DATABASE `stefan`
COMMENT
  'just for test'
LOCATION
  'hdfs://jms-master-01:9000/user/hive/warehouse/stefan.db'
Time taken: 0.222 seconds, Fetched: 5 row(s)
hive> alter database stefan set dbproperties ('owner'='stefan', 'create_time'='20190403');
OK
Time taken: 0.07 seconds
hive> show create database stefan;
OK
CREATE DATABASE `stefan`
COMMENT
  'just for test'
LOCATION
  'hdfs://jms-master-01:9000/user/hive/warehouse/stefan.db'
WITH DBPROPERTIES (
  'create_time'='20190403',
  'owner'='stefan')
Time taken: 0.196 seconds, Fetched: 8 row(s)

修改owner：

hive> alter schema stefan set owner user stefan_test;
OK
Time taken: 0.084 seconds
hive> desc database stefan;
OK
stefan  just for test   hdfs://jms-master-01:9000/user/hive/warehouse/stefan.db stefan_test USER
Time taken: 0.145 seconds, Fetched: 1 row(s)

切换数据库

USE database_name;
USE DEFAULT;

hive> use stefan;
OK
Time taken: 0.019 seconds

查看库

show databases;

hive> show databases;
OK
default
Time taken: 5.987 seconds, Fetched: 1 row(s)

查看当前库

show current_database();

hive> select current_database();
OK
default
Time taken: 0.852 seconds, Fetched: 1 row(s)

查看库的属性信息

desc database extended default;

hive> desc database extended default;
OK
default Default Hive database   hdfs://jms-master-01:9000/user/hive/warehouse   public  ROLE
Time taken: 0.361 seconds, Fetched: 1 row(s)

查看建库语句

show create database default;

hive> show create database default;
OK
CREATE DATABASE `default`
COMMENT
  'Default Hive database'
LOCATION
  'hdfs://jms-master-01:9000/user/hive/warehouse'
Time taken: 0.525 seconds, Fetched: 5 row(s)

建库时携带属性

create database if not exists prop_database comment 'test properties' with dbproperties ('prop1'='aaa', 'prop2'='bbb');

hive> create database if not exists prop_database comment 'test properties database' with dbproperties('prop1'='aaa', 'prop2'='bbb');
OK
Time taken: 0.121 seconds
hive> show create database prop_database;
OK
CREATE DATABASE `prop_database`
COMMENT
  'test properties database'
LOCATION
  'hdfs://jms-master-01:9000/user/hive/warehouse/prop_database.db'
WITH DBPROPERTIES (
  'prop1'='aaa',
  'prop2'='bbb')
Time taken: 0.181 seconds, Fetched: 8 row(s)

创建/删除/清空表

建表

根据指定表明创建表。当表明存在时抛出异常，可以使用 IF NOT EXISTS 跳过该异常。
*表和列的注释comment格式是字符串，使用单引号。
*表名和列名不区分大小写。
*使用external创建的表是外部表，不使用默认创建的表是托管表（我习惯称之为内部表）。查看一个表是托管表还是外部表，可以通过describe extended table_name;查看其中的tableType属性值。

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name -- (Note: TEMPORARY available in Hive 0.14.0 and later)
[(col_name data_type [COMMENT col_comment], ... [constraint_specification])]
[COMMENT table_comment]
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
[CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
[SKEWED BY (col_name, col_name, ...) -- (Note: Available in Hive 0.10.0 and later)]
ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)
[STORED AS DIRECTORIES]
[
[ROW FORMAT row_format]
[STORED AS file_format]
| STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)] -- (Note: Available in Hive 0.6.0 and later)
]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)] -- (Note: Available in Hive 0.6.0 and later)
[AS select_statement]; -- (Note: Available in Hive 0.5.0 and later; not supported for external tables)

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
LIKE existing_table_or_view_name
[LOCATION hdfs_path];

data_type
: primitive_type
| array_type
| map_type
| struct_type
| union_type -- (Note: Available in Hive 0.7.0 and later)

primitive_type
: TINYINT
| SMALLINT
| INT
| BIGINT
| BOOLEAN
| FLOAT
| DOUBLE
| DOUBLE PRECISION -- (Note: Available in Hive 2.2.0 and later)
| STRING
| BINARY -- (Note: Available in Hive 0.8.0 and later)
| TIMESTAMP -- (Note: Available in Hive 0.8.0 and later)
| DECIMAL -- (Note: Available in Hive 0.11.0 and later)
| DECIMAL(precision, scale) -- (Note: Available in Hive 0.13.0 and later)
| DATE -- (Note: Available in Hive 0.12.0 and later)
| VARCHAR -- (Note: Available in Hive 0.12.0 and later)
| CHAR -- (Note: Available in Hive 0.13.0 and later)

array_type
: ARRAY < data_type >

map_type
: MAP < primitive_type, data_type >

struct_type
: STRUCT < col_name : data_type [COMMENT col_comment], ...>

union_type
: UNIONTYPE < data_type, data_type, ... > -- (Note: Available in Hive 0.7.0 and later)

row_format
: DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
[NULL DEFINED AS char] -- (Note: Available in Hive 0.13 and later)
| SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]

file_format:
: SEQUENCEFILE
| TEXTFILE -- (Default, depending on hive.default.fileformat configuration)
| RCFILE -- (Note: Available in Hive 0.6.0 and later)
| ORC -- (Note: Available in Hive 0.11.0 and later)
| PARQUET -- (Note: Available in Hive 0.13.0 and later)
| AVRO -- (Note: Available in Hive 0.14.0 and later)
| JSONFILE -- (Note: Available in Hive 4.0.0 and later)
| INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname

constraint_specification:
: [, PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE ]
[, CONSTRAINT constraint_name FOREIGN KEY (col_name, ...) REFERENCES table_name(col_name, ...) DISABLE NOVALIDATE

创建托管表：
hive> create table manage_table (id int, name string);
OK
Time taken: 0.674 seconds

创建外部表：

hive> create external table external_table (id int, name string)  location '/user/hadoop/tmp/hive/default.db/external_table';
OK
Time taken: 0.449 seconds

查看表是托管表还是外部表：
可以看出托管表的tableType是MANAGED_TABLE；外部表的tableType是EXTERNAL_TABLE。

hive> describe extended manage_table;
OK
id                      int
name                    string

Detailed Table Information  Table(tableName:manage_table, dbName:default, owner:hadoop, createTime:1554341982, lastAccessTime:0, retention:0, sd:StorageDescriptor(cols:[FieldSchema(name:id, type:int, comment:null), FieldSchema(name:name, type:string, comment:null)], location:hdfs://jms-master-01:9000/user/hive/warehouse/manage_table, inputFormat:org.apache.hadoop.mapred.TextInputFormat, outputFormat:org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat, compressed:false, numBuckets:-1, serdeInfo:SerDeInfo(name:null, serializationLib:org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, parameters:{serialization.format=1}), bucketCols:[], sortCols:[], parameters:{}, skewedInfo:SkewedInfo(skewedColNames:[], skewedColValues:[], skewedColValueLocationMaps:{}), storedAsSubDirectories:false), partitionKeys:[], parameters:{totalSize=0, numRows=0, rawDataSize=0, COLUMN_STATS_ACCURATE={"BASIC_STATS":"true"}, numFiles=0, transient_lastDdlTime=1554341982}, viewOriginalText:null, viewExpandedText:null, tableType:MANAGED_TABLE, rewriteEnabled:false)
Time taken: 0.329 seconds, Fetched: 4 row(s)
hive> describe extended external_table;
OK
id                      int
name                    string

Detailed Table Information  Table(tableName:external_table, dbName:default, owner:hadoop, createTime:1554342083, lastAccessTime:0, retention:0, sd:StorageDescriptor(cols:[FieldSchema(name:id, type:int, comment:null), FieldSchema(name:name, type:string, comment:null)], location:hdfs://jms-master-01:9000/user/hadoop/tmp/hive/default.db/external_table, inputFormat:org.apache.hadoop.mapred.TextInputFormat, outputFormat:org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat, compressed:false, numBuckets:-1, serdeInfo:SerDeInfo(name:null, serializationLib:org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, parameters:{serialization.format=1}), bucketCols:[], sortCols:[], parameters:{}, skewedInfo:SkewedInfo(skewedColNames:[], skewedColValues:[], skewedColValueLocationMaps:{}), storedAsSubDirectories:false), partitionKeys:[], parameters:{EXTERNAL=TRUE, transient_lastDdlTime=1554342083}, viewOriginalText:null, viewExpandedText:null, tableType:EXTERNAL_TABLE, rewriteEnabled:false)
Time taken: 0.143 seconds, Fetched: 4 row(s)

创建分区表

hive> create table partition_table (id int, name string) partitioned by (dt string);
OK
Time taken: 0.393 seconds
hive> show create table partition_table;
OK
CREATE TABLE `partition_table`(
  `id` int,
  `name` string)
PARTITIONED BY (
  `dt` string)
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://jms-master-01:9000/user/hive/warehouse/stefan.db/partition_table'
TBLPROPERTIES (
  'transient_lastDdlTime'='1554344052')
Time taken: 0.275 seconds, Fetched: 15 row(s)

create table as select（CTAS）

CTAS语法限制：

  • 目标表不能是外部表
  • 目标表不能是分桶表
    CTAS语法可以实现表的格式转换。
hive> select * from partition_table;
OK
1   郭靖  20190402    china
2   黄蓉  20190402    china
3   杨康  20190402    china
4   穆念慈 20190402    china
5   东邪  20190402    china
6   西毒  20190402    china
7   黄老邪 20190402    china
8   杨铁心 20190402    china
Time taken: 1.499 seconds, Fetched: 8 row(s)
hive> create table new_key_value_store row format serde "org.apache.hadoop.hive.serde2.columnar.ColumnarSerDe" stored as RCFile as select (id * 10) new_key, concat(id, name) key_value_pair from partition_table sort by new_key, key_value_pair;
WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
Query ID = hadoop_20190404103533_902fb48c-2471-4e2d-bc79-5cba46b8c710
Total jobs = 1
Launching Job 1 out of 1
Number of reduce tasks not specified. Estimated from input data size: 1
In order to change the average load for a reducer (in bytes):
  set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
  set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
  set mapreduce.job.reduces=<number>
Starting Job = job_1552651623473_0007, Tracking URL = http://jms-master-01:8088/proxy/application_1552651623473_0007/
Kill Command = /home/hadoop/tools/hadoop-2.7.7/bin/hadoop job  -kill job_1552651623473_0007
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1
2019-04-04 10:35:45,795 Stage-1 map = 0%,  reduce = 0%
2019-04-04 10:35:50,537 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 2.39 sec
2019-04-04 10:35:56,978 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 5.27 sec
MapReduce Total cumulative CPU time: 5 seconds 270 msec
Ended Job = job_1552651623473_0007
Moving data to directory hdfs://jms-master-01:9000/user/hive/warehouse/new_key_value_store
MapReduce Jobs Launched:
Stage-Stage-1: Map: 1  Reduce: 1   Cumulative CPU: 5.27 sec   HDFS Read: 8487 HDFS Write: 248 SUCCESS
Total MapReduce CPU Time Spent: 5 seconds 270 msec
OK
Time taken: 24.897 seconds
hive> select * from  new_key_value_store;
OK
10  1郭靖
20  2黄蓉
30  3杨康
40  4穆念慈
50  5东邪
60  6西毒
70  7黄老邪
80  8杨铁心
Time taken: 0.21 seconds, Fetched: 8 row(s)
hive> show create table new_key_value_store;
OK
CREATE TABLE `new_key_value_store`(
  `new_key` int,
  `key_value_pair` string)
ROW FORMAT SERDE
  'org.apache.hadoop.hive.serde2.columnar.ColumnarSerDe'
STORED AS INPUTFORMAT
  'org.apache.hadoop.hive.ql.io.RCFileInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.RCFileOutputFormat'
LOCATION
  'hdfs://jms-master-01:9000/user/hive/warehouse/new_key_value_store'
TBLPROPERTIES (
  'transient_lastDdlTime'='1554345358')
Time taken: 0.299 seconds, Fetched: 13 row(s)

克隆表（create table ... like）

create table ... like 会创建一个和源表结构完全一致的空表。

hive> create table like_new_key_value_store like new_key_value_store;
OK
Time taken: 0.149 seconds
hive> show create table like_new_key_value_store;
OK
CREATE TABLE `like_new_key_value_store`(
  `new_key` int,
  `key_value_pair` string)
ROW FORMAT SERDE
  'org.apache.hadoop.hive.serde2.columnar.ColumnarSerDe'
STORED AS INPUTFORMAT
  'org.apache.hadoop.hive.ql.io.RCFileInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.RCFileOutputFormat'
LOCATION
  'hdfs://jms-master-01:9000/user/hive/warehouse/like_new_key_value_store'
TBLPROPERTIES (
  'transient_lastDdlTime'='1554345937')
Time taken: 1.801 seconds, Fetched: 13 row(s)
hive> select * from like_new_key_value_store;
OK
Time taken: 0.252 seconds

分桶排序表

创建一个分桶排序表
hive> create table bucketed_table (id int, name string) partitioned by (dt string, country string) clustered by (id) sorted by (name) into 4 buckets row format delimited fields terminated by '\001' collection items terminated by '\002' map keys terminated by '\003' stored as sequencefile;
OK
Time taken: 0.241 seconds
hive> show create table bucketed_table;
OK
CREATE TABLE `bucketed_table`(
  `id` int,
  `name` string)
PARTITIONED BY (
  `dt` string,
  `country` string)
CLUSTERED BY (
  id)
SORTED BY (
  name ASC)
INTO 4 BUCKETS
ROW FORMAT SERDE
  'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES (
  'colelction.delim'='',
  'field.delim'='',
  'mapkey.delim'='',
  'serialization.format'='')
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.SequenceFileInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat'
LOCATION
  'hdfs://jms-master-01:9000/user/hive/warehouse/stefan.db/bucketed_table'
TBLPROPERTIES (
  'transient_lastDdlTime'='1554346570')
Time taken: 0.268 seconds, Fetched: 26 row(s)

向多个分区多动态插入，需要设置参数
set hive.exec.dynamici.partition=true; #开启动态分区，默认是false
set hive.exec.dynamic.partition.mode=nonstrict; #开启允许所有分区都是动态的，否则必须要有静态分区才能使用。

hive> insert into bucketed_table partition(dt, country) select id, name, dt, country from default.partition_table;
WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
Query ID = hadoop_20190404110332_1e7c3bf3-e5e6-4ff1-af30-2a5bcf0278e6
Total jobs = 1
Launching Job 1 out of 1
Number of reduce tasks determined at compile time: 4
In order to change the average load for a reducer (in bytes):
  set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
  set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
  set mapreduce.job.reduces=<number>
Starting Job = job_1552651623473_0008, Tracking URL = http://jms-master-01:8088/proxy/application_1552651623473_0008/
Kill Command = /home/hadoop/tools/hadoop-2.7.7/bin/hadoop job  -kill job_1552651623473_0008
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 4
2019-04-04 11:03:38,849 Stage-1 map = 0%,  reduce = 0%
2019-04-04 11:03:43,157 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 1.47 sec
2019-04-04 11:03:48,331 Stage-1 map = 100%,  reduce = 25%, Cumulative CPU 4.05 sec
2019-04-04 11:03:50,399 Stage-1 map = 100%,  reduce = 75%, Cumulative CPU 9.22 sec
2019-04-04 11:03:51,435 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 12.12 sec
MapReduce Total cumulative CPU time: 12 seconds 120 msec
Ended Job = job_1552651623473_0008
Loading data to table stefan.bucketed_table partition (dt=null, country=null)

Loaded : 1/1 partitions.
     Time taken to load dynamic partitions: 0.243 seconds
     Time taken for adding to write entity : 0.0 seconds
MapReduce Jobs Launched:
Stage-Stage-1: Map: 1  Reduce: 4   Cumulative CPU: 12.12 sec   HDFS Read: 21718 HDFS Write: 937 SUCCESS
Total MapReduce CPU Time Spent: 12 seconds 120 msec
OK
Time taken: 20.806 seconds
hive> select * from bucketed_table;
OK
8   杨铁心 20190402    china
4   穆念慈 20190402    china
5   东邪  20190402    china
1   郭靖  20190402    china
6   西毒  20190402    china
2   黄蓉  20190402    china
3   杨康  20190402    china
7   黄老邪 20190402    china
Time taken: 0.225 seconds, Fetched: 8 row(s)

数据倾斜表（Skewed Tables）

暂时没接触过也没研究过。

临时表

临时表只在当前会话可见。数据存储在用户的临时目录总，会话结束时删除。

  • 临时表不支持分区，不支持创建索引。
  • 如果临时表表明和永久表名冲突，则在当前会话中会屏蔽永久表；只有drop或rename临时表表名才能使用永久表。
  • 从hive1.1+版本起，临时表可以存储在内存或者SSD中，通过参数hive.exec.temporary.table.storage配置，可选值有memory，ssd，default。

create temporary table ...

小牛试刀
hive> create temporary table temp_table(id int, name string);
OK
Time taken: 6.14 seconds
hive> insert into temp_table select id, name from partition_table;
WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
Query ID = hadoop_20190408171623_2c787f42-e5e5-460d-8a21-90a5a5c9afc3
Total jobs = 3
Launching Job 1 out of 3
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_1552651623473_0010, Tracking URL = http://jms-master-01:8088/proxy/application_1552651623473_0010/
Kill Command = /home/hadoop/tools/hadoop-2.7.7/bin/hadoop job  -kill job_1552651623473_0010
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0
2019-04-08 17:16:32,625 Stage-1 map = 0%,  reduce = 0%
2019-04-08 17:16:38,994 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 2.1 sec
MapReduce Total cumulative CPU time: 2 seconds 100 msec
Ended Job = job_1552651623473_0010
Stage-4 is selected by condition resolver.
Stage-3 is filtered out by condition resolver.
Stage-5 is filtered out by condition resolver.
Moving data to directory hdfs://jms-master-01:9000/tmp/hive-hadoop/hadoop/8dbf32dc-51a3-4caa-b5be-9d682ccdb29c/_tmp_space.db/09e10ccb-5d5f-45ab-b07a-afe5590b2352/.hive-staging_hive_2019-04-08_17-16-23_750_5995661068890136721-1/-ext-10000
Loading data to table default.temp_table
MapReduce Jobs Launched:
Stage-Stage-1: Map: 1   Cumulative CPU: 2.1 sec   HDFS Read: 4605 HDFS Write: 155 SUCCESS
Total MapReduce CPU Time Spent: 2 seconds 100 msec
OK
Time taken: 16.73 seconds
hive> select * from temp_table;
OK
1   郭靖
2   黄蓉
3   杨康
4   穆念慈
5   东邪
6   西毒
7   黄老邪
8   杨铁心
Time taken: 0.161 seconds, Fetched: 8 row(s)

临时表插入数据不支持insert overwrite语法。

hive> insert overwrite temp_table select id, name from partition_table;
NoViableAltException(24@[])
    at org.apache.hadoop.hive.ql.parse.HiveParser.destination(HiveParser.java:38762)
    at org.apache.hadoop.hive.ql.parse.HiveParser.insertClause(HiveParser.java:38531)
    at org.apache.hadoop.hive.ql.parse.HiveParser.regularBody(HiveParser.java:36478)
    at org.apache.hadoop.hive.ql.parse.HiveParser.queryStatementExpressionBody(HiveParser.java:35822)
    at org.apache.hadoop.hive.ql.parse.HiveParser.queryStatementExpression(HiveParser.java:35710)
    at org.apache.hadoop.hive.ql.parse.HiveParser.execStatement(HiveParser.java:2284)
    at org.apache.hadoop.hive.ql.parse.HiveParser.statement(HiveParser.java:1333)
    at org.apache.hadoop.hive.ql.parse.ParseDriver.parse(ParseDriver.java:208)
    at org.apache.hadoop.hive.ql.parse.ParseUtils.parse(ParseUtils.java:77)
    at org.apache.hadoop.hive.ql.parse.ParseUtils.parse(ParseUtils.java:70)
    at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:468)
    at org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:1317)
    at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:1457)
    at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1237)
    at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1227)
    at org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriver.java:233)
    at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:184)
    at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:403)
    at org.apache.hadoop.hive.cli.CliDriver.executeDriver(CliDriver.java:821)
    at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:759)
    at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:686)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.apache.hadoop.util.RunJar.run(RunJar.java:226)
    at org.apache.hadoop.util.RunJar.main(RunJar.java:141)
FAILED: ParseException line 1:17 cannot recognize input near 'temp_table' 'select' 'id' in destination specification

退出当前会话，临时表会被删除。

hive> quit;
[hadoop@jms-master-01 ~]$ hive
which: no hbase in (/usr/share/svensudo/bin:/home/hadoop/tools/scala-2.12.8/bin:/home/hadoop/tools/spark-2.4.0-bin-hadoop2.7/bin:/home/hadoop/tools/java/jdk1.8.0_191/bin:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/usr/local/go/bin/:/alc/tool/bin:/alc/tool/bin:/home/hadoop/tools/hadoop-2.7.7/bin:/home/hadoop/tools/apache-hive-2.3.4-bin/bin:/home/hadoop/bin)
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/home/hadoop/tools/apache-hive-2.3.4-bin/lib/log4j-slf4j-impl-2.6.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/home/hadoop/tools/hadoop-2.7.7/share/hadoop/common/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]

Logging initialized using configuration in file:/home/hadoop/tools/apache-hive-2.3.4-bin/conf/hive-log4j2.properties Async: true
Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
hive> select * from temp_table;
FAILED: SemanticException [Error 10001]: Line 1:14 Table not found 'temp_table'

事务表（Transactional Tables）

从Hive1.4.0+版本开始支持事务表。Hive提供了支持ACID语义的事务表，可以完成增删改操作。
关于事务表，展开又是一个专题了。可以参考官方事务表文档

主键约束

从Hive2.1.0版本开始，支持约束。Hive支持未经验证的主键和外键约束，注意是未经验证的，所以主键的正确性需要上游数据来保证。

删除表

drop table [if exists] table_name [purge];
通常情况下，删除托管表，hive会删除元数据信息和数据文件，删除文件相当于执行了hadoop fs -rm，也就是说数据文件会被移动到hdfs系统下是trash回收站中，在误操作的情况下，在一定时效期间还可以找回数据。如果指定了purge选项，则不会移动到回收站下，而是直接永久删除。所以drop动作一定要谨慎。

Truncate表

truncate table table_name [partition partition_spec]
这里partition_spec指(partition_column=partition_col_value,partition_column=partition_col_value, ...)

相信如果对关系型数据库sql语言比较熟悉的话，以上操作都可以理解。

Alter table/partition/column

Alter table
重命名表

alter table table_name rename to new_table_name;

hive> show tables;
OK
first_table
Time taken: 5.51 seconds, Fetched: 9 row(s)
hive> alter table first_table rename to second_table;
OK
Time taken: 0.406 seconds
hive> show tables;
OK
second_table
Time taken: 0.05 seconds, Fetched: 9 row(s)
修改表的属性

alter table table_name set tblproperties table_properties;
table_properties:
(property_name = property_value, property_name = property_value, ...)

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 202,905评论 5赞 476
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 85,140评论 2赞 379
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 149,791评论 0赞 335
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,483评论 1赞 273
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,476评论 5赞 364
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,516评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 37,905评论 3赞 395
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,560评论 0赞 256
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 40,778评论 1赞 296
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,557评论 2赞 319
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,635评论 1赞 329
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,338评论 4赞 318
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 38,925评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,898评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,142评论 1赞 259
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 42,818评论 2赞 349
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,347评论 2赞 342

推荐阅读更多精彩内容