Tablestore:Use the Tablestore driver for Go to query data by executing SQL statements

Usage notes

The SQL query feature is available in the China (Hangzhou), China (Shanghai), China (Beijing), China (Zhangjiakou), China (Hohhot), China (Ulanqab), China (Shenzhen), China (Chengdu), China (Hong Kong), Singapore, Indonesia (Jakarta), Germany (Frankfurt), SAU (Riyadh - Partner Region), and US (Virginia) regions.

Prerequisites

• If you want to query data as a Resource Access Management (RAM) user, a RAM user is created and all SQL operation permissions are granted to the RAM user. You can configure "Action": "ots:SQL*" in a custom policy to grant all SQL operation permissions to the RAM user. For more information, see Use a RAM policy to grant permissions to a RAM user.

• An AccessKey pair that consists of an AccessKey ID and an AccessKey secret is obtained. For more information, see Create an AccessKey pair.

• A data table is created, and a mapping table is created for the data table. For more information, see Step 3: Create a data table and Create a mapping table for a table.

Procedure

go get github.com/aliyun/aliyun-tablestore-go-sql-driver

// Specify an SQL statement based on your business requirements. In this example, an SQL statement is specified to query the data in the pk1, col1, and col2 columns of the test_table table. 
rows, err := db.Query("SELECT pk1, col1, col2 FROM test_table WHERE pk1 = ?", 3)
if err != nil {
    panic(err)    // Handle errors. 
}
for rows.Next() {
    var pk1 int64
    var col1 float64
    var col2 string
    err := rows.Scan(&pk1, &col1, &col2)
    if err != nil {
        panic(err)    // Handle errors. 
    }
}

// Specify an SQL statement based on your business requirements. In this example, an SQL statement is specified to query the data in the pk1, col1, and col2 columns of the test_table table. 
stmt, err := db.Prepare("SELECT pk1, col1, col2 FROM test_table WHERE pk1 = ?")
if err != nil {
    panic(err)    // Handle errors. 
}
rows, err := stmt.Query(3)
if err != nil {
    panic(err)    // Handle errors. 
}
for rows.Next() {
    var pk1 int64
    var col1 float64
    var col2 string
    err := rows.Scan(&pk1, &col1, &col2)
    if err != nil {
        panic(err)    // Handle errors. 
    }
}

Complete sample code

The following sample code provides an example on how to query all data in the table named test_table in the myinstance instance in the China (Hangzhou) region:

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/aliyun/aliyun-tablestore-go-sql-driver"
)

func main() {
    db, err := sql.Open("ots", "https://************************:********************************@myinstance.cn-hangzhou.ots.aliyuncs.com/myinstance")
    if err != nil {
        panic(err)
    }
    rows, err := db.Query("SELECT * FROM test_table")
    if err != nil {
        panic(err)
    }
    for rows.Next() {
        // Specify that all columns of the rows that meet the query conditions are returned. 
        columns, err := rows.Columns()
        if err != nil {
            panic(err)
        }
        // Create an array and a pointer to store data. 
        values := make([]interface{}, len(columns))
        pointers := make([]interface{}, len(columns))
        for i := range values {
            pointers[i] = &values[i]
        }
        // Scan data rows. 
        err = rows.Scan(pointers...)
        if err != nil {
            panic(err)
        }
        fmt.Println(values...)
    }
    rows.Close()
    db.Close()
}

Configuration items

You can use the Tablestore driver for Go to modify the configuration items of Tablestore SDK for Go. The following table describes the common configuration items.

Data type mappings

The following table describes the data type mappings between the fields of Tablestore and the Tablestore driver for Go. If the data types of fields in Tablestore do not match the data types of fields in the Tablestore driver for Go, an error occurs.

References

• You can access Tablestore in the Tablestore console or Tablestore CLI, or by using Tablestore SDKs or Java Database Connectivity (JDBC). For more information, see Use the SQL query feature in the Tablestore console, Use the SQL query feature in the Tablestore CLI, Use Tablestore SDKs to use the SQL query feature, Use JDBC to access Tablestore, Use Hibernate to query data by executing SQL statements, and Use MyBatis to query data by executing SQL statements.

• If you want to accelerate data queries and computing by executing SQL statements, you can create a secondary index or a search index. For more information, see Index selection policy and Computing pushdown.

• You can also use computing engines, such as MaxCompute, Spark, Hive, HadoopMR, Function Compute, Flink, and PrestoDB, to compute and analyze data in tables. For more information, see Overview.

• To visualize data, you can use Grafana. For example, you can use Grafana to display Tablestore data in charts. For more information, see Connect Tablestore to Grafana.