DEV Community: shiyuhang0

Best PlanetScale alternative: TiDB Serverless

shiyuhang0 — Sat, 09 Mar 2024 04:20:41 +0000

PlanetScale is sunsetting their hobby plan, it is still a good product if you can afford at least $39 monthly. Otherwise, you may need an alternative as they will sleep all the databases in the hobby plan on April 8th, 2024.

Neon or supabase sounds good, but you should be very careful because you need to deal with the difference between MySQL and PostgreSQL. Here, I want to introduce TiDB Serverless for you, it may be the best choice for you at present.

MySQL compatible

PlanetScale is built on top of open-source Vitess, a database clustering system for horizontal scaling of MySQL.

TiDB Serverless is also fully MySQL compatible. Which means:

You can import the data from PlanetScale to TiDB Serverless smoothly without considering data types and other details.
You can still use your preferred driver or ORM. TiDB Serverless works with almost all popular MySQL drivers and ORMs.
You need not change any of your codes except the connection string. The connection to TiDB Serverless enforces TLS, but you need not worry about the TLS cert as TiDB Serverless's cert is signed from Let's encrypt, the same as PlanetScale.

True Serverless

PlanetScale advertises that they are serverless in the beginning. But it is strange that you need to choose CPU and memory when you start your PlanetScale journey.

TiDB Serverless provides true serverless for you. It will auto-scale based on your traffic, you only need to pay as you used. It also scales to zero when you don't use it, and It will be ready to serve in seconds when traffic income.

Features

TiDB Serverless provides the following for you, you can continue to use the branching that you are already familiar with in PlanetScale.

Data Branching
Point-in-time Restore
Vector Search
Serverless Driver
Data Service
CLI

There are more waiting for your exploration. get started with TiDB Serverles now!

Ecosystem

There are lots of integrations in TiDB Serverless. Here is a simple list:

Vercel
Cloudflare
Dbt
Terraform
Zapier
Prisma

You can find more here

Pricing

TiDB Serverless provides a more generous free quota for you. You can enjoy 25G storage and 50M Request Units(worth around $5) for every TiDB Serverless cluster and create up to 5 free clusters.

I believe you can also enjoy a free journey in TiDB Serverless If you are going to migrate from PlanetScale hobby plan. One thing you need to pay attention to is importing into TiDB Serverless may burn up your free quota if your data is too large.

According to my test, you may cost around $0.5 for importing one GB of data, which means you may need to pay if your data is larger than 10GB in a database. This may not be accurate, you can import your data to have a try. Don't worry about the fee, TiDB Serverless only limits your traffic instead of charging an extra fee when you exceed the free quota unless you add your credit card and set the spending limit.

As far as I know, TiDB Serverless is optimizing import costs and may charge less for importing in the future.

Migration

Want to have a try with TiDB Serverless? I find a migration guide for you.

Connect database from the edge

shiyuhang0 — Mon, 28 Aug 2023 05:17:56 +0000

What is edge

Cloud is not a new thing, you needn't worry about the infrastructure on the cloud. For example, if you want to deploy your code in us-west-2. You don't need to buy a machine and configure the network for it. Just pay for an EC2 in us-west-2 and deploy your code on that is all what you need to do. But wait, is this a real Cloud?

Why do I need to buy a EC2 first? Image that you can write and deploy your code directly without a server. Here comes the serverless.

Based on the serverless, why do I need to pay attention to where the codes are running? Image the code can not only run in us-west-2, but it also runs everywhere - closer to the code triggers. Here comes the edge.

Cloudflare Workers, Vercel Edge Function, and Netlify Edge Function all provide the edge, which allows you to implement low-latency HTTP API without any other infrastructure. And your code can run closer to your customer.

Edge is actually edge computing, which is a networking philosophy focused on bringing computing as close to the source of data as possible in order to reduce latency and bandwidth use.

Why it is hard to connect the database at the edge

Before talking about why it is hard. We'd better learn how the edge works.

There are two important parts of edge:

Edge network: Your code runs everywhere, which means your code will be copied into a bunch of nodes of a a global network (edge network).
Edge runtime: The runtime of your code. For example, Java runs on JVM, javascript runs on Node.

Whether you can connect to the database depends on the edge runtime.

provider	edge runtime
Cloudflare	Cloudflare Workers
Vercel	Cloudflare Workers
Netlify	Deno
Supbase	Deno

Cloudflare Workers is written in JavaScript, and executed using the V8 JavaScript engine (from Google Chrome). Deno also provides edge runtime beyond V8 JavaScript engine. The main reason why they don't choose to build on top of node.js lies in the security —— node is not designed to be a sandbox.

Thus, Cloudflare Workers and Deno don't support all the node.js APIs that are used by the database JavaScript drivers. Instead, their APIs are based on the Web Platform APIs (the APIs generally follow the specifications and should match the implementation in Chrome and Firefox). There is also WinterCG, which aims to use Web Platform APIs outside of browsers, namely on the server (Deno / Node.js) or edge runtimes (Cloudflare Workers / Deno).

In other words, we can not use database drivers directly at the edge with Cloudflare Workers or Deno.

Efforts from the database providers

Despite insufficient TCP connect support, edge runtimes usually have better support for WebSocket and HTTP. Thus, many cloud database providers use serverless driver or data service to connect to the database.

provider	connection method
planetscale	serverless driver (HTTP)
neon	serverless driver (HTTP and Websocket )
tidb serverless	serverless driver (HTTP) and data service
supbase	serverless driver (HTTP)
mongo	Realm Web SDK

Efforts from edge provider

The limitations of Data service or serverless driver is obvious. As a stateless protocol, HTTP performs differently from the traditional TCP way when connected to the database. For example, it is harder to provide transactions beyond HTTP.

Thus, I think the better way is to optimize in edge runtime. Let's see what edge providers have done for it.

Deno

Apart from the Web Platform APIs, Deno also provides global APIs to read from files, open TCP sockets, server HTTP, and execute subprocesses, etc. Based on these APIs, there are deno drivers, make it easy to connect to different databases from deno edge runtime.

What's more, Deno paid a lot to the node compatibility. Deno supports import node and npm modules, you can import and use node-mysql2 in deno, but node compatibility is not an easy thing. For example, errors occur when using TLS with node-mysql2. There is still a long way to go for it.

Cloudflare Workers

Cloudflare Workers also provide node compatibility with the compatibility_flags = [ "nodejs_compat" ] configuration. But it doesn't mean you can use all the node APIs. Database drivers still can not be used in Cloudflare Workers.

Recently, Cloudflare Workers provided socket API for creating TCP sockets. Meanwhile, They add the socket API in node-postgres, which means you can use this driver in Cloudflare Workers edge runtime directly.

Conclusion

This article introduces what's edge and why it is hard to connect to the database at the edge. The article also shows you the efforts of database providers and edge providers. Believe that connecting to the database at the edge will no longer be a hassle in the near future.

Reference

Access TiDB Cloud by Hashicorp Vault

shiyuhang0 — Tue, 17 Jan 2023 12:27:27 +0000

Hashicorp Vault can be used to manage database access.

As a MySQL-compatible Database. TiDB Cloud Serverless Tier can use the MySQL database Secrets Engine as a plugin to generate database credentials dynamically.

The following figure shows the workflow of using Vault's Dynamic Secrets. This article will demonstrate how to implement the workflow with TiDB Cloud.

Prerequest

Create a serverless tier on TiDB Cloud console
Install Vault

Starting the Server

Start a Vault server in development mode (dev server) with root as the root token

 vault server -dev -dev-root-token-id root

Launch a new terminal session and set environment variables:

export VAULT_ADDR='http://127.0.0.1:8200'

Verify the Server is Running:

➜  ~ vault status
Key             Value
---             -----
Seal Type       shamir
Initialized     true
Sealed          false
Total Shares    1
Threshold       1
Version         1.12.2
Build Date      2022-11-23T12:53:46Z
Storage Type    inmem
Cluster Name    vault-cluster-7368793c
Cluster ID      d892d1b1-f975-8bbb-cc73-369d1f48f3ad
HA Enabled      false

Configure secrets engine (admin role)

Launch a new terminal session and perform the admin role.

Export an environment variable to authenticate with the root token:

export VAULT_ADDR='http://127.0.0.1:8200'
export VAULT_TOKEN=root

Enable the database secrets engine:

vault secrets enable database

Configure Vault with the following command:

vault write database/config/my-tidb-database \
    plugin_name=mysql-database-plugin \
    connection_url="{{username}}:{{password}}@tcp(gateway01.us-east-1.prod.aws.tidbcloud.com:4000)/" \
    allowed_roles="readonly" \
    username="8kwxfAybggzi***.root" \
    password="***"\
    username_template="8kwxfAybggzi***.{{.RoleName | truncate 8}}_{{random 7}}" \
    tls_ca=@/etc/ssl/cert.pem

A templated connection_url is required when using root credential rotation.
An username_template is required because Serverless Tier's username must be started with 8kwxfAybggzi*** and no longer than 32. You can adjust it according to Vault official doc.
a tls_ca is required because Serverless Tier must be connected with TLS. You need to change the path according to your OS, here is a reference

Configure a role that maps a name in Vault to an SQL statement to execute to create the database credential. I create a user and grant SELECT privileges for read-only.

vault write database/roles/readonly \
    db_name=my-tidb-database \
    creation_statements="CREATE USER '{{name}}'@'%' IDENTIFIED BY '{{password}}';GRANT SELECT ON *.* TO '{{name}}'@'%';" \
    revocation_statements="DROP USER '{{name}}'@'%'; " \
    default_ttl="1h" \
    max_ttl="24h"

Create a readonly token (admin role)

Now, we need to create a token with a suitable policy for our customers.

create a readonly policy

➜  ~ vim readonly.hcl

# Get credentials from the database secrets engine 'readonly' role.
path "database/creds/readonly" {
  capabilities = [ "read" ]
}

write the policy:

vault policy write readonly-policy readonly.hcl

create a token with the policy

➜ ~ vault token create -policy=readonly-policy
Key                  Value
---                  -----
token                hvs.CAESIOMvEUz3UuoUyp_nv5YV5KdyzeSnowZp_UoBAzDh64VdGh4KHGh2cy5FNllaUWFRN1I4VnBNdDAyZFNjTkI1U0Q
token_accessor       4CZMNFZ8PNKUPsLZFQJ2dXvQ
token_duration       768h
token_renewable      true
token_policies       ["default" "readonly-policy"]
identity_policies    []
policies             ["default" "readonly-policy"]

Now, the admin can control the permission by giving the readonly token.

Use dynamic secrets (app role)

Launch a new terminal session and perform the app role.

Export an environment variable to authenticate with the readonly token:

export VAULT_ADDR='http://127.0.0.1:8200'
export VAULT_TOKEN=hvs.CAESIOMvEUz3UuoUyp_nv5YV5KdyzeSnowZp_UoBAzDh64VdGh4KHGh2cy5FNllaUWFRN1I4VnBNdDAyZFNjTkI1U0Q

Generate a new credential by reading from the /creds endpoint with the readonly role:

➜  ~ vault read database/creds/readonly
Key                Value
---                -----
lease_id           database/creds/readonly/3PNLHrgWyoRLg5QVWblDFiPP
lease_duration     1h
lease_renewable    true
password           DTuVZ-rvWlbMVG8ST***
username           8kwxfAybggzi***.readonly_MVmoAos

Now, you can use the password and username to connect to TiDB Cloud. And it will only have read permission.

Other operators such as creating a role or reading with another role will be denied.

➜  ~ vault write database/roles/readonly2 \
    db_name=my-tidb-database \
    creation_statements="CREATE USER '{{name}}'@'%' IDENTIFIED BY '{{password}}';GRANT SELECT ON *.* TO '{{name}}'@'%';" \
    default_ttl="1h" \
    max_ttl="24h"
Error writing data to database/roles/readonly2: Error making API request.

URL: PUT http://127.0.0.1:8200/v1/database/roles/readonly2
Code: 403. Errors:

* 1 error occurred:
    * permission denied

using root token to create a writeonly role:

vault write database/roles/writeonly \
    db_name=my-tidb-database \
    creation_statements="CREATE USER '{{name}}'@'%' IDENTIFIED BY '{{password}}';GRANT INSERT ON *.* TO '{{name}}'@'%';" \
    revocation_statements="DROP USER '{{name}}'@'%'; " \
    default_ttl="2m" \
    max_ttl="24h"

using readonly token to read the writeonly role:

➜  ~ vault read database/creds/writeonly
Error reading database/creds/writeonly: Error making API request.

URL: GET http://127.0.0.1:8200/v1/database/creds/writeonly
Code: 403. Errors:

* 1 error occurred:
    * permission denied

About the leases

The credential generated with the readonly role will expire after the 1h because readonly role's default_ttl is 1h.

For admin role:

One way to avoid the expiration of credential is to delete the revocation_statements when admin creates the readonly role.
admin can revoke the credential at any time.
admin can renew the credential before it expires. But can't renew more than readonly role's max_ttl.

For app role:

app role can ask for the renew permission from admin. Then app role can renew the credential.
Once the credential expires, app role needs to generate a new credential with the readonly token by vault read database/creds/readonly command. For example, use a cron job to reload the credential for each ttl.

Prospect

You can also try out TiDB Cloud Dedicated tier with Vault on the similar workflow.
TiDB Cloud Serverless tier may modify the mechanism of user management in the future. We may also provide a TiDB Cloud plugin in that time. Thus, this doc may not always work.

