Hey there! I get it—you were digging through the main Redshift CLI command docs trying to find a way to pass SQL statements as strings and execute them remotely, but couldn't spot a direct command for that. No worries, there's a dedicated solution for this using AWS's Redshift Data API, which has its own set of CLI subcommands.

The Fix: Use redshift-data CLI Commands #

The Redshift Data API eliminates the need for JDBC/ODBC connections to run queries, and the CLI has full support for it. Here's how to use it:

1. Submit Your SQL Query #

Use the execute-statement command to send your query to Redshift. Replace the placeholders with your cluster/database details:

aws redshift-data execute-statement \
    --cluster-identifier my-redshift-cluster \
    --database dev \
    --db-user admin \
    --sql "SELECT customer_id, email FROM customers LIMIT 5;"

Let's break down the key parameters:

• --cluster-identifier: The name of your Redshift cluster
• --database: The target database (e.g., the default dev database)
• --db-user: A database user with permissions to run the query
• --sql: Your full SQL statement, wrapped in quotes

When you run this, you'll get a statement ID in the response, like this:

{
  "Id": "a1b2c3d4-5678-90ef-ghij-klmnopqrstuv"
}

2. Fetch the Query Results #

Use the get-statement-result command with the statement ID from the previous step to retrieve your results:

aws redshift-data get-statement-result --id "a1b2c3d4-5678-90ef-ghij-klmnopqrstuv"

The response will include column metadata and your query results in JSON format:

{
  "ColumnMetadata": [
    {"name": "customer_id", "typeName": "integer"},
    {"name": "email", "typeName": "varchar"}
  ],
  "Records": [
    [{"longValue": 1}, {"stringValue": "customer1@example.com"}],
    [{"longValue": 2}, {"stringValue": "customer2@example.com"}]
  ]
}

Bonus Tips #

• Check Query Status: For long-running queries, use describe-statement to track progress:

  aws redshift-data describe-statement --id "a1b2c3d4-5678-90ef-ghij-klmnopqrstuv"
  

• Redshift Serverless: If you're using Serverless instead of a provisioned cluster, replace --cluster-identifier with --workgroup-name.
• IAM Permissions: Make sure your AWS IAM identity has the redshift-data:ExecuteStatement and redshift-data:GetStatementResult permissions attached.

内容的提问来源于stack exchange，提问作者Sai Bhasker Raju