Google BigQuery

Google BigQuery is a fully-managed, serverless data warehouse that enables scalable analysis over petabytes of data.

Google BigQuery-Specific Features and Limitations

Since Google BigQuery does not have primary or unique keys, Skyvia has the following limitations for Google BigQuery:

  • Synchronization is not supported for Google BigQuery.
  • UPSERT operation in Import is not supported for Google BigQuery.
  • When performing import with the UPDATE or DELETE operation, you need to manually specify columns, which will be considered a primary key.

Google BigQuery Connections

To create a Google BigQuery connection, perform the following steps:

  1. Click +NEW in the top menu.
  2. Open the Select Connector page by clicking Connection in the menu on the left.
  3. In the opened pane, click Google BigQuery.

  4. In the Connection Editor page, in the Untitled field, specify a connection name that will be used to identify the connection.

    Connection Editor window

  5. Click Sign In with Google.

  6. In the opened window, enter your email or phone and click Next.

    Sign In window

  7. Enter your password and click Next once again.

    Sign In window 2

  8. Click the Allow button.

    Allow Access window

  9. Specify the Project Id and DataSet Id to connect to.
  10. Also specify the name of the bucket (Cloud Storage Bucket) that will be used for temporary upload of CSV files when performing Import or Replication to Google BigQuery. The uploaded files will be deleted after the corresponding operations are finished.

  11. You can also optionally click Advanced Settings and set the Command Timeout interval. It specifies the wait time before terminating an attempt to execute a command and generating an error. Note that it is the time to wait for any server reply since the command was sent to a server, and it doesn’t include the time necessary to fetch data if the command selects some data.

  12. Here you can also optionally enable the use of legacy SQL syntax by selecting the Use Legacy SQL checkbox. Note, that the Incremental Replication does not support the legacy SQL syntax and may return the syntax error when this checkbox is selected.

  13. In Advanced Settings, you can also disable bulk import by clearing the Use Bulk Import checkbox. This setting affects only import packages with the INSERT, UPDATE, and DELETE operation and with BigQuery as a target.

    By default, such packages import data in the following way: Skyvia writes data into multiple temporary CSV files, upload them to Google Cloud Storage and then tells Google BigQuery to import data from these CSV files. These actions are performed simultaneously, providing the best performance. After the CSV files are imported, they are deleted. However, when data are imported this way, it’s not possible to obtain a per-record error log.

    If you disable bulk import, Skyvia will use the corresponding DML statements for importing data. This allows you to obtain a per-record error log, but provides far less performance. Thus, disabling bulk import for BigQuery is not recommended and should only be considered if you need to import a small amount of data and need to have a per-record error log or you don’t want to use Google Cloud Storage for temporary files.

  14. Click the Create Connection button to create the connection.

Supported Actions and Actions Specifics

Google BigQuery connector supports the following actions: