Communities
A collaborative platform to connect and grow with like-minded Informaticans across the globe
Product Communities
Connect and collaborate with Informatica experts and champions
Discussions
Have a question? Start a Discussion and get immediate answers you are looking for
User Groups
Customer-organized groups that meet online and in-person. Join today to network, share ideas, and get tips on how to get the most out of Informatica
Get Started
Community Guidelines
Knowledge Center
Troubleshooting documents, product guides, how to videos, best practices, and more
Knowledge Base
One-stop self-service portal for solutions, FAQs, Whitepapers, How Tos, Videos, and more
Support TV
Video channel for step-by-step instructions to use our products, best practices, troubleshooting tips, and much more
Documentation
Information library of the latest product documents
Velocity (Best Practices)
Best practices and use cases from the Implementation team
Learn
Rich resources to help you leverage full capabilities of our products
Trainings
Role-based training programs for the best ROI
Certifications
Get certified on Informatica products. Free, Foundation, or Professional
Product Learning Paths
Free and unlimited modules based on your expertise level and journey
Resources
Library of content to help you leverage the best of Informatica products
Tech Tuesdays Webinars
Most popular webinars on product architecture, best practices, and more
Product Availability Matrix
Product Availability Matrix statements of Informatica products
SupportFlash
Monthly support newsletter
Support Documents
Informatica Support Guide and Statements, Quick Start Guides, and Cloud Product Description Schedule
Product Lifecycle
End of Life statements of Informatica products
Ideas
Events
Change Request Tracking
Marketplace
English
  • PowerExchange Adapters for PowerCenter 10.5.6
  • All Products

Table of Contents

Search

  1. Preface
  2. Introduction to PowerExchange for Google BigQuery
  3. PowerExchange for Google BigQuery Configuration
  4. Google BigQuery Sources and Targets
  5. Google BigQuery Mappings
  6. Google BigQuery Sessions
  7. Google BigQuery as CDC Target
  8. Google BigQuery Pushdown Optimization
  9. Appendix A: Google BigQuery Data Type Reference

PowerExchange for Google BigQuery User Guide for PowerCenter

PowerExchange for Google BigQuery User Guide for PowerCenter

Back Next

Configure Google BigQuery Target Session Properties

Configure Google BigQuery Target Session Properties

You can configure the session properties for a Google BigQuery target on the
Mapping
 tab. Define the properties for the target instance in the session.
The following table describes the session properties that you can configure for a Google BigQuery target session:
Property
Description
UpdateMode
Determines the mode that PowerExchange for Google BigQuery uses to update rows in the Google BigQuery target.
If you select an update mode, you need to select
Update
 for the
Treat Source Rows As
 session property in the
Properties
 page.
You can select one of the following modes:
  • Update As Update. PowerExchange for Google BigQuery updates all rows flagged for update if the entries exist.
  • Update Else Insert. PowerExchange for Google BigQuery first updates all rows flagged for update if the entries exist in the target. If the entries do not exist, PowerExchange for Google BigQuery inserts the entries.
Default is Update As Update.
Target Dataset ID
Optional. Overrides the Google BigQuery dataset name that you specified in the target definition.
Target Table Name
Optional. Overrides the Google BigQuery target table name that you specified in the target definition.
Create Disposition
Specifies whether PowerExchange for Google BigQuery must create the target table if it does not exist.
You can select one of the following values:
  • Create if needed. If the table does not exist, PowerExchange for Google BigQuery creates the table.
  • Create never. If the table does not exist, PowerExchange for Google BigQuery does not create the table.
Write Disposition
Specifies how PowerExchange for Google BigQuery must write data in bulk mode if the target table already exists.
You can select one of the following values:
  • Write append. If the target table exists, PowerExchange for Google BigQuery appends the data to the existing data in the table.
  • Write truncate. If the target table exists, PowerExchange for Google BigQuery overwrites the existing data in the table.
  • Write empty. If the target table exists and contains data, PowerExchange for Google BigQuery displays an error and does not write the data to the target.
    PowerExchange for Google BigQuery writes the data to the target only if the target table does not contain any data.
Write disposition is applicable for bulk mode.
Write disposition is applicable only when you perform an insert operation on a Google BigQuery target.
Write Mode
Specifies the mode to write data to the Google BigQuery target.
You can select one of the following modes:
  • Bulk. In bulk mode, PowerExchange for Google BigQuery first writes the data to a staging file in Google Cloud Storage.
    When the staging file contains all the data, PowerExchange for Google BigQuery loads the data from the staging file to the BigQuery target. Google BigQuery then deletes the staging file unless you
    configure the mapping to persist the staging file.
  • Streaming. In streaming mode, PowerExchange for Google BigQuery directly writes data to the BigQuery target. PowerExchange for Google BigQuery writes the data into the target row by row.
  • CDC. Applies only when you capture changed data from a CDC source. In CDC mode, PowerExchange for Google BigQuery captures changed data from any CDC source and writes the changed data to a Google BigQuery target table.
