http_request

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(api_base_url, api_access_token, url, options)

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.

• api_base_url: STRING|NULL url of the API where the customer account is stored. If default credentials have been configured with SETUP NULL can be passed to use them.

• api_access_token: STRING|NULL an API Access Token that is allowed to use the HTTP Request API. If default credentials have been configured with SETUP NULL can be passed to use them.

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

• options: STRING 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. Return type

STRING

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.

Examples

carto-un

SELECT `carto-un`.carto.HTTP_REQUEST(
    NULL, NULL,
    '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"
-- }

carto-un-eu

manual

carto-un

SELECT `carto-un`.carto.HTTP_REQUEST(
    'my_api_base_url', 'my_api_access_token',
    '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"
-- }

carto-un-eu

manual

carto-un

SELECT `carto-un`.carto.HTTP_REQUEST(
    'my_api_base_url', 'my_api_access_token',
    'https://jsonplaceholder.typicode.com/posts/1',
    r'''{
        "method": "POST",
        "headers":{
            "Content-Type":"application/json"
        },
        "mode": "cors",
        "cache": "default",
        "body": "{\"text\":\"hello from bigquery\"}"
    }''');
-- {
--   "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"
-- }

carto-un-eu

manual

carto-un

SELECT `carto-un`.carto.HTTP_REQUEST(
    'my_api_base_url', 'my_api_access_token',
    'https://jsonplaceholder.typicode.com/posts',
    FORMAT('''{
                "method": "POST",
                "headers":{
                    "Content-Type":"application/json"
                },
                "mode": "cors",
                "cache": "default",
                "body": %s,
            }''',
            COALESCE(body, '""')
        )
    )
FROM `my-project.my-dataset.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": "..." } ]

carto-un-eu

manual

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: STRING|NULL url of the API where the customer account is stored. If default credentials have been configured with SETUP NULL can be passed to use them.

• api_access_token: STRING|NULL an API Access Token that is allowed to use the HTTP Request API. If default credentials have been configured with SETUP NULL can be passed to use them.

Return type

STRING

Example

carto-un

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

carto-un-eu

manual

This project has received funding from the European Union’s Horizon 2020 research and innovation programme under grant agreement No 960401.