This document describes how to connect to YMatrix and query using the programming language. Two ways of high availability and non-high availability connection will be given.
High availability refers to YMatrix's [automatic fault transfer mechanism] (/doc/5.1/maintain/crash_recover). After the master node (Master) fails, the master node standby node (Standby) provides services to the outside.
High Availability Connection refer to the program automatically connecting to the node after the failover after YMatrix fails.
High Available Connection Strings (Connection URLs/DSN) provides a standardized format that can be used in different applications without re-entering the complete connection information.
It usually consists of multiple fields, each representing different connection information, for example:
postgres://username:password@master_ip:master_port,standby_ip:standby_port/database?sslmode=disable
in:
username and password are the username and password for logging in to the database, respectively.master_ip:master_port and standby_ip:standby_port are the connection information for Master and Standby.database represents the name of the database to be connected.sslmode indicates whether SSL connection is enabled.Notes!
YMatrix supports high-speed writing of data through the MatrixGate API interface. For details, see Programming Language Write MatrixGate.
Create sample user ymatrix with password 1234.
=# CREATE USER ymatrix PASSWORD '1234';It is recommended to use the official driver PostgreSQL JDBC Driver as the database connection driver.
package com.postgresqltutorial;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class App{
private final String url = "jdbc:postgres://ymatrix:[email protected]:5432,172.16.100.82:5432/ymatrixdb?sslmode=disable";
private final String user = "ymatrix";
private final String password = "1234";
/**
* Connect to the YMatrix database
*
* @return a Connection object
*/
public Connection connect() {
Connection conn = null;
try {
conn = DriverManager.getConnection(url, user, password);
System.out.println("Connected to the YMatrix server successfully.");
} catch (SQLException e) {
System.out.println(e.getMessage());
}
return conn;
}
/**
* @param args the command line arguments
*/
public static void main(String[] args) {
App app = new App();
app.connect();
}
}It is recommended to use psycopg 2/3 as the database connection driver.
# -*- coding: utf-8 -*-
import psycopg2
class YMatrix(object):
def __init__(self):
self.dsn = "postgres://ymatrix:[email protected]:5432,172.16.100.82:5432/ymatrixdb?sslmode=disable"
def get_conn(self):
conn = psycopg2.connect(dsn=self.dsn)
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" \
")USING MARS3" \
"DISTRIBUTED BY (tag_id)" \
"ORDER 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 = YMatrix()
mxdb.create_table()
mxdb.insert()
print(mxdb.select())It is recommended to use pgx as the database connection driver.
package main
import (
"context"
"fmt"
"github.com/jackc/pgx/v4"
)
func main() {
// Build connection configuration
dsn := "postgres://ymatrix:<password>@172.16.100.81:5432,172.16.100.82:5432/ymatrixdb?sslmode=disable"
config, err := pgx.ParseConfig(dsn)
if err != nil {
fmt.Println("Unable to parse config:", err)
return
}
// Connect to the database
conn, err := pgx.ConnectConfig(context.Background(), config)
if err != nil {
fmt.Println("Unable to connect to database:", err)
return
}
defer conn.Close(context.Background())
}It is recommended to use ruby-pg as the database connection driver and compile with postgresql 12's headers.
require 'pg'
# Build connection configuration
dsn = "postgres://ymatrix:[email protected]:5432,172.16.100.82:5432/ymatrixdb?sslmode=disable"
conn = PG.connect(dsn)
# Perform database operations
begin
conn.exec('SELECT * FROM table_name') do |result|
result.each do |row|
# Process each row of data
puts row.inspect
end
end
rescue PG::Error => e
puts "执行数据库操作时发生错误：#{e.message}"
ensure
# Close the database connection
conn.close if conn
endIt is recommended to use rust-postgres as the database connection driver.
use postgres::{Client, Error, NoTls};
fn main() -> Result<(), Error> {
let mut client = Client::connect(
"postgresql://ymatrix:[email protected]:5432,172.16.100.82:5432/ymatrixdb",
NoTls,
)?;
for row in client.query("SELECT * FROM table_name", &[])? {
let id: i32 = row.get(0);
let name: String = row.get(1);
println!("ID: {}, Name: {}", id, name);
// Here you can perform the data processing operations you need
}
Ok(())
}It is recommended to use npgsql as the database connection driver.
C#
using System;
using Npgsql;
class Program
{
static void Main()
{
// Configure high availability DSN
var connectionString = "Host=sdw1, sdw2;Port=5432;Database=mydatabase;Username=myuser;Password=mypassword;Load Balance Hosts=true";
// Create a database connection
using var connection = new NpgsqlConnection(connectionString);
try
{
// Open a database connection
connection.Open();
// Perform database operations
using var command = new NpgsqlCommand("SELECT * FROM table_name", connection);
using var reader = command.ExecuteReader();
// Process query results
while (reader.Read())
{
// Process each row of data
var column1Value = reader.GetString(0);
var column2Value = reader.GetInt32(1);
Console.WriteLine($"{column1Value}, {column2Value}");
}
}
catch (Exception ex)
{
Console.WriteLine($"An error occurred: {ex.Message}");
}
}
}It is recommended to use ODBC as the database connection driver.
Notes!
All of the following commands need to be executed using the root user or through the sudo permissions.
Download and install ODBC.
# yum install -y unixODBC.x86_64
# yum install -y postgresql-odbc.x86_64Edit ODBC related files.
# cat /usr/local/etc/odbcinst.ini
[PostgreSQL]
Description = ODBC for PostgreSQL
Driver = /usr/lib/psqlodbcw.so
Setup = /usr/lib/libodbcpsqlS.so
Driver64 = /usr/lib64/psqlodbcw.so
Setup64 = /usr/lib64/libodbcpsqlS.so
FileUsage = 1
# cat /usr/local/etc/odbc.ini
[pg]
Description = Test to pg
Driver = PostgreSQL
Database = test
// The content of "<>" needs to be replaced with the actual cluster information, and the "<>" symbol is removed
Servername = <Master,Standby>
UserName = mxadmin
Password = mxadmin
Port = <Master Port,Standby Port>
ReadOnly = 0Edit C/C++ related files.
# cat test.c
#include <stdio.h>
#include <sql.h>
#include <sqlext.h>
#include <string.h>
#include <sqltypes.h>
int main()
{
SQLHENV henv;
SQLHDBC hdbc;
SQLHSTMT hstmt;
SQLRETURN ret;
// Assign environment handles
ret = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
// Set environment properties
ret = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0);
// Assign a connection handle
ret = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
// Connect to the database
ret = SQLConnect(hdbc, "pg", SQL_NTS, "mxadmin", SQL_NTS, "mxadmin", SQL_NTS);
if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)
{
printf("Connected to PostgreSQL database successfully.\n");
// Create a table and replace <tablename> with actual information
ret = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
ret = SQLExecDirect(hstmt, "CREATE TABLE <tablename> (id int) USING MARS3 DISTRIBUTED BY (id) ORDER BY (id)", SQL_NTS);
if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)
{
printf("成功创建表<tablename>.\n");
}
// Disconnect the database
SQLDisconnect(hdbc);
}
else
{
printf("Failed to connect to PostgreSQL database.\n");
}
// Release the handle
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
SQLFreeHandle(SQL_HANDLE_ENV, henv);
return 0;
}Compile.
# gcc -lodbc -o test test.crun.
# ./test
Connected to PostgreSQL database successfully.Confirm that the table has been successfully created.
=# \d <tablename>
Table "public.test2"
Column | Type | Collation | Nullable | Default
-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
id | integer | | |
Distributed by: (id)It is recommended to use the postgres module as the database connection driver.
const postgres = require('postgres')
async function query() {
// Create a highly available database connection
const conn = postgres(`postgres://username:password@master,stanby:port/database?sslmode=disable`)
try {
// Execute query SQL
const rows = await conn`SELECT id, name FROM users`
rows.forEach(row => {
console.log(`id: ${row.id}, name: ${row.name}`)
})
} catch (err) {
console.error(err)
} finally {
// Close the connection
await conn.end()
}
}
query()