Transcription
PowerExchange for Snowflake - PowercenterRaveender Nagulavancha Informatica Global Customer Support
Housekeeping Tips Todays Webinar is scheduled to last 1 hour including Q&A All dial-in participants will be muted to enable the speakers to present without interruption Questions can be submitted to “ All Panelists" via the Chat option and we will respond at the end of the presentation The webinar is being recorded and will be available to view on our INFASupport YouTube channel. The link will be emailedas well. Please take time to complete the post-webinar survey and provide your feedback and suggestions for upcoming topics.2 Informatica. Proprietary and Confidential.
Visit Success Portal athttps://success.informatica.comBootstrap producttrial experienceEnriched Onboardingexperience Informatica. Proprietary and Confidential.Product LearningPaths and weeklyExpert sessionsInformaticaConcierge withChatbot integrationsTailored training andcontentrecommendations
Safe HarborThe information being provided today is for informational purposes only. Thedevelopment, release and timing of any Informatica product or functionality describedtoday remain at the sole discretion of Informatica and should not be relied upon inmaking a purchasing decision. Statements made today are based oncurrently available information, which is subject to change. Such statements shouldnot be relied upon as a representation, warranty or commitment to deliver specificproducts or functionality in the future.4 Informatica. Proprietary and Confidential.
Agenda Overview Installation/Configuration Sources/targets Snowflake Lookups Mappings/Sessions SQL Overrides Tips/Tuning PDO Demo Q/A5 Informatica. Proprietary and Confidential.
Snowflake Architecture6 Informatica. Proprietary and Confidential.
Snowflake Benefits Datawarehouse built for cloud Unlimited scalability Pay per use Less maintenance overheads Less upfront capital costs Security Concurrency Compression and encryption by default7 Informatica. Proprietary and Confidential.
PWX for snowflake - Introduction Connector uses Snowflake’s JDBC driver to perform operations Supports JDBC SSL-encryption end-to-end. Supports following CRUD operation: INSERT, UPSERT, UPDATE, DELETE Supported as Source, Target and Lookup8 Informatica. Proprietary and Confidential.
Installation/Configuration Starting from 10.2 HF1, snowflake connector for is installed with Powercenter – noseparate installation Register plugin with Repository using pmrep registerplugin or using Administratorconsole. For more Information, please look into Powerexchange for snowflake userguide for Powercenter9 Informatica. Proprietary and Confidential.
Configuration – contd. HTTP Proxy Config: Add the following properties to the INSTALL s.ini file: -Dhttp.useProxy true -Dhttps.proxyHost Host name of the HTTP proxy server -Dhttps.proxyPort Port number of the HTTP proxy server Add the following JVM options as custom properties under Integration service JVMOption1 -Dhttp.useProxy true JVMOption2 -Dhttps.proxyHost Host name of the HTTP proxy server JVMOption3 -Dhttps.proxyPort Port number of the HTTP proxy server Java Heap Config: Increase the java maximum heap memory Add the following JVM Option Custom property JVMOption1 --Xmx1024m10 Informatica. Proprietary and Confidential.
Importing Source/Target11 Informatica. Proprietary and Confidential.
Import Source/Target12 Informatica. Proprietary and Confidential.
Source Filter You can enter a filter condition to reduce the number of source rows returned from Snowflake sources.13 Informatica. Proprietary and Confidential.
Source Join You can enter a join condition to join multiple Snowflake source tables using a single application source qualifier14 Informatica. Proprietary and Confidential.
Source Join15 Informatica. Proprietary and Confidential.
Custom SQL query16 Informatica. Proprietary and Confidential.
Lookups Pipeline Lookup is used to perform a lookup on a snowflake table Pipeline lookups in a mapping are based on a partial pipeline in the mapping that includes the source and sourcequalifier. The partial pipeline does not include a target. The Integration Service retrieves the lookup source data in thispipeline and passes the data to the lookup cache. Both connected and unconnected lookups are supported17 Informatica. Proprietary and Confidential.
Pipeline lookups18 Informatica. Proprietary and Confidential.
SQL override SQL override can be provided at the sourcequalifier level in session properties. Ensure that the order and data types in the querymatches the Source qualifier ports SQL override can also be done on top of thesource definition created using custom SQLquery. You can also provide SQL override for lookups atthe associated source qualifier used for thepipeline lookup19 Informatica. Proprietary and Confidential.
Session level overrides Filter override – Source filter if used canbe overridden at the session level. Database Name, Schema Name,Warehouse can be overridden in sessionproperties. This applies to source andtarget Source table name and target table namecan be overridden as needed20 Informatica. Proprietary and Confidential.
Session Target properties Batch Row size - Number of rows that session writes in a batch to the Snowflake target. Number of local staging files – This is the number of local staging files that session will write to before writing totarget table Truncate Target Table – truncates table before load Rejected File Path – path where reject file will be created and rejected records will be written to. If the file alreadyexits, session appends rejected records to the file. csvFileSize – Size of local staging files specified in bytes. This is specified as an additional write runtimeparameter. This is recommended to use instead of batch row size property21 Informatica. Proprietary and Confidential.
Target Updates UpdateMode – When Update as Update is selected, all rows flagged for update will be updated on match. Themerge statement runs without the insert clause. UpdateMode - Update else Insert. All rows flagged for update will be updated on match and the rest will beinserted. The merge statement contains the Insert clause. Default Update mode is Update else Insert.22 Informatica. Proprietary and Confidential.
How snowflake writer works Insert rows from upstream into local staging files on the local temp directory Upload the files using snowflake put command into snowflake internal storage Create temporary table on snowflake similar to the target table Copy command to load from snowflake internal storage into temporary table Finally insert/Merge into main target table from temporary table23 Informatica. Proprietary and Confidential.
Error handling Defaulto Copy statement will be run with OnError Continue option.o In case of any row error, session will continue to load that batch and any remaining batches in the session.o Failed records will be written to the rejected file configured and can be corrected to run the load again onError ABORT STATEMENT&oneBatch true (Additional write runtime parameters)o Copy statement will be run with onError ABORT STATEMENT and the entire load will be run as a single batcho In case of any row error, session will fail and not commit anythingo Failed record will be written to the rejected file configuredo This option is currently available as part of EBF (EBF-16164) on top of 10.2 HF2 and will be merged to future versions. Please contact supportfor further details24 Informatica. Proprietary and Confidential.
Troubleshooting Tips What logs to check for any issues/failures Powercenter Session logs Snowflake query monitor How to enable JDBC tracing Set the value tracing ALL for the Additional JDBC URL Parameters Attribute in application connection How to change the local staging directory where the data is written before upload into snowflake JVM option java.io.tempdir on integration service If using SQL override, please make sure the order and data types matches, otherwise this will end up in a failure25 Informatica. Proprietary and Confidential.
Performance Tuning Adjust csvFileSize in additional write parameters. Adjust number of staging files Use partitioning to run the mapping with multiple threads. Please make sure to have enough resources for themultiple partitions. Bigger csvFileSize and partitioning needs more java heap. Please make sure to increase the maximum java heapsize JVM Option If using pipeline lookup on a huge lookup table, selected “Sorted Input” and use SQL override with order by clause.This is to avoid the sorting in Powercenter and push it down to snowflake for better performance26 Informatica. Proprietary and Confidential.
Pushdown Optimization Configure pushdown optimization to pushtransformation logic to the source or targetSnowflake database PDO is supported only with ODBC driver ODBC driver needs to be downloaded from snowflakewebsite -download.html) 32 bit driver needs to be configured for Powercenterclient to import source and target definitions 64 bit driver should be configured on PWC servermachine to be used at the session level Please look into the documentation on more detailsabout configuring the ODBC driver27 Informatica. Proprietary and Confidential.
Important KB links tlyAskedQuestions-H2L.pdf essionsinPowerCenter-H2L.pdf spx28 Informatica. Proprietary and Confidential.
Demo29 Informatica. Proprietary and Confidential.
Q&A30 Informatica. Proprietary and Confidential.
Thank You 31 Informatica. Proprietary and Confidential.
development, release and timing of any Informatica product or functionality described today remain at the sole discretion of Informatica and s
