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 2018/04/28 06:08:31 UTC
zeppelin git commit: [ZEPPELIN-3377] Passing Z variables to JDBC
interpreter
Repository: zeppelin
Updated Branches:
refs/heads/master 9e18159d4 -> e65f73066
[ZEPPELIN-3377] Passing Z variables to JDBC interpreter
### What is this PR for?
This PR enables the interpolation of ZeppelinContext objects into the paragraph text of JDBC cells. It also introduces a new interpreter-level configuration parameter named _zeppelin.jdbc.interpolation_. This new parameter is _false_ by default, and must be set to _true_ to enable object interpolation. The default value of _false_ guarantees backward compatibility for users who are not aware of the new feature.
The implementation takes the same approach that was followed in [PR-2898](https://github.com/apache/zeppelin/pull/2898).
I have also taken the liberty to correct a preexisting error in the description of the use of Dynamic Forms in the associated documentation (_jdbc.md_).
### What type of PR is it?
[Feature]
### Todos
* [ ] - Task
### What is the Jira issue?
https://issues.apache.org/jira/browse/ZEPPELIN-3377
### How should this be tested?
CI Pass.
The code in this PR merely causes the JDBC interpreter to "opt-in" to the implementation already existing in the `Interpreter` base class - described in [PR-2898](https://github.com/apache/zeppelin/pull/2898). The unit tests necessary are already present in PR-2898
### Screenshots (if appropriate)
### Questions:
* Does the licenses files need update? No
* Is there breaking changes for older versions? No
* Does this needs documentation? Yes, documentation has been added to the file _jdbc.md_. I have also taken the liberty to correct a preexisting error in the description of the use of Dynamic Forms in the associated documentation.
Author: Sanjay Dasgupta <sa...@gmail.com>
Author: Sanjay Dasgupta <sa...@users.noreply.github.com>
Closes #2903 from sanjaydasgupta/zeppelin-3342-jdbc and squashes the following commits:
9947d36 [Sanjay Dasgupta] Expanded * imports to remove check-style errors
094d3ce [Sanjay Dasgupta] Reduced indentation to remove check-style errors
07561f5 [Sanjay Dasgupta] Revisions after Felix Cheung's review https://github.com/apache/zeppelin/pull/2903#pullrequestreview-110276872
df99ab0 [Sanjay Dasgupta] Revisions after Felix Cheung's review https://github.com/apache/zeppelin/pull/2903#pullrequestreview-110276872
315a9ad [Sanjay Dasgupta] Corrected use of rlike in SQL statement
eb9194d [Sanjay Dasgupta] ZEPPELIN-3377 Updates Initial Load
0f49867 [Sanjay Dasgupta] Merge branch 'master' of https://github.com/apache/zeppelin into zeppelin-3342-hdfs
a19e998 [Sanjay Dasgupta] Merge branch 'master' of https://github.com/apache/zeppelin into ZEPPELIN-1967
77738aa [Sanjay Dasgupta] Changes to comply with Felix Cheung's comment at https://github.com/apache/zeppelin/pull/2834#discussion_r176976263 and Jeff Zhang's subsequent clarification
5f8505b [Sanjay Dasgupta] Changes due to Felix Cheung's comments at https://github.com/apache/zeppelin/pull/2834#pullrequestreview-106738198
d600d86 [Sanjay Dasgupta] Merge branch 'master' of https://github.com/apache/zeppelin into ZEPPELIN-1967
cc3727f [Sanjay Dasgupta] Changes due the Jeff Zhang's comments at https://github.com/apache/zeppelin/pull/2834/files/1e2c87dd36dc091ca898baf8e9f178d6d1a5e600#r176930418
1e2c87d [Sanjay Dasgupta] Merge branch 'master' of https://github.com/apache/zeppelin into ZEPPELIN-1967
3dd3dd8 [Sanjay Dasgupta] Merge branch 'master' of https://github.com/apache/zeppelin into ZEPPELIN-1967
a1703b8 [Sanjay Dasgupta] Changes suggested in Felix Cheung's review https://github.com/apache/zeppelin/pull/2834#pullrequestreview-104805661
b7ddf6b [Sanjay Dasgupta] Implementing configuration (global enable/disable interpolation) following https://github.com/apache/zeppelin/pull/2834#issuecomment-373948398
5268803 [Sanjay Dasgupta] Merge branch 'master' of https://github.com/apache/zeppelin into ZEPPELIN-1967
1718e79 [Sanjay Dasgupta] Merge branch 'master' of https://github.com/apache/zeppelin into ZEPPELIN-1967
3b30ea2 [Sanjay Dasgupta] Reversing previous incorrect update
3beebce [Sanjay Dasgupta] Merge branch 'master' of https://github.com/apache/zeppelin into ZEPPELIN-1967
f43fd99 [Sanjay Dasgupta] Merge branch 'master' of https://github.com/apache/zeppelin into ZEPPELIN-1967
a3215fc [Sanjay Dasgupta] Merge branch 'master' of https://github.com/apache/zeppelin into ZEPPELIN-1967
ced295c [Sanjay Dasgupta] Merge branch 'master' of https://github.com/apache/zeppelin into ZEPPELIN-1967
b461c82 [Sanjay Dasgupta] Merge branch 'master' of https://github.com/apache/zeppelin into ZEPPELIN-1967
2868825 [Sanjay Dasgupta] ZEPPELIN-1967: Initial updates
Project: http://git-wip-us.apache.org/repos/asf/zeppelin/repo
Commit: http://git-wip-us.apache.org/repos/asf/zeppelin/commit/e65f7306
Tree: http://git-wip-us.apache.org/repos/asf/zeppelin/tree/e65f7306
Diff: http://git-wip-us.apache.org/repos/asf/zeppelin/diff/e65f7306
Branch: refs/heads/master
Commit: e65f73066c5741878177f45c283521868c775b7a
Parents: 9e18159
Author: Sanjay Dasgupta <sa...@gmail.com>
Authored: Wed Apr 11 16:19:42 2018 +0530
Committer: Felix Cheung <fe...@apache.org>
Committed: Fri Apr 27 23:08:13 2018 -0700
----------------------------------------------------------------------
docs/interpreter/jdbc.md | 28 ++-
.../apache/zeppelin/jdbc/JDBCInterpreter.java | 4 +-
.../src/main/resources/interpreter-setting.json | 7 +
.../jdbc/JDBCInterpreterInterpolationTest.java | 181 +++++++++++++++++++
4 files changed, 218 insertions(+), 2 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/zeppelin/blob/e65f7306/docs/interpreter/jdbc.md
----------------------------------------------------------------------
diff --git a/docs/interpreter/jdbc.md b/docs/interpreter/jdbc.md
index a6c28df..2f64da9 100644
--- a/docs/interpreter/jdbc.md
+++ b/docs/interpreter/jdbc.md
@@ -197,6 +197,10 @@ 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.interpolation</td>
+ <td>Enables ZeppelinContext variable interpolation into paragraph text. Default value is false.</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).
@@ -245,7 +249,7 @@ You can leverage [Zeppelin Dynamic Form](../usage/dynamic_form/intro.html) insid
%jdbc_interpreter_name
SELECT name, country, performer
FROM demo.performers
-WHERE name='{{"{{performer=Sheryl Crow|Doof|Fanfarlo|Los Paranoia"}}}}'
+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.
@@ -729,5 +733,27 @@ Before Adding one of the below dependencies, check the Phoenix version first.
[Maven Repository: org.apache.tajo:tajo-jdbc](https://mvnrepository.com/artifact/org.apache.tajo/tajo-jdbc)
+## Object Interpolation
+The JDBC interpreter also supports interpolation of `ZeppelinContext` objects into the paragraph text.
+The following example shows one use of this facility:
+
+####In Scala cell:
+```
+z.put("country_code", "KR")
+ // ...
+```
+
+####In later JDBC cell:
+```sql
+%jdbc_interpreter_name
+ select * from patents_list where
+ priority_country = '{country_code}' and filing_date like '2015-%'
+```
+
+Object interpolation is disabled by default, and can be enabled for all instances of the JDBC interpreter by
+setting the value of the property `zeppelin.jdbc.interpolation` to `true` (see _More Properties_ above).
+More details of this feature can be found in the Spark interpreter documentation under
+[Object Interpolation](spark.html#object-interpolation)
+
## Bug reporting
If you find a bug using JDBC interpreter, please create a [JIRA](https://issues.apache.org/jira/browse/ZEPPELIN) ticket.
http://git-wip-us.apache.org/repos/asf/zeppelin/blob/e65f7306/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 4bfaf04..7aae644 100644
--- a/jdbc/src/main/java/org/apache/zeppelin/jdbc/JDBCInterpreter.java
+++ b/jdbc/src/main/java/org/apache/zeppelin/jdbc/JDBCInterpreter.java
@@ -783,7 +783,9 @@ public class JDBCInterpreter extends KerberosInterpreter {
}
@Override
- public InterpreterResult interpret(String cmd, InterpreterContext contextInterpreter) {
+ public InterpreterResult interpret(String originalCmd, InterpreterContext contextInterpreter) {
+ String cmd = Boolean.parseBoolean(getProperty("zeppelin.jdbc.interpolation")) ?
+ interpolate(originalCmd, contextInterpreter.getResourcePool()) : originalCmd;
logger.debug("Run SQL command '{}'", cmd);
String propertyKey = getPropertyKey(cmd);
http://git-wip-us.apache.org/repos/asf/zeppelin/blob/e65f7306/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 2cda3ba..04262d9 100644
--- a/jdbc/src/main/resources/interpreter-setting.json
+++ b/jdbc/src/main/resources/interpreter-setting.json
@@ -107,6 +107,13 @@
"defaultValue": "",
"description": "Kerberos principal",
"type": "string"
+ },
+ "zeppelin.jdbc.interpolation": {
+ "envName": null,
+ "propertyName": "zeppelin.jdbc.interpolation",
+ "defaultValue": false,
+ "description": "Enable ZeppelinContext variable interpolation into paragraph text",
+ "type": "checkbox"
}
},
"editor": {
http://git-wip-us.apache.org/repos/asf/zeppelin/blob/e65f7306/jdbc/src/test/java/org/apache/zeppelin/jdbc/JDBCInterpreterInterpolationTest.java
----------------------------------------------------------------------
diff --git a/jdbc/src/test/java/org/apache/zeppelin/jdbc/JDBCInterpreterInterpolationTest.java b/jdbc/src/test/java/org/apache/zeppelin/jdbc/JDBCInterpreterInterpolationTest.java
new file mode 100644
index 0000000..fe7bc80
--- /dev/null
+++ b/jdbc/src/test/java/org/apache/zeppelin/jdbc/JDBCInterpreterInterpolationTest.java
@@ -0,0 +1,181 @@
+/**
+ * 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.jdbc;
+
+import com.mockrunner.jdbc.BasicJDBCTestCaseAdapter;
+import org.apache.zeppelin.interpreter.InterpreterContext;
+import org.apache.zeppelin.interpreter.InterpreterResult;
+import org.apache.zeppelin.resource.LocalResourcePool;
+import org.apache.zeppelin.resource.ResourcePool;
+import org.apache.zeppelin.user.AuthenticationInfo;
+import org.junit.Before;
+import org.junit.Test;
+
+import java.io.IOException;
+import java.nio.file.Files;
+import java.nio.file.Path;
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.Statement;
+import java.util.Properties;
+
+import static java.lang.String.format;
+import static org.junit.Assert.assertEquals;
+
+/**
+ * JDBC interpreter Z-variable interpolation unit tests.
+ */
+public class JDBCInterpreterInterpolationTest extends BasicJDBCTestCaseAdapter {
+
+ private static String jdbcConnection;
+ private InterpreterContext interpreterContext;
+ private ResourcePool resourcePool;
+
+ private String getJdbcConnection() throws IOException {
+ if (null == jdbcConnection) {
+ Path tmpDir = Files.createTempDirectory("h2-test-");
+ tmpDir.toFile().deleteOnExit();
+ jdbcConnection = format("jdbc:h2:%s", tmpDir);
+ }
+ return jdbcConnection;
+ }
+
+ @Before
+ public void setUp() throws Exception {
+ Class.forName("org.h2.Driver");
+ Connection connection = DriverManager.getConnection(getJdbcConnection());
+ Statement statement = connection.createStatement();
+ statement.execute(
+ "DROP TABLE IF EXISTS test_table; " +
+ "CREATE TABLE test_table(id varchar(255), name varchar(255));");
+
+ Statement insertStatement = connection.createStatement();
+ insertStatement.execute("insert into test_table(id, name) values " +
+ "('pro', 'processor')," +
+ "('mem', 'memory')," +
+ "('key', 'keyboard')," +
+ "('mou', 'mouse');");
+ resourcePool = new LocalResourcePool("JdbcInterpolationTest");
+
+ interpreterContext = new InterpreterContext("", "1", null, "", "",
+ new AuthenticationInfo("testUser"), null, null, null, null, resourcePool, null, null);
+ }
+
+ @Test
+ public void testEnableDisableProperty() throws IOException {
+ Properties properties = new Properties();
+ properties.setProperty("common.max_count", "1000");
+ properties.setProperty("common.max_retry", "3");
+ properties.setProperty("default.driver", "org.h2.Driver");
+ properties.setProperty("default.url", getJdbcConnection());
+ properties.setProperty("default.user", "");
+ properties.setProperty("default.password", "");
+
+ resourcePool.put("zid", "mem");
+ String sqlQuery = "select * from test_table where id = '{zid}'";
+
+ //
+ // Empty result expected because "zeppelin.jdbc.interpolation" is false by default ...
+ //
+ JDBCInterpreter t = new JDBCInterpreter(properties);
+ t.open();
+ InterpreterResult interpreterResult = t.interpret(sqlQuery, interpreterContext);
+ assertEquals(InterpreterResult.Code.SUCCESS, interpreterResult.code());
+ assertEquals(InterpreterResult.Type.TABLE, interpreterResult.message().get(0).getType());
+ assertEquals(1, interpreterResult.message().size());
+ assertEquals("ID\tNAME\n", interpreterResult.message().get(0).getData());
+
+ //
+ // 1 result expected because "zeppelin.jdbc.interpolation" set to "true" ...
+ //
+ properties.setProperty("zeppelin.jdbc.interpolation", "true");
+ t = new JDBCInterpreter(properties);
+ t.open();
+ interpreterResult = t.interpret(sqlQuery, interpreterContext);
+ assertEquals(InterpreterResult.Code.SUCCESS, interpreterResult.code());
+ assertEquals(InterpreterResult.Type.TABLE, interpreterResult.message().get(0).getType());
+ assertEquals(1, interpreterResult.message().size());
+ assertEquals("ID\tNAME\nmem\tmemory\n",
+ interpreterResult.message().get(0).getData());
+ }
+
+ @Test
+ public void testNormalQueryInterpolation() throws IOException {
+ Properties properties = new Properties();
+ properties.setProperty("common.max_count", "1000");
+ properties.setProperty("common.max_retry", "3");
+ properties.setProperty("default.driver", "org.h2.Driver");
+ properties.setProperty("default.url", getJdbcConnection());
+ properties.setProperty("default.user", "");
+ properties.setProperty("default.password", "");
+
+ properties.setProperty("zeppelin.jdbc.interpolation", "true");
+
+ JDBCInterpreter t = new JDBCInterpreter(properties);
+ t.open();
+
+ //
+ // Empty result expected because "kbd" is not defined ...
+ //
+ String sqlQuery = "select * from test_table where id = '{kbd}'";
+ InterpreterResult interpreterResult = t.interpret(sqlQuery, interpreterContext);
+ assertEquals(InterpreterResult.Code.SUCCESS, interpreterResult.code());
+ assertEquals(InterpreterResult.Type.TABLE, interpreterResult.message().get(0).getType());
+ assertEquals(1, interpreterResult.message().size());
+ assertEquals("ID\tNAME\n", interpreterResult.message().get(0).getData());
+
+ resourcePool.put("itemId", "key");
+
+ //
+ // 1 result expected because z-variable 'item' is 'key' ...
+ //
+ sqlQuery = "select * from test_table where id = '{itemId}'";
+ interpreterResult = t.interpret(sqlQuery, interpreterContext);
+ assertEquals(InterpreterResult.Code.SUCCESS, interpreterResult.code());
+ assertEquals(InterpreterResult.Type.TABLE, interpreterResult.message().get(0).getType());
+ assertEquals(1, interpreterResult.message().size());
+ assertEquals("ID\tNAME\nkey\tkeyboard\n",
+ interpreterResult.message().get(0).getData());
+ }
+
+ @Test
+ public void testEscapedInterpolationPattern() throws IOException {
+ Properties properties = new Properties();
+ properties.setProperty("common.max_count", "1000");
+ properties.setProperty("common.max_retry", "3");
+ properties.setProperty("default.driver", "org.h2.Driver");
+ properties.setProperty("default.url", getJdbcConnection());
+ properties.setProperty("default.user", "");
+ properties.setProperty("default.password", "");
+
+ properties.setProperty("zeppelin.jdbc.interpolation", "true");
+
+ JDBCInterpreter t = new JDBCInterpreter(properties);
+ t.open();
+
+ //
+ // 2 rows (keyboard and mouse) expected when searching names with 2 consecutive vowels ...
+ // The 'regexp' keyword is specific to H2 database
+ //
+ String sqlQuery = "select * from test_table where name regexp '[aeiou]{{2}}'";
+ InterpreterResult interpreterResult = t.interpret(sqlQuery, interpreterContext);
+ assertEquals(InterpreterResult.Code.SUCCESS, interpreterResult.code());
+ assertEquals(InterpreterResult.Type.TABLE, interpreterResult.message().get(0).getType());
+ assertEquals(1, interpreterResult.message().size());
+ assertEquals("ID\tNAME\nkey\tkeyboard\nmou\tmouse\n",
+ interpreterResult.message().get(0).getData());
+ }
+
+}