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?
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>