When you configure a connection, you can choose to use Google BigQuery Storage to stage the data. To enable Google BigQuery Storage, select the Enable BigQuery Storage API property in the connection advanced properties.
You can use hybrid connection mode to import the metadata and stage files that contain hierarchical data types such as record and repeat.
You can use the standard SQL format for the custom query, but you cannot use the legacy SQL format.
Read operation
•Uses the Staging mode as read mode.
•You can configure the following runtime attributes for the read operation:
- Connection
- Source Type
- Object
- Parameter
- Query
- Filter
- Source Dataset ID
- Source Table Name
- Source Staging Dataset
- Number of Rows to Read
- Allow Large Results
- Query Results Table Name
- Job Poll Interval In Seconds
- Read Mode
- Persist Destination Table
- pre SQL
- post SQL
- pre SQL Configuration
- post SQL Configuration
- SQL Override Query
- Use Legacy SQL For SQL Override
Write operation
•Uses the Bulk mode as write mode.
• You cannot use update, delete, and merge target operations on the rows in a target table that were written in the last 30 minutes. If you configure a mapping with this scenario, the mapping fails with one of the following errors:
- [ERROR] The Google BigQuery V2 Target definition post-SQL operation failed with the following error: [UPDATE or DELETE statement over table table1_BQ_Storage.tgt_mct_151_table1 would affect rows in the streaming buffer, which is not supported].
- [ERROR] Error occured while trying to Initialize Data Source Operation | com.informatica.cci.runtime.internal.utils.impl.CExceptionImpl: Unable to render embedded object: File (Truncate Target Failed UPDATE or DELETE statement over table automation-bigquery-project.table1_BQ_Storage.tgt_mct_151_table1 would affect rows in the streaming buffer, which is not supported) not found.
•You cannot use the write empty option in the Write Disposition property.
•You can use the write truncate option in the Write Disposition property only with a truncated target table.
•You can configure the following runtime attributes for the write operation:
- Connection
- Object
- Target Type. You can use single object as the Target Type.
- Operation. You can use only the Insert operation.