Operations (sample payloads)

Main operations

Create CSV

Create a CSV instance that can be queried. Sample Input

{
    "scope": "current_run",
    "file": \{
        "name": "sales_data.csv",
        "content": "Date,Product,Quantity,Price\n2023-05-01,Widget A,100,10.99\n2023-05-02,Widget B,50,15.99\n2023-05-03,Widget C,75,12.99"
    \},
    "has_header": true,
    "delimiter": ",",
    "quote_char": "\"",
    "escape_char": "\\",
    "end_line": "Unix / LF",
    "return_results": true
}

Sample Output

{
    "id": "csv_12345",
    "results": {
        "QueryExecutionId": "query_67890",
        "Result": "success",
        "RowData": [
            \{
                "Date": "2023-05-01",
                "Product": "Widget A",
                "Quantity": "100",
                "Price": "10.99"
            \},
            \{
                "Date": "2023-05-02",
                "Product": "Widget B",
                "Quantity": "50",
                "Price": "15.99"
            \},
            \{
                "Date": "2023-05-03",
                "Product": "Widget C",
                "Quantity": "75",
                "Price": "12.99"
            \}
        ],
        "NextToken": null
    },
    "columns": [
        \{
            "column_name": "Date",
            "type": "string"
        \},
        \{
            "column_name": "Product",
            "type": "string"
        \},
        \{
            "column_name": "Quantity",
            "type": "int"
        \},
        \{
            "column_name": "Price",
            "type": "string"
        \}
    ]
}

Delete CSV

Delete a specified CSV. Sample Input

{
    "id": "csv_12345abcde"
}

Sample Output

{
    "deleted": true
}

Export CSV file

Export a CSV file with results of a query execution. Query execution needs to be in status "SUCCEEDED" in order to retrieve CSV file. Sample Input

\{
    "id": "abc123def456",
    "line_limit": 150000
\}

Sample Output

{
    "QueryExecutionId": "abc123def456",
    "files": [
        \{
            "name": "query_results_part1.csv",
            "url": "https://example.com/query_results_part1.csv",
            "mime_type": "text/csv",
            "expires": 1623456789
        \},
        \{
            "name": "query_results_part2.csv",
            "url": "https://example.com/query_results_part2.csv",
            "mime_type": "text/csv",
            "expires": 1623456789
        \}
    ]
}

Export split CSV file

Export a CSV file with results of a query execution. CSV can be split into multiple files by file size limit. Sample Input

\{
    "id": "12345abcde-6789-fghij-0123-456789klmnop",
    "max_bytes": 15
\}

Sample Output

{
    "QueryExecutionId": "12345abcde-6789-fghij-0123-456789klmnop",
    "files": [
        \{
            "name": "query_result_part1.csv",
            "url": "https://example.com/query_result_part1.csv",
            "mime_type": "text/csv",
            "expires": 1623456789
        \},
        \{
            "name": "query_result_part2.csv",
            "url": "https://example.com/query_result_part2.csv",
            "mime_type": "text/csv",
            "expires": 1623456789
        \}
    ]
}

Get query execution

Retrieve details and status of a query execution Sample Input

{
    "id": "123e4567-e89b-12d3-a456-426614174000"
}

Sample Output

{
    "QueryExecution": {
        "QueryExecutionId": "123e4567-e89b-12d3-a456-426614174000",
        "Status": \{
            "State": "SUCCEEDED",
            "SubmissionDateTime": "2023-05-15T10:30:00.000Z",
            "CompletionDateTime": "2023-05-15T10:30:15.000Z"
        \},
        "DataScannedMB": 256.5
    }
}

Get query results

Retrieve results from a completed query execution Sample Input

\{
    "id": "a1b2c3d4-5e6f-7g8h-9i0j-1k2l3m4n5o6p",
    "max_results": 50,
    "next_token": "ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789"
\}

Sample Output

{
    "Result": "SUCCESS",
    "RowData": [
        \{
            "id": 1,
            "name": "John Doe",
            "email": "john.doe@example.com",
            "age": 35
        \},
        \{
            "id": 2,
            "name": "Jane Smith",
            "email": "jane.smith@example.com",
            "age": 28
        \},
        \{
            "id": 3,
            "name": "Bob Johnson",
            "email": "bob.johnson@example.com",
            "age": 42
        \}
    ],
    "NextToken": "ZYXWVUTSRQPONMLKJIHGFEDCBA987654321"
}

Get row count

Returns the numbers of rows in a CSV Sample Input

{
    "id": "csv-12345abcde"
}

Sample Output

{
    "rowCount": 1000,
    "result": {
        "QueryExecutionId": "query-execution-67890fghij",
        "results": {
            "Result": "SUCCESS",
            "RowData": [
                {
                    "_col0": "1000"
                }
            ],
            "NextToken": null
        }
    }
}

Start query

Start a query execution against a CSV instance. Returns a query execution ID which can be used in Get Query Results. Sample Input

{
    "id": "csv_12345",
    "return_columns": [
        "Name",
        "Age",
        "City"
    ],
    "rename_columns": [
        \{
            "current_name": "Name",
            "display_name": "Full Name"
        \}
    ],
    "filters": [
        {
            "matchAll": [
                \{
                    "column_name": "Age",
                    "operator": "greater_than",
                    "value": 25
                \},
                \{
                    "column_name": "City",
                    "operator": "in",
                    "value": [
                        "New York",
                        "Los Angeles",
                        "Chicago"
                    ]
                \}
            ]
        }
    ],
    "conditions_strictness": "AND",
    "order_by": \{
        "column": "Age",
        "sort": "Ascending"
    \},
    "return_results": true
}

Sample Output

{
    "result": {
        "QueryExecutionId": "query_exec_67890",
        "results": {
            "Result": "SUCCESS",
            "RowData": [
                \{
                    "Full Name": "John Doe",
                    "Age": "28",
                    "City": "New York"
                \},
                \{
                    "Full Name": "Jane Smith",
                    "Age": "32",
                    "City": "Los Angeles"
                \},
                \{
                    "Full Name": "Mike Johnson",
                    "Age": "35",
                    "City": "Chicago"
                \}
            ],
            "NextToken": null
        }
    }
}

Start query on rows

Start a query execution against a number of rows in a CSV instance. Returns a query execution ID which can be used in Get Query Results. Note this adds a new column called 'tray_io_row_number' to the CSV. To guarentee consistent results when reading a number of rows, please order by a column whose values are unique for every row. Sample Input

{
    "id": "csv_12345",
    "start_row": 0,
    "end_row": 100,
    "return_columns": [
        "name",
        "email",
        "age"
    ],
    "rename_columns": [
        \{
            "current_name": "name",
            "display_name": "Full Name"
        \}
    ],
    "filters": [
        {
            "matchAll": [
                \{
                    "column_name": "age",
                    "operator": "greater_than",
                    "value": 18
                \},
                \{
                    "column_name": "email",
                    "operator": "like",
                    "value": "%@example.com"
                \}
            ]
        }
    ],
    "conditions_strictness": "AND",
    "order_by": \{
        "column": "name",
        "sort": "Ascending"
    \}
}

Sample Output

{
    "result": {
        "QueryExecutionId": "qe-98765432"
    }
}