http_request

BETA

This module contains functions and procedures that allows making requests to external endpoints.

For manual installations of the CARTO Analytics Toolbox, after installing it for the first time, and before using any HTTP Request function you need to call the SETUP procedure to configure the HTTP Request functions. It also optionally sets default credentials.

HTTP_REQUEST

HTTP_REQUEST(url, options[, api_base_url, api_access_token])

warning

This function consumes HTTP Request quota. Each call consumes as many units of quota as the number of rows your input table or query has. Before running, we recommend checking the size of the data to be geocoded and your available quota using the GET_HTTP_REQUEST_QUOTA_INFO function.

warning

Calls to this function are limited to a maximum number of rows in your input table or query. At this moment, the maximum number of requests per call is 10.

Description

Performs a request to an external URL and returns the results.

• url: VARCHAR specifies the url to make the request.

• options: VARCHAR containing a valid JSON with the options in curl syntax. It is used through a fetch request, so options should be compliant with fetch. Notice that when adding a body, the property should be stringified if it is required buy the request.

• api_base_url (optional): VARCHAR url of the API where the customer account is stored.

• api_access_token (optional): VARCHAR an API Access Token that is allowed to use the HTTP Request API.

Return type

VARCHAR

Endpoint response in base64. If a Content-Type is specified with the the value application/json or text/plain the response will be returned in utf-8.

warning

The maximum VARCHAR length supported by Redshift is 65535 bytes. This function will fail for responses bigger than this size.

Examples

SELECT carto.HTTP_REQUEST(
    'https://jsonplaceholder.typicode.com/posts/1',
    '{
        "method": "GET",
        "headers":{
            "Content-Type":"application/json"
        },
        "mode": "cors",
        "cache": "default"
    }');
-- {
--   "userId": 1,
--   "id": 1,
--   "title": "sunt aut facere repellat provident occaecati excepturi optio reprehenderit",
--   "body": "quia et suscipit\nsuscipit recusandae consequuntur expedita et cum\nreprehenderit molestiae ut ut quas totam\nnostrum rerum est autem sunt rem eveniet architecto"
-- }

SELECT carto.HTTP_REQUEST(
    'https://jsonplaceholder.typicode.com/posts/1',
    '{
        "method": "GET",
        "headers":{
            "Content-Type":"application/json"
        },
        "mode": "cors",
        "cache": "default"
    }',
    'my_api_base_url', 'my_api_access_token');
-- {
--   "userId": 1,
--   "id": 1,
--   "title": "sunt aut facere repellat provident occaecati excepturi optio reprehenderit",
--   "body": "quia et suscipit\nsuscipit recusandae consequuntur expedita et cum\nreprehenderit molestiae ut ut quas totam\nnostrum rerum est autem sunt rem eveniet architecto"
-- }

SELECT carto.HTTP_REQUEST(
    'https://jsonplaceholder.typicode.com/posts/1',
    $${
        "method": "GET",
        "headers":{
            "Content-Type":"application/json"
        },
        "mode": "cors",
        "cache": "default",
        "body": "{\"text\":\"hello from redshift\"}"
    }$$,
    'my_api_base_url', 'my_api_access_token');
-- {
--   "userId": 1,
--   "id": 1,
--   "title": "sunt aut facere repellat provident occaecati excepturi optio reprehenderit",
--   "body": "quia et suscipit\nsuscipit recusandae consequuntur expedita et cum\nreprehenderit molestiae ut ut quas totam\nnostrum rerum est autem sunt rem eveniet architecto"
-- }

SELECT carto.HTTP_REQUEST(
    'https://jsonplaceholder.typicode.com/posts/1',
    '{
        "method": "POST",
        "headers":{
            "Content-Type":"application/json"
        },
        "mode": "cors",
        "cache": "default",
        "body": ' || COALESCE(body, '""') || '
    }',
    'my_api_base_url', 'my_api_access_token');
FROM my-schema.my-table;
-- [ { "userId": 1, "id": 1, "title": "...", , "body": "..." }, ... { "userId": 1, "id": 100, "title": "...", , "body": "..." } ]
-- [ { "userId": 1, "id": 1, "title": "...", , "body": "..." }, ... { "userId": 1, "id": 100, "title": "...", , "body": "..." } ]
-- [ { "userId": 1, "id": 1, "title": "...", , "body": "..." }, ... { "userId": 1, "id": 100, "title": "...", , "body": "..." } ]

GET_HTTP_REQUEST_QUOTA_INFO

GET_HTTP_REQUEST_QUOTA_INFO([api_base_url, api_access_token])

Description

Returns statistics about the HTTP Request quota. HTTP Request quota is an daily quota that defines how many HTTP requests you can compute. Each valid row or computed request counts as one HTTP Request quota unit. The single element in the result of GET_HTTP_REQUEST_QUOTA_INFO will show your HTTP Request quota for the current daily period (availableQuota), how much you’ve spent (usedQuota).

• api_base_url (optional): VARCHAR(MAX) url of the API where the customer account is stored.

• api_access_token (optional): VARCHAR(MAX) an API Access Token that is allowed to use the HTTP Request API.

Return type

VARCHAR(MAX)

Examples

SELECT carto.GET_HTTP_REQUEST_QUOTA_INFO();
--   {
--     "usedQuota": 7,
--     "availableQuota": 25
--   }

SELECT carto.GET_HTTP_REQUEST_QUOTA_INFO('my_api_base_url', 'my_api_access_token');
--   {
--     "usedQuota": 7,
--     "availableQuota": 25
--   }