Downloads 576this week

GitHub stars 27

Extension repository on GitHub

Extension descriptor (YAML)

ERPL is a DuckDB extension to connect to API based ecosystems via standard interfaces like OData, GraphQL and REST. This works e.g. for SAP ERP, SAP Ariba, SAP C4C, Microsoft Dynamics and many more.

Installing and Loading

INSTALL erpl_web FROM community;
LOAD erpl_web;

Added Functions

| function_name | function_type | description | comment | examples | |——————————-|—————|————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————|———|——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————–| | bc_describe | table | Describe the schema (property names, types, keys) of a Business Central entity. | NULL | [SELECT * FROM bc_describe('customers')] | | bc_read | table | Read data from a Business Central entity with filter and predicate pushdown support. | NULL | [SELECT * FROM bc_read('customers')] | | bc_show_companies | table | List all companies accessible in Microsoft Dynamics 365 Business Central. | NULL | [SELECT * FROM bc_show_companies()] | | bc_show_entities | table | List all OData entity sets available in Microsoft Dynamics 365 Business Central. | NULL | [SELECT * FROM bc_show_entities()] | | crm_describe | table | Describe the attribute schema (names, types, keys) of a Microsoft Dataverse entity. | NULL | [SELECT * FROM crm_describe('accounts')] | | crm_read | table | Read data from a Microsoft Dataverse entity with filter and predicate pushdown support. | NULL | [SELECT * FROM crm_read('accounts')] | | crm_show_entities | table | List all entity types available in Microsoft Dataverse (Dynamics 365 CRM). | NULL | [SELECT * FROM crm_show_entities()] | | datasphere_describe_asset | table | Describe the schema and metadata of a specific asset in a SAP Datasphere space. | NULL | [SELECT * FROM datasphere_describe_asset('MY_SPACE', 'MY_VIEW')] | | datasphere_describe_space | table | Describe the metadata and configuration of a SAP Datasphere space. | NULL | [SELECT * FROM datasphere_describe_space('MY_SPACE')] | | datasphere_read_analytical | table | Read data from an analytical (cube/view) asset in SAP Datasphere using space and asset ID. | NULL | [SELECT * FROM datasphere_read_analytical('MY_SPACE', 'MY_CUBE')] | | datasphere_read_analytical | table | Read data from an analytical asset in SAP Datasphere using space, asset ID, and explicit secret name. | NULL | [SELECT * FROM datasphere_read_analytical('MY_SPACE', 'MY_CUBE', 'my_secret')] | | datasphere_read_relational | table | Read data from a relational asset in SAP Datasphere using space and asset ID. | NULL | [SELECT * FROM datasphere_read_relational('MY_SPACE', 'MY_VIEW')] | | datasphere_read_relational | table | Read data from a relational asset in SAP Datasphere using space, asset ID, and explicit secret name. | NULL | [SELECT * FROM datasphere_read_relational('MY_SPACE', 'MY_VIEW', 'my_secret')] | | datasphere_show_assets | table | List all assets across all accessible SAP Datasphere spaces. | NULL | [SELECT * FROM datasphere_show_assets()] | | datasphere_show_assets | table | List all assets in a specific SAP Datasphere space. | NULL | [SELECT * FROM datasphere_show_assets('MY_SPACE')] | | datasphere_show_spaces | table | List all SAP Datasphere spaces accessible with the configured credentials. | NULL | [SELECT * FROM datasphere_show_spaces()] | | delta_share_scan | table | Read data from a Delta Sharing table using a profile file and share/schema/table path. | NULL | [SELECT * FROM delta_share_scan('/path/to/profile.json', 'my_share', 'my_schema', 'my_table')] | | delta_share_show_schemas | table | List all schemas within a Delta Sharing share. | NULL | [SELECT * FROM delta_share_show_schemas('/path/to/profile.json', 'my_share')] | | delta_share_show_shares | table | List all shares available in a Delta Sharing server using a profile file. | NULL | [SELECT * FROM delta_share_show_shares('/path/to/profile.json')] | | delta_share_show_tables | table | List all tables within a Delta Sharing schema. | NULL | [SELECT * FROM delta_share_show_tables('/path/to/profile.json', 'my_share', 'my_schema')] | | erpl_trace_directory | pragma | NULL | NULL | | | erpl_trace_enable | pragma | NULL | NULL | | | erpl_trace_level | pragma | NULL | NULL | | | erpl_trace_status | pragma | NULL | NULL | | | graph_calendar_events | table | List Outlook calendar events for a user via Microsoft Graph. Returns id, subject, body_preview, start_time, end_time, location, organizer_name, organizer_email, is_all_day, is_cancelled, web_link. Use start_date + end_date (ISO 8601 date or datetime) for a date-bounded calendarView query; omit both for all upcoming events. Use calendar_id to target a specific calendar instead of the default. | NULL | [SELECT * FROM graph_calendar_events(secret := 'ms_graph'), SELECT * FROM graph_calendar_events(start_date := '2024-01-01', end_date := '2024-12-31', secret := 'ms_graph'), SELECT * FROM graph_calendar_events(user := '[email protected]', calendar_id := 'calendar-guid', secret := 'ms_graph')] | | graph_calendars | table | List Outlook calendars for a user via Microsoft Graph. Returns id, name, color, is_default_calendar, can_edit. Omit user to query the authenticated user's calendars (delegated); provide user (GUID, UPN, or email) for app-only auth. | NULL | [SELECT * FROM graph_calendars(secret := 'ms_graph'), SELECT * FROM graph_calendars(user := '[email protected]', secret := 'ms_graph')] | | graph_channel_messages | table | List messages in a Microsoft Teams channel. Pages lazily through large message histories via @odata.nextLink. Both arguments accept a GUID/channel-ID or a human-readable displayName. | NULL | [SELECT * FROM graph_channel_messages('Engineering', 'General', secret := 'ms_graph'), SELECT * FROM graph_channel_messages('team-guid', '19:[email protected]', secret := 'ms_graph')] | | graph_contacts | table | List Outlook contacts for a user via Microsoft Graph. Returns id, display_name, email, phone, company, job_title. Omit user to query the authenticated user's contacts (delegated); provide user (GUID, UPN, or email) for app-only auth. | NULL | [SELECT * FROM graph_contacts(secret := 'ms_graph'), SELECT display_name, email FROM graph_contacts(user := '[email protected]', secret := 'ms_graph') WHERE company = 'Acme'] | | graph_describe_list | table | Describe the column schema of a SharePoint list. Returns column_name, column_type, is_required, is_hidden. Both arguments accept a GUID, display name, or site URL. Use this to discover field names before calling graph_sharepoint_create_item() or graph_sharepoint_update_item(). | NULL | [SELECT * FROM graph_describe_list('Finance', 'Budget', secret := 'ms_graph'), SELECT * FROM graph_describe_list('https://tenant.sharepoint.com/sites/Finance', 'Budget', secret := 'ms_graph')] | | graph_devices | table | List registered devices from Microsoft Entra ID (Azure Active Directory). | NULL | [SELECT * FROM graph_devices(secret := 'ms_entra')] | | graph_excel_add_rows | table | Append rows to a named table in a Microsoft Excel workbook. Returns rows_added. data is a JSON 2-D array where each inner array is one row in column order: '[[v1,v2,…],[v1,v2,…],…]'. Column order must match the table's column order (use graph_excel_tables() to verify). | NULL | [SELECT * FROM graph_excel_add_rows('report.xlsx', 'Sales', data := '[[1,"Alice",100.0],[2,"Bob",200.0]]', drive := 'https://tenant.sharepoint.com/Shared%20Documents', secret := 'ms_graph'), SELECT * FROM graph_excel_add_rows('report.xlsx', 'Sales', data := '[[1,"Alice"]]', site := 'Finance', drive := 'Documents', secret := 'ms_graph')] | | graph_excel_delete_rows | table | Delete all rows in an Excel table where a column value matches. Returns rows_deleted. col_index is 0-based (0 = first column). col_value is always compared as a string; cast numeric IDs to VARCHAR if needed. | NULL | [SELECT * FROM graph_excel_delete_rows('report.xlsx', 'Sales', 0, 'obsolete_row', drive := 'b!abc…', secret := 'ms_graph'), SELECT * FROM graph_excel_delete_rows('report.xlsx', 'Sales', 2, '42', site := 'Finance', drive := 'Documents', secret := 'ms_graph')] | | graph_excel_range | table | Read a cell range from a worksheet in a Microsoft Excel workbook. Returns dynamic columns named by the first header row. sheet_name must match exactly (use graph_excel_worksheets() to discover names). Optionally pass a range address as a third positional argument (e.g. 'A1:D100'); omit it to read the sheet's used range. | NULL | [SELECT * FROM graph_excel_range('test.xlsx', 'Sheet1', drive := 'b!abc…', secret := 'ms_graph'), SELECT * FROM graph_excel_range('test.xlsx', 'Sheet1', drive := 'https://tenant.sharepoint.com/Shared%20Documents', secret := 'ms_graph'), SELECT * FROM graph_excel_range('test.xlsx', 'Sheet1', site := 'Finance', drive := 'Documents', secret := 'ms_graph')] | | graph_excel_read | table | Read all rows from a named table in a Microsoft Excel workbook. Returns dynamic columns matching the table's header row. table_name must match a name returned by graph_excel_tables(). Use graph_excel_tables() to discover available table names first. | NULL | [SELECT * FROM graph_excel_read('test.xlsx', 'SalesTable', drive := 'b!abc…', secret := 'ms_graph'), SELECT * FROM graph_excel_read('test.xlsx', 'SalesTable', drive := 'https://tenant.sharepoint.com/Shared%20Documents', secret := 'ms_graph'), SELECT * FROM graph_excel_read('test.xlsx', 'SalesTable', site := 'Finance', drive := 'Documents', secret := 'ms_graph')] | | graph_excel_tables | table | List all named tables in a Microsoft Excel workbook. Returns table_name, sheet_name, row_count, column_count. Use the returned table_name with graph_excel_read() to read table data. | NULL | [SELECT * FROM graph_excel_tables('test.xlsx', drive := 'b!abc…', secret := 'ms_graph'), SELECT * FROM graph_excel_tables('test.xlsx', drive := 'https://tenant.sharepoint.com/Shared%20Documents', secret := 'ms_graph'), SELECT * FROM graph_excel_tables('test.xlsx', site := 'Finance', drive := 'Documents', secret := 'ms_graph')] | | graph_excel_worksheets | table | List all worksheets in a Microsoft Excel workbook. Returns sheet_name, position, visibility. Use the returned sheet_name with graph_excel_range() to read a cell range. | NULL | [SELECT * FROM graph_excel_worksheets('test.xlsx', drive := 'b!abc…', secret := 'ms_graph'), SELECT * FROM graph_excel_worksheets('test.xlsx', drive := 'https://tenant.sharepoint.com/Shared%20Documents', secret := 'ms_graph'), SELECT * FROM graph_excel_worksheets('test.xlsx', site := 'Finance', drive := 'Documents', secret := 'ms_graph')] | | graph_groups | table | List groups from Microsoft Entra ID (Azure Active Directory). | NULL | [SELECT * FROM graph_groups(secret := 'ms_entra')] | | graph_my_teams | table | List Microsoft Teams for a user. Omit user to query the authenticated user's teams (delegated); provide user (GUID, UPN, or email) for app-only auth. | NULL | [SELECT * FROM graph_my_teams(secret := 'ms_graph'), SELECT * FROM graph_my_teams(user := '[email protected]', secret := 'ms_graph')] | | graph_outlook_emails | table | List Outlook email messages for a user via Microsoft Graph. Returns id, subject, sender_name, sender_email, received_datetime, body_preview, is_read, has_attachments, web_link. Use folder to filter by a well-known name ('inbox', 'sentitems', 'drafts', 'deleteditems') or any displayName from graph_outlook_mail_folders(). Pages lazily through large mailboxes. | NULL | [SELECT * FROM graph_outlook_emails(secret := 'ms_graph'), SELECT subject, sender_name, received_datetime FROM graph_outlook_emails(folder := 'inbox', secret := 'ms_graph') WHERE NOT is_read, SELECT * FROM graph_outlook_emails(user := '[email protected]', folder := 'sentitems', secret := 'ms_graph')] | | graph_outlook_mail_folders | table | List Outlook mail folders for a user via Microsoft Graph. Returns id, display_name, total_item_count, unread_item_count. Use the returned display_name or id as the folder parameter of graph_outlook_emails() to filter messages by folder. | NULL | [SELECT * FROM graph_outlook_mail_folders(secret := 'ms_graph'), SELECT display_name, unread_item_count FROM graph_outlook_mail_folders(user := '[email protected]', secret := 'ms_graph') ORDER BY unread_item_count DESC] | | graph_planner_buckets | table | List all buckets in a Microsoft Planner plan. | NULL | [SELECT * FROM graph_planner_buckets('plan-id-here', secret := 'ms_graph')] | | graph_planner_create_task | table | Create a Microsoft Planner task. Returns (task_id, task_url). Designed for use with lateral joins to bulk-create tasks from a query result. priority: 0-10 (1=urgent, 3=important, 5=medium, 9=low). assigned_to: single Graph user ID. due_date/start_date: ISO 8601 date or datetime string. | NULL | [– Create a single task SELECT * FROM graph_planner_create_task('plan-id', 'My Task', bucket_id := 'bucket-id', due_date := '2024-06-30', assigned_to := 'user-guid', description := 'Details here', priority := 5, secret := 'ms_graph'), – Bulk-create from a table using a lateral join SELECT t.title, pt.task_url FROM todos t, graph_planner_create_task('plan-id', t.title, due_date := t.due_date::VARCHAR, description := t.notes, secret := 'ms_graph') pt] | | graph_planner_plans | table | List all Microsoft Planner plans in a Microsoft 365 group. | NULL | [SELECT * FROM graph_planner_plans('group-id-here', secret := 'ms_graph')] | | graph_planner_tasks | table | List all tasks in a Microsoft Planner plan. | NULL | [SELECT * FROM graph_planner_tasks('plan-id-here', secret := 'ms_graph')] | | graph_sharepoint_create_item | table | Create a new item in a SharePoint list. Returns item_id (the newly created item's integer ID). fields_json is a JSON object of SharePoint internal field name/value pairs (use graph_describe_list() to find field names). Both site and list accept a GUID, display name, or URL. | NULL | [SELECT * FROM graph_sharepoint_create_item('Finance', 'Budget', '{"Title":"Q1","Status":"Active"}', secret := 'ms_graph'), SELECT * FROM graph_sharepoint_create_item('https://tenant.sharepoint.com/sites/Finance', 'Budget', '{"Title":"Q2"}', secret := 'ms_graph')] | | graph_sharepoint_delete_item | scalar | Delete an item from a SharePoint list by its item ID. Returns true on success. item_id is the SharePoint integer ID (from the item_id column of graph_sharepoint_list_read()). secret is positional (last argument), not a named parameter. | NULL | [SELECT graph_sharepoint_delete_item('Finance', 'Budget', '42', 'ms_graph'), SELECT graph_sharepoint_delete_item('Finance', 'Budget', item_id::VARCHAR, 'ms_graph') FROM graph_sharepoint_list_read('Finance', 'Budget', secret := 'ms_graph') WHERE Title = 'OldEntry'] | | graph_sharepoint_list_read | table | Read all items from a SharePoint list. Returns dynamic columns matching the list schema (use graph_describe_list() to inspect them). Also returns item_id (SharePoint internal integer ID) on every row. Both arguments accept a GUID, display name, or site URL. | NULL | [SELECT * FROM graph_sharepoint_list_read('Finance', 'Budget', secret := 'ms_graph'), SELECT * FROM graph_sharepoint_list_read('https://tenant.sharepoint.com/sites/Finance', 'Budget', secret := 'ms_graph')] | | graph_sharepoint_update_item | scalar | Update fields of an existing SharePoint list item. Returns true on success. item_id is the SharePoint integer ID (from the item_id column of graph_sharepoint_list_read()). fields_json is a JSON object of SharePoint internal field name/value pairs. secret is positional (last argument), not a named parameter. | NULL | [SELECT graph_sharepoint_update_item('Finance', 'Budget', '42', '{"Status":"Approved"}', 'ms_graph'), UPDATE items SET ok = graph_sharepoint_update_item('Finance', 'Budget', item_id::VARCHAR, json_object('Status', new_status), 'ms_graph') FROM items] | | graph_show_drives | table | List document library drives in a SharePoint site. Returns id, name, drive_type, web_url. Pass a composite site_id (guid,guid,guid), a site display name, or a site URL as the first positional argument, or use the site named parameter instead. | NULL | [SELECT * FROM graph_show_drives(site := 'Finance', secret := 'ms_graph'), SELECT * FROM graph_show_drives('https://tenant.sharepoint.com/sites/Finance', secret := 'ms_graph')] | | graph_show_files | table | List files and folders in OneDrive or a SharePoint document library. Returns id, name, web_url, size, created_at, modified_at, mime_type, is_folder. Locate the drive with graph_show_drives() first. Pass drive as a drive ID (b!…), a web URL, or a display name combined with site. | NULL | [SELECT * FROM graph_show_files(drive := 'b!abc…', secret := 'ms_graph'), SELECT * FROM graph_show_files(drive := 'https://tenant.sharepoint.com/Shared%20Documents', secret := 'ms_graph'), SELECT * FROM graph_show_files(site := 'Finance', drive := 'Documents', secret := 'ms_graph')] | | graph_show_lists | table | List all lists in a SharePoint site. Returns id, name, display_name, list_template, web_url. Pass a composite site_id (guid,guid,guid), a site display name, or a site URL as the first positional argument, or use the site named parameter instead. Use the returned id or display_name as the list argument of graph_sharepoint_list_read(), graph_describe_list(), etc. | NULL | [SELECT * FROM graph_show_lists(site := 'Finance', secret := 'ms_graph'), SELECT * FROM graph_show_lists('https://tenant.sharepoint.com/sites/Finance', secret := 'ms_graph')] | | graph_show_sites | table | Search and list SharePoint sites accessible via Microsoft Graph. Returns id, name, display_name, web_url, created_at, last_modified_at. Pass an optional search term as the first positional argument to filter by name; omit it to list all accessible sites. | NULL | [SELECT * FROM graph_show_sites(secret := 'ms_graph'), SELECT * FROM graph_show_sites('Finance', secret := 'ms_graph')] | | graph_signin_logs | table | List sign-in logs from Microsoft Entra ID (requires Azure AD Premium P1 or P2). | NULL | [SELECT * FROM graph_signin_logs(secret := 'ms_entra')] | | graph_team_members | table | List all members of a Microsoft Teams team. The first argument accepts a team GUID or a displayName (resolved via /me/joinedTeams). | NULL | [SELECT * FROM graph_team_members('Engineering', secret := 'ms_graph'), SELECT * FROM graph_team_members('xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx', secret := 'ms_graph')] | | graph_teams_channels | table | List all channels in a Microsoft Teams team. The first argument accepts a team GUID or a displayName (resolved via /me/joinedTeams). | NULL | [SELECT * FROM graph_teams_channels('Engineering', secret := 'ms_graph'), SELECT * FROM graph_teams_channels('xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx', secret := 'ms_graph')] | | graph_users | table | List users from Microsoft Entra ID (Azure Active Directory). | NULL | [SELECT * FROM graph_users(secret := 'ms_entra')] | | http_delete | table | Send an HTTP DELETE request with a JSON body and return the response as a table. | NULL | [SELECT * FROM http_delete('https://httpbin.org/delete', '{}'::JSON)] | | http_delete | table | Send an HTTP DELETE request with raw content and return the response as a table. | NULL | [SELECT * FROM http_delete('https://example.com/resource', '', 'text/plain')] | | http_get | table | Send an HTTP GET request and return the response as a table. | NULL | [SELECT * FROM http_get('https://httpbin.org/ip')] | | http_head | table | Send an HTTP HEAD request and return the response headers as a table. | NULL | [SELECT * FROM http_head('https://httpbin.org/ip')] | | http_patch | table | Send an HTTP PATCH request with a JSON body and return the response as a table. | NULL | [SELECT * FROM http_patch('https://httpbin.org/patch', '{"key": "value"}'::JSON)] | | http_patch | table | Send an HTTP PATCH request with raw content and return the response as a table. | NULL | [SELECT * FROM http_patch('https://example.com/resource', 'patch', 'text/plain')] | | http_post | table | Send an HTTP POST request with a JSON body and return the response as a table. | NULL | [SELECT * FROM http_post('https://httpbin.org/post', '{"key": "value"}'::JSON)] | | http_post | table | Send an HTTP POST request with raw content and return the response as a table. | NULL | [SELECT * FROM http_post('https://httpbin.org/post', 'hello', 'text/plain')] | | http_put | table | Send an HTTP PUT request with a JSON body and return the response as a table. | NULL | [SELECT * FROM http_put('https://httpbin.org/put', '{"key": "value"}'::JSON)] | | http_put | table | Send an HTTP PUT request with raw content and return the response as a table. | NULL | [SELECT * FROM http_put('https://example.com/resource', 'data', 'text/plain')] | | odata_attach | table | Attach all entity sets of an OData service as views in the current DuckDB catalog. | NULL | [SELECT * FROM odata_attach('https://services.odata.org/V4/Northwind/Northwind.svc/')] | | odata_describe | table | Describe the schema of an OData entity set by reading its metadata document. | NULL | [SELECT * FROM odata_describe('https://services.odata.org/V4/Northwind/Northwind.svc/Customers')] | | odata_read | table | Read data from an OData v2/v4 entity set with automatic version detection and predicate pushdown. | NULL | [SELECT * FROM odata_read('https://services.odata.org/V4/Northwind/Northwind.svc/Customers')] | | odata_sap_show | table | List available SAP OData entity sets and services at a given service root URL. | NULL | [SELECT * FROM odata_sap_show('https:///sap/opu/odata/sap/')] | | odp_odata_list_subscriptions | table | List all active ODP subscriptions with their status and current delta tokens. | NULL | [SELECT * FROM odp_odata_list_subscriptions()] | | odp_odata_read | table | Read data from a SAP ODP extractor via OData with delta token support for incremental/CDC loads. | NULL | [SELECT * FROM odp_odata_read('https:///sap/opu/odata/sap/ZEXTRACTOR_SRV/DataSet')] | | odp_odata_remove_subscription | pragma | NULL | NULL | | | odp_odata_show | table | List available SAP ODP (Operational Data Provisioning) contexts and extractors at a given OData service root URL. | NULL | [SELECT * FROM odp_odata_show('https:///sap/opu/odata/iwbep/GWSAMPLE_BASIC/')] | | sac_describe_model | table | Get detailed metadata and dimension/measure schema for a SAP Analytics Cloud model. | NULL | [SELECT * FROM sac_describe_model('t.2:MY_MODEL')] | | sac_describe_story | table | Get detailed metadata for a SAP Analytics Cloud story. | NULL | [SELECT * FROM sac_describe_story('ABC12345')] | | sac_read_analytical | table | Read analytical data from a SAP Analytics Cloud model with optional dimension and measure filtering. | NULL | [SELECT * FROM sac_read_analytical('t.2:MY_ANALYTICAL_MODEL')] | | sac_read_planning_data | table | Read planning data from a SAP Analytics Cloud planning model. | NULL | [SELECT * FROM sac_read_planning_data('t.2:MY_PLANNING_MODEL')] | | sac_read_story_data | table | Extract underlying data from a SAP Analytics Cloud story's visualizations. | NULL | [SELECT * FROM sac_read_story_data('ABC12345')] | | sac_show_models | table | List all planning and analytics models available in SAP Analytics Cloud. | NULL | [SELECT * FROM sac_show_models()] | | sac_show_stories | table | List all stories (dashboards/reports) available in SAP Analytics Cloud. | NULL | [SELECT * FROM sac_show_stories()] |

Overloaded Functions

This extension does not add any function overloads.

Added Types

Added Settings