使用Java连接Hive2

╰半夏微凉° 2022-12-07 12:49 182阅读 0赞

先在Linux中启动hiveserver2，  
进入beeline 新窗口打开：  
beeline -u jdbc:hive2://192.168.56.100:10000 -n root，IP是虚拟机里的URL。  
打开IDEA，首先还是导入依赖。

<dependency>
        <groupId>org.apache.hive</groupId>
        <artifactId>hive-jdbc</artifactId>
        <version>1.1.0</version>
    </dependency>
    <dependency>
        <groupId>org.apache.hadoop</groupId>
        <artifactId>hadoop-common</artifactId>
        <version>2.6.0</version>
    </dependency>
    <dependency>
         <groupId>org.apache.hive</groupId>
         <artifactId>hive-exec</artifactId>
         <version>1.1.0</version>
    </dependency>

public class HiveToCon { 
    
        private static String driverName = "org.apache.hive.jdbc.HiveDriver";
    
    
        private static Connection con = null;
        private static Statement state = null;
        private static ResultSet rst = null;
    
    
    @Before
     public void init() throws ClassNotFoundException, SQLException { 
    	Class.forName("org.apache.hive.jdbc.HiveDriver");
            con = DriverManager.getConnection("jdbc:hive2://192.168.56.100:10000/mydemo","","");
         state = con.createStatement();
         }
    //创建数据库
    @Test
        public void createDb() throws SQLException { 
        	state.execute("create database test");
        	}
        	
        // 查询数据库
        @Test
        public void showtDb() throws SQLException { 
    	 rst = state.executeQuery("show databases");
            while (rst.next()) { 
                System.out.println(rst.getString(1));
            }
        }
    
    //删除数据库
    @Test
        public void deleDb() throws SQLException { 
         state.execute("drop database if exists test");
         }
       
    // 查询表数据
    @Test
        public void selectTab() throws SQLException { 
            rst = state.executeQuery(("select * from userinfos limit 3");
       
       while (rst.next()){ 
            System.out.println(rst.getInt("userid")+":"+rst.getString("username"));
            }
         }
     // 创建表
        @Test
        public void createTab() throws SQLException { 
    
            state.execute("create table if not exists userinfos ( " +
                    "userid int , " +
                    "username string)" +                
                    "row format delimited " +
                    "fields terminated by ',' " +//字段与字段之间的分隔符
                    "collection items terminated by ':'" +//一个字段各个item的分隔符
                    "lines terminated by '\n' ");//行分隔符
        }
    
      // 查看表结构
        @Test
        public void descTab() throws SQLException { 
            rst= state.executeQuery("desc userinfos");
            while (rst.next()) { 
                System.out.println(rst.getString(1) + "\t" + rst.getString(2));
            }
        }
     
     // 加载数据
        @Test
        public void loadData() throws SQLException { 
            String infile = " '/opt/userinfos' ";
            state.execute("load data local inpath " + infile + "overwrite into table userinfos");
        }
    
    // 统计查询次数
        @Test
        public void countData() throws SQLException { 
            rst = state.executeQuery("select count(1) from userinfos");
            while (rst.next()) { 
                System.out.println(rst.getInt(1));
            }
        }
    
    //静态分区
    
    //创建分区格式表
        @Test
        public void creatPartab() throws SQLException { 
            state.execute("create table if not exists emp (" +
                    "name string ," +
                    "salary int ," +
                    "subordinate array<string> ," +
                    "deductions map<string,float> ," +
                    "address struct<street:string,city:string>) " +
                    "partitioned by (city string,street string) " +
                    "row format delimited " +
                    "fields terminated by '\t' " +
                    "collection items terminated by ',' " +
                    "map keys terminated by ':' " +
                    "lines terminated by '\n' " +
                    "stored as textfile");
        }
    
        //添加分区表
        @Test
        public void addPartition() throws SQLException { 
            state.execute("alter table emp add partition(city='nanjing',street='yuhualu')");
        }
       
        //查看分区表信息
        @Test
        public void showPartition() throws SQLException { 
            //rst=state.executeQuery("select * from emp");
            rst = state.executeQuery("show partitions emp");
            while (rst.next()) { 
                System.out.println(rst.getString(1));
            }
        }
       
        //插入数据
        @Test
        public void loadParData() throws SQLException { 
            String filepath = " '/opt/emp' ";
            state.execute("load data local inpath " + filepath + " overwrite into table emp partition (city='nanjing',street='yuhualu')");
        }
        
        //删除分区表
        @Test
        public void dropPartition() throws SQLException { 
            state.execute("alter table employees drop partition (city='nanjing',street='yuhualu')");
            /* * * 1、保护分区防止被删除 alter table emp partition (city='nanjing',street='yuhualu') enable no_drop 2、保护分区防止被查询 alter table emp partition (city='nanjing',street='yuhualu') enable offline 3、允许区删除和查询 alter table emp partition (city='nanjing',street='yuhualu') disable no_drop alter table emp partition (city='nanjing',street='yuhualu') disable offline * */
        }
        
        
         //动态分区
        
       
        //当需要一次插入多个分区的数据时，可以使用动态分区，根据查询得到的数据动态分配到分区里。
        // 动态分区与静态分区的区别就是不指定分区目录，由hive根据实际的数据选择插入到哪一个分区。
        //
        //set hive.exec.dynamic.partition=true; 启动动态分区功能
        //set hive.exec.dynamic.partition.mode=nonstrict 分区模式，默认nostrict
        //set hive.exec.max.dynamic.partitions=1000 最大动态分区数,默认1000
    
    //创建分区格式表
        @Test
        public void creatPartab1() throws SQLException { 
            state.execute("create table if not exists emp1 (" +
                            "name string ," +
                            "salary int ," +
                            "subordinate array<string> ," +
                            "deductions map<string,float> ," +
                            "address struct<street:string,city:string>) " +
                            "partitioned by (city string,street string) " +
                            "row format delimited " +
                            "fields terminated by '\t' " +
                            "collection items terminated by ',' " +
                            "map keys terminated by ':' " +
                            "lines terminated by '\n' " +
                            "stored as textfile");
        }
         
         //靠查询到的数据来分区
        @Test
        public void loadPartitionData() throws SQLException { 
            state.execute("insert overwrite table emp1 partition (city='nanjing',street) " +
                    "select name,salary,subordinate,deductions,address,address.street from emp");
        }
        
        // 释放资源
        @After
        public void destory() throws SQLException { 
            if (rst != null) state.close();
            if (state != null) state.close();
            if (con != null) con.close();
        }
    }