Hive Loader Node - External Table

Hi,

Is there an option to Load the table from knime as external table using the "Hive Loader"?

If not, would you able to add such a flag to the "Hive Loader" node?

Thanks,

Moran

Hallo Moran,

the node does not support this option yet. I have opened a feature request for it.

Thanks for your feedback.

Bye

Tobias

I have also some problems with the hive loader, just using a local file loaded in knime. (SSH connection, file reader, hive connector - into hive loader)

I get an error that the file is not found. Even though the log seems to indicate the transfer is working.

Error

ERROR Hive Loader 3:7 Execute failed: [Cloudera][HiveJDBCDriver](500051) ERROR processing query/statement. Error Code: 40000, SQL state: TStatus(statusCode:ERROR_STATUS, infoMessages:[*org.apache.hive.service.cli.HiveSQLException:Error while compiling statement: FAILED: SemanticException Line 1:23 Invalid path ''/tmp/hive-import7831403958819358140.csv'': No files matching path file:/tmp/hive-import7831403958819358140.csv:17:16

=== KNIME LOG ===
2017-07-21 10:22:40,521 : DEBUG : main : NodeContainerEditPart :  :  : Hive Connector 3:9 (EXECUTED)
2017-07-21 10:22:42,068 : DEBUG : main : NodeContainerEditPart :  :  : Hive Loader 3:7 (CONFIGURED)
2017-07-21 10:22:44,974 : DEBUG : main : ExecuteAction :  :  : Creating execution job for 1 node(s)...
2017-07-21 10:22:44,975 : DEBUG : main : NodeContainer :  :  : Setting dirty flag on Hive Loader 3:7
2017-07-21 10:22:44,975 : DEBUG : main : NodeContainer :  :  : Setting dirty flag on hdfs_write_test 3
2017-07-21 10:22:44,975 : DEBUG : main : NodeContainer :  :  : Hive Loader 3:7 has new state: CONFIGURED_MARKEDFOREXEC
2017-07-21 10:22:44,975 : DEBUG : main : NodeContainer :  :  : Hive Loader 3:7 has new state: CONFIGURED_QUEUED
2017-07-21 10:22:44,975 : DEBUG : KNIME-Workflow-Notifier : WorkflowEditor :  :  : Workflow event triggered: WorkflowEvent [type=WORKFLOW_DIRTY;node=3;old=null;new=null;timestamp=Jul 21, 2017 10:22:44 AM]
2017-07-21 10:22:44,975 : DEBUG : main : NodeContainer :  :  : hdfs_write_test 3 has new state: EXECUTING
2017-07-21 10:22:44,977 : DEBUG : KNIME-Worker-7 : WorkflowManager : Hive Loader : 3:7 : Hive Loader 3:7 doBeforePreExecution
2017-07-21 10:22:44,977 : DEBUG : KNIME-Worker-7 : NodeContainer : Hive Loader : 3:7 : Hive Loader 3:7 has new state: PREEXECUTE
2017-07-21 10:22:44,977 : DEBUG : KNIME-Worker-7 : WorkflowManager : Hive Loader : 3:7 : Hive Loader 3:7 doBeforeExecution
2017-07-21 10:22:44,977 : DEBUG : KNIME-Worker-7 : NodeContainer : Hive Loader : 3:7 : Hive Loader 3:7 has new state: EXECUTING
2017-07-21 10:22:44,977 : DEBUG : KNIME-Worker-7 : WorkflowFileStoreHandlerRepository : Hive Loader : 3:7 : Adding handler 708fe2b4-f600-473f-a127-44fd4862e5f6 (Hive Loader 3:7:  ) - 1 in total
2017-07-21 10:22:44,977 : DEBUG : KNIME-Worker-7 : LocalNodeExecutionJob : Hive Loader : 3:7 : Hive Loader 3:7 Start execute
2017-07-21 10:22:44,977 : DEBUG : KNIME-WFM-Parent-Notifier : NodeContainer :  :  : ROOT  has new state: EXECUTING
2017-07-21 10:22:44,981 : DEBUG : KNIME-Worker-7 : HiveLoaderNodeModel : Hive Loader : 3:7 : Start writing KNIME table to temporary file /private/var/folders/r8/t4jjlf_j7zs1pgv9sq4pb92m0000gn/T/knime_hdfs_write_test79848/hive-import7831403958819358140.csv
2017-07-21 10:22:44,981 : DEBUG : KNIME-Worker-7 : HiveLoaderNodeModel : Hive Loader : 3:7 : Table structure name=connect_cdl2.txt,columns=[imsi; volume]
2017-07-21 10:22:44,981 : DEBUG : KNIME-Worker-7 : HiveLoaderNodeModel : Hive Loader : 3:7 : No of rows to write 500.0
2017-07-21 10:22:44,982 : DEBUG : KNIME-Worker-7 : Buffer : Hive Loader : 3:7 : Opening input stream on file "/private/var/folders/r8/t4jjlf_j7zs1pgv9sq4pb92m0000gn/T/knime_hdfs_write_test79848/knime_container_20170721_2310957789424027331.bin.gz", 1 open streams
2017-07-21 10:22:44,987 : DEBUG : KNIME-Worker-7 : Buffer : Hive Loader : 3:7 : Closing input stream on "/private/var/folders/r8/t4jjlf_j7zs1pgv9sq4pb92m0000gn/T/knime_hdfs_write_test79848/knime_container_20170721_2310957789424027331.bin.gz", 0 remaining
2017-07-21 10:22:44,988 : DEBUG : KNIME-Worker-7 : HiveLoaderNodeModel : Hive Loader : 3:7 : Temporary file successful created at /private/var/folders/r8/t4jjlf_j7zs1pgv9sq4pb92m0000gn/T/knime_hdfs_write_test79848/hive-import7831403958819358140.csv
2017-07-21 10:22:44,988 : DEBUG : KNIME-Worker-7 : HiveLoader : Hive Loader : 3:7 : Uploading local file /private/var/folders/r8/t4jjlf_j7zs1pgv9sq4pb92m0000gn/T/knime_hdfs_write_test79848/hive-import7831403958819358140.csv
2017-07-21 10:22:44,988 : DEBUG : KNIME-Worker-7 : HiveLoader : Hive Loader : 3:7 : Create remote folder with URI ssh://emil@130.130.130.130:22/tmp/
2017-07-21 10:22:53,835 : DEBUG : KNIME-Worker-7 : HiveLoader : Hive Loader : 3:7 : Remote folder created
2017-07-21 10:22:53,835 : DEBUG : KNIME-Worker-7 : HiveLoader : Hive Loader : 3:7 : Create remote file with URI ssh://emil@130.130.130.130:22/tmp/hive-import7831403958819358140.csv
2017-07-21 10:22:53,835 : DEBUG : KNIME-Worker-7 : HiveLoader : Hive Loader : 3:7 : Remote file created. Start writing file content...
2017-07-21 10:22:53,901 : DEBUG : KNIME-Worker-7 : HiveLoader : Hive Loader : 3:7 : File content sucessful written to remote file
2017-07-21 10:22:53,901 : DEBUG : KNIME-Worker-7 : NodeContext : Hive Loader : 3:7 : Workflow user found: emil
2017-07-21 10:22:53,902 : DEBUG : KNIME-Worker-7 : RegisteredDriversConnectionFactory : Hive Loader : 3:7 : Valid connection found in cache with key: ConnectionKey [db-user=, db-name=jdbc:hive2://dl200nn09.ddc.teliasonera.net:10000/;AuthMech=1;KrbRealm=TCAD.TELIA.SE;KrbHostFQDN=dl200nn09.ddc.teliasonera.net;KrbServiceName=hive;KrbAuthType=2;DelegationUID=emil, wf-user=emil]
2017-07-21 10:22:53,902 : DEBUG : KNIME-Worker-7 : HiveLoader : Hive Loader : 3:7 : Column names: [imsi, volume]
2017-07-21 10:22:53,902 : DEBUG : KNIME-Worker-7 : HiveLoader : Hive Loader : 3:7 : Partition columns: []
2017-07-21 10:22:53,921 : DEBUG : KNIME-Worker-7 : HiveLoader : Hive Loader : 3:7 : Importing data
2017-07-21 10:22:53,922 : DEBUG : KNIME-Worker-7 : HiveLoader : Hive Loader : 3:7 : Executing 'CREATE TABLE prod_swe_sandboxes.emil_test_2 (
   imsi DOUBLE,
   volume DOUBLE
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' ESCAPED BY '\\'
STORED AS TEXTFILE'
2017-07-21 10:23:01,182 : DEBUG : KNIME-Worker-7 : HiveLoader : Hive Loader : 3:7 : Table sucessful created
2017-07-21 10:23:02,935 : INFO  : KNIME-Worker-7 : HiveLoader : Hive Loader : 3:7 : Executing 'LOAD DATA LOCAL INPATH '/tmp/hive-import7831403958819358140.csv' INTO TABLE prod_swe_sandboxes.emil_test_2'
2017-07-21 10:23:03,754 : DEBUG : KNIME-Worker-7 : Hive Loader : Hive Loader : 3:7 : reset
2017-07-21 10:23:03,755 : ERROR : KNIME-Worker-7 : Hive Loader : Hive Loader : 3:7 : Execute failed: [Cloudera][HiveJDBCDriver](500051) ERROR processing query/statement. Error Code: 40000, SQL state: TStatus(statusCode:ERROR_STATUS, infoMessages:[*org.apache.hive.service.cli.HiveSQLException:Error while compiling statement: FAILED: SemanticException Line 1:23 Invalid path ''/tmp/hive-import7831403958819358140.csv'': No files matching path file:/tmp/hive-import7831403958819358140.csv:17:16, org.apache.hive.service.cli.operation.Operation:toSQLException:Operation.java:385, org.apache.hive.service.cli.operation.SQLOperation:prepare:SQLOperation.java:136, org.apache.hive.service.cli.operation.SQLOperation:runInternal:SQLOperation.java:206, org.apache.hive.service.cli.operation.Operation:run:Operation.java:327, org.apache.hive.service.cli.session.HiveSessionImpl:executeStatementInternal:HiveSessionImpl.java:424, org.apache.hive.service.cli.session.HiveSessionImpl:executeStatementAsync:HiveSessionImpl.java:401, org.apache.hive.service.cli.CLIService:executeStatementAsync:CLIService.java:258, org.apache.hive.service.cli.thrift.ThriftCLIService:ExecuteStatement:ThriftCLIService.java:500, org.apache.hive.service.cli.thrift.TCLIService$Processor$ExecuteStatement:getResult:TCLIService.java:1313, org.apache.hive.service.cli.thrift.TCLIService$Processor$ExecuteStatement:getResult:TCLIService.java:1298, org.apache.thrift.ProcessFunction:process:ProcessFunction.java:39, org.apache.thrift.TBaseProcessor:process:TBaseProcessor.java:39, org.apache.hadoop.hive.thrift.HadoopThriftAuthBridge$Server$TUGIAssumingProcessor:process:HadoopThriftAuthBridge.java:746, org.apache.thrift.server.TThreadPoolServer$WorkerProcess:run:TThreadPoolServer.java:286, java.util.concurrent.ThreadPoolExecutor:runWorker:ThreadPoolExecutor.java:1145, java.util.concurrent.ThreadPoolExecutor$Worker:run:ThreadPoolExecutor.java:615, java.lang.Thread:run:Thread.java:745, *org.apache.hadoop.hive.ql.parse.SemanticException:Line 1:23 Invalid path ''/tmp/hive-import7831403958819358140.csv'': No files matching path file:/tmp/hive-import7831403958819358140.csv:23:7, org.apache.hadoop.hive.ql.parse.LoadSemanticAnalyzer:applyConstraints:LoadSemanticAnalyzer.java:139, org.apache.hadoop.hive.ql.parse.LoadSemanticAnalyzer:analyzeInternal:LoadSemanticAnalyzer.java:230, org.apache.hadoop.hive.ql.parse.BaseSemanticAnalyzer:analyze:BaseSemanticAnalyzer.java:223, org.apache.hadoop.hive.ql.Driver:compile:Driver.java:446, org.apache.hadoop.hive.ql.Driver:compile:Driver.java:312, org.apache.hadoop.hive.ql.Driver:compileInternal:Driver.java:1201, org.apache.hadoop.hive.ql.Driver:compileAndRespond:Driver.java:1188, org.apache.hive.service.cli.operation.SQLOperation:prepare:SQLOperation.java:134], sqlState:42000, errorCode:40000, errorMessage:Error while compiling statement: FAILED: SemanticException Line 1:23 Invalid path ''/tmp/hive-import7831403958819358140.csv'': No files matching path file:/tmp/hive-import7831403958819358140.csv), Query: LOAD DATA LOCAL INPATH '/tmp/hive-import7831403958819358140.csv' INTO TABLE prod_swe_sandboxes.emil_test_2.
2017-07-21 10:23:03,755 : DEBUG : KNIME-Worker-7 : Hive Loader : Hive Loader : 3:7 : Execute failed: [Cloudera][HiveJDBCDriver](500051) ERROR processing query/statement. Error Code: 40000, SQL state: TStatus(statusCode:ERROR_STATUS, infoMessages:[*org.apache.hive.service.cli.HiveSQLException:Error while compiling statement: FAILED: SemanticException Line 1:23 Invalid path ''/tmp/hive-import7831403958819358140.csv'': No files matching path file:/tmp/hive-import7831403958819358140.csv:17:16, org.apache.hive.service.cli.operation.Operation:toSQLException:Operation.java:385, org.apache.hive.service.cli.operation.SQLOperation:prepare:SQLOperation.java:136, org.apache.hive.service.cli.operation.SQLOperation:runInternal:SQLOperation.java:206, org.apache.hive.service.cli.operation.Operation:run:Operation.java:327, org.apache.hive.service.cli.session.HiveSessionImpl:executeStatementInternal:HiveSessionImpl.java:424, org.apache.hive.service.cli.session.HiveSessionImpl:executeStatementAsync:HiveSessionImpl.java:401, org.apache.hive.service.cli.CLIService:executeStatementAsync:CLIService.java:258, org.apache.hive.service.cli.thrift.ThriftCLIService:ExecuteStatement:ThriftCLIService.java:500, org.apache.hive.service.cli.thrift.TCLIService$Processor$ExecuteStatement:getResult:TCLIService.java:1313, org.apache.hive.service.cli.thrift.TCLIService$Processor$ExecuteStatement:getResult:TCLIService.java:1298, org.apache.thrift.ProcessFunction:process:ProcessFunction.java:39, org.apache.thrift.TBaseProcessor:process:TBaseProcessor.java:39, org.apache.hadoop.hive.thrift.HadoopThriftAuthBridge$Server$TUGIAssumingProcessor:process:HadoopThriftAuthBridge.java:746, org.apache.thrift.server.TThreadPoolServer$WorkerProcess:run:TThreadPoolServer.java:286, java.util.concurrent.ThreadPoolExecutor:runWorker:ThreadPoolExecutor.java:1145, java.util.concurrent.ThreadPoolExecutor$Worker:run:ThreadPoolExecutor.java:615, java.lang.Thread:run:Thread.java:745, *org.apache.hadoop.hive.ql.parse.SemanticException:Line 1:23 Invalid path ''/tmp/hive-import7831403958819358140.csv'': No files matching path file:/tmp/hive-import7831403958819358140.csv:23:7, org.apache.hadoop.hive.ql.parse.LoadSemanticAnalyzer:applyConstraints:LoadSemanticAnalyzer.java:139, org.apache.hadoop.hive.ql.parse.LoadSemanticAnalyzer:analyzeInternal:LoadSemanticAnalyzer.java:230, org.apache.hadoop.hive.ql.parse.BaseSemanticAnalyzer:analyze:BaseSemanticAnalyzer.java:223, org.apache.hadoop.hive.ql.Driver:compile:Driver.java:446, org.apache.hadoop.hive.ql.Driver:compile:Driver.java:312, org.apache.hadoop.hive.ql.Driver:compileInternal:Driver.java:1201, org.apache.hadoop.hive.ql.Driver:compileAndRespond:Driver.java:1188, org.apache.hive.service.cli.operation.SQLOperation:prepare:SQLOperation.java:134], sqlState:42000, errorCode:40000, errorMessage:Error while compiling statement: FAILED: SemanticException Line 1:23 Invalid path ''/tmp/hive-import7831403958819358140.csv'': No files matching path file:/tmp/hive-import7831403958819358140.csv), Query: LOAD DATA LOCAL INPATH '/tmp/hive-import7831403958819358140.csv' INTO TABLE prod_swe_sandboxes.emil_test_2.
java.sql.SQLException: [Cloudera][HiveJDBCDriver](500051) ERROR processing query/statement. Error Code: 40000, SQL state: TStatus(statusCode:ERROR_STATUS, infoMessages:[*org.apache.hive.service.cli.HiveSQLException:Error while compiling statement: FAILED: SemanticException Line 1:23 Invalid path ''/tmp/hive-import7831403958819358140.csv'': No files matching path file:/tmp/hive-import7831403958819358140.csv:17:16, org.apache.hive.service.cli.operation.Operation:toSQLException:Operation.java:385, org.apache.hive.service.cli.operation.SQLOperation:prepare:SQLOperation.java:136, org.apache.hive.service.cli.operation.SQLOperation:runInternal:SQLOperation.java:206, org.apache.hive.service.cli.operation.Operation:run:Operation.java:327, org.apache.hive.service.cli.session.HiveSessionImpl:executeStatementInternal:HiveSessionImpl.java:424, org.apache.hive.service.cli.session.HiveSessionImpl:executeStatementAsync:HiveSessionImpl.java:401, org.apache.hive.service.cli.CLIService:executeStatementAsync:CLIService.java:258, org.apache.hive.service.cli.thrift.ThriftCLIService:ExecuteStatement:ThriftCLIService.java:500, org.apache.hive.service.cli.thrift.TCLIService$Processor$ExecuteStatement:getResult:TCLIService.java:1313, org.apache.hive.service.cli.thrift.TCLIService$Processor$ExecuteStatement:getResult:TCLIService.java:1298, org.apache.thrift.ProcessFunction:process:ProcessFunction.java:39, org.apache.thrift.TBaseProcessor:process:TBaseProcessor.java:39, org.apache.hadoop.hive.thrift.HadoopThriftAuthBridge$Server$TUGIAssumingProcessor:process:HadoopThriftAuthBridge.java:746, org.apache.thrift.server.TThreadPoolServer$WorkerProcess:run:TThreadPoolServer.java:286, java.util.concurrent.ThreadPoolExecutor:runWorker:ThreadPoolExecutor.java:1145, java.util.concurrent.ThreadPoolExecutor$Worker:run:ThreadPoolExecutor.java:615, java.lang.Thread:run:Thread.java:745, *org.apache.hadoop.hive.ql.parse.SemanticException:Line 1:23 Invalid path ''/tmp/hive-import7831403958819358140.csv'': No files matching path file:/tmp/hive-import7831403958819358140.csv:23:7, org.apache.hadoop.hive.ql.parse.LoadSemanticAnalyzer:applyConstraints:LoadSemanticAnalyzer.java:139, org.apache.hadoop.hive.ql.parse.LoadSemanticAnalyzer:analyzeInternal:LoadSemanticAnalyzer.java:230, org.apache.hadoop.hive.ql.parse.BaseSemanticAnalyzer:analyze:BaseSemanticAnalyzer.java:223, org.apache.hadoop.hive.ql.Driver:compile:Driver.java:446, org.apache.hadoop.hive.ql.Driver:compile:Driver.java:312, org.apache.hadoop.hive.ql.Driver:compileInternal:Driver.java:1201, org.apache.hadoop.hive.ql.Driver:compileAndRespond:Driver.java:1188, org.apache.hive.service.cli.operation.SQLOperation:prepare:SQLOperation.java:134], sqlState:42000, errorCode:40000, errorMessage:Error while compiling statement: FAILED: SemanticException Line 1:23 Invalid path ''/tmp/hive-import7831403958819358140.csv'': No files matching path file:/tmp/hive-import7831403958819358140.csv), Query: LOAD DATA LOCAL INPATH '/tmp/hive-import7831403958819358140.csv' INTO TABLE prod_swe_sandboxes.emil_test_2.
	at com.cloudera.hiveserver2.hivecommon.api.HS2Client.executeStatementInternal(Unknown Source)
	at com.cloudera.hiveserver2.hivecommon.api.HS2Client.executeStatement(Unknown Source)
	at com.cloudera.hiveserver2.hivecommon.dataengine.HiveJDBCNativeQueryExecutor.executeHelper(Unknown Source)
	at com.cloudera.hiveserver2.hivecommon.dataengine.HiveJDBCNativeQueryExecutor.execute(Unknown Source)
	at com.cloudera.hiveserver2.jdbc.common.SStatement.executeNoParams(Unknown Source)
	at com.cloudera.hiveserver2.jdbc.common.SStatement.execute(Unknown Source)
	at com.knime.bigdata.hive.utility.HiveLoader.importData(HiveLoader.java:174)
	at com.knime.bigdata.hive.node.loader.HiveLoaderNodeModel.execute(HiveLoaderNodeModel.java:200)
	at org.knime.core.node.NodeModel.executeModel(NodeModel.java:566)
	at org.knime.core.node.Node.invokeFullyNodeModelExecute(Node.java:1128)
	at org.knime.core.node.Node.execute(Node.java:915)
	at org.knime.core.node.workflow.NativeNodeContainer.performExecuteNode(NativeNodeContainer.java:561)
	at org.knime.core.node.exec.LocalNodeExecutionJob.mainExecute(LocalNodeExecutionJob.java:95)
	at org.knime.core.node.workflow.NodeExecutionJob.internalRun(NodeExecutionJob.java:179)
	at org.knime.core.node.workflow.NodeExecutionJob.run(NodeExecutionJob.java:110)
	at org.knime.core.util.ThreadUtils$RunnableWithContextImpl.runWithContext(ThreadUtils.java:328)
	at org.knime.core.util.ThreadUtils$RunnableWithContext.run(ThreadUtils.java:204)
	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
	at java.util.concurrent.FutureTask.run(FutureTask.java:266)
	at org.knime.core.util.ThreadPool$MyFuture.run(ThreadPool.java:123)
Caused by: com.cloudera.hiveserver2.support.exceptions.GeneralException: [Cloudera][HiveJDBCDriver](500051) ERROR processing query/statement. Error Code: 40000, SQL state: TStatus(statusCode:ERROR_STATUS, infoMessages:[*org.apache.hive.service.cli.HiveSQLException:Error while compiling statement: FAILED: SemanticException Line 1:23 Invalid path ''/tmp/hive-import7831403958819358140.csv'': No files matching path file:/tmp/hive-import7831403958819358140.csv:17:16, org.apache.hive.service.cli.operation.Operation:toSQLException:Operation.java:385, org.apache.hive.service.cli.operation.SQLOperation:prepare:SQLOperation.java:136, org.apache.hive.service.cli.operation.SQLOperation:runInternal:SQLOperation.java:206, org.apache.hive.service.cli.operation.Operation:run:Operation.java:327, org.apache.hive.service.cli.session.HiveSessionImpl:executeStatementInternal:HiveSessionImpl.java:424, org.apache.hive.service.cli.session.HiveSessionImpl:executeStatementAsync:HiveSessionImpl.java:401, org.apache.hive.service.cli.CLIService:executeStatementAsync:CLIService.java:258, org.apache.hive.service.cli.thrift.ThriftCLIService:ExecuteStatement:ThriftCLIService.java:500, org.apache.hive.service.cli.thrift.TCLIService$Processor$ExecuteStatement:getResult:TCLIService.java:1313, org.apache.hive.service.cli.thrift.TCLIService$Processor$ExecuteStatement:getResult:TCLIService.java:1298, org.apache.thrift.ProcessFunction:process:ProcessFunction.java:39, org.apache.thrift.TBaseProcessor:process:TBaseProcessor.java:39, org.apache.hadoop.hive.thrift.HadoopThriftAuthBridge$Server$TUGIAssumingProcessor:process:HadoopThriftAuthBridge.java:746, org.apache.thrift.server.TThreadPoolServer$WorkerProcess:run:TThreadPoolServer.java:286, java.util.concurrent.ThreadPoolExecutor:runWorker:ThreadPoolExecutor.java:1145, java.util.concurrent.ThreadPoolExecutor$Worker:run:ThreadPoolExecutor.java:615, java.lang.Thread:run:Thread.java:745, *org.apache.hadoop.hive.ql.parse.SemanticException:Line 1:23 Invalid path ''/tmp/hive-import7831403958819358140.csv'': No files matching path file:/tmp/hive-import7831403958819358140.csv:23:7, org.apache.hadoop.hive.ql.parse.LoadSemanticAnalyzer:applyConstraints:LoadSemanticAnalyzer.java:139, org.apache.hadoop.hive.ql.parse.LoadSemanticAnalyzer:analyzeInternal:LoadSemanticAnalyzer.java:230, org.apache.hadoop.hive.ql.parse.BaseSemanticAnalyzer:analyze:BaseSemanticAnalyzer.java:223, org.apache.hadoop.hive.ql.Driver:compile:Driver.java:446, org.apache.hadoop.hive.ql.Driver:compile:Driver.java:312, org.apache.hadoop.hive.ql.Driver:compileInternal:Driver.java:1201, org.apache.hadoop.hive.ql.Driver:compileAndRespond:Driver.java:1188, org.apache.hive.service.cli.operation.SQLOperation:prepare:SQLOperation.java:134], sqlState:42000, errorCode:40000, errorMessage:Error while compiling statement: FAILED: SemanticException Line 1:23 Invalid path ''/tmp/hive-import7831403958819358140.csv'': No files matching path file:/tmp/hive-import7831403958819358140.csv), Query: LOAD DATA LOCAL INPATH '/tmp/hive-import7831403958819358140.csv' INTO TABLE prod_swe_sandboxes.emil_test_2.
	... 20 more
2017-07-21 10:23:03,755 : DEBUG : KNIME-Worker-7 : WorkflowManager : Hive Loader : 3:7 : Hive Loader 3:7 doBeforePostExecution
2017-07-21 10:23:03,755 : DEBUG : KNIME-Worker-7 : NodeContainer : Hive Loader : 3:7 : Hive Loader 3:7 has new state: POSTEXECUTE
2017-07-21 10:23:03,756 : DEBUG : KNIME-Worker-7 : WorkflowManager : Hive Loader : 3:7 : Hive Loader 3:7 doAfterExecute - failure
2017-07-21 10:23:03,756 : DEBUG : KNIME-Worker-7 : Hive Loader : Hive Loader : 3:7 : reset
2017-07-21 10:23:03,756 : DEBUG : KNIME-Worker-7 : Hive Loader : Hive Loader : 3:7 : clean output ports.
2017-07-21 10:23:03,756 : DEBUG : KNIME-Worker-7 : WorkflowFileStoreHandlerRepository : Hive Loader : 3:7 : Removing handler 708fe2b4-f600-473f-a127-44fd4862e5f6 (Hive Loader 3:7:  ) - 0 remaining
2017-07-21 10:23:03,756 : DEBUG : KNIME-Worker-7 : NodeContainer : Hive Loader : 3:7 : Hive Loader 3:7 has new state: IDLE
2017-07-21 10:23:03,759 : DEBUG : KNIME-Node-Usage-Writer : NodeTimer$GlobalNodeStats :  :  : Successfully wrote node usage stats to file: /Users/emil/knime-workspace/.metadata/knime/nodeusage_3.0.json
2017-07-21 10:23:03,760 : DEBUG : KNIME-Worker-7 : Hive Loader : Hive Loader : 3:7 : Configure succeeded. (Hive Loader)
2017-07-21 10:23:03,760 : DEBUG : KNIME-Worker-7 : NodeContainer : Hive Loader : 3:7 : Hive Loader 3:7 has new state: CONFIGURED
2017-07-21 10:23:03,760 : DEBUG : KNIME-Worker-7 : NodeContainer : Hive Loader : 3:7 : hdfs_write_test 3 has new state: CONFIGURED
2017-07-21 10:23:03,760 : DEBUG : KNIME-WFM-Parent-Notifier : NodeContainer :  :  : ROOT  has new state: IDLE
2017-07-21 10:28:01,520 : DEBUG : main : WorkflowEditor :  :  : Saving workflow hdfs_write_test 3
2017-07-21 10:28:01,548 : DEBUG : ModalContext : FileSingleNodeContainerPersistor :  :  : Replaced node directory "/private/var/folders/r8/t4jjlf_j7zs1pgv9sq4pb92m0000gn/T/knime_temp_space_emil_79841/hdfs_write_test79847/hdfs_write_test/Hive Loader (#7)"
2017-07-21 10:28:01,690 : INFO  : Worker-13 : WorkflowUpload :  :  : Start compressing local workflow knime-temp-space:/hdfs_write_test79847/hdfs_write_test ...
2017-07-21 10:28:01,695 : INFO  : Worker-13 : WorkflowUpload :  :  : Waiting for workflow compression to finish...
2017-07-21 10:28:01,715 : DEBUG : Worker-13 : WorkflowUpload :  :  : Uploading hdfs_write_test (0MB)
2017-07-21 10:28:01,888 : DEBUG : Worker-13 : WorkflowUpload :  :  : Upload of 'hdfs_write_test' finished. Stored in 'hdfs_write_test'
2017-07-21 10:28:01,902 : INFO  : Worker-13 : WorkflowUpload :  :  : Uploading 'knime-temp-space:/hdfs_write_test79847/hdfs_write_test' to '/scheduling/hdfs_write_test' finished.
2017-07-21 10:28:05,074 : DEBUG : main : NodeContainerEditPart :  :  : Hive Loader 3:7 (CONFIGURED)







screen_shot_2017-07-21_at_10.39.51.png

Hi Emil,

from the logs I noticed that you did the SSH Connection to

130.130.130.130

whereas the Hive Connector connected to

dl200nn09.ddc.teliasonera.net

which is a different machine than the SSH host. If you use SSH Connection with the Hive Loader node, you need to connect to the SSH service on the same machine where Hiveserver2 is running (in your case dl200nn09.ddc.teliasonera.net).

That being said, it is usually better to use Hive Loader with the httpFS Connection node. In that case the data is transfered via HDFS which does not require httpFS and hiveserver2 to run on the same machine (just within the same Hadoop cluster).

- Björn