UK Department for Business and Trade Data Integration

info@ismena.com

Connector Details

Type

Virtual machines, Single VM , BYOL

Runs on

Google Compute Engine

Last Update

24 October, 2024

Category

Overview

The UK Department for Business and Trade (DBT) Data Integration Connector provides seamless integration with the DBT Data API, enabling access to datasets, versions, tables, reports, and metadata. This connector acts as a proxy to streamline data retrieval, supporting multiple action points for listing datasets, versions, tables, reports, and retrieving data and metadata, optimized for deployment on Google Cloud Platform (GCP).

Integration Overview

This document provides a detailed guide for the supported action points of the UK DBT Data Integration Connector, their purposes, configurations, and workflow support when deployed on GCP. The connector leverages GCP services such as Cloud Functions or Cloud Run for API calls and BigQuery for data storage and querying.
Supported Integration Action Points

listDatasets: Retrieves a list of all available datasets.
listVersions: Retrieves a list of versions for a specific dataset.
listTables: Retrieves a list of tables in a specific dataset version.
listReports: Retrieves a list of reports in a specific dataset version.
getMetadata: Retrieves metadata for a specific dataset version.
getVersionData: Retrieves data for a specific dataset version.
getTableData: Retrieves data for a specific table in a dataset version.
getReportData: Retrieves data for a specific report in a dataset version.

Detailed Integration Documentation

List Datasets

Action	listDatasets
Purpose	Retrieves a list of all datasets available in the DBT Data API.
Parameters	Required: format (string, enum: [json], e.g., json) Optional: None
Configuration	Deploy the connector on GCP Cloud Functions or Cloud Run with the base URL set to https://data.api.trade.gov.uk. Ensure GCP IAM roles allow HTTP requests to external APIs.
Output	Successful: Returns a JSON object with result (success status) and datasets (array of dataset records, e.g., {"datasets": [{"id": "uk-tariff-2021-01-01"}]}) Failure: Returns error details (e.g., {"error": "Invalid format parameter"} for 400)
Workflow Example	Deploy the connector on Cloud Functions with the base URL https://data.api.trade.gov.uk. Execute the listDatasets action with format=json. Store the response in BigQuery for further analysis or display dataset IDs in a GCP-hosted dashboard.

List Versions

Action	listVersions
Purpose	Retrieves a list of versions for a specified dataset.
Parameters	Required: dataset_id (string, e.g., "uk-tariff-2021-01-01"), format (string, enum: [json], e.g., json) Optional: None
Configuration	Deploy on GCP Cloud Run. Use GCP Secret Manager to securely manage API configurations.
Output	Successful: Returns a JSON object with result (success status) and versions (array of version records, e.g., {"versions": [{"id": "v2.1.0"}]}) Failure: Returns error details (e.g., {"error": "Dataset not found"} for 404)
Workflow Example	Execute the listVersions action with dataset_id=uk-tariff-2021-01-01 and format=json. Cache the response in GCP Memorystore for quick access. Use the version IDs to fetch further details or populate a version selection interface.

2.3 List Tables

Action	listTables
Purpose	Retrieves a list of tables in a specified dataset version.
Parameters	Required: dataset_id (string, e.g., "uk-tariff-2021-01-01"), version_id (string, e.g., "v2.1.0"), format (string, enum: [json], e.g., json) Optional: None
Configuration	Deploy on GCP Cloud Functions. Configure Cloud Logging for monitoring API call performance.
Output	Successful: Returns a JSON object with result (success status) and tables (array of table records, e.g., {"tables": [{"id": "commodities"}]}) Failure: Returns error details (e.g., {"error": "Version not found"} for 404)
Workflow Example	Execute the listTables action with dataset_id=uk-tariff-2021-01-01, version_id=v2.1.0, and format=json. Store table IDs in BigQuery for downstream processing. Display table IDs in a GCP-hosted application for user selection.

List Reports

