Dagster Materializations from DuckDB via GraphQL

duckdb-dagster-graphql-materializations.sql

install http_client from community;

load http_client;

with __input as (
  select
    http_post(
      'http://localhost:3000/graphql',
      headers => map {
        'content-type': 'application/json',
      },
      params => {
        'query': '
query AssetEventsQuery(
  $assetKey: AssetKeyInput!
  $limit: Int
  $before: String
  $partitionInLast: Int
) {
  assetOrError(assetKey: $assetKey) {
    ... on Asset {
      id
      key {
        path
      }
      assetMaterializations(
        limit: $limit
        beforeTimestampMillis: $before
        partitionInLast: $partitionInLast
      ) {
        ...AssetMaterializationFragment
      }
    }
  }
}
fragment AssetMaterializationFragment on MaterializationEvent {
  runId
  timestamp
  stepKey
}
',
        'variables': {
          'assetKey': {
            'path': 'example_asset_1'
          }
        }
      }
    ) as res
),
__extracted as (
  select
    (res->>'status')::int as status,
    (res->>'reason') AS reason,
    unnest(from_json((res->>'body')::JSON->>'data'->>'assetOrError'->>'assetMaterializations', '["JSON"]'))  as materializations
  from __input
)
select
  status,
  reason,
  materializations->>'runId' as run_id,
  materializations->>'timestamp' as timestamp,
  materializations->>'stepKey' as step_key,
from __extracted
;

-- ┌────────┬─────────┬──────────────────────────────────────┬───────────────┬─────────────────┐
-- │ status │ reason  │                run_id                │   timestamp   │    step_key     │
-- │ int32  │ varchar │               varchar                │    varchar    │     varchar     │
-- ├────────┼─────────┼──────────────────────────────────────┼───────────────┼─────────────────┤
-- │    200 │ OK      │ 8926ea9e-e200-4ba6-9443-a92063d2c3bb │ 1730072967554 │ example_asset_1 │
-- │    200 │ OK      │ 81db20d0-fed1-40e2-a70b-58d6ff67da53 │ 1730072907537 │ example_asset_1 │
-- │    200 │ OK      │ 9b732191-8321-4a5e-ac2a-19c9b8bc7e88 │ 1730072848784 │ example_asset_1 │
-- │    200 │ OK      │ faa98fb8-50bd-444c-80b1-e146d6fe1c19 │ 1730072787470 │ example_asset_1 │
-- │    200 │ OK      │ 9f6f1c3b-cf23-480c-ae86-14cb88d390e9 │ 1730072727340 │ example_asset_1 │
-- │    200 │ OK      │ 467cd8c0-4b45-4cb0-a8a7-42d9767c66de │ 1730072667211 │ example_asset_1 │
-- │    200 │ OK      │ 3d6c4e30-ac14-45be-ac54-cc1e4bf1a74b │ 1730072607304 │ example_asset_1 │
-- │    200 │ OK      │ 171b04bd-5348-46d1-96ed-475bc4d6c2b3 │ 1730072547093 │ example_asset_1 │
-- │    200 │ OK      │ 82241d54-dfc0-4506-a0f8-1a90ab2517b7 │ 1730072486873 │ example_asset_1 │
-- │    200 │ OK      │ c56790c4-3a4e-49f9-b370-ea6d246a5339 │ 1730072426894 │ example_asset_1 │
-- │    200 │ OK      │ 87a4bf20-b16f-45b6-b7b9-10c47c189ca1 │ 1730072366904 │ example_asset_1 │
-- │    200 │ OK      │ 50c93ab0-7d68-4630-9aaf-7f9e3ece0b0d │ 1730072306635 │ example_asset_1 │
-- │    200 │ OK      │ 8c95e8f5-c114-40a3-89b3-1cec0c243158 │ 1730072246413 │ example_asset_1 │
-- │    200 │ OK      │ a851dafc-ae8a-4abf-9715-3cba3bfab987 │ 1730072186490 │ example_asset_1 │
-- │    200 │ OK      │ a7858c4a-7ff2-42d0-b092-9d9ccc76da41 │ 1730072126260 │ example_asset_1 │
-- │    200 │ OK      │ 0837080f-207e-478b-ada8-ccdf045b8401 │ 1730072066219 │ example_asset_1 │
-- │    200 │ OK      │ 76b3dcdd-a620-4ccf-b652-b68d38a9c611 │ 1730072006053 │ example_asset_1 │
-- │    200 │ OK      │ 012cad6a-34ef-4e96-ac3d-a2036152e126 │ 1730071946011 │ example_asset_1 │
-- │    200 │ OK      │ d04620af-1786-4b6e-96da-be1e1b4bc751 │ 1730071885881 │ example_asset_1 │
-- │    200 │ OK      │ 831fbc13-3231-477d-8818-cab6b315100e │ 1730071825842 │ example_asset_1 │
-- │     ·  │ ·       │                  ·                   │       ·       │        ·        │
-- │     ·  │ ·       │                  ·                   │       ·       │        ·        │
-- │     ·  │ ·       │                  ·                   │       ·       │        ·        │
-- │    200 │ OK      │ 15a54d2c-013f-4369-ae9b-c64664cb3e03 │ 1730071345053 │ example_asset_1 │
-- │    200 │ OK      │ 39473b0a-ac77-49dc-8ac1-88e6d7a0113c │ 1730071284744 │ example_asset_1 │
-- │    200 │ OK      │ 910368f8-6a9e-4540-bf6e-607386e80831 │ 1730071224939 │ example_asset_1 │
-- │    200 │ OK      │ 79e40262-8a43-491a-a59b-903458150528 │ 1730071164519 │ example_asset_1 │
-- │    200 │ OK      │ 1927d381-2ae7-4e5a-aa30-a2c2fe697d18 │ 1730071104515 │ example_asset_1 │
-- │    200 │ OK      │ 2d4f3525-d65f-4b50-859f-2442af076425 │ 1730071044336 │ example_asset_1 │
-- │    200 │ OK      │ 9f3d06b7-4561-4b85-bfff-c5f3c620e0b9 │ 1730070984558 │ example_asset_1 │
-- │    200 │ OK      │ 2fcf025b-3fd8-46a0-b3dd-0b0ff7766104 │ 1730070924235 │ example_asset_1 │
-- │    200 │ OK      │ 9591728f-036a-42a7-aa82-44d00b8dabaf │ 1730070864172 │ example_asset_1 │
-- │    200 │ OK      │ f879b9bd-fbf6-41bb-964b-f43922d077d7 │ 1730070804109 │ example_asset_1 │
-- │    200 │ OK      │ 3a20be55-8ec3-4b64-96f1-60ec2383ab02 │ 1730070743947 │ example_asset_1 │
-- │    200 │ OK      │ d9a03ec6-78b9-4c19-86d0-66b1a02b4768 │ 1730070684073 │ example_asset_1 │
-- │    200 │ OK      │ 8850c2bb-9a1a-4342-a3ad-e18b262cf5a5 │ 1730070623948 │ example_asset_1 │
-- │    200 │ OK      │ 88a5d7fc-03cd-43d1-828a-42b899455348 │ 1730070564031 │ example_asset_1 │
-- │    200 │ OK      │ 2e685df3-355b-4e86-b2ab-4081f7a960f3 │ 1730070503542 │ example_asset_1 │
-- │    200 │ OK      │ faa216ed-78ad-4e3b-bd2d-f9337d070c8b │ 1730070443388 │ example_asset_1 │
-- │    200 │ OK      │ cf5881f9-82f2-4356-81c4-758ff468fa92 │ 1730070383343 │ example_asset_1 │
-- │    200 │ OK      │ 9d900184-212a-4f17-ba4e-a6d1e53f922f │ 1730070375800 │ example_asset_1 │
-- │    200 │ OK      │ 002fee2f-af9d-465c-9fe3-0cda39b10a56 │ 1730070203055 │ example_asset_1 │
-- │    200 │ OK      │ 79b0a297-232b-43ce-bc2d-4db6dcb05f22 │ 1730070173259 │ example_asset_1 │
-- ├────────┴─────────┴──────────────────────────────────────┴───────────────┴─────────────────┤
-- │ 47 rows (40 shown)                                                              5 columns │
-- └───────────────────────────────────────────────────────────────────────────────────────────┘