Java tutorial
/** * Licensed to the Apache Software Foundation (ASF) under one * or more contributor license agreements. See the NOTICE file * distributed with this work for additional information * regarding copyright ownership. The ASF licenses this file * to you under the Apache License, Version 2.0 (the * "License"); you may not use this file except in compliance * with the License. You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. */ package org.apache.zeppelin.phoenix; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Statement; import java.util.List; import java.util.Properties; import org.apache.commons.lang.StringUtils; import org.apache.zeppelin.interpreter.Interpreter; import org.apache.zeppelin.interpreter.InterpreterContext; import org.apache.zeppelin.interpreter.InterpreterPropertyBuilder; import org.apache.zeppelin.interpreter.InterpreterResult; import org.apache.zeppelin.interpreter.InterpreterResult.Code; import org.apache.zeppelin.scheduler.Scheduler; import org.apache.zeppelin.scheduler.SchedulerFactory; import org.slf4j.Logger; import org.slf4j.LoggerFactory; /** * Phoenix interpreter for Zeppelin. */ public class PhoenixInterpreter extends Interpreter { Logger logger = LoggerFactory.getLogger(PhoenixInterpreter.class); private static final String EXPLAIN_PREDICATE = "EXPLAIN "; private static final String UPDATE_HEADER = "UPDATES "; private static final String WS = " "; private static final String NEWLINE = "\n"; private static final String TAB = "\t"; private static final String TABLE_MAGIC_TAG = "%table "; static final String PHOENIX_JDBC_URL = "phoenix.jdbc.url"; static final String PHOENIX_JDBC_USER = "phoenix.user"; static final String PHOENIX_JDBC_PASSWORD = "phoenix.password"; static final String PHOENIX_MAX_RESULT = "phoenix.max.result"; static final String PHOENIX_JDBC_DRIVER_NAME = "phoenix.driver.name"; static final String DEFAULT_JDBC_URL = "jdbc:phoenix:localhost:2181:/hbase-unsecure"; static final String DEFAULT_JDBC_USER = ""; static final String DEFAULT_JDBC_PASSWORD = ""; static final String DEFAULT_MAX_RESULT = "1000"; static final String DEFAULT_JDBC_DRIVER_NAME = "org.apache.phoenix.jdbc.PhoenixDriver"; private Connection jdbcConnection; private Statement currentStatement; private Exception exceptionOnConnect; private int maxResult; static { Interpreter.register("sql", "phoenix", PhoenixInterpreter.class.getName(), new InterpreterPropertyBuilder() .add(PHOENIX_JDBC_URL, DEFAULT_JDBC_URL, "Phoenix JDBC connection string") .add(PHOENIX_JDBC_USER, DEFAULT_JDBC_USER, "The Phoenix user") .add(PHOENIX_JDBC_PASSWORD, DEFAULT_JDBC_PASSWORD, "The password for the Phoenix user") .add(PHOENIX_MAX_RESULT, DEFAULT_MAX_RESULT, "Max number of SQL results to display.") .add(PHOENIX_JDBC_DRIVER_NAME, DEFAULT_JDBC_DRIVER_NAME, "Phoenix Driver classname.").build()); } public PhoenixInterpreter(Properties property) { super(property); } @Override public void open() { logger.info("Jdbc open connection called!"); close(); try { Class.forName(getProperty(PHOENIX_JDBC_DRIVER_NAME)); maxResult = Integer.valueOf(getProperty(PHOENIX_MAX_RESULT)); jdbcConnection = DriverManager.getConnection(getProperty(PHOENIX_JDBC_URL), getProperty(PHOENIX_JDBC_USER), getProperty(PHOENIX_JDBC_PASSWORD)); exceptionOnConnect = null; logger.info("Successfully created Jdbc connection"); } catch (ClassNotFoundException | SQLException e) { logger.error("Cannot open connection", e); exceptionOnConnect = e; } } @Override public void close() { logger.info("Jdbc close connection called!"); try { if (getJdbcConnection() != null) { getJdbcConnection().close(); } } catch (SQLException e) { logger.error("Cannot close connection", e); } finally { exceptionOnConnect = null; } } private String clean(boolean isExplain, String str) { return (isExplain || str == null) ? str : str.replace(TAB, WS).replace(NEWLINE, WS); } private InterpreterResult executeSql(String sql) { try { if (exceptionOnConnect != null) { return new InterpreterResult(Code.ERROR, exceptionOnConnect.getMessage()); } currentStatement = getJdbcConnection().createStatement(); boolean isExplain = StringUtils.containsIgnoreCase(sql, EXPLAIN_PREDICATE); StringBuilder msg = (isExplain) ? new StringBuilder() : new StringBuilder(TABLE_MAGIC_TAG); ResultSet res = null; try { boolean hasResult = currentStatement.execute(sql); if (hasResult) { //If query had results res = currentStatement.getResultSet(); //Append column names ResultSetMetaData md = res.getMetaData(); String row = clean(isExplain, md.getColumnName(1)); for (int i = 2; i < md.getColumnCount() + 1; i++) row += TAB + clean(isExplain, md.getColumnName(i)); msg.append(row + NEWLINE); //Append rows int rowCount = 0; while (res.next() && rowCount < getMaxResult()) { row = clean(isExplain, res.getString(1)); for (int i = 2; i < md.getColumnCount() + 1; i++) row += TAB + clean(isExplain, res.getString(i)); msg.append(row + NEWLINE); rowCount++; } } else { // May have been upsert or DDL msg.append(UPDATE_HEADER + NEWLINE + "Rows affected: " + currentStatement.getUpdateCount() + NEWLINE); } } finally { try { if (res != null) res.close(); getJdbcConnection().commit(); currentStatement.close(); } finally { currentStatement = null; } } return new InterpreterResult(Code.SUCCESS, msg.toString()); } catch (SQLException ex) { logger.error("Can not run " + sql, ex); return new InterpreterResult(Code.ERROR, ex.getMessage()); } } @Override public InterpreterResult interpret(String cmd, InterpreterContext contextInterpreter) { logger.info("Run SQL command '" + cmd + "'"); return executeSql(cmd); } @Override public void cancel(InterpreterContext context) { if (currentStatement != null) { try { currentStatement.cancel(); } catch (SQLException ex) { } finally { currentStatement = null; } } } @Override public FormType getFormType() { return FormType.SIMPLE; } @Override public int getProgress(InterpreterContext context) { return 0; } @Override public Scheduler getScheduler() { return SchedulerFactory.singleton() .createOrGetFIFOScheduler(PhoenixInterpreter.class.getName() + this.hashCode()); } @Override public List<String> completion(String buf, int cursor) { return null; } public Connection getJdbcConnection() { return jdbcConnection; } public int getMaxResult() { return maxResult; } }