Use OpenAI and Zapier to perform blog moderation automatically - no code required

shiyuhang0 — Thu, 22 Dec 2022 09:48:52 +0000

Nowadays, there has been an increasing number of people building their own blogs. However, moderate blogs can be time-consuming and tedious.

Fortunately, Zapier integrates OpenAI 2 weeks ago. With it, you can automate the moderation process for your blog and ensure that only appropriate content is published.

Just imagine when you write your blog. Zapier and OpenAI will moderate it automatically. Then the blog will be published automatically if it passes the moderation. Otherwise, you will receive a notification email.

Let's see how I achieve this - no code required.

Set up your blog

Ignore this step if you have built your own blog site.

Don't worry if you don't have your own blog site. Here is a simple demo that you can build a blog site in several minutes.

You can deploy it with Vercel or Netlify to publish your blog site to the internet. I just build it locally for convenience.

This demo enables you to write a draft blog and then publish it. All blogs are stored in TiDB Cloud Serverless Tier, a free and MySQL-Compatible HTAP database service.

Use OpenAI and Zapier

Here is the Zap named Blog moderation I build.

New Row in TiDB Cloud will trigger the whole workflow once you create a new draft (a new row will be added in Serverless Tier's post table)
Check Moderation in OpenAI will check for hate, hate/threatening, self-harm, sexual, sexual/minors, violence, or violence/graphic content in your post.
Paths will perform different actions according to the result of moderation.

Click view you can see the details of the Paths:

if your post passed the moderation of OpenAI(Flagged is false), Zapier will perform Update Row in TiDB Cloud. It will update the published field in the Serverless tier's post table so that the draft will be published.

if your post doesn't pass the moderation of OpenAI(Flagged is true), Zapier will perform Send Email in Gmail. It will notify you by email with the moderation result.

Try the workflow

Now, let's try it. Click Create draft on your blog site and create two drafts.

Wait for a moment and you will find the first blog has been published, while the second blog is still a draft.

Check your email, you will find the notification.

Summary

As you can see. OpenAI and Zapier can help you be freed from the repetitive moderation work. And a Zap template is on the way to help you build such a workflow. You can also build by yourself, it isn't too difficult.

Hope the post will help you!

Make Deno MySQL driver works better

shiyuhang0 — Thu, 01 Dec 2022 11:07:09 +0000

Deno is a JavaScript and TypeScript runtime similar to Node.js, built on Rust and the V8 JavaScript engine.

Deno MySQL driver (also be called as deno_mysql) is a library that allows us to connect our deno application to SQL servers similar to MySQL or MariaDB.

However, deno_mysql is not fully compatible with mysql protocol, causing it not works with all MySQL versions and some MySQL-compatible database. such as TiDB.

In this post, I will introduce two compatible issues of the deno_mysql v2.10.3 and how to solve them.

Authentication Method Mismatch

Authentication Method Mismatch is a new feature of MySQL 8.0. It is a phase of connection which is used to prevent the downgrade attack of the authentication method. When the client and server use different authentication methods, the server will send an AuthSwitchRequest packet to the client. The client can then choose to use the authentication method that the server supports.

deno_mysql does not support the authentication method mismatch, so it may fail to connect to MySQL 8.0.x and other MySQL-compatible databases which trigger the mismatch.

switch (authResult) {
    case AuthResult.AuthMoreRequired:
          const adaptedPlugin = (authPlugin as any)[handshakePacket.authPluginName];
          handler = adaptedPlugin;
          break;
    case AuthResult.MethodMismatch:
          // TODO: Negotiate
          throw new Error("Currently cannot support auth method mismatch!");
}

Here is an example code to support it:

Parse AuthSwitchRequest
Send AuthSwitchResponse with authData
Do not allow to change the auth plugin more than once

case AuthResult.MethodMismatch:
    // 1. parse AuthSwitchRequest
    const authSwitch = parseAuthSwitch(receive.body); 
    // If CLIENT_PLUGIN_AUTH capability is not supported, no new cipher is sent and we have to keep using the cipher sent in the init packet. 
    if ( authSwitch.authPluginData === undefined || authSwitch.authPluginData.length === 0 ) { 
        authSwitch.authPluginData = handshakePacket.seed; 
    } 
    // 2. build authData
    let authData; 
    if (password) { 
        authData = auth(authSwitch.authPluginName, password, authSwitch.authPluginData); 
    } else { 
        authData = Uint8Array.from([]); 
    } 
    // 3. send AuthSwitchResponse with authData
    await new SendPacket(authData, receive.header.no + 1).send(this.conn); 
    // 4. do not allow to change the auth plugin more than once
    receive = await this.nextPacket(); 
    const authSwitch2 = parseAuthSwitch(receive.body); 
    if (authSwitch2.authPluginName !== "") { 
        throw new Error( "Do not allow to change the auth plugin more than once!"); 
    }

1. Parse AuthSwitchRequest

Once Auth Method Mismatch, server will send a AuthSwitchRequest to client. We need to parse the AuthSwitchRequest follow the payload:

Type	Name	Description
int<1>	0xFE (254)	status tag
string[NUL]	plugin name	name of the client authentication plugin to switch to
string[EOF]	plugin provided data	Initial authentication data for that client plugin

2. Send AuthSwitchResponse with authData

Client will select the authentication method that the server supports first. deno_mysql supports mysql_native_password and caching_sha2_password now. So, it will throw error if server does not support both.

Then client will encode password and plugin provided data with the authentication method. The result is authData.

At last, client need to send AuthSwitchResponse to server with the authData.

3. Change the auth plugin only once

Authentication method mismatch is not allowed to occur more than once. It is not a part of MySQL protocol. go-sql-driver also has the same rule.

CLIENT_DEPRECATE_EOF

MySQL deprecated EOF packet since MySQL v5.7.5 and CLIENT_DEPRECATE_EOF was introduced since then.

Once Client and Server both support CLIENT_DEPRECATE_EOF, server will:

Send OK packet rather than EOF packet in some cases.
Not send EOF packet in some cases.

deno_mysql does support the CLIENT_DEPRECATE_EOF flag. However, there are some problems of the implementation. it causes the deno_mysql not works with the latest mariadb and other MySQL-compatible databases which support CLIENT_DEPRECATE_EOF

1. Deprecated EOF packet without `CLIENT_DEPRECATE_EOF` flag

deno_mysql judge the EOF packet by the version of the server. For example, it will expect an EOF packet for the version less than 5.7.

if (this.lessThan5_7() || this.isMariaDBAndVersion10_0Or10_1()) {
    // EOF(less than 5.7 or mariadb version is 10.0 or 10.1)
    receive = await this.nextPacket();
    if (receive.type !== PacketType.EOF_Packet) {
        throw new ProtocolError();
    }
}

It is too hacker and not reliable. It may block some MySQL-compatible databases which do not follow the version rule. The best practice is to judge the EOF packet by the CLIENT_DEPRECATE_EOF flag. Here is an example:

if (!(this.capabilities & ServerCapabilities.CLIENT_DEPRECATE_EOF)) {
    receive = await this.nextPacket();
    if (receive.type !== PacketType.EOF_Packet) {
          throw new ProtocolError();
    }
}

2. EOF packet need to be replaced by OK packet

deno_mysql always expect an EOF packet after the result set.

if (!iterator) {
        while (true) {
          receive = await this.nextPacket();        
          if (receive.type === PacketType.EOF_Packet) {
            break;
          } else {
            const row = parseRow(receive.body, fields);
            rows.push(row);
          }
        }
        return { rows, fields };
      }

EOF packet's header is 0xFE, OK packet's header can be either 0x00 or 0xFE. When the client and server both support CLIENT_DEPRECATE_EOF, serve will send OK packet. It will not work once the server send OK packet with 0x00 header.

We need to support OK packet too:

        while (true) {
          receive = await this.nextPacket();
          // OK_Packet when CLIENT_DEPRECATE_EOF is set. OK_Packet can be 0xfe or 0x00
          if ( receive.type === PacketType.EOF_Packet ||receive.type === PacketType.OK_Packet ) {
            break;
          } else {
            const row = parseRow(receive.body, fields);
            rows.push(row);
          }
        }
        return { rows, fields };
      }

3. More things to do

There are more things to do if deno_mysql want to support CLIENT_DEPRECATE_EOF. For example, the prepared statement protocol was introduced in MySQL 4.1. deno_mysql need to handle the CLIENT_DEPRECATE_EOF once it support this ability.

In fact, there is no clear solution to support CLIENT_DEPRECATE_EOF. MariaDB and MySQL also behavior different with CLIENT_DEPRECATE_EOF flag. It is the main reason that some driver does not support CLIENT_DEPRECATE_EOF, for example, the go-sql-driver.

For deno_mysql, fully supporting CLIENT_DEPRECATE_EOF may be too far ahead.

Test with TiDB

TiDB is fully compatible with MySQL protocol. There is also a TiDB Cloud.

You may find deno_mysql <= v2.10.3 can't work with TiDB <= v6.3 and TiDB Cloud. The former because of the problem of CLIENT_DEPRECATE_EOF and the latter because of the unsupported of Authentication Method Mismatch.

Now, deno_mysql is able to work with all the versions of TiDB and the TiDB Cloud.

Manage TiDB Cloud with Terraform

shiyuhang0 — Tue, 20 Sep 2022 09:46:40 +0000

Introduce
Set up
Create an API key
Get TiDB Cloud provider
Config the provider
Get projectId with project Data Source
Get cluster spec info with cluster spec Data Source
Create a dedicated cluster with cluster resource
Change the dedicated cluster
Create a backup with backup resource
Create a restore task with restore resource
Importing the restore cluster
Destroy the dedicated cluster
Conclusion

Introduce

TiDB Cloud is a fully-managed Database-as-a-Service (DBaaS) that brings TiDB, an open-source Hybrid Transactional and Analytical Processing (HTAP) database, to your cloud. TiDB Cloud offers an easy way to deploy and manage databases to let you focus on your applications, not the complexities of the databases. You can create TiDB Cloud clusters to quickly build mission-critical applications on Google Cloud Platform (GCP) and Amazon Web Services (AWS).

Terraform is an infrastructure as code tool that lets you define both cloud and on-prem resources in human-readable configuration files that you can version, reuse, and share. You can then use a consistent workflow to provision and manage all of your infrastructure throughout its lifecycle.

You can use terraform to manage the TiDB Cloud with the provider terraform-provider-tidbcloud

This article will give an example to show how to use the terraform-provider-tidbcloud

Set up

TiDB Cloud provider has released to terraform registry. All you need to do is install terraform (>=1.0).

For Mac user, you can install it with Homebrew.

First, install the HashiCorp tap, a repository of all our Homebrew packages.

brew tap hashicorp/tap

Now, install Terraform with hashicorp/tap/terraform.

brew install hashicorp/tap/terraform

See official doc for other installation methods.

Create an API key

The TiDB Cloud API uses HTTP Digest Authentication. It protects your private key from being sent over the network.

However, terraform-provider-tidbcloud does not support managing API key now. So you need to create the API key in the console.

Turn to TiDB Cloud API doc for help if you meet any problems.

Get TiDB Cloud provider

Create a main.tf file:

terraform {
  required_providers {
    tidbcloud = {
      source = "tidbcloud/tidbcloud"
      version = "~> 0.0.1"
    }
  }
  required_version = ">= 1.0.0"
}

The source attribute defines the provider which will be downloaded from Terraform Registry by default
The version attribute is optional which defines the version of the provider, it will use the latest version by default
The required_version is optional which defines the version of the terraform, it will use the latest version by default

To get the TiDB Cloud provider, execute terraform init. It will download the provider from terraform registry

$ terraform init

Initializing the backend...

Initializing provider plugins...
- Reusing previous version of tidbcloud/tidbcloud from the dependency lock file
- Using previously-installed tidbcloud/tidbcloud v0.0.1

Terraform has been successfully initialized!

You may now begin working with Terraform. Try running "terraform plan" to see
any changes that are required for your infrastructure. All Terraform commands
should now work.

If you ever set or change modules or backend configuration for Terraform,
rerun this command to reinitialize your working directory. If you forget, other
commands will detect it and remind you to do so if necessary.

Config the provider

You need to config the provider like:

terraform {
  required_providers {
    tidbcloud = {
      source = "tidbcloud/tidbcloud"
      version = "~> 0.0.1"
    }
  }
  required_version = ">= 1.0.0"
}

provider "tidbcloud" {
  username = "fake_username"
  password = "fake_password"
}

username and password are the API key's public key and private key, you can also pass them with the environment:

export TIDBCLOUD_USERNAME = ${public_key}
export TIDBCLOUD_PASSWORD = ${private_key}

Now, you can use the tidbcloud provider!

Get projectId with project Data Source

Let us get all the projects by project data source first:

Use data block to define the data source of tidbcloud, it consists of the data source type and the data source name. In this example, data source type is tidbcloud_project and the name is example_project. The prefix of the type maps to the name of the provider.
Use output block to get the information, and expose information for other Terraform configurations to use. It is similar to return values in programming languages. See official doc for more detail

Besides, you can find all the supported configs for the data source and resource here

terraform {
  required_providers {
    tidbcloud = {
      source = "tidbcloud/tidbcloud"
      version = "~> 0.0.1"
    }
  }
  required_version = ">= 1.0.0"
}

provider "tidbcloud" {
  username = "fake_username"
  password = "fake_password"
}

data "tidbcloud_project" "example_project" {
  page      = 1
  page_size = 10
}

output "projects" {
  value = data.tidbcloud_project.example_project.items
}

