Consuming Data for External Applications

This comprehensive guide provides instructions for consuming data products from the meshX Foundation platform using external systems and applications.The guide covers two primary consumption methods: direct API access for programmatic data retrieval and Power BI connector for business intelligence and visualization workflows.Each method includes detailed setup instructions, authentication requirements, and practical examples to help you successfully integrate Foundation data into your external systems.

Querying Data Products via API

How to Query Data Products via API

Prerequisites for API Access

Before querying data products via API, ensure you meet the following requirements:

• Query Permissions: Your user account must have data:data_product:query permission for each data product you want to query

• Authentication: Valid authentication credentials (Bearer token) for API access

• Organization Access: Access to your organization within the Foundation platform

• Table Name Knowledge: You need the specific table names for the data products you want to query

Obtaining Data Product Table Names

To query a data product, you need to know its table name. This information can be obtained from:

• GET /api/data/data_product/list - Returns all available data products

• GET /api/data/data_product?identifier=<data_product_identifier> - Returns specific data product details

• GET /api/data/data_product/list/query - Returns only data products the user can query (recommended)

API Response StructureThe API endpoints return data product information in the following structure:

{
  "identifier": "<data_product_identifier>",
  "urn": "urn:meshx:backend:data:<environment>:data_product:<data_product_identifier>",
  "name": "<data_product_name>",
  "is_system": false,
  "description": "<data_product_description>",
  "label": "<data_product_label>",
  "created_at": "<timestamp>",
  "state": {
    "code": "<status_code>",
    "reason": "<status_message>",
    "healthy": true
  },
  "owner": "<owner_email>",
  "host_mesh_identifier": "<host_mesh_id>",
  "table": {
    "engine": "<table_engine>",
    "table_name": "<full_table_name>"
  }
}

Key Information for Querying:

• The table.table_name field contains the exact table name to use in your SQL queries

• The table name follows the format: <engine>.<schema>."<actual_table_name>"

• Use the complete table.table_name value as-is in your queries

• We recommend using GET /api/data/data_product/list/query as it returns only data products you have permission to query

Important: All tables referenced in your query must belong to data products for which you have the data:data_product:query permission. Queries referencing unauthorized tables will be rejected.

Endpoint Details

Base Endpoint: /api/data/data_product/queryMethod: POSTQuery Parameters:

• format (optional): Specifies the response format

  • Available values: json, csv

  • Default value: json

Request Body Schema

{
  "query": "SELECT * FROM <table name>"
}

Only SELECT statements are allowed and supported.

Response Formats

JSON Format Response

When format=json (or no format specified), the response structure includes two main components:Response Structure:json

{
  "columns": [
    ["column_name", "data_type"],
    ["column_name", "data_type"]
  ],
  "rows": [
    [value1, value2, ...],
    [value1, value2, ...]
  ]
}

Components Explained:

• columns: An array of arrays where each inner array contains:

  • [0]: Column name (string) - the name of the database column

  • [1]: Data type (string) - the SQL data type of the column (e.g., "integer", "varchar", "timestamp")

• rows: An array of arrays where each inner array represents a single row of data:

  • Each inner array contains values in the same order as defined in the columns array

  • Values are returned in their native JSON types (numbers for integers, strings for varchar, etc.)

  • The position of each value corresponds to the column position in the columns array

Example JSON Response:

{
  "columns": [
    ["accident_id", "integer"],
    ["report_id", "integer"],
    ["accident_type", "varchar"],
    ["accident_count", "integer"],
    ["workers_affected_count", "integer"],
    ["cost", "integer"]
  ],
  "rows": [
    [1, 1, "Falls", 0, 0, 0],
    [2, 1, "Machinery Incident", 0, 0, 0],
    (...)
  ]
}

In this example:

• The first row [1, 1, "Falls", 0, 0, 0] represents: accident_id=1, report_id=1, accident_type="Falls", accident_count=0, workers_affected_count=0, cost=0

• The second row [2, 1, "Machinery Incident", 0, 0, 0] represents: accident_id=2, report_id=1, accident_type="Machinery Incident", accident_count=0, workers_affected_count=0, cost=0

CSV Format Response

When format=csv, the response includes:

• Header row with column names

• Column metadata comments (format: #COLUMN:column_name|data_type)

• Data rows

• Row count comment at the end (format: #ROWS:n)

Example CSV Response:

accident_id,report_id,accident_type,accident_count,workers_affected_count,cost
#COLUMN:accident_id|integer
#COLUMN:report_id|integer
#COLUMN:accident_type|varchar
#COLUMN:accident_count|integer
#COLUMN:workers_affected_count|integer
#COLUMN:cost|integer
1,1,Falls,0,0,0
2,1,Machinery Incident,0,0,0
(...)
#ROWS:435

Important: The #ROWS:n comment at the end indicates successful transmission. If this comment is missing, it signifies an interrupted transmission and the data may be incomplete.

Example cURL Request

curl -X 'POST' \
  'https://<foundation url>/api/data/data_product/query?format=csv' \
  -H 'accept: application/json' \
  -H 'x-org: <your_organization>' \
  -H 'Authorization: Bearer <your_access_token>' \
  -H 'Content-Type: application/json' \
  -d '{
  "query": "SELECT * FROM <your table>"
}'