Oracle to Redshift - Dynamic Metadata
Created by Chris Ward, SnapLogic This Pipeline replicates table data obtained from an Oracle database and upserts the data into Redshift. Firstly, the Parent Pipeline reads the Source and Target schema & tables and a where clause used to filter the source data from an expression file “oracle_to_redshift__c.expr” (the customer would need to modify this file to correspond with the table & schemas they are looking to target), then obtains the primary key constraints for each target table in Redshift. The Pipeline then constructs an array of documents for up to 10 key columns that will be used to determine the unicity of the data when being upserted into Redshift. Each document is then passed into the child pipeline that reads the data from the source Oracle table and then upserts the data into Redshift. A router Snap is used between the Oracle read and Redshift upsert to achieve concurrency for large data volumes. The Pattern used within this Pipeline could potentially be reworked to operate with different source and target databases, eg SQL Server to Google Big Query. Configuration To make this Pipeline reusable, make use of the expression file to store the source and target tables & schemas. Sources: Oracle Table Targets: Redshift Table Snaps used: Mapper, JSON Splitter, Redshift Execute, Group By Fields, Pipeline Execute, Oracle Select, Router, Redshift Upsert Downloads Oracle to Redshift - Dynamic Metadata.slp (15.8 KB) Oracle_to_Redshift_02.slp (11.3 KB) oracle_to_redshift_c.expr (3.2 KB)
Purple to Redshift-WiFi Analytics
Created by @asharifian , SnapLogic This pattern integrates Purple with Redshift. Purple is a WiFi analytics application that builds detailed visitor profiles and tracks visitors in your venues, such as conferences, workshops, gatherings, etc. At Purple, all of the data collected is stored within a centralized, enterprise-class reporting suite, available for analysis. This pattern retrieves the analytics data from Purple and bulk upserts it into Redshift for reporting and additional data analytics purposes. Configuration A child pipeline is used to generate the HMAC SHA256 key. This is a requirement for the Purple API’s. Sources: Purple Venues, Visitors Targets: Redshift database tables to store venue and visitor data. Snaps used: Mapper, Pipeline Execute, REST Get, JSON Splitter, Copy, Redshift Bulk Upsert, Script, Join Downloads Purple to Redshift-WiFi Analytics.slp (21.4 KB) HMAC_SHA256.slp (18.5 KB)
Batch sync new leads from Marketo to Amazon Redshift
Created by @mthammareddy When there is a new lead activity batch in Marketo, synch the batch into Amazon Redshift. Configuration This pattern also requires the Get Marketo Access Token pattern. Sources: Marketo Targets: Redshift Snaps used: Pipeline Execute, REST Get, JSON Splitter, Mapper, Redshift - Insert Downloads MarketoBatchLeads_To_RedShift.slp (9.3 KB)
New CSV in Box will create a new record in AWS Redshift
Created by @kjain This pipeline parses a CSV file in Box and creates a new record in Redshift. Configuration Provide value in update condition field in the Redshift - Update Snap. Example id = ‘5069829837’ Sources: Box Targets: Redshift Snaps used: Box Directory, Box Read, CSV Parser, Mapper, Redshift - Update Downloads Box_to_RedshiftUpdate.slp (6.9 KB)
Reference Implementation - Integration Pattern - On-Prem Database to Cloud Datawarehouse
This is a common integration patterns that customers tend to use as they are moving from on-prem to cloud. The following videos describe this integration pattern and the attached reference implementation pipelines help you get a head start on the implementation. Part 1 Part 2 Pipelines Attached The pipeline moves all the tables from an Oracle database to Redshift. The pipelines are designed to use our best practices like parameterization of all aspects of the pipeline (eg. account names , db names, table names). All are dynamic and controlled via pipeline parameters. Source: Oracle Tables Target: Redshift Tables Snaps used: Step 1: Oracle - Table List, Filter, Oracle - Execute, Mapper, Pipeline Execute Step 2: Mapper, Redshift - Execute, Router, Join, Oracle - Select, Shard Offsets*, Redshift - Bulk Load, Pipeline Execute, Exit, Union Step 3: Mapper, Oracle - Select, Redshift - Bulk Load, Exit *This is a Snap developed by the SnapLogic Professional Services team. Downloads Pattern 1 - Step 1.0 - Oracle to Redshift Parent_2018_06_25.slp (9.4 KB) Pattern 1 - Step 1.1 - Oracle to Redshift No Shredding_2018_06_25.slp (20.9 KB) Pattern 1 - Step 1.2 - Oracle to Redshift Shred_2018_06_25.slp (7.7 KB)
Get check object data from NetSuite and move data into Redshift
Created by @pkoppishetty This pipeline pattern is designed to get the check object data from NetSuite and move the data into Redshift data warehouse. Configuration Sources: NetSuite check Targets: Redshift table Snaps used: Mapper, NetSuite Search, JSON Splitter, Redshift Bulk Load Downloads Integration Pattern from NetSuite to Redshift.slp (8.8 KB)
Look up addresses from multiple databases
Created by @dkifle This pipeline pattern is used to read records of student information and lookup and compile the address of each student based on whether or not the student is a domestic or international student. Configuration Sources: CSV data, Oracle, SQL Server (CSV Generator can be replaced by any student information system.) Targets: Oracle and Redshift tables Snaps used: CSV Generator, Router, Oracle and SQL Server Lookup, Mapper, Union, Copy, Oracle Merge, Redshift Insert Downloads Look Up Student Address.slp (19.0 KB)Move data from files (SFTP/HDFS) into Redshift tables
Contributed by @pkona This pipeline pattern allows users to move data available in files into a cloud data warehouse like Redshift. The pattern includes best practices for using configurable pipeline arguments and parallelized execution via the Pipeline Execute Snap. The pattern also uses bulk execute Snaps to efficiently load data into Redshift. Pattern 2 - Step 1.0 - files to Redshift Parent Source: Files uploaded to SnapLogic Target: the Pattern 2 - Step 1.1 pipeline Snaps used: Directory Browser, Mapper, Pipeline Execute Configuration Update the Pipeline Parameters with the following: Path to the files within SnapLogic The target table schema and database type The source and target account names Pattern 2 - Step 1.1 - Process a file and bulk load to Redshift Source: Files located by the Pattern 2 - Step 1.0 pipeline Target: Redshift Snaps used: File Reader, CSV Parser, Redshift Bulk Load Configuration Update the Pipeline Parameters with the following: Path to the files within SnapLogic The target database account name and schema Downloads Pattern 2 - Step 1.0 - files to Redshift Parent.slp (6.2 KB) Pattern 2 - Step 1.1 - Process a file and bulk load to Redshift.slp (5.5 KB)
