Tutorial BigQuery/Connect AI

BigQuery in Power Automate: Three Ways to Read Your Tables

Compare calling the BigQuery REST API, using a catalog connector, and querying BigQuery as a SQL Server database through CData Connect AI, then set up the SQL route.

Jerod Johnson
Director, Technology Evangelism
Updated

You have a flow to build and a BigQuery dataset it needs to read. Power Automate doesn't make the route obvious, and there's more than one reasonable way to do it. Which one fits depends on whether your team is comfortable writing HTTP requests, whether a BigQuery connector is available in your tenant, and how much you want SQL to do the work.

Three ways to read BigQuery from a flow

HTTP action against the BigQuery API A BigQuery connector from the catalog SQL Server connection through CData Connect AI
How it works You build the request and parse the JSON response yourself A prebuilt connector with its own actions Connect AI presents BigQuery as a SQL Server database
Auth to Google You manage OAuth or a service account token in the request Whatever the connector's sign-in uses OAuth sign-in once, in Connect AI
Query language BigQuery SQL in the request body Depends on the connector SQL through the normal SQL Server actions
Needs a premium or custom component Usually Depends on the connector Uses the SQL Server connector
Good fit One-off calls, API features outside SQL You already have the connector approved Several flows reading tables, or joins across sources

Check the middle column in your own Power Automate environment: whether a connector exists, who published it and what its actions cover are not things to assume. The HTTP route is a fair choice when you need an API feature that SQL doesn't express. The route below is for when you'd prefer to write a SELECT than parse JSON.

Three option cards comparing an HTTP action on the BigQuery API, a catalog connector, and a SQL Server connection through CData Connect AI, with the Connect AI route highlighted.
The SQL Server route trades JSON parsing for a plain SELECT. Illustration.

How the SQL route works

CData Connect AI holds the BigQuery connection. Power Automate connects to tds.cdata.com,14333 as if it were SQL Server, and Connect AI sends supported SQL operations, including filters and joins, to BigQuery to process there. The data isn't copied into Connect AI or Power Automate ahead of time; each run reads from BigQuery. If what you want is a copy of BigQuery data in another database, that's replication, and CData Sync is the product for it.

Authenticate and scope the BigQuery connection

In Connect AI, go to Sources > Add Connection and choose BigQuery. Google uses OAuth, so the form has you click Sign in and authenticate with Google. CData's driver can use its embedded OAuth credentials or an OAuth app you register yourself, and for a service account it uses OAuth JWT values.

Either way, the driver also needs a ProjectId and a DatasetId. In its connection-string form, a connection looks like this:

DataSetId=MyDataSetId;ProjectId=MyProjectId;InitiateOAuth=GETANDREFRESH;

The Connect AI form has the same two fields, and setting a dataset narrows what the flow's table drop-down offers. Finish on the Permissions tab and set the user-based permissions.

Create the Power Automate connection

First create a personal access token in Connect AI: the gear icon, then Personal Access Tokens, then Create PAT. The token shows once, and CData advises a separate one per service.

In Power Automate, go to Data > Connections > New connection, choose SQL Server, and connect directly. Enter tds.cdata.com,14333 as the SQL server name and the BigQuery connection's name in Connect AI as the SQL database name. The Username is your Connect AI user and the Password is the PAT.

A SQL Server connection form with the server tds.cdata.com,14333, a Connect AI connection name as the database, a Connect AI user as the username, and a masked token as the password.
Four fields make the connection; the password is the Connect AI token. Illustration with placeholder values.

Read a table in a flow

Start a flow from My flows > New flow, add the SQL Server action Get rows, pick the connection, and choose a table. The sample table in CData's BigQuery material is Orders, and a filtered read of it is equivalent to this:

A result table of OrderName and Freight values under the SELECT query that filters the Orders table to ShipCity New York.
The filtered read returns only the columns and rows you ask for. Sample output.
SELECT OrderName, Freight
FROM Orders
WHERE ShipCity = 'New York'

Use the action's advanced options for the filter and ordering, so BigQuery does the narrowing.

If the flow can't reach BigQuery

  • If Sign in succeeds but no tables appear, check the ProjectId and DatasetId on the connection.
  • If Power Automate refuses the credentials, make sure the password is the Connect AI PAT, not a Google credential.
  • If a colleague can't see the connection's tables, review the connection's Permissions tab.

Ready to get started?

Learn more about CData Connect AI or sign up for free trial access:

Free Trial