Documentation

    BigQuery

    Read BigQuery tables and views into a Strata flow, and write results to tables. BigQuery uses a Google connection, the same kind Google Sheets uses.

    Before you start

    • A Google account with access to a Google Cloud project that has BigQuery enabled.
    • IAM roles on your own Google user: job creation in the project that runs the query, plus read access to the data. To write, you also need write access to the dataset. See BigQuery access control.
    • A dataset to write to. Output doesn't create datasets.
    • Permissions Oplema asks for: bigquery.readonly from Input, and bigquery from Output.
    • Requests to BigQuery come from Oplema's servers, not your browser. Oplema doesn't promise a fixed IP address; contact us if you need to allowlist.

    Connect

    1. In Strata, select an Input block to read, or an Output block to write.
    2. Set Source to Connectors. On an Output block the same control is Destination.
    3. In the connector dropdown below it, choose BigQuery.
    4. Open the Connection dropdown and choose Create new connection.
    5. Set Name (optional). Without a name, the connection is labeled google plus the date and time, so a name helps if you have several.
    6. Select Connect to BigQuery. Google's own sign-in opens in a popup. Approve access there.

    The popup closes, the new connection is selected and the pickers below it load. If access later expires or you need more permissions, select the connection, then select Reconnect to BigQuery.

    Shared with Google Sheets

    BigQuery and Google Sheets use one kind of connection, so the Connection dropdown for either lists all your Google connections. Permissions add up: a connection made for Google Sheets doesn't yet have BigQuery access. Select it, then select Reconnect to BigQuery, and Oplema asks for the new permission while keeping the ones it already has. The same applies when a connection made from Input is first used in Output.

    Read

    SettingWhat it does
    Project selectionChoose project lists your projects. Enter project ID takes a project ID directly, not its display name.
    Dataset, then Table or viewPick these in order to choose what to read.
    ColumnsAll columns or Selected columns.
    Date column, From and ToOptional. These are inclusive whole days. TIMESTAMP uses UTC.
    Query / billing projectUnder Advanced. Blank bills the data project.
    Maximum query size (GiB)Under Advanced. Default 1.

    Oplema runs a read-only query in the table's location. Select Refresh data to run it again. A query must finish within two minutes.

    Write

    Pick the same project, then Dataset, then Table. The project also runs the load job.

    • Create new table: enter a Table name, default output. Column names can use letters, numbers and underscores.
    • An existing table: set Write to Replace or Append rows. Replace keeps the existing columns and types.

    Output writes when you run the flow. Choosing tables in the panel never writes. If you pick Create new table and a table with that name already exists, the run fails. If the table is created but loading then fails, the empty table remains.

    Limits and billing

    • Reads: the first 50 MB of each download is free. After that, each download costs $0.01 per GB from your usage. Failed reads aren't charged, and a read stops when your usage runs out.
    • Writes: the first 50 MB of each write is free. After that, each write costs $0.01 per GB from your usage, and a write stops when your usage runs out.
    • Google bills query and storage costs separately. Maximum query size (GiB) caps the bytes billed per query; it isn't related to Oplema's prices.

    See Billing for who pays and how usage works.

    Troubleshooting

    MessageWhat to do
    BigQuery: Reconnect your Google connection.Select the connection, then select Reconnect to BigQuery.
    BigQuery: reconnect this Google connection with BigQuery write permissionThe connection only has read access. Select Reconnect to BigQuery from the Output block.
    BigQuery: maximum query size must be a positive numberFix Maximum query size (GiB) under Advanced.
    BigQuery: query did not finish within two minutes; it may still be runningNarrow the read with columns or dates. Check the job in Google Cloud, because it may keep running.
    BigQuery: destination has no column <name>Rename the column in Strata, or add it to the table in BigQuery.
    BigQuery: incompatible type for <name>; convert it explicitly in StrataConvert the column with a block before Output.
    BigQuery: JSON null in <name> cannot be distinguished from SQL NULL in a JSON load; convert it explicitly in StrataRemove or convert the JSON null values before Output.
    BigQuery: empty replacement is blocked; existing data was preservedThe incoming table has no rows. Check the flow upstream.
    BigQuery: completion could not be confirmed for job <project>/<location>/<job>. Check the job before running again.The connection dropped during the write. Look at the job and the table first, because a retry could write the rows twice.
    This transfer exceeds the free 50 MB and the available usage creditsThe read or write passed 50 MB and your usage ran out. Add usage, or move less data.

    Prepare your data. Build it in Strata.

    Strata opens in your browser, with no account.