Repository: zeppelin
Updated Branches:
  refs/heads/branch-0.8 4635259fe -> 1795ed427


[ZEPPELIN-2679] JDBC. precode for session

We have a precode for interpreter (all interpreters) which executes once after 
start interpreter. With it we can create shared code that will be available in 
the paragraph, create tables in the database and etc.
This precode for jdbc only which executes before run paragraph in same session 
(sql). In this precode we can declare session variables that will be available 
when you run paragraph (any sql which will be executed before the paragraph). 
For example `set application_name='#{user};`
Previous discussion in #2442

Feature

https://issues.apache.org/jira/browse/ZEPPELIN-2679

(jdbc interpreter, postgres)
1. set property **default.statementPrecode** `set application_name='#{user}';`
2. run paragraph `select current_setting('application_name');`

* 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>
Author: mebelousov <mebelou...@ya.ru>
Author: Tinkoff DWH <tinkoff....@gmail.com>
Author: Renjith Kamath <rkam...@apache.org>
Author: isys.mreshetov <m.reshe...@i-sys.ru>

Closes #2920 from mebelousov/ZEPPELIN-2679 and squashes the following commits:

d610805 [mebelousov] Revert "ZEPPELIN-3406 Travis build failure due to npm 
error"
11cbc44 [Renjith Kamath] ZEPPELIN-3406 Travis build failure due to npm error
11f89a5 [mebelousov] ZEPPELIN-2679 Small fix in docs
e9d36d2 [mebelousov] Merge remote-tracking branch 'apache/master' into 
ZEPPELIN-2679
00aeabf [tinkoff-dwh] ZEPPELIN-2679 fix checkstyle
f7529ca [tinkoff-dwh] Merge remote-tracking branch 'upstream/master' into 
ZEPPELIN-2679
07b41c5 [tinkoff-dwh] ZEPPELIN-2679 rename test methods
cd0d6ed [tinkoff-dwh] Merge remote-tracking branch 'upstream/master' into 
ZEPPELIN-2679
7bba0db [tinkoff-dwh] ZEPPELIN-2679 rename session to statement
719d0a2 [isys.mreshetov] ZEPPELIN-2679 docs
ae7a0d6 [Tinkoff DWH] [ZEPPELIN-2679] tests
bd3d809 [Tinkoff DWH] [ZEPPELIN-2679] jdbc. precode for session

(cherry picked from commit edf3c2589b2d1ccc080e2b9b30dbbd9c53e9db54)
Signed-off-by: Jeff Zhang <zjf...@apache.org>


Project: http://git-wip-us.apache.org/repos/asf/zeppelin/repo
Commit: http://git-wip-us.apache.org/repos/asf/zeppelin/commit/1795ed42
Tree: http://git-wip-us.apache.org/repos/asf/zeppelin/tree/1795ed42
Diff: http://git-wip-us.apache.org/repos/asf/zeppelin/diff/1795ed42

Branch: refs/heads/branch-0.8
Commit: 1795ed42781ca16fb7d5ed34e89eb37be7cb00f3
Parents: 4635259
Author: tinkoff-dwh <tinkoff....@gmail.com>
Authored: Sun Apr 15 21:16:55 2018 +0300
Committer: Jeff Zhang <zjf...@apache.org>
Committed: Mon Apr 16 16:50:58 2018 +0800

----------------------------------------------------------------------
 docs/interpreter/jdbc.md                        |  7 ++-
 .../apache/zeppelin/jdbc/JDBCInterpreter.java   | 10 +++
 .../src/main/resources/interpreter-setting.json |  8 ++-
 .../zeppelin/jdbc/JDBCInterpreterTest.java      | 66 ++++++++++++++++++++
 4 files changed, 89 insertions(+), 2 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/zeppelin/blob/1795ed42/docs/interpreter/jdbc.md
----------------------------------------------------------------------
diff --git a/docs/interpreter/jdbc.md b/docs/interpreter/jdbc.md
index 44f8aab..a6c28df 100644
--- a/docs/interpreter/jdbc.md
+++ b/docs/interpreter/jdbc.md
@@ -125,6 +125,11 @@ The JDBC interpreter properties are defined by default 
like below.
     <td>Some SQL which executes every time after initialization of the 