Action	listReports
Purpose	Retrieves a list of reports in a specified dataset version.
Parameters	Required: dataset_id (string, e.g., "uk-tariff-2021-01-01"), version_id (string, e.g., "v2.1.0"), format (string, enum: [json], e.g., json) Optional: None
Configuration	Deploy on GCP Cloud Run. Use GCP Monitoring to track API request latency.
Output	Successful: Returns a JSON object with result (success status) and reports (array of report records, e.g., {"reports": [{"id": "quotas-including-current-volumes"}]}) Failure: Returns error details (e.g., {"error": "Version not found"} for 404)
Workflow Example	Execute the listReports action with dataset_id=uk-tariff-2021-01-01, version_id=v2.1.0, and format=json. Store report IDs in BigQuery for analysis. Use report IDs to fetch detailed report data for visualization.

Get Metadata

Action	getMetadata
Purpose	Retrieves metadata for a specified dataset version, including table schemas and dataset details.
Parameters	Required: dataset_id (string, e.g., "uk-tariff-2021-01-01"), version_id (string, e.g., "v2.1.0"), format (string, enum: [csvw, html], e.g., csvw) Optional: download (boolean, e.g., true)
Configuration	Deploy on GCP Cloud Functions. Store metadata in Google Cloud Storage for persistence.
Output	Successful: Returns a JSON object with result (success status) and metadata (object containing dataset details and table schemas, e.g., {"dc:title": "Tariffs to trade with the UK from 1 January 2021", "tables": [{"id": "commodities", "tableSchema": {"columns": [{"name": "id"}]}}]}) Failure: Returns error details (e.g., {"error": "Invalid format"} for 400)
Workflow Example	Execute the getMetadata action with dataset_id=uk-tariff-2021-01-01, version_id=v2.1.0, and format=csvw. Store the metadata in Google Cloud Storage. Use the metadata to define BigQuery table schemas for data ingestion.

Get Version Data

Action	getVersionData
Purpose	Retrieves data for a specified dataset version in the requested format.
Parameters	Required: dataset_id (string, e.g., "uk-tariff-2021-01-01"), version_id (string, e.g., "v2.1.0"), format (string, enum: [sqlite, json, ods], e.g., json) Optional: query-s3-select (string, e.g., "SELECT * FROM commodities WHERE commodity__code='0101210000'"), download (boolean, e.g., true)
Configuration	Deploy on GCP Cloud Run. Use BigQuery for storing and querying retrieved data.
Output	Successful: Returns a JSON object with result (success status) and data (object containing dataset version data, e.g., {"commodities": [{"id": "1", "commodity__code": "0100000000", "commodity__description": "LIVE ANIMALS"}]}) Failure: Returns error details (e.g., {"error": "Invalid query"} for 400)
Workflow Example	Execute the getVersionData action with dataset_id=uk-tariff-2021-01-01, version_id=v2.1.0, and format=json. Load the data into BigQuery for analysis. Use Data Studio to visualize the dataset data.

Get Table Data

Action	getTableData
Purpose	Retrieves data for a specific table in a dataset version.
Parameters	Required: dataset_id (string, e.g., "uk-tariff-2021-01-01"), version_id (string, e.g., "v2.1.0"), table_id (string, e.g., "commodities"), format (string, enum: [csv, ods, parquet], e.g., csv) Optional: query-s3-select (string, e.g., "SELECT * FROM commodities WHERE commodity__code='0101210000'"), query-simple (boolean, e.g., true), download (boolean, e.g., true)
Configuration	Deploy on GCP Cloud Functions. Store table data in Google Cloud Storage or BigQuery.
Output	Successful: Returns a JSON object with result (success status) and rows (array of table data, e.g., {"rows": [{"commodity__code": "0101210000", "commodity__suffix": "10", "commodity__description": "Horses"}]}) Failure: Returns error details (e.g., {"error": "Table not found"} for 404)
Workflow Example	Execute the getTableData action with dataset_id=uk-tariff-2021-01-01, version_id=v2.1.0, table_id=commodities, and format=csv. Store the CSV data in Google Cloud Storage. Load the data into BigQuery for querying and analysis.

