Tablestore:Use the Tablestore driver for Go to access Tablestore

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), Germany (Frankfurt), US (Virginia), India (Mumbai), Indonesia (Jakarta), SAU (Riyadh), and Singapore 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 Configure user permissions.

• 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

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 Endpoints. 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.

• 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

Data type mappings