interpreter (see <a 
href="../usage/interpreter/overview.html#interpreter-binding-mode">Binding 
mode</a>)</td>
   </tr>
   <tr>
+    <td>default.statementPrecode</td>
+    <td></td>
+    <td>SQL code which executed before the SQL from paragraph, in the same 
database session (database connection)</td>
+  </tr>
+  <tr>
     <td>default.completer.schemaFilters</td>
     <td></td>
     <td>Сomma separated schema (schema = catalog = database) filters to get 
metadata for completions. Supports '%' symbol is equivalent to any set of 
characters. (ex. prod_v_%,public%,info)</td>
@@ -306,7 +311,7 @@ Returns value of `search_path` which is set in the 
*default.precode*.
 
 
 ```sql
-%jdbc(mysql)
+%mysql
 select @v
 ```
 Returns value of `v` which is set in the *mysql.precode*.

http://git-wip-us.apache.org/repos/asf/zeppelin/blob/1795ed42/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 fa69165..21f6a62 100644
--- a/jdbc/src/main/java/org/apache/zeppelin/jdbc/JDBCInterpreter.java
+++ b/jdbc/src/main/java/org/apache/zeppelin/jdbc/JDBCInterpreter.java
@@ -103,6 +103,7 @@ public class JDBCInterpreter extends KerberosInterpreter {
   static final String USER_KEY = "user";
   static final String PASSWORD_KEY = "password";
   static final String PRECODE_KEY = "precode";
+  static final String STATEMENT_PRECODE_KEY = "statementPrecode";
   static final String COMPLETER_SCHEMA_FILTERS_KEY = "completer.schemaFilters";
   static final String COMPLETER_TTL_KEY = "completer.ttlInSeconds";
   static final String DEFAULT_COMPLETER_TTL = "120";
@@ -110,6 +111,7 @@ public class JDBCInterpreter extends KerberosInterpreter {
   static final String JDBC_JCEKS_FILE = "jceks.file";
   static final String JDBC_JCEKS_CREDENTIAL_KEY = "jceks.credentialKey";
   static final String PRECODE_KEY_TEMPLATE = "%s.precode";
+  static final String STATEMENT_PRECODE_KEY_TEMPLATE = "%s.statementPrecode";
   static final String DOT = ".";
 
   private static final char WHITESPACE = ' ';
@@ -125,6 +127,7 @@ public class JDBCInterpreter extends KerberosInterpreter {
   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 DEFAULT_STATEMENT_PRECODE = DEFAULT_KEY + DOT + 
STATEMENT_PRECODE_KEY;
 
   static final String EMPTY_COLUMN_VALUE = "";
 
@@ -705,6 +708,13 @@ public class JDBCInterpreter extends KerberosInterpreter {
         try {
           getJDBCConfiguration(user).saveStatement(paragraphId, statement);
 
+          String statementPrecode =
+              getProperty(String.format(STATEMENT_PRECODE_KEY_TEMPLATE, 
propertyKey));
+          
+          if (StringUtils.isNotBlank(statementPrecode)) {
+            statement.execute(statementPrecode);
+          }
+
           boolean isResultSetAvailable = statement.execute(sqlToExecute);
           
getJDBCConfiguration(user).setConnectionInDBDriverPoolSuccessful(propertyKey);
           if (isResultSetAvailable) {

http://git-wip-us.apache.org/repos/asf/zeppelin/blob/1795ed42/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 bedd9df..2cda3ba 100644
--- a/jdbc/src/main/resources/interpreter-setting.json
+++ b/jdbc/src/main/resources/interpreter-setting.json
@@ -48,11 +48,17 @@
       },
       "default.precode": {
         "envName": null,
-        "propertyName": "zeppelin.jdbc.precode",
+        "propertyName": "default.precode",
         "defaultValue": "",
         "description": "SQL which executes while opening connection",
         "type": "textarea"
       },
+      "default.statementPrecode": {
+        "envName": null,
+        "propertyName": "default.statementPrecode",
+        "defaultValue": "",
+        "description": "Runs before each run of the paragraph, in the same 
connection"
+      },
       "default.splitQueries": {
         "envName": null,
         "propertyName": "default.splitQueries",

http://git-wip-us.apache.org/repos/asf/zeppelin/blob/1795ed42/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 f1587d5..4847cf5 100644
--- a/jdbc/src/test/java/org/apache/zeppelin/jdbc/JDBCInterpreterTest.java
+++ b/jdbc/src/test/java/org/apache/zeppelin/jdbc/JDBCInterpreterTest.java
@@ -17,6 +17,7 @@ package org.apache.zeppelin.jdbc;
 import static java.lang.String.format;
 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_STATEMENT_PRECODE;
 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;
@@ -24,6 +25,11 @@ 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.*;
 
+import org.junit.Before;
+import org.junit.Test;
+import static 
org.apache.zeppelin.jdbc.JDBCInterpreter.STATEMENT_PRECODE_KEY_TEMPLATE;
+
+
 import java.io.IOException;
 import java.nio.file.Files;
 import java.nio.file.Path;
@@ -529,6 +535,66 @@ public class JDBCInterpreterTest extends 
BasicJDBCTestCaseAdapter {
   }
 
   @Test
+  public void testStatementPrecode() throws SQLException, IOException {
+    Properties properties = new Properties();
+    properties.setProperty("default.driver", "org.h2.Driver");
+    properties.setProperty("default.url", getJdbcConnection());
+    properties.setProperty("default.user", "");
+    properties.setProperty("default.password", "");
+    properties.setProperty(DEFAULT_STATEMENT_PRECODE, "set @v='statement'");
+    JDBCInterpreter jdbcInterpreter = new JDBCInterpreter(properties);
+    jdbcInterpreter.open();
+
+    String sqlQuery = "select @v";
+
+    InterpreterResult interpreterResult = jdbcInterpreter.interpret(sqlQuery, 
interpreterContext);
+
+    assertEquals(InterpreterResult.Code.SUCCESS, interpreterResult.code());
+    assertEquals(InterpreterResult.Type.TABLE, 
interpreterResult.message().get(0).getType());
+    assertEquals("@V\nstatement\n", 
interpreterResult.message().get(0).getData());
+  }
+
+  @Test
+  public void testIncorrectStatementPrecode() throws SQLException, IOException 
{
+    Properties properties = new Properties();
+    properties.setProperty("default.driver", "org.h2.Driver");
+    properties.setProperty("default.url", getJdbcConnection());
+    properties.setProperty("default.user", "");
+    properties.setProperty("default.password", "");
+    properties.setProperty(DEFAULT_STATEMENT_PRECODE, "set incorrect");
+    JDBCInterpreter jdbcInterpreter = new JDBCInterpreter(properties);
+    jdbcInterpreter.open();
+
+    String sqlQuery = "select 1";
+
+    InterpreterResult interpreterResult = jdbcInterpreter.interpret(sqlQuery, 
interpreterContext);
+
+    assertEquals(InterpreterResult.Code.ERROR, interpreterResult.code());
+    assertEquals(InterpreterResult.Type.TEXT, 
interpreterResult.message().get(0).getType());
+  }
+
+  @Test
+  public void testStatementPrecodeWithAnotherPrefix() 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(STATEMENT_PRECODE_KEY_TEMPLATE, 
"anotherPrefix"),
+            "set @v='statementAnotherPrefix'");
+    JDBCInterpreter jdbcInterpreter = new JDBCInterpreter(properties);
+    jdbcInterpreter.open();
+
+    String sqlQuery = "(anotherPrefix) select @v";
+
+    InterpreterResult interpreterResult = jdbcInterpreter.interpret(sqlQuery, 
interpreterContext);
+
+    assertEquals(InterpreterResult.Code.SUCCESS, interpreterResult.code());
+    assertEquals(InterpreterResult.Type.TABLE, 
interpreterResult.message().get(0).getType());
+    assertEquals("@V\nstatementAnotherPrefix\n", 
interpreterResult.message().get(0).getData());
+  }
+
+  @Test
   public void testSplitSqlQueryWithComments() throws SQLException, IOException 
{
     Properties properties = new Properties();
     properties.setProperty("common.max_count", "1000");

Reply via email to