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)
Dynamic keys in Redshift Bulk Upsert
Hello, We have a pipeline design(in concept) which will pick up files from source and upsert into the corresponding redshift table (currently scoping approximately 250 source files and target tables). We’d like to do this in a single pipeline by defining the source, target and keys (and other information) in the database. We’ve hit a hurdle at the specifying the keys in the redshift bulk upsert snap (applies to other snaps too), each table has a different number of keys. Is it possible to define the keys in an array format perhaps? Any suggestions to try will be greatly appreciated. It would also be interesting to see how other similar projects have been implemented. Many thanks in advance, Shasta
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 AnubhavMove 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)
Redshift and SnapLogic
We are trying to stand up a new Redshift environment and want to be able to connect and run pipelines with SnapLogic. Corporate security rules require us to launch the Redshift cluster in a private subnet, no NAT or Internet Gateway, all access through firewall rules. Do we absolutely have to use a Groundplex, can we do it in a cloudplex? If we have to use a Groundplex, can we use an existing Groundplex already running in our SnapLogic account? Thanks!
Unable to execute stored procedure (Redshift) using Redshift Execute
SQL: CALL get_result_set(1, ‘mycursor’); FETCH ALL FROM mycursor; Failure: SQL operation failed, Reason: A result was returned when none was expected., error code: 0, SQL state: 0100E, Resolution: Please check for valid Snap properties and input data.Unable 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”.How to Grant Access to SnapLogic's IPs for Amazon Redshift
In order for SnapLogic’s Snaplex application to be able to read and write data to your Amazon Redshift cluster, we need allow external connection from the SnapLogic IP addresses for inbound access. Open the Clusters page within the Amazon Redshift console and choose the cluster you wish to integrate with. Click the “Properties” tab: In the “Network and security” section, click the “Edit publicly accessible” button and change it to be “Enabled” (if not already). Click the link to the security group for the cluster: Under the “Inbound rules” tab, click the “Edit inbound rules” button and add the following entries, clicking the “Add Rule” button after each one (you may customize the Description column value as you see fit): Redshift TCP 5439 52.11.8.103/32 Redshift TCP 5439 34.208.181.167/32 Redshift TCP 5439 52.10.35.99/32 Redshift TCP 5439 52.36.97.11/32 Redshift TCP 5439 34.208.230.181/32 Redshift TCP 5439 34.209.24.34/32 SnapLogic should now be able to connect to your Redshift cluster directly. If you experience any issues, follow the Amazon Redshift documentation for assistance, or please do contact us at trial-support@snaplogic.com.