Product Documentation

Appearance

All platforms

Managing Tables and Views ​

After creating a database and (optionally) schemas within it, you can create tables and views. You can also create tables and views within the default yellowbrick database.

Database Tables ​

Yellowbrick supports persistent tables and temporary tables.

  • Persistent tables remain in the database until they are explicitly dropped.
  • Temporary tables are local to the session in which they are created and are dropped when the session ends.

You can use CREATE TABLE, CTAS, and SELECT INTO statements to create tables.

Table Maintenance ​

Aside from loading tables, you may need to maintain them in a few other ways:

Altering and Dropping Tables

Yellowbrick supports basic ALTER TABLE and DROP TABLE commands. You can add columns, rename a table, and change the owner of the table.

You can also GRANT and REVOKE privileges at the table and column level.

Inserting, Updating, and Deleting Rows

Yellowbrick supports standard SQL INSERT, UPDATE, and DELETE commands.

You can also run the \copy command to load smaller tables from files via ybsql. In general, you should use the bulk loader (ybload) to load large tables.

Note: The Yellowbrick column store is designed to optimally store, compress, and organize data for high-volume read-oriented analytics. As a result, when data is deleted it continues to exist in the system for a period of time until it can be efficiently removed and data can be reorganized. These optimizations are fully automatic and require no administration or manual interaction.

Analyzing Tables

By default, tables are automatically analyzed. This analysis updates column-level statistics that the planner uses to optimize query execution. See Auto-Analyzing Tables.

Flushing Tables

A background flush command periodically updates the Yellowbrick column store with rows that are temporarily stored in the row store. See Managing the Row Store.

Table DDL

You can use Yellowbrick Manager to copy the DDL for a table. Go to Databases and select the database and schema. Then click the table name and go to Details > Definition > Copy to Clipboard. For example:

See also Distribution Options.

User-Defined Views ​

You can create a view based on any valid Yellowbrick query, and you can reference other views in the view definition. Views are not materialized. See CREATE VIEW for syntax details.

Yellowbrick views are persistent objects. They are not dependent on the tables that they reference; therefore, you can drop or rename a table and its associated views persist in the database. For example, you can create table team, then create view teamview by selecting from team. If you drop team with CASCADE, the view remains in the database. If table team is re-created, you can still select from teamview as long as the columns that originally existed in the table also exist in the new instance of the table and have the same data types.

For example:

premdb=# create view htv as select * from hometeam;
CREATE VIEW
premdb=# \d htv
            View "public.htv"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 htid   | smallint              | 
 name   | character varying(30) | 

premdb=# drop table hometeam cascade;
DROP TABLE
premdb=# select * from htv;
ERROR:  relation "public.hometeam" does not exist
premdb=# create table hometeam (htid smallint, name varchar(30), stadium varchar(50));
CREATE TABLE
premdb=# insert into hometeam values (1, 'Leicester City', 'King Power Stadium');
INSERT 0 1
premdb=# select * from htv;
 htid |      name      
------+----------------
   1 | Leicester City
(1 row)

Note that a select * query against a view whose table was dropped and re-created will only return data for the set of columns that were defined in the original instance of the table. The select * view definition is expanded to the specific columns that are in the table when the view is created:

premdb=# \d+ htv
                   View "public.htv"
 Column |         Type          | Modifiers | Description 
--------+-----------------------+-----------+-------------
 htid   | smallint              |           | 
 name   | character varying(30) |           | 
View definition:
 SELECT hometeam.htid,
   hometeam.name
   FROM hometeam;

System Views ​

System views contain metadata and statistics for various aspects of the system. You can query these views, but you cannot modify or drop them. System views are based on a set of underlying system tables, but these tables are not intended for general use. See System Views.