Query data with SQL#
Read your data back from Nominal with SQL, through the SQL REST API. Writing queries covers the tables you can query and the SQL they accept.
To connect a Postgres-compatible driver such as tokio-postgres instead, see Postgres clients.
Run a query#
Add reqwest, with its json feature, and serde_json:
cargo add reqwest --features json
cargo add serde_json
// Load the API key, base URL and workspace that API Keys Setup saved
let config = nominal::Config::load()?;
let profile = config.get_profile("default").ok_or("no profile named default")?;
// Send the query, and fail on an error status
let response = reqwest::Client::new()
.post(format!("{}/sql/v1/query", profile.base_url()))
.bearer_auth(profile.token())
.json(&serde_json::json!({
"query": "SELECT dataset_rid, name FROM datasets LIMIT 20",
"workspace_rid": profile.workspace_rid(),
"result_format": "SQL_SERVICE_QUERY_RESULT_FORMAT_CSV",
}))
.send()
.await?
.error_for_status()?;
// Read the result: CSV with a header row
let csv = response.text().await?;
println!("{csv}");
Query has the endpoint’s limits and its other return formats.
Export a large result#
/sql/v1/query/export writes a query’s whole result to a file, with no row or size limit, and returns a download URL:
// Send the export request, and fail on an error status
let response = reqwest::Client::new()
.post(format!("{}/sql/v1/query/export", profile.base_url()))
.bearer_auth(profile.token())
.json(&serde_json::json!({
"query": "SELECT ts, channel, value FROM points_double WHERE dataset_rid = '<ri.catalog.instance.dataset.identifier>'",
"workspace_rid": profile.workspace_rid(),
"format": "SQL_SERVICE_EXPORT_FORMAT_CSV", // or SQL_SERVICE_EXPORT_FORMAT_PARQUET
}))
.send()
.await?
.error_for_status()?;
// Read the download URL. It carries its own credentials, so keep it secret.
let export: serde_json::Value = response.json().await?;
let url = export["presignedUrl"].as_str().ok_or("no presignedUrl in the response")?;
// Download the file
let download = reqwest::get(url).await?.error_for_status()?;
std::fs::write("export.csv", download.bytes().await?)?;
The export runs the whole query before it returns the URL, so a large query can take a while. The URL works for a limited time. When it expires, http code 403 Forbidden is returned and you will have to perform the export again.
Export can also generate a Parquet file. To enable, change format in the request to SQL_SERVICE_EXPORT_FORMAT_PARQUET, and save the file as export.parquet. Export covers Parquet’s compression and row group options.