search

dbeaver / dbeaver

Free universal database tool and SQL client
https://dbeaver.io
Apache License 2.0
40.26k stars 3.48k forks source link

BigQuery Create DDL wrong #10011

Open tpcgold opened 4 years ago

tpcgold commented 4 years ago

System information:

Connection specification:

Describe the problem you're observing:

The created SQL statement in DBeaver Tasks to migrate Tables from MySQL and Create Table on BigQuery is wrong Target DDL shows:

CREATE TABLE 'project'.dataset.table( a timestamp NOT NULL, b INT64 NOT NULL, c INT64, d GEOGRAPHY(65535) ); but should be (note the single ` after project above and below after table)( ' is used due to markdown compatibility instead of ` ): CREATE TABLE 'project.dataset.table'( a timestamp NOT NULL, b INT64 NOT NULL, c INT64, d GEOGRAPHY(65535) );

Steps to reproduce, if exist:

Create a task that needs to create a table on BigQuery

Include any warning/errors/backtraces from the logs

`2020-10-08 10:37:28.648 - Data transfer initiated 2020-10-08 10:37:28.648 - Initialize data transfer sources 2020-10-08 10:37:28.648 - Initialize pipes 2020-10-08 10:37:28.648 - Create necessary database objects 2020-10-08 10:37:28.648 - Create table 'acdactions' 2020-10-08 10:37:28.648 - Create target metadata 2020-10-08 10:37:28.648 - Create new table 2020-10-08 10:37:28.945 - Error executing query org.jkiss.dbeaver.model.sql.DBSQLException: SQL Error [100032] [HY000]: [Simba][BigQueryJDBCDriver](100032) Error executing query job. Message: Syntax error: Expected ")" or "," but got "(" at [7:36] at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:133) at org.jkiss.dbeaver.model.exec.DBExecUtils.executePersistAction(DBExecUtils.java:311) at org.jkiss.dbeaver.model.exec.DBExecUtils.executeScript(DBExecUtils.java:267) at org.jkiss.dbeaver.tools.transfer.database.DatabaseTransferUtils.executeDDL(DatabaseTransferUtils.java:305) at org.jkiss.dbeaver.tools.transfer.database.DatabaseTransferConsumer.createTargetTable(DatabaseTransferConsumer.java:543) at org.jkiss.dbeaver.tools.transfer.database.DatabaseTransferConsumer.createTargetDatabaseObjects(DatabaseTransferConsumer.java:500) at org.jkiss.dbeaver.tools.transfer.database.DatabaseTransferConsumer.startTransfer(DatabaseTransferConsumer.java:469) at org.jkiss.dbeaver.tools.transfer.task.DTTaskHandlerTransfer.lambda$1(DTTaskHandlerTransfer.java:79) at org.jkiss.dbeaver.registry.task.TaskRunJob.run(TaskRunJob.java:124) at org.jkiss.dbeaver.tools.transfer.task.DTTaskHandlerTransfer.executeWithSettings(DTTaskHandlerTransfer.java:72) at org.jkiss.dbeaver.tools.transfer.task.DTTaskHandlerTransfer.executeTask(DTTaskHandlerTransfer.java:61) at org.jkiss.dbeaver.registry.task.TaskRunJob.executeTask(TaskRunJob.java:119) at org.jkiss.dbeaver.registry.task.TaskRunJob.run(TaskRunJob.java:88) at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:105) at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63) Caused by: java.sql.SQLException: [Simba][BigQueryJDBCDriver](100032) Error executing query job. Message: Syntax error: Expected ")" or "," but got "(" at [7:36] at com.simba.googlebigquery.googlebigquery.client.BQClient.insertJob(Unknown Source) at com.simba.googlebigquery.googlebigquery.client.BQClient.executeQuery(Unknown Source) at com.simba.googlebigquery.googlebigquery.dataengine.BQAbstractExecutor.execute(Unknown Source) at com.simba.googlebigquery.googlebigquery.dataengine.BQSQLExecutor.execute(Unknown Source) at com.simba.googlebigquery.jdbc.common.SStatement.executeNoParams(Unknown Source) at com.simba.googlebigquery.jdbc.common.SStatement.execute(Unknown Source) at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.execute(JDBCStatementImpl.java:327) at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:130) at org.jkiss.dbeaver.model.exec.DBExecUtils.executePersistAction(DBExecUtils.java:311) at org.jkiss.dbeaver.model.exec.DBExecUtils.executeScript(DBExecUtils.java:267) at org.jkiss.dbeaver.tools.transfer.database.DatabaseTransferUtils.executeDDL(DatabaseTransferUtils.java:305) at org.jkiss.dbeaver.tools.transfer.database.DatabaseTransferConsumer.createTargetTable(DatabaseTransferConsumer.java:543) at org.jkiss.dbeaver.tools.transfer.database.DatabaseTransferConsumer.createTargetDatabaseObjects(DatabaseTransferConsumer.java:500) at org.jkiss.dbeaver.tools.transfer.database.DatabaseTransferConsumer.startTransfer(DatabaseTransferConsumer.java:469) at org.jkiss.dbeaver.tools.transfer.task.DTTaskHandlerTransfer.lambda$1(DTTaskHandlerTransfer.java:79) at org.jkiss.dbeaver.registry.task.TaskRunJob.run(TaskRunJob.java:124) at org.jkiss.dbeaver.tools.transfer.task.DTTaskHandlerTransfer.executeWithSettings(DTTaskHandlerTransfer.java:72) at org.jkiss.dbeaver.tools.transfer.task.DTTaskHandlerTransfer.executeTask(DTTaskHandlerTransfer.java:61) at org.jkiss.dbeaver.registry.task.TaskRunJob.executeTask(TaskRunJob.java:119) at org.jkiss.dbeaver.registry.task.TaskRunJob.run(TaskRunJob.java:88) at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:105) Caused by: com.simba.googlebigquery.support.exceptions.GeneralException: [Simba][BigQueryJDBCDriver](100032) Error executing query job. Message: Syntax error: Expected ")" or "," but got "(" at [7:36] ... 21 more 2020-10-08 10:39:08.692 - Refresh navigator model 2020-10-08 10:39:08.692 - Load items ... 2020-10-08 10:39:08.692 - Load Tables 2020-10-08 10:39:08.692 - Load items ... 2020-10-08 10:39:08.692 - Load Table 2020-10-08 10:39:09.458 - Load TableCache 2020-10-08 10:39:09.458 - Load Views 2020-10-08 10:39:09.458 - Load Indexes 2020-10-08 10:39:09.458 - Load Procedures 2020-10-08 10:39:09.458 - Load Data Types 2020-10-08 10:39:09.458 - Task fatal error org.jkiss.dbeaver.DBException: Error starting data transfer at org.jkiss.dbeaver.tools.transfer.task.DTTaskHandlerTransfer.executeWithSettings(DTTaskHandlerTransfer.java:89) at org.jkiss.dbeaver.tools.transfer.task.DTTaskHandlerTransfer.executeTask(DTTaskHandlerTransfer.java:61) at org.jkiss.dbeaver.registry.task.TaskRunJob.executeTask(TaskRunJob.java:119) at org.jkiss.dbeaver.registry.task.TaskRunJob.run(TaskRunJob.java:88) at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:105) at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63) Caused by: org.jkiss.dbeaver.model.exec.DBCException: New table acdactions not found in container speedcom at org.jkiss.dbeaver.tools.transfer.database.DatabaseTransferUtils.refreshDatabaseModel(DatabaseTransferUtils.java:63) at org.jkiss.dbeaver.tools.transfer.database.DatabaseTransferConsumer.startTransfer(DatabaseTransferConsumer.java:472) at org.jkiss.dbeaver.tools.transfer.task.DTTaskHandlerTransfer.lambda$1(DTTaskHandlerTransfer.java:79) at org.jkiss.dbeaver.registry.task.TaskRunJob.run(TaskRunJob.java:124) at org.jkiss.dbeaver.tools.transfer.task.DTTaskHandlerTransfer.executeWithSettings(DTTaskHandlerTransfer.java:72) ... 5 more `
uslss commented 4 years ago

Thanks for the bug report

tpcgold commented 4 years ago

In addition, there is no GEOGRAPHY(65535) type in BigQuery, only GEOGRAPHY is enough

serge-rider commented 3 years ago

DBeaver doesn't support BigQuery DDL syntax, as well as many other BigQuery aspects (partitioning, datasets, etc). We plan to add proper BigQuery extensions (similar to BigTable) closer to version 8.0.