Document toolboxDocument toolbox

Forward response to Amazon S3

Request

Using the destination object in a query request you can have query responses forwarded to your organization's data storage services, including Amazon S3. 

To send your response to S3, include the destination object in your request and set type to s3 like this:

"destination": {
 "type":"s3"
}

Parameters

With the destination.type set to s3, you need to specify some additional parameters specific to Amazon Web Services to authenticate the connection and save the data contained in the response:

ParameterDescription

aws.bucket

Bucket name that should exist in your organization's AWS account.

aws.region

AWS region. See the available region codes here.

aws.accesskey

This is your Access Key ID for your organization's AWS account.

aws.secretkey

This is your Secret Access Key for your organization's AWS account.

format

The format used to store the information in the file uploaded to S3. It may be zip, gzip or plain. If not specified, the default format will be zip.

file.password

Add a password for the compressed file. Only valid for zip format.

notification.email

Email address to which to send a notification when the request has been completed. This parameter is optional.

multiplefile

To obtain the results in just one file, set this parameter to false. (By default, it is set to true).

If this parameter is deactivated, the results will not be accessible until the query is finished.

Note that you can only set this parameter to false if you select gzip or plain in the format parameter.

retention.size

Maximum file size (megabytes) before updating to S3. The default value is 500 MB. The smallest value is 50MB and the biggest is 500MB.

This parameter is only valid if the parameter multiplefile is set to true.

retention.time

Maximum time (seconds) before updating to S3. The default time is infinite. The smallest value is 5 min and the biggest is 24h. 

This parameter is only valid if the parameter multiplefile is set to true.

Query using QueryID and forwarding to S3

In the example below, we are using our own AWS account to deliver the S3 files.

{
    "query": "from demo.ecommerce.data select *",
    "from": 1546083949,
    "to": 1546947949,
    "destination": {
        "type": "s3",
        "params": {
            "aws.bucket": "my-bucket",
            "aws.region": "eu-west-1",
            "aws.accesskey": "abcdefghijkl",
            "aws.secretkey": "Tg4T0aGKvd/aaaaaaaWWQv3Vs0kS15tpn3kbd0V2UZ",
			"notification.email":"john.smith@company.com",
            "format": "zip"
        }
    }
}

Response

When the query request is received and the results are successfully being forwarded to S3, a response that includes the job ID is returned to the query sender. This job ID is needed to manage the task. For more information, see the Job requests article.

{
    "msg": "",
    "status": 0,
    "object": {
        "id": "193507c8-93df-405f-bdf7-acf1a91890e2"
    }
}

Files are uploaded in this format:

apiv2_<task id>_<index>.csv.(zip|gz)
When you export or forward a .csv response, only the first file will contain the .csv header row. The "headers" in trailing files are not real .csv headers, but rather the sequential row of data from the .csv export.

Examples:

apiv2_983c5a6b-7081-40eb-a631-7492d9badbfe_001.csv.zip
apiv2_983c5a6b-7081-40eb-a631-7492d9badbfe_002.csv.zip
apiv2_983c5a6b-7081-40eb-a631-7492d9badbfe_003.csv.zip
apiv2_983c5a6b-7081-40eb-a631-7492d9badbfe_004.csv.zip

apiv2_4c92c89e-19a3-4e49-bb8a-4dbf30d47214_001.csv.gz
apiv2_4c92c89e-19a3-4e49-bb8a-4dbf30d47214_002.csv.gz

If you want to see the paths of the uploaded files you can query the task status to see the current upload files (not finished tasks) or see all the files paths (finished task).

{
    "status": 0,
    "cid": "tjIqEhbIKZ",
    "timestamp": 1533548393561,
    "object": {
        "status": "COMPLETED",
        "id": "983c5a6b-7081-40eb-a631-7492d9badbfe",
        "eventGenerated": 2619588,
        "eventsSent": 2619588,
        "lastDatetime": 0,
        "extra": {
            "paths": [
                "https://sis-2523.s3.eu-west-1.amazonaws.com/apiv2_983c5a6b-7081-40eb-a631-7492d9badbfe_001.csv.zip",
                "https://sis-2523.s3.eu-west-1.amazonaws.com/apiv2_983c5a6b-7081-40eb-a631-7492d9badbfe_002.csv.zip",
                "https://sis-2523.s3.eu-west-1.amazonaws.com/apiv2_983c5a6b-7081-40eb-a631-7492d9badbfe_003.csv.zip",
                "https://sis-2523.s3.eu-west-1.amazonaws.com/apiv2_983c5a6b-7081-40eb-a631-7492d9badbfe_004.csv.zip"
            ]
        },
        "type": "s3",
        "friendlyName": "s3_ezilvjQ1Sh",
        "query": "LinQ_Query",
        "cid": "lSypa1lvKv",
        "owner": "kW5Ule1m*omUatc8tu4S5LuQSnX9UpLs",
        "table": "demo.ecommerce.data",
        "error": []
    }
}

For more information about Amazon S3, visit the Amazon S3 website.