1 Reply Latest reply on Sep 18, 2017 1:13 PM by shawkins

    Does teiid support bulk loader/inserts in database

    kulbhushanc
    kulbhushanc Sep 18, 2017 9:51 AM

      Hi,

       

      I am using admin api teiid 9.1.3 and WildFly JBoss 10.0.0

       

      I am trying to load/insert data into mysql database using LOAD DATA LOCAL INFILE ... utility. but failed with the exception.

       

      Query in java Program:

      String select_par_sql = "LOAD DATA LOCAL INFILE 'c:\\test_data.csv' INTO TABLE test FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\r\n';"

       

      Stack Trace :

      Used values{SQL = LOAD DATA LOCAL INFILE 'c:\test_data.csv'INTO TABLE testFIELDS TERMINATED BY ','ENCLOSED BY '"'LINES TERMINATED BY '

      ';}

      Remote org.teiid.api.exception.query.QueryParserException: TEIID31100 Parsing error: Encountered "[*]LOAD[*] DATA LOCAL" at line 1, column 1.

      Was expecting: "alter" | "begin" | "call" | "create" | "delete" | "drop" | "exec" | "execute" | "insert" | "merge" ...org.teiid.jdbc.TeiidSQLException: Remote org.teiid.api.exception.query.QueryParserException: TEIID31100 Parsing error: Encountered "[*]LOAD[*] DATA LOCAL" at line 1, column 1.

      Was expecting: "alter" | "begin" | "call" | "create" | "delete" | "drop" | "exec" | "execute" | "insert" | "merge" ...

      at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:135)

      at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:71)

      at org.teiid.jdbc.StatementImpl.postReceiveResults(StatementImpl.java:703)

      at org.teiid.jdbc.StatementImpl.access$100(StatementImpl.java:64)

      at org.teiid.jdbc.StatementImpl$2.onCompletion(StatementImpl.java:542)

      at org.teiid.client.util.ResultsFuture.done(ResultsFuture.java:135)

      at org.teiid.client.util.ResultsFuture.access$200(ResultsFuture.java:40)

      at org.teiid.client.util.ResultsFuture$1.receiveResults(ResultsFuture.java:79)

      at org.teiid.net.socket.SocketServerInstanceImpl.receivedMessage(SocketServerInstanceImpl.java:268)

      at org.teiid.net.socket.SocketServerInstanceImpl.read(SocketServerInstanceImpl.java:306)

      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:497)

      at org.teiid.net.socket.SocketServerConnectionFactory$ShutdownHandler.invoke(SocketServerConnectionFactory.java:98)

      at com.sun.proxy.$Proxy1.read(Unknown Source)

      at org.teiid.net.socket.SocketServerInstanceImpl$RemoteInvocationHandler$1.get(SocketServerInstanceImpl.java:405)

      at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:551)

      at org.teiid.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1088)

      at org.teiid.jdbc.StatementImpl.executeUpdate(StatementImpl.java:344)

      at com.bitwise.mySql.WithTeiid.execute(WithTeiid.java:546)

      at com.bitwise.mySql.WithTeiid.executeSelect(WithTeiid.java:159)

      at com.bitwise.mySql.WithTeiid.main(WithTeiid.java:83)

      Caused by: org.teiid.core.TeiidProcessingException: Remote org.teiid.api.exception.query.QueryParserException: TEIID31100 Parsing error: Encountered "[*]LOAD[*] DATA LOCAL" at line 1, column 1.

      Was expecting: "alter" | "begin" | "call" | "create" | "delete" | "drop" | "exec" | "execute" | "insert" | "merge" ...

      at org.teiid.query.parser.QueryParser.convertParserException(QueryParser.java:214)

      at org.teiid.query.parser.QueryParser.parseCommand(QueryParser.java:164)

      at org.teiid.query.parser.QueryParser.parseCommand(QueryParser.java:140)

      at org.teiid.dqp.internal.process.Request.parseCommand(Request.java:306)

      at org.teiid.dqp.internal.process.Request.generatePlan(Request.java:407)

      at org.teiid.dqp.internal.process.Request.processRequest(Request.java:481)

      at org.teiid.dqp.internal.process.RequestWorkItem.processNew(RequestWorkItem.java:657)

      at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:338)

      at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51)

      at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:275)

      at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:282)

      at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)

      at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)

      at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)

      at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)

      at java.lang.Thread.run(Thread.java:745)

       

      Thanks,

      Kulbhushan Chaskar

        • 1. Re: Does teiid support bulk loader/inserts in database
          shawkins
          shawkins Sep 18, 2017 1:13 PM (in response to kulbhushanc)

          We don't support a specific bulk loading syntax as part of the Teiid dialect.  There is the texttable function for converting between csv and tabular, which can then be inserted into a target table.  For most sources these inserts don't involve special bulk load logic, but rather use standard statement/parameter batching.

           

          We will likely be looking to add more bulk loading features in the near future.  If there is a particular case that you would like to see supported, please log an enhancement issue.

            • Actions