Integrate data from Apache Phoenix to Amazon Redshift using Matillion

Example: Give phoenixuser full permissions on a specific table

grant 'phoenixuser', 'RWXCA', 'MY_TABLE' ``` - R: Read, W: Write, X: Execute, C: Create, A: Admin

4. Connecting to Phoenix as the User

With Kerberos Authentication

Authenticate as the user:

bash
kinit [email protected]

Connect using Phoenix sqlline:

bash
sqlline.py localhost:2181:/hbase-secure

If using Phoenix Query Server (PQS), ensure your HTTP/S request is made as the authenticated user.

5. Example: Assigning Schema Ownership (Optional)

If you are using Apache Ranger or a similar solution, you can assign fine-grained access, including at the schema or table level, using its interface.

Note:
If you need to implement application-level logical users, you would create an application-specific user table in Phoenix yourself:

CREATE TABLE USERS (
    USER_ID VARCHAR PRIMARY KEY,
    FULL_NAME VARCHAR,
    EMAIL VARCHAR,
    PASSWORD_HASH VARCHAR
);

Summary:
- Define users at the system or Kerberos level. - Grant privileges using HBase shell. - Control fine-grained access via Apache Ranger or similar policy engines if required.

There is no built-in SQL

CREATE USER

command in Apache Phoenix.

Installing the JDBC Driver

The JDBC driver for Apache Phoenix is essential for establishing connectivity between Matillion Data Productivity Cloud and a Phoenix database. However, as of this writing, the required JDBC driver is not bundled with Matillion Data Productivity Cloud due to licensing or redistribution restrictions. Therefore, you must download and install the driver manually before configuring Phoenix connections in your Matillion environment. Please follow the steps outlined below:

Downloading the Phoenix JDBC Driver

1. Navigate to the official Apache Phoenix website: https://phoenix.apache.org/.
2. Locate the downloads section and identify the appropriate JDBC driver for your Apache Phoenix version.
3. Prefer to select the Type 4 JDBC driver ("pure Java" driver), as this guarantees the broadest compatibility and does not require native client libraries.
4. Download the relevant

   .jar

   file(s) to your local system.

Uploading the Phoenix JDBC Driver to Matillion Data Productivity Cloud

Once you have the Phoenix JDBC jar file(s), you need to upload them into your Matillion Data Productivity Cloud Agent. The installation process is documented in the Matillion online documentation here: Uploading External Drivers.

In summary, you will need to:

• Access the Agent’s configuration user interface or file system, as described in the documentation.
• Upload or copy the downloaded JDBC jar file(s) to the appropriate folder or location reserved for custom drivers.
• If required, restart the Agent so that it recognizes the newly uploaded JDBC driver.

Be sure to reference the linked documentation for any version-specific details or updates on the installation process.

Configuring and Using the Phoenix JDBC Connection

With the JDBC driver installed, you can proceed to configure connections and query your Apache Phoenix database within Matillion Data Productivity Cloud. Full usage instructions, including how to define a Phoenix connection and build queries using the database, are provided here: Database Query Documentation.

This documentation will guide you through setting up the connection parameters and making use of the new JDBC capability for data extraction, loading, and transformation tasks.

Checking network connectivity

To enable successful connectivity between Matillion Data Productivity Cloud and your Apache Phoenix database, you must ensure that the database can accept incoming connections based on your deployment configuration:

• Full SaaS Agent Configuration:
  Allow incoming traffic to your Apache Phoenix database from the Matillion Data Productivity Cloud IP addresses. The list of required IP addresses is maintained here. Make sure to update your network firewall or security group rules to permit connections from these IP ranges.

• Hybrid SaaS Deployment:
  If you are using a Hybrid SaaS deployment, configure your Apache Phoenix database to accept connections from the IP addresses of your own virtual private cloud (VPC). To assist with verifying your connectivity setup, Matillion provides helpful utilities at the following location: Matillion Network Access Utilities.

Additionally, if your Apache Phoenix database is referenced via a DNS hostname, ensure that the Full SaaS or Hybrid SaaS agent can resolve the database's DNS address. This is essential for establishing and maintaining a connection. Make sure the relevant DNS servers and routing infrastructure are accessible from the agent's environment.

How to Query Data from an Apache Phoenix Database

This guide outlines the process for querying data from an Apache Phoenix database, including SQL syntax, considerations for datatype conversion with Redshift, and best-practice patterns for initial and incremental data loads.

Example Apache Phoenix SQL SELECT Queries

Here are some typical Phoenix SQL queries:

``` -- Retrieve all columns from a table SELECT * FROM users;

-- Retrieve specific columns with a filter SELECT id, username, last_login FROM users WHERE last_login > TO_DATE('2024-01-01', 'yyyy-MM-dd');

-- Aggregate data SELECT country, COUNT(*) AS user_count FROM users GROUP BY country;

-- Join example SELECT a.order_id, b.username FROM orders a JOIN users b ON a.user_id = b.id WHERE a.order_date >= TO_DATE('2024-04-01', 'yyyy-MM-dd'); ```

Datatype Conversion: Phoenix to Redshift

Apache Phoenix and Amazon Redshift have comparable but not identical datatypes. Common caveats:

• VARCHAR

  in Phoenix typically maps to

  VARCHAR

  in Redshift, but watch for length limitations.

• INTEGER

  and

  BIGINT

  generally map directly.

• DATE

  ,

  TIMESTAMP

  conversions can be nuanced—ensure timezone semantics are preserved.
• Binary/text types (

  VARBINARY

  ,

  BINARY

  ) often require explicit handling or conversion.

Validate field mappings to avoid data truncation or conversion errors during the ETL process.

Recommended Load Pattern: Initial and Incremental Loads

When transferring data from Phoenix to another system (e.g., Redshift), the preferred strategy is:

1. Initial Load
2. Extract all data (full table scan).
3. No filter clause in the SQL used by the Database Query component.

SELECT * FROM orders;

1. Incremental Loads
2. Extract only new or changed records.
3. Add a filter clause to limit rows (for example, using a timestamp or sequence number).

SELECT * FROM orders
   WHERE last_updated > TO_TIMESTAMP('2024-06-01 00:00:00', 'yyyy-MM-dd HH:mm:ss');

• Both load types can use the same Database Query component—simply adjust the WHERE clause as needed.

Read more about this strategy at Matillion Exchange: Incremental Load Data Replication Strategy.

Tip: Always validate data consistency after each incremental load by checking counts or using checksums/hashes where feasible.

Data Integration Architecture

Loading data into Amazon Redshift in advance of integration exemplifies the "divide and conquer" approach, as it separates the data loading step from subsequent transformation and integration processes. This distinction is a core advantage of the ELT (Extract, Load, Transform) architecture, as it permits organizations to deal with disparate data sources efficiently by first consolidating raw data into the data warehouse. The transformation and integration of this data—key elements for meaningful analytics—are best handled through dedicated data transformation pipelines, which provide a structured, reproducible, and modular way to manage complex business logic. Another principal benefit of the ELT model is that all data transformation and integration activities occur directly within the target Redshift database. This benefits organizations by leveraging the performance, scalability, and elasticity of the Redshift environment to process even large data volumes quickly and on demand, without incurring the cost or overhead of external transformation infrastructure.