Repository: zeppelin Updated Branches: refs/heads/master 0cb0f36ba -> cf131c868
[ZEPPELIN-2245] separate precode into JDBCInterpreter ### What is this PR for? Separate precode by prefix. Added the ability to set different precode for different data sources ### What type of PR is it? Improvement ### What is the Jira issue? https://issues.apache.org/jira/browse/ZEPPELIN-2245 ### How should this be tested? 1. Set properties ``` default.password 1 default.precode set search_path='test_path' default.url jdbc:postgresql://localhost:5432/ default.user postgres mysql.driver com.mysql.jdbc.Driver mysql.password 1 mysql.precode set v=12 mysql.url jdbc:mysql://localhost:3306/ mysql.user root ``` 2. Run `show search_path` 3. Run ``` %jdbc(mysql) select v ``` ### Questions: * Does the licenses files need update? no * Is there breaking changes for older versions? no * Does this needs documentation? no Author: Tinkoff DWH <tinkoff....@gmail.com> Closes #2121 from tinkoff-dwh/ZEPPELIN-2245 and squashes the following commits: 970c064 [Tinkoff DWH] [ZEPPELIN-2245] editing documentation a136a0e [Tinkoff DWH] [ZEPPELIN-2245] documentation for usage of precode f896ea8 [Tinkoff DWH] [ZEPPELIN-2245] separate precode into JDBCInterpreter Project: http://git-wip-us.apache.org/repos/asf/zeppelin/repo Commit: http://git-wip-us.apache.org/repos/asf/zeppelin/commit/cf131c86 Tree: http://git-wip-us.apache.org/repos/asf/zeppelin/tree/cf131c86 Diff: http://git-wip-us.apache.org/repos/asf/zeppelin/diff/cf131c86 Branch: refs/heads/master Commit: cf131c868072d575a693a09b79d67fb324fbe1bc Parents: 0cb0f36 Author: Tinkoff DWH <tinkoff....@gmail.com> Authored: Tue Mar 14 12:56:15 2017 +0500 Committer: Felix Cheung <felixche...@apache.org> Committed: Tue Mar 14 20:05:15 2017 -0700 ---------------------------------------------------------------------- docs/interpreter/jdbc.md | 78 +++++++++++++++++++- .../apache/zeppelin/jdbc/JDBCInterpreter.java | 10 ++- .../src/main/resources/interpreter-setting.json | 12 +-- .../zeppelin/jdbc/JDBCInterpreterTest.java | 30 ++++++-- 4 files changed, 111 insertions(+), 19 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/zeppelin/blob/cf131c86/docs/interpreter/jdbc.md ---------------------------------------------------------------------- diff --git a/docs/interpreter/jdbc.md b/docs/interpreter/jdbc.md index 75da51f..28403fc 100644 --- a/docs/interpreter/jdbc.md +++ b/docs/interpreter/jdbc.md @@ -118,6 +118,11 @@ The JDBC interpreter properties are defined by default like below. <td>gpadmin</td> <td>The JDBC user name</td> </tr> + <tr> + <td>default.precode</td> + <td></td> + <td>Some SQL which executes while opening connection</td> + </tr> </table> If you want to connect other databases such as `Mysql`, `Redshift` and `Hive`, you need to edit the property values. @@ -167,10 +172,6 @@ There are more JDBC interpreter properties you can specify like below. <td>default.jceks.credentialKey</td> <td>jceks credential key</td> </tr> - <tr> - <td>zeppelin.jdbc.precode</td> - <td>Some SQL which executes while opening connection</td> - </tr> </table> You can also add more properties by using this [method](http://docs.oracle.com/javase/7/docs/api/java/sql/DriverManager.html#getConnection%28java.lang.String,%20java.util.Properties%29). @@ -221,6 +222,75 @@ SELECT name, country, performer FROM demo.performers WHERE name='{{"{{performer=Sheryl Crow|Doof|Fanfarlo|Los Paranoia"}}}}' ``` +### Usage *precode* +You can set *precode* for each data source. Code runs once while opening the connection. + +##### Properties +An example settings of interpreter for the two data sources, each of which has its *precode* parameter. + +<table class="table-configuration"> + <tr> + <th>Property Name</th> + <th>Value</th> + </tr> + <tr> + <td>default.driver</td> + <td>org.postgresql.Driver</td> + </tr> + <tr> + <td>default.password</td> + <td>1</td> + </tr> + <tr> + <td>default.url</td> + <td>jdbc:postgresql://localhost:5432/</td> + </tr> + <tr> + <td>default.user</td> + <td>postgres</td> + </tr> + <tr> + <td>default.precode</td> + <td>set search_path='test_path'</td> + </tr> + <tr> + <td>mysql.driver</td> + <td>com.mysql.jdbc.Driver</td> + </tr> + <tr> + <td>mysql.password</td> + <td>1</td> + </tr> + <tr> + <td>mysql.url</td> + <td>jdbc:mysql://localhost:3306/</td> + </tr> + <tr> + <td>mysql.user</td> + <td>root</td> + </tr> + <tr> + <td>mysql.precode</td> + <td>set @v=12</td> + </tr> +</table> + +##### Usage +Test of execution *precode* for each data source. + +```sql +%jdbc +show search_path +``` +Returns value of `search_path` which is set in the *default.precode*. + + +```sql +%jdbc(mysql) +select @v +``` +Returns value of `v` which is set in the *mysql.precode*. + ## Examples Here are some examples you can refer to. Including the below connectors, you can connect every databases as long as it can be configured with it's JDBC driver. http://git-wip-us.apache.org/repos/asf/zeppelin/blob/cf131c86/jdbc/src/main/java/org/apache/zeppelin/jdbc/JDBCInterpreter.java ---------------------------------------------------------------------- diff --git a/jdbc/src/main/java/org/apache/zeppelin/jdbc/JDBCInterpreter.java b/jdbc/src/main/java/org/apache/zeppelin/jdbc/JDBCInterpreter.java index 0b25a23..f83caf9 100644 --- a/jdbc/src/main/java/org/apache/zeppelin/jdbc/JDBCInterpreter.java +++ b/jdbc/src/main/java/org/apache/zeppelin/jdbc/JDBCInterpreter.java @@ -101,9 +101,10 @@ public class JDBCInterpreter extends Interpreter { static final String URL_KEY = "url"; static final String USER_KEY = "user"; static final String PASSWORD_KEY = "password"; + static final String PRECODE_KEY = "precode"; static final String JDBC_JCEKS_FILE = "jceks.file"; static final String JDBC_JCEKS_CREDENTIAL_KEY = "jceks.credentialKey"; - static final String ZEPPELIN_JDBC_PRECODE_KEY = "zeppelin.jdbc.precode"; + static final String PRECODE_KEY_TEMPLATE = "%s.precode"; static final String DOT = "."; private static final char WHITESPACE = ' '; @@ -118,6 +119,7 @@ public class JDBCInterpreter extends Interpreter { static final String DEFAULT_URL = DEFAULT_KEY + DOT + URL_KEY; static final String DEFAULT_USER = DEFAULT_KEY + DOT + USER_KEY; static final String DEFAULT_PASSWORD = DEFAULT_KEY + DOT + PASSWORD_KEY; + static final String DEFAULT_PRECODE = DEFAULT_KEY + DOT + PRECODE_KEY; static final String EMPTY_COLUMN_VALUE = ""; @@ -342,7 +344,7 @@ public class JDBCInterpreter extends Interpreter { if (!getJDBCConfiguration(user).isConnectionInDBDriverPool(propertyKey)) { createConnectionPool(url, user, propertyKey, properties); try (Connection connection = DriverManager.getConnection(jdbcDriver)) { - executePrecode(connection); + executePrecode(connection, propertyKey); } } return DriverManager.getConnection(jdbcDriver); @@ -548,8 +550,8 @@ public class JDBCInterpreter extends Interpreter { return queries; } - private void executePrecode(Connection connection) throws SQLException { - String precode = getProperty(ZEPPELIN_JDBC_PRECODE_KEY); + private void executePrecode(Connection connection, String propertyKey) throws SQLException { + String precode = getProperty(String.format(PRECODE_KEY_TEMPLATE, propertyKey)); if (StringUtils.isNotBlank(precode)) { precode = StringUtils.trim(precode); logger.info("Run SQL precode '{}'", precode); http://git-wip-us.apache.org/repos/asf/zeppelin/blob/cf131c86/jdbc/src/main/resources/interpreter-setting.json ---------------------------------------------------------------------- diff --git a/jdbc/src/main/resources/interpreter-setting.json b/jdbc/src/main/resources/interpreter-setting.json index 6134243..322ea5a 100644 --- a/jdbc/src/main/resources/interpreter-setting.json +++ b/jdbc/src/main/resources/interpreter-setting.json @@ -28,6 +28,12 @@ "defaultValue": "org.postgresql.Driver", "description": "JDBC Driver Name" }, + "default.precode": { + "envName": null, + "propertyName": "zeppelin.jdbc.precode", + "defaultValue": "", + "description": "SQL which executes while opening connection" + }, "common.max_count": { "envName": null, "propertyName": "common.max_count", @@ -63,12 +69,6 @@ "propertyName": "zeppelin.jdbc.principal", "defaultValue": "", "description": "Kerberos principal" - }, - "zeppelin.jdbc.precode": { - "envName": null, - "propertyName": "zeppelin.jdbc.precode", - "defaultValue": "", - "description": "SQL which executes while opening connection" } }, "editor": { http://git-wip-us.apache.org/repos/asf/zeppelin/blob/cf131c86/jdbc/src/test/java/org/apache/zeppelin/jdbc/JDBCInterpreterTest.java ---------------------------------------------------------------------- diff --git a/jdbc/src/test/java/org/apache/zeppelin/jdbc/JDBCInterpreterTest.java b/jdbc/src/test/java/org/apache/zeppelin/jdbc/JDBCInterpreterTest.java index 197c368..2e7e1a5 100644 --- a/jdbc/src/test/java/org/apache/zeppelin/jdbc/JDBCInterpreterTest.java +++ b/jdbc/src/test/java/org/apache/zeppelin/jdbc/JDBCInterpreterTest.java @@ -19,6 +19,8 @@ import static org.apache.zeppelin.jdbc.JDBCInterpreter.DEFAULT_DRIVER; import static org.apache.zeppelin.jdbc.JDBCInterpreter.DEFAULT_PASSWORD; import static org.apache.zeppelin.jdbc.JDBCInterpreter.DEFAULT_USER; import static org.apache.zeppelin.jdbc.JDBCInterpreter.DEFAULT_URL; +import static org.apache.zeppelin.jdbc.JDBCInterpreter.DEFAULT_PRECODE; +import static org.apache.zeppelin.jdbc.JDBCInterpreter.PRECODE_KEY_TEMPLATE; import static org.apache.zeppelin.jdbc.JDBCInterpreter.COMMON_MAX_LINE; import static org.junit.Assert.*; @@ -44,8 +46,6 @@ import org.junit.Test; import com.mockrunner.jdbc.BasicJDBCTestCaseAdapter; -import static org.apache.zeppelin.jdbc.JDBCInterpreter.ZEPPELIN_JDBC_PRECODE_KEY; - /** * JDBC interpreter unit tests */ @@ -397,7 +397,7 @@ public class JDBCInterpreterTest extends BasicJDBCTestCaseAdapter { properties.setProperty("default.url", getJdbcConnection()); properties.setProperty("default.user", ""); properties.setProperty("default.password", ""); - properties.setProperty(ZEPPELIN_JDBC_PRECODE_KEY, "SET @testVariable=1"); + properties.setProperty(DEFAULT_PRECODE, "SET @testVariable=1"); JDBCInterpreter jdbcInterpreter = new JDBCInterpreter(properties); jdbcInterpreter.open(); @@ -417,7 +417,7 @@ public class JDBCInterpreterTest extends BasicJDBCTestCaseAdapter { properties.setProperty("default.url", getJdbcConnection()); properties.setProperty("default.user", ""); properties.setProperty("default.password", ""); - properties.setProperty(ZEPPELIN_JDBC_PRECODE_KEY, "incorrect command"); + properties.setProperty(DEFAULT_PRECODE, "incorrect command"); JDBCInterpreter jdbcInterpreter = new JDBCInterpreter(properties); jdbcInterpreter.open(); @@ -428,4 +428,24 @@ public class JDBCInterpreterTest extends BasicJDBCTestCaseAdapter { assertEquals(InterpreterResult.Code.ERROR, interpreterResult.code()); assertEquals(InterpreterResult.Type.TEXT, interpreterResult.message().get(0).getType()); } - } + + @Test + public void testPrecodeWithAnotherPrefix() throws SQLException, IOException { + Properties properties = new Properties(); + properties.setProperty("anotherPrefix.driver", "org.h2.Driver"); + properties.setProperty("anotherPrefix.url", getJdbcConnection()); + properties.setProperty("anotherPrefix.user", ""); + properties.setProperty("anotherPrefix.password", ""); + properties.setProperty(String.format(PRECODE_KEY_TEMPLATE, "anotherPrefix"), "SET @testVariable=2"); + JDBCInterpreter jdbcInterpreter = new JDBCInterpreter(properties); + jdbcInterpreter.open(); + + String sqlQuery = "(anotherPrefix) select @testVariable"; + + InterpreterResult interpreterResult = jdbcInterpreter.interpret(sqlQuery, interpreterContext); + + assertEquals(InterpreterResult.Code.SUCCESS, interpreterResult.code()); + assertEquals(InterpreterResult.Type.TABLE, interpreterResult.message().get(0).getType()); + assertEquals("@TESTVARIABLE\n2\n", interpreterResult.message().get(0).getData()); + } +}
