java.nio.channels.OverlappingFileLockException with Teiid Excel data source

Question

I'm running a simple Spring Boot with Teiid (project available here). I made a vdb xml configuration file with a model on an Excel file data source, and a view to manipulate data from that source:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<vdb name="MyVDB" version="1">

    <model name="mymodel" type="PHYSICAL" visible="false">
        <property name="importer.headerRowNumber" value="1"/>
        <property name="importer.dataRowNumber" value="2"/>
        <property name="importer.excelFileName" value="my data.xlsx"/>
        <source connection-jndi-name="java:/data" name="xlsdata" translator-name="excel"/>
        <metadata type="DDL">

            CREATE FOREIGN TABLE Sheet1 (
                element_id        string OPTIONS (SEARCHABLE 'Unsearchable', "teiid_excel:CELL_NUMBER" '1'),
                parent_element_id string OPTIONS (SEARCHABLE 'Unsearchable', "teiid_excel:CELL_NUMBER" '3'),
                element_title00   string OPTIONS (SEARCHABLE 'Unsearchable', "teiid_excel:CELL_NUMBER" '2'),
                element_title01   string OPTIONS (SEARCHABLE 'Unsearchable', "teiid_excel:CELL_NUMBER" '4'),
                element_title02   string OPTIONS (SEARCHABLE 'Unsearchable', "teiid_excel:CELL_NUMBER" '5'),
                CONSTRAINT PK0 PRIMARY KEY(element_id)
            ) OPTIONS ("teiid_excel:FILE" 'my data.xlsx', "teiid_excel:FIRST_DATA_ROW_NUMBER" '2');
        </metadata>
    </model>

    <model name="data-view" type="VIRTUAL">
        <metadata type="DDL">

            CREATE VIEW my_view (
                element_id string,
                parent_element_id string,
                element_title string,
                CONSTRAINT PK0 PRIMARY KEY(element_id)
            ) OPTIONS (UPDATABLE TRUE) AS
            SELECT DISTINCT
                element_id,
                parent_element_id,
                element_title00
            FROM mymodel.Sheet1
            UNION ALL
            SELECT DISTINCT
                element_id,
                parent_element_id,
                element_title01
            FROM mymodel.Sheet1
            UNION ALL
            SELECT DISTINCT
                element_id,
                parent_element_id,
                element_title02
            FROM mymodel.Sheet1;
        </metadata>
    </model>
</vdb>

When I run a SELECT query via jdbc against the view my_view I run into a java.nio.channels.OverlappingFileLockException

My main app:

@SpringBootApplication
public class MainApplication implements CommandLineRunner {

    @Autowired
    TeiidServer server;

    public static void main(String[] args) {
        SpringApplication.run(MainApplication.class, args).close();
    }

    @Override
    public void run(String... args) throws Exception {

        deployVdb();

        Connection c = server.getDriver().connect("jdbc:teiid:MyVDB", null);
        JDBCUtils.execute(c, "SELECT * FROM my_view", true);
    }

    private void deployVdb() throws Exception {

        ExcelExecutionFactory factory = new ExcelExecutionFactory();
        factory.start();
        factory.setSupportsDirectQueryProcedure(true);
        server.addTranslator("excel", factory);

        FileManagedConnectionFactory managedconnectionFactory = new FileManagedConnectionFactory();
        managedconnectionFactory.setParentDirectory("src/main/resources/data");
        server.addConnectionFactory("java:/data", managedconnectionFactory.createConnectionFactory());

        server.deployVDB(MainApplication.class.getClassLoader().getResourceAsStream("my-vdb.xml"));
    }
}

JDBCUtils.execute(c, "SELECT * FROM my_view", true) just runs the query and print the result. You can find the JDBCUtils here

