Release History
Requirements

Introduction

Quick onboard

Deployment

Data Modeling

Connecting

Write data

Migration

Query

Operations and Maintenance

Performance Tuning

Troubleshooting

Reference Guide

SQL Reference

FAQ

Code Examples

Earlier sections described how to create tables and perform data insertion and queries using psql in a command-line terminal. In real-world development, however, developers typically use programming languages to connect to the database and perform DML and DDL operations. This section demonstrates how to connect to MatrixDB and execute queries using Java, Python, Golang, and C.

MatrixDB is compatible with the PostgreSQL wire protocol. Therefore, you can connect to it using the same methods as for PostgreSQL.


1. JAVA

To connect to MatrixDB using Java, first download the JDBC driver from https://jdbc.postgresql.org/download.html.

Example code:

package main;
import java.sql.*;

public class Main {
    public static void main(String []args) {
        Connection connection = null;
        Statement statement = null;
        try {
            String url = "jdbc:postgresql://127.0.0.1:5432/mxadmin";
            String user = "mxadmin";
            String password = "abcd";
            Class.forName("org.postgresql.Driver");
            connection = DriverManager.getConnection(url, user, password);

            // Create table
            String sql = "CREATE TABLE data ("
                    + "time timestamp,"
                    + "tag_id int,"
                    + "metrics1 float8,"
                    + "metrics2 float8,"
                    + "metrics3 float8"
                    + ")Distributed by(tag_id)";
            statement = connection.createStatement();
            statement.execute(sql);

            // Insert data
            sql = "INSERT INTO data VALUES(now(), 1, 1.1, 1.2, 1.3)";
            statement.execute(sql);

            // Query data
            sql = "SELECT * FROM data";
            ResultSet resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                String ts = resultSet.getString(1);
                int tag_id = resultSet.getInt(2);
                double metrics1 = resultSet.getDouble(3);
                double metrics2 = resultSet.getDouble(4);
                double metrics3 = resultSet.getDouble(5);
                System.out.printf("%s %d %f %f %f\n", ts, tag_id, metrics1, metrics2, metrics3);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException(e);
            } finally {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    throw new RuntimeException(e);
                }
            }
        }
    }
}


2. Python

To connect to MatrixDB using Python, install psycopg2 first:

pip3 install psycopg2

Example code:

# -*- coding: utf-8 -*-
import psycopg2

class MatrixDB(object):
    def __init__(self):
        self.host = "127.0.0.1"
        self.user = "mxadmin"
        self.database = "mxadmin"
        self.port = "5432"
        self.password = "abcd"

    def get_conn(self):
        conn = psycopg2.connect(database=self.database,
                                user=self.user,
                                password=self.password,
                                host=self.host,
                                port=self.port)
        return conn

    def create_table(self):
        conn = self.get_conn()
        cursor = conn.cursor()
        sql = "CREATE TABLE data(" \
                "time timestamp," \
                "tag_id int," \
                "metrics1 float8," \
                "metrics2 float8," \
                "metrics3 float8" \
                ")Distributed by(tag_id)"
        cursor.execute(sql)
        conn.commit()
        conn.close()

    def insert(self):
        conn = self.get_conn()
        cursor = conn.cursor()
        sql = "INSERT INTO data VALUES(now(), 1, 1.1, 1.2, 1.3)"
        cursor.execute(sql)
        conn.commit()
        conn.close()

    def select(self):
        conn = self.get_conn()
        cursor = conn.cursor()
        sql = "SELECT * FROM data"
        cursor.execute(sql)
        data = cursor.fetchone()
        conn.commit()
        conn.close()
        return data

if __name__ == '__main__':
    mxdb = MatrixDB()
    mxdb.create_table()
    mxdb.insert()
    print(mxdb.select())


3. Golang

Connecting to MatrixDB using Golang requires the github.com/lib/pq package.

Example code:

package main

import (
    "database/sql"
    "fmt"
    "time"

    _ "github.com/lib/pq"
)

type Data struct {
    Time     time.Time
    TagId    int32
    Metrics1 float64
    Metrics2 float64
    Metrics3 float64
}

