Operations (sample payloads)

Main operations
Copy

Create CSV
Copy

Create a CSV instance that can be queried.

Sample Input

1{
2    "scope": "current_run",
3    "file": {
4        "name": "sales_data.csv",
5        "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"
6    },
7    "has_header": true,
8    "delimiter": ",",
9    "quote_char": "\"",
10    "escape_char": "\\",
11    "end_line": "Unix / LF",
12    "return_results": true
13}

Sample Output

1{
2    "id": "csv_12345",
3    "results": {
4        "QueryExecutionId": "query_67890",
5        "Result": "success",
6        "RowData": [
7            {
8                "Date": "2023-05-01",
9                "Product": "Widget A",
10                "Quantity": "100",
11                "Price": "10.99"
12            },
13            {
14                "Date": "2023-05-02",
15                "Product": "Widget B",
16                "Quantity": "50",
17                "Price": "15.99"
18            },
19            {
20                "Date": "2023-05-03",
21                "Product": "Widget C",
22                "Quantity": "75",
23                "Price": "12.99"
24            }
25        ],
26        "NextToken": null
27    },
28    "columns": [
29        {
30            "column_name": "Date",
31            "type": "string"
32        },
33        {
34            "column_name": "Product",
35            "type": "string"
36        },
37        {
38            "column_name": "Quantity",
39            "type": "int"
40        },
41        {
42            "column_name": "Price",
43            "type": "string"
44        }
45    ]
46}

Delete CSV
Copy

Delete a specified CSV.

Sample Input

1{
2    "id": "csv_12345abcde"
3}

Sample Output

1{
2    "deleted": true
3}

Export CSV file
Copy

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

1{
2    "id": "abc123def456",
3    "line_limit": 150000
4}

Sample Output

1{
2    "QueryExecutionId": "abc123def456",
3    "files": [
4        {
5            "name": "query_results_part1.csv",
6            "url": "https://example.com/query_results_part1.csv",
7            "mime_type": "text/csv",
8            "expires": 1623456789
9        },
10        {
11            "name": "query_results_part2.csv",
12            "url": "https://example.com/query_results_part2.csv",
13            "mime_type": "text/csv",
14            "expires": 1623456789
15        }
16    ]
17}

Export split CSV file
Copy

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

Sample Input

1{
2    "id": "12345abcde-6789-fghij-0123-456789klmnop",
3    "max_bytes": 15
4}

Sample Output

1{
2    "QueryExecutionId": "12345abcde-6789-fghij-0123-456789klmnop",
3    "files": [
4        {
5            "name": "query_result_part1.csv",
6            "url": "https://example.com/query_result_part1.csv",
7            "mime_type": "text/csv",
8            "expires": 1623456789
9        },
10        {
11            "name": "query_result_part2.csv",
12            "url": "https://example.com/query_result_part2.csv",
13            "mime_type": "text/csv",
14            "expires": 1623456789
15        }
16    ]
17}

Get query execution
Copy

Retrieve details and status of a query execution

Sample Input

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

Sample Output

1{
2    "QueryExecution": {
3        "QueryExecutionId": "123e4567-e89b-12d3-a456-426614174000",
4        "Status": {
5            "State": "SUCCEEDED",
6            "SubmissionDateTime": "2023-05-15T10:30:00.000Z",
7            "CompletionDateTime": "2023-05-15T10:30:15.000Z"
8        },
9        "DataScannedMB": 256.5
10    }
11}

Get query results
Copy

Retrieve results from a completed query execution

Sample Input

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

Sample Output

1{
2    "Result": "SUCCESS",
3    "RowData": [
4        {
5            "id": 1,
6            "name": "John Doe",
7            "email": "john.doe@example.com",
8            "age": 35
9        },
10        {
11            "id": 2,
12            "name": "Jane Smith",
13            "email": "jane.smith@example.com",
14            "age": 28
15        },
16        {
17            "id": 3,
18            "name": "Bob Johnson",
19            "email": "bob.johnson@example.com",
20            "age": 42
21        }
22    ],
23    "NextToken": "ZYXWVUTSRQPONMLKJIHGFEDCBA987654321"
24}

Get row count
Copy

Returns the numbers of rows in a CSV

Sample Input

1{
2    "id": "csv-12345abcde"
3}

Sample Output

1{
2    "rowCount": 1000,
3    "result": {
4        "QueryExecutionId": "query-execution-67890fghij",
5        "results": {
6            "Result": "SUCCESS",
7            "RowData": [
8                {
9                    "_col0": "1000"
10                }
11            ],
12            "NextToken": null
13        }
14    }
15}

Start query
Copy

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

Sample Input

1{
2    "id": "csv_12345",
3    "return_columns": [
4        "Name",
5        "Age",
6        "City"
7    ],
8    "rename_columns": [
9        {
10            "current_name": "Name",
11            "display_name": "Full Name"
12        }
13    ],
14    "filters": [
15        {
16            "matchAll": [
17                {
18                    "column_name": "Age",
19                    "operator": "greater_than",
20                    "value": 25
21                },
22                {
23                    "column_name": "City",
24                    "operator": "in",
25                    "value": [
26                        "New York",
27                        "Los Angeles",
28                        "Chicago"
29                    ]
30                }
31            ]
32        }
33    ],
34    "conditions_strictness": "AND",
35    "order_by": {
36        "column": "Age",
37        "sort": "Ascending"
38    },
39    "return_results": true
40}

Sample Output

1{
2    "result": {
3        "QueryExecutionId": "query_exec_67890",
4        "results": {
5            "Result": "SUCCESS",
6            "RowData": [
7                {
8                    "Full Name": "John Doe",
9                    "Age": "28",
10                    "City": "New York"
11                },
12                {
13                    "Full Name": "Jane Smith",
14                    "Age": "32",
15                    "City": "Los Angeles"
16                },
17                {
18                    "Full Name": "Mike Johnson",
19                    "Age": "35",
20                    "City": "Chicago"
21                }
22            ],
23            "NextToken": null
24        }
25    }
26}

Start query on rows
Copy

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

1{
2    "id": "csv_12345",
3    "start_row": 0,
4    "end_row": 100,
5    "return_columns": [
6        "name",
7        "email",
8        "age"
9    ],
10    "rename_columns": [
11        {
12            "current_name": "name",
13            "display_name": "Full Name"
14        }
15    ],
16    "filters": [
17        {
18            "matchAll": [
19                {
20                    "column_name": "age",
21                    "operator": "greater_than",
22                    "value": 18
23                },
24                {
25                    "column_name": "email",
26                    "operator": "like",
27                    "value": "%@example.com"
28                }
29            ]
30        }
31    ],
32    "conditions_strictness": "AND",
33    "order_by": {
34        "column": "name",
35        "sort": "Ascending"
36    }
37}

Sample Output

1{
2    "result": {
3        "QueryExecutionId": "qe-98765432"
4    }
5}

Operations (sample payloads)

Main operationsCopy

Create CSVCopy

Delete CSVCopy

Export CSV fileCopy

Export split CSV fileCopy

Get query executionCopy

Get query resultsCopy

Get row countCopy

Start queryCopy

Start query on rowsCopy

Overview

Operations

Main operations
Copy

Create CSV
Copy

Delete CSV
Copy

Export CSV file
Copy

Export split CSV file
Copy

Get query execution
Copy

Get query results
Copy

Get row count
Copy

Start query
Copy

Start query on rows
Copy