Then you can apply the configuration with the terraform apply, you need to type yes at the confirmation prompt to proceed. Use terraform apply --auto-approve to skip the type.

$ terraform apply --auto-approve
data.tidbcloud_project.example_project: Reading...
data.tidbcloud_project.example_project: Read complete after 1s [id=just for test]

Changes to Outputs:
  + projects = [
      + {
          + cluster_count    = 0
          + create_timestamp = "1649154426"
          + id               = "1372813089191121286"
          + name             = "test1"
          + org_id           = "1372813089189921287"
          + user_count       = 1
        },
      + {
          + cluster_count    = 1
          + create_timestamp = "1640602740"
          + id               = "1372813089189561287"
          + name             = "default project"
          + org_id           = "1372813089189921287"
          + user_count       = 1
        },
    ]

You can apply this plan to save these new output values to the Terraform state, without changing any real infrastructure.

Apply complete! Resources: 0 added, 0 changed, 0 destroyed.

Outputs:

projects = tolist([
  {
    "cluster_count" = 0
    "create_timestamp" = "1649154426"
    "id" = "1372813089191121286"
    "name" = "test1"
    "org_id" = "1372813089189921287"
    "user_count" = 1
  },
  {
    "cluster_count" = 1
    "create_timestamp" = "1640602740"
    "id" = "1372813089189561287"
    "name" = "default project"
    "org_id" = "1372813089189921287"
    "user_count" = 1
  },
])

Now, you get all the available projects, copy one of the id you need. Here we use the default project's ID.

Get cluster spec info with cluster spec Data Source

Before creating a TiDB cluster, you may need to get the available config values (providers, regions, etc.) by cluster-spec Data Source:

data "tidbcloud_cluster_spec" "example_cluster_spec" {
}

output "cluster_spec" {
  value = data.tidbcloud_cluster_spec.example_cluster_spec.items
}

Execute the terraform apply --auto-approve, we will get all the specifications. Here we show a part of the results:

{
    "cloud_provider" = "AWS"
    "cluster_type" = "DEDICATED"
    "region" = "eu-central-1"
    "tidb" = tolist([
      {
        "node_quantity_range" = {
          "min" = 1
          "step" = 1
        }
        "node_size" = "2C8G"
      },
      {
        "node_quantity_range" = {
          "min" = 1
          "step" = 1
        }
        "node_size" = "4C16G"
      },
      {
        "node_quantity_range" = {
          "min" = 1
          "step" = 1
        }
        "node_size" = "8C16G"
      },
      {
        "node_quantity_range" = {
          "min" = 1
          "step" = 1
        }
        "node_size" = "16C32G"
      },
    ])
    "tiflash" = tolist([
      {
        "node_quantity_range" = {
          "min" = 0
          "step" = 1
        }
        "node_size" = "8C64G"
        "storage_size_gib_range" = {
          "max" = 2048
          "min" = 500
        }
      },
      {
        "node_quantity_range" = {
          "min" = 0
          "step" = 1
        }
        "node_size" = "16C128G"
        "storage_size_gib_range" = {
          "max" = 2048
          "min" = 500
        }
      },
    ])
    "tikv" = tolist([
      {
        "node_quantity_range" = {
          "min" = 3
          "step" = 3
        }
        "node_size" = "2C8G"
        "storage_size_gib_range" = {
          "max" = 500
          "min" = 200
        }
      },
      {
        "node_quantity_range" = {
          "min" = 3
          "step" = 3
        }
        "node_size" = "4C16G"
        "storage_size_gib_range" = {
          "max" = 2048
          "min" = 200
        }
      },
      {
        "node_quantity_range" = {
          "min" = 3
          "step" = 3
        }
        "node_size" = "8C32G"
        "storage_size_gib_range" = {
          "max" = 4096
          "min" = 500
        }
      },
      {
        "node_quantity_range" = {
          "min" = 3
          "step" = 3
        }
        "node_size" = "8C64G"
        "storage_size_gib_range" = {
          "max" = 4096
          "min" = 500
        }
      },
      {
        "node_quantity_range" = {
          "min" = 3
          "step" = 3
        }
        "node_size" = "16C64G"
        "storage_size_gib_range" = {
          "max" = 4096
          "min" = 500
        }
      },
    ])
  }

cloud_provider is the cloud provider on which your TiDB cluster is hosted
region is the region of cloud_provider
node_quantity_range shows the min quantity of the node and the step if you want to scale the node
node_size is the size of the node
storage_size_gib_range shows the min and max storage you can set to the node

Create a dedicated cluster with cluster resource

Make sure you have set a Project CIDR on TiDB Cloud console first.

Now, you can create a dedicated cluster with the projectId and the spec info:

Use resource block to define the resource of tidbcloud, it consists of the resource type and the resource name. In this example, resource type is tidbcloud_cluster and the name is example_cluster

Once again, you can find all the supported configs for the data source and resource here

Here I give an example for tidbcloud_cluster:

resource "tidbcloud_cluster" "example_cluster" {
  project_id     = "1372813089189561287"
  name           = "firstCluster"
  cluster_type   = "DEDICATED"
  cloud_provider = "AWS"
  region         = "eu-central-1"
  config = {
    root_password = "Your_root_password1."
    port = 4000
    components = {
      tidb = {
        node_size : "8C16G"
        node_quantity : 1
      }
      tikv = {
        node_size : "8C32G"
        storage_size_gib : 500,
        node_quantity : 3
      }
    }
  }
}

Execute terraform apply, it is not recommended to use terraform apply --auto-approve when you apply a resource.

$ terraform apply
data.tidbcloud_project.example_project: Reading...
data.tidbcloud_project.example_project: Read complete after 1s [id=just for test]

Terraform used the selected providers to generate the following execution plan. Resource actions are indicated with the following symbols:
  + create

Terraform will perform the following actions:

  # tidbcloud_cluster.example_cluster will be created
  + resource "tidbcloud_cluster" "example_cluster" {
      + cloud_provider = "AWS"
      + cluster_type   = "DEDICATED"
      + config         = {
          + components     = {
              + tidb = {
                  + node_quantity = 1
                  + node_size     = "8C16G"
                }
              + tikv = {
                  + node_quantity    = 3
                  + node_size        = "8C32G"
                  + storage_size_gib = 500
                }
            }
          + ip_access_list = [
              + {
                  + cidr        = "0.0.0.0/0"
                  + description = "all"
                },
            ]
          + port           = 4000
          + root_password  = "Your_root_password1."
        }
      + id             = (known after apply)
      + name           = "firstCluster"
      + project_id     = "1372813089189561287"
      + region         = "eu-central-1"
      + status         = (known after apply)
    }

Plan: 1 to add, 0 to change, 0 to destroy.

Do you want to perform these actions?
  Terraform will perform the actions described above.
  Only 'yes' will be accepted to approve.

  Enter a value:

Terraform will generate an execution plan for you:

You can check the diff between the configuration and the state
You can also see the results of this apply: it will add a new resource, and no resource will be changed or destroyed
The known after apply shows that you will get the value after apply

If everything is in your plan, type the yes to continue:

Do you want to perform these actions?
  Terraform will perform the actions described above.
  Only 'yes' will be accepted to approve.

  Enter a value: yes

tidbcloud_cluster.example_cluster: Creating...
tidbcloud_cluster.example_cluster: Creation complete after 1s [id=1379661944630234067]

Apply complete! Resources: 1 added, 0 changed, 0 destroyed.

Use terraform show or terraform state show tidbcloud_cluster.example_cluster to inspect the state of your resource. The former will show all the states (all the resources and the data source).

$ terraform state show tidbcloud_cluster.example_cluster

# tidbcloud_cluster.example_cluster:
resource "tidbcloud_cluster" "example_cluster" {
    cloud_provider = "AWS"
    cluster_type   = "DEDICATED"
    config         = {
        components     = {
            tidb = {
                node_quantity = 1
                node_size     = "8C16G"
            }
            tikv = {
                node_quantity    = 3
                node_size        = "8C32G"
                storage_size_gib = 500
            }
        }
        ip_access_list = [
            # (1 unchanged element hidden)
        ]
        port           = 4000
        root_password  = "Your_root_password1."
    }
    id             = "1379661944630234067"
    name           = "firstCluster"
    project_id     = "1372813089189561287"
    region         = "eu-central-1"
    status         = "CREATING"
}

The status of the cluster is CREATING, we need to wait until it changes to AVAILABLE, it usually takes 10+ minutes.

Once you want to check the status, execute terraform refresh to update the state, then use terraform state show tidbcloud_cluster.example_cluster to check the status.

$ terraform refresh

tidbcloud_cluster.example_cluster: Refreshing state... [id=1379661944630234067]

$ terraform state show tidbcloud_cluster.example_cluste

# tidbcloud_cluster.example_cluster:
resource "tidbcloud_cluster" "example_cluster" {
    cloud_provider = "AWS"
    cluster_type   = "DEDICATED"
    config         = {
        components     = {
            tidb = {
                node_quantity = 1
                node_size     = "8C16G"
            }
            tikv = {
                node_quantity    = 3
                node_size        = "8C32G"
                storage_size_gib = 500
            }
        }
        ip_access_list = [
            # (1 unchanged element hidden)
        ]
        port           = 4000
        root_password  = "Your_root_password1."
    }
    id             = "1379661944630234067"
    name           = "firstCluster"
    project_id     = "1372813089189561287"
    region         = "eu-central-1"
    status         = "AVAILABLE"
}

Congratulations! Your cluster is available now.

Change the dedicated cluster

We can also use terraform to manage the resource. As for cluster resource, we can:

Increase TiFlash component for the dedicated cluster
Scale the TiDB cluster
Pause or resume the cluster

Increase TiFlash component

First, add tiflash config in components:

    components = {
      tidb = {
        node_size : "8C16G"
        node_quantity : 1
      }
      tikv = {
        node_size : "8C32G"
        storage_size_gib : 500
        node_quantity : 3
      }
      tiflash = {
        node_size : "8C64G"
        storage_size_gib : 500
        node_quantity : 1
      }
    }

Then, execute terraform apply:

$ terraform apply

tidbcloud_cluster.example_cluster: Refreshing state... [id=1379661944630234067]

Terraform used the selected providers to generate the following execution plan. Resource actions are indicated with the following symbols:
  ~ update in-place

Terraform will perform the following actions:

  # tidbcloud_cluster.example_cluster will be updated in-place
  ~ resource "tidbcloud_cluster" "example_cluster" {
      ~ config         = {
          ~ components     = {
              + tiflash = {
                  + node_quantity    = 1
                  + node_size        = "8C64G"
                  + storage_size_gib = 500
                }
                # (2 unchanged attributes hidden)
            }
            # (3 unchanged attributes hidden)
        }
        id             = "1379661944630234067"
        name           = "firstCluster"
      ~ status         = "AVAILABLE" -> (known after apply)
        # (4 unchanged attributes hidden)
    }

Plan: 0 to add, 1 to change, 0 to destroy.

Do you want to perform these actions?
  Terraform will perform the actions described above.
  Only 'yes' will be accepted to approve.

  Enter a value:

Check the plan, you will find tiflash is added. And one resource will be changed after apply. Type yes:

  Enter a value: yes

tidbcloud_cluster.example_cluster: Modifying... [id=1379661944630234067]
tidbcloud_cluster.example_cluster: Modifications complete after 2s [id=1379661944630234067]

Apply complete! Resources: 0 added, 1 changed, 0 destroyed.

Use terraform state show tidbcloud_cluster.example_cluster to see the status:

$ terraform state show tidbcloud_cluster.example_cluster

# tidbcloud_cluster.example_cluster:
resource "tidbcloud_cluster" "example_cluster" {
    cloud_provider = "AWS"
    cluster_type   = "DEDICATED"
    config         = {
        components     = {
            tidb    = {
                node_quantity = 1
                node_size     = "8C16G"
            }
            tiflash = {
                node_quantity    = 1
                node_size        = "8C64G"
                storage_size_gib = 500
            }
            tikv    = {
                node_quantity    = 3
                node_size        = "8C32G"
                storage_size_gib = 500
            }
        }
        ip_access_list = [
            # (1 unchanged element hidden)
        ]
        port           = 4000
        root_password  = "Your_root_password1."
    }
    id             = "1379661944630234067"
    name           = "firstCluster"
    project_id     = "1372813089189561287"
    region         = "eu-central-1"
    status         = "MODIFYING"
}

The MODIFYING status shows the cluster is changing now. Wait for a moment, the status will be changed to AVAILABLE.

Scale the TiDB cluster

After the status is AVAILABLE, let us try to scale the TiDB cluster.

Add one node for TiDB and TiFlash, TiKV needs to add at least 3 nodes for its step is 3.

    components = {
      tidb = {
        node_size : "8C16G"
        node_quantity : 2
      }
      tikv = {
        node_size : "8C32G"
        storage_size_gib : 500
        node_quantity : 6
      }
      tiflash = {
        node_size : "8C64G"
        storage_size_gib : 500
        node_quantity : 2
      }
    }

Execute terraform apply and type yes after check:

$ terraform apply

tidbcloud_cluster.example_cluster: Refreshing state... [id=1379661944630234067]

Terraform used the selected providers to generate the following execution plan. Resource actions are indicated with the following symbols:
  ~ update in-place

