概述
文章目录
- hive 基本操作
- 1、数据库的基本操作
- 2、表的操作
- 3、表的其他操作
- 4、hive导入数据
- 5、hive 导出数据
hive 基本操作
1、数据库的基本操作
1）默认的数据库是 default
2）创建数据库：create database hivedb comment 'I am hive database';
完整格式：
create database|schema
[if not exists] database_name
[comment database_comment]
[location hdfs_path]
[with dbproperties (property_name=property_value, ...)]; 设置一些属性
创建数据库使用 database 或 schema 都可以；
if not exists：判断数据库是否存在；
comment：数据库注释；
location hdfs_path：指定数据库在 hdfs 上的目录，默认/user/hive/warehouse/database_name；
with dbproperties (property_name=property_value, ...)：设置一些属性
例：create database hivetestdb;
# 创建数据库，Hive会在/user/hive/warehouse/下创建一个以database_name.db命名的目录（如：/user/hive/warehouse/hivetestdb.db）
3）查看所有数据库：show databases;
4）查看指定数据库：desc database database_name;
5）查看指定数据库详细信息：desc database extended database_name;
6）查看当前数据库：select current_database();
7）切换数据库：use database_name;
8）删除空数据库：drop database database_name;
级联删除数据库(包括表)：drop database database_name cascade;
删除数据库完善格式：drop database if exists database_name cascade;
2、表的操作
操作 hive 表时，最好在前面加上数据库名
1）创建表：create database auser(id int comment 'I am id',name varchar(30));
创建表时指定分隔符：create database auser(id int comment 'I am id',name varchar(30)) [row format delimited fields terminated by 't'];
创建带分区的表：CREATE TABLE tab01 (id int,name string) PARTITIONED BY (ds string);
不指定 database 时，默认创建在 default 数据库中；
说明：在 hivetestdb 中创建表 auth_user，hive 会在 /user/hive/warehouse/hivetestdb.db 下创建 auth_user 目录。
2）查看表数据：select * from table_name;
3）查看当前库所有表：show tables;
4）查看建表语句：show create table table_name;
5）查看一个表的分区：show partition table_name;
如果这个表不存在分区则会报错
6）查看表结构(包括注释)：describe table_name 或 desc table_name 或 desc formatted table_name;
例：
describe extended utab2;
输出：
Table(
tableName : utab2,
dbName : default,
owner : root,
createTime : 1537504392,
lastAccessTime : 0,
retention : 0,
sd : StorageDescriptor(
cols : [
FieldSchema(name : id,type : int,comment : null),
FieldSchema(name : uname,type : string,comment : null),
FieldSchema(name : age,type : int,comment : null)
],
location : hdfs://localhost:9000/user/hive/warehouse/utab2,
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=8,
numRows=1,
rawDataSize=7,
COLUMN_STATS_ACCURATE={"BASIC_STATS" : "true","COLUMN_STATS" : {"age" : "true","id" : "true","uname" : "true"}},
numFiles=1,
transient_lastDdlTime=1537505000,
bucketing_version=2
},
viewOriginalText : null,
viewExpandedText : null,
tableType : MANAGED_TABLE,
rewriteEnabled : false,
catName : hive,
ownerType : USER
)
7）查看表详细信息：describe extended table_name;
8）修改表
a. 修改表名：alter table table_name rename to new_table_name;
b. 修改 location：alter table table_name set location 'hdfs://nameservice1/data/test';
c. 修改表属性(如编码)：alter table table_name set serdeproperties('serialization.encoding'='GBK');
d. 添加列：alter table table_name add columns (age int comment 'this is age');
e. 使用新字段覆盖表原所有字段：alter table table_name replace columns (age string comment 'only keep the column');
9）删除和清空表
a. 删除表：drop table [if exists] table_name;
b. 清空表：truncate table table_name;
注意：
》truncate table student; 只能对管理表使用，不能对外部表使用
》truncate 命令操作时表名前不能加上database_name
》外部表 drop 之后，数据表在hdfs上目录的数据文件依然存在，实际是只删除了保存在关系型数据库中的元数据
》管理表 truncate 之后，数据表location 所在目录的数据文件删除，目录依然存在。
管理表 drop 之后，数据表location 所在目录删除。删除了元数据和数据文件，数据目录。
10）插入数据
a. 单表插入
insert overwrite table tab09 select a.* from tab03 a;
insert overwrite table tab09 select 7,'cat';
insert into table auth_user values (4,'kitty');
说明：
overwrite 是覆盖，into 是追加。
b. 多表插入（Multi Table/File Inserts）
FROM src
INSERT OVERWRITE TABLE dest1 SELECT src.* WHERE src.key < 100
INSERT OVERWRITE TABLE dest2 SELECT src.key, src.value WHERE src.key >= 100 and src.key < 200
INSERT OVERWRITE TABLE dest3 PARTITION(ds='2008-04-08', hr='12') SELECT src.key WHERE src.key >= 200 and src.key < 300
INSERT OVERWRITE LOCAL DIRECTORY '/tmp/dest4.out' SELECT src.value WHERE src.key >= 300;
FROM pv_users
INSERT OVERWRITE TABLE pv_gender_sum SELECT pv_users.gender, count_distinct(pv_users.userid) GROUP BY pv_users.gender
INSERT OVERWRITE DIRECTORY '/user/data/tmp/pv_age_sum' SELECT pv_users.age, count_distinct(pv_users.userid) GROUP BY pv_users.age;
【Dynamic-Partition Insert -- version 0.6.0 后增加的新特征】
FROM page_view_stg pvs
INSERT OVERWRITE TABLE page_view PARTITION(dt='2008-06-08', country='US')
SELECT pvs.viewTime, pvs.userid, pvs.page_url, pvs.referrer_url, null, null, pvs.ip WHERE pvs.country = 'US'
INSERT OVERWRITE TABLE page_view PARTITION(dt='2008-06-08', country='CA')
SELECT pvs.viewTime, pvs.userid, pvs.page_url, pvs.referrer_url, null, null, pvs.ip WHERE pvs.country = 'CA'
INSERT OVERWRITE TABLE page_view PARTITION(dt='2008-06-08', country='UK')
SELECT pvs.viewTime, pvs.userid, pvs.page_url, pvs.referrer_url, null, null, pvs.ip WHERE pvs.country = 'UK';
上面这个语句，是一个非常不好的示例，因为我们预先需要知道所有的 country，并且 dt 如果变了，那么我们需要重新增加新的 insert 语句。例如，当还有另外一个 country='DC' 或者 dt = '2008-09-10'
Dynamic-partition insert 是为了解决上述问题而被设计的。所以 Dynamic-partition insert 如下即可
FROM page_view_stg pvs
INSERT OVERWRITE TABLE page_view PARTITION(dt='2008-06-08', country)
SELECT pvs.viewTime, pvs.userid, pvs.page_url, pvs.referrer_url, null, null, pvs.ip, pvs.country
说明：
上述示例语句中，dt 是一个静态分区列（因为它的值一直都是2008-06-08，没有任何变化），country 是动态分区列。
动态分区列的值来自输入列。
目前，只允许动态分区列作为分区子句中的最后一列（s），因为分区列顺序表示它的层次顺序，所以不能用(dt, country='US')来指定分区子句。
select 语句中额外增加的 pvs.country 列，是动态分区列对应的输入列。请注意，您不需要为静态分区列添加一个输入列，因为它的值已经在隔断子句中知道了。
注意，动态分区值是通过排序、而不是名称来选择的，并作为select子句的最后一列来选择。（即动态分区列的值是来自 select 子句的最后一列，而不通过名字匹配的）
动态分区插入语句的语义：
当动态分区列已经存在非空分区时（例如，在某些ds根分区之下存在着country='CA'），如果动态分区插入在输入数据中看到相同的值（比如'CA'），就会被覆盖。
因为 Hive 分区对应于HDFS中的一个目录，所以分区值必须符合HDFS路径格式。任何在URI中具有特殊含义的字符（例如，'%', ':', '/', '#'）都将以'%'的形式转义，后跟2字节的ASCII值。
如果输入列是非字符串的类型，那么它的值将首先被转换成字符串，用于构造HDFS路径。
如果输入列值为NULL或空字符串，这一行将被放入一个特殊的分区中，该分区的名称由hive参数hive.exec.default.default.name控制。默认值是HIVE_DEFAULT_PARTITION{}。基本上这个分区将包含所有的“坏”行，它们的值不是有效的分区名称。这种方法的警告是，如果您选择Hive，那么它将丢失并被HIVE_DEFAULT_PARTITION{}所取代。JIRA hia-1309是一个解决方案，让用户指定“坏文件”来保留输入分区列值。
动态分区插入可能是一个资源占用者，因为它可以在短时间内生成大量的分区。为了让自己分桶，我们定义了三个参数：
hive.exec.max.dynamic.partitions.pernode：(默认值是1000)是每个mapper或reducer可以创建的最大动态分区数。如果一个mapper或reducer创建的比这个阈值更多，那么将会从map/reducer（通过计数器）中产生一个致命的错误，整个job将被杀死。
hive.exec.max.dynamic.partitions：(默认值是100)能够被一个DML创建的动态分区的总数。如果每一个mapper/reducer都没有超过限制，但是动态分区的总数是超过了，那么在将中间数据移动到最终目的地之前，将会抛出一个异常结束 job。
hive.exec.max.created.files：(默认值是100000)是所有的mapper和reducer创建的最大的文件总数。每一个mapper/reducer 创建一个新文件时将执行 Hadoop counter 更新。如果总数超过了hive.exec.max.created.files，将抛出一个致命的错误，job 将被杀死。
我们想要保护不利于动态分区插入的另一种情况是，用户可能意外地指定所有分区为动态分区，而不指定一个静态分区，虽然最初的目的是想覆盖一个根分区的子分区。我们可以定义另外一个参数 hive.exec.dynamic.partition.mode=strict 来保护这种全动态分区情况。在严格模式下，您必须指定至少一个静态分区。默认模式是严格的。另外，我们可以用一个参数 hive.exec.dynamic.partition=true/false 来控制是否允许动态分区。在Hive 0.9.0之前默认值是false，在Hive 0.9.0和之后默认值是 true。
在Hive 0.6中，hive.merge.mapfiles=true or hive.merge.mapredfiles=true时动态分区插入不工作。所以它内部关闭了merge 参数。在Hive 0.7中 merging file 是支持动态分区插入的（详见JIRA hi1307）。
3、表的其他操作
1）group by
CREATE TABLE tab10 (name string) row format delimited fields terminated by 't';
a. select 语句为常规 sql
INSERT OVERWRITE TABLE tab10 SELECT a.name FROM tab09 a GROUP BY a.name;
b. from 写到了 select 的前面
FROM tab09 a INSERT OVERWRITE TABLE tab10 SELECT a.name GROUP BY a.name;
2）Aggregations
INSERT OVERWRITE TABLE pv_gender_sum SELECT pv_users.gender, count(DISTINCT pv_users.userid) FROM pv_users GROUP BY pv_users.gender;
INSERT OVERWRITE TABLE pv_gender_agg SELECT pv_users.gender, count(DISTINCT pv_users.userid), count(*), sum(DISTINCT pv_users.userid) FROM pv_users GROUP BY pv_users.gender;
3）joins
a. join
FROM tab001 t1 JOIN tab002 t2 ON (t1.bar = t2.bar) INSERT OVERWRITE TABLE tab003 SELECT t1.bar, t1.foo, t2.foo;
INSERT OVERWRITE TABLE pv_users SELECT pv.*, u.gender, u.age FROM user u JOIN page_view pv ON (pv.userid = u.id) WHERE pv.date = '2008-03-03';
b. LEFT OUTER, RIGHT OUTER or FULL OUTER，外部连接
INSERT OVERWRITE TABLE pv_users SELECT pv.*, u.gender, u.age FROM user u FULL OUTER JOIN page_view pv ON (pv.userid = u.id) WHERE pv.date = '2008-03-03';
c. LEFT SEMI JOIN，为了检查另一张表中的键的存在，用户可以使用左半连接，如下面的例子所示
INSERT OVERWRITE TABLE pv_users SELECT u.* FROM user u LEFT SEMI JOIN page_view pv ON (pv.userid = u.id) WHERE pv.date = '2008-03-03';
d. 多表连接
INSERT OVERWRITE TABLE pv_friends SELECT pv.*, u.gender, u.age, f.friends FROM page_view pv JOIN user u ON (pv.userid = u.id) JOIN friend_list f ON (u.id = f.uid) WHERE pv.date = '2008-03-03';
4）Sampling -- 抽样
从pv_gender_sum的32个桶中选择第三个桶
INSERT OVERWRITE TABLE pv_gender_sum_sample SELECT pv_gender_sum.* FROM pv_gender_sum TABLESAMPLE(BUCKET 3 OUT OF 32);
5）Union All
INSERT OVERWRITE TABLE actions_users
SELECT u.id, actions.date
FROM (
SELECT av.uid AS uid
FROM action_video av
WHERE av.date = '2008-06-03'
UNION ALL
SELECT ac.uid AS uid
FROM action_comment ac
WHERE ac.date = '2008-06-03'
) actions JOIN users u ON(u.id = actions.uid);
6）Custom Map/Reduce Scripts -- 自定义 Map/Reduce 脚本
https://cwiki.apache.org/confluence/display/Hive/Tutorial#Tutorial-BuiltInOperatorsandFunctions
FROM (
FROM pv_users
MAP pv_users.userid, pv_users.date
USING 'map_script'
AS dt, uid
CLUSTER BY dt) map_output
INSERT OVERWRITE TABLE pv_users_reduced
REDUCE map_output.dt, map_output.uid
USING 'reduce_script'
AS date, count;
7）streaming
FROM invites a INSERT OVERWRITE TABLE events SELECT TRANSFORM(a.foo, a.bar) AS (oof, rab) USING '/bin/cat' WHERE a.ds > '2008-08-09';
4、hive导入数据
1）table --> table
a. 导入其他表数据
insert overwrite|into table table_name select * from table_name2;
b. 创建表时导入其他表数据
create table table_name as select * from table_name2;
c. 创建表时指定 location，并导入其他表数据
注意：创建表指定 location 导入数据时必须是不存在的目录，不然创建表会失败。可以先创建表，再删除相应的目录，再将数据上传到对应的目录。表就可以直接加载出数据。
2）file --> table 将文件内容导入到表中（表所有目录）
load data [local] inpath '/tmp/students.txt' [overwrite] into table table_name [partition(part1='value1',part2='value2',...)];
示例1：
load data local inpath '/tmp/students.txt' overwrite into table tab03;
说明：
local 表示加载本地数据；不使用 local 时，表示加载 HDFS 上的数据。
加上 overwrite 表示覆盖，不加 overwrite 表示追加。
inpath 是加载的数据的目录，一般写全路径。可以指定到文件，也可以直接指定目录，指定目录会加载目录下所有文件的数据。
partition：向分区表中导入数据。
示例2：
# local 用于指定本地文件，如果缺省了，则会在 HDFS 中查找文件；OVERWRITE 表示覆盖，如果缺省则表示添加
load data local inpath '/tmp/students.txt' overwrite into table tab03;
# students.txt 内容为
id name
1 'zhangsan'
2 'lisi'
3 wangwu
# select * from tab03;
NULL name
1 'zhangsan'
2 'lisi'
3 wangwu
上面为 NULL 是因为第一列类型为 int
示例3：
CREATE TABLE tab07 (id int,name string) PARTITIONED BY (ds string) row format delimited fields terminated by 't';
load data local inpath '/tmp/students.txt' overwrite into table tab07 PARTITION (ds='2008-08-15');
# students.txt
1 'zhangsan'
2 'lisi'
3 wangwu
4 tom
5 cat
6 kitty
select * from tab07;
1 'zhangsan' 2008-08-15
2 'lisi' 2008-08-15
3 wangwu 2008-08-15
4 tom 2008-08-15
5 cat 2008-08-15
6 kitty 2008-08-15
5、hive 导出数据
1）table --> file
insert overwrite|into [local] directory '/temp/students.txt' [row format delimited fields terminated by 't'] select * from table_name
示例：
# 往 HDFS 里写数据
insert overwrite directory '/tmp/students.txt' select * from tab07 t where t.ds = '2008-08-15';
# 往本地文件里写数据
insert overwrite local directory '/tmp/students5' select a.* from tab03 a;
insert overwrite local directory '/tmp/students6' select * from tab07 t where t.ds = '2008-08-15';
参考地址：
https://cwiki.apache.org/confluence/display/Hive/Tutorial
https://www.cnblogs.com/johnny-YJL/p/7940594.html
最后
以上就是留胡子指甲油为你收集整理的004-hive基本操作hive 基本操作的全部内容，希望文章能够帮你解决004-hive基本操作hive 基本操作所遇到的程序开发问题。
如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。
本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
发表评论 取消回复