Medium Twitter Linkedin Facebook
Hosts Dashboard

Z6 Database API

What you need to get started

The Zerosix DB API is an HTTP POST extension of OmniSQL that allows for easy interaction with your rented instance. 

Placeholders for example code in examples are in capital letter below

  • A GPU Omnisci Database Instance
  • Host Name (HOST_NAME)
  • API Port (API_PORT)
  • Username (USERNAME)
  • Password (PASSWORD)

Optional for SCP:

  • SSH Port (SSH_PORT)
  • SSH KeyFile (KEY_FILE)

Endpoint

You can construct your endpoint by combining the Host Name and API Port.

Structure:

HOST_NAME: API_PORT/remote/

http://zerosix.hopto.org:222/remote/

How to Authenticate

We use BASIC HTTP Authentication via HTTP POST. Upon rental of an instance we provide a username and password via email. Using your programing language of choice place a Base64 encoded string of the Username and Password, appended to the text “Basic ” as follows:

"Authorization: Basic aW90dWl6aTpHUFVzMzrODNh"

Queries

The Zerosix DB uses standard SQL queries as a post field. Results are returned in a JSON format with two nodes that define the header and the result which contains the result of the query. For larger result sets you can export to file and retrieve the result set via SCP and SSH.

DML

Key Value
q DML to be executed by API

Example Q Value:

Select count(*) from TABLE;

Example Result: HTTP 200

{
    "header": [
        "EXPR$0"
    ],
    "result": [
        [
            "172854"
        ]
    ]
}

Other Administrative Commands

\u [regex] List all users, optionally matching regex.
\l List all databases.
\t [regex] List all tables, optionally matching regex.
\v [regex] List all views, optionally matching regex.
\d <table> List all columns of a table or a view.
\c <database> <user> <password> Connect to database as different user.
\db [database|…] Switch database. Use … to switch to your default.
\o <table> Return a memory optimized schema based on current data distribution in table.
\gpu Execute in GPU mode’s.
\cpu Execute in CPU mode’s.
\timing Print timing information.
\notiming Do not print timing information.
\memory_summary Print memory usage summary.
\version Print OmniSci Server version.
\copy <file path> <table> Copy data from file to table.
\detect {parquet} <file_name|s3_details> Reads a sample of the specified file and returns a CREATE TABLE statement
\clear_cpu Releases CPU memory held by OmniSci server Data Manager
\clear_gpu Releases GPU memory held by OmniSci server Data Manager

Errors

The result of a query that is malformed or references a Table, View, or Database that does not exist.

Example Result: HTTP 400

{
    "result": "Error: There were no results."
}

Limitations

JSON response is limited to 10MBs. If you need to extract more data it is suggested to export to a local file and use SCP or a web accessible file to retrieve the result set. Alternatives are to use pagination by adding offsets to your queries. 

See: How to export data section

Useful Queries

How to create a database

Example d Value (Create Table Statement)

CREATE DATABASE testdb;

Example Result (Returns a list of available databases and owners) HTTP 200

{
    "result": [
        "omnisci | admin",
        "testdb | admin"
    ]
}

How to create a user

Example d Value

CREATE USER johnsmith (password= 'test1234', default_db='omnisci');

Example Result (Returns a list of available users) HTTP 200

{
    "result": [
        "admin",
        "rdonaire"
    ]
}

How to create a table

Example d Value (Create Table Statement)

CREATE TABLE example (
Field1 INT,
Field2 TEXT,
Field3 TIMESTAMP);

Example Result (Returns a list of available tables on database with added table) HTTP 200

{
    "result": [
        "omnisci_states",
        "omnisci_counties",
        "omnisci_countries",
        "example"
    ]
}

How to load data into the database

Data can be imported directly into the database from Amazon S3, via a local instance file, or direct insert.

Inserts

Example d Value (Single ad-hoc inserts)

INSERT INTO <destination_table> VALUES (<value>, ...);

INSERT INTO <table> (<column>, ...) VALUES (value, ...);

From Amazon S3

Example d Value (COPY Table Statement FROM S3 for bulk loading)

COPY MY_TABLE FROM 'S3_FILE_LOCATION’ WITH (s3_access_key = ‘S3_ACCESS_KEY',s3_secret_key = 'S3_ACCESS_KEY',s3_region = 'S3_REGION');

SCP Import

Example SCP statement on your local machine to rented instance

