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.

External Tables

Yellowbrick also supports external tables, which are tables that are stored in files outside the database. You can create an external table by "unloading" the results of any valid Yellowbrick query into a specific file on an NFS-mounted file system. You can also read from an external table by selecting from it directly or by using CTAS or INSERT statements to select external data that creates or updates other 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 backend storage with rows that are temporarily stored in the front end database. See Flushing Tables.
Table DDL
Use the SMC to copy the DDL for a table to the clipboard. Go to Databases and select a database. Then double-click the table ID. On the Details screen, click 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.

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.