如何在AWS Glue Spark Shell中向RDS PostgreSQL执行原生SQL？

Absolutely, you can execute raw PostgreSQL SQL statements like CREATE INDEX, CREATE TABLE, or even DML commands directly in the AWS Glue Scala Spark Shell—using your pre-existing Glue connection to RDS PostgreSQL. Here’s a straightforward, step-by-step guide to make this work:

Step 1: Fetch Your Glue Connection Details #

First, you need to pull the JDBC URL, username, and password stored in your Glue connection. The Glue Shell has built-in access to the AWS Glue SDK, so you can retrieve these details programmatically:

import com.amazonaws.services.glue.AWSGlueClientBuilder
import com.amazonaws.services.glue.model.GetConnectionRequest

// Initialize the Glue client
val glueClient = AWSGlueClientBuilder.defaultClient()

// Replace "your-glue-connection-name" with the actual name of your Glue connection
val connRequest = new GetConnectionRequest().withName("your-glue-connection-name")
val glueConnection = glueClient.getConnection(connRequest).getConnection

// Extract the JDBC connection details
val jdbcUrl = glueConnection.getConnectionProperties.get("JDBC_CONNECTION_URL")
val dbUsername = glueConnection.getConnectionProperties.get("USERNAME")
val dbPassword = glueConnection.getConnectionProperties.get("PASSWORD")

Step 2: Execute Raw SQL Using JDBC #

With the connection details in hand, you can use standard JDBC to connect directly to your PostgreSQL instance and run your native SQL commands. The Postgres JDBC driver is pre-installed in the Glue Shell, so no extra setup is needed:

import java.sql.DriverManager
import java.sql.Statement

// Load the Postgres JDBC driver (already available in Glue Shell)
Class.forName("org.postgresql.Driver")

// Establish a direct JDBC connection to your RDS PostgreSQL instance
val jdbcConnection = DriverManager.getConnection(jdbcUrl, dbUsername, dbPassword)

// Create a statement object to execute SQL
val sqlStatement = jdbcConnection.createStatement()

// Example 1: Create a new table
val createTableSql = """
CREATE TABLE IF NOT EXISTS customer (
    id SERIAL PRIMARY KEY,
    full_name VARCHAR(150) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    signup_date DATE
)
"""
sqlStatement.execute(createTableSql)
println("Customer table created (or already exists)")

// Example 2: Create an index on the email column
val createIndexSql = "CREATE INDEX IF NOT EXISTS idx_customer_email ON customer(email)"
sqlStatement.execute(createIndexSql)
println("Email index created (or already exists)")

// Clean up resources to avoid leaks
sqlStatement.close()
jdbcConnection.close()

Key Notes & Best Practices #

• IAM Permissions: Ensure your Glue execution role has the glue:GetConnection permission to retrieve connection details. Also, confirm your RDS security group allows inbound traffic from Glue, and the database user has the necessary privileges (like CREATE TABLE, CREATE INDEX) on the target database.
• SQL Injection Prevention: For dynamic SQL (using variable values), use PreparedStatement instead of Statement to avoid injection risks. Here’s a quick example:

  val insertUserSql = "INSERT INTO customer(full_name, email) VALUES (?, ?)"
  val preparedStmt = jdbcConnection.prepareStatement(insertUserSql)
  preparedStmt.setString(1, "Jane Smith")
  preparedStmt.setString(2, "jane.smith@example.com")
  preparedStmt.executeUpdate()
  preparedStmt.close()
  

• Resource Cleanup: Always close JDBC statements and connections after use to prevent resource leaks in the Shell environment.

内容的提问来源于stack exchange，提问作者Randomize