2019-12-05 11:58:37.399  INFO 12324 --- [           main] it.ithrowexceptions.MainApplication      : Starting MainApplication on PC1542 with PID 12324 (C:\Users\schiavi\Desktop\my-teiid-project\target\classes started by schiavi in C:\Users\schiavi\Desktop\my-teiid-project)
2019-12-05 11:58:37.406  INFO 12324 --- [           main] it.ithrowexceptions.MainApplication      : No active profile set, falling back to default profiles: default
2019-12-05 11:58:38.978  INFO 12324 --- [           main] o.t.s.a.TeiidAutoConfiguration           : Starting Teiid Server.
2019-12-05 11:58:40.511  INFO 12324 --- [           main] org.teiid.RUNTIME.VDBLifeCycleListener   : TEIID40118 VDB spring.1.0.0 added to the repository
2019-12-05 11:58:40.520  INFO 12324 --- [           main] org.teiid.RUNTIME.VDBLifeCycleListener   : TEIID40003 VDB spring.1.0.0 is set to ACTIVE
2019-12-05 11:58:40.556  INFO 12324 --- [           main] o.s.j.d.e.EmbeddedDatabaseFactory        : Starting embedded database: url='jdbc:teiid:spring;PassthroughAuthentication=true;useCallingThread=true;autoFailover=true;waitForLoad=5000;autoCommitTxn=OFF;disableLocalTxn=true', username='null'
2019-12-05 11:58:41.589  INFO 12324 --- [           main] o.hibernate.jpa.internal.util.LogHelper  : HHH000204: Processing PersistenceUnitInfo [name: default]
2019-12-05 11:58:41.625  INFO 12324 --- [           main] org.hibernate.Version                    : HHH000412: Hibernate Core {5.4.8.Final}
2019-12-05 11:58:41.799  INFO 12324 --- [           main] o.hibernate.annotations.common.Version   : HCANN000001: Hibernate Commons Annotations {5.1.0.Final}
2019-12-05 11:58:42.063  INFO 12324 --- [           main] org.hibernate.dialect.Dialect            : HHH000400: Using dialect: org.teiid.dialect.TeiidDialect
2019-12-05 11:58:42.522  INFO 12324 --- [           main] o.h.e.t.j.p.i.JtaPlatformInitiator       : HHH000490: Using JtaPlatform implementation: [org.hibernate.engine.transaction.jta.platform.internal.NoJtaPlatform]
2019-12-05 11:58:42.534  INFO 12324 --- [           main] j.LocalContainerEntityManagerFactoryBean : Initialized JPA EntityManagerFactory for persistence unit 'default'
2019-12-05 11:58:42.967  INFO 12324 --- [           main] o.t.spring.autoconfigure.TeiidServer     : Added file to the Teiid Database
2019-12-05 11:58:42.967  INFO 12324 --- [           main] org.teiid.RUNTIME.VDBLifeCycleListener   : TEIID40120 VDB spring.1.0.0 will be removed from the repository
2019-12-05 11:58:42.968  INFO 12324 --- [           main] org.teiid.RUNTIME.VDBLifeCycleListener   : TEIID40119 VDB spring.1.0.0 removed from the repository
2019-12-05 11:58:42.991  INFO 12324 --- [           main] org.teiid.RUNTIME.VDBLifeCycleListener   : TEIID40118 VDB spring.1.0.0 added to the repository
2019-12-05 11:58:42.994  INFO 12324 --- [           main] org.teiid.RUNTIME                        : TEIID50029 VDB spring.1.0.0 model "file" metadata is currently being loaded. Start Time: 12/5/19 11:58 AM
2019-12-05 11:58:42.998  INFO 12324 --- [           main] org.teiid.RUNTIME                        : TEIID50030 VDB spring.1.0.0 model "file" metadata loaded. End Time: 12/5/19 11:58 AM
2019-12-05 11:58:42.999  INFO 12324 --- [           main] org.teiid.RUNTIME.VDBLifeCycleListener   : TEIID40003 VDB spring.1.0.0 is set to ACTIVE
2019-12-05 11:58:42.999  INFO 12324 --- [           main] o.t.s.autoconfigure.TeiidPostProcessor   : Non JDBC Datasource added to Teiid = file
2019-12-05 11:58:43.029  INFO 12324 --- [           main] it.ithrowexceptions.MainApplication      : Started MainApplication in 6.299 seconds (JVM running for 7.565)
2019-12-05 11:58:43.480  INFO 12324 --- [           main] org.teiid.RUNTIME.VDBLifeCycleListener   : TEIID40118 VDB MyVDB.1 added to the repository
2019-12-05 11:58:43.481  INFO 12324 --- [           main] org.teiid.RUNTIME                        : TEIID50029 VDB MyVDB.1 model "mymodel" metadata is currently being loaded. Start Time: 12/5/19 11:58 AM
2019-12-05 11:58:43.484  INFO 12324 --- [           main] org.teiid.RUNTIME                        : TEIID50030 VDB MyVDB.1 model "mymodel" metadata loaded. End Time: 12/5/19 11:58 AM
2019-12-05 11:58:43.485  INFO 12324 --- [           main] org.teiid.RUNTIME                        : TEIID50029 VDB MyVDB.1 model "data-view" metadata is currently being loaded. Start Time: 12/5/19 11:58 AM
2019-12-05 11:58:43.486  INFO 12324 --- [           main] org.teiid.RUNTIME                        : TEIID50030 VDB MyVDB.1 model "data-view" metadata loaded. End Time: 12/5/19 11:58 AM
2019-12-05 11:58:43.491  INFO 12324 --- [           main] org.teiid.RUNTIME.VDBLifeCycleListener   : TEIID40003 VDB MyVDB.1 is set to ACTIVE
SQL: SELECT * FROM my_view
2019-12-05 11:58:43.556 ERROR 12324 --- [ProcessorQueue2] org.teiid.CONNECTOR                      : Connector worker process failed for atomic-request=QLbbcdeqDIOx.0.7.2

