Invalid query - insert into BigQuery table

Help Activities
, ,
1

Hello!
I’m trying to use Insert activity to upload datatable into BigQuery table. I’m using Connect to database to set connection. Then I tried:

  • Building dt in workflow, then insert it → error.
  • Creating excel file, reading it, inserting → error.
  • Creating excel file, reading it, substituting column names with names from BigQuery table to make them identical → error.
  • I even tried to download table from GCP directly, and then upload those same values with Insert → still same error…
    Still getting this one:
    Insert: ERROR [42000] [Simba][BigQuery] (70) Invalid query: Syntax error: SELECT list must not be empty at [1:9]

What am I doing wrong? Normal insert row by row, like with query is working fine, but activity “Insert” keeps failing.

image
image1196×242 10.8 KB

2

@olgu

Looks like some data is expected and it is missing in the provided datatable…

can you open the exception details from locals panel when you run in debug mode and check

cheers

3

Exception details has the same message, nothing more useful.
I created another dummy table, only two string type nullable columns, simple names. Recreated it in Studio and try to Insert.
Same error, but with ending [1:17]

4

@olgu

Can you show your Datatable…is any of the column havign empty values?

cheers

5

image
image1207×965 39.3 KB

Only two values in excel, one per column, read properly in studio. But still having issue while trying to insert such small data.

6

1/ I would recommend to change name of this topic to indicate it is about insert to BigQuery
2/ From your desription I understand that you can read from BigQuery table but can not insert to it → it might be a problem of Insert activity implementation

BTW - Did you try search forum for BigQuery related topics?

Cheers

7

Hello J0ska,

  1. Thank you, I changed the topic.
  2. Yes, exactly. Reading, executing queries - everything is working perfect, except Insert activity
8

Why aren’t you using Bulk Insert?

9

@olgu

A quick question …are you suing odbc drivers? or oledb?

cheers

10

Hello Anil_G, It’s odbc driver

11

It’s not working - same as Insert

12

@olgu

Then as a next step can you try changing the version of your database activities and check…looks like the statement generated by the activity is wrong and it is failing

cheers

13

I would look in the database logs to see if you can find what it doesn’t like about the database query. I would also try a simple insert to see if it works. If it does, then the issue may be in your data. Special characters in a column name or something.

14
  1. Checked all of the available packages of Database package - no effect.
  2. Simple insert with query is working. I’m operating on the most simple example, one string type column, named column1 - and even this is failing.
  3. Also there isn’t any log in database regarding this one, like it’s not even executed.
15

Hi @olgu ,

I am facing the similar issue. Do you have any resolution in hand?

It would be very helpful if you share me some inputs on this.

thank you :slight_smile: