DuckDB HTTP Client Extension

This very experimental extension spawns an HTTP Client from within DuckDB resolving GET/POST requests.

Experimental: USE AT YOUR OWN RISK!

📦 Installation

INSTALL http_client FROM community;
LOAD http_client;

Functions

• http_get(url)
• http_post(url, headers, params)
  • Sends POST request with params encoded as a JSON object
• http_post_form(url, headers, params)
  • Sends POST request with params being application/x-www-form-urlencoded encoded (used by many forms and some APIs)

Examples

GET

D WITH __input AS (
    SELECT
      http_get(
          'https://httpbin.org/delay/0'
      ) AS res
  ),
  __response AS (
    SELECT
      (res->>'status')::INT AS status,
      (res->>'reason') AS reason,
      unnest( from_json(((res->>'body')::JSON)->'headers', '{"Host": "VARCHAR"}') ) AS features
    FROM
      __input
  )
  SELECT
    __response.status,
    __response.reason,
    __response.Host AS host,
  FROM
    __response
  ;
┌────────┬─────────┬─────────────┐
│ status │ reason  │    host     │
│ int32  │ varchar │   varchar   │
├────────┼─────────┼─────────────┤
│    200 │ OK      │ httpbin.org │
└────────┴─────────┴─────────────┘

POST

D WITH __input AS (
    SELECT
      http_post(
          'https://httpbin.org/delay/0',
          headers => MAP {
            'accept': 'application/json',
          },
          params => MAP {
          }
      ) AS res
  ),
  __response AS (
    SELECT
      (res->>'status')::INT AS status,
      (res->>'reason') AS reason,
      unnest( from_json(((res->>'body')::JSON)->'headers', '{"Host": "VARCHAR"}') ) AS features
    FROM
      __input
  )
  SELECT
    __response.status,
    __response.reason,
    __response.Host AS host,
  FROM
    __response
  ;
┌────────┬─────────┬─────────────┐
│ status │ reason  │    host     │
│ int32  │ varchar │   varchar   │
├────────┼─────────┼─────────────┤
│    200 │ OK      │ httpbin.org │
└────────┴─────────┴─────────────┘

POST using form encoding(application/x-www-form-urlencoded, not multipart/form-data)

D WITH __input AS (
  SELECT
    http_post_form(
        'https://httpbin.org/delay/0',
        headers => MAP {
          'accept': 'application/json',
        },
        params => MAP {
          'limit': 10
        }
    ) AS res
),
__response AS (
  SELECT
    (res->>'status')::INT AS status,
    (res->>'reason') AS reason,
    unnest( from_json(((res->>'body')::JSON)->'form', '{"limit": "VARCHAR"}') ) AS features
  FROM
    __input
)
SELECT
  __response.status,
  __response.reason,
  __response.limit AS limit
FROM
  __response
;
┌────────┬─────────┬─────────┐
│ status │ reason  │  limit  │
│ int32  │ varchar │ varchar │
├────────┼─────────┼─────────┤
│    200 │ OK      │ 10      │
└────────┴─────────┴─────────┘

Full Example w/ spatial data

This is the original example by @ahuarte47 inspiring this community extension.

D SET autoinstall_known_extensions=1; SET autoload_known_extensions=1;
D LOAD json; LOAD httpfs; LOAD spatial;

D WITH __input AS (
    SELECT
      http_get(
        'https://earth-search.aws.element84.com/v0/search')
      AS res
  ),
  __features AS (
    SELECT
      unnest( from_json(((res->>'body')::JSON)->'features', '["json"]') )
      AS features
    FROM
      __input
  )
  SELECT
    features->>'id' AS id,
    features->'properties'->>'sentinel:product_id' AS product_id,
    concat(
      'T',
      features->'properties'->>'sentinel:utm_zone',
      features->'properties'->>'sentinel:latitude_band',
      features->'properties'->>'sentinel:grid_square'
    ) AS grid_id,
    ST_GeomFromGeoJSON(features->'geometry') AS geom
  FROM
    __features
  ;
┌──────────────────────┬──────────────────────┬─────────┬──────────────────────────────────────────────────────────────────────────────────┐
│          id          │      product_id      │ grid_id │                                       geom                                       │
│       varchar        │       varchar        │ varchar │                                     geometry                                     │
├──────────────────────┼──────────────────────┼─────────┼──────────────────────────────────────────────────────────────────────────────────┤
│ S2B_55GDP_20241003…  │ S2B_MSIL2A_2024100…  │ T55GDP  │ POLYGON ((146.7963024570636 -42.53859799130381, 145.7818492341335 -42.53284395…  │
│ S2B_55HEC_20241003…  │ S2B_MSIL2A_2024100…  │ T55HEC  │ POLYGON ((146.9997932100229 -34.429312828654396, 146.9997955899612 -33.4390429…  │
│ S2B_55JHN_20241003…  │ S2B_MSIL2A_2024100…  │ T55JHN  │ POLYGON ((149.9810192714723 -25.374826158099584, 149.9573295859729 -24.3845516…  │
│ S2B_15MWT_20230506…  │ S2B_MSIL2A_2023050…  │ T15MWT  │ POLYGON ((-92.01266261624052 -2.357695714729873, -92.0560908879947 -2.35076658…  │
│ S2B_16PBT_20230506…  │ S2B_MSIL2A_2023050…  │ T16PBT  │ POLYGON ((-88.74518736203468 11.690012668805194, -88.9516536515512 11.72635252…  │
│ S2B_16PCT_20230506…  │ S2B_MSIL2A_2023050…  │ T16PCT  │ POLYGON ((-87.82703591176752 11.483638069337541, -88.8349824533826 11.70734355…  │
│ S2B_15PZP_20230506…  │ S2B_MSIL2A_2023050…  │ T15PZP  │ POLYGON ((-89.24113885498912 11.784951995968179, -89.38831685490888 11.8080246…  │
│ S2B_16PET_20230506…  │ S2B_MSIL2A_2023050…  │ T16PET  │ POLYGON ((-87.00017408768262 11.277451946475995, -87.00017438483464 11.7600349…  │
│ S2B_16PBU_20230506…  │ S2B_MSIL2A_2023050…  │ T16PBU  │ POLYGON ((-88.74518962519173 11.690373971442378, -89.62017907866615 11.8466519…  │
│ S2B_16PDU_20230506…  │ S2B_MSIL2A_2023050…  │ T16PDU  │ POLYGON ((-87.91783982214183 11.670141095427311, -87.92096676562824 12.5828090…  │
├──────────────────────┴──────────────────────┴─────────┴──────────────────────────────────────────────────────────────────────────────────┤
│ 10 rows                                                                                                                        4 columns │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