SQL Command API

Endpoint

Send a POST request to Onehouse REST API using the endpoint:

https://api.onehouse.ai/v1/resource/

Required Headers

Name	Description
x-onehouse-account-uid	ID of the account. Find this in the Onehouse UI within the account console as AccountId when you click your profile in the top right.
x-onehouse-project-uid	ID of the project. Find this in the Onehouse UI for the selected project as ProjectID when you click your profile in the top right.
x-onehouse-api-key	Public key for your access token. Find this on the 'Tokens' tab of your service principal in the Onehouse console.
x-onehouse-api-secret	Secret key for your access token. Find this when you create a new access token on the 'Tokens' tab of your service principal in the Onehouse console.
x-onehouse-link-uid	Find this in the Onehouse UI for the selected project as RequestID when you click your profile in the top right.
x-onehouse-region	Region for the project. Find this in the Onehouse UI on the project selector at the top of the screen. Example format: us-west-1 or us-west1 (all lowercase).
x-onehouse-uuid	Your user ID. Find this in the Onehouse UI as UserID when you click your profile in the top right. You must be an admin in the Onehouse project or use a service principal.

Sample Request

In the body of the API request send a JSON object of the following form.

{
    "statement": "CREATE STREAM CAPTURE test_stream10 SOURCE = 'avro-kafka-source-palak' 
                  LAKE = 'staging' DATABASE = 'andy_api_test' TABLE_NAME = 'andy_api_table' WRITE_MODE = 'MUTABLE' 
                  CATALOGS = ('test_duplicates7') RECORD_KEY_FIELDS = ('date', 'timestamp') 
                  PARTITION_KEY_FIELDS = (field = 'date' partition_type = 'DATE_STRING' input_format = 'yyyy-mm-dd' output_format = 'yyyy-mm-dd') 
                  MIN_SYNC_FREQUENCY_MINS = 5 QUARANTINE_ENABLED = 'true' VALIDATIONS = ('schemavalidation', 'timestampvalidation')
                  WITH 'kafka.topic.name' = 'ghCfAvroPublicEvent', 'kafka.topic.type' = 'event'"
}

Refer to SQL Command Reference for statements corresponding different requests.

Rate Limiting

All projects have 10 queries per second. Reach out if you require a higher rate limit.

Sample Response

Upon successful request we receive a Request ID(requestId) in the response with HTTP 200. Sample response:

{
    "requestId": "da0eba48-c487-3668"
}

We can poll the status of this request using 'requestId' with Status API.

SQL Parsing Error Messages

Any error that is thrown while parsing SQL (mostly due to invalid syntax) is returned as part of the HTTP response header. Check the value of grpc-message key in the header of the response to see the error message.

If you use CURL to make the request, you can specify -v option to view the response headers.

Sample request (OCU Limit should be an Integer not a String):

curl -v --location 'https://api.onehouse.ai/v1/resource/' \
--header 'x-onehouse-account-uid: <ACCOUNT_UID>' \
--header 'x-onehouse-project-uid: <PROJECT_UID>' \
--header 'x-onehouse-api-key: <API_KEY>' \
--header 'x-onehouse-api-secret: <API_SECRET>' \
--header 'Content-Type: application/json' \
--header 'x-onehouse-link-uid: <PROJECT_REQUEST_ID>' \
--header 'x-onehouse-region: <PROJECT_REGION>' \
--header 'x-onehouse-uuid: <USER_ID>' \
--data '{
    "statement": "ALTER OCU SET LIMIT = '\''3'\''"
}'

Sample response with error message:

... Logs and other headers ...
< HTTP/2 400 
< content-type: application/grpc
< content-length: 0
< grpc-status: 3
< grpc-message: Invalid sql query: Encountered "\'3\'" at line 1, column 23.%0AWas expecting one of:%0A    <UNSIGNED_INTEGER_LITERAL> ...%0A    <APPROX_NUMERIC_LITERAL> ...%0A    <DECIMAL_NUMERIC_LITERAL> ...%0A    "+" ...%0A    "-" ...%0A

Note that errors other than SQL parsing errors can be fetched with the Status API.

Python Example

This example adds a MetaSync table service to a specified table. You can replace the statement with any Onehouse SQL API syntax.

import requests
import json

url = "https://api.onehouse.ai/v1/resource/"
api_key = "j+m8wRhgpKYFTLxCHNDzQA=="
api_secret = "tXpzrqfUBNK9yhS5+FmLM37xwfhVeZygJntCzHG4Dpq="
payload = json.dumps({
  "statement": """
      ALTER SERVICE IN TABLE hudi_trips_table_cow
      LAKE = 'external_hudi_new'
      DATABASE = 'external_hudi_new_discover'
      SERVICE = 'METASYNC'
      ENABLED = 'TRUE'
      WITH 'metasync.catalogs' = 'ajax'
"""
})

headers = {
  'x-onehouse-account-uid': '92e5f1ab-4c3a-4b81-b1fa-ec9c2e14d3f2',
  'x-onehouse-project-uid': '3afe72cd-b841-4135-a673-1289c992edf7',
  'x-onehouse-api-key': api_key,
  'x-onehouse-api-secret': api_secret,
  'Content-Type': 'application/json'
}

try:
    print("Sending request...")
    response = requests.request("POST", url, headers=headers, data=payload)
    print(f"Status code: {response.status_code}")
    print("Response text:")
    print(response.text)
except requests.exceptions.RequestException as e:
    print(f"An error occurred: {e}")

print("Script completed.")