java.nio.channels.OverlappingFileLockException: null
    at sun.nio.ch.SharedFileLockTable.checkList(FileLockTable.java:255) ~[na:1.8.0_231]
    at sun.nio.ch.SharedFileLockTable.add(FileLockTable.java:152) ~[na:1.8.0_231]
    at sun.nio.ch.FileChannelImpl.tryLock(FileChannelImpl.java:1107) ~[na:1.8.0_231]
    at org.teiid.file.JavaVirtualFile.openInputStream(JavaVirtualFile.java:74) ~[file-api-12.3.0.jar:12.3.0]
    at org.teiid.translator.excel.BaseExcelExecution.readXLSFile(BaseExcelExecution.java:101) ~[translator-excel-12.3.0.jar:12.3.0]
    at org.teiid.translator.excel.BaseExcelExecution.execute(BaseExcelExecution.java:97) ~[translator-excel-12.3.0.jar:12.3.0]
    at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:402) ~[teiid-engine-12.3.0.jar:12.3.0]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_231]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_231]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_231]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_231]
    at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:228) [teiid-engine-12.3.0.jar:12.3.0]
    at com.sun.proxy.$Proxy70.execute(Unknown Source) [na:na]
    at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:302) [teiid-engine-12.3.0.jar:12.3.0]
    at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108) [teiid-engine-12.3.0.jar:12.3.0]
    at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:104) [teiid-engine-12.3.0.jar:12.3.0]
    at java.util.concurrent.FutureTask.run(FutureTask.java:266) [na:1.8.0_231]
    at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:59) [teiid-engine-12.3.0.jar:12.3.0]
    at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:281) [teiid-engine-12.3.0.jar:12.3.0]
    at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:124) [teiid-engine-12.3.0.jar:12.3.0]
    at org.teiid.dqp.internal.process.ThreadReuseExecutor$2.run(ThreadReuseExecutor.java:212) [teiid-engine-12.3.0.jar:12.3.0]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [na:1.8.0_231]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [na:1.8.0_231]
    at java.lang.Thread.run(Thread.java:748) [na:1.8.0_231]

2019-12-05 11:58:43.559 ERROR 12324 --- [ProcessorQueue0] org.teiid.CONNECTOR                      : Connector worker process failed for atomic-request=QLbbcdeqDIOx.0.3.0

