Remove "AS" keyword for table aliases in Visual Query Builder

Description

We know that Kylo latest version(0.8.0.1) has a new feature as below.

"Join Hive and any JDBC tables in Data Transformation feeds by creating a new Data Source"

I found a bug that occurs some exception when I modify a query on EDIT SQL of Data Transformation feed and then go to next step for Oracle data source. I know that Oracle not allows table alias 'AS'. However, when I saw a spark-shell error log, Kylo used 'AS' table alias in scala logic. At this time, I think we cannot modify Oracle sql on the feed.

Environment

None

Activity

Show:
Greg Hart
May 4, 2017, 6:16 PM

Actually I'm not able to reproduce either issue:
1) The SELECT query only uses AS for column aliases and is supported by Oracle.
2) The EDIT SQL button allows me to change the SQL and it uses the updated SQL on the next step.

Could you provide more information?

  • What version of Oracle are you using?

  • What are the steps needed to reproduce the issue?

  • What is the invalid SQL query produced by the Data Transformation feed?

  • What changes did you make to the SQL query in order for it to work?

  • Please provide screenshots of any error messages you received.

Thanks!

Joel Lee
May 8, 2017, 12:32 AM
Edited

1) I opened the BUILD QUERY step of Visual Query.
2) We got the table(GMES20DBA.TBM_PM_PRODC_PRGS) using ADD Table button of BUILD QUERY step and go to Continue to step 2.
3) Go back to previous step.
4) I typed "SELECT FCT_CODE FROM GMES20DBA.TBM_PM_PRODC_PRGS" in EDIT SQL.
5) Go to Continue to step 2

I attached my log of kylo-spark-shell.err. Oracle Version is 11g.
============================================================================================

15:23:40,937 SparkScriptEngine.execute: Executing script:
class Script (engine: com.thinkbiganalytics.spark.repl.ScriptEngine) extends com.thinkbiganalytics.spark.repl.Script (engine) {
override def eval (): Any = {
class Transform (destination: String, sendResults: Boolean, sqlContext: org.apache.spark.sql.SQLContext) extends com.thinkbiganalytics.spark.metadata.TransformScript(destination, sendResults, sqlContext) {
override def dataFrame: org.apache.spark.sql.DataFrame = {import org.apache.spark.sql._
var df = datasourceProvider.getTableFromDatasource("(SELECT FCT_CODE FROM GMES20DBA.TBM_PM_PRODC_PRGS) AS KYLO_SPARK_QUERY", "18abc1ae-915d-4e9b-9352-34b521f791d1", sqlContext)
df = df.limit(1000)
df
}
}
new Transform(tableName, true, sqlContext).run()
}
def database (): String = getValue("database")
def tableName (): String = getValue("tableName")
def datasourceProvider (): com.thinkbiganalytics.spark.shell.DatasourceProviderV1 = getValue("datasourceProvider")
}
new Script(engine).run()

15:23:41,023 TransformService.execute: Failed to update table weight: org.apache.spark.sql.catalyst.analysis.NoSuchTableException
15:23:41,243 TransformService.execute: Throwing {}
javax.script.ScriptException: java.util.concurrent.ExecutionException: java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended

