5.2 hive语句

内表删除表或者分区元数据和数据都删了

外表删除表元数据删除，数据保留

show databases; 查看所有库

hive -f create.sql 在hive里执行脚本

create database traffic; 创建数据库

use traffic; 切换数据库

show tables; 查看表

创建表(内部表)：

create table psn1 (

id int, name string, sex string, likes ARRAY <string>,

address MAP <string, string>

)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY ','

COLLECTION ITEMS TERMINATED BY '-'

MAP KEYS TERMINATED BY ':';

创建表(外部表)：

create EXTERNAL table psn2 (

id int, name string, sex string, likes ARRAY <string>,

address MAP <string, string>

)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY ','

COLLECTION ITEMS TERMINATED BY '-'

MAP KEYS TERMINATED BY ':'

LOCATION '/home/psn2'; --指定数据路径

删除表：

DROP TABLE table_name;

导入数据(加上local，inpath的路径就是本地路径，否则是hdfs的路径)：

load data local inpath '/home/data' into table psn1;

data的数据格式：

1,zhangsan1,man,lol-book,shanghai:songjiangqu-beijing:xisanqi

2,zhangsan2,girl,lol-book,shanghai:songjiangqu-beijing:xisanqi

3,lisi,weizhi,lol-pnp-kanpian,huoxing:xxx-yueqiu:yy

show tables; 查看所有表

创建分区：

create table psn3 (

id int, name string, sex string, likes ARRAY <string>,

address MAP <string, string>

)

PARTITIONED BY (age string)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY ','

COLLECTION ITEMS TERMINATED BY '-'

MAP KEYS TERMINATED BY ':';

ALTER TABLE psn3 ADD PARTITION (age=30); 在表psn3 中添加一个年纪30的分区

ALTER TABLE psn3 drop PARTITION (age=1); 删除age=1de 分区

创建多重分区：

create table psn4 (

id int, name string, sex string, likes ARRAY <string>,

address MAP <string, string>

)

PARTITIONED BY (age string, shengao int)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY ','

COLLECTION ITEMS TERMINATED BY '-'

MAP KEYS TERMINATED BY ':';

往多重分区中导入数据

load data local inpath '/home/data' into table psn4 partition (age=1,shengao=2);

ALTER TABLE psn4 ADD PARTITION (age=30, shengao=10); 添加age=30,shengao=10的分区

ALTER TABLE psn4 drop PARTITION (shengao=3); 删除所有shengao=3的分区

ALTER TABLE psn4 drop PARTITION (age=1);删除age=1的分区

往分区中导入数据：

LOAD DATA local INPATH '/home/data' INTO TABLE psn4 partition(age=1,shengao=2);

show partitions psn4 ; 查看分区

创建表的例外几种方式：

create table psn5 like psn4;

create table psn6 as select id, name, sex from psn3;

create table jg (num int);

from psn5 insert into jg select count(*);

---

hive整合jdbc:

整合JDBC要是测试连接需要启动 hiveserver2  ：

/**

* <p>内容描述：操作hive</p>

* @author lvjie

* @date 2017年7月7日 上午11:54:43

*/

public class UseHive {

//hive连接驱动

private static String driverName = "org.apache.hive.jdbc.HiveDriver";

/**

* 创建表

* @param stmt

*/

public void addTab(Statement stmt){

String tableName = "hellohive";

try {

stmt.execute("drop table if exists " + tableName);

stmt.execute("create table " + tableName +" (key int, value string)");

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

System.out.println("Create table success!");

}

/**

* 查询表

* @param stmt

*/

public void findTab(Statement stmt){

String tableName = "hellohive";

String sql = "select count(1) from " + tableName + "";

//String sql = "select * from " + tableName;

ResultSet res;

try {

res = stmt.executeQuery(sql);

if (res.next()) {

System.out.println(res.getString(1));

//System.out.println(String.valueOf(res.getInt(1)) + "\t" + res.getString(2));

}

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

public static void main(String[] args) throws SQLException {

try {

Class.forName(driverName);

} catch (ClassNotFoundException e) {

// TODO Auto-generated catch block

e.printStackTrace();

System.exit(1);

}

Connection con = DriverManager.getConnection(

"jdbc:hive2://node2:10000/default", "root", "password");//default是库名

Statement stmt = con.createStatement();

UseHive use = new UseHive();

//use.addTab(stmt);

use.findTab(stmt);

}

}