September Monthly Release Upgrade Impact

 

ALERT: For  customers connecting to  Google Big Query V2  using  IICS

Informatica is providing a new capability to execute Advanced Push Down Optimization using latest Google API’s i.e. com.google.cloud. If customer has created Full Push Down Optimization mapping using Google Cloud Storage V2 as Source and Google Big Query V2 as target with pre SQL or post SQL configured in Google BigQuery V2 target advanced attribute, then customer may see change in behavior. Customers with these scenarios will be reached out to via email, individually.

 

 Scenario 1: (Pre/Post SQL and Full PDO)

 

Am I impacted?

If you use Informatica Google Cloud Storage V2 connection as a source and Google BigQuery V2 as target, your mappings will fail if they meet all the below conditions:

  • pre SQL or postSQL provided on target advance attribute having LegacySQL syntax (ex: screenshot1)
  • The mapping withoutUseLegacySql: true in pre/postSQL config (screenshot2), 
  • Configured PushDownOptimization=Full in Mapping Task (screenshot3)

 

The link to the Google BigQuery V2 documentation is here.

 

 

Screenshot1: Pre/Post SQL on target

 

Screenshot2: Mappings in which pre/postSQL config is not provided with UseLegacySql: true

 

Screenshot3: Full PDO in Mapping task

How do I  address  the issue?

Options are:

  • New behavior: Update pre SQL Configuration or post SQL Configuration on GBQ target in mapping add UseLegacySql”: trueas shown in (Screenshot 4)

 

Screenshot 4: pre SQL Configuration or post SQL Configuration with add UseLegacySql”: true 

  • Old behavior: Enable optional connection property to revert to older API behaviour -"UseAPIServiceForExecution:true" as shown in (Screenshot 5)

 

 

Screenshot 5: Connection property to set “UseAPIServiceForExecution:true

 

 

Scenario 2: (IS_DATE function and Full PDO)

 

Am I impacted?

If you use Informatica Google Cloud Storage V2 connection as a source and Google Big Query V2 as target, your mappings will see a change in number of rowspushed to the next transformation if they meet all the below conditions:

  • Using IS_DATE function (ex: screenshot1_b)
  • Any business logic with respect to fractional seconds(screenshot2_b), and
  • Configured PushDownOptimization=Full in Mapping Task (screenshot3_b)

Example –

  • IS_DATE(col2_string, 'YYYY-MM-DD HH24:MI:SS')

 

 

The link to the Google Big Query V2 documentation is here.

 

Screenshot1_b: IS_DATE function in a mapping

 

 

Screenshot2_b: Business logic includes seconds

 

Screenshot3_b: Full PushDownOptimization in mapping task

How do I  address  the issue?

Only if required, to get the prior (July release) behavior one will need to edit the format provided in expressions containing IS_DATE; change format from 'YYYY-MM-DD HH24:MI:SS'  to 'YYYY-MM-DD HH24:MI:SS.US'

 

 

Scenario 3: (Target Advanced attribute - Write Disposition and Full PDO)

 

Am I impacted?

If you use Informatica Google Cloud Storage V2 connection as a source and Google BigQuery V2 as target, your mappings will fail if they meet all the below conditions:

  • Write disposition provided on target advance attribute has been selected as Write Truncate or Write Empty(Screenshot 1_c, Screenshot 2_c)
  • Configured PushDownOptimization=Full in Mapping Task (screenshot3_c)

The link to the Google BigQuery V2 documentation is here.

 

Screenshot1_c: Write Disposition as “Write Truncate”

 


- Screenshot2: Write Disposition as “Write Empty”

 

Screenshot3_c: Full Push Down Optimization in Mapping task

 

How do I  address  the issue?

  • Users will have to switch the mappings manually to non- Push Down Optimization mode, if they want to truncate the table or write into an empty table using write disposition.
  • User can change Write Disposition to Write append

 

Scenario 4: (Target with runtime attribute "Create Disposition" as "Create If Needed:)

If customer has created Full Push Down Optimization mapping using Google Cloud Storage V2 as Source and Google Big Query V2 as target with runtime attribute "Create Disposition" as "Create If Needed", then customer will see a mapping failure.

Till now, all these mappings used to treat any create disposition value as "Create Never". This was leading to incorrect behavior being performed in Push Down Optimization mode. Corrected the behavior by adding the logic to fail such mappings.

 

Am I impacted?

If you use Informatica Google Cloud Storage V2 connection as a source and Google Big Query V2 as target, your mappings will fail if they meet all the below conditions:

  • Create Disposition on target advance attribute has value selected as “Create if needed” (screenshot1)
  • Configured PushDownOptimization=Full in Mapping Task (screenshot2)

 

The link to the Google Big Query V2 documentation is here.

 

Screenshot1: Create Disposition as “Create If needed”

 

Screenshot2: Full PDO in Mapping task

How do I address the issue?

  • Users will have to switch the mappings manually to non-pushdown mode, if they want to create an empty table using create disposition. User can change Create Disposition value to “Create never".

 

Who do I contact if I need help?

If you have any questions, please contact Global Customer Support.