scp -i PATH_SSH_KEY_FILE -P SSH_PORT  LOCALFILEPATH/FILENAME.CSV renter@HOSTNAME:/home/renter/data/FILENAME.CSV  

Example how to bulk load transferred file into database

COPY TABLE FROM  ‘LOCALFILEPATH/FILENAME.CSV’

Example Result (Returns a summary of all loaded records) HTTP 200

{
    "result": "ResultLoaded: 320635 recs, Rejected: 0 recs in 2.253000 secs"
}

How To Export Data

Save File To Instance

COPY (YOUR_QUERY_HERE) TO '/home/renter/data/FILENAME.CSV';

Example Result (Returns a empty string) HTTP 200

{
    "result": ""
}

Web Accessible file

Visit in a browser:

HOSTNAME:API_PORT/data/

Authenticate with API_USERNAME and API_PASSWORD

Click on saved File to download

SCP Export

scp -i PATH_SSH_KEY_FILE -P SSH_PORT  renter@HOSTNAME:/home/omnisci/FILENAME.CSVLOCALFILEPATH

Datatypes (DDL)

Each datatype uses space in memory and on disk. For certain datatypes, you can use fixed encoding for a more compact representation of these values. Datatypes, variations, and sizes are described in the following table.

Datatype Size (bytes) Notes
BIGINT 8 Minimum value: -9,223,372,036,854,775,807; maximum value: 9,223,372,036,854,775,807.
BIGINT ENCODING FIXED(8) 1 Minimum value: -127; maximum value: 127
BIGINT ENCODING FIXED(16) 2 Same as SMALLINT.
BIGINT ENCODING FIXED(32) 4 Same as INTEGER.
BOOLEAN 1 TRUE: ‘true’, ‘1’, ‘t’. FALSE: ‘false’, ‘0’, ‘f’. Text values are not case-sensitive.
DATE1 4 Range in years: +/-5,883,517 around epoch. Maximum date January 1, 5885487 (approximately).Minimum value: -2,147,483,648; maximum value: 2,147,483,647.Supported formats when using COPY FROM: mm/dd/yyyy, dd-mmm-yy, yyyy-mm-dd, dd/mmm/yyyy.
DATE ENCODING FIXED(16) 2 Range in days: -32,768 – 32,767Range in years: +/-90 around epoch, April 14, 1880 – September 9, 2059.Minumum value: -2,831,155,200; maximum value: 2,831,068,800.Supported formats when using COPY FROM: mm/dd/yyyy, dd-mmm-yy, yyyy-mm-dd, dd/mmm/yyyy.
DATE ENCODING FIXED(32) 4 Default encoding; same as DATE.When you create a column using DATE ENCODING FIXED(32), OmniSci defines the column as DATE ENCODING DAYS(16).
DECIMAL 2, 4, or 8 Takes precision and scale parameters: DECIMAL(precision,scale)Size depends on precision:Up to 4: 2 bytes5 to 9: 4 bytes10 to 18 (maximum): 8 bytesScale must be less than precision.
DOUBLE 8 Variable precision. Minimum value: -1.79 x e^308; maximum value: 1.79 x e^308
EPOCH  8 Seconds ranging from -30610224000 (1/1/1000 00:00:00) through 185542587100800 (1/1/5885487 23:59:59).
FLOAT 4 Variable precision. Minimum value: -3.4 x e^38; maximum value: 3.4 x e^38.
INTEGER 4 Minimum value: -2,147,483,647; maximum value: 2,147,483,647.
INTEGER ENCODING FIXED(8) 1 Minumum value: -127; maximum value: 127.
INTEGER ENCODING FIXED(16) 2 Same as SMALLINT.
LINESTRING Variable2 Geospatial datatype. A sequence of 2 or more points and the lines that connect them. For example: LINESTRING(0 0,1 1,1 2)
MULTIPOLYGON Variable2 Geospatial datatype. A set of one or more polygons. For example:MULTIPOLYGON(((0 0,4 0,4 4,0 4,0 0),(1 1,2 1,2 2,1 2,1 1)), ((-1 -1,-1 -2,-2 -2,-2 -1,-1 -1)))
POINT Variable2 Geospatial datatype. A point described by two coordinates. When the coordinates are longitude and latitude, OmniSci stores longitude first, and then latitude. For example: POINT(0 0)
POLYGON Variable2 Geospatial datatype. A set of one or more rings (closed line strings), with the first representing the shape (external ring) and the rest representing holes in that shape (internal rings). For example: POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1))
SMALLINT 2 Minimum value: -32,767; maximum value: 32,767.
SMALLINT ENCODING FIXED(8) 1 Minumum value: -127 ; maximum value: 127.
TEXT ENCODING DICT 4 Max cardinality 2 billion distinct string values. Maximum string length is 32,767.
TEXT ENCODING DICT(8) 1 Max cardinality 255 distinct string values.
TEXT ENCODING DICT(16) 2 Max cardinality 64 K distinct string values.
TEXT ENCODING NONE Variable Size of the string + 6 bytes. Maximum string length is 32,767.
TIME 8 Minimum value: 00:00:00; maximum value: 23:59:59.
TIME ENCODING FIXED(32) 4 Minimum value: 00:00:00; maximum value: 23:59:59.
TIMESTAMP(0) 8 Linux timestamp from -30610224000 (1/1/1000 00:00:00) through 29379542399 (12/31/2900 23:59:59). Can also be inserted and stored in human-readable format: YYYY-MM-DD HH:MM:SS or YYYY-MM-DDTHH:MM:SS (the T is dropped when the field is populated).
TIMESTAMP(3) (milliseconds) 8 Linux timestamp from -30610224000000 (1/1/1000 00:00:00.000) through 29379542399999 (12/31/2900 23:59:59.999). Can also be inserted and stored in human-readable format: YYYY-MM-DD HH:MM:SS.fff or YYYY-MM-DDTHH:MM:SS.fff (the T is dropped when the field is populated).
TIMESTAMP(6) (microseconds) 8 Linux timestamp from -30610224000000000 (1/1/1000 00:00:00.000000) through 29379542399999999 (12/31/2900 23:59:59.999999). Can also be inserted and stored in human-readable format: YYYY-MM-DD HH:MM:SS.ffffff or YYYY-MM-DDTHH:MM:SS.ffffff (the T is dropped when the field is populated).
TIMESTAMP(9) (nanoseconds) 8 Linux timestamp from -9223372036854775807 (09/21/1677 00:12:43.145224193) through 9223372036854775807 (11/04/2262 23:47:16.854775807). Can also be inserted and stored in human-readable format: YYYY-MM-DD HH:MM:SS.fffffffff or YYYY-MM-DDTHH:MM:SS.fffffffff (the T is dropped when the field is populated).
TIMESTAMP ENCODING FIXED(32) 4 Range: 1901-12-13 20:45:53 – 2038-01-19 03:14:07. Can also be inserted and stored in human-readable format: YYYY-MM-DD HH:MM:SS or YYYY-MM-DDTHH:MM:SS (the T is dropped when the field is populated).
TINYINT 1 Minimum value: -127; maximum value: 127.

LINESTRING, MULTIPOLYGON, POINT, and POLYGON geospatial datatypes are supported**

Code Examples

Interaction with your instance’s API can happen with any language that supports HTTP POST. Here are a few examples.

Python

import requests

url = "http://HOST_NAME:API_PORT/remote.php"

payload = {'q': 'Select * from TABLE limit 10;'}
files = [

]
headers = {
  'Authorization': 'Basic aW90dWl6aTpHUFVzMzrODNh'
}

response = requests.request("POST", url, headers=headers, data = payload, files = files)

print(response.text.encode('utf8'))

Node.JS

var request = require('request');
var options = {
  'method': 'POST',
  'url': 'http://HOST_NAME:API_PORT/remote.php',
  'headers': {
    'Authorization': 'Basic aW90dWl6aTpHUFVzMzrODNh'
  },
  formData: {
    'q': 'Select * from TABLE limit 10;'
  }
};
request(options, function (error, response) { 
  if (error) throw new Error(error);
  console.log(response.body);
});

PHP

<?php

$curl = curl_init();

curl_setopt_array($curl, array(
  CURLOPT_URL => "http://HOST_NAME:API_PORT/remote.php",
  CURLOPT_RETURNTRANSFER => true,
  CURLOPT_ENCODING => "",
  CURLOPT_MAXREDIRS => 10,
  CURLOPT_TIMEOUT => 0,
  CURLOPT_FOLLOWLOCATION => true,
  CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
  CURLOPT_CUSTOMREQUEST => "POST",
  CURLOPT_POSTFIELDS => array('q' => 'Select * from TABLE limit 10;'),
  CURLOPT_HTTPHEADER => array(
    "Authorization: Basic aW90dWl6aTpHUFVzMzrODNh"
  ),
));

