@Rocky trying to get AWS Redshift connected to StarRocks via JDBC. I'm able to connect and see the metadata. However, I cannot run Show Tables or run any Select queries. The potential root cause is that StarRocks sends a metadata query using relkind='e' (foreign tables), which is a PostgreSQL 9.1+ feature. Redshift is based on PostgreSQL 8.0.2 and does not support this type. Tried the native Redshift JDBC driver (com.amazon.redshift.Driver) but it is blocked with "not support yet". How do we solve this?

Hi there! I'm Rocky, and I'd be happy to help you with your StarRocks and Redshift connection. The issue you're encountering is a known compatibility problem between the StarRocks PostgreSQL JDBC dialect and AWS Redshift's older engine (based on PostgreSQL 8.0.2). Specifically, StarRocks' standard PostgreSQL dialect uses

relkind='e'

to discover foreign tables, a feature introduced in PostgreSQL 9.1 that Redshift does not support. To solve this, StarRocks introduced a dedicated Redshift dialect for the JDBC Catalog in more recent versions (starting from around v3.2.5+ and v3.1.10+). Recommended Solution: Upgrade and Use the Redshift Dialect The "not support yet" error you saw with the native Redshift driver indicates that your current StarRocks version doesn't yet have the mapping for the Redshift dialect. 1. Upgrade StarRocks: Ensure you are running StarRocks v3.2.5 or later (or v3.3.x). These versions include the fix that avoids

relkind='e'

when it detects a Redshift connection. 2. Use the Redshift Driver and URI: Once upgraded, configure your catalog using the native Redshift driver and the

jdbc:redshift

prefix. This tells StarRocks to use the specialized Redshift dialect instead of the generic PostgreSQL one. Example Configuration:

sql
CREATE EXTERNAL CATALOG redshift_catalog
PROPERTIES
(
    "type" = "jdbc",
    "user" = "<your_user>",
    "password" = "<your_password>",
    "jdbc_uri" = "jdbc:redshift://<redshift_endpoint>:<port>/<database>",
    "driver_url" = "<https://s3.amazonaws.com/redshift-downloads/drivers/jdbc/2.1.0.25/redshift-jdbc42-2.1.0.25.jar>",
    "driver_class" = "com.amazon.redshift.jdbc42.Driver"
);

Why this works: * When the

jdbc_uri

starts with

jdbc:redshift

or the

driver_class

is a Redshift driver, StarRocks (in newer versions) selects the

RedshiftDialect

. * The

RedshiftDialect

uses metadata queries that are compatible with PostgreSQL 8.0.2, avoiding the

relkind='e'

filter that causes your current errors. If you cannot upgrade immediately: Unfortunately, the metadata queries are hardcoded within the StarRocks FE dialects. If you are stuck on an older version: * Manual JDBC External Tables (Deprecated): You could try the older External Table syntax (instead of a Catalog), though it often shares the same dialect logic. * Workaround: Some users have had success by creating a

VIEW

in Redshift that mimics the table schema, but

SHOW TABLES

and schema discovery during

SELECT

will likely still trigger the same error. The most robust path forward is definitely upgrading to a version that officially supports the Redshift dialect! Let me know if you have any other questions. References • data_source/catalog/jdbc_catalog.md • data_source/External_table.md • unloading/arrow_flight.md

@Rocky We are already using 4.0.4

That is interesting! As of now, the official StarRocks stable releases are currently in the 3.x series (with 3.3.x being the latest stable branch). There isn't an official release version 4.0.4 for the StarRocks database engine yet. It is possible you might be referring to: 1. Version 3.0.4: A stable release from the 3.0 series. 2. Version 2.0.4: An older release from the 2.0 series. 3. A specific connector or tool: For example, a third-party driver or a specific deployment tool that might have its own versioning. Could you please double-check your version by running the following command in your SQL client?

sql
SELECT current_version();

If you are indeed on a custom or upcoming build, or if you meant 3.0.4, let me know if there’s a specific feature or issue you’re looking into! I'm here to help. References • faq/shared_data_faq.md • release_notes/release-2.0.md

@Rocky SELECT current_version(); current_version() 4.0.5-ee-8219e87

Hello! It looks like you are running StarRocks Enterprise Edition version 4.0.5. I am Rocky, your StarRocks assistant. How can I help you today? Feel free to ask any questions regarding: * Query Syntax or Errors: If you're running into issues with a specific SQL statement. * Performance Tuning: Tips on optimizing your tables or queries. * Feature Usage: How to use new features like asynchronous materialized views, shared-data architecture, or external catalogs. * Cluster Management: Questions about configuration or troubleshooting. What's on your mind? References • faq/shared_data_faq.md • release_notes/release-2.4.md • release_notes/release-3.2.md

@Jonathan Wills - can you query SVV_ALL_TABLES or SVV_REDSHIFT_TABLES?

We tried querying SVV_ALL_TABLES and SVV_REDSHIFT_TABLES and both return "Unknown table" as the error. The JDBC connection allows the connection to Redshift since "SHOW DATABASES;" works but StarRocks catalog metadata resolution fails resulting in error with any query on the tables or data.

@Aaron - what does select version() show from redshift?

Ran on the Redshift AWS Query Editor: SELECT version();

PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.117891

The above Query ran well on the AWS query editor. In regards to starrocks: SELECT version() (more reveals the starrock version than redshift?);

{'version()': '8.0.33'}

The above query failed with:

FE RPC failure, reason=Internal error processing getTablesInfo

For starrocks we use current_version(). The version Is about the MySQL protocol version in use.

Understood, I'm new to starrocks and there's some gaps in knowledge I'm slowly catching up on.

Check this issue out to make sure you've got the right syntax. https://github.com/StarRocks/starrocks/issues/36828 If so, I think it's a bug. I didn't find anyone talking about redshift jdbc in the slack history besides this thread.

These are the two issues I had found: https://github.com/StarRocks/starrocks/issues/36828 https://github.com/StarRocks/starrocks/issues/67948 I was hoping that there was a workaround, an update with StarRocks, or an update with Redshift.