Terraform will perform the following actions:

  # tidbcloud_cluster.example_cluster will be updated in-place
  ~ resource "tidbcloud_cluster" "example_cluster" {
      ~ config         = {
          ~ components     = {
              ~ tidb    = {
                  ~ node_quantity = 1 -> 2
                    # (1 unchanged attribute hidden)
                }
              ~ tiflash = {
                  ~ node_quantity    = 1 -> 2
                    # (2 unchanged attributes hidden)
                }
              ~ tikv    = {
                  ~ node_quantity    = 3 -> 6
                    # (2 unchanged attributes hidden)
                }
            }
            # (3 unchanged attributes hidden)
        }
        id             = "1379661944630234067"
        name           = "firstCluster"
      ~ status         = "AVAILABLE" -> (known after apply)
        # (4 unchanged attributes hidden)
    }

Plan: 0 to add, 1 to change, 0 to destroy.

Do you want to perform these actions?
  Terraform will perform the actions described above.
  Only 'yes' will be accepted to approve.

  Enter a value: yes

tidbcloud_cluster.example_cluster: Modifying... [id=1379661944630234067]
tidbcloud_cluster.example_cluster: Modifications complete after 2s [id=1379661944630234067]

Apply complete! Resources: 0 added, 1 changed, 0 destroyed.

Wait for the status from MODIFYING to AVAILABLE.

Pause or resume the cluster

The cluster can also be paused when the status is AVAILABLE or be resumed when the status is PAUSED.

set paused = true to pause the cluster
set paused = false to resume the cluster

config = {
    paused = true
    root_password = "Your_root_password1."
    port          = 4000
    ...
  }

execute terraform apply and type yes after check:

$ terraform apply

tidbcloud_cluster.example_cluster: Refreshing state... [id=1379661944630234067]

Terraform used the selected providers to generate the following execution plan. Resource actions are indicated with the following symbols:
  ~ update in-place

Terraform will perform the following actions:

  # tidbcloud_cluster.example_cluster will be updated in-place
  ~ resource "tidbcloud_cluster" "example_cluster" {
      ~ config         = {
          + paused         = true
            # (4 unchanged attributes hidden)
        }
        id             = "1379661944630234067"
        name           = "firstCluster"
      ~ status         = "AVAILABLE" -> (known after apply)
        # (4 unchanged attributes hidden)
    }

Plan: 0 to add, 1 to change, 0 to destroy.

Do you want to perform these actions?
  Terraform will perform the actions described above.
  Only 'yes' will be accepted to approve.

  Enter a value: yes

tidbcloud_cluster.example_cluster: Modifying... [id=1379661944630234067]
tidbcloud_cluster.example_cluster: Modifications complete after 2s [id=1379661944630234067]

Apply complete! Resources: 0 added, 1 changed, 0 destroyed.

Use the terraform state show tidbcloud_cluster.example_cluster to check the status:

$ terraform state show tidbcloud_cluster.example_cluster

# tidbcloud_cluster.example_cluster:
resource "tidbcloud_cluster" "example_cluster" {
    cloud_provider = "AWS"
    cluster_type   = "DEDICATED"
    config         = {
        components     = {
            tidb    = {
                node_quantity = 2
                node_size     = "8C16G"
            }
            tiflash = {
                node_quantity    = 2
                node_size        = "8C64G"
                storage_size_gib = 500
            }
            tikv    = {
                node_quantity    = 6
                node_size        = "8C32G"
                storage_size_gib = 500
            }
        }
        ip_access_list = [
            # (1 unchanged element hidden)
        ]
        paused         = true
        port           = 4000
        root_password  = "Your_root_password1."
    }
    id             = "1379661944630234067"
    name           = "firstCluster"
    project_id     = "1372813089189561287"
    region         = "eu-central-1"
    status         = "PAUSED"
}

Now, resume the cluster by set paused = false:

config = {
    paused = false
    root_password = "Your_root_password1."
    port          = 4000
    ...
  }

After apply you will find the status turns to RESUMING:

# tidbcloud_cluster.example_cluster:
resource "tidbcloud_cluster" "example_cluster" {
    cloud_provider = "AWS"
    cluster_type   = "DEDICATED"
    config         = {
        components     = {
            tidb    = {
                node_quantity = 2
                node_size     = "8C16G"
            }
            tiflash = {
                node_quantity    = 2
                node_size        = "8C64G"
                storage_size_gib = 500
            }
            tikv    = {
                node_quantity    = 6
                node_size        = "8C32G"
                storage_size_gib = 500
            }
        }
        ip_access_list = [
            # (1 unchanged element hidden)
        ]
        paused         = false
        port           = 4000
        root_password  = "Your_root_password1."
    }
    id             = "1379661944630234067"
    name           = "firstCluster"
    project_id     = "1372813089189561287"
    region         = "eu-central-1"
    status         = "RESUMING"
}

Wait for a moment, the status will be changed to AVAILABLE again.

Create a backup with backup resource

You have created and managed a dedicated cluster with terraform now.

Next, you will create a backup for the cluster by the backup resource.

First, copy the following config:

resource "tidbcloud_backup" "example_backup" {
  project_id  = "1372813089189561287"
  cluster_id  = "1379661944630234067"
  name        = "firstBackup"
  description = "create by terraform"
}

You can also get project_id and cluster_id from the cluster resource like:

resource "tidbcloud_backup" "example_backup" {
  project_id  = tidbcloud_cluster.example_cluster.project_id
  cluster_id  = tidbcloud_cluster.example_cluster.id
  name        = "firstBackup"
  description = "create by terraform"
}

Here we use the second config and execute terraform apply:

$ terraform apply

tidbcloud_cluster.example_cluster: Refreshing state... [id=1379661944630234067]

Terraform used the selected providers to generate the following execution plan. Resource actions are indicated with the following symbols:
  + create

Terraform will perform the following actions:

  # tidbcloud_backup.example_backup will be created
  + resource "tidbcloud_backup" "example_backup" {
      + cluster_id       = "1379661944630234067"
      + create_timestamp = (known after apply)
      + description      = "create by terraform"
      + id               = (known after apply)
      + name             = "firstBackup"
      + project_id       = "1372813089189561287"
      + size             = (known after apply)
      + status           = (known after apply)
      + type             = (known after apply)
    }

Plan: 1 to add, 0 to change, 0 to destroy.

Do you want to perform these actions?
  Terraform will perform the actions described above.
  Only 'yes' will be accepted to approve.

  Enter a value:

Type yes to create a backup:

  Enter a value: yes

tidbcloud_backup.example_backup: Creating...
tidbcloud_backup.example_backup: Creation complete after 2s [id=1350048]

Apply complete! Resources: 1 added, 0 changed, 0 destroyed.

Use terraform state show tidbcloud_backup.example_backup to check the state of the backup:

$ terraform state show tidbcloud_backup.example_backup

# tidbcloud_backup.example_backup:
resource "tidbcloud_backup" "example_backup" {
    cluster_id       = "1379661944630234067"
    create_timestamp = "2022-08-26T07:56:10Z"
    description      = "create by terraform"
    id               = "1350048"
    name             = "firstBackup"
    project_id       = "1372813089189561287"
    size             = "0"
    status           = "PENDING"
    type             = "MANUAL"
}

Wait for some minutes and use terraform refersh to update the states:

$ terraform refresh  
tidbcloud_cluster.example_cluster: Refreshing state... [id=1379661944630234067]
tidbcloud_backup.example_backup: Refreshing state... [id=1350048]
$ terraform state show tidbcloud_backup.example_backup
# tidbcloud_backup.example_backup:
resource "tidbcloud_backup" "example_backup" {
    cluster_id       = "1379661944630234067"
    create_timestamp = "2022-08-26T07:56:10Z"
    description      = "create by terraform"
    id               = "1350048"
    name             = "firstBackup"
    project_id       = "1372813089189561287"
    size             = "198775"
    status           = "SUCCESS"
    type             = "MANUAL"
}

Congratulations! You have create a backup for your cluster. Pay attention that the backup can not be updated.

Create a restore task with restore resource

You have created a dedicated cluster and have a backup of the cluster.

Now, it is time to create a restore task by restore resource. With it, you can restore a cluster according to a backup.

Here is the config for restore resource. Note that you can only restore data from a smaller node size to a larger node size:


resource "tidbcloud_restore" "example_restore" {
  project_id = tidbcloud_cluster.example_cluster.project_id
  backup_id  = tidbcloud_backup.example_backup.id
  name       = "restoreCluster"
  config = {
    root_password = "Your_root_password1."
    port          = 4000
    components = {
      tidb = {
        node_size : "8C16G"
        node_quantity : 2
      }
      tikv = {
        node_size : "8C32G"
        storage_size_gib : 500
        node_quantity : 6
      }
      tiflash = {
        node_size : "8C64G"
        storage_size_gib : 500
        node_quantity : 2
      }
    }
  }
}

Execute terraform apply and type yes:

$ terraform apply
tidbcloud_cluster.example_cluster: Refreshing state... [id=1379661944630234067]
tidbcloud_backup.example_backup: Refreshing state... [id=1350048]

Terraform used the selected providers to generate the following execution plan. Resource actions are indicated with the following symbols:
  + create

Terraform will perform the following actions:

  # tidbcloud_restore.example_restore will be created
  + resource "tidbcloud_restore" "example_restore" {
      + backup_id        = "1350048"
      + cluster          = {
          + id     = (known after apply)
          + name   = (known after apply)
          + status = (known after apply)
        }
      + cluster_id       = (known after apply)
      + config           = {
          + components    = {
              + tidb    = {
                  + node_quantity = 2
                  + node_size     = "8C16G"
                }
              + tiflash = {
                  + node_quantity    = 2
                  + node_size        = "8C64G"
                  + storage_size_gib = 500
                }
              + tikv    = {
                  + node_quantity    = 6
                  + node_size        = "8C32G"
                  + storage_size_gib = 500
                }
            }
          + port          = 4000
          + root_password = "Your_root_password1."
        }
      + create_timestamp = (known after apply)
      + error_message    = (known after apply)
      + id               = (known after apply)
      + name             = "restoreCluster"
      + project_id       = "1372813089189561287"
      + status           = (known after apply)
    }

Plan: 1 to add, 0 to change, 0 to destroy.

Do you want to perform these actions?
  Terraform will perform the actions described above.
  Only 'yes' will be accepted to approve.

  Enter a value: yes

tidbcloud_restore.example_restore: Creating...
tidbcloud_restore.example_restore: Creation complete after 1s [id=780114]

Apply complete! Resources: 1 added, 0 changed, 0 destroyed.

Check the state of the restore task with terraform state show tidbcloud_restore.example_restore:

$ terraform state show tidbcloud_restore.example_restore

# tidbcloud_restore.example_restore:
resource "tidbcloud_restore" "example_restore" {
    backup_id        = "1350048"
    cluster          = {
        id     = "1379661944630264072"
        name   = "restoreCluster"
        status = "INITIALIZING"
    }
    cluster_id       = "1379661944630234067"
    config           = {
        components    = {
            tidb    = {
                node_quantity = 2
                node_size     = "8C16G"
            }
            tiflash = {
                node_quantity    = 2
                node_size        = "8C64G"
                storage_size_gib = 500
            }
            tikv    = {
                node_quantity    = 6
                node_size        = "8C32G"
                storage_size_gib = 500
            }
        }
        port          = 4000
        root_password = "Your_root_password1."
    }
    create_timestamp = "2022-08-26T08:16:33Z"
    id               = "780114"
    name             = "restoreCluster"
    project_id       = "1372813089189561287"
    status           = "PENDING"
}

You can see the restore task's status is PENDING and the cluster's status is INITIALIZING.

After the cluster is AVAILABLE, the restore task will be RUNNING and turn to SUCCESS at last.

It is everything ok? No, the bad news is the restored cluster is not managed by terraform.

Don't worry, we can solve it in the next section.

Importing the restore cluster

We can manage a cluster with terraform by import even if it is not created by terraform.

Let us import the cluster which is created by the restore task in the last section.

First add a cluster resource like:

resource "tidbcloud_cluster" "restore_cluster1" {}

Then import the cluster by terraform import tidbcloud_cluster.restore_cluster1 projectId,clusterId, you can get the projectId and clusterId by restore resource:

$ terraform import tidbcloud_cluster.restore_cluster1 1372813089189561287,1379661944630264072

tidbcloud_cluster.restore_cluster1: Importing from ID "1372813089189561287,1379661944630264072"...
tidbcloud_cluster.restore_cluster1: Import prepared!
  Prepared tidbcloud_cluster for import
tidbcloud_cluster.restore_cluster1: Refreshing state... [id=1379661944630264072]

Import successful!

The resources that were imported are shown above. These resources are now in
your Terraform state and will henceforth be managed by Terraform.

Use terraform state show tidbcloud_cluster.restore_cluster1 to get the state of the cluster:

$ terraform state show tidbcloud_cluster.restore_cluster1

# tidbcloud_cluster.restore_cluster1:
resource "tidbcloud_cluster" "restore_cluster1" {
    cloud_provider = "AWS"
    cluster_type   = "DEDICATED"
    config         = {
        components = {
            tidb    = {
                node_quantity = 2
                node_size     = "8C16G"
            }
            tiflash = {
                node_quantity    = 2
                node_size        = "8C64G"
                storage_size_gib = 500
            }
            tikv    = {
                node_quantity    = 6
                node_size        = "8C32G"
                storage_size_gib = 500
            }
        }
        port       = 4000
    }
    id             = "1379661944630264072"
    name           = "restoreCluster"
    project_id     = "1372813089189561287"
    region         = "eu-central-1"
    status         = "AVAILABLE"
}

