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

KeyValue
qDML 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.
\lList 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.
\gpuExecute in GPU mode’s.
\cpuExecute in CPU mode’s.
\timingPrint timing information.
\notimingDo not print timing information.
\memory_summaryPrint memory usage summary.
\versionPrint 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_cpuReleases CPU memory held by OmniSci server Data Manager
\clear_gpuReleases 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.

DatatypeSize (bytes)Notes
BIGINT8Minimum value: -9,223,372,036,854,775,807; maximum value: 9,223,372,036,854,775,807.
BIGINT ENCODING FIXED(8)1Minimum value: -127; maximum value: 127
BIGINT ENCODING FIXED(16)2Same as SMALLINT.
BIGINT ENCODING FIXED(32)4Same as INTEGER.
BOOLEAN1TRUE: ‘true’, ‘1’, ‘t’. FALSE: ‘false’, ‘0’, ‘f’. Text values are not case-sensitive.
DATE14Range 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)2Range 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)4Default encoding; same as DATE.When you create a column using DATE ENCODING FIXED(32), OmniSci defines the column as DATE ENCODING DAYS(16).
DECIMAL2, 4, or 8Takes 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.
DOUBLE8Variable precision. Minimum value: -1.79 x e^308; maximum value: 1.79 x e^308
EPOCH 8Seconds ranging from -30610224000 (1/1/1000 00:00:00) through 185542587100800 (1/1/5885487 23:59:59).
FLOAT4Variable precision. Minimum value: -3.4 x e^38; maximum value: 3.4 x e^38.
INTEGER4Minimum value: -2,147,483,647; maximum value: 2,147,483,647.
INTEGER ENCODING FIXED(8)1Minumum value: -127; maximum value: 127.
INTEGER ENCODING FIXED(16)2Same as SMALLINT.
LINESTRINGVariable2Geospatial datatype. A sequence of 2 or more points and the lines that connect them. For example: LINESTRING(0 0,1 1,1 2)
MULTIPOLYGONVariable2Geospatial 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)))
POINTVariable2Geospatial 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)
POLYGONVariable2Geospatial 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))
SMALLINT2Minimum value: -32,767; maximum value: 32,767.
SMALLINT ENCODING FIXED(8)1Minumum value: -127 ; maximum value: 127.
TEXT ENCODING DICT4Max cardinality 2 billion distinct string values. Maximum string length is 32,767.
TEXT ENCODING DICT(8)1Max cardinality 255 distinct string values.
TEXT ENCODING DICT(16)2Max cardinality 64 K distinct string values.
TEXT ENCODING NONEVariableSize of the string + 6 bytes. Maximum string length is 32,767.
TIME8Minimum value: 00:00:00; maximum value: 23:59:59.
TIME ENCODING FIXED(32)4Minimum value: 00:00:00; maximum value: 23:59:59.
TIMESTAMP(0)8Linux 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)8Linux 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)8Linux 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)8Linux 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)4Range: 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).
TINYINT1Minimum 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"