Default is Bulk mode.
Streaming Template Table Suffix
Specifies the suffix that PowerExchange for Google BigQuery adds to the individual target tables that it creates based on the template target table.
This property applies to streaming mode.
Rows per Streaming Request
Specifies the number of rows that PowerExchange for Google BigQuery streams to the BigQuery target for each request.
Default is 500 rows.
The maximum row size that PowerExchange for Google BigQuery can stream to the BigQuery target for each request is 10 MB.
This property applies to streaming mode.
Staging File Name
Name of the staging file that PowerExchange for Google BigQuery creates in the Google Cloud Storage before it loads the data to the Google BigQuery target.
This property applies to bulk mode.
Data Format of the staging file
Specifies the data format of the staging file.
You can select one of the following data formats:
  • JSON (Newline Delimited). Supports flat and record data with nested and repeated fields.
  • CSV. Supports flat data.
    If you use hybrid and complex connection mode, you cannot use CSV format as the data format of the staging file.
Persist Staging File After Loading
Indicates whether PowerExchange for Google BigQuery must persist the staging file in the Google Cloud Storage after it writes the data to the Google BigQuery target. You can persist the staging file if you want to archive the data for future reference.
By default, PowerExchange for Google BigQuery deletes the staging file in Google Cloud Storage.
This property applies to bulk mode.
Enable Staging File Compression
Select this option to compress the size of the staging file before PowerExchange for Google BigQuery writes the data to the Google Cloud Storage and decompress the staging file before it loads the data to the Google BigQuery target.
You can enable staging file compression to reduce cost and transfer time.
Job Poll Interval in Seconds
The number of seconds after which PowerExchange for Google BigQuery polls the status of the write job operation.
Default is 10.
Number of Threads for Uploading Staging file
The number of files that PowerExchange for Google BigQuery must upload to Google Cloud Storage in bulk mode.
Local Stage File Directory
Specifies the directory on your local machine where PowerExchange for Google BigQuery stores the files temporarily before writing the data to the staging file in Google Cloud Storage.
This property applies to bulk mode.
Allow Quoted Newlines
Indicates whether PowerExchange for Google BigQuery must allow the quoted data sections with newline character in a .csv file.
Field Delimiter
Delimiter character for the fields in a .csv file.
Quote Char
Specifies the quote character that defines the boundaries of text strings in a .csv file. You can configure parameters such as single quote or double quote.
Allow Jagged Rows
Indicates whether PowerExchange for Google BigQuery must accept the rows without trailing columns in a .csv file.
Pre SQL
SQL statement that you want to run before writing data to the target.
For example, if you want to select records from the database before you write the records into the table, specify the following pre SQL statement:
SELECT * FROM `api-project-80697026669.EMPLOYEE.RegionNation` LIMIT 1000
Pre SQL Configuration
Specify a pre SQL configuration.
For example,
DestinationTable:PRESQL_TGT2,DestinationDataset:EMPLOYEE,
FlattenResults:False,WriteDisposition:WRITE_TRUNCATE,UseLegacySql:False
Post SQL
SQL statement that you want to run after writing the data into the target.
For example, if you want to update records in a table after you write the records into the target table, specify the following post SQL statement:
UPDATE [api-project-80697026669.EMPLOYEE.PERSONS_TGT_DEL]
SET phoneNumber.number =1000011, phoneNumber.areaCode=100 where fullname='John Doe'
Post SQL Configuration
Specify a post SQL configuration.
For example,
DestinationTable:POSTSQL_SRC,DestinationDataset:EMPLOYEE,
FlattenResults:True,UseLegacySQL:False
Enable Merge
Implements the Merge query to perform update, upsert, or delete operations on the Google BigQuery target table.
Default is not selected.
Treat Empty String as Null
Determines whether PowerExchange for Google BigQuery must treat empty strings in the source as null in the Google BigQuery target.
INSERT
Inserts all rows to the Google BigQuery target.
DELETE
Deletes rows from the Google BigQuery target.
If you select DELETE, you need to select
Delete
 for the
Treat Source Rows As
 session property in the
Properties
 page.
UPDATE
Not applicable for PowerExchange for Google BigQuery.
Configure the update strategy for a target object in the
UpdateMode
 session property.
Success File Directory
Not applicable for PowerExchange for Google BigQuery.
Error File Directory
Not applicable for PowerExchange for Google BigQuery.
Forward Rejected Rows
Not applicable for PowerExchange for Google BigQuery.
0 COMMENTS
We’d like to hear from you!