In order to manage it, you can copy it to your config file. Remember to delete the status and id, for they are computed by terraform and can not be set in the config:

resource "tidbcloud_cluster" "restore_cluster1" {
      cloud_provider = "AWS"
      cluster_type   = "DEDICATED"
      config         = {
          components = {
              tidb    = {
                  node_quantity = 2
                  node_size     = "8C16G"
              }
              tiflash = {
                  node_quantity    = 2
                  node_size        = "8C64G"
                  storage_size_gib = 500
              }
              tikv    = {
                  node_quantity    = 6
                  node_size        = "8C32G"
                  storage_size_gib = 500
              }
          }
          port       = 4000
      }
      name           = "restoreCluster"
      project_id     = "1372813089189561287"
      region         = "eu-central-1"
}

You can use terraform fmt to format your config file:

$ terraform fmt 

main.tf

To ensure the consistency of the config and state, you can execute terraform plan or terraform apply. If you see No changes, the import is successful.

$ terraform apply

tidbcloud_cluster.restore_cluster1: Refreshing state... [id=1379661944630264072]
tidbcloud_cluster.example_cluster: Refreshing state... [id=1379661944630234067]
tidbcloud_backup.example_backup: Refreshing state... [id=1350048]
tidbcloud_restore.example_restore: Refreshing state... [id=780114]

No changes. Your infrastructure matches the configuration.

Terraform has compared your real infrastructure against your configuration and found no differences, so no changes are needed.

Apply complete! Resources: 0 added, 0 changed, 0 destroyed.

Now you can manage the cluster created by the restore task.

Destroy the dedicated cluster

To destroy the resource, you can simply use terraform destroy and type yes. Don't worry about the order of deletion, terraform will generate a DAG based on the dependencies automatically.

$ terraform destroy

Plan: 0 to add, 0 to change, 4 to destroy.

Do you really want to destroy all resources?
  Terraform will destroy all your managed infrastructure, as shown above.
  There is no undo. Only 'yes' will be accepted to confirm.

  Enter a value: yes

tidbcloud_cluster.restore_cluster1: Destroying... [id=1379661944630264072]
tidbcloud_cluster.restore_cluster1: Destruction complete after 2s
tidbcloud_restore.example_restore: Destroying... [id=780114]
tidbcloud_restore.example_restore: Destruction complete after 0s
tidbcloud_backup.example_backup: Destroying... [id=1350048]
tidbcloud_backup.example_backup: Destruction complete after 2s
tidbcloud_cluster.example_cluster: Destroying... [id=1379661944630234067]
tidbcloud_cluster.example_cluster: Destruction complete after 0s
╷
│ Warning: Unsupported
│ 
│ restore can't be delete
╵

Destroy complete! Resources: 4 destroyed.

Note that a warning is appeared for restore can't be deleted.

If you execute terraform show, you will find nothing for all the states is cleared:

$ terraform show

Conclusion

This article use terraform-provider-tidbcloud to manage TiDB Cloud's cluster backup and restore task. The provider's capabilities are limited by the TiDB Cloud API (beta). Believe that the ability of the provider will be stronger when the API GA.

Understand TiSpark pushdown

shiyuhang0 — Tue, 06 Sep 2022 09:34:13 +0000

Author: shiyuhang

TiSpark is a thin layer built for running Apache Spark on top of TiKV/TiFlash to answer complex OLAP queries. It supports reading, writing, and deleting from TiKV/TiFlash with the guarantee of transactions.

In order to speed up reading, TiSpark will push some operators to TiKV or TiFlash. In this article, you will learn:

What is pushdown in Spark
How Spark implements pushdown
TiSpark pushdown strategy

What is pushdown in Spark?

Pushdown is a classic SQL optimization that can speed up SQL queries. It pushes some operators closer to the data source as much as possible to reduce the data that the upper layer needs to process. For example, predicate pushdown will push the where condition, and aggregation pushdown will push the aggregate function.

So, what is pushdown in Spark? Does it have the same meaning as above?

First of all, let us see the internal of Spark SQL. The core of Spark SQL is the catalyst, it will:

Parse Spark SQL to the unresolved logical plan
Apply analysis rules and catalog to get the resolved logical plan
Apply optimization rules to get the optimized logical plan
Use planning strategies to get the physical plan
Select one of the physical plans based on the cost model
Generate the executable RDDs and assign them to Spark Core

In the process of catalyst, Spark SQL will be parsed as a tree. The node of the tree is called TreeNode. Spark has several classes that inherit TreeNode to represent different types of nodes in the logical plan and physical plan. For example, where condition will be parsed as filter node.

Now, let 's talk about the pushdown in Spark. There are two steps of pushdown in Spark:

Pushdown Optimization: first, Spark will push the operators closer to the data source when it optimizes the logical plan.
Pushdown to data source: then Spark will push the operators to the data source when it builds the physical plan.

As an example, consider the following SQL:

select * from A join B on A.id = B.id where A.a>10 and B.b<100;

This SQL will be parsed as a tree in Spark. The filter is where condition, join is the join operator and scan is the data source（here it represents the table A and table B）

After the first step of pushdown, the filter will be closer to the data source to reduce the data which will be processed by join.

Then, the filter may be pushed to data source when building the physical plan. That is to say, Spark need not filter the data anymore.

How Spark implements pushdown

In the last section, you have learned that there are two steps pushdown in Spark. The first will optimize Spark SQL and the second will push to the data source.

In this section, you will learn how Spark implements the two pushdowns. The code in this section is based on Spark 3.2.

pushdown optimization

pushdown optimization will be applied to the logical plan in the optimizer phase of the catalyst. Take the predicate pushdown as an example.

The rule of predicate pushdown is called PushDownPredicates in Spark.

object PushDownPredicates extends Rule[LogicalPlan] with PredicateHelper {
  def apply(plan: LogicalPlan): LogicalPlan = plan transform {
    CombineFilters.applyLocally
      .orElse(PushPredicateThroughNonJoin.applyLocally)
      .orElse(PushPredicateThroughJoin.applyLocally)
  }
}

The rule will be recursively applied to the tree with the transform.

For every TreeNode in the plan:

CombineFilters will be used to combine where condition. For example, a > 1 and a >2 will be combined to a >2
PushPredicateThroughNonJoin will handle the predicate without join
PushPredicateThroughJoin will handle the predicate with join

You can refer to the Spark source code to see the details of PushPredicateThroughNonJoin and PushPredicateThroughJoin.

Pushdown to data source

Spark may pushdown to the data source when building the physical plan based on the results of the pushdown optimization.

Whether to pushdown depends on the ability and implementation of the data source. If your data source does not support pushdowns, then you need to tell Spark does not do that.

Thus, Spark provides some interface for data sources to communicate with Spark. We also take predicate pushdown as an example. Spark provides the following interface for it:

@Evolving
public interface SupportsPushDownFilters extends ScanBuilder {
  Filter[] pushFilters(Filter[] filters);
  Filter[] pushedFilters();
}

Filter[] pushFilters(Filter[] filters)：the input is the result of the pushdown optimization and the output is the filters that can't be pushed to data source, which are called postScanFilters in Spark.
Filter[] pushedFilters()：the input is empty and the output is the filters that can be pushed to data source which are called pushedFilters.
A filter can be both the postScanFilters and the pushedFilters. In this case, the data source and Spark will perform the filter together.

As a maintainer of Spark Data source connector, we can easily control the pushdown with the interface. But, how Spark applies the pushdown rules? There are two steps:

Keep the postScanFilters by the implements of the interface. Spark will handle them later.
Handle the pushedFilters with the scan operator. How it is handled depends on the implementation of the data source.

Keep the postScanFilters

The first step occurs in the optimization phase of the catalyst by V2ScanRelationPushDown. The core code is as follows (simplified):

object V2ScanRelationPushDown extends Rule[LogicalPlan] with PredicateHelper {
  import DataSourceV2Implicits._

  def apply(plan: LogicalPlan): LogicalPlan = {
    applyColumnPruning(pushDownAggregates(pushDownFilters(createScanBuilder(plan))))
  }

  private def pushDownFilters(plan: LogicalPlan) = plan.transform {
    case Filter(condition, sHolder: ScanBuilderHolder) =>
      val (pushedFilters, postScanFiltersWithoutSubquery) = PushDownUtils.pushFilters(
        sHolder.builder, normalizedFiltersWithoutSubquery)

      val filterCondition = postScanFilters.reduceLeftOption(And)
      filterCondition.map(Filter(_, sHolder)).getOrElse(sHolder)
  }

All of the pushdown rules are applied in the apply method, including pushDownFilters which is responsible for predicate pushdown.

pushDownFilters will get postScanFilters and pushedFilters by PushDownUtils and only return postScanFilters for the final logical plan. Spark will do the filters later.

The PushDownUtils code is as follows:

object PushDownUtils extends PredicateHelper {
  def pushFilters(
      scanBuilder: ScanBuilder,
      filters: Seq[Expression]): (Seq[sources.Filter], Seq[Expression]) = {
    scanBuilder match {
      case r: SupportsPushDownFilters =>
        val postScanFilters = r.pushFilters(translatedFilters.toArray).map { filter =>
          DataSourceStrategy.rebuildExpressionFromFilter(filter, translatedFilterToExpr)
        }
        (r.pushedFilters(), (untranslatableExprs ++ postScanFilters).toSeq)

      case _ => (Nil, filters)
    }
  }
}

PushDownUtils will match the SupportsPushDownFilters and get pushedFilters and postScanFilters by the implementation of the data source. pushedFilters will be empty once the implementation is empty, which means no predicate will be pushdown.

Handle the pushedFilters

Let us take JDBC data source as an example:

The build method in JDBCScanBuilder will return JDBCScan with the input pushedFilter.

override def build(): Scan = {
  JDBCScan(JDBCRelation(schema, parts, jdbcOptions)(session), finalSchema, pushedFilter,
    pushedAggregateList, pushedGroupByCols)
}

JDBCScan will call relation.buildScan in the toV1TableScan method and return JDBCRDD

case class JDBCScan(
    relation: JDBCRelation,
    prunedSchema: StructType,
    pushedFilters: Array[Filter],
    pushedAggregateColumn: Array[String] = Array(),
    groupByColumns: Option[Array[String]]) extends V1Scan {
  override def toV1TableScan[T <: BaseRelation with TableScan](context: SQLContext): T = {
    new BaseRelation with TableScan {
      override def buildScan(): RDD[Row] = {
        val columnList = if (groupByColumns.isEmpty) {
          prunedSchema.map(_.name).toArray
        } else {
          pushedAggregateColumn
        }
        relation.buildScan(columnList, prunedSchema, pushedFilters, groupByColumns)
      }
    }.asInstanceOf[T]
  }
}

Filter will be parsed as where condition and saved into filterWhereClause in JDBCRDD. Then a complete SQL with where condition will request the data sources which are compatible with the MySQL protocol.

private[jdbc] class JDBCRDD(
    sc: SparkContext,
    getConnection: () => Connection,
    schema: StructType,
    columns: Array[String],
    filters: Array[Filter],
    partitions: Array[Partition],
    url: String,
    options: JDBCOptions,
    groupByColumns: Option[Array[String]])
  extends RDD[InternalRow](sc, Nil) {

  private val filterWhereClause: String =
    filters
      .flatMap(JDBCRDD.compileFilter(_, JdbcDialects.get(url)))
      .map(p => s"($p)").mkString(" AND ")

}

Then the implementation of JDBC data source will be applied to Spark in the planner phase by DataSourceV2Strategy. The simplified core code is as follows:

class DataSourceV2Strategy(session: SparkSession) extends Strategy with PredicateHelper {

