Integrate data from Sybase SQL Anywhere to Snowflake using Matillion

Creating a User in Sybase SQL Anywhere

To add a new user to a Sybase SQL Anywhere database, use the

CREATE USER

Prerequisites

• You must be connected to the database as a user with sufficient privileges (typically, the DBA or a user with

  CREATE USER

  or

  DBA

  authority).

Instructions

1. Connect to Your Database

Connect to your database using your client tool of choice (e.g., Interactive SQL, SQL Central, or via ODBC/JDBC).

-- Example connection string (adjust for your environment):
CONNECT TO demo USER DBA IDENTIFIED BY sql;

2. Create the User

Run the following SQL script, replacing

username

password

CREATE USER username IDENTIFIED BY 'password';

Example

CREATE USER reporting IDENTIFIED BY 'Rep0rtPass!';

This statement creates a user called

reporting

Rep0rtPass!

3. Grant Appropriate Permissions (Optional)

By default, a new user has minimal rights. Assign privileges as required. For example, to grant SELECT permission on a table:

GRANT SELECT ON table_name TO username;

Example

GRANT SELECT ON sales_data TO reporting;

4. (Optional) Grant DBA Authority

If the new user requires database administrator capabilities, use:

GRANT DBA TO username;

Example

GRANT DBA TO reporting;

Additional Notes

• Passwords are case-sensitive and can include special characters.
• You can review all users by querying

  SYSUSER

  :

SELECT * FROM SYSUSER;

DROP USER username;

Be sure to tailor privileges to the needs of each user to maintain database security.

Installing the JDBC driver

The Sybase SQL Anywhere JDBC driver is required for Matillion Data Productivity Cloud to connect to Sybase SQL Anywhere databases. At the time of writing, due to licensing and redistribution restrictions, this driver is not bundled with Matillion Data Productivity Cloud. Users must manually obtain and install the JDBC driver before SQL Anywhere integrations can be configured.

Downloading the JDBC driver

1. Visit the official Sybase SQL Anywhere page:
   https://www.sap.com/products/sybase-sql-anywhere.html
2. Locate the section for JDBC drivers. Look specifically for a Type 4 JDBC driver because it is a pure Java driver, platform-independent, and recommended for cloud-based integrations such as Matillion.
3. Download the latest version of the Type 4 JDBC driver that matches your SQL Anywhere server version.

Note: Access to the download may require creating an SAP account and accepting relevant license agreements.

Preparing for installation

• After downloading, extract the JAR file(s) supplied as the Type 4 JDBC driver.
• Record the full path to the main JDBC driver JAR file. Additional dependency JARs, if any, must be gathered as well.

Uploading the driver to Matillion Data Productivity Cloud

1. Follow the step-by-step instructions provided by Matillion for uploading external JDBC drivers:
   https://docs.matillion.com/data-productivity-cloud/agent/docs/uploading-external-drivers/
2. Use the Matillion Agent interface or method suitable for your deployment model.
3. Upload the JDBC driver JAR file(s), ensuring all required files are included and correctly referenced.
4. Restart or refresh the agent process as instructed to complete registration of the new driver.

Using the Sybase SQL Anywhere JDBC driver

Once the driver is installed, you can use it within Matillion Data Productivity Cloud for database connectivity and queries. Detailed instructions for configuring and using the driver with workflow components are available at:
https://docs.matillion.com/data-productivity-cloud/designer/docs/database-query/

These usage instructions cover setup of connections, parameter requirements, and best practices for leveraging the new JDBC driver within your Matillion workflows.

Checking network connectivity

To ensure successful connectivity between Matillion Data Productivity Cloud and your Sybase SQL Anywhere database, you must configure your network to allow incoming connections from the correct sources, depending on your deployment type:

• Full SaaS agent configuration:
  Ensure that your Sybase SQL Anywhere database allows incoming connections from the public IP addresses used by Matillion agents. The list of IP addresses to allow can be found at: https://docs.matillion.com/data-productivity-cloud/security/docs/allowing-ip-addresses/.

• Hybrid SaaS deployment:
  You must allow incoming connections from resources within your own virtual private cloud (VPC) where the Matillion Hybrid SaaS agent is running. To assist with verifying network paths between your VPC and the target database, you can use the tools available at: https://exchange.matillion.com/data-productivity-cloud/pipeline/check-network-access/.

Additionally, if you are referencing the Sybase SQL Anywhere database by a DNS address (rather than by IP address), ensure that the Full SaaS or Hybrid SaaS agent you are using is able to resolve the DNS name to the correct IP address. This may involve managing DNS resolution settings or providing access to required name servers in your networking infrastructure.

Querying Data from Sybase SQL Anywhere

This guide describes how to extract data from a Sybase SQL Anywhere database using SQL queries. These instructions are relevant for users seeking to load data—initially or incrementally—into a destination such as Snowflake, possibly utilizing a tool such as Matillion, in which datatype conversions may occur.

Example SQL SELECT Statements

Below are sample SQL queries for extracting data from a Sybase SQL Anywhere database.

Simple SELECT all:

SELECT *
FROM sales.orders;

Selecting specific columns:

SELECT order_id, customer_id, order_date, total_amount
FROM sales.orders;

Using WHERE to filter rows (for incremental loads):

SELECT *
FROM sales.orders
WHERE order_date > '2024-01-01';

Selecting using JOIN:

SELECT o.order_id, c.customer_name
FROM sales.orders AS o
JOIN sales.customers AS c
    ON o.customer_id = c.customer_id;

Datatype Conversion Considerations

When transferring data from Sybase SQL Anywhere to Snowflake, data types may require conversion. Examples:

• Sybase

  VARCHAR

  and

  CHAR

  map to Snowflake

  VARCHAR

  .
• Sybase

  INT

  ,

  SMALLINT

  map to Snowflake

  NUMBER

  .
• Date/time types (

  DATE

  ,

  TIME

  ,

  TIMESTAMP

  ) generally convert directly but verify compatibility.
• Watch for differences in handling nulls, case sensitivity, or time zone data.

Always validate your mappings and test with sample data.

Loading Patterns: Initial vs. Incremental Loads

The preferred approach for integrating Sybase SQL Anywhere data is to run a once-off initial extraction, then perform regular incremental extracts using the same Database Query component with query variations as follows:

Initial Load

Execute the Database Query with no filter condition to extract the full dataset.

SELECT *
FROM sales.orders;

Incremental Load

Add a filter clause restricting extraction to new or changed records only (for example, using a

last_updated

SELECT *
FROM sales.orders
WHERE last_updated > '2024-06-01 00:00:00';

Learn more about this approach and best practices in Matillion’s article on incremental-load data replication strategy.

By adapting the query's WHERE clause for incremental loads, you can ensure efficiency and data consistency across your integration process.

Data Integration Architecture

Loading data in advance of integration exemplifies the "divide and conquer" approach within the ELT (Extract, Load, Transform) architecture by breaking the process into two distinct steps: first loading raw data into the target Snowflake environment, and then performing necessary integrations and transformations. This separation allows each phase to be managed independently, enhancing flexibility and control. Data integration often involves extensive transformation, and the most effective way to manage these activities is through dedicated data transformation pipelines. These pipelines automate and orchestrate the transformation logic, ensuring reliable, repeatable processes. Additionally, a significant benefit of the ELT architecture is that all data transformation and integration tasks are performed directly in the Snowflake database. As a result, transformations are not only fast and scalable—leveraging Snowflake’s high-performance processing capabilities and elastic compute—but also cost-effective, as there is no need to invest in or maintain separate data processing infrastructure outside of your Snowflake environment.