This topic describes how to use the Tablestore driver for Go to access Tablestore.

Precautions

The SQL query feature is available in the China (Hangzhou), China (Shanghai), China (Beijing), China (Zhangjiakou), China (Shenzhen), Germany (Frankfurt), and Singapore regions.

Prerequisites

  • If you want to use a RAM user to perform operations, make sure that a RAM user is created and all SQL operation permissions are granted to the RAM user. You can configure "Action": "ots:SQL*" in the custom policy to grant all SQL operation permissions to the RAM user. For more information, see 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 Obtain an AccessKey pair.
  • A data table is created, and a mapping table is created for the data table. For more information, see Create a data table and Create mapping tables for tables.

Step 1: Install the Tablestore driver for Go

Run the following command to install the Tablestore driver for Go:
go get github.com/aliyun/aliyun-tablestore-go-sql-driver

Step 2: Use the Tablestore driver for Go to access Tablestore

The Tablestore driver for Go is implemented based on the database/sql/driver interface. After you import the Tablestore driver for Go package and database/sql, you can use database/sql to access Tablestore.

  • Parameters

    When you use the Tablestore driver for Go to access Tablestore, you must specify the name of the driver and the name of the Tablestore data source. The following table describes the parameters.

    Parameter Example Description
    driverName ots The name of the Tablestore driver for Go. The name is ots and cannot be changed.
    dataSourceName https://************************:********************************@myinstance.cn-hangzhou.ots.aliyuncs.com/myinstance The name of the Tablestore data source in the following format: schema://accessKeyId:accessKeySecret@endpoint/instanceName[?param1=value1&...&paramN=valueN]. The name contains the following fields:
    • schema: required. This field specifies the protocol that is used by the Tablestore driver. In most cases, this field is set to https.
    • accessKeyId:accessKeySecret: required. This field specifies the AccessKey ID and AccessKey secret of your Alibaba Cloud account or a RAM user.
    • endpoint: required. This field specifies the endpoint of the instance. For more information, see Endpoint.
    • instanceName: required. This field specifies the name of the instance.

    For more information about other configuration items, see Configuration items.

  • Sample code
    import (
        "database/sql"
        _ "github.com/aliyun/aliyun-tablestore-go-sql-driver"
    )
    
    // Specify the name of the Tablestore driver for Go and the name of the Tablestore data source. 
    db, err := sql.Open("ots", "https://access_key_id:access_key_secret@endpoint/instance_name")
    if err != nil {
        panic(err)    // Handle errors. 
    }

Step 3: Query data

The Tablestore driver for Go allows you to use the Query method to execute query statements and Prepare to create a statement to query data.

Notice The data types of fields in the query results must match the data types of fields in Tablestore. For more information about data type mappings, see Data type mappings.
  • Use the Query method to query data
    // Create a SQL statement based on your business requirements. The following sample code provides an example on how 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. 
        }
    }
  • Use Prepare to create a statement to query data
    // Create a SQL statement based on your business requirements. The following sample code provides an example on how 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 test_table table on the myinstance instance in the China (Hangzhou) region:

package main

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

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.
Configuration item Example Description
retryTimes 10 The allowed number of retries. Default value: 10.
connectionTimeout 15 The timeout period for connection setup. Default value: 15. Unit: seconds. The value of 0 specifies an indefinite period of time.
requestTimeout 30 The timeout period for sending the request. Default value: 30. Unit: seconds.
maxRetryTime 5 The maximum period of time during which retries are triggered. Default value: 5. Unit: seconds.
maxIdleConnections 2000 The maximum number of idle connections. Default value: 2000.

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.
Data type in Tablestore Data type in the Tablestore driver for Go
Integer int64
Binary []byte
String string
Double float64
Boolean bool