<code>文末有好圖~/<code>
<code><dependency> <groupid>com.cloudera/<groupid> <artifactid>ImpalaJDBC4/<artifactid> <version>2.6.15/<version>/<dependency><dependency> <groupid>org.apache.thrift/<groupid> <artifactid>libfb303/<artifactid> <version>0.9.0/<version>/<dependency><dependency> <groupid>org.apache.thrift/<groupid> <artifactid>libthrift/<artifactid> <version>0.9.0/<version>/<dependency><dependency> <groupid>org.apache.hive/<groupid> <artifactid>hive-metastore/<artifactid> <version>3.1.0/<version>/<dependency><dependency> <groupid>org.apache.hive/<groupid> <artifactid>hive-service/<artifactid> <version>3.1.0/<version>/<dependency><dependency> <groupid>log4j/<groupid> <artifactid>log4j/<artifactid> <version>1.2.14/<version>/<dependency><dependency> <groupid>slf4j-api/<groupid> <artifactid>slf4j-api/<artifactid> <version>1.5.8/<version>/<dependency><dependency> <groupid>atom.core1/<groupid> <artifactid>slf4j-log4j12/<artifactid> <version>1.5.8/<version>/<dependency><dependency> <groupid>com.cloudera.impala.jdbc/<groupid> <artifactid>TCLIServiceClient/<artifactid> <version>2.5.41/<version>/<dependency><dependency> <groupid>ql/<groupid> <artifactid>ql/<artifactid> <version>1.0.0/<version>/<dependency>/<code>
可能你會需要這個：
<code><dependency><groupid>org.hibernate/<groupid><artifactid>hibernate-validator/<artifactid><version>4.1.0.Final/<version>/<dependency>/<code>
在conttoller裡的搞個test測試一下.因為之前我曾經遇到，在main方法裡執行沒什麼問題，但在啟動了jetty容器之後，就會報找不到驅動類的錯；
<code> static String JDBC_DRIVER = "com.cloudera.impala.jdbc4.Driver";// static String JDBC_DRIVER = "org.apache.hive.jdbc.HiveDriver";// static String JDBC_DRIVER = "org.apache.phoenix.jdbc.PhoenixDriver"; static String CONNECTION_URL = "jdbc:impala://192.168.13.75:21050/kd_orcl_sumpay";// static String CONNECTION_URL = "jdbc:hive2://192.168.13.75:21050/kd_orcl_sumpay;auth=noSasl";// static String CONNECTION_URL = "jdbc:phoenix:192.168.13.219,192.168.13.220,192.168.13.221:12181";/<code>
<code> Connection con = null; ResultSet rs = null; PreparedStatement ps = null; Statement stmt = null; try { Class.forName(JDBC_DRIVER); con = DriverManager.getConnection(CONNECTION_URL, "", ""); stmt = con.createStatement(); String sql00 = "select * from us_population"; String sql0 = "select * from ods_bus_scene_order_info limit 20"; rs = stmt.executeQuery(sql0); while (rs.next()) { System.out.println(rs.getString(1) + '\\t' + rs.getString(2)); } String sql = "create table pay_tradepay_info_tian1_kudu (TRADE_PAY_ID string , TRADE_NO string, ORDER_NO string, TOTAL_AMOUNT decimal, CURRENCY string, TRADE_TYPE int, TRADE_MODE string, TRADE_PAY_STATUS int, MEMO string, USER_ID string, PAYER_ACCOUNT string, PAYEE_ACCOUNT string, REFUND_SERIAL_NO string, PAY_SOURCE string, CREATE_TIME string, MODIFY_TIME string, SYSTEM_ENV_FLAG string, TRX_STATUS_CD string, ACCOUNT_REMARK string, SHARE_BENEFIT_FLAG int, SHARE_BENEFIT_EXP string, SHARE_BENEFIT_FEE string, SECURED_PAY string, PRO_CODE string, PRIMARY KEY (TRADE_PAY_ID) ) PARTITION BY HASH (TRADE_PAY_ID) PARTITIONS 2 STORED AS KUDU "; String sql1 = "insert into pay_tradepay_info_tian1_kudu select * from pay_tradepay_info_tian1"; Properties properties = new Properties(); properties.setProperty("driverClassName", "com.cloudera.impala.jdbc4.Driver"); properties.setProperty("url", "jdbc:impala://192.168.13.75:21050/kd_orcl_sumpay"); properties.setProperty("username", ""); properties.setProperty("password", ""); properties.setProperty("initialSize", "50"); properties.setProperty("maxActive", "100"); properties.setProperty("maxWait", "60000"); properties.setProperty("timeBetweenEvictionRunsMillis", "60000"); properties.setProperty("minEvictableIdleTimeMillis", "300000"); properties.setProperty("validationQuery", "SELECT 1"); properties.setProperty("testWhileIdle", "true"); properties.setProperty("testOnBorrow", "false"); properties.setProperty("testOnReturn", "false"); properties.setProperty("poolPreparedStatements", "false"); //當該值大於0時，啟用pool，poolPreparedStatements為true properties.setProperty("maxPoolPreparedStatementPerConnectionSize", "-1"); try { druidDataSource = (DruidDataSource) DruidDataSourceFactory.createDataSource(properties); //DruidDataSrouce工廠模式 } catch (Exception e) { } JdbcTemplate jdbcTemplate = new JdbcTemplate(druidDataSource); String masterSql = "select count(0) from ods_bus_scene_order_info limit 20"; Integer totalCount = jdbcTemplate.queryForObject(masterSql, Integer.class); System.out.println("count:"+totalCount);/<code>
impala shell
數據倉庫常常會分有多層，對ODS/DWD層做彙總聚合成模型時，可以在一個調度平臺上執行如下shell腳本即可.
SQL可以是多個一連串的SQL語句，以分號“；”做結束符；
調度平臺可選擇易觀大數據開源給Apatch的項目：dolphinscheduler
<code>source ~/.bash_profileimpala-shell -i $DW_IMPALA_SHELL_IP -f create_db_dws_tpl.sql/<code>
然後，來一個勁爆的圖片吧~