func getConn() (*sql.DB, error) {
    psqlInfo := fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=disable", "127.0.0.1", 5432, "mxadmin", "", "mxadmin")

    db, err := sql.Open("postgres", psqlInfo)
    if err != nil {
        return nil, err
    }

    return db, nil
}

func createTable() error {
    db, _ := getConn()
    defer db.Close()

    _, err := db.Exec(`
CREATE TABLE data(
    time timestamp,
    tag_id int,
    metrics1 float8,
    metrics2 float8,
    metrics3 float8
)Distributed by(tag_id)
`)
    return err
}

func insertData() error {
    db, _ := getConn()
    defer db.Close()

    _, err := db.Exec(`INSERT INTO data VALUES(now(), 1, 1.1, 1.2, 1.3)`)
    return err
}

func selectData() ([]Data, error) {
    db, _ := getConn()
    defer db.Close()

    datas := make([]Data, 0)
    rows, err := db.Query(`SELECT * FROM data`)
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    for rows.Next() {
        data := Data{}
        err = rows.Scan(&data.Time, &data.TagId, &data.Metrics1, &data.Metrics2, &data.Metrics3)
        if err != nil {
            return nil, err
        }
        datas = append(datas, data)
    }

    return datas, err
}

func main() {
    createTable()
    insertData()
    datas, _ := selectData()
    fmt.Println(datas)
}


4. C

To connect to MatrixDB using C, you must use the libpq library. This library is installed with MatrixDB and located in the $GPHOME directory.

When compiling, specify the header and library paths:

gcc -I $GPHOME/include -L $GPHOME/lib -lpq main.c -o main

Example code:

#include <stdio.h>
#include "libpq-fe.h"

void PQresultPrint(PGresult *res)
{
    int nFields = PQnfields(res);
    int nTuples = PQntuples(res);
    int i, j;
    for (i = 0; i < nTuples; i++)
    {
        for (j = 0; j < nFields; j++)
        {
            printf("%s ", PQgetvalue(res, i, j));
        }
        printf("\n");
    }
}

PGconn *GetConn()
{
    PGconn *conn;
    char str[128];

    sprintf(str, "host=%s port=%d dbname=%s user=%s password=%s",
            "127.0.0.1",
            5432,
            "mxadmin",
            "mxadmin",
            "abcd"
            );

    // Establish connection
    conn = PQconnectdb(str);
    if (PQstatus(conn) == CONNECTION_BAD)
    {
        fprintf(stderr, "Failed to connect to database!");
        fprintf(stderr, "%s", PQerrorMessage(conn));
        return NULL;
    }
    return conn;
}

PGresult *ExecuteQuery(const char *query)
{
    PGconn *conn;
    PGresult *res;

    conn = GetConn();
    if (conn == NULL)
        return NULL;

    // Execute SQL
    res = PQexec(conn, query);
    if (PQresultStatus(res) == PGRES_FATAL_ERROR)
    {
        fprintf(stderr, "%s", PQerrorMessage(conn));
        return NULL;
    }

    PQfinish(conn);
    return res;
}

PGresult *CreateTable()
{
    const char *sql =
"CREATE TABLE data \
( \
    time timestamp, \
    tag_id int, \
    metrics1 float8, \
    metrics2 float8, \
    metrics3 float8 \
)Distributed by(tag_id)";
    return ExecuteQuery(sql);
}

PGresult *InsertData()
{
    const char *sql = "INSERT INTO data VALUES(now(), 1, 1.1, 1.2, 1.3)";
    return ExecuteQuery(sql);
}

PGresult *SelectData()
{
    const char *sql = "SELECT * FROM data";
    return ExecuteQuery(sql);
}

int main()
{
    PGresult *res;
    res = CreateTable();
    if (res != NULL)
        PQclear(res);
    res = InsertData();
    if (res != NULL)
        PQclear(res);
    res = SelectData();
    if (res != NULL) {
        PQresultPrint(res);
        PQclear(res);
    }
    return 0;
}
Next →
Release History