Hive几种使用方式
1.Hive交互shell bin/hive
2.Hive JDBC服务(参考java jdbc连接mysql)
3.hive启动为一个服务器,来对外提供服务
bin/hiveserver2
nohup bin/hiveserver2 1>/var/log/hiveserver.log 2>/var/log/hiveserver.err &
启动成功后,可以在别的节点上用beeline去连接
bin/beeline -u jdbc:hive2://mini1:10000 -n root
或者
bin/beeline
! connect jdbc:hive2://mini1:10000
4.Hive命令
hive -e ‘sql’
bin/hive -e 'select * from t_test'
Hive交互shell操作hive命令
若无法正常启动需要关闭安全模式:hdfs dfsadmin -safemode leave
hive文件路径下的bin路径内。运行hive文件。
展示所有的数据库 : show database;
创建名为test620的数据库:create database test620;
使用test620数据库:use test620;
展示数据库中的所有表:show tables;
单分区表的创建:create table day_table (id int, content string) partitioned by (dt int);
有格式的创建:
hive> create table par1(
> id int,
> name string
> )
> partitioned by (p string)
> row format delimited
> fields terminated by ','
> ;
OK
Time taken: 0.137 seconds
hive> show databases;
OK
default
Time taken: 0.024 seconds, Fetched: 1 row(s)
hive> create database test620;
OK
Time taken: 0.426 seconds
hive> show databases;
OK
default
test620
Time taken: 0.026 seconds, Fetched: 2 row(s)
hive> use test620;
OK
Time taken: 0.034 seconds
hive> show tables;
OK
Time taken: 0.101 seconds
hive> create table day_table (id int, content string) partitioned by (dt int);
OK
Time taken: 0.725 seconds
hive> load data local inpath '/opt/test/tes.txt' into table test620.day_table partition (dt=10);
Loading data to table test620.day_table partition (dt=10)
Partition test620.day_table{dt=10} stats: [numFiles=1, numRows=0, totalSize=51, rawDataSize=0]
OK
Time taken: 4.049 seconds
hive> select * from day_table;
OK
NULL NULL 10
NULL NULL 10
NULL NULL 10
NULL NULL 10
NULL NULL 10
NULL NULL 10
Time taken: 0.711 seconds, Fetched: 6 row(s)
hive> create table hour(id int, content string) partitioned by (dt int, hour int);
OK
Time taken: 0.173 seconds
hive> desc hour;
OK
id int
content string
dt int
hour int
# Partition Information
# col_name data_type comment
dt int
hour int
Time taken: 0.205 seconds, Fetched: 10 row(s)
hive> load data local inpath '/opt/test/t620.txt' into table hour partition (dt=10,hour=20)
;
Loading data to table test620.hour partition (dt=10, hour=20)
Partition test620.hour{dt=10, hour=20} stats: [numFiles=1, numRows=0, totalSize=45, rawDataSize=0]
OK
Time taken: 1.113 seconds
load命令导入数据
LOAD DATA命令,可分为LOAD DATA LOCAL INPATH和LOAD DATA INPATH。两者的区别在于LOCAL导入的是本地文件而不加LOCAL的导入的是HDFS文件
导入数据:
1、load data local inpath '/root/tes.txt' into table test.usr;
将本地的数据