Microsoft Excel para SQL usando Pentaho: erro de sintaxe em 'OPTION SQL_SELECT_LIMIT = DEFAULT'

Question

Estou usando o Pentaho Kettle versão 5.1 para transferir dados do Excel para o SQL seguindo estas etapas (respondidas por < um href="https://superuser.com/users/55493/edgar"> Edgar ):

1. Launch Pentaho Kettle.
2. (Typically) click on 'No Repository'.
3. Create a new transformation.
4. On the left side you will see several 'blocks'. In this case, drag an 'Excel Input' block from the 'Input' category into your transformation file.
5. Double-click on the block you just dragged. The next steps refer to this block:
   • Click 'Browse' and select the Excel file you want to import.
   • Click on 'Add' (located to the left of the 'Browse' button). You should see the file listed on the 'Selected Files' list underneath.
   • Now go to the '!Sheets' tab (should be the second tab) and select the worksheet with the data you want to import into your database.
   • Finally, go the '!Fields' tab (should be the last to the right), click on the button labeled 'Get fields from header row...' and change the different datatypes accordingly.
   • Click on the button labeled 'Preview rows' to make sure everything is OK.
6. Now, let's move this data to your SQL database. For simplicity, I'm going to describe how to create a new table on the database. However, notice that Kettle provide blocks for handling updates and 'insert/update' tasks.
7. Once again, drag a block from the left hand side. This time go to the 'Output' section and select 'Table Output'.
8. Connect the two blocks by holding the Shift key and dragging the mouse pointer from the from the Excel to the 'Table Output' block. The next steps are related to the 'Table Output' block.
   • First, we need to build a connection to the database. To do this, click on the button labeled 'New...' next to the 'Connection' drop-down box.
   • On the 'Connection Type' list, select 'MySQL' (as you can see, Kettle allows you to connect with a vast number of different databases). Fill all of the appropriate fields and remember to click on 'Test' to ensure the communication is working.
   • Enter the name of the table.
   • Go the tab labeled 'Database fields' and select 'Enter field mapping'. From here make sure you map all of the Excel columns in the stream to the appropriate MySQL fields and click 'OK'. (If the names in Excel match your table you can simply click on 'Guess' to get you close.)
9. Finally, Save your transformation and run it by selecting the 'Transformation>Run' menu. If everything goes well you should see a message that says 'Transformation Finished' on the execution box under the 'Logging' tab.

Good Luck!

No entanto, não consigo realizar o passo 8 devido a esta mensagem de erro:

I was unable to connect to the database to verify the status of the table: Unable to close prepared statement after determining SQL layout. You have an error in you SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OPTION SQL_SELECT_LIMIT=DEFAULT' at line 1

Atualmente estou usando o MySQL Server versão 5.6 e o MySQL workbench versão 6.1. Como posso resolver esse erro? Eu não posso continuar com a nossa tese por causa deste problema.

    

Answer 1

O problema que você está enfrentando é o uso de MySQL Connector / J mais ou menos incompatíveis, Verifique se você está usando o mais recente mysql-connector-java-x.x.xx-bin.jar ?

No meu caso eu estava usando o MySQL Server 5.6 e o conector MySQL era 'mysql-connector-java-5.1.42-bin.jar'

1. Faça o download do mais recente conector / driver do MySQL Java
2. Descompacte o arquivo zip
3. copie o arquivo .jar e cole-o na sua pasta Lib:

PC: C: \ Arquivos de Programas \ pentaho \ design-tools \ data-integration \ lib

Mac: / Aplicativos / integração de dados / lib

Reinicie o Pentaho (Integração de Dados) e execute sua transformação.