  override def apply(plan: LogicalPlan): Seq[SparkPlan] = plan match {
    case PhysicalOperation(project, filters,
        DataSourceV2ScanRelation(_, V1ScanWrapper(scan, pushed, aggregate), output)) =>
      val v1Relation = scan.toV1TableScan[BaseRelation with TableScan](session.sqlContext)
      val rdd = v1Relation.buildScan()
      val unsafeRowRDD = DataSourceStrategy.toCatalystRDD(v1Relation, output, rdd)
      val dsScan = RowDataSourceScanExec(
        output,
        output.toStructType,
        Set.empty,
        pushed.toSet,
        aggregate,
        unsafeRowRDD,
        v1Relation,
        tableIdentifier = None)
      withProjectAndFilter(project, filters, dsScan, needsUnsafeConversion = false)

    case PhysicalOperation(project, filters, DataSourceV2ScanRelation(_, scan: LocalScan, output))

    case PhysicalOperation(project, filters, relation: DataSourceV2ScanRelation)

    case PhysicalOperation(p, f, r: StreamingDataSourceV2Relation)

    case PhysicalOperation(p, f, r: StreamingDataSourceV2Relation)

Spark will match PhysicalOperation in the strategy. As for the JDBC data source, it will go into the first case because it is a V1Scan.
In the first case, scan.toV1TableScan is called to get the JDBCRDD which is introduced above. Then RowDataSourceScanExec will perform the data fetch with the JDBCRDD. Finally, withProjectAndFilter will put RowDataSourceScanExec into the entire physical plan. Spark will first execute the scan with filter and then execute the other operators.

TiSpark pushdown strategy

The strategy in TiSpark

TiSpark is a connector of Spark that provides TiKV data source. So, we can push some operators from Spark to TiKV according to the discussion above.

The question is: what is the strategy of pushdown in TiSpark?

We need to support pushdown in Spark >= 2.4 and meet the following strategies.

First, an operator can not be pushed down when the data source can not handle the operator. For predicate, TiKV can not support every expression and data type. So, TiSpark needs to exclude them automatically.

Second, we may not want to pushdown to reduce the pressure of TiKV. It will help when your Spark resources are abundant and TiKV resources are scarce. TiSpark provides some configs for this:

spark.tispark.plan.allow_agg_pushdown: you can refuse the aggregation pushdown by this config.
spark.tispark.plan.unsupported_pushdown_exprs: you can specify unsupported expressions to refuse them to pushdown. It will also help you work with the old version of TiKV which may not support some of the expressions.

How TiSpark implements the strategy

Next, we will learn how TiSpark implements the strategy.

We have introduced the pushdown interface provided by Spark. However, it can not meet the strategy of TiSpark. Here are some questions about the pushdown interface:

Poor expansion ability: The design of the pushdown interface is not good enough to expand in Data Source API V1 (DSV1). This means it is difficult to support various pushdowns in DSV1.
The limited ability of pushdown: DSV2 improves the pushdown interface and solves the expansion issues. But the ability of pushdown is limited. Spark 3.0 only supports predicate pushdown and column prune pushdown. Spark 3.1 add the aggregation pushdown and Spark 3.2 add the limit pushdown.
Inflexible pushdown strategy: For example, aggregation pushdown does not support push avg which can be converted as sum/count in DSV2

TiSpark needs support common pushdowns in every supported spark version. Thus, the pushdown interface is not suitable. What to do next? The answer lies in catalyst extension.

The catalyst extension is supported after Spark 2.2. We can inject custom rules and strategies into most of the phases of the catalyst. In other words, we can inject TiSpark pushdown strategies to control the pushdown precisely.

Spark will pushdown to the data source in the planner phase. The corresponding extension interface is injectPlannerStrategy（based on Spark 3.2.1 and TiSpark 3.0.1）

def injectPlannerStrategy(builder: StrategyBuilder): Unit = {
  plannerStrategyBuilders += builder
}

TiSpark needs to implement the interface:

ReflectionUtil will return the TiStrategy according to the different spark versions by reflection in scala. This can avoid compatibility issues caused by different spark versions.

e.injectPlannerStrategy(new TiStrategyFactory(getOrCreateTiContext))

class TiStrategyFactory(getOrCreateTiContext: SparkSession => TiContext)
    extends (SparkSession => Strategy) {
  override def apply(sparkSession: SparkSession): Strategy = {
    TiExtensions.validateCatalog(sparkSession)
    ReflectionUtil.newTiStrategy(getOrCreateTiContext, sparkSession)
  }
}

TiStrategy is the core of pushdowns. It will match the TiDBtable which represents the TiDB data source and then execute thedoPlan. If the match fails, TiSpark will do nothing to avoid affecting other data sources.

ase class TiStrategy(getOrCreateTiContext: SparkSession => TiContext)(sparkSession: SparkSession)
    extends Strategy with Logging {

  override def apply(plan: LogicalPlan): Seq[SparkPlan] = {
    plan
      .collectFirst {
        case DataSourceV2ScanRelation(DataSourceV2Relation(table: TiDBTable, _, _, _, _), _, _) =>
          doPlan(table, plan)
      }
      .toSeq
      .flatten
  }

The strategies in doPlan imitate the strategies in DataSourceV2Strategy. TiSpark will identify the operators which can be pushed to the data source with pattern match. Then request TiKV based on these operators. Let us take predicate pushdown as an example. TiSpark will match the PhysicalOperation and execute the pruneFilterProject method.

private def doPlan(source: TiDBTable, plan: LogicalPlan): Seq[SparkPlan] =
  plan match {
    case PhysicalOperation(
          projectList,
          filters,
          DataSourceV2ScanRelation(
            DataSourceV2Relation(source: TiDBTable, _, _, _, _),
            _,
            _)) =>
      pruneFilterProject(projectList, filters, source, newTiDAGRequest()) :: Nil

    case TiAggregation(
          groupingExpressions,
          aggregateExpressions,
          resultExpressions,
          TiAggregationProjectionV2(filters, _, `source`, projects))
        if isValidAggregates(groupingExpressions, aggregateExpressions, filters, source) =>
    case _ => Nil
  }

The pruneFilterProject method will:

Convert Spark Filter expression to TiKV Filter expression with TiExprUtils.isSupportedFilter. TiSpark will also judge if the expression can be pushed in the method. The Filters that can be pushed will be put into pushdownFilters, and those that cannot be pushed will be put into the residualFilter
The DAGRequest, which is the parameter in the request to TiKV will be built by filterToDAGRequest. The pushdownFilters will be put into the DAGRequest. Then, A scan that can get TiKV data will be generated by toCoprocessorRDD
The scan will be wrapped and executed by FilterExec. Meanwhile, we need to apply the residualFilter back to the Spark.

private def pruneFilterProject(
    projectList: Seq[NamedExpression],
    filterPredicates: Seq[Expression],
    source: TiDBTable,
    dagRequest: TiDAGRequest): SparkPlan = {

  val (pushdownFilters: Seq[Expression], residualFilters: Seq[Expression]) =
    filterPredicates.partition((expression: Expression) =>
      TiExprUtils.isSupportedFilter(expression, source, blocklist))

  val residualFilter: Option[Expression] =
    residualFilters.reduceLeftOption(catalyst.expressions.And)

  filterToDAGRequest(tiColumns, pushdownFilters, source, dagRequest)

  val scan = toCoprocessorRDD(source, projectSeq, dagRequest)
  residualFilter.fold(scan)(FilterExec(_, scan))
}

In this way, TiSpark can support most of the pushdown in every spark version (>=2.4).

So far, TiSpark supports predicate pushdown, aggregation pushdown, limit pushdown, order by pushdown, and projection pushdown. And TiSpark can control whether a specific expression or data type can be pushed down.

Conclusion

TiSpark support pushdown by catalyst extension which brings several problems:

The increase in code complexity
Unstable for we may touch the evolving interface or method in Spark
We need to be very careful to avoid affecting the original Spark logical

Spark is focusing on the development of DataSource API as well as the pushdown interface. Hope that in the near future DataSource API will be strong enough to meet the needs of TiSpark. At that time, we will be happy to transfer pushdown to DataSource API.

Appendix

The support pushdown in TiSpark is as follows


Data Type	sum	count	avg	min	max	predicate & order by & group by
BIT	❌	✅	❌	✅	✅	❌
BOOLEAN	✅	✅	✅	✅	✅	✅
TINYINT	✅	✅	✅	✅	✅	✅
SMALLINT	✅	✅	✅	✅	✅	✅
MEDIUMINT	✅	✅	✅	✅	✅	✅
INTEGER	✅	✅	✅	✅	✅	✅
BIGINT	✅	✅	✅	✅	✅	✅
FLOAT	❌	✅	✅	✅	✅	✅
DOUBLE	✅	✅	✅	✅	✅	✅
DECIMAL	✅	✅	✅	✅	✅	✅
DATE	❌	✅	❌	✅	✅	✅
DATETIME	❌	✅	❌	✅	✅	✅
TIMESTAMP	❌	✅	❌	✅	✅	✅
TIME	❌	✅	❌	？	？	✅
YEAR	✅	✅	✅	✅	✅	✅
CHAR	❌	✅	❌	✅	✅	✅
VARCHAR	❌	✅	❌	✅	✅	✅
TINYTEXT	❌	✅	❌	✅	✅	✅
TEXT	❌	✅	❌	✅	✅	✅
MEDIUMTEXT	❌	✅	❌	✅	✅	✅
LONGTEXT	❌	✅	❌	✅	✅	✅
BINARY	❌	✅	❌	✅	✅	✅
VARBINARY	❌	✅	❌	✅	✅	✅
TINYBLOB	❌	✅	❌	✅	✅	❌
BLOB	❌	✅	❌	✅	✅	❌
MEDIUMBLOB	❌	✅	❌	✅	✅	❌
LONGBLOB	❌	✅	❌	✅	✅	❌
ENUM	❌	✅	❌	✅	✅	❌
SET	❌	❌	❌	？	？	❌

Pushdown min/max(time) leads to a wrong result

Pushdown min/max(set) may cause TiKV panic

You can judge if an operator is pushdown by explain in TiSpark. Here is an example:

1.Create a table in TiDB

CREATE TABLE `test`.`t`  (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
);

2.Execute Spark SQL with explan

spark.sql("select avg(id) from test.t where id > 10").explain

3.You will get the execute plan

*(2) HashAggregate(keys=[], functions=[specialsum(specialsum(id#252L, DecimalType(38,0), null)#258, DecimalType(38,0), null), specialsum(count(id#252L)#259L, LongType, 0)])
+- Exchange SinglePartition, true, [id=#38]
   +- *(1) HashAggregate(keys=[], functions=[partial_specialsum(specialsum(id#252L, DecimalType(38,0), null)#258, DecimalType(38,0), null), partial_specialsum(count(id#252L)#259L, LongType, 0)])
      +- *(1) ColumnarToRow
         +- TiKV CoprocessorRDD{[table: t] IndexReader, Columns: id@LONG: { IndexRangeScan(Index:primary(id)): { RangeFilter: [[id@LONG GREATER_THAN 10]], Range: [([t\200\000\000\000\000\000\000o_i\200\000\000\000\000\000\000\001\003\200\000\000\000\000\000\000\v], [t\200\000\000\000\000\000\000o_i\200\000\000\000\000\000\000\001\372])] }, Aggregates: Sum(id@LONG), Count(id@LONG) }, startTs: 434873744501506049}

Focus on TiKV CoprocessorRDD

RangeFilter: [[id@LONG GREATER_THAN 10]]: indicates that id>10 is pushed down
Aggregates: Sum(id@LONG), Count(id@LONG): indicates that Sum and Count are pushed down, they will be rewritten into avg in Spark.

Data Transformation on TiDB Made Easier

shiyuhang0 — Thu, 01 Sep 2022 04:36:01 +0000

Author: Wu Qiang

Data build tool (dbt) is a popular open-source data transformation tool that enables analytics engineers to transform data in their warehouses through SQL statements. The TiDB community recently released the dbt-tidb adapter to make TiDB, a distributed SQL database to work with dbt. Through the dbt-tidb plug-in, analytics engineers working with TiDB can directly create forms and match data through SQL without having to think about the process of creating tables or views. They can also use Jinja, a dbt template language for writing SQL, test, package management, and other functions, which greatly improves efficiency.

In this tutorial, I will show you how to use dbt with TiDB. Before you try any of the steps below, make sure the following items are installed:

TiDB 5.3 or later
dbt 1.01 or later
dbt-tidb 1.0.0

Installation

There are several ways you can install dbt and dbt-tidb, In this tutorial, we will use pypi. When you install dbt-tidb, dbt is installed as a dependency. So you only need one command to install both:

$ pip install dbt-tidb

You can also install dbt separately. Please refer to How to install dbt in the dbt documentation.

Creating the project: jaffle shop

dbt-lab provides a project, jaffle_shop, to demonstrate dbt’s functionality. You can get the project directly from GitHub:

$ git clone https://github.com/dbt-labs/jaffle_shop
$ cd jaffle_shop

All files in the jaffle_shop project directory are structured as follows.

ubuntu@ubuntu:~/jaffle_shop$ tree
.
├── dbt_project.yml
├── etc
│ ├── dbdiagram_definition.txt
│ └── jaffle_shop_erd.png
├── LICENSE
├── models
│ ├── customers.sql
│ ├── docs.md
│ ├── orders.sql
│ ├── overview.md
│ ├── schema.yml
│ └── staging
│ ├── schema.yml
│ ├── stg_customers.sql
│ ├── stg_orders.sql
│ └── stg_payments.sql
├── README.md
└── seeds
├── raw_customers.csv
├── raw_orders.csv
└── raw_payments.csv

dbt_project.yml is the dbt project configuration file, which holds the project name and database configuration file information.
The models directory contains the project’s SQL models and table schemas. Note that the data analyst at your company writes this section. To learn more about models, see dbt Docs.
The seed directory stores CSV files that are dumped from database export tools. For example, TiDB can export the table data into CSV files through Dumpling. In the jaffle shop project, these CSV files are used as raw data to be processed.

Configuring the project

To configure the project:

1.Complete the global configuration. In the user directory, edit the default global profile, ~/.dbt/profiles.yml to configure the connection with TiDB:

$ vi ~/.dbt/profiles.yml
jaffle_shop_tidb:           # project name
target: dev                # target
outputs:
   dev:
      type: tidb           # adapter type
      server: 127.0.0.1
      port: 4000
      schema: analytics    # database name
      username: root
      password: ""

2.Complete the project configuration.
In the jaffle_shop project directory, enter the project configuration file dbt_project.yml and change the profile field to jaffle_shop_tidb. This configuration allows the project to query from the database as specified in the ~/.dbt/profiles.yml file.

$ cat dbt_project.yml
name: 'jaffle_shop'

config-version: 2
version: '0.1'

profile: 'jaffle_shop_tidb'      # note the modification here

model-paths: ["models"]          # model path
seed-paths: ["seeds"]            # seed path
test-paths: ["tests"] 
analysis-paths: ["analysis"]
macro-paths: ["macros"]

target-path: "target"
clean-targets:
- "target"
- "dbt_modules"
- "logs"

require-dbt-version: [">=1.0.0", "<2.0.0"]

models:
jaffle_shop:
materialized: table # *.sql which in models/ would be materialized to table
staging: 
materialized: view # *.sql which in models/staging/ would bt materialized to view

3.Verify the configuration.
Run the following command to check whether the database and project configuration are correct:

$ dbt debug

Loading CSV files

Now that you have successfully created and configured the project, it’s time to load the CSV data and materialize the CSV as a table in the target database. Note that this step is not generally required for a dbt project because the data items for processing are already in the database.

1.Load the CSV files by running the following command:

$ dbt seed

This displays the following:

Running with dbt=1.0.1
Partial parse save file not found. Starting full parse.
Found 5 models, 20 tests, 0 snapshots, 0 analyses, 172 macros, 0 operations, 3 seed files, 0 sources, 0 exposures, 0 metrics

Concurrency: 1 threads (target='dev')

1 of 3 START seed file analytics.raw_customers.................................. [RUN]
1 of 3 OK loaded seed file analytics.raw_customers.............................. [INSERT 100 in 0.19s]
2 of 3 START seed file analytics.raw_orders..................................... [RUN]
2 of 3 OK loaded seed file analytics.raw_orders................................. [INSERT 99 in 0.14s]
3 of 3 START seed file analytics.raw_payments................................... [RUN]
3 of 3 OK loaded seed file analytics.raw_payments............................... [INSERT 113 in 0.24s]

As you can see in the results, the seed file was started and loaded into three tables: analytics.raw_customers, analytics.raw_orders, and analytics.raw_payments.

2.Verify the results in TiDB. The show databases command lists the new analytics database that dbt created. The show tables command indicates that there are three tables in the analytics database, corresponding to the ones we created above.

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| INFORMATION_SCHEMA |
| METRICS_SCHEMA |
| PERFORMANCE_SCHEMA |
| analytics |
| mysql |
| test |
+--------------------+
6 rows in set (0.00 sec)

mysql> show tables;
+---------------------+
| Tables_in_analytics |
+---------------------+
| raw_customers |
| raw_orders |
| raw_payments |
+---------------------+
3 rows in set (0.00 sec)

Running the dbt project

Now you are ready to run the configured projects and finish the data transformation.

1.Run the dbt project to finish the data transformation:

$ dbt run
Running with dbt=1.0.1
Unable to do partial parsing because profile has changed
Unable to do partial parsing because a project dependency has been added
Found 5 models, 20 tests, 0 snapshots, 0 analyses, 172 macros, 0 operations, 3 seed files, 0 sources, 0 exposures, 0 metrics
Concurrency: 1 threads (target='dev')
1 of 5 START view model analytics.stg_customers................................. [RUN]
1 of 5 OK created view model analytics.stg_customers............................ [SUCCESS 0 in 0.12s]
2 of 5 START view model analytics.stg_orders.................................... [RUN]
2 of 5 OK created view model analytics.stg_orders............................... [SUCCESS 0 in 0.08s]
3 of 5 START view model analytics.stg_payments.................................. [RUN]
3 of 5 OK created view model analytics.stg_payments............................. [SUCCESS 0 in 0.07s]
4 of 5 START table model analytics.customers.................................... [RUN]
4 of 5 OK created table model analytics.customers............................... [SUCCESS 0 in 0.16s]
5 of 5 START table model analytics.orders....................................... [RUN]
5 of 5 OK created table model analytics.orders.................................. [SUCCESS 0 in 0.12s]

The result shows three views (analytics.stg_customers, analytics.stg_orders, and analytics.stg_payments) and two tables (analytics.customers and analytics.orders) were created successfully.

2.Go to the TiDB database to verify that the operation is successful.

mysql> show tables;
+---------------------+
| Tables_in_analytics |
+---------------------+
| customers     |
| orders        |
| raw_customers |
| raw_orders    |
| raw_payments  |
| stg_customers |
| stg_orders.   |
| stg_payments  |
+---------------------+
8 rows in set (0.00 sec)

mysql> select * from customers;
+-------------+------------+-----------+-------------+-------------------+------------------+-------------------------+
| customer_id | first_name | last_name | first_order | most_recent_order | number_of_orders | customer_lifetime_value |
+-------------+------------+-----------+-------------+-------------------+------------------+-------------------------+
| 1 | Michael   | P. | 2018-01-01 | 2018-02-10 | 2 | 33.0000 |
| 2 | Shawn     | M. | 2018-01-11 | 2018-01-11 | 1 | 23.0000 |
| 3 | Kathleen  | P. | 2018-01-02 | 2018-03-11 | 3 | 65.0000 |
| 4 | Jimmy     | C. | NULL | NULL | NULL | NULL |
| 5 | Katherine | R. | NULL | NULL | NULL | NULL |
| 6 | Sarah     | R. | 2018-02-19 | 2018-02-19 | 1 | 8.0000 |
| 7 | Martin    | M. | 2018-01-14 | 2018-01-14 | 1 | 26.0000 |
| 8 | Frank     | R. | 2018-01-29 | 2018-03-12 | 2 | 45.0000 |

The output shows that five more tables or views have been added, and the data in the tables or views has been transformed. Note that only part of the data from the customer table is shown here.

Generating visual documents

dbt lets you generate visual documents that display the overall structure of the project and describe all the tables and views. To generate visual documents:

1.Generate the document:

$ dbt docs generate

2.Start the server:

$ dbt docs serve 
Running with dbt=1.0.1
Serving docs at 0.0.0.0:8080

3.To access the document view from your browser, navigate to http://localhost:8080.

Conclusion

Currently, TiDB supports dbt in TiDB 4.0 and later versions. Earlier versions of TiDB may run into issues when working with dbt. For details, visit the tidb-dbt project on GitHub. To get the most out of dbt, we recommend that you run TiDB 5.3 or later. These versions support all of dbt’s functions.

If you run into issues, feel free to join our community on Slack or file an issue on our repository.

How to upgrade your dbt adapter

shiyuhang0 — Wed, 31 Aug 2022 09:47:03 +0000

Author: shiyuhang

Recently, we published a new release of dbt-tidb, an adapter that enables dbt to work with TiDB.

Here, I will introduce some experience of how to upgrade a dbt adapter to support new dbt-core. For how to build a new adapter, please refer to the official doc.

Version Rules

dbt-core follows the Semantic Versioning. So I suggest you also use Semantic Versioning when you publish your own adapter.

Although dbt-core will try to avoid break changes, compatibility issues still occur. For example, dbt-core v1.2.0 has added retry_connection method in BaseConnectionManagerto support the retry connection feature. If the adapter implements this method, it can't run on dbt-core v1.1.0.

In order to avoid compatibility issues, dbt-tidb will follow the version number of dbt-core. For example, dbt-tidb v1.2.0 will only support dbt-core v1.2.0. I suggest you do the same for your adapter.

Investigation

When we support the new dbt-core, the first step is to investigate which features need to be supported.

Here are a few investigation methods you can use in combination:

Visit the official Version migration guides, which may include suggestions for adapter maintainers.
Check out the dbt-core release note, focusing on new features for adapters. Sort out the new features that need to be implemented.
Sometimes, the dbt official will list TODO in the Github Discussions. You can upgrade your adapter according to the discussion.
Refer to other adapters, you can find all the adapters apps in Available adapters.
Not recommended: You can also do nothing but only upgrade the version of dbt-core at the setup.py. If you are lucky, it will still work for the new dbt-core, but you can't enjoy the new features brought by the new dbt-core.

According to the investigation methods, dbt-tidb sorts out the features that need to be implemented as follows:

For dbt-tidb 1.1.0:

Deprecate Python 3.7 and support Python 3.10
Implement the new adapter testing framework
Support multiple unique keys in incremental

For dbt-tidb 1.2.0:

Support Connection retry feature
Support grants feature
Support Cross-database macros (some macros under the dbt-util package have been migrated to dbt-core
Added BaseDocsGenerate and BaseValidateConnection tests

Test

I would like to introduce how to test before code because I strongly recommend using Test Driven Development(TTD) to develop the dbt adapter. That is: write the test first, and then implement the corresponding feature. If the test passes, the feature is considered to be supported.

Since dbt-core v1.1.0, dbt has provided a new testing framework for adapter maintainers. The new testing framework is included in dbt-core. With it, you can write your own tests easier. For more detail, you can refer to Testing a new adapter. dbt-tidb v1.1.0 started to use the new testing framework, and introduced the basic package to test the basic features.

dbt-tidb v1.2.0 adds the following tests based on the new features

basic package: add BaseValidateConnection and BaseDocsGenerate for testing connection and document generation
grant package: add a new grant package for testing grant features
util: add a new util package for testing Cross-database macros which are migrated from dbt-util

Let's take grants as an example, according to the newly added tests in dbt-core v1.2.0. We need to add the following tests for grants:

class TestModelGrantsTiDB(BaseModelGrants):
    pass


class TestIncrementalGrantsTiDB(BaseIncrementalGrants):
    pass


class TestSeedGrantsTiDB(BaseSeedGrants):
    pass


class TestSnapshotGrantsTiDB(BaseSnapshotGrants):
    pass


class TestInvalidGrantsTiDB(BaseInvalidGrants):
    pass

Here, we use pass without any implementation, the tests will inherit the default implementation of the test framework.

Code

Implement the feature

Now, it is time to implement the features.

Generally, dbt can be expanded by overriding the default macros or some methods. About which one needed to implement, here is a reference:

dbt officials may explain how to implement features in the GitHub discussions.
Refer to dbt-core's implementation of this feature.
Refer to the implementation of the other adapters.

According to the Upgrading to dbt-core 1.2.0 in the dbt-core's discussions. We find that grant is mainly implemented by overriding the macros of dbt-core, and the following macros need to be implemented:

get_show_grant_sql

Returns auth information, and the return format needs to be grantee (username in some adapter) + privilege_type.

For TiDB, we query the mysql.tables_priv table to obtain the auth information. Then filter the given DB and table. Next poll Select, Insert, Update, and Delete. Finally, output in the format of grantee,privilege_typeThe corresponding SQL is as follows:

{% macro tidb__get_show_grant_sql(relation) %}

    select case(Table_priv) when null then null else 'select' end as privilege_type, `User` as grantee from mysql.tables_priv  where `DB` = '{{relation.schema}}' and `Table_name` = '{{relation.identifier}}' and Table_priv like '%Select%'
    union ALL
    select case(Table_priv) when null then null else 'insert' end as privilege_type, `User` as grantee from mysql.tables_priv  where `DB` = '{{relation.schema}}' and `Table_name` = '{{relation.identifier}}' and Table_priv like '%Insert%'
    union ALL
    select case(Table_priv) when null then null else 'update' end as privilege_type, `User` as grantee from mysql.tables_priv  where `DB` = '{{relation.schema}}' and `Table_name` = '{{relation.identifier}}' and Table_priv like '%Update%'
    union ALL
    select case(Table_priv) when null then null else 'delete' end as privilege_type, `User` as grantee from mysql.tables_priv  where `DB` = '{{relation.schema}}' and `Table_name` = '{{relation.identifier}}' and Table_priv like '%Delete%'

{% endmacro %}

get_grant_sql

Use standard grant SQL to give privileges to multiple users. Note that users need to use double quotes in TiDB. The corresponding SQL is as follows:

{%- macro tidb__get_grant_sql(relation, privilege, grantees) -%}
    grant {{ privilege }} on {{ relation }} to {{ '\"' + grantees|join('\", \"') + '\"' }}
{%- endmacro -%}

get_revoke_sql

Use standard revoke SQL to revoke privileges. Double quotes are also needed in TiDB. The corresponding SQL is as follows:

{%- macro tidb__get_revoke_sql(relation, privilege, grantees) -%}
    revoke {{ privilege }} on {{ relation }} from {{ '\"' + grantees|join('\", \"') + '\"' }}
{%- endmacro -%}

Find the bug by tests

After implementing the feature, we need to test it with the testing framework. In most cases, you will find the tests fail. Here are some suggestions for you to fix the bugs:

Find the root cause according to the errors shown by dbt and fix it. For example, most of the SQL syntax errors can be found in this way.
Read the pr for the feature in dbt-core.
- Check if any macros/methods that have been overridden by your adapter changed. If so, we may need to change the adapter too.
- See if there are other macros/methods added.
Refer to the implementation of the other adapters to see if you miss anything.

When we support grants in dbt-tidb. We find that the incremental materialization and snapshot materialization are changed in the dbt-core's pr of grant feature. Unfortunately, dbt-tidb has overridden them before. Thus, we need to update them in dbt-tidb:

{% materialization incremental, adapter='tidb' %}

   -- other code
  {% set grant_config = config.get('grants') %}

   -- other code
  {% set should_revoke = should_revoke(existing_relation, full_refresh_mode) %}
  {% do apply_grants(target_relation, grant_config, should_revoke=should_revoke) %}

   -- other code

{%- endmaterialization %}

The code first gets the grant configuration and then calls apply_grants to apply the get_grant_sql method which is implemented above.

We also found that the newly added call_dcl_statements macro needs to be overwritten to convert multiple SQL into some single SQL. Because dbt-tidb does not support multiple SQL yet

{% macro tidb__call_dcl_statements(dcl_statement_list) %}
    {% for dcl_statement in dcl_statement_list %}
        {% call statement('grant_or_revoke') %}
            {{ dcl_statement }}
        {% endcall %}
    {% endfor %}
{% endmacro %}

Fix the test

After you fix all the bugs, there may also occur some errors in the test. These errors are not because of your implementation, but for some compatibility issues of the tests. Some tests need to be changed to be compatible with your adapter. You can also find out how to modify the test by Testing a new adapter

We also modify the test when implementing grants in dbt-tidb.

BaseInvalidGrants is the test for the invalid grantee and privilege_type. Obviously, different adapters may throw different errors for the invalid case. So, it is necessary to modify the test. For TiDB:

class TestInvalidGrantsTiDB(BaseInvalidGrants):
    def grantee_does_not_exist_error(self):
        return "You are not allowed to create a user with GRANT"

    def privilege_does_not_exist_error(self):
        return "Illegal privilege level specified for"

Congratulations! I think your adapter is ready for release now.

Using dbt-tidb v1.2.0

Now, let's experience the new adapter. Here we will use dbt-tidb v1.2.0.

Set up

1.install dbt-tidb v1.2.0

$ pip3 install dbt-tidb=1.2.0

2.Build TiDB. We choose to build TiDB with TiDB Cloud's free trial — developer tier

Sign in with your account, and the page will jump to the TiDB Cloud console.
Click Create Cluster button and you will jump to the creation page.
Click Create on the creation page without any change to get a developer tier and you will go back to the TiDB Cloud console.
Wait for the cluster to be ready. Then you can click Connect button to get the user and host.

3.Download jaffle_shop on Github. It is a dbt project for testing maintained by the official

git clone https://github.com/dbt-labs/jaffle_shop

4.Addprofiles.yml in ~/.dbt. It configures the connection information

jaffle_shop_tidb:                        # project name
  target: dev                             
  outputs:
    dev:
      type: tidb                         # adapter name
      server: gateway01.ap-southeast-1.prod.aws.tidbcloud.com # your TiDB host
      port: 4000                         # your TiDB port
      schema: test                       # db name
      username: 41y7Jq2g5sBr2ia.root     # your TiDB user name
      password: ${fake_password}         # you tidb password

5.Change the dbt_project.yml in jaffle_shop, you only need to change the profile

profile: 'jaffle_shop_tidb'

6.Execute dbt debug in the jaffle_shop to check your configs

dbt debug

Connection Retry Feature

Run, build and test in dbt may generate hundreds of independent connections. A single connection timeout due to networking may fail the entire project. Therefore, dbt-tidb adds a retry feature to solve the temporary connection timeout problem.

Connection Retry example

1.Add retries config in profile.yml, it determines the retry times. We use an invalid user to simulate connection failure.

jaffle_shop_tidb:                        # project name
  target: dev                             
  outputs:
    dev:
      type: tidb                         # adapter name
      server: gateway01.ap-southeast-1.prod.aws.tidbcloud.com # your TiDB host
      port: 4000                         # your TiDB port
      schema: test                       # db name
      username: 41y7Jq2g5sBr2ia.invaild_user     # your TiDB user name
      password: ${fake_password}         # you tidb password

2.Execute dbt debug, you will find the Database Error

$ dbt debug
Connection:
  server: gateway01.ap-southeast-1.prod.aws.tidbcloud.com
  port: 4000
  database: None
  schema: test
  user: 41y7Jq2g5sBr2ia.invaild_user
  Connection test: [ERROR]

1 check failed:
dbt was unable to connect to the specified database.
The database returned the following error:

  >Database Error
  1045 (28000): Access denied for user '41y7Jq2g5sBr2ia.invaild_user'@'10.0.123.88' (using password: YES)

3.Check the dbt log and you will find three times of retry before throw error.

$ cat dbt.log
06:24:19.875482 [debug] [MainThread]: tidb adapter: Got a retryable error when attempting to open a tidb connection.
3 attempts remaining. Retrying in 1 seconds.
Error:
1045 (28000): Access denied for user '41y7Jq2g5sBr2ia.invaild_user'@'10.0.123.88' (using password: YES)
06:24:21.321733 [debug] [MainThread]: tidb adapter: Got a retryable error when attempting to open a tidb connection.
2 attempts remaining. Retrying in 1 seconds.
Error:
1045 (28000): Access denied for user '41y7Jq2g5sBr2ia.invaild_user'@'10.0.123.88' (using password: YES)
06:24:22.703960 [debug] [MainThread]: tidb adapter: Got a retryable error when attempting to open a tidb connection.
1 attempts remaining. Retrying in 1 seconds.
Error:
1045 (28000): Access denied for user '41y7Jq2g5sBr2ia.invaild_user'@'10.0.123.88' (using password: YES)
06:24:24.069883 [debug] [MainThread]: tidb adapter: Error running SQL: select 1 as id

Grants Feature

In dbt-tidb, you can grant table privileges to the given user with grants.

Now, you can add grants config in the model, seed, and snapshots. If you configure it under dbt_project.yml, all resources in the project will take effect. Of course, you can also configure it with SQL or YAML for a specific resource, which will override the config in dbt_project.yml. Pay attention that grants do not support creating users, you need to create the users in TiDB first.

Grants Example

1.Create users in TiDB, note that the user name in the dev tier contains a prefix

CREATE USER '41y7Jq2g5sBr2ia.user1'@'%' IDENTIFIED BY '';
CREATE USER '41y7Jq2g5sBr2ia.user2'@'%' IDENTIFIED BY '';
CREATE USER '41y7Jq2g5sBr2ia.user3'@'%' IDENTIFIED BY '';

2.Add grants config in dbt_project.yml

seeds:
  +grants:
     select: ['41y7Jq2g5sBr2ia.user1','41y7Jq2g5sBr2ia.user2']
     insert: ['41y7Jq2g5sBr2ia.user1','41y7Jq2g5sBr2ia.user3']

3.Execute dbt seed

$ dbt seed
06:38:49  Concurrency: 1 threads (target='dev')
06:38:49
06:38:49  1 of 3 START seed file test.raw_customers ...................................... [RUN]
06:38:50  1 of 3 OK loaded seed file test.raw_customers .................................. [INSERT 100 in 1.58s]
06:38:50  2 of 3 START seed file test.raw_orders ......................................... [RUN]
06:38:52  2 of 3 OK loaded seed file test.raw_orders ..................................... [INSERT 99 in 1.52s]
06:38:52  3 of 3 START seed file test.raw_payments ....................................... [RUN]
06:38:54  3 of 3 OK loaded seed file test.raw_payments ................................... [INSERT 113 in 1.66s]
06:38:55
06:38:55  Finished running 3 seeds in 0 hours 0 minutes and 9.09 seconds (9.09s).
06:38:55
06:38:55  Completed successfully
06:38:55
06:38:55  Done. PASS=3 WARN=0 ERROR=0 SKIP=0 TOTAL=3

4.Query the result in TiDB

mysql> select * from mysql.tables_priv where User in('41y7Jq2g5sBr2ia.user1','41y7Jq2g5sBr2ia.user2','41y7Jq2g5sBr2ia.user3');
+------+------+-----------------------+---------------+---------+---------------------+---------------+---------------+
| Host | DB   | User                  | Table_name    | Grantor | Timestamp           | Table_priv    | Column_priv   |
+------+------+-----------------------+---------------+---------+---------------------+---------------+---------------+
| %    | test | 41y7Jq2g5sBr2ia.user1 | raw_customers |         | 2022-08-19 06:46:08 | Select,Insert | Select,Insert |
| %    | test | 41y7Jq2g5sBr2ia.user2 | raw_customers |         | 2022-08-19 06:46:08 | Select        | Select        |
| %    | test | 41y7Jq2g5sBr2ia.user3 | raw_customers |         | 2022-08-19 06:46:08 | Insert        | Insert        |
| %    | test | 41y7Jq2g5sBr2ia.user1 | raw_orders    |         | 2022-08-19 06:46:10 | Select,Insert | Select,Insert |
| %    | test | 41y7Jq2g5sBr2ia.user2 | raw_orders    |         | 2022-08-19 06:46:10 | Select        | Select        |
| %    | test | 41y7Jq2g5sBr2ia.user3 | raw_orders    |         | 2022-08-19 06:46:10 | Insert        | Insert        |
| %    | test | 41y7Jq2g5sBr2ia.user1 | raw_payments  |         | 2022-08-19 06:46:12 | Select,Insert | Select,Insert |
| %    | test | 41y7Jq2g5sBr2ia.user2 | raw_payments  |         | 2022-08-19 06:46:12 | Select        | Select        |
| %    | test | 41y7Jq2g5sBr2ia.user3 | raw_payments  |         | 2022-08-19 06:46:12 | Insert        | Insert        |
+------+------+-----------------------+---------------+---------+---------------------+---------------+---------------+

The grants are just as what we have set:

41y7Jq2g5sBr2ia.user1 has Select and Insert priv
41y7Jq2g5sBr2ia.user2 has Select priv
41y7Jq2g5sBr2ia.user3 has Insert pri

Use Cross-database macros

One of the strengths of dbt is that it can reuse macros (like functions). dbt-util is an official tool repository. We can reuse its macros by importing dbt-util.

Cross-database macros have been migrated from dbt-util to dbt-core in v1.2.0, which means you can use them directly without importing dbt-util.

dbt-tidb also supports using Cross-database macros directly. You can use the following macros in dbt-tidb.

bool_or
cast_bool_to_text
dateadd
datediff
date_trunc
hash
safe_cast
split_part
last_day
cast_bool_to_text
concat
escape_single_quotes
except
intersect
length
position
replace
right

Read our doc to see how to use them. Here we will take datediff as an example

1.Execute dbt seed to generate raw_orders table

dbt seed

2.Create datediff.sql in the models directory. It will calculate the days between order_date and 2018-01-01 in raw_orders

with orders as (

    select * from {{ ref('raw_orders') }}

)

select * , {{datediff( "'2018-01-01'", "order_date", 'day' )}} as datediff from orders

3.Execute dbt run -s datediff

4.Query the result in TiDB, you will get the results

mysql> select * from test.datediff;
+------+---------+------------+----------------+----------+
| id   | user_id | order_date | status         | datediff |
+------+---------+------------+----------------+----------+
|    1 |       1 | 2018-01-01 | returned       |        0 |
|    2 |       3 | 2018-01-02 | completed      |        1 |
|    3 |      94 | 2018-01-04 | completed      |        3 |
|    4 |      50 | 2018-01-05 | completed      |        4 |
|    5 |      64 | 2018-01-05 | completed      |        4 |
|    6 |      54 | 2018-01-07 | completed      |        6 |
|    7 |      88 | 2018-01-09 | completed      |        8 |
|    8 |       2 | 2018-01-11 | returned       |       10 |
|    9 |      53 | 2018-01-12 | completed      |       11 |
|   10 |       7 | 2018-01-14 | completed      |       13 |
|   11 |      99 | 2018-01-14 | completed      |       13 |
|   12 |      59 | 2018-01-15 | completed      |       14 |
|   13 |      84 | 2018-01-17 | completed      |       16 |
|   14 |      40 | 2018-01-17 | returned       |       16 |
|   15 |      25 | 2018-01-17 | completed      |       16 |
|   16 |      39 | 2018-01-18 | completed      |       17 |
|   17 |      71 | 2018-01-18 | completed      |       17 |
|   18 |      64 | 2018-01-20 | returned       |       19 |
|   19 |      54 | 2018-01-22 | completed      |       21 |
|   20 |      20 | 2018-01-23 | completed      |       22 |

Conclusion

This article takes dbt-tidb as an example to share the process and skills of upgrading dbt adapter. Then introduce how to use the new features in dbt-tidb v1.2.0. Hope it will help you with the upgrade of your adapter.

Welcome to contribute dbt-tidb.

DEV Community: shiyuhang0

Best PlanetScale alternative: TiDB Serverless

MySQL compatible

True Serverless

Features

Ecosystem

Pricing

Migration

Connect database from the edge

What is edge

Why it is hard to connect the database at the edge

Efforts from the database providers

Efforts from edge provider

Deno

Cloudflare Workers

Conclusion

Reference

Access TiDB Cloud by Hashicorp Vault

Prerequest

Starting the Server

Configure secrets engine (admin role)

Create a readonly token (admin role)

Use dynamic secrets (app role)

About the leases

Prospect

Use OpenAI and Zapier to perform blog moderation automatically - no code required

Set up your blog

Use OpenAI and Zapier

Try the workflow

Summary

Make Deno MySQL driver works better

Authentication Method Mismatch

1. Parse AuthSwitchRequest

2. Send AuthSwitchResponse with authData

3. Change the auth plugin only once

CLIENT_DEPRECATE_EOF

1. Deprecated EOF packet without CLIENT_DEPRECATE_EOF flag

2. EOF packet need to be replaced by OK packet

3. More things to do

Test with TiDB

Manage TiDB Cloud with Terraform

Introduce

Set up

Create an API key

Get TiDB Cloud provider

Config the provider

Get projectId with project Data Source

Get cluster spec info with cluster spec Data Source

Create a dedicated cluster with cluster resource

Change the dedicated cluster

Create a backup with backup resource

Create a restore task with restore resource

Importing the restore cluster

Destroy the dedicated cluster

Conclusion

Understand TiSpark pushdown

What is pushdown in Spark?

How Spark implements pushdown

pushdown optimization

Pushdown to data source

TiSpark pushdown strategy

The strategy in TiSpark

How TiSpark implements the strategy

Conclusion

Appendix

Data Transformation on TiDB Made Easier

Installation

Creating the project: jaffle shop

Configuring the project

Loading CSV files

Running the dbt project

Generating visual documents

Conclusion

How to upgrade your dbt adapter

Version Rules

Investigation

Test

Code

Implement the feature

Find the bug by tests

1. Deprecated EOF packet without `CLIENT_DEPRECATE_EOF` flag