Adapter Properties and Commands

Adapter properties are used to configure the BigQuery adapter connections, actions, cards, and nodes in the Design Server Web UI.

When a map or flow is configured and built, the properties in the BigQuery cards and nodes are mapped to the adapter commands, which are used at runtime.

The adapter command line contains the generated adapter commands. It can be previewed in the Design Server with the Use Command Line toggle in the Connection step in the BigQuery action, card, or node design workflow.

The following properties are supported by the adapter:

Credentials File

Specifies the file with the private key of the GCP service account used to access the BigQuery service. It is the reference to the private key file for the service account used to establish the BigQuery connection.

The corresponding adapter command is -CF (or -CREDENTIALSFILE).

Project

Specifies the name of the project that contains the dataset to access. If the value is not provided, the current project to which the adapter is connected is used.

The corresponding adapter command is -P (or -PROJECT).

The default is the project to which the adapter is connected and is defined by the credentials file that was used to establish the connection.

Dataset

Specifies the name of the dataset that contains the table or view to access.

The corresponding adapter command is -DS (or -DATASET).

The Fetch button displayed next to the property can be used to retrieve the list of all discovered datasets, and a selection can then be made from the list. Alternatively, the name can be typed in manually.

Table

Specifies the name of the table or view to access.

The corresponding adapter command is -TBL (or -TABLE).

The Fetch button displayed next to the property can be used to discover and retrieve the list of tables and views, and a selection can then be made from the list. Alternatively, the name can be typed in manually.

Query

Specifies user-defined query statement text.

The corresponding adapter command is -QRY (or -QUERY).

When specified in an input card, source node, or two-argument GET map function, the adapter runs the query as a static SQL statement with no parameters, and fetches the matching rows from BigQuery, and emits them on the output link.

When used in an output card, target node, request node, three-argument GET map function, or PUT map function, the adapter runs the query as a parameterized query.

The Fetch button displayed next to the property can be used to discover and retrieve the list of tables and views, and a selection can then be made from the list. Alternatively, the name can be typed in manually.

Parameters

Specifies a list of query parameter definitions in parameterized query statements.

The corresponding adapter command is -PARAMS (or -PARAMETERS).

The list of query parameter definitions needs to be provided in the format:
name:type name:type ...

Each unique query parameter referenced by the specified query statement must be included in this list. The name part is case-sensitive, and the type part is not. Zero or more leading and trailing whitespace characters are allowed for the entire list. One or more whitespace characters are allowed between any two consecutive parameter definitions in the list.

Query parameters need to use the @name notation in the Query statement text. In the Parameters property list, the @ prefix must be omitted.

The supported type values are: STRING, INT64, DATE, TIME, DATETIME, TIMESTAMP, BOOL, NUMERIC, BIGNUMERIC, FLOAT64 and BYTES. If the :type portion is omitted, STRING type is assumed.

Example of Parameters

Consider the following scenario:

The mydataset.mytable table is defined with three columns: c1 INT64, c2 STRING, and c3 DATE.

The specified query statement is: 
SELECT c1, c2 FROM mydataset.mytable WHERE c1 = @p1 AND c2 = @p2 AND c3 BETWEEN = @p3 AND
        @p4 ORDER BY c1 DESC;

An example of a valid Parameters property value would be: p1:INT64 p2 p3:DATE p4:DATE

Notice that the p2 parameter does not have the type specified, so the STRING type is used by default.

Null Marker

Specifies a special string value to use for serializing and deserializing BigQuery NULL values.

The corresponding adapter command is -NM (or -NULLMARKER).

The adapter expects the data that it consumes and produces to consist of linefeed (/n) terminated lines, and each line to consist of pipe (|) delimited field values. By default, the adapter treats any missing field value as a NULL value.

To configure the adapter to use a special marker text value to represent NULL values, the Null Marker property can be used. The null marker can be used for example to distinguish between NULL and zero-length string values for the STRING data type, and between NULL and zero-size byte array values for the BYTES data type.

Example of Null Marker

Consider the following scenario:

A table is defined with the columns c1 INT64, c2 STRING, c3 STRING and all the columns are defined as NULLABLE.

If a BigQuery output card is used to populate this table and the Null Marker property is not set, then for the following input data:

1||EMPTY|

The adapter will insert the row:

1,(null),'EMPTY'.

If however the Null Marker property is set to the value EMPTY, the same input line will be inserted as:

1,'',(null)
Row Limit

Specified the maximum number of rows to retrieve from the BigQuery source and produce on the output link.

The corresponding adapter command is -RL (or -ROWLIMIT).

The adapter uses this property to limit the number of rows returned on the output link. The default value of the property is 0 which means no limit.

Pre SQL

Specifies one or more SQL statements to execute before reading or writing any rows.

The corresponding adapter command is -PRESQL.

This property specifies one or more semicolon-separated SQL statements for the adapter to execute before processing any rows. Line breaks are not allowed.

Post SQL

Specifies one or more SQL statements to execute AFTER reading or writing all rows.

The corresponding adapter command is -POSTSQL.

This property specifies one or more semicolon-separated SQL statements for the adapter to execute after processing all rows. Line breaks are not allowed.

BYTES Format

Specifies the data format to use for serializing and deserializing BigQuery BYTES values.

The corresponding adapter command is -BYF (or -BYTESFORMAT).