Get Report Data

Action	getReportData
Purpose	Retrieves data for a specific report in a dataset version.
Parameters	Required: dataset_id (string, e.g., "uk-tariff-2021-01-01"), version_id (string, e.g., "v2.1.0"), report_id (string, e.g., "quotas-including-current-volumes"), format (string, enum: [csv, ods, parquet], e.g., csv) Optional: query-s3-select (string, e.g., "SELECT * FROM reports WHERE quota__order_number='50123'"), query-simple (boolean, e.g., true), download (boolean, e.g., true)
Configuration	Deploy on GCP Cloud Run. Use BigQuery for storing report data.
Output	Successful: Returns a JSON object with result (success status) and rows (array of report data, e.g., {"rows": [{"quota__order_number": "50123", "quota_definition__validity_start_date": "2021-01-01", "quota_definition__status": "Exhausted"}]}) Failure: Returns error details (e.g., {"error": "Report not found"} for 404)
Workflow Example	Execute the getReportData action with dataset_id=uk-tariff-2021-01-01, version_id=v2.1.0, report_id=quotas-including-current-volumes, and format=csv. Store the report data in BigQuery. Create a Data Studio dashboard to visualize quota statuses.

Workflow Creation with the Connector

Example Workflow: Dataset Exploration and Analysis

List Available Datasets	Execute the listDatasets action with format=json to fetch all dataset IDs. Store the dataset list in BigQuery for reference. Display dataset IDs in a GCP-hosted web application for user selection.
Explore Dataset Versions	Execute the listVersions action with dataset_id=uk-tariff-2021-01-01 and format=json. Cache version IDs in GCP Memorystore. Allow users to select a version in the application interface.
Retrieve Table and Report Data	Execute the listTables and listReports actions with dataset_id=uk-tariff-2021-01-01 and version_id=v2.1.0 to fetch table and report IDs. Execute the getTableData action with table_id=commodities and format=csv to retrieve table data. Execute the getReportData action with report_id=quotas-including-current-volumes and format=csv to retrieve report data. Store the data in Google Cloud Storage and load into BigQuery for analysis.
Analyze Metadata and Data	Execute the getMetadata action with dataset_id=uk-tariff-2021-01-01, version_id=v2.1.0, and format=csvw to fetch metadata. Use the metadata to define BigQuery schemas. Run BigQuery SQL queries to analyze table and report data (e.g., filter commodities by code or quotas by status).
Visualize Results	Use Google Data Studio to create dashboards visualizing commodity data and quota statuses. Deploy the dashboard on GCP App Engine for user access.

Assumptions Made

Authentication: The OpenPrescribing API currently requires no API key, but the documentation notes this may change. The connector assumes no authentication is needed unless specified otherwise by the API provider. If authentication is introduced, GCP Secret Manager can be used to manage API keys securely.
Deployment Environment: The connector is assumed to be deployed on GCP services like Cloud Functions, Cloud Run, or App Engine, based on the need for serverless or scalable solutions. Specific deployment details (e.g., runtime, memory) are not specified and should be configured based on application needs.
Error Handling: Error responses are assumed to follow the format specified in the OpenAPI schema (e.g., “error-type” for specific errors). Detailed error codes beyond HTTP 400 are not provided in the YAML, so generic error handling is assumed.
GeoJSON Integration: The GeoJSON output from getOrgLocation is assumed to be compatible with Google Maps API for visualization, as it is a common use case for GeoJSON data on GCP.

Pricing

bill-and-calculator-2023-11-27-05-25-06-utc 1

Request a Quote

Support

For Technical support please contact us on

custom-connectors-support@isolutions.sa

A tech solution company dedicated to providing innovation thus empowering businesses to thrive in the digital age.

Register to IBM x iSolution Event