This topic describes how to 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 (Shenzhen),China (Hong Kong), Germany (Frankfurt), US (Virginia), India (Mumbai),and Singapore (Singapore) regions.
Prerequisites
- If you want to use a RAM user to perform operations, 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
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&...¶mN=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.
- 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
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
Data type in Tablestore | Data type in the Tablestore driver for Go |
---|---|
Integer | int64 |
Binary | []byte |
String | string |
Double | float64 |
Boolean | bool |