The Corresponding properties values are Binary, Text, Base64.

The corresponding Command values are binary, text, base64.

The adapter supports the following formats:
  • Binary: This is the default format. It is defined as size|content where size is the text representation of the value size in bytes, and content is the actual bytes. For example, 5|ef34780123 represents a 5-byte value. In this example, hex-digit pair notation is used to represent the actual value bytes.
  • Text: Represents BYTES values as text values that match the BYTES values decoded based on the default character set encoding. For example 'my-text' represents a UTF-8 decoded 7-byte 6d792d74657874 value.

  • Base64: Represents BYTES values as text values that match the base64 encoded BYTES values. For example, AQIDBAU= represents base64 encoded 5-byte 0102030405 value.

FLOAT64 Format

Specifies the data format to use for serializing and deserializing BigQuery FLOAT64 values.

The corresponding adapter command is -FLF (or -FLOAT64FORMAT).

The Corresponding properties values are Binary, Text, Base64.

The corresponding Command values are binary, text, base64.

The adapter supports the following formats:
  • Binary - This is the default format. It is defined as size|content where size is the textual representation of the number 8, which is the size in bytes of FLOAT64 values, and content is the actual 8 bytes of the FLOAT64 value. For example, 8|400c0000000000 represents an 8-byte FLOAT64 value of 3.5. In this example, hex-digit pair notation is used to represent the actual value bytes.
  • Text - Represents FLOAT64 values as text values that match the text representation of the FLOAT64 values. For example "3.5" is a text representation of the FLOAT64 value 3.5. This format may result in rounding of values since FLOAT64 values use binary floating point precision and not decimal precision.
  • Base64 - Represents FLOAT64 values as text values that match the FLOAT64 values encoded with base64. For example, QAwAAAAAAAA= represents a FLOAT64 value 3.5 value in base64 encoding.
BOOL Format

Data format to use for serializing and deserializing BigQuery BOOL values.

The corresponding adapter command is -BOF (or -BOOLFORMAT).

The Corresponding properties values are Digits, Text.

The corresponding Command values are digits, text.

The adapter supports the following formats:
  • Digits - This is the default format. Boolean true value is represented as digit 1, and boolean false value is represented as digit 0.
  • Text - Boolean true values are represented as text literal "true", and boolean false values are represented as a text literal "false".
Logging

Specifies the settings for the adapter log severity, append mode, and file location.

This property, along with its two sub-properties, Append Log and Log File Name is used to enable logging at the adapter level. When enabled, the adapter logs messages with details about the operations it is performing on the BigQuery service. The default logging level is Info and is used to log informational-level messages. The level Error can be specified to enable logging of error-level messages only, and the level Verbose can be used to enable logging of all messages. The property Append Log can be set to instruct the adapter to append messages to the log file if the log file exists already, instead of overwriting it, which is the default option. The sub-property Log File Name can be used to specify the file name for the log file. By default the log file name is m4bigquery.mtr and the file is created in the current map directory.

The corresponding adapter command is -T [V|E][+] [log_file] (or -TRACE [V|E][+] [log_file]).

-T -> Log adapter informational messages.

-TV -> Use verbose (debug) logging. The log file records all activity that occurs while the adapter is

producing or consuming messages.

-TE -> Log only adapter errors.

+ -> Appends the trace information to the existing log file. Omit this argument to create a new log file.

log_file-> The log file name.

Quantity

Internal property that serves as a contract between the adapter and the adapter framework for defining the desired number of rows to return from the data source.

The corresponding adapter command is -QTY.

This property is implemented internally in the adapter for historical reasons and is not available in the Design Server UI.

When the adapter is configured to fetch rows from BigQuery, it uses the value specified in the Row Limit property to determine how many rows to return. In addition to generating the -RL command, it generates and includes in the command line the -QTY command with the value of 0. This value instructs the adapter framework to keep making calls to the adapter to provide more rows until the adapter notifies it that no more rows are available.

The adapter uses the Row Limit property value to limit the number of rows to provide on the output link. When the specified limit value is reached, the adapter reports to the adapter framework that no more rows are available, and the reading operation ends. If the limit value is not specified, the adapter keeps fetching rows from BigQuery until it notifies the adapter that no more rows are available. The adapter then passes that notification to the adapter framework, and the reading operation ends.

When using the adapter to read data from BigQuery, and creating the adapter command line manually, the -QTY 0 command must be manually included in the command line. This is the case for example when defining a GET map rule that uses the adapter, or when the Use Command Line mode is enabled in the adapter action, card, or node settings in the Design Server UI.

Read Mode

Internal property that indicates the source operation the adapter needs to perform.

The corresponding adapter command is -RM (or -READMODE).

This property is internally set to match the specified operation and is mapped to the -READMODE adapter command. When creating the adapter command line manually, the -READMODE command needs to be manually included in the command line. The supported values for the property are: read_table (default) and execute_query.

Write Mode

Internal property that indicates the target operation the adapter needs to perform.

The corresponding adapter command is -WM (or -WRITEMODE).

This property is internally set to match the specified Operation and is mapped to the -WRITEMODE adapter command. When creating the adapter command line manually, the -WRITEMODE command needs to be manually included in the command line. The supported values for the property are: write_table (default) and execute_parameterized_query.