Writing Queries

In general, Vertica is designed to execute the same SQL standard queries that run on other databases. However, there are several differences between Vertica queries and those used in other relational database management systems:

• Existing queries that were written for a normalized database may need modification in order to conform to the Star Schema or Snowflake Schema used in the Vertica database.

  A snowflake schema is the same as a star schema except that a dimension table can be normalized (hierarchically decomposed) into additional dimension tables. Every dimension table participates in a 1::n join with the fact table or another dimension table. 

  The star schema (sometimes called a star join schema) is the simplest data warehouse schema. In a star schema design there is a central fact table with a large number of tuples, optionally surrounded by a collection of dimension tables, each with a lesser number of tuples.  Every dimension table participates in a 1::n join with the fact table.

  A data warehouse is a relational database that is designed for query and analysis rather than transaction processing. Data warehouses:

  • are often subjected to a heavy load of periodic and ad-hoc queries.
  • contain historical information that enables analysis of correlations and trends over long periods of time.
  • integrate data from various production (transactional) databases. Extraction, transformation, and loading (ETL) software converts the data to a common format and copies it into a data warehouse at regular intervals.

    ETL (Extract, Transform, Load) is a process in data warehousing that involves extracting data from outside sources, transforming it to fit a specific schema, and ultimately loading it into the database.

  • typically consist of one or more star or snowflake schemas.
• Vertica does not support SQL standard views (stored queries). Existing queries that were written for standard SQL views may need to be rewritten to incorporate the query stored in the view. These queries are excellent candidates for pre-join Projections.

  A pre-join projection stores the result set obtained by joining a single fact table in the logical schema (the anchor table) with one or more dimension tables. The result set is typically sorted for optimal query execution performance. Most query-specific projections are pre-joins.

• The Vertica transaction model is different from the SQL standard in a way that has a profound effect on query performance (see below). You can:
  • execute a query on a static snapshot of the database from any specific date and time. This avoids holding locks or blocking other database operations.
  • you can use a subset of the standard SQL isolation levels and access modes (read/write or read-only) for a user session.

    A SQL session is an occurrence of a user interacting with a database through the use of SQL statements. A session can be invoked using vsql or a JDBC application. In Vertica, the scope of a session is the same as that of a connection.

    JDBC (Java Database Connectivity) is a call-level API that provides connectivity between Java programs and data sources (SQL databases and other non-relational data sources, such as spreadsheets or flat files). JDBC is included in the Java 2 standard and enterprise editions.

    vsql is the Vertica implementation of psql, a character-based, interactive, front-end that is part of PostgreSQL and used by other database management systems. It allows you to type in SQL statements and see the results. It also provides a number of meta-commands and various shell-like features to facilitate writing scripts and automating a wide variety of tasks.