You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@zeppelin.apache.org by fe...@apache.org on 2017/03/15 03:05:19 UTC
zeppelin git commit: [ZEPPELIN-2245] separate precode into
JDBCInterpreter
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 <ti...@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 <ti...@gmail.com>
Authored: Tue Mar 14 12:56:15 2017 +0500
Committer: Felix Cheung <fe...@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());
+ }
+}