java.nio.channels.OverlappingFileLockException: null
    at sun.nio.ch.SharedFileLockTable.checkList(FileLockTable.java:255) ~[na:1.8.0_231]
    at sun.nio.ch.SharedFileLockTable.add(FileLockTable.java:152) ~[na:1.8.0_231]
    at sun.nio.ch.FileChannelImpl.tryLock(FileChannelImpl.java:1107) ~[na:1.8.0_231]
    at org.teiid.file.JavaVirtualFile.openInputStream(JavaVirtualFile.java:74) ~[file-api-12.3.0.jar:12.3.0]
    at org.teiid.translator.excel.BaseExcelExecution.readXLSFile(BaseExcelExecution.java:101) ~[translator-excel-12.3.0.jar:12.3.0]
    at org.teiid.translator.excel.BaseExcelExecution.execute(BaseExcelExecution.java:97) ~[translator-excel-12.3.0.jar:12.3.0]
    at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:402) ~[teiid-engine-12.3.0.jar:12.3.0]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_231]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_231]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_231]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_231]
    at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:228) [teiid-engine-12.3.0.jar:12.3.0]
    at com.sun.proxy.$Proxy70.execute(Unknown Source) [na:na]
    at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:302) [teiid-engine-12.3.0.jar:12.3.0]
    at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108) [teiid-engine-12.3.0.jar:12.3.0]
    at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:104) [teiid-engine-12.3.0.jar:12.3.0]
    at java.util.concurrent.FutureTask.run(FutureTask.java:266) [na:1.8.0_231]
    at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:59) [teiid-engine-12.3.0.jar:12.3.0]
    at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:281) [teiid-engine-12.3.0.jar:12.3.0]
    at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:124) [teiid-engine-12.3.0.jar:12.3.0]
    at org.teiid.dqp.internal.process.ThreadReuseExecutor$2.run(ThreadReuseExecutor.java:212) [teiid-engine-12.3.0.jar:12.3.0]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [na:1.8.0_231]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [na:1.8.0_231]
    at java.lang.Thread.run(Thread.java:748) [na:1.8.0_231]

2019-12-05 11:58:43.563  WARN 12324 --- [           main] org.teiid.PROCESSOR                      : TEIID30020 Processing exception for request QLbbcdeqDIOx.0 'TEIID30504 xlsdata: null'. Originally TeiidProcessingException FileLockTable.java:255. Enable more detailed logging to see the entire stacktrace.
org.teiid.jdbc.TeiidSQLException: TEIID30504 xlsdata: null
    at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:131)
    at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:67)
    at org.teiid.jdbc.StatementImpl.postReceiveResults(StatementImpl.java:783)
    at org.teiid.jdbc.StatementImpl.access$100(StatementImpl.java:62)
    at org.teiid.jdbc.StatementImpl$2.onCompletion(StatementImpl.java:581)
    at org.teiid.client.util.ResultsFuture.addCompletionListener(ResultsFuture.java:144)
    at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:577)
    at org.teiid.jdbc.StatementImpl.execute(StatementImpl.java:1119)
    at org.teiid.jdbc.StatementImpl.execute(StatementImpl.java:336)
    at it.ithrowexceptions.JDBCUtils.execute(JDBCUtils.java:59)
    at it.ithrowexceptions.MainApplication.run(MainApplication.java:29)
    at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:784)
    at org.springframework.boot.SpringApplication.callRunners(SpringApplication.java:768)
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:322)
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1226)
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1215)
    at it.ithrowexceptions.MainApplication.main(MainApplication.java:20)
