package com.chengshu.impala;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class ClouderaImpalaJdbcExample {
private static final String SQL_STATEMENT = "select count(*) as cnt from web_logs ";
//private static final String SQL_STATEMENT = "DESCRIBE test.t_a";
// set the impalad host
private static final String IMPALAD_HOST = "192.168.202.31";
// port 21050 is the default impalad JDBC port
private static final String IMPALAD_JDBC_PORT = "25003";
public static final String UTF_8 = "UTF-8";
private static final String CONNECTION_URL_PROPERTY = "connection.url";
private static final String JDBC_DRIVER_NAME_PROPERTY = "jdbc.driver.class.name";
private static String connectionUrl = "jdbc:hive2://" + IMPALAD_HOST + ':' + IMPALAD_JDBC_PORT + "/default;auth=noSasl";
private static String jdbcDriverName = "org.apache.hive.jdbc.HiveDriver";
public static void main(String[] args) throws IOException {
// if (args.length != 1) {
// System.out.println("Syntax: ClouderaImpalaJdbcExample \"<SQL_query>\"");
// System.exit(1);
// }
String sqlStatement = SQL_STATEMENT;
System.out.println("\n=============================================");
System.out.println("Cloudera Impala JDBC Example");
System.out.println("Using Connection URL: " + connectionUrl);
System.out.println("Running Query: " + sqlStatement);
// loadConfiguration();
Connection con = null;
try {
Class.forName(jdbcDriverName);
con = DriverManager.getConnection(connectionUrl);
Statement stmt = con.createStatement();
// stmt.execute("use default");
ResultSet rs = stmt.executeQuery(sqlStatement);
System.out.println("\n== Begin Query Results ======================");
// print the results to the console
while (rs.next()) {
// the example query returns one String column
System.out.println(rs.getString(1));
}
rs = stmt.executeQuery(sqlStatement);
while (rs.next()) {
// the example query returns one String column
System.out.println(rs.getString(1));
}
System.out.println("== End Query Results =======================\n\n");
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
con.close();
} catch (Exception e) {
// swallow
}
}
}
}
ClouderaImaplaJdbcExample.java
package com.chengshu.impala;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.datasource.SingleConnectionDataSource;
public class SpringJdbcImpala {
public static void main(String args[]) throws Exception {
SingleConnectionDataSource ds = new SingleConnectionDataSource();
ds.setDriverClassName("org.apache.hive.jdbc.HiveDriver");
ds.setUrl("jdbc:hive2://192.168.202.31:25003/default;auth=noSasl");
ds.setConnectionProperties(new Properties());
// ds.setUsername("sa");
// ds.setPassword("");
//
// ds.setDriverClassName("com.mysql.jdbc.Driver");
// ds.setUrl("jdbc:mysql://localhost:3306/spring");
// ds.setUsername("spring");
// ds.setPassword("t1cket");
//
// ds.setDriverClassName("oracle.jdbc.driver.OracleDriver");
// ds.setUrl("jdbc:oracle:thin:@fiji:1521:my10g");
// ds.setUsername("spring");
// ds.setPassword("t1cket");
JdbcTemplate impalaTemplate = new JdbcTemplate(ds);
impalaTemplate.setIgnoreWarnings(false);
impalaTemplate.execute("CREATE TABLE IF NOT EXISTS tmp_segment_tb (id STRING) STORED AS PARQUET");
impalaTemplate.execute("INSERT into tmp_segment_tb select 'String1' ");
int count = impalaTemplate.queryForInt("select count(*) from tmp_segment_tb where id = 'String1' ");
System.out.println(count);
// SqlRowSet res = impalaTemplate.queryForRowSet("select id from tmp_segment_tb");
// impalaTemplate.queryForObject("select * from web_logs", new RowMapper<ResultSet>(){
//
// public ResultSet mapRow(ResultSet rs, int rowNum)
// throws SQLException {
// System.out.println("--------" + rs.getString("id"));
// while(rs.next()){
// System.out.println("--------" + rs.getString("id"));
// }
// return rs;
// }
//
// });
ds.destroy();
}
}
SpringJdbcImpala.java
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>com.chengshu</groupId> <artifactId>impala</artifactId> <version>0.0.1-SNAPSHOT</version> <packaging>jar</packaging> <name>impala</name> <url>http://maven.apache.org</url> <properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> </properties> <dependencies> <dependency> <groupId>org.apache.hive</groupId> <artifactId>hive-jdbc</artifactId> <version>1.2.1</version> </dependency> <dependency> <groupId>org.apache.hadoop</groupId> <artifactId>hadoop-common</artifactId> <version>2.7.1</version> </dependency> <dependency> <groupId>org.apache.hadoop</groupId> <artifactId>hadoop-client</artifactId> <version>2.7.1</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>4.0.3.RELEASE</version> </dependency> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>3.8.1</version> <scope>test</scope> </dependency> </dependencies> <!-- <repositories> <repository> <id>cdh.repo</id> <url>https://repository.cloudera.com/artifactory/cloudera-repos</url> <name>Cloudera Repositories</name> <snapshots> <enabled>false</enabled> </snapshots> </repository> <repository> <id>cdh.snapshots.repo</id> <url>https://repository.cloudera.com/artifactory/libs-snapshot-local</url> <name>Cloudera Snapshots Repository</name> <snapshots> <enabled>true</enabled> </snapshots> <releases> <enabled>false</enabled> </releases> </repository> <repository> <id>central</id> <url>http://repo1.maven.org/maven2/</url> <releases> <enabled>true</enabled> </releases> <snapshots> <enabled>false</enabled> </snapshots> </repository> </repositories> --> </project>
pom.xml