5.2 hive语句

朱雀 2022-05-23 06:23 126阅读 0赞

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

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

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**  ：

![20180604091522326][]

/\*\*

\* <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);

[20180604091522326]: /images/20220523/fdff1e1e413c46ceb7877f96fe5e6d34.png

5.2 hive语句

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 Hive 常用语句

相关 52条SQL语句性能优化策略

相关 Hive的查询语句

相关 Linux笔记（52）——多分支case语句

相关 Hive lateral view语句(翻译自Hive wiki)

相关 hive相关操作语句

相关 hive语句执行顺序

相关 5.2 hive语句

相关 hive lateral view语句

相关 Mysql语句性能优化52条