备份hive元数据仓库 metastore :mysqldump -uroot -p metasore > metastore.sql
还原hive元数据库 metastore:
①mysql -uroot -p metastore < metastore.sql
****HQL语句操作的两种方式
①bin/hive -e "HQL语句" :bin/hive -e "select * from stupid.t1;"
②bin/hive -f 文件.hql : bin/hive -f hql/emp.hql
****显示表的详细信息
hive > desc formate 表名
****导入数据
load data local inpath '文件路劲' into table 表名;
load data local inpath '/opt/modules/hive-0.13.1-cdh5.3.6/input/dept.txt' into table db_hive_demo.dept;
****Hive历史命令的存放地址
cat ~/.hivehistory (~是当前用户的家目录)
*****HIVE临时生效设置
set 属性名=属性值 (只在本次运行客户端时有效,下次启动无效)
列如:set hive.cli.print.current.db=false;
*****Hive的内部表与外部表
默认情况:inner
hive>CERATE INNER TABLE
显示指定:external
hive>CREATE EXTERNAL TABLE
内部表:
删除表数据时,连同数据源以及元数据同时删除
外部表:
①只会删除元数据信息
②方便共享数据,相对而言也更安全
相同之处:
如果你导入数据时,操作于HDFS上,则会将数据进行迁移,并在metastore留下记录,而不是copy数据源
*******Hive分区表
创建分区表:
create database if not exists db_web_data ;
create table if not exists db_web_data.track_log(
id string,
url string,
referer string,
keyword string,
type string,
guid string,
pageId string,
moduleId string,
linkId string,
attachedInfo string,
sessionId string,
trackerU string,
trackerType string,
ip string,
trackerSrc string,
cookie string,
orderCode string,
trackTime string,
endUserId string,
firstLink string,
sessionViewNo string,
productId string,
curMerchantId string,
provinceId string,
cityId string,
fee string,
edmActivity string,
edmEmail string,
edmJobId string,
ieVersion string,
platform string,
internalKeyword string,
resultSum string,
currentPage string,
linkPosition string,
buttonPosition string
)
partitioned by (date string,hour string) -- 分区表的分区字段以逗号分隔
row format delimited fields terminated by '\t';
导入分区表:
load data local inpath '/opt/modules/hive-0.13.1-cdh5.3.6/input/2015082819' into table track_log partition(date='20150828', hour='19');
查询分区中的数据:
select url from track_log where date='20150828' and hour='18';
从HDFS上传数据
*******HiveServer2(目的:使其他节点也可以操作Hive)
**配置:hive-site.xml
①hive.server2.thrift.port:10000
②hive.server2.thrift.bind.host:hadoop-senior01.atguigu.com
③hive.server2.long.polling.timeout:5000(去掉L)
**检查端口号
netstat -antp (netstat -antp | grep 10000)
**启动服务
bin/hive --service hiveserver2
***(用另一个客户端去连接)help命令
bin/beeline
beeline> !connect jdbc:hive2://hadoop-senior01.atguigu.com:10000
使这个客户端能够有权限调度mapreducer:hive-site.xml
hive.server2.enable.doAs --> flase