Tutorial 1: Create and manage databases, schemas, and tables¶
Introduction¶
In this tutorial, you learn how to submit REST queries to create and manage databases, tables, and schemas.
Prerequisites¶
Note
If you have already completed the steps in Common setup for Snowflake REST APIs tutorials, you can skip these prerequisites and proceed to the first step of this tutorial.
Before you start this tutorial, you must complete the common setup instructions, which includes the following steps:
Import the Snowflake REST APIs Postman collections.
Authenticate your connection by setting the bearer token in Postman.
After completing these prerequisites, you are ready to start using the API.
Create a database and list available databases¶
You can use Postman to create a database and list available databases.
To create a database, send a
POST
request with the following request body to the/api/v2/databases
endpoint, as shown.{ "name": "demo_db", "kind": "PERMANENT", "comment": "snowflake rest api demo-db", "data_retention_time_in_days": "1", "max_data_extension_time_in_days": "1" }
To list available databases, send a
GET
request to the/api/v2/databases
endpoint, as shown in the following examples:To find databases whose name contains the string,
demo
, specify%25demo%25
in the like query parameter.To return the first database whose name starts with the string,
DEMO_DB
, specifyDEMO_DB
and1
in the startsWith and showLimit query parameters, respectively.
For more information, see the Snowflake Database API reference.
Create a schema and list available schemas¶
You can use Postman to create a schema and list available schemas.
To create a schema, send a
POST
request to the/api/v2/databases/{database}/schemas
endpoint, as follows:Add the database name (
demo_db
) to the database path variable in the request header.Add the schema name (
demo_sc
) to the request body.{ "name": "demo_sc", }
To list available schemas, send a
GET
request to the/api/v2/databases/{database}/schemas
endpoint. In this example, you return the first schema whose name starts with the string,DEMO_SC
, by specifyingDEMO_SC
and1
in the startsWith and showLimit query parameters, respectively.
For more information, see the Snowflake Schema API reference.
Create a table and fetch the table details¶
You can use Postman to create a table and list available tables.
To create a table, send a
POST
request to the/api/v2/databases/{database}/schemas/{schema}/tables
endpoint, as follows:Add the database name (
demo_db
) and the schema name (demo_sc
) in the database and database path variables, respectively, in the request header.Add the table name (
demo_tbl
) and the table columns to the request body. In this case, you added one column namedC1
.{ "name": "demo_tbl", "columns": [ { "name": "c1", "datatype": "integer", "nullable": true, "comment": "An integral value column" } ], "comment": "Demo table for Snowflake REST API" }
To fetch the table you just created, send a
GET
request to the/api/v2/databases/{database}/schemas/{schema}/tables/{name}
endpoint. In this case, you specifydemo_db
,demo_sc
, anddemo_tbl
in the database, schema and name path variables, respectively.
For more information, see the Snowflake Table API reference.
Alter a table and fetch the table details¶
You can use Postman to alter a table.
To alter the table you created in the last tutorial, send a
PUT
request to the/api/v2/databases/{database}/schemas/{schema}/tables/{name}
endpoint, as follows:Specify the names of the database, schema, and table you created in the corresponding path variables.
In the request body, enter the new table definition. In this case, you add a new column to the table.
{ "name": "demo_tbl", "columns": [ { "name": "c1", "datatype": "integer", "nullable": true, "comment": "An integral value column" }, { "name": "c2", "datatype": "string", "comment": "An string value column" } ], "comment": "Demo table for Snowflake REST API" }
Verify the change by fetching the table details by sending a
GET
request to the/api/v2/databases/{database}/schemas/{schema}/tables/{name}
endpoint. In this case, you specifydemo_db
,demo_sc
, anddemo_tbl
in the database, schema and name path variables, respectively.Notice the table now contains a new
C2
column.
For more information, see the Snowflake Table API reference.
List available tables¶
You can use the /api/v2/databases/{database}/schemas/{schema}/tables
endpoint to return lists of all tables available to you.
To list all available tables, send a
GET
request to the/api/v2/databases/{database}/schemas/{schema}/tables
endpoint with no query parameters, as follows. In this case, you specifydemo_db
anddemo_sc
, anddemo_tbl
in the database, schema and name path variables, respectively.To list full details of the columns and constraints in every table, add the recursive query parameter and set the value to
true
, as shown. Be aware that enabling this query parameter can overwhelm your connection if you have multiple complex tables.
For more information, see the Snowflake Table API reference.