Databases: Key Terms

[jamesnestler]

Knowledge Drop

Last tested: Dec 9, 2019
 

Permissions and Objects

Databases restrict what (action) a user can do on which objects. The terms for each of these differ slightly from dialect-to-dialect, but themes will remain the same across all.

• Permission: What a user can do. Most common examples:
  • SELECT : Can run a query (SELECT *, SELECT AVG(some_column), etc).
  • ALTER/UPDATE TABLE : Can change a table in some way. Usually needs more permissions depending on the action we're trying to take on the table, see below.
  • INSERT: Can add rows to a column in a table.
  • DROP TABLE : Can delete a table.
  • CREATE TABLE : Can create a new table in a given schema
  • GRANT : Can change other user's permissions on the given table.
• Objects: An object is a container for data or containers containing data. Objects are hierarchal (columns live in tables inside schemas inside databases) and permissions can be granted on each object, individually.
  • Database: Databases are themselves objects (meta I know). A database is a grouping of schemas.
  • Schema: A container for tables. Typically, a given schema will contain tables that relate to each-other in some way.
  • Table: A container for columns of data.
  • Column: A container for cells of data. Some databases do not allow column-level permissions.
• Role: A grouping of permissions over a given set of objects. A user's role could have permission to run SELECT on table1 and no permissions on table2, drop table on tables in schema1 and only select permissions on tables in schema2, etc.
• Group: A way of bulk-managing multiple user's Roles.

Other terms you may see in the wild:

• Temp Table: Some databases support the creation of temporary(ish) tables derived from a SQL query. These are roughly equivalent to Looker's concept of Persistent Derived Tables, and can be queried like any other table. Synonyms: Derived Table (MySQL), View, Virtual Table
  • Temp Tables are generally created with the CREATE TEMPORARY TABLE(MySQL) command, or the CREATE VIEW command (in other dialects).
• Stored Process: A custom-user-defined function that can be used in a query. Basically a snippet of SQL that can be passed parameters and called by referring to it's user-given function name. Synonyms: UDF (User Defined Function -- BigQuery), Stored Procedure
  • For example, we could create a function SUBTRACT(colx, coly) that takes values from column x and column y and subtracts them.
  • UDFs can be defined in and used by Looker in some dialects with the sql_preamble: LookML parameter (see Lloyd's Community article here for a great example).
• Virtual Warehouse: You'll usually hear this term in the context of Snowflake, however some other databases have a similar notion. In Snowflake, a data warehouse (virtual warehouse) is a SQL engine (the compute power that generates and runs queries) shared by one or more databases.
• Information Schema: Database tables that store metadata about the database. Users and their permissions, information about objects and the datatypes they contain, and query history will all be stored here.
  • While not every database calls their metadata tables the information_schema, all databases have an equivalent concept. See the table here: https://dataedo.com/kb/databases/all/information_schema
  • These tables can all be queried the same way one would query any other table.

This content is subject to limited support.