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)
Calling a Redshift Stored procedure in Snaplogic
Hi All, Has anyone been able to call a redshift stored proc using Snaplogic, couple of items which I read on the community mentioned they were not able to do so and there is an enhancement request for the same. If anyone one has an idea regarding how to achieve this, please let me know in the comments. Thanks and Regards AnubhavUnable to execute Redshift Script with comments in it using Redshift Execute
Hi All, I am using redshift execute to run a redshift script . While I am able to run the script when every redshift query is enclosed in double quote, but the script is failing when I am adding comments to that. Would like to know how to write comment in redshift script so that it can be acceptable in “Redshift Execute”.Redshift Unload Directly to Parquet
Hi, Has anyone figured out a way to Unload data directly to parquet format from Redshift? The Redshift Unload snap does not have parquet format as an option (just delimited and fixed width files). I have tried to circumvent this by using a Redshift Execute snap and then writing an unload command. The process seems to work on a limited data set but I get an error at the end of the Unload like this (data in brackets redacted from actual values): ERROR: Mismatch in returned vs expected bucket in CompleteMultipartUpload. Expected: returned: [my-bucket-name-only]. Mismatch in returned vs expected key in CompleteMultipartUpload. Expected: [my-bucket-name]/[mypath]/[my-prefix] The path I entered into the redshift unload command was: [my-bucket-name]/[mypath]/[my-prefix]
