Striim 3.9.8 documentation

BigQuery Writer

Writes to a table in Google BigQuery using the google-cloud-bigquery client library 1.35.0.



default value


Allow Quoted Newlines



Set to True to allow quoted newlines in the delimited text files in which BigQueryWriter accumulates batched data.

Batch Policy


eventCount:1000000, Interval:90

The batch policy includes eventCount and interval (see Setting output names and rollover / upload policies for syntax). Events are buffered locally on the Striim server and sent as a batch to the target every time either of the specified values is exceeded. When the app is stopped, any remaining data in the buffer is discarded. To disable batching, set to EventCount:1,Interval:0.

With the default setting, data will be written every 90 seconds or sooner if the buffer accumulates 1,000,000 events.

Do not exceed BigQuery's quotas or limits (see Load jobs in the "Quotas and limits" section of Google's BigQuery documentation). For example, if you exceed 1000 batches per table in one day, BigQueryWriter will throw an exception such as error code 500, "An internal error occurred and the request could not be completed," and stop the application. To avoid this, reduce the number of batches by increasing the event count and/or interval.

Column Delimiter


| (UTF-8 007C)

The character(s) used to delimit fields in the delimited text files in which the adapter accumulates batched data. If the data will contain the | character, change the default value to a sequence of characters that will not appear in the data.

Connection Retry Policy


retryInterval=30, maxRetries=3

With the default setting, if a connection attempt is unsuccessful, the adapter will try again in 30 seconds (retryInterval. If the second attempt is unsuccessful, in 30 seconds it will try a third time (maxRetries). If that is unsuccessful, the adapter will fail and log an exception. Negative values are not supported.

Data Location


Specify the dataset's Data location property value if necessary (see Dataset Locations).




Encoding for the delimited text files in which BigQueryWriter accumulates batched data. Currently the only supported encoding is UTF-8 (see Loading encoded data).




With the default value APPENDONLY, updates and deletes in DatabaseReader, IncrementalBatchReader, and SQL CDC sources are handled as inserts in BigQuery. Set to MERGE to handle them as updates and deletes instead.

Since BigQuery does not have primary keys, when using MERGE mode you must use the keycolumns option in the Tables property to specify a column in the target table that will contain a unique identifier for each row: for example, Tables:'SCOTT.EMP,mydataset.employee keycolumns(emp_num)'. You may use wildcards for the source table provided all the tables have the key columns: for example, Tables:'DEMO.%,mydataset.% KeyColumns(...)'.

Null Marker



A string inserted into fields in the delimited text files in which BigQueryWriter accumulates batched data to indicate that a field has a null value. These are converted back to nulls in the target tables. If any field might contain the string NULL, change this to a sequence of characters that will not appear in the data.

Optimized Merge



When the input stream of the target is the output of an OracleReader source:

  • with the default value of false, Oracle supplemental logging must be enabled on all columns

  • when set to true, supplemental logging is required only for primary key columns

See Enabling supplemental log data.

Parallel Threads


See Creating multiple writer instances.

Project Id


Specify the project ID of the dataset's project.

Quote Character


" (UTF-8 0022)

The character(s) used to quote (escape) field values in the delimited text files in which the adapter accumulates batched data. If the data will contain ", change the default value to a sequence of characters that will not appear in the data.

Service Account Key


The path (from root or the Striim program directory) and file name to the .json credentials file downloaded from Google (see Service Accounts). This file must be copied to the same location on each Striim server that will run this adapter, or to a network location accessible by all servers.

The associated service account must have the BigQuery userjobUser, or higher role for the target tables (see Access Control).

Standard SQL



With the default setting of True, BigQueryWriter constrains timestamp values to standard SQL. Set to False to use legacy SQL. See Migrating to Standard SQL for more information.



The name(s) of the table(s) to write to, in the format <dataset>.<table>. The table(s) must exist when the application is started.

When the target's input stream is a user-defined event, specify a single table.

When the input stream of the target is the output of a DatabaseReader, IncrementalBatchReader, or SQL CDC source (that is, when replicating data from one database to another), it can write to multiple tables. In this case, specify the names of both the source and target tables. You may use wildcards for the table names, but not for the schema or database. For example:


MySQL and Oracle names are case-sensitive, SQL Server names are not. Specify names as <schema name>.<table name> for MySQL and Oracle and as <database name>.<schema name>.<table name> for SQL Server.

See Mapping columns for additional options.

The following describes use of BigQueryWriter with an input stream of a user-defined type. For an example of input from a CDC or DatabaseReader source, see Replicating Oracle data to Google BigQuery.

The sample application below assumes that you have created the following table in BigQuery:

Screen Shot 2016-09-25 at 5.34.11 PM.png
CREATE SOURCE PosSource USING FileReader (
  wildcard: 'PosDataPreview.csv',
  directory: 'Samples/PosApp/appData',
    positionByEOF:false )
  trimquote:false )
OUTPUT TO PosSource_Stream;
CREATE CQ PosSource_Stream_CQ
INSERT INTO PosSource_TransformedStream
SELECT TO_STRING(data[1]) AS MerchantId,
  TO_DATE(data[4]) AS DateTime,
  TO_DOUBLE(data[7]) AS AuthAmount,
  TO_STRING(data[9]) AS Zip
FROM PosSource_Stream;

CREATE TARGET BigQueryTarget USING BigQueryWriter(
  ServiceAccountKey:"/<path>/<file name>.json",
  Tables: "mydataset.mytable"
INPUT FROM PosSource_TransformedStream;

After running this application, BigQuery, run select * from mydataset.mytable; and you will see the data from the file. Since the default timeout is 90 seconds, it may take that long after the application completes before you see all 1160 records in BigQuery.

BigQueryWriter data type support and correspondence

Use the following when the input stream is of a user-defined type. When the input is the output of a CDC or DatabaseReader source, see Replicating Oracle data to Google BigQuery.

TQL type

BigQuery type

notes on BigQuery types



  • CSV format: true or false (case-insensitive) or 1 or 0

  • JSON format: true or false (case-insensitive



  • Imported BYTES data must be base64-encoded, except for Avro BYTES data, which BigQuery can read and convert.

  • BYTES data read from a BigQuery table are base64-encoded, unless you export to Avro format, in which case the Avro bytes data type applies.



stored internally as a UNIX timestamp with microsecond precision

Double, Float





64-bit signed