Caused by: org.teiid.core.TeiidProcessingException: TEIID30504 xlsdata: null
    at org.teiid.dqp.internal.process.DataTierTupleSource.exceptionOccurred(DataTierTupleSource.java:397)
    at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:157)
    at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:401)
    at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
    at org.teiid.query.processor.relational.DupRemoveNode.nextBatchDirect(DupRemoveNode.java:58)
    at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
    at org.teiid.query.processor.relational.UnionAllNode.nextBatchDirectInternal(UnionAllNode.java:165)
    at org.teiid.query.processor.relational.UnionAllNode.nextBatchDirect(UnionAllNode.java:138)
    at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
    at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:139)
    at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:147)
    at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:110)
    at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
    at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:142)
    at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:492)
    at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:362)
    at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:43)
    at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:285)
    at org.teiid.dqp.internal.process.DQPCore.executeRequest(DQPCore.java:361)
    at org.teiid.dqp.internal.process.DQPCore.executeRequest(DQPCore.java:276)
    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.teiid.logging.LogManager$LoggingProxy.invoke(LogManager.java:119)
    at org.teiid.transport.SessionCheckingProxy.invoke(SessionCheckingProxy.java:60)
    at com.sun.proxy.$Proxy48.executeRequest(Unknown Source)
    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.teiid.transport.LocalServerConnection$1$1.call(LocalServerConnection.java:212)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:281)
    at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:265)
    at org.teiid.transport.LocalServerConnection$1.invoke(LocalServerConnection.java:210)
    at com.sun.proxy.$Proxy48.executeRequest(Unknown Source)
    at org.teiid.jdbc.StatementImpl.execute(StatementImpl.java:745)
    at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:575)
    ... 10 more
Caused by: org.teiid.translator.TranslatorException
    at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.handleError(ConnectorWorkItem.java:342)
    at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:405)
    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.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:228)
    at com.sun.proxy.$Proxy70.execute(Unknown Source)
    at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:302)
    at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
    at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:104)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:59)
    at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:281)
    at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:124)
    at org.teiid.dqp.internal.process.ThreadReuseExecutor$2.run(ThreadReuseExecutor.java:212)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:748)
Caused by: java.nio.channels.OverlappingFileLockException
    at sun.nio.ch.SharedFileLockTable.checkList(FileLockTable.java:255)
    at sun.nio.ch.SharedFileLockTable.add(FileLockTable.java:152)
    at sun.nio.ch.FileChannelImpl.tryLock(FileChannelImpl.java:1107)
    at org.teiid.file.JavaVirtualFile.openInputStream(JavaVirtualFile.java:74)
    at org.teiid.translator.excel.BaseExcelExecution.readXLSFile(BaseExcelExecution.java:101)
    at org.teiid.translator.excel.BaseExcelExecution.execute(BaseExcelExecution.java:97)
    at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:402)
    ... 17 more

If I declare the view my_view with no UNION ALL (just a single SELECT), the query would run smoothly.

Why does this happen? How should I implement the vdb?

score 0 · Answer 1 · answered Dec 07 '19 at 01:27

0

Why not use the Teiid Spring Starter provided way to read the Excel source using the Teiid. See an example here [1].

But if you want you to use the VDB with Spring Boot Starter, you can do that too. You need to use the dependencies from Teiid Spring Boot.

[1] https://github.com/teiid/teiid-spring-boot/tree/master/samples/excel

answered Dec 07 '19 at 01:27

Ramesh Reddy

554
1
3
8

