System Connection Guide

DataHive supports the following system connection methods for accessing and exporting datasets:

Category	Method	Use Cases
DataHive Web API	Asynchronous Query Job	Bulk data extraction and processing. System able to implement job tracking and polling. System has network/platform restrictions that prevents systems from accessing Snowflake private endpoint (e.g Agency Gateway only allows for file based ingress, or is unable to add DNS records).
	Synchronous Query	Simple filtering queries. Need to receive the data within the same request. System has network/platform restrictions that prevents systems from accessing Snowflake private endpoint (e.g Agency Gateway is unable to add DNS records). If the data field you want to filter on is an identifier, it must be available in identifiable form. If a Service Level Agreement and higher rate limit is needed, then the Enhanced tier is required.
Direct Connection to DataHive's Snowflake	Snowflake SQL Interfaces	Require flexible query pattern for data egress via SQL. Bulk data egress or single/multi-record retrieval. System that require data access from the Internet.

---

1. DataHive Web API​

1a. Asynchronous Query Job​

The Asynchronous Web API provides a way to submit queries and retrieve results asynchronously, in bulk.

There are two versions of the Asynchronous Query Job API available:

• v1: Supports basic date range filtering and simple key-value additional filters. Filter metadata is retrieved via GET::/v1/subscription_requests/{request_id}/datasets/{dataset_id}/query_filters and GET::/v1/subscription_requests/{request_id}/datasets/{dataset_id}/query_filters/{filter_key}.
• v2 (Recommended): Adopts the same flexible query filter syntax as the Synchronous Query API. This includes support for terms (exact match), range (numeric/date bounds), and exists (field presence) filters, which can be composed using boolean operators (and, or, not). v2 also allows filtering on a larger set of columns beyond date and additional filter fields available in v1. Filter metadata can be retrieved via the same endpoint used for Synchronous Query: GET::/v1/subscription_requests/{request_id}/query_metadata/{ds_or_dp_id}.

Both versions currently coexist, differentiated by the version path prefix (v1 or v2). Users are strongly encouraged to migrate to v2 for its richer filtering capabilities and alignment with the Synchronous Query API.

Workflow:

1. Submit a Query Job

   • Endpoint: POST::/v1/subscription_requests/{request_id}/query_jobs (or /v2/ for v2)
   • Response: A job_id is returned, which can be used to track query progress.

2. Poll for completion

   • Endpoint: GET::/v1/query_jobs/{job_id}/results
   • When the job completes, the response includes a short-lived URL to download the results in CSV/parquet format.

Constraints:

• Maximum compressed file size: 5 GB
• Maximum concurrent query jobs: 5 per account
• Result format: CSV/parquet file download only

---

1b. Synchronous Query​

The Synchronous Web API provides a way to submit queries and retrieve a small subset of results synchronously.

There are two tiers of synchronous APIs available, Standard and Enhanced. Opt for the Enhanced tier if a Service Level Agreement (SLA) and higher rate limit is required with DataHive.

Workflow:

1. Query the metadata endpoint of the dataset:

   • Endpoint: GET::https://api-stg.datahive.gov.sg//v1/subscription_requests/{request_id}/query_metadata/{dataset_id}?scope=user
   • Response: Metadata of the dataset.
     • The information will help you formulate the query in the next step.
     • If you are using Synchronous API (Enhanced), look out for the enhanced_syncapi_metadata attribute, as it contains additional metadata specific to the Enhanced tier.

2. Query the Synchronous API endpoint

   • Endpoint:
     • [API Spec] Synchronous API (Standard) POST::https://api-stg.datahive.gov.sg//v1/subscription_requests/{request_id}/query/{dataset_id}
     • [API Spec] Synchronous API (Enhanced) POST::https://gateway-stg.datahive.gov.sg/syncapi/v1/subscription_requests/{request_id}/query/{dataset_id}
   • Response: The result of the query.
     • To get query results in subsequent pages, you can increment the page field in the request body. Each page contains a maximum of 1000 records.
     • [Standard Tier] Please note that the query results are retained for 5 minutes only, so please ensure to fetch all subsequent pages within this timeframe.

---

2. Direct Connection to DataHive’s Snowflake – Asynchronous / Synchronous​

Client systems can directly connect to DataHive’s managed Snowflake environment for synchronous or asynchronous SQL query execution.

Workflow:

1. Establish Network Connection:

   • Refer to Connection setup guide

2. Connecting to DataHive's Snowflake:

   • Snowflake SQL connectors

     • Query via Snowflake's "JDBC/ODBC-like" connectors using SQL.
     • ℹ️ Connectors provides a "JDBC/ODBC-like" SDK interface, but runs on REST APIs.

   • OR: Snowflake SQL REST API

     • Provides SQL access via REST API.

---

Choosing Between Options​

For single/multi-record retrieval of data, DataHive recommends either Snowflake connection or Synchronous API (Standard) option. If a SLA and higher rate limit is required, then please opt for Synchronous API (Enhanced).

For bulk egress of data, DataHive recommends the Snowflake Connection method as it offers more query flexibility than Asynchronous Query Job method.

Aspect	Asynchronous Query Job	DataHive Synchronous API (Standard)	DataHive Synchronous API (Enhanced)	Snowflake Connection
Accessibility	Open to all DataHive accounts with active data requests.	Open to all accounts with active data requests.	Provisioning of API is subject to DataHive's approval. Standard pricing may apply.	Access by Snowflake SDK or REST API via a GEN-routable private endpoint.
Use Case	Bulk data extraction for processing.	Good for ad-hoc single/multi-record queries with no SLA requirements.	Suitable for fast single/multi-record retrieval with SLA requirements.	Flexible, can support bulk data processing and singleton/multi-record retrieval via optimisation.
Rate Limit	Maximum of 5 concurrent query jobs 5 per account	10 queries per minute per account.	Higher rate limits will be provisioned based on use case.	Shared warehouse & queries will be queued based on the WOG usage.
API Endpoint	Access API from: https://api-stg.datahive.gov.sg/	Access API from: https://api-stg.datahive.gov.sg/	Access API from: https://gateway-stg.datahive.gov.sg/syncapi	Accesss from Snowflake private endpoint or Snowflake public endpoint.
Authentication	Authentication via Bearer token.	Authentication via Bearer token.	Authentication via Bearer token and DataHive Gateway Key.	Authentication by SDK or by Bearer token.
Query Pattern	Basic filtering.	More permissive filtering capabilities.	More restrictive compared to standard tier. Can only run queries that are supported by syncapi_enhanced_supported_queries defined in Get sync query dataset metadata endpoint.	Has flexibility to express the egress in form of SQL.
Usage	Refer to the API Usage	Refer to the API Usage.	This feature is in private preview. Email admin@datahive.gov.sg to get started.	Send SQL queries via SDK or REST API.

---

References​

• Snowflake Documentation – Connectors
• Snowflake REST API

---