From commits-return-4390-archive-asf-public=cust-asf.ponee.io@zeppelin.apache.org Sat Apr 28 08:13:37 2018 Return-Path: X-Original-To: archive-asf-public@cust-asf.ponee.io Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by mx-eu-01.ponee.io (Postfix) with SMTP id 063E5180649 for ; Sat, 28 Apr 2018 08:13:35 +0200 (CEST) Received: (qmail 3051 invoked by uid 500); 28 Apr 2018 06:13:34 -0000 Mailing-List: contact commits-help@zeppelin.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@zeppelin.apache.org Delivered-To: mailing list commits@zeppelin.apache.org Received: (qmail 3037 invoked by uid 99); 28 Apr 2018 06:13:34 -0000 Received: from git1-us-west.apache.org (HELO git1-us-west.apache.org) (140.211.11.23) by apache.org (qpsmtpd/0.29) with ESMTP; Sat, 28 Apr 2018 06:13:34 +0000 Received: by git1-us-west.apache.org (ASF Mail Server at git1-us-west.apache.org, from userid 33) id 04033E96B8; Sat, 28 Apr 2018 06:13:34 +0000 (UTC) Content-Type: text/plain; charset="us-ascii" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit From: felixcheung@apache.org To: commits@zeppelin.apache.org Message-Id: <548c2052e69745d69dd3f4735cbb497d@git.apache.org> X-Mailer: ASF-Git Admin Mailer Subject: zeppelin git commit: [ZEPPELIN-3377] Passing Z variables to JDBC interpreter Date: Sat, 28 Apr 2018 06:13:34 +0000 (UTC) Repository: zeppelin Updated Branches: refs/heads/branch-0.8 4d4e415a4 -> ddf0fd5f7 [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 Author: Sanjay Dasgupta 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 (cherry picked from commit e65f73066c5741878177f45c283521868c775b7a) Signed-off-by: Felix Cheung Project: http://git-wip-us.apache.org/repos/asf/zeppelin/repo Commit: http://git-wip-us.apache.org/repos/asf/zeppelin/commit/ddf0fd5f Tree: http://git-wip-us.apache.org/repos/asf/zeppelin/tree/ddf0fd5f Diff: http://git-wip-us.apache.org/repos/asf/zeppelin/diff/ddf0fd5f Branch: refs/heads/branch-0.8 Commit: ddf0fd5f7f5daa5d1ec9fd7de08d040ed6f55c60 Parents: 4d4e415 Author: Sanjay Dasgupta Authored: Wed Apr 11 16:19:42 2018 +0530 Committer: Felix Cheung Committed: Fri Apr 27 23:13:29 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/ddf0fd5f/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. default.jceks.credentialKey jceks credential key + + zeppelin.jdbc.interpolation + Enables ZeppelinContext variable interpolation into paragraph text. Default value is false. + 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/ddf0fd5f/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 21f6a62..7ccf749 100644 --- a/jdbc/src/main/java/org/apache/zeppelin/jdbc/JDBCInterpreter.java +++ b/jdbc/src/main/java/org/apache/zeppelin/jdbc/JDBCInterpreter.java @@ -786,7 +786,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/ddf0fd5f/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/ddf0fd5f/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()); + } + +}