Database / Schemas

Databases and Schemas :

Databases and schemas are used to organize data stored in Snowflake:

• A database is a logical grouping of schemas. Each database belongs to a single Snowflake account.

• A schema is a logical grouping of database objects (tables, views, etc.). Each schema belongs to a single database.

 Together, a database and schema comprise a namespace in Snowflake. When performing any operations on database objects in Snowflake, the namespace is inferred from the current database and schema in use for the session. If a database and schema are not in use for the session, the namespace must be explicitly specified when performing any operations on the objects.

Creating Database :

example : 

CREAE DATABAE my_database;

syntax : 

CREATE [ OR REPLACE ] [ TRANSIENT ] DATABASE [ IF NOT EXISTS ] <name>

    [ CLONE <source_db>

          [ { AT | BEFORE } ( { TIMESTAMP => <timestamp> | OFFSET => <time_difference> | STATEMENT => <id> } ) ] ]

    [ DATA_RETENTION_TIME_IN_DAYS = <integer> ]

    [ MAX_DATA_EXTENSION_TIME_IN_DAYS = <integer> ]

    [ DEFAULT_DDL_COLLATION = '<collation_specification>' ]

    [ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]

    [ COMMENT = '<string_literal>' ]

Schemas :

A second -level organizational grouping, within a database, is called a schema. 

Every time you create a database, Snowflake will automatically creates two schemas for you.

1. Information_schema :

The information schema holds a collection views. This schema can't be deleted (dropped) , renamed or moved.

2. Public Schema :

The public schema is created empty and you can fill it with tables, views and oher things over time. The PUBLIC schema can be dropped, renamed or moved at any time.

Ex : 

drop schema public;

Creating a schema :

Example :

create schema demo;

syntax :

CREATE [ OR REPLACE ] [ TRANSIENT ] SCHEMA [ IF NOT EXISTS ] <name>
  [ CLONE <source_schema>
        [ { AT | BEFORE } ( { TIMESTAMP => <timestamp> | OFFSET => <time_difference> | STATEMENT => <id> } ) ] ]
  [ WITH MANAGED ACCESS ]
  [ DATA_RETENTION_TIME_IN_DAYS = <integer> ]
  [ MAX_DATA_EXTENSION_TIME_IN_DAYS = <integer> ]
  [ DEFAULT_DDL_COLLATION = '<collation_specification>' ]
  [ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]
  [ COMMENT = '<string_literal>' ]

Objects that are physically stored under databases (Named spaces) are called database objects

• Tables
• views
• UDFs
• Stored Procedures etc..

All Database objects follow hierarchy like Account --> Database --> Schema--> Database objects

Generally we use the below commands to work with database objects

SHOW,    GET_DDL,    DESCRIBE

SHOW : 

The SHOW command, which will list all objects of a specified type, as long as you have a privilege on that object. For example, if I want to list all tables or a specific table,

show tables;

show tables like 'TABLE_1%' in database demo_db;

GET_DDL : 

 Getting DDL gives you the script you can use to re-create or modify the object.

select get_ddl('table', 'table_1');

select get_ddl('database','demo_db');

DESCRIBE : 

When we need need the column names of a table we can use this Describe command to learn more about the object like table.

 describe table table_1;