Operations (sample payloads)

Main operations

Count rows

Counts the number of rows that meet a given set of conditions. Sample Input

{
    "host": "redshift-cluster-1.abc123xyz789.us-west-2.redshift.amazonaws.com",
    "port": 5439,
    "database": "mydb",
    "schema": "public",
    "user": "admin",
    "password": "MySecurePassword123!",
    "table": "customers",
    "conditions": [
        \{
            "field": "country",
            "operator": "equal to",
            "value": "USA"
        \},
        \{
            "field": "age",
            "operator": "greater than",
            "value": 18
        \}
    ]
}

Sample Output

{
    "count": 1250
}

Delete rows

Deletes rows in your database based on criteria of your choosing. Sample Input

{
    "host": "redshift-cluster-1.abc123xyz789.us-west-2.redshift.amazonaws.com",
    "port": 5439,
    "database": "mydb",
    "schema": "public",
    "user": "admin",
    "password": "MySecurePassword123!",
    "table": "customers",
    "conditions": [
        \{
            "field": "last_purchase_date",
            "operator": "less than",
            "value": "2022-01-01"
        \},
        \{
            "field": "status",
            "operator": "equal to",
            "value": "inactive"
        \}
    ]
}

Sample Output

\{
    "count": 15,
    "deleted": true
\}

Find rows

Find rows in your database based on criteria of your choosing. Sample Input

{
    "host": "redshift-cluster-1.abc123xyz.us-west-2.redshift.amazonaws.com",
    "port": 5439,
    "database": "ticketdb",
    "schema": "public",
    "user": "admin",
    "password": "SecurePassword123!",
    "table": "listings",
    "fields": [
        "listid",
        "sellerid",
        "eventid",
        "numtickets",
        "totalprice"
    ],
    "conditions": [
        \{
            "field": "eventid",
            "operator": "equal to",
            "value": 1234
        \},
        \{
            "field": "numtickets",
            "operator": "greater than",
            "value": 2
        \}
    ],
    "limit": 10,
    "offset": 0
}

Sample Output

{
    "rows": [
        \{
            "listid": 5001,
            "sellerid": 101,
            "eventid": 1234,
            "numtickets": 4,
            "totalprice": "240.00"
        \},
        \{
            "listid": 5002,
            "sellerid": 102,
            "eventid": 1234,
            "numtickets": 3,
            "totalprice": "180.00"
        \},
        \{
            "listid": 5003,
            "sellerid": 103,
            "eventid": 1234,
            "numtickets": 5,
            "totalprice": "300.00"
        \}
    ]
}

Insert new rows

Insert one or more rows into your Redshift database. Sample Input

{
    "host": "redshift-cluster-1.abc123xyz789.us-west-2.redshift.amazonaws.com",
    "port": 5439,
    "database": "mydb",
    "schema": "public",
    "user": "admin",
    "password": "MySecurePassword123!",
    "table": "customers",
    "data": [
        \{
            "customer_id": 1001,
            "first_name": "John",
            "last_name": "Doe",
            "email": "john.doe@example.com",
            "phone": "+1-555-123-4567",
            "created_at": "2023-04-15T10:30:00Z"
        \},
        \{
            "customer_id": 1002,
            "first_name": "Jane",
            "last_name": "Smith",
            "email": "jane.smith@example.com",
            "phone": "+1-555-987-6543",
            "created_at": "2023-04-15T11:45:00Z"
        \}
    ]
}

Sample Output

\{
    "count": 2,
    "inserted": true
\}

Run SQL query

Execute raw specified SQL code on the chosen database Sample Input

\{
    "host": "redshift-cluster-1.abc123xyz789.us-west-2.redshift.amazonaws.com",
    "port": 5439,
    "database": "mydb",
    "schema": "public",
    "user": "admin",
    "password": "MySecurePassword123!",
    "sql": "SELECT * FROM public.users WHERE age > $1 AND city = $2",
    "sql_parameters": [
        30,
        "New York"
    ]
\}

Sample Output

{
    "count": 3,
    "result": [
        \{
            "id": 1,
            "name": "John Doe",
            "age": 35,
            "city": "New York"
        \},
        \{
            "id": 2,
            "name": "Jane Smith",
            "age": 42,
            "city": "New York"
        \},
        \{
            "id": 3,
            "name": "Bob Johnson",
            "age": 38,
            "city": "New York"
        \}
    ]
}

Update rows

Updates rows in your database based on criteria of your choosing. Sample Input

{
    "host": "redshift-cluster-1.abc123xyz.us-west-2.redshift.amazonaws.com",
    "port": 5439,
    "database": "mydb",
    "schema": "public",
    "user": "admin",
    "password": "MySecurePassword123!",
    "table": "customers",
    "data": \{
        "email": "johndoe@example.com",
        "phone": "+1-555-123-4567"
    \},
    "conditions": [
        \{
            "field": "customer_id",
            "operator": "equal to",
            "value": 1001
        \}
    ]
}

Sample Output

\{
    "count": 1,
    "updated": true
\}

DDL operations

List table fields (DDL)

Note that DDL operations can only be called directly by Connectors API, or when using CustomJS in the Embedded solution editor for e.g. DDL-dependent data mapping

Sample Input

\{
    "host": "redshift-cluster-1.abcdefghijkl.us-west-2.redshift.amazonaws.com",
    "port": 5439,
    "database": "mydatabase",
    "schema": "public",
    "user": "admin",
    "password": "MySecurePassword123!"
\}

Sample Output

{}

List tables (DDL)

Note that DDL operations can only be called directly by Connectors API, or when using CustomJS in the Embedded solution editor for e.g. DDL-dependent data mapping

Sample Input

\{
    "host": "redshift-cluster-1.abc123xyz789.us-west-2.redshift.amazonaws.com",
    "port": 5439,
    "database": "mydb",
    "schema": "public",
    "user": "admin",
    "password": "MySecurePassword123!"
\}

Sample Output

{}