at com.thinkbiganalytics.spark.service.TransformService.execute(TransformService.java:214)
at com.thinkbiganalytics.spark.rest.SparkShellTransformController.create(SparkShellTransformController.java:105)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.glassfish.jersey.server.model.internal.ResourceMethodInvocationHandlerFactory$1.invoke(ResourceMethodInvocationHandlerFactory.java:81)
at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher$1.run(AbstractJavaResourceMethodDispatcher.java:144)
at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.invoke(AbstractJavaResourceMethodDispatcher.java:161)
at org.glassfish.jersey.server.model.internal.JavaResourceMethodDispatcherProvider$ResponseOutInvoker.doDispatch(JavaResourceMethodDispatcherProvider.java:160)
at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.dispatch(AbstractJavaResourceMethodDispatcher.java:99)
at org.glassfish.jersey.server.model.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:389)
at org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:347)
at org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:102)
at org.glassfish.jersey.server.ServerRuntime$2.run(ServerRuntime.java:326)
at org.glassfish.jersey.internal.Errors$1.call(Errors.java:271)
at org.glassfish.jersey.internal.Errors$1.call(Errors.java:267)
at org.glassfish.jersey.internal.Errors.process(Errors.java:315)
at org.glassfish.jersey.internal.Errors.process(Errors.java:297)
at org.glassfish.jersey.internal.Errors.process(Errors.java:267)
at org.glassfish.jersey.process.internal.RequestScope.runInScope(RequestScope.java:317)
at org.glassfish.jersey.server.ServerRuntime.process(ServerRuntime.java:305)
at org.glassfish.jersey.server.ApplicationHandler.handle(ApplicationHandler.java:1154)
at org.glassfish.jersey.servlet.WebComponent.serviceImpl(WebComponent.java:473)
at org.glassfish.jersey.servlet.WebComponent.service(WebComponent.java:427)
at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:388)
at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:341)

Claudiu Stanciu
July 5, 2017, 2:59 PM
Edited

I've tried it as well on Kylo 0.8.1 and Oracle 11g, and my query looks a bit more different than yours

My only issue is that I don't know where to put the Oracle jdbc driver to work with spark
I've added it in:

  • /opt/kylo/kylo-services/lib

  • /opt/kylo/kylo-services/plugin

  • /opt/kylo/lib

Any idea or ? Thanks

class Script (engine: com.thinkbiganalytics.spark.repl.ScriptEngine) extends com.thinkbiganalytics.spark.repl.Script (engine) {
override def eval (): Any = {
class Transform (destination: String, profiler: com.thinkbiganalytics.spark.dataprofiler.Profiler, sqlContext: org.apache.spark.sql.SQLContext, sparkContextService: com.thinkbiganalytics.spark.SparkContextService) extends com.thinkbiganalytics.spark.metadata.TransformScript16(destination, profiler, sqlContext, sparkContextService) {
override def dataFrame: org.apache.spark.sql.DataFrame = {import org.apache.spark.sql._
val tbl10 = datasourceProvider.getTableFromDatasource("SYSTEM.PET", "36c699e5-fccc-4cd7-a542-2488332777fb", sqlContext).alias("tbl10")
var df = tbl10.select(tbl10.col("NAME"), tbl10.col("MASTER"), tbl10.col("DESCRIPTION"))
df = df.limit(1000)
df
}
}
new Transform(tableName, profiler, sqlContext, sparkContextService).run()
}
def profiler (): com.thinkbiganalytics.spark.dataprofiler.Profiler = getValue("profiler")
def sparkContextService (): com.thinkbiganalytics.spark.SparkContextService = getValue("sparkContextService")
def tableName (): String = getValue("tableName")
def datasourceProvider (): com.thinkbiganalytics.spark.shell.DatasourceProvider[org.apache.spark.sql.DataFrame] = getValue("datasourceProvider")
}
new Script(engine).run()

2017-07-05 14:38:15 INFO http-nio-8450-exec-1:SparkScriptEngine:223 - Loaded Spark deny patterns from spark-deny-patterns.default.conf.
2017-07-05 14:38:17 ERROR http-nio-8450-exec-1:TransformService:188 - Throwing {}
javax.script.ScriptException: java.util.concurrent.ExecutionException: java.lang.ClassNotFoundException: oracle.jdbc.OracleDriver

Claudiu Stanciu
July 6, 2017, 9:58 AM

I've added the oracle jar in /usr/local/spark/lib, but the kylo spark shell doesn't use the datasource connection URL, it uses a random localhost:9000

Claudiu Stanciu
July 7, 2017, 3:49 PM
Edited

It works fine after you add the jdbc driver in 4 locations. Will make a PR for the kylo doc.
I saw that the syntax for table alias is different between kylo 0.7 and kylo 0.8, as for the later you no longer have SELECT ... AS ...", but instead have a functor. Might want to try the newer kylo releases and see if you still have the issue.

Assignee

Greg Hart

Reporter

Joel Lee

Labels

None

Reviewer

None

Story point estimate

None

Components

Priority

Highest
Configure