Thank you for your kind answer. What I am really missing here is why a `SELECT` with `UNION` query against a model on an Excel data source causes an `OverlappingFileLockException`. What should I do with a vdb model on an Excel file to work on its data? Should I insert its data in another table? Should I 'cache' them somehow? I am sorry if I sound too naif, I am just a beginner, and I cannot find an example suiting my needs. – ithrowexceptions Dec 09 '19 at 10:06
P.s.: I tried to use the facilities that teiid-spring-boot-starter offers, and made an attempt using entities annotated with `@ExcelTable` and `@SelectQuery`, but I end with the same exception. – ithrowexceptions Dec 09 '19 at 10:06
Can you share your project, I will see whats going on? – Ramesh Reddy Dec 09 '19 at 15:43
Shared [here](https://github.com/ithrowexceptions/my-teiid-starter-project). The exception is not always thrown, sometimes the program ends properly, sometimes it doesn't. About my original question, see my answer (I'd gladly know if I'm doing right). And thank you very much for your attention – ithrowexceptions Dec 10 '19 at 10:30

score 0 · Accepted Answer · answered Dec 10 '19 at 08:44

I circumvented the issue by adding a materialized view on the Excel data source. The vdb now is implemented as:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<vdb name="MyVDB" version="1">

    <model name="mymodel" type="PHYSICAL" visible="true">
        <property name="importer.headerRowNumber" value="1"/>
        <property name="importer.dataRowNumber" value="2"/>
        <property name="importer.excelFileName" value="my data.xlsx"/>
        <source connection-jndi-name="java:/data" name="xlsdata" translator-name="excel"/>
        <metadata type="DDL">

            CREATE FOREIGN TABLE Sheet1 (
                element_id        string OPTIONS (SEARCHABLE 'Unsearchable', "teiid_excel:CELL_NUMBER" '1'),
                parent_element_id string OPTIONS (SEARCHABLE 'Unsearchable', "teiid_excel:CELL_NUMBER" '3'),
                element_title00   string OPTIONS (SEARCHABLE 'Unsearchable', "teiid_excel:CELL_NUMBER" '2'),
                element_title01   string OPTIONS (SEARCHABLE 'Unsearchable', "teiid_excel:CELL_NUMBER" '4'),
                element_title02   string OPTIONS (SEARCHABLE 'Unsearchable', "teiid_excel:CELL_NUMBER" '5'),
                CONSTRAINT PK0 PRIMARY KEY(element_id)
            ) OPTIONS ("teiid_excel:FILE" 'my data.xlsx', "teiid_excel:FIRST_DATA_ROW_NUMBER" '2');
        </metadata>
    </model>

    <model name="data-view" type="VIRTUAL">
        <metadata type="DDL">

            CREATE VIEW data_view (
                element_id        string PRIMARY KEY,
                parent_element_id string,
                element_title00   string,
                element_title01   string,
                element_title02   string
            ) OPTIONS (MATERIALIZED TRUE) AS
            SELECT
                element_id,
                parent_element_id,
                element_title00,
                element_title01,
                element_title02
            FROM mymodel.Sheet1;
        </metadata>
    </model>

    <model name="my-view" type="VIRTUAL">
        <metadata type="DDL">

            CREATE VIEW my_view (
                element_id string,
                parent_element_id string,
                element_title string,
                CONSTRAINT PK0 PRIMARY KEY(element_id)
            ) OPTIONS (UPDATABLE TRUE) AS
            SELECT DISTINCT
                element_id,
                parent_element_id,
                element_title00
            FROM data_view
            WHERE element_title00 IS NOT NULL AND element_title00 NOT LIKE '' AND (element_title01 IS NULL OR element_title01 LIKE '')
            UNION ALL
            SELECT DISTINCT
                element_id,
                parent_element_id,
                element_title01
            FROM data_view
            WHERE (element_title01 IS NOT NULL AND element_title01 NOT LIKE '') AND (element_title02 IS NULL OR element_title02 LIKE '')
            UNION ALL
            SELECT DISTINCT
                element_id,
                parent_element_id,
                element_title02
            FROM data_view
            WHERE (element_title02 IS NOT NULL AND element_title02 NOT LIKE '');
        </metadata>
    </model>
</vdb>

sure, this will work as long as the above exception did not occur very first time you are trying to read the excel sheet, then your excel sheet is not changing in content. if it is changing, then you can also consider setting a TTL on materialization to load in a regular intervel. — Ramesh Reddy, Dec 10 '19 at 13:13

score 0 · Answer 3 · answered Jan 13 '20 at 18:07

0

This exception was captured as a Teiid issue: https://issues.redhat.com/browse/TEIID-5877

answered Jan 13 '20 at 18:07

Steven Hawkins

538
1
4
7

java.nio.channels.OverlappingFileLockException with Teiid Excel data source

3 Answers3