$response = curl_exec($curl);

curl_close($curl);
echo $response;

?>

RUBY

require "uri"
require "net/http"

url = URI("http://HOST_NAME:API_PORT/remote.php")

http = Net::HTTP.new(url.host, url.port);
request = Net::HTTP::Post.new(url)
request["Authorization"] = "Basic aW50dWl6aTpHUFVzMzIwODUh"
form_data = [['q', 'Select * from TABLE limit 10;']]
request.set_form form_data, 'multipart/form-data'
response = http.request(request)
puts response.read_body

C

CURL *curl;
CURLcode res;
curl = curl_easy_init();
if(curl) {
  curl_easy_setopt(curl, CURLOPT_CUSTOMREQUEST, "POST");
  curl_easy_setopt(curl, CURLOPT_URL, "http://HOST_NAME:API_PORT/remote.php");
  curl_easy_setopt(curl, CURLOPT_FOLLOWLOCATION, 1L);
  curl_easy_setopt(curl, CURLOPT_DEFAULT_PROTOCOL, "https");
  struct curl_slist *headers = NULL;
  headers = curl_slist_append(headers, "Authorization: Basic aW50dWl6aTpHUFVzMzIwODUh");
  curl_easy_setopt(curl, CURLOPT_HTTPHEADER, headers);
  curl_mime *mime;
  curl_mimepart *part;
  mime = curl_mime_init(curl);
  part = curl_mime_addpart(mime);
  curl_mime_name(part, "q");
  curl_mime_data(part, "Select * from TABLE limit 10;", CURL_ZERO_TERMINATED);
  curl_easy_setopt(curl, CURLOPT_MIMEPOST, mime);
  res = curl_easy_perform(curl);
  curl_mime_free(mime);
}
curl_easy_cleanup(curl);

C#

var client = new RestClient("http://HOST_NAME:API_PORT/remote.php");
client.Timeout = -1;
var request = new RestRequest(Method.POST);
request.AddHeader("Authorization", "Basic aW50dWl6aTpHUFVzMzIwODUh");
request.AlwaysMultipartFormData = true;
request.AddParameter("q", "Select * from TABLE limit 10;");
IRestResponse response = client.Execute(request);
Console.WriteLine(response.Content);

Go

package main

import (
  "fmt"
  "bytes"
  "mime/multipart"
  "net/http"
  "io/ioutil"
)

func main() {

  url := "http://HOST_NAME:API_PORT/remote.php"
  method := "POST"

  payload := &bytes.Buffer{}
  writer := multipart.NewWriter(payload)
  _ = writer.WriteField("q", "Select * from TABLE limit 10;")
  err := writer.Close()
  if err != nil {
    fmt.Println(err)
  }


  client := &http.Client {
  }
  req, err := http.NewRequest(method, url, payload)

  if err != nil {
    fmt.Println(err)
  }
  req.Header.Add("Authorization", "Basic aW50dWl6aTpHUFVzMzIwODUh")

  req.Header.Set("Content-Type", writer.FormDataContentType())
  res, err := client.Do(req)
  defer res.Body.Close()
  body, err := ioutil.ReadAll(res.Body)

  fmt.Println(string(body))
}

JavaScript – jQuery

var form = new FormData();
form.append("q", "Select * from TABLE limit 10;");

var settings = {
  "url": "http://HOST_NAME:API_PORT/remote.php",
  "method": "POST",
  "timeout": 0,
  "headers": {
    "Authorization": "Basic aW50dWl6aTpHUFVzMzIwODUh"
  },
  "processData": false,
  "mimeType": "multipart/form-data",
  "contentType": false,
  "data": form
};

$.ajax(settings).done(function (response) {
  console.log(response);
});

Swift

import Foundation

var semaphore = DispatchSemaphore (value: 0)

let parameters = [
  [
    "key": "q",
    "value": "Select * from TABLE limit 10;",
    "type": "text"
  ]] as [[String : Any]]

let boundary = "Boundary-\(UUID().uuidString)"
var body = ""
var error: Error? = nil
for param in parameters {
  if param["disabled"] == nil {
    let paramName = param["key"]!
